March 21, 2010 at 10:54 am
Good morning. I hope someone can help me.
I am running into a problem executing the following query within a job in SQL Server Management Studio.
DROP TABLE xyz..xyz_BatchProcessRuntime
select * into xyz..xyz_BatchProcessRuntime FROM [xx.xxx.xx.xxx].xyz.dbo.xyz_BatchProcessRuntime go
It executes for about an hour and then comes back with this error:
Executed as user: xxx\svc_xxxxx. Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000] (Error 0) Cannot drop the table xyz..xyz_BatchProcessRuntime', because it does not exist or you do not have permission. [SQLSTATE 42S02] (Error 3701). The step failed.
Why does it take an hour or more to come back and tell me that it can't drop the table?
I think that the remote database is reachable because other lines in the job that reference it work fine. When I try to connect to the remote machine to check permissions, I get no response from ping, RDC, or SSMS. Could there be a firewall rule in place that only allows one sort of traffic to connect to the remote database?
Is there a way that I can track down the problem with the above error?
March 21, 2010 at 4:17 pm
I wish that there was some better logging going on. I've been looking at the history of this job which has failed every day for the last two weeks. Prior to that, it worked fine.
Here are the errors that are reported when there is a failure. There is a different one practically every day.
Day 1 of failure:
Executed as user: xxx\svc_xxxxx. TCP Provider: An existing connection was forcibly closed by the remote host. [SQLSTATE 42000] (Error 10054) OLE DB provider "SQLNCLI10" for linked server "xx.xxx.xx.xxx" returned message "Protocol error in TDS stream". [SQLSTATE 01000] (Error 7412) OLE DB provider "SQLNCLI10" for linked server "xx.xxx.xx.xxx" returned message "Protocol error in TDS stream". [SQLSTATE 01000] (Error 7412) OLE DB provider "SQLNCLI10" for linked server "xx.xxx.xx.xxx" returned message "Protocol error in TDS stream". [SQLSTATE 01000] (Error 7412) OLE DB provider "SQLNCLI10" for linked server "xx.xxx.xx.xxx" returned message "Communication link failure". [SQLSTATE 01000] (Error 7412). The step failed.
Day 2:
Executed as user: xxx\svc_xxxxx. Cannot drop the table xyz..xyz_name', because it does not exist or you do not have permission. [SQLSTATE 42S02] (Error 3701). The step failed.
Day 3:
Same as day 2
Day 4:
Executed as user: xxx\svc_xxxxx. Cannot fetch a row from OLE DB provider "SQLNCLI10" for linked server "xx.xxx.xx.xxx". [SQLSTATE 42000] (Error 7330) OLE DB provider "SQLNCLI10" for linked server "xx.xxx.xx.xxx" returned message "Query timeout expired". [SQLSTATE 01000] (Error 7412). The step failed.
Day 5:
Same as day 2
Day 6:
Executed as user: xxx\svc_xxxxx. Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000] (Error 0). The step failed.
Day 7-12 has one of the above errors each day.
Any ideas where the problem started and how I can fix it?
April 19, 2013 at 8:34 am
any idea abt the solutions..
April 19, 2013 at 9:56 am
This link may be useful, perhaps the problem is already resolved.
http://msdn.microsoft.com/en-us/library/ms189580(v=sql.105).aspx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply