question

sqlrookie avatar image
sqlrookie asked

Xp_cmdshell throw error with hyphen in server name

Hi, Please help me to resolve the issue. I am using BCP OUT to export data into text file. My server name has - (hyphen) like SERVER-NAME. BCP out is not recognising - (hyphen) and thows error. I tried double quotes like "SERVER-NAME" also [SERVER-NAME], Still i am getting error. Please suggest me how to deal with it.
xp_cmdshell
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

can you confirm you have read this https://msdn.microsoft.com/en-GB/library/ms162802.aspx ? Please post the errors you are getting with [ ] as that is what i would expect to have to use
0 Likes 0 ·
I am getting below error when i use [].Here is the script: declare @sql nvarchar(4000) declare @servername nvarchar(50) set @sql = N'BCP "SELECT * FROM dbo.Table_Name" QUERYOUT C:\Folder\ Table_Name.txt -c -T -S [SERVER-NAME]' Error: --------- SQLState = 08001, NativeError = 53 Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [53]. SQLState = 08001, NativeError = 53 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 config ured to allow remote connections. For more information see SQL Server Books Online. SQLState = S1T00, NativeError = 0 Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired NULL
0 Likes 0 ·
This might sound stupid, but have you confirmed that the server is indeed up and functioning as you would expect? Can you connect to it via SSMS?
0 Likes 0 ·
This web site runs based on your votes. Please indicate all the helpful answers below by clicking on the thumbs up next to them. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
raadee avatar image
raadee answered
I am guessing that you do not have Named Pipes enabled on the remote server or there is a firewall issue regarding Named Pipes. In the error message: "Named Pipes Provider: Could not open a connection to SQL Server [53]" So you probably need to access your server with TCP/IP. Like JohnM said, try it with SSMS (click options, connection properties tab, and select TCP/IP in the network protocol drop down list) to make sure you are trying via TCP/IP.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nice catch!!
0 Likes 0 ·
Hi, went through the steps which you have mentioned, but still i am getting the same error. Named pipes is also enabled on the server.
0 Likes 0 ·
KenJ avatar image
KenJ answered
Let's break this down into its pieces. Presumably, you debugged the BCP command from the cmd shell before building it up in SQL, so let's start there. From a cmd prompt, let's make sure you can actually reach your server... ping server-name didn't work? you can't get there from here - you'll have to find out why (dns/subnet/firewall/etc). Maybe your workstation isn't supposed to have access. If that is the case, move on to the server section (just re-run this on the server where you ran the SQL script from the question) worked? good start. still from the cmd prompt, let's confirm SQL access (no useful query, just enough to verify connectivity - double quotes are okay around the server name but brackets are not, just like BCP): sqlcmd -Q "select @@servername" -E -S "server-name" didn't work? Try SSMS (tcp connections are enabled on the server - **requires restart**? firewall? subnet? vlan?) worked? next step. still from the cmd prompt, let's confirm BCP access. bcp "select @@servername" QUERYOUT "c:\temp\ servername.txt" -c -T -S "server-name" didn't work but sqlcmd did? Not sure where to go from here. worked? okay, basic connectivity is verified from **your machine**. let's move on to the server... Open a remote desktop session to the server where you are running the `set @sql = N'BCP "SELECT * FROM...` script and perform the ping, sqlcmd and bcp steps from a cmd prompt on that machine. If everything worked from a cmd prompt on the server, we can move on to xp_cmdshell. I would still take the same basic approach: declare @pingCmd nvarchar(4000); set @pingCmd = N'ping "server-name"' exec master..xp_cmdshell @pingCmd; declare @sqlCmd nvarchar(4000); set @sqlCmd = N'sqlcmd -Q "select @@servername" -E -S "server-name"'; exec master..xp_cmdshell @sqlCmd; declare @bcpCmd nvarchar(4000); set @bcpCmd = N'bcp "select @@servername" QUERYOUT "c:\temp\ servername.txt" -c -T -S "server-name"'; exec master..xp_cmdshell @bcpCmd; One of these steps probably broke along the way. If not, add your query back in declare @queryCmd nvarchar(4000); set @queryCmd = N'bcp "select * from dbo.table_name" QUERYOUT "c:\temp\ table_name.txt" -c -T -S "server-name"'; exec master..xp_cmdshell @queryCmd; And, finally, the full output file path declare @fullQueryCmd nvarchar(4000); set @fullQueryCmd = N'bcp "select * from dbo.table_name" QUERYOUT "c:\folder\ table_name.txt" -c -T -S "server-name"'; exec master..xp_cmdshell @fullQueryCmd;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.