Post reply

scheduling a job in sql

  • November 15, 2007 at 8:03 am

    #122445

    I am using studio express and there is no facility to scedule a job, which version of sql do i need and i assume i can run a query as a job.

    will it go as low as 1 second for running a job?

  • November 15, 2007 at 8:26 am

    #751543

    All other versions support job scheduling.

    You are right. You can run a query as a job.

    Minimun 1 minute.

  • November 15, 2007 at 8:40 am

    #751557

    so there is no way of running a query every 1 second from withn sql?

  • November 15, 2007 at 12:50 pm

    #751625

    try using at command and osql at os level if you are on express. just thoughts

  • November 15, 2007 at 9:57 pm

    #751703

    I am not sure what you are trying to achieve.

    But you can use WAITFOR DELAY command and put in While Loop.

    my 2 cents.

  • November 16, 2007 at 8:57 am

    #751878

    I have 1 sending data to excel on a LAN, i then intend to use upto 10 PC's to send queries via excel

    I am unsing it for stockmarket data analysis during market hour so it is in real time.

    At the moment i am send queries via excel to sql, that then i place the data into excel cells for a charting package to read from excel, but i am using sql more and more, and can see the queries per PC getting longer, i wantto do this in the best way possable

    So i was thinking that if sql can handle 1 query on timed loop or schedule a query every 1 second, then the PC that use excel for charting can call the results of that query, there for is faster, as within the current query i am having to find that last record and that is quick but as i add more PC's it will slow down.

    please help any advice

    Jon

  • November 16, 2007 at 8:29 pm

    #752030

    If you script this, you could use some sort of external timer, say in a Windows service. There's no point dropping and re-establishing the connection every second when you know you're going to be executing queries that quickly. Better to check that the connection is still valid then execute the query. If the connection has been dropped, re-establish it and execute the query.

    K. Brian Kelley
    @kbriankelley

  • November 19, 2007 at 8:54 am

    #752331

    Cant SQL execute a query when a new record comes in that woukd be simplist of all

  • November 20, 2007 at 6:07 pm

    #752923

    Yes, via a trigger. However, the trigger is considered part of that transaction so if the trigger fails and is rolled back, so is the original query.

    K. Brian Kelley
    @kbriankelley

  • November 21, 2007 at 8:42 am

    #753138

    I think that would be ok in this instance, any details you know of to guide me through setting one up

    I would like to produce a table from it, that only has one record so it may have to be updated rather than adding a new record every time a trigger fires

    That would be then very simple for excel to call the new table, I would only have to draw of 1 record rather than find the last record like I have to fo now

    JP

  • November 23, 2007 at 9:04 am

    #753658

    In Books Online there is the CREATE TRIGGER topic. You'll be wanting the AFTER triggers (which fire after the query operation and do not interrupt it, unless you rollback everything using a ROLLBACK TRANSACTION command). Also, you're likely going to be interested in the inserted table that's available within the trigger. It contains the new rows (in the case of triggers firing on the INSERT command) or the rows as they will appear (in the case of triggers firing on the UPDATE command).

    The links to both are here:

    CREATE TRIGGER

    Using the inserted and deleted Tables

    K. Brian Kelley
    @kbriankelley

  • November 26, 2007 at 5:33 am

    #753970

    1)

    rsRecordset7.Open "select TIMESTAMP from DEMANDSPREAD WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP)FROM DEMANDSPREAD)", gcnConnect

    DSMAXTS = rsRecordset7.Fields(0).Value

    rsRecordset7.Close

    2)

    sSQL = "select ESDSTotal,ER2DSTotal,ESASKSIZE,ESBIDSIZE,ER2ASKSIZE,ER2BIDSIZE,ER2RATIOASK,ER2RATIOBID,ESRATIOASK,ESRATIOBID,ESNETR,ER2NETR,MMAVGBESTASKPRICE,MMAVGBESTASKORDERS,MMAVGASKBESTASK,TOTALMM4CENTSASK,TOTALACTIVEMMASK,MMASKNRAT,MMAVGBESTBIDPRICE,MMAVGBESTBIDORDERS,MMAVGBIDBESTBID,TOTALMM4CENTSBID,TOTALACTIVEMMBID,MMBIDNRAT,"

    sSQL = sSQL & "SPYAVGASKPRICE,SPYAVGASKORDERS,SPYASKBESTASK,SPY10CASK,SPYACTIVEMMASK,SPY10CACTIVEASK,SPYAVGBIDPRICE,SPYAVGBIDORDERS,SPYBIDBESTBID,SPY10CBID,SPYACTIVEMMBID,SPY10CACTIVEBID,"

    sSQL = sSQL & "IWMAVGASKPRICE, IWMAVGASKORDERS ,IWMASKBESTASK,IWM10CASK,IWMACTIVEMMASK,IWM10CACTIVEASK,IWMAVGBIDPRICE,IWMAVGBIDORDERS,IWMBIDBESTBID,IWM10CBID,IWMACTIVEMMBID,IWM10CACTIVEBID,"

    sSQL = sSQL & "MMORDERS10CBID,MMORDERS10CASK,SPYORDERS10CBID,SPYORDERS10CASK,IWMORDERS10CBID,IWMORDERS10CASK"

    sSQL = sSQL & " from DEMANDSPREAD WHERE TIMESTAMP=" & "'" & DSMAXTS & "'"

    3)

    rsRecordset6.Open "SELECT AVG(ESbidsize) AS ESbidsize, AVG(ESasksize) AS ESasksize, AVG(ER2bidsize) AS ER2bidsize, AVG(ER2asksize) AS ER2asksize FROM dbo.DemandSpread WHERE TIMESTAMP BETWEEN DATEADD(mi, -1, " & "'" & DSMAXTS & "'" & ") AND " & "'" & DSMAXTS & "'", gcnConnect

    rsRecordset6.Close

    ///////////////////////////////////////

    Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

    CREATE TRIGGER internaltriggerDS

    ON DEMANDSPREAD

    AFTER

    INSERT

    [ WITH APPEND ]

    [ NOT FOR REPLICATION ]

    AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME }

    I need to mesh these 3 statements together,

    This is how I call sql from excel, but I dont know how to hold results in sql and call them later on in a query

    The first statement 1) finds the last record on the time stamp field

    I hold the value in VB excel and use it in the other queries following it

    2) This statements uses statement 1 to draw of the fields on the last record

    3) This statement selects the average of a range, again I use statement 1 to select the records to average

    So i need some help, my sql knowledge is very small,

    How do I in sql update trigger, call the last field and reference it from other areas in the query?

    I only want the queries to have one record so i can then call them from excel outside of sql from many PC's

    So i will just have a simple query accessing the new table prodiced by each query on the trigger

    Also how do i delete and update new queries using triggers?

    So i dont want to have old queries running

    Plus will they run on sql opening or do i have to run the query every time I want to start sql

    Many thanks

    JP

  • November 26, 2007 at 12:09 pm

    #754200

    Hi JP,

    Service Broker and Notification Services are also other alternatives that can be explored.

    -Najm

Viewing 13 posts - 1 through 12 (of 12 total)

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