xp_cmdshell

[SpiderFlight]

I am trying to use the extended SP of xp_cmdshell . However when try to save my store procedure I receive the following message:

[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
ODBC: Msg 0, Level 16, State 1
Communication link failure

Connection Broken

Doing the same process on the server itself is successful. I suspect I have a corrupt file somewhere. I have reinstalled all service packs and still have the same issue. Any insight would be appreciated.

 

[Catadmin]

I take it you're trying to save this procedure via PC client tools which are connected to a remote server, then?

If so, a couple of questions:

Have you tried removing & reinstalling the client tools on the PC?

What version of SQL Server are you using?

When you say you saved it directly on the server, are you saying you walked over to the box and typed it in on that server's client tools or that you connected to the server via Remote Desktop?


I've personally seen that error when we've had problems with a NIC card or the wire going to the NIC. In the wire case, a tech accidently pulled the wrong wire in the middle of the day and "hastily" put it back in, but several of us lost connections on and off for about 10 minutes during that incident. In the NIC card problem, our card was going bad and was dropping packets when communicating with the server.

Go to Start -> Run -> CMD on your PC and ping the server. See how many, if any, packets drop. Also run TraceRt to see if anything funny is going on with the hops. If either is true, contact your network people.

I've also, occasionally, seen that error when Connection Pooling on the server isn't working or when all the connections have been hogged up by a serious resource-hog of a query. Usually, though, the problem is network related, not SQL related. See if your network tech can't put a sniffer on the server &/or the PC to figure out what the problem is.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SpiderFlight]

This is a SQL2000 server. I have tried removing & reinstalling the client tools. If connected to the server via pcAnywhere and I enter in the code everything is good.

If connected thru Enterprise Manger and I remove the line containing xp_cmdshell I can save the stored procedure. If I place it in the SP it generates the error. Also, if I try running the code thru Query Analyzer I get the same error. If I remove the xp_cmdShell and parse the query no error. Put it back I get the error. (Strange behavior)

I'm not ruling out a network issue but why would this problem only occur when the xp_cmdshell is present? Below is a portion of my code and further below the results of my ping and trace.

code:
.
.
.
SET @FileName1 = @Location + '\' + Cast(@HospToRptID as varchar(10) ) + @HospAbbr + '_PDIINFWOA.rpt'
SET @errlog = @Location + '\' + Cast(@HospToRptID as varchar(10) ) + @HospAbbr + '_bcpError.log'
SET @pw = 'PEPSI'
SET @bcpCommand = '\\SDCCASEMIX01\VBMedStatSPR\BCP_sp_VBMedStat_QryExtract.bat ' + @FileName1 + ' ' + @errlog + ' ' + @pw + ' ' + @Server

EXECUTE master..xp_cmdshell @bcpCommand




Results:
C:\>ping sdccasemix01

Pinging SDCCASEMIX01.corp.ad.sentara.com [163.230.1.72] with 32 bytes of data:

Reply from 163.230.1.72: bytes=32 time=1ms TTL=124
Reply from 163.230.1.72: bytes=32 time=1ms TTL=124
Reply from 163.230.1.72: bytes=32 time=1ms TTL=124
Reply from 163.230.1.72: bytes=32 time=1ms TTL=124

Ping statistics for 163.230.1.72:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 1ms, Maximum = 1ms, Average = 1ms

C:\>tracert sdccasemix01

Tracing route to SDCCASEMIX01.corp.ad.sentara.com [163.230.1.72]
over a maximum of 30 hops:

1 <10 ms <10 ms 2 ms 10.127.17.2
2 1 ms 1 ms 1 ms 10.127.0.13
3 14 ms 21 ms 22 ms 10.101.4.1
4 1 ms 1 ms 1 ms 10.101.14.14
5 3 ms 3 ms 9 ms sdccasemix01.corp.ad.sentara.com [163.230.1.72]

Trace complete.

 

[Catadmin]

Hrm. If this was SQL Server 2005, I'd say xp_cmdshell might be disabled. But since you're using 2000, that can't possibly be the issue...

Are you sure the job running the proc is part of the Sysadmin fixed server role or has been granted a specific permission to exec it? Per Books Online:

[/quote BOL]
By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure.

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail.
[/quote]

Another idea is to convert the variables in your last string to varchar. This is NOT test, BTW, but try something like:

SET @bcpCommand =  '\\SDCCASEMIX01\VBMedStatSPR\BCP_sp_VBMedStat_QryExtract.bat ' +  Convert(varchar(200),@FileName1) + ' ' + Convert(varchar(100),@errlog) + ' ' +  @pw + ' ' + @Server

The problem is I don't see where a lot of your variables are being generated, so I can't trouble shoot those. And you're setting several strings with these mysterious variables and then calling them in the final @bcpCommand string. It could be that the string is just too big for an xp_cmdshell command. What's the largest string you have?




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[natedev]

I am experiancing this same issue in sql server 2005 when logged in with my sa account i can run locally (with rdc) with no problems but when i attempt to compile the stored proc i get the Communication link failure error i also tested with osql to eliminate the management studio. xp_cmdshell is enabled on the server and i can run it fine when it's not in a stored proc.