May 21, 2018 at 12:19 pm
Hi not sure if this is the correct section but I've got to start somewhere, I've got a general SQL question , I have SQL 2008 running on a large data base collecting live data .
What I'm wanting to do is have a query run every minuet to check a set of values in the data base its looking for the value to equal 192 if the value is not 192 on the rows returned I then want it to start a dbmail relevant to the row .
Is this even possible or am I coming at this from the wrong angle .
Any help would be greatly appreciated
May 21, 2018 at 12:29 pm
Post Table DDL and sample data please
May 21, 2018 at 12:35 pm
jeremy.taylor - Monday, May 21, 2018 12:19 PMHi not sure if this is the correct section but I've got to start somewhere, I've got a general SQL question , I have SQL 2008 running on a large data base collecting live data .
What I'm wanting to do is have a query run every minuet to check a set of values in the data base its looking for the value to equal 192 if the value is not 192 on the rows returned I then want it to start a dbmail relevant to the row .
Is this even possible or am I coming at this from the wrong angle .Any help would be greatly appreciated
You could write a stored procedure to do the part of "check a set of values in the data base its looking for the value to equal 192 if the value is not 192 on the rows returned I then want it to start a dbmail relevant to the row". Not sure what the last part is but if you are going to send mail based on the value <> 192, just add the sp_send_dbmail to the stored procedure. So work on a stored procedure that does what you want and put that in a job scheduled to run every minute.
Sue
May 21, 2018 at 12:36 pm
Hi Sorry your going to have to dumb it down for me a bit here whats a table DDL?
May 21, 2018 at 12:42 pm
Thanks Sue I know what I need to be looking at now many thanks
May 21, 2018 at 12:45 pm
jeremy.taylor - Monday, May 21, 2018 12:36 PMHi Sorry your going to have to dumb it down for me a bit here whats a table DDL?
Table DDL is the CREATE TABLE statement used to create the table.
May 21, 2018 at 12:53 pm
I doubt I could find that the data base wasn't created by me and its huge
May 21, 2018 at 2:10 pm
create a variable
write a query to populate it
write if statement if it equals doesn't equal 192 then Sp_send_dbmail with whatever you want to email
maybe put the whole thing into a cursor if you need to check it row by row
you can also write a powershell script with a foreach loop to do this, or send it off to some C# guru
May 21, 2018 at 2:18 pm
It's definitely possible to do. You'll most likely need an index on the table to support the lookup. You might be able to use a filtered index, but I can't tell for sure from what you've posted so far. We need more details to give you a more specific and detailed response.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 21, 2018 at 3:18 pm
DDL = Data Definition Language. It includes CREATE, ALTER and DROP statements.
DML = Data Manipulation Language. It includes DELETE, INSERT, SELECT, UPDATE and MERGE statements.
CREATE TABLE MyTable(FirstColumn int not null primary key, SecondColumn varchar(100)); --< This is Table DDL
GO
INSERT INTO MyTable
VALUES ( 1, 'One'), ( 2, 'Two' );
In SQL Management Studio if you right click a table and select script as create>new query window, it will script the table DDL. Then you can write the insert(s) with sample data.
May 22, 2018 at 7:52 am
Ahh I see
Tried that this is what I get
USE [Runtime]
GO
/****** Object: View [dbo].[v_Live] Script Date: 05/22/2018 13:22:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
create view [dbo].[v_Live] AS
select * from INSQL.Runtime.dbo.Live;
GO
May 22, 2018 at 8:08 am
jeremy.taylor - Tuesday, May 22, 2018 7:52 AMAhh I seeTried that this is what I get
USE [Runtime]
GO/****** Object: View [dbo].[v_Live] Script Date: 05/22/2018 13:22:07 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER OFF
GOcreate view [dbo].[v_Live] AS
select * from INSQL.Runtime.dbo.Live;GO
That's nice, so what is actually happening is that you are querying data from a remote (linked) server, pulling back all rows of data. It is also possible that the object on the other end could in fact be another view (even though it looks like views are created with the tbling v_). Running this every minute could be an issue if the data volume is large.
May 22, 2018 at 11:29 am
Thanks 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?
May 22, 2018 at 2:41 pm
Jezz,
The mention of a variable above refers to a table variable for the sample data instead of creating a table.
May 22, 2018 at 4:05 pm
jeremy.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.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply