May 22, 2018 at 4:36 pm
Lynn Pettis - Tuesday, May 22, 2018 4:05 PMjeremy.taylor - Tuesday, May 22, 2018 11:29 AMThanks I've got a query that returns the data I require:SELECT OPCQuality,
FROM Live
WHERE TagName IN ('Work-Shop-Email-Test')So now your saying I need to create a variable to hold the data (OPCQuality) and then test that variable with an if statement sounds easy any chance of a quick example?
Just so you know, since you are actually query data over a linked server, ALL the data will come across the network and then be filtered on the local system.
If you query that view only if there are multiple tables joined using the 4 part naming convention will the criteria be applied locally. With a single table being accessed by the view, like this case, the where clause is passed and only filtered data is returned across the network. In this case it shouldn't behave poorly as long as there is a covering index.
In this example:
SELECT *
FROM LS.DB.SCH.TBL1 t1
JOIN LS.DB.SCH.TBL2 t2
WHERE t1.C2=192;
ALL the data comes back from the 2 tables and the criterial is applied locally.
With one table (or a view querying 1 table)
SELECT *
FROM LS.DB.SCH.TBL1 t1
WHERE t1.C2=192;
or
SELECT * FROM OpenQuery(LS, 'SELECT *
FROM LS.DB.SCH.TBL1 t1
JOIN LS.DB.SCH.TBL2 t2
WHERE t1.C2=192') o;
The criteria is applied on the linked server.
May 22, 2018 at 4:43 pm
Joe Torre - Tuesday, May 22, 2018 4:36 PMLynn Pettis - Tuesday, May 22, 2018 4:05 PMjeremy.taylor - Tuesday, May 22, 2018 11:29 AMThanks I've got a query that returns the data I require:SELECT OPCQuality,
FROM Live
WHERE TagName IN ('Work-Shop-Email-Test')So now your saying I need to create a variable to hold the data (OPCQuality) and then test that variable with an if statement sounds easy any chance of a quick example?
Just so you know, since you are actually query data over a linked server, ALL the data will come across the network and then be filtered on the local system.
If you query that view only if there are multiple tables joined using the 4 part naming convention will the criteria be applied locally. With a single table being accessed by the view, like this case, the where clause is passed and only filtered data is returned across the network. In this case it shouldn't behave poorly as long as there is a covering index.
In this example:
SELECT *
FROM LS.DB.SCH.TBL1 t1
JOIN LS.DB.SCH.TBL2 t2
WHERE t1.C2=192;
ALL the data comes back from the 2 tables and the criterial is applied locally.
With one table (or a view querying 1 table)
SELECT *
FROM LS.DB.SCH.TBL1 t1
WHERE t1.C2=192;
or
SELECT * FROM OpenQuery(LS, 'SELECT *
FROM LS.DB.SCH.TBL1 t1
JOIN LS.DB.SCH.TBL2 t2
WHERE t1.C2=192') o;The criteria is applied on the linked server.
Well, thank you for the insight. I haven't fully tested this, but when using a linked server, I tend to use OPENQUERY whether I am querying a single table or multiple tables. Sometimes that also means using dynamic SQL. Having run into the problem before I don't chance it. I would rather pull the data I need, putting it in a local table, then using it from there.
May 23, 2018 at 8:31 am
That's it you've officially lost me now this is SQL way over my head LOL
This Query
SELECT OPCQuality,
FROM Live
WHERE TagName IN ('Work-Shop-Email-Test')
Returns a Table with 1 row and 1 column which contains a value ,if that value is not 192 I then want to run
Set Quoted_Identifier OFF
exec msdb.dbo.sp_send_dbmail @profile_name="SQL Mail",
@recipients ="jeremy.taylor@mail.com",
@body = "OPC Quality lost"
I'm not seeing how to trigger it using your Query's.
May 23, 2018 at 9:06 am
I'm confused about whether you're using a linked server or not. But try something like this. If you are indeed using a linked server, make sure you understand about linked servers and OPENQUERY before you go live with it - you'll be the one supporting it, after all. Put it in a job if you need to run the check every minute.
IF (
SELECT OPCQuality
FROM OpenQuery(INSQL,
'SELECT OPCQuality
FROM Runtime.dbo.Live
WHERE TagName = ''Work-Shop-Email-Test'''
)
) = 192
BEGIN
SET QUOTED_IDENTIFIER OFF;
EXEC msdb.dbo.sp_send_dbmail
@profile_name="SQL Mail"
, @recipients ="jeremy.taylor@mail.com"
, @body = "OPC Quality lost";
END;
John
May 23, 2018 at 11:33 am
Cheers that worked perfectly I'll run some check s now see if any thing breaks.
A big thanks to everyone who's helped ,thanks for you support.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply