none
sqlcmd error - HResult 0xFFFFFFFF, Level 16, State 1

    Question

  • I am trying make to make a remote SQL server execute a small .sql file (for now. later, i will try a huge 200 mb.sql file) using sqlcmd (on my client computer). I can easily connect to my server via management studio. But, I get an error when I try to connect via sqlcmd.exe instead. I can access the server using sqlcmd - I get the > command prompt.

    my command is of the form - 

    sqlcmd -S .\MYSQLSERVER2008 -U MyUsername -P MyPassword -i C:\Database\hugescript.sql

    This causes an error -

    HResult 0xFFFFFFFF, Level 16, State 1 SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. Sqlcmd : error : Microsoft 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.. Sqlcmd : error : Microsoft SQL Server Native Client 10.0 : Login timeout expired

    Solutions that did not work -

    How to connect Sqlcmd to the server?

    http://blogs.msdn.com/b/spike/archive/2010/06/21/quot-error-locating-server-instance-specified-xffffffff-quot-sqlsrv32-to-the-rescue.aspx

    I tried his add a odbc dsn thing. But, I still get the same error. I am going to try more google searches. But, if anyone knows how to fix this, please help me. Thanks.




    • Edited by blastoSO Wednesday, October 30, 2013 6:29 PM
    Wednesday, October 30, 2013 6:06 PM

