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.
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 " 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.
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;