Running queries using osql

Question

When executing any one of the following commands:

osql -E -S ComputerName\InstanceName
osql -E -S ComputerName\InstanceName -i MyScript.sql -o MyOutput.rpt
osql -E -q "SELECT * FROM Northwind.dbo.Shippers"
osql -E -Q "SELECT * FROM Northwind.dbo.Shippers" -o MyOutput.rpt

I am getting the following error:

[SQL Server Native Client 10.0]SQL Server Network Interfaces: Connection
string is not valid [87].
[SQL Server Native Client 10.0]Login timeout expired
[SQL Server Native Client 10.0]A network-related or instance-specific error
has occurred while establishing a connection to SQL Server. Server is not
found or not accessible. Check if instance name is correct and if SQL Server
is configured to allow remote connections. For more information see SQL Server
Books Online.

However, I am able, without issue to login and run SELECT queries from SSMS.

How do I run queries against SQL Server 2008 using osql?

[Steps to troubleshoot SQL connectivity issues](http://blogs.msdn.com/b/sql_protocols/archive/2008/04/30/steps-to-troubleshoot-connectivity-issues.aspx) — Remus Rusanu, Jul 20 '15 at 21:35

score 5 · Accepted Answer · answered Jul 20 '15 at 21:32

Do you have your logged in account set up as a user in SQL Server?

I usually work with specific accounts and SQL Server logins instead of Trusted Logins, and then just specify the database coordinates on the command line with the -S, -D, -U, and -P options:

osql -S %SERVERNAME% -U %USERNAME% -P %PASSWORD% -d %DBNAME%

For instance, if your server name is MyServer\SQL2008 and your user name is Foo and your password is Bar and your database is MyDB, then you'd use this:

osql -S MyServer\SQL2008 -U Foo -P Bar -d MyDB

And then continue on with the rest of your options after that.

If you really want to use your Trusted connection, you need to go to SQL Server Management Studio, and ensure your current Widows Login is added as a user and given appropriate permissions to your database, etc.

In SSMS, connect to your server manually (the 'sa' user and password perhaps), and then expand the "Security" node and look at the logins. If your currently logged in Windows User isn't listed, you'll want to right-click, add new Login, and add your current user.

Then you should be able to run with a Trusted Connection.

Thanks. I don't have admin rights. If I'm able to select from ssms why wouldn't it work from osql? — Alex Gordon, Jul 21 '15 at 00:42

score 4 · Answer 2 · answered Jul 21 '15 at 01:18

4

You have to run all command in a single line like this

osql -E -S ComputerName\InstanceName -i MyScript.sql -o MyOutput.rpt

or

osql -E -S ComputerName\InstanceName -Q "SELECT * FROM Northwind.dbo.Shippers" -o MyOutput.rpt

Now you have to see if you can log in SQL Server or if the service is up or even if the TCP/IP protocol is enable

answered Jul 21 '15 at 01:18

Krismorte

642
7
24

Looks like the SQL server browser service is not running. Could this be the issue? I don't have permissions to start the service – Alex Gordon Jul 21 '15 at 09:37
SQL Server Browser works with instances named SQL and Cluster instalations. – Krismorte Jul 24 '15 at 14:03

score 1 · Answer 3 · edited Jan 02 '20 at 04:15

1

Use the value in the Server name: field for ComputerName\InstanceName. (e.g. MYPC\SQLEXPRESS) Type:

osql -E -S MYPC\SQLEXPRESS

You will see the interactive prompt. Then enter your commands:

USE pubs
GO

Also, you can use sqlcmd:

sqcmd -E -S MYPC\SQLEXPRESS

edited Jan 02 '20 at 04:15

phwt

1,356
1
22
42

answered Jan 02 '20 at 02:04

Robert Lazo

11
2

Running queries using osql

3 Answers3