sqlcmd error when executed from bat file

Question

sqlcmd error when executed from bat file

davidsalazar01

SSCarpal Tunnel

Points: 4072
More actions
February 27, 2017 at 5:21 pm

#341081

Hi All,
Any ideas why I am getting the following error below? It is being executing in a batch file locally on the server using the sqlcmd syntax -
sqlcmd -Q "select value from sysconfigures where comment = 'c2 audit mode'" -o abc.txt
Error: HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
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.
Thanks,
David
March 1, 2017 at 10:27 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1

Unless you're running this on the server you have to specify a server to connect to.
sqlcmd -S myserver -Q "select value from sysconfigures where comment = 'c2 audit mode'" -o abc.txt

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 2

I am running the batch file on the server from a local drive. Any other recommendations?

Thanks

Joe Torre SSChampion Points: 10248 More actions · Answer 3

Since you're running from your workstation, you must specify the server to connect to regardless of where the script is in the file system. Adding -S Yourservernamehere should resolve the issue.

sgmunson SSC Guru Points: 110640 More actions · Answer 4

davidsalazar01 - Tuesday, February 28, 2017 1:20 PM
I am running the batch file on the server from a local drive. Any other recommendations?
Thanks

You may still need to specify the server name using the -S parameter, and if you have a named instance, include the instance name after localhost and a "\":

sqlcmd -S localhost -Q "select value from sysconfigures where comment = 'c2 audit mode'" -o abc.txt

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 5

Hello,

I've tried executing a batch file with the following commands below while RDP'ed into the server. None of the commands are working and still getting error:

"Error: HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
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.

sqlcmd -Q "select value from sysconfigures where comment = 'c2 audit mode'" -o abc.txt
sqlcmd -S localhost -Q "select value from sysconfigures where comment = 'c2 audit mode'" -o abc.txt
sqlcmd -S servername -Q "select value from sysconfigures where comment = 'c2 audit mode'" -o abc.txt

Any other ideas please let me know.
Thanks

Sue_H SSC Guru Points: 90860 More actions · Answer 6

davidsalazar01 - Wednesday, March 1, 2017 3:28 PM
Hello,
I've tried executing a batch file with the following commands below while RDP'ed into the server. None of the commands are working and still getting error:
"Error: HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
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.
sqlcmd -Q "select value from sysconfigures where comment = 'c2 audit mode'" -o abc.txt
sqlcmd -S localhost -Q "select value from sysconfigures where comment = 'c2 audit mode'" -o abc.txt
sqlcmd -S servername -Q "select value from sysconfigures where comment = 'c2 audit mode'" -o abc.txt
Any other ideas please let me know.
Thanks

Is this a default instance or a named instance? Default port or others? Is the browser running or not?
You could also try forcing it to use TCP IP:
sqlcmd -S tcp:Servername,port
or
sqlcmd -S tcp:Servername\Instancename, port

Port may or may not be necessary depending the questions.

Sue

schleep SSChampion Points: 12585 More actions · Answer 7

Does your server show up in the list when you
sqlcmd -L

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 8

When I try to execute the command, it just hangs. I've even tried updating the hosts file with the ip/server name.

schleep SSChampion Points: 12585 More actions · Answer 9

It takes a while, longer depending on the number of servers you have. About 10 seconds for my ~20 instances.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

I'm pretty sure that you also need to specify the parameter that provides for a "trusted connection".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 11

Jeff Moden - Thursday, March 2, 2017 7:48 PM
I'm pretty sure that you also need to specify the parameter that provides for a "trusted connection".

I'm pretty sure it's the default - I usually don't add it. Probably not a bad idea to include it though. I'm just too lazy to type all that.

Sue

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

Sue_H - Thursday, March 2, 2017 7:59 PM
Jeff Moden - Thursday, March 2, 2017 7:48 PM
I'm pretty sure that you also need to specify the parameter that provides for a "trusted connection".
I'm pretty sure it's the default - I usually don't add it. Probably not a bad idea to include it though. I'm just too lazy to type all that.
Sue

Heh... you're right, Sue. I always include the -E out of habit and never looked it up. BOL clearly states that it's the default. Thanks for the correction.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 13

Jeff Moden - Thursday, March 2, 2017 8:13 PM
Heh... you're right, Sue. I always include the -E out of habit and never looked it up. BOL clearly states that it's the default. Thanks for the correction.

It is the right thing to do and I really got to get into the habit of always including it myself. I just didn't think it was causing the problems.

Sue