All replies

  • What do you see when executing:  sqlcmd -L

    Why do you have .\ prefix?


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Wednesday, October 30, 2013 6:14 PM
    Moderator
  • What do you see when executing:  sqlcmd -L

    Why do you have .\ prefix?


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    I see a list of servers when i type sqlcmd -L . My server's name is not there in that list. I never cared about that because i never saw my server even in SSIS (an ETL tool that I am using these days) connection managers, but I could still connect to it by typing its name in the server field. So, I tried the same thing with sqlcmd.

    .\ prefix - I don't know what that means or what it does. I just picked it up from a forum in the hope of making sqlcmd execute a 200mb sql script. I am guessing that . means all servers ???



    • Edited by blastoSO Wednesday, October 30, 2013 6:35 PM
    Wednesday, October 30, 2013 6:35 PM
  • Hi,

    What happens if you use the older tool osql?

    osql -S .\MYSQLSERVER2008 -U MyUser -P MyPassword -i c:\database\hugescript.sql


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Wednesday, October 30, 2013 6:35 PM
  • Hi,

    What happens if you use the older tool osql?

    osql -S .\MYSQLSERVER2008 -U MyUser -P MyPassword -i c:\database\hugescript.sql


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    For starters, I tried only - osql -S .\MYSQLSERVER2008 -U MyUser -P MyPassword .Like sqlcmd, I see a command prompt after that >. I type exit to exit after that. why should one try osql instead of sqlcmd ?

      Okay, now I also tried with file and It gives me my original error.
    • Edited by blastoSO Wednesday, October 30, 2013 6:50 PM
    Wednesday, October 30, 2013 6:49 PM
  • That is a generic "cannot find server" message.

    ".\MYSQLSERVER2008" is not a valid server.  Please enter your correct server\instance name.

    Wednesday, October 30, 2013 6:52 PM
    Moderator
  • Hi,

    If you see a ">" then you're able to login to your instance.

    I just wanted to discard any trouble with SQLCMD client tool.

    So, you don't have a connectivity issue (either SQLCMD or OSQL), looks like there's a problem with the script itself.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Wednesday, October 30, 2013 6:52 PM
  • Hi,

    If you see a ">" then you're able to login to your instance.

    I just wanted to discard any trouble with SQLCMD client tool.

    So, you don't have a connectivity issue (either SQLCMD or OSQL), looks like there's a problem with the script itself.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Okay, what is allowed and what is not allowed inside a script that you want to execute with sqlcmd. I have a simple USE [DB] GO SELECT TOP 1000 ROWS FROM [dbo].[MyTable].

    Is that okay ? SQL server management studio always executes that query.

     

    Wednesday, October 30, 2013 7:03 PM
  • Hi,

    You don't need the USE [DB] statement on your script file (unless you're connecting to several DBs).

    You can pass the default database name with the -d parameter (on both SQLCMD and OSQL tools).

    I don't see any trouble with the insertion of 1000 or 100000 rows from script.

    sqlcmd -S .\MYSQLSERVER2008 -U MyUsr -P MyPwd -i C:\script.sql -d MyDatabase

    Where is this script running? Right on the server? Or on some client?


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Wednesday, October 30, 2013 7:13 PM
  • Hi,

    You don't need the USE [DB] statement on your script file (unless you're connecting to several DBs).

    You can pass the default database name with the -d parameter (on both SQLCMD and OSQL tools).

    I don't see any trouble with the insertion of 1000 or 100000 rows from script.

    sqlcmd -S .\MYSQLSERVER2008 -U MyUsr -P MyPwd -i C:\script.sql -d MyDatabase

    Where is this script running? Right on the server? Or on some client?


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    The script is stored on a client. I am trying to run the SQLCMD on client machine. Will sqlcmd be able to load the query from client machine and execute it in remote sql server ?

    Wednesday, October 30, 2013 7:20 PM
  • Hi,

    -S stands for server\instance.

    So, unless your database is running locally on your own PC, you should provide both server name and instance

    like in -S MyServer\MyInstance.

    -i is the script path, and it's local to the client

    So -i c:\MyDatabase is a c:\MyDatabase folder on the same PC where you launch your SQLCMD script,

    not a C:\MyDatabase folder on the SQL box.

    So, SQLCMD -S .\MYSQLSERVER2008 -U MyUsr -P MyPwd -i C:\Database\xyz.sql -d MyDB will

    1) Connect to a local instance MYSQLSERVER2008 with those credentials and default database

    2) Open xyz.sql script on local folder c:\Database

    3) Execute it sequentially


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Wednesday, October 30, 2013 7:27 PM
  • Hi,

    -S stands for server\instance.

    So, unless your database is running locally on your own PC, you should provide both server name and instance

    like in -S MyServer\MyInstance.

    -i is the script path, and it's local to the client

    So -i c:\MyDatabase is a c:\MyDatabase folder on the same PC where you launch your SQLCMD script,

    not a C:\MyDatabase folder on the SQL box.

    So, SQLCMD -S .\MYSQLSERVER2008 -U MyUsr -P MyPwd -i C:\Database\xyz.sql -d MyDB will

    1) Connect to a local instance MYSQLSERVER2008 with those credentials and default database

    2) Open xyz.sql script on local folder c:\Database

    3) Execute it sequentially


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Thanks. I understood. In step 1)  you meant local instance or remote instance ? How do you access a remote server with sqlcmd ?

    Wednesday, October 30, 2013 7:31 PM
  • Hi,

    -S .\MyInstance = local instance (on your own PC)

    -S MyBox\MyInstance = remote instance (on a server somewhere in your LAN)

    It's perfectly valid to use IP or FQDNs instead of short machine name

    SQLCMD -S 192.168.0.4\Carrots is fine

    SQLCMD -S bugsbunny.acme.com\Carrots is OK as well

    If your instance runs on a fixed port (let's say 1234) you can specify it and skip the instance name part

    SQLCMD -S bugsbunny,1234

    If you have no instances other than default one, then just use the server name, IP or FQDN

    SQLCMD -S bugsbunny


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Wednesday, October 30, 2013 7:37 PM
  • Hi,

    -S .\MyInstance = local instance (on your own PC)

    -S MyBox\MyInstance = remote instance (on a server somewhere in your LAN)

    It's perfectly valid to use IP or FQDNs instead of short machine name

    SQLCMD -S 192.168.0.4\Carrots is fine

    SQLCMD -S bugsbunny.acme.com\Carrots is OK as well

    If your instance runs on a fixed port (let's say 1234) you can specify it and skip the instance name part

    SQLCMD -S bugsbunny,1234

    If you have no instances other than default one, then just use the server name, IP or FQDN

    SQLCMD -S bugsbunny


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    How do I find out the MyBox in MyBox\MyInstance ? MyBox has a name and not IP address (Security reasons ???) . Also, how do I find out the port on which the instance is running ? I don't have direct access to see the port (ie via configuration manager). 

    Wednesday, October 30, 2013 7:56 PM
  • Hi,

    The port number is optional, you can either use the server\instance notation or the server,port one.

    In some cases, you're provided with an instance name, in others just a port.

    Finally, MyBox stands for the server hostname or IP or FQDN, you should know one of those three to connect.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Wednesday, October 30, 2013 7:58 PM
  • It seems that you can connect to your SQL Server or instance when you are sitting on the server locally. That is the reason that the (.) dot notation works, that is .\MYSQLSERVER2008. Maybe you are trying to run the same command from another machine. At that time you'll need to mention the <server name>\MYSQLSERVER2008

    Also, the sql file path c:\database\hugescript.sql will be recognized only when you are on the server locally. It will not work when you run SQLCMD from another machine because the c:\database folder may not exist on the other machine. You can try using UNC (Universal Naming Convention) name for the folder, that is 

    \\<machine with the script>\database\hugescript.sql

    For this to work, you might have to share the folder on the network. Right-click on the c:\database folder > Properties > Sharing tab


    - Aalam | (Blog)

    Wednesday, October 30, 2013 8:10 PM