November 15, 2007 at 8:03 am
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
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
so there is no way of running a query every 1 second from withn sql?
November 15, 2007 at 12:50 pm
try using at command and osql at os level if you are on express. just thoughts
November 15, 2007 at 9:57 pm
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
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
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
Cant SQL execute a query when a new record comes in that woukd be simplist of all
November 20, 2007 at 6:07 pm
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
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
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:
Using the inserted and deleted Tables
K. Brian Kelley
@kbriankelley
November 26, 2007 at 5:33 am
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
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