Remote Stored Procedures
-
I am trying to implement a simple log shipping setup between two SQl2K servers. The log shipping works fine, but I am running into problems shipping the whole database programmatically. I have two stored procedures, one to backup the database on the primary server, which then calls another remotely to restore it on the secondary.
Herein lies the problem: when I run this as a SQL agent job, the database backs up, the remote procedure fires and restores the db on the secondary, but it stays in a 'Loading' state despite the 'WITH STANDBY' clause in the procedure. When I run the remote procedure manually from the Query Analyzer, it goes into the 'Read-only' state as expected.
Any ideas?
-
Update:
When I said 'When I run the remote procedure manually from the Query Analyzer,' I was connected to the secondary. When I tried remote execution while connected to the primary, I got '[OLE/DB provider returned message: Timeout expired].' Where is this timeout value specified?
-
Found it. The server properties, Connections tab specifies a remote query timeout. This value can be overridden on the linked server properties Server Options tab.
Don't ya just love it when some RsTFM and figures it out on their own?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply