Terminating a program if the sql query returns with a data
-
The below query does what I need, but I need a small piece of program to execute or terminate another program if the query returns with a result.
SELECT TOP 100 * FROM table1
WHERE Text LIKE '%fire%'
Order By [TimeInserted] Desc;
The reason for this is, that I have a live emergency map. I also have another program running flyers and presentations on the reception TV screen via a dedicated Windows 10 PC. All works fine, but in case of a certain event, such as fire emergency, I want to close the presentation.exe so the live emergency map instantly will be shown on the TV screen. Any idea how can I achieve this?
-
The obvious question is from where are you running this query?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
- Erland Sommarskog wrote:
The obvious question is from where are you running this query?
From the potential program or method, I am hoping to find via this topic. I confirmed the query by using excel, so all I need is a program that can keep repeating the query all the time and once the result returns, it will kill or execute the selected program.
-
How about this, would this be helpful?
WHILE NOT EXISTS (SELECT * FROM table1 WHERE Text LIKE '%fire%' ) BEGIN
WAITFOR DELAY '00:00:10'
END;
THROW 50000,'Some error message',1It should loop indefinitely every 10 second until a rowset is returned. In that case it will thow an error, and this in turn can be checked for in the client and the appropriate measures taken.
But on the other hand I would argue that this loop should happen in the application itself. That would free the connection from being tied up while doing the wait, for example.
You could create a small stored procedure that returns a boolean output parameter that can be checked by the client application, and if no data is available, i.e. the boolean/bit parameter is false, the client goes to sleep for a number of seconds and then repeats the call. If true is returned, some shutdown code can be executed.
CREATE PROCEDURE dbo.usp_CheckForFire
@Fire bit out
AS
BEGIN
SET NOCOUNT ON;
SELECT @Fire = CASE WHEN EXISTS (SELECT * FROM table1 WHERE Text LIKE '%fire%') THEN 1 ELSE 0 END
END
GO - kaj wrote:
How about this, would this be helpful?
WHILE NOT EXISTS (SELECT * FROM table1 WHERE Text LIKE '%fire%' ) BEGIN
WAITFOR DELAY '00:00:10'
END;
THROW 50000,'Some error message',1It should loop indefinitely every 10 second until a rowset is returned. In that case it will thow an error, and this in turn can be checked for in the client and the appropriate measures taken.
But on the other hand I would argue that this loop should happen in the application itself. That would free the connection from being tied up while doing the wait, for example.
You could create a small stored procedure that returns a boolean output parameter that can be checked by the client application, and if no data is available, i.e. the boolean/bit parameter is false, the client goes to sleep for a number of seconds and then repeats the call. If true is returned, some shutdown code can be executed.
CREATE PROCEDURE dbo.usp_CheckForFire
@Fire bit out
AS
BEGIN
SET NOCOUNT ON;
SELECT @Fire = CASE WHEN EXISTS (SELECT * FROM table1 WHERE Text LIKE '%fire%') THEN 1 ELSE 0 END
END
GODear Kaj,
Sorry, I am completely lost. I do not have the deep programming knowledge you have.
-
From the potential program or method, I am hoping to find via this topic. I confirmed the query by using excel, so all I need is a program that can keep repeating the query all the time and once the result returns, it will kill or execute the selected program.
Well, you would write a program or a script in the language of your choice do this. It is not particularly advanced. If you want us to write the code for you, you need to tell us which environment you are working in.
Then again, I suspect that to get this working well, one needs to know about your presentation program, the live emergency map and some understanding the actual business you are working in. That may be a little too many details to explain in a forum. So it may be more fruitful to find a consultant locally that can help you with this.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
- Erland Sommarskog wrote:
From the potential program or method, I am hoping to find via this topic. I confirmed the query by using excel, so all I need is a program that can keep repeating the query all the time and once the result returns, it will kill or execute the selected program.
Well, you would write a program or a script in the language of your choice do this. It is not particularly advanced. If you want us to write the code for you, you need to tell us which environment you are working in.
Then again, I suspect that to get this working well, one needs to know about your presentation program, the live emergency map and some understanding the actual business you are working in. That may be a little too many details to explain in a forum. So it may be more fruitful to find a consultant locally that can help you with this.
Thank you sir,
I wouldn't mind paying for the below piece of the program. Does it make sense like this?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply