Search code examples
sql-serversql-server-2008sqlcmd

Run sql script using sqlcmd.exe

I have a .sql file which should be executed, and this command bellow:

set auth=-S 192.168.1.200 -d solarix -U sa -P masterkey  -f 65001
set sqlcmd="%ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe"
%sqlcmd% -i dictionary.sql -b -a 32767 -o mssql-dictionary.log %auth%

How can I make it work?

Solution

  • So I just ran this:

    set _auth=-S . -d Master -U sa -P mypassword  -f 65001
set _sqlcmd="%ProgramFiles%\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE"
%_sqlcmd%  -i dictionary.sql -b -a 32767 -o mssql-dictionary.log  %_auth%
set _auth=
set _sqlcmd=

    Where my dictionary.sql file contained just one line:

    Select @@Version

    (Note I picked "Master" as my db).

    Note my Server -S argument is ".".

    The above worked.

    When I put in hte IP address of the "." machine.

    set _auth=-S 192.168.1.101 -d Master -U sa -P mypassword -f 65001
set _sqlcmd="%ProgramFiles%\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE"
%_sqlcmd%  -i dictionary.sql -b -a 32767 -o mssql-dictionary.log %_auth%

set _auth=
set _sqlcmd=

    When I put in the IP address, it did NOT work.

    Which means that even though I am on "192.168.1.101", the Sql Server is not setup for remote-connections.

    Here is the poor man's way to test a connection.

    Go to Control-Panel/Admin-Tools/ODBC

    And create a system dsn (temporary, you can delete it later)....put in your credentials...and see if you can connect.

    It may be that your sqlcmd parameters are correct, but you're not allowed to remote connect to that machine.

    But the syntax sugar of your code looks right.

    You would see something like this:

    Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [5]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.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..

    In your log file, if you cannot connect to the sql-server.