CODEWITHSUNDEEP
sql-serverstored-proceduresopenquery
tesfahun
tesfahun

Reputation: 13

sp_procoption is not executing stored procedure with OPENQUERY

Using sp_procoption it successfully execute a stored procedure "MyBackgroundTask"

   sp_procoption    @ProcName = 'MyBackgroundTask',
            @OptionName = 'startup',
            @OptionValue = 'on'

on master db which intern calls a stored procedure in my database 

   use master 

   CREATE PROCEDURE MyBackgroundTask
   AS
   BEGIN

     /*
     .
     .
     .

     */

     execute [MyDatabaseName].[dbo].[MyDatabaseStoredProcedure]; 

   END;

This works fine except when MyDatabseStoredProcedure contains a query on linked server 

  Insert Into [LOCALTABLE] (id, name)
  Select    id, name
  from OPENQUERY(LINKED_SERVER, 'SELECT * FROM RemoteDB.RemoteTable')

Why is that the above lines of code is not working?

reference

Upvotes: 1

Views: 551

Answers (1)

Ionic
Ionic

Reputation: 3935

This is just simple. The startup procedure is triggered before the connection to the linked servers is established. Therefore you cannot use a linked server in stored procedure which is used as a startup procedure.

One suggestion could be to generate a SQL Server Agent Job which will be generated by your procedure and has a small wait timer. It will execute a few seconds/minutes after the start and delete it self after execution. This may be a solution, but it may fail if your Agent won't come up after the restart.

Upvotes: 1

Related Questions