Execute Proc at Startup Question

Question

Execute Proc at Startup Question

Ed Zann

SSCrazy Eights

Points: 8694
More actions
July 14, 2016 at 12:51 pm

#309196

We are having an issue with our SQL 2014 server where the Linked Server to our AS400 does not connect after a restart. Apparently this is some quirky bug that can be fixed by running the sp_enum_oledb_providers system proc. I want to execute this at SQL startup. I will use the following script:
USE [master]
GO
SET ANSI_NULLS ON
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_enum_oledb_providers]
AS
exec sp_enum_oledb_providers
GO
sp_procoption 'usp_enum_oledb_providers', 'startup', 1
My change management guy is asking what my rollback plan is. I think I would just turn the auto-execution off with sp_procoption. That assumes that SQL starts and such a change can be made. In this case the startup proc is very simple and is calling a Microsoft system proc. It is very unlikely that it would fail. But my question is, what happens if a proc marked for execution at startup fails? Does it interfere with the startup of SQL in general? Or does SQL just report the error and continue on.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Make a sproc that does SELECT 1/0 and mark it as start up and start the instance. Voila, you will see what happens (and what is logged in the error log perhaps) when a startup sproc fails.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Another option is to create a job. One of the schedule options for jobs is "Start Automatically when SQL Agent starts".

-SQLBill

Steve Jones - SSC Editor SSC Guru Points: 736188 More actions · Answer 3

Nothing happens. I created this proc on SQL 2014:

CREATE PROCEDURE mystarer

AS

SELECT 1/0

EXEC sp_procoption 'mystarer', 'startup', 1

I then restarted the instance. After loading XP DLLs, the next entry is "Launched startup procedure mystarer" and that's it. No message, no anything.

If you need any data from your proc, you need to capture it and store it. Nothing in the log, no error, things just keep running.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Steve Jones - SSC Editor (7/14/2016)
Nothing happens. I created this proc on SQL 2014:
CREATE PROCEDURE mystarer
AS
SELECT 1/0
EXEC sp_procoption 'mystarer', 'startup', 1
I then restarted the instance. After loading XP DLLs, the next entry is "Launched startup procedure mystarer" and that's it. No message, no anything.
If you need any data from your proc, you need to capture it and store it. Nothing in the log, no error, things just keep running.

Indeed.

Gotta ding you for not having SET NOCOUNT ON at the top of your sproc Steve. 😛

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Steve Jones - SSC Editor SSC Guru Points: 736188 More actions · Answer 5

It was a test, a test. Sheesh, I didn't even check that one into the VCS :w00t:

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 6

So it's practically a non-event with no impact on the SQL process. Thanks for your replies.