SQL Query potential

  • 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

  • Post Table DDL and sample data please

  • jeremy.taylor - Monday, May 21, 2018 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

    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

  • Hi Sorry your going to have to dumb it down for me a bit here whats a table DDL?

  • Thanks Sue I know what I need to be looking at now many thanks

  • jeremy.taylor - Monday, May 21, 2018 12:36 PM

    Hi 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.

  • I doubt I could find that the data base wasn't created by me and its huge

  • 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

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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.

  • 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

  • jeremy.taylor - Tuesday, May 22, 2018 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

    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.

  • 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?

  • Jezz,
    The mention of a variable above refers to a table variable for the sample data instead of creating a table.

  • jeremy.taylor - Tuesday, May 22, 2018 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?

    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 20 total)

You must be logged in to reply to this topic. Login to reply