x

Run Stored Procedure at Startup

I am trying to run a stored procedure at startup of the SQL instance. I created the proc in the Master database and it contains linked servers. I have setup the procedure to run at startup but when the instance restarts it does not run.

I have queried xp_readerrorlog and see the message that the procedure launched

Launched startup procedure 'TestProc'.

When I run the procedure manually it runs fine and when I set it up in a SQL Agent Job to run at startup it runs fine. Why would I not be able to get it to run at startup?

I also created a simple proc that inserts the time into a table on startup and that works fine so I know my setup is correct and it has something to do with the linked servers.

more ▼

asked Jan 07, 2015 at 07:17 PM in Default

avatar image

sqlLearner 1
972 43 51 57

What kind of error handling do you have in the procedure? If you have a TRY/CATCH-block - what's happening in the catch block?

Jan 08, 2015 at 10:06 AM Magnus Ahlkvist

I will need to add in more error handling. Hopefully it will write to the errorlog though. The Proc runs fine when I run it manually.

Jan 08, 2015 at 03:47 PM sqlLearner 1

In order to simplify the issue I created a separate proc that does a simple insert to a table on the current server from the information it pulls from a table on the linked server. I included a TRY/CATCH but it did not write anything to the error log. Once again the proc ran fine manually or within a sql agent job at startup. I am beginning to believe you can not use linked server in a proc at startup. The reason for my hesitation to just include it in a SQL agent job is because the proc adjusts memory in a failover situation, so in the event the SQL Server Agent does not start I would have to run the proc manually to adjust memory to avoiding maxing out memory on the server. Maybe I am just overthinking the risk of the SQL Server Agent not starting up.

Jan 08, 2015 at 06:20 PM sqlLearner 1

Did you read the link in my answer? The problem you are having is most likely because the linked servers are not available when the master database is first recovered (and your procedure executes).

I think you should be fine with an agent job. I suppose you could add a procedure to master that logs an error and/or sends an email if agent doesn't start. You could have it wait for 5 minutes and then use "EXEC xp_servicecontrol 'querystate', 'SQLSERVERAGENT';".

Jan 08, 2015 at 06:51 PM Tom Staab ♦

Thank you for your time and the link it was helpful.

Jan 08, 2015 at 09:56 PM sqlLearner 1
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The master database is recovered before the instance is made available on the network. Therefore, your startup procedure in master is running before the linked server is available. I am not sure why your procedure didn't log any error, however.

I did a little searching and found this summary of the startup process.

more ▼

answered Jan 07, 2015 at 07:38 PM

avatar image

Tom Staab ♦
14.5k 7 14 20

I was getting this error originally: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Now once I set them on I am no longer getting any errors but the proc is not running successfully.

Jan 07, 2015 at 08:29 PM sqlLearner 1

What is the timeout on your linked server? Is it possible the procedure is stuck waiting for a connection because it is running before the linked server is available?

Jan 07, 2015 at 08:48 PM Tom Staab ♦

I set remote login timeout (s) to 100 as a test and they didn't help. Remote query timeout is set to 600.

I also set linked server connection timeout to 600 and query timeout to 600.

Jan 07, 2015 at 09:47 PM sqlLearner 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x471
x372
x140
x3

asked: Jan 07, 2015 at 07:17 PM

Seen: 265 times

Last Updated: Jan 08, 2015 at 09:56 PM

Copyright 2016 Redgate Software. Privacy Policy