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?

Answer 1

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.

Answer 2

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