|
Home > Archive > MS SQL Server > January 2006 > Stored Procedure - wont auto start
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Stored Procedure - wont auto start
|
|
|
| Hi,
We have a stored procedure in our SQL 2000 (SP4) installation that is
set to auto start. All the variables are set correctly as per BOL.
However, it NEVER runs automatically. If I log into Query Analyzer and
manually execute the SP, it runs fine. Can anyone give me any pointers
on how I can troubleshoot it ? I've tried setting the debug level on
and all the auditing on, but I still get no errors logged re: this
stored procedure.
Thanks in advance.
Sean Spencer.
| |
|
| Did you use sp_procoption for this ? Could you show us the code which
registers this procedure for startup ?
Jens Suessmeyer.
| |
|
| Hi Jens,
The actual SP was set to autostart via the Enterprise Manager GUI
initially. However, I have also tried:
sp_procoption sqlshield_startup,st
artup,'1'
go
reconfigure
go
Command(s) Completed Sucessfully.
Although it still doesnt start.
| |
| Paul Ibison 2006-01-15, 9:23 am |
| Trace flag 4022 will prevent automatic stored procedures from executing -
worth checking if this is part of your startup parameters (dbcc
tracestatus(-1)). Also, check the server configuration setting to see if
someone has reset the value (sp_configure 'scan for startup procs').
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
| |
|
| Hi Paul,
Tried the dbcc tracestatus - trace not enabled for this connection was
the output. I have checked the sp_configure scan for startup procs, and
it is set to '1'.
Really is baffling me.
| |
| Kalen Delaney 2006-01-15, 11:23 am |
|
Hi Sean
Reconfigure is meaningless here; it is only required after running
sp_configure.
Verify that your proc really is marked for startup:
select objectproperty(objec
t_id('sqlshield_star
tup'), 'ExecIsStartup')
How do you know it isn't running?
--
HTH
Kalen Delaney, SQL Server MVP
www. solidqualitylearning
.com
"Sean" < sean_spencer@blueyon
der.co.uk> wrote in message
news:1137333467.342431.226960@f14g2000cwb.googlegroups.com...
> Hi Jens,
>
> The actual SP was set to autostart via the Enterprise Manager GUI
> initially. However, I have also tried:
>
> sp_procoption sqlshield_startup,st
artup,'1'
> go
> reconfigure
> go
>
> Command(s) Completed Sucessfully.
>
> Although it still doesnt start.
>
>
| |
|
| Hi Kalen,
I've ran the command, and it returns '1' - which I presume means its
set to startuo,
I know it isnt running because the database application fails to
connect to the database if its not running. SQL Shield is encryption
software. (its a .dll).
Regards
Sean
| |
| Kalen Delaney 2006-01-15, 1:23 pm |
|
Sean
The terminology here is bugging me a bit.
A stored procedure in general is just a set of tasks to carry out, and then
it's done. Your comment below makes it sound like the running of the sproc
is an ongoing, continual thing, that has to be in effect for your app to
run. Maybe the sproc is supposed to set something up for the app to use, and
something in the sproc is failing.
My guess, assuming all the other info you've provided is accurate, is that
the sproc is running at startup, but it is not doing what you think it is
doing.
--
HTH
Kalen Delaney, SQL Server MVP
www. solidqualitylearning
.com
"Sean" < sean_spencer@blueyon
der.co.uk> wrote in message
news:1137345845.023530.99720@g49g2000cwa.googlegroups.com...
> Hi Kalen,
>
> I've ran the command, and it returns '1' - which I presume means its
> set to startuo,
>
> I know it isnt running because the database application fails to
> connect to the database if its not running. SQL Shield is encryption
> software. (its a .dll).
>
> Regards
>
> Sean
>
>
| |
| Dan Guzman 2006-01-15, 8:23 pm |
| > I know it isnt running because the database application fails to
> connect to the database if its not running. SQL Shield is encryption
> software. (its a .dll).
Are you saying that this startup proc is supposed to run continuously and
that it invokes a DLL?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sean" < sean_spencer@blueyon
der.co.uk> wrote in message
news:1137345845.023530.99720@g49g2000cwa.googlegroups.com...
> Hi Kalen,
>
> I've ran the command, and it returns '1' - which I presume means its
> set to startuo,
>
> I know it isnt running because the database application fails to
> connect to the database if its not running. SQL Shield is encryption
> software. (its a .dll).
>
> Regards
>
> Sean
>
| |
|
| Hi Dan,
All I know really is that if I type:
exec sqlshield_on
Then the application runs fine and it does so until the SQL server is
stopped and restarted.
(this is invoked by sqlshield_startup (or at least thats what is meant
to happen). sqlshield_on is an Extended Stored Procedure.
sqlshield_startup is a standard Stored Procedure.
| |
|
| Kalen,
If the server is rebooted, I must go into query analyzer and type:
exec sqlshield_on
If I dont, then we cannot connect to our database. Does this run all
the time ? I dont really know. The intention here was just to make sure
sqlshield_on ran on startup so I didnt have to go in and type it each
time I rebooted the server. Once its ran, it doesnt have to be run
again unless we reboot the server or stop\start the SQL instance.
| |
| Kalen Delaney 2006-01-15, 8:23 pm |
|
What if you go into QA and type
exec sqlshield_startup
What happens?
Maybe sqlshield_on is getting executed on startup, but it is failing when it
tries to call sqlshield_on.
Why can't you make sqlshield_on a startup proc?
--
HTH
Kalen Delaney, SQL Server MVP
www. solidqualitylearning
.com
"Sean" < sean_spencer@blueyon
der.co.uk> wrote in message
news:1137354331.519643.77930@g43g2000cwa.googlegroups.com...
> Kalen,
>
> If the server is rebooted, I must go into query analyzer and type:
>
> exec sqlshield_on
>
> If I dont, then we cannot connect to our database. Does this run all
> the time ? I dont really know. The intention here was just to make sure
> sqlshield_on ran on startup so I didnt have to go in and type it each
> time I rebooted the server. Once its ran, it doesnt have to be run
> again unless we reboot the server or stop\start the SQL instance.
>
>
| |
|
| >What if you go into QA and type
>exec sqlshield_startup
>What happens
It runs fine.
| |
|
| ** PROBLEM RESOLVED **
I've fixed it. I tested the SP on another instance I have of SQL server
- it ran fine (autorun as well).
Then I compared the 2 instances of SQL server - the options that were
set (through the Enterprise Manager GUI). The one and only difference
on the instance that was not allowing any Auto Start SP's to run, was
that 'Use Windows NT Fibres' was checked. Why it was checked I dont
really know, but I unchecked this option - restarted SQL, and crazily
enough it works now !!
Thanks all for your input, although your suggestions did not fix my
problem - I have learned from all of you which is just as valuable.
Sean
|
|
|
|
|