May 18, 2008 at 10:41 am
Here is a better version, forgot a couple of things:
ALTER PROCEDURE dbo.TestProcedure
AS
DECLARE @objectid int;
DECLARE @sessionid smallint;
-- Get the object id for this procedure
SELECT @objectid = objectid
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE session_id = @@spid;
SELECT @objectid;
SELECT @sessionid = session_id
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE a.objectid = @objectid
AND session_id <> @@spid;
SELECT @sessionid;
IF (@sessionid IS NOT NULL)
BEGIN;
RAISERROR('Process is already running - exiting...', -1, -1) WITH NOWAIT;
RETURN;
END;
RAISERROR('Process is not running - starting...', -1, -1) WITH NOWAIT;
WAITFOR DELAY '00:00:30';
Go
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 18, 2008 at 12:09 pm
Well, after thinking about this a little - I remembered something that simplifies this a little. Here is the updated script:
ALTER PROCEDURE dbo.TestProcedure
AS
DECLARE @sessionid smallint;
SELECT @sessionid = session_id
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE a.objectid = @@procid
AND session_id <> @@spid;
IF (@sessionid IS NOT NULL)
BEGIN;
RAISERROR('Process is already running - exiting...', -1, -1) WITH NOWAIT;
RETURN;
END;
RAISERROR('Process is not running - starting...', -1, -1) WITH NOWAIT;
WAITFOR DELAY '00:00:30';
Go
The variable @@procid has the object id of the current Transact-SQL module, so we don't need to try and find it. If you want the name of the procdure - it is simply a call to object_name. For example:
SELECT object_name(@@procid);
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 18, 2008 at 1:19 pm
rbarryyoung (5/17/2008)
Service Broker only works on SQL Express when it is connected to another SQL Server with a full license to Service Broker.
SQL Server 2005 Features Comparison
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
states :
Service Broker
See comments
SQL Server Express can exchange messages with other editions. Messages between instances of SQL Server Express must be routed through another edition.
Since I understand the only need is within the instance, it should work.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 18, 2008 at 2:18 pm
Hi,
Actually you may try to use Message Queueing system. The task will be to send MSMQ message in one procedure, and receive it in another. I know that outside SQL MSMQ may generate event which can fire some procedure on message arrival, and this works like charm, but actually never tried to receive message inside SQL.
There is a set of stored procedures sp_OA* and MSMQ automation control, which may be used for doing the job.
Message Queueing system is a part of W2k3/XP/Vista which is not installed by default, you need to add this Windows component. This is totally separate from Service Broker, but idea is very similar - messages sent are queued inside the system and delivered asynchronously to the reciever.
Regards,
Slawek
May 18, 2008 at 4:32 pm
ALZDBA (5/18/2008)
rbarryyoung (5/17/2008)
Service Broker only works on SQL Express when it is connected to another SQL Server with a full license to Service Broker.SQL Server 2005 Features Comparison
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
states :
Service Broker
See comments
SQL Server Express can exchange messages with other editions. Messages between instances of SQL Server Express must be routed through another edition.
Since I understand the only need is within the instance, it should work.
ALZDBA, unfortunately, what that footnote is describing is all that Express Edition can do. It cannot exchange messages with Express Edtion instances, including itself. It can only work through an active connection to a different edition.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 19, 2008 at 7:10 am
rbarryyoung (5/18/2008)
ALZDBA, unfortunately, what that footnote is describing is all that Express Edition can do. It cannot exchange messages with Express Edtion instances, including itself. It can only work through an active connection to a different edition.
Now that's a bummer with SQLExpress :blink::ermm:
I haven't got one to test it and maybe it's time to install an instance to play with 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 19, 2008 at 9:02 am
I have not had a chance to test it either, I am just taking it from conversations with MS PSS. I would love it if you could prove me wrong!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 20, 2008 at 7:39 am
This works:
I took the SSquared recommendation to use sp_procoption (starts when master db initialises) and configured SQL Express to start my procedure on start-up:
EXEC master.dbo.sp_procoption @ProcName = 'recDataControl_Loop', @OptionName = 'startup', @OptionValue = 'true'
sp_configure 'show advanced options', 1
RECONFIGURE
sp_configure
NOTE: Here you have to create the procedure in master db - redirects don't work.
I then added a very simple check in the procedure(s) that run continually to check this start-up procedure is still running. If it has stopped I issue a cmdshell command to stop and restart SQL. I am still looking for a more elegant option here (to restart SQL from within the procedure):
IF EXISTS (Select TOP 1 * FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE object_name(a.objectid) = 'recDataControl_Loop')
BEGIN;
RAISERROR('Process is already running - exiting...', -1, -1) WITH NOWAIT;
RETURN;
END;
ELSE
Restart SQL
When SQL Restarts the procedure will restart.
To maintain some sort of control I am now going to add a few message flags so I receive an email when the SQL service is restarted - in case I get some bad code and it ends up in a restart loop.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply