October 22, 2010 at 3:17 pm
I've been given a large DTS package to debug. I've found that xp_cmdshell has just started failing. The error message is:
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
it's a package that runs every monday and from 2 weeks ago to this past monday it seems to have quit. The package runs under an admin account. The directory that it's trying to delete and recreate to has full permissions to everyone, but I've also tried other commands and nothing will work off of xp_cmdshell. I'm not sure what else to look for.
edit:
oh... the SQL ver is 2000, OS is 2003 Server Standard x64
October 27, 2010 at 2:59 am
As the error above, I believe Enterprise Manager uses the msdb.dbo.sp_enum_dtspackages stored procedure to list the packages, so by examining this procedure you should be able to see what is restricting the results.
I of course assume that if you run msdb.dbo.sp_enum_dtspackages directly it doesn't return anything.
1) I would start by calling msdb.dbo.sp_enum_dtspackages, as that is the procedure used under the covers, if memory serves me correctly - it has been a while. If you look at the proc code you'll see the table used, try ad SELECT * FROM msdb.dbo.sysdtspackages as well. Is there any filter in the procedure that restricts your ability to see them?
To try and debug the job, start by checking the DTSRUN command. Is it pointing to the correct place? Running a profiler trace may help check this too, again a stored proc is used to load the package (msdb.dbo.sp_get_dtspackage I think) which should show up in the trace.
2) Is your QA connection valid? The error indicates connection problems, which doesn't make a lot of sense. Can you run a simpler query to check connectivity? SELECT @@VERSION for example?
[font="Verdana"]Regards,
Pritam Salvi
SQL DBA
Mumbai. India[/font]
October 27, 2010 at 7:34 am
I've found that when I use a remote desktop connection to the server xp_cmdshell runs fine. When I use QA from my own workstation it doesn't. All other queries seem to work OK from my workstation
October 27, 2010 at 9:54 pm
Checkout parameters set in sp_configure for these below
remote access
remote login timeout (s)
remote proc trans
remote query timeout (s)
network packet size (B)
[font="Verdana"]Regards,
Pritam Salvi
SQL DBA
Mumbai. India[/font]
October 27, 2010 at 10:04 pm
As well as checkout the services started or not on both the servers.
Make them automatic.
Remote Procedure Call (RPC)
Remote Procedure Call (RPC) Locator
Remote Registry
Secondary Logon
Indexing Service(Dependencies RPC)
Type services.msc in run
[font="Verdana"]Regards,
Pritam Salvi
SQL DBA
Mumbai. India[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply