January 15, 2008 at 9:16 am
I am using Excel to interface with SQL, as my charting tool can access excel, but excel hangs alot for some reason, does anyone know of an off the shelf package that can access sql on a timer basis. i am looking for 1-2 seconds, sending a query to sql and report the data back into a field
Thanks
January 15, 2008 at 5:57 pm
Further info to my post
This VBA code is on a timed loop, it sends the query to sql every 2 seconds
I am using excel to pull data from SQL on a network, this excel spreadsheet is being called by another package as well to get the data, so its like a scrach pad.
When the package that is calling this is busy ie alot of calls to excel, then excel tends to hang or times out
Normally the query is very fast, less than .5 second, then other times it hangs
I put in some do events to release it and does make it quicker, but It still hangs during busy times.
Can anyone explain why or where I could look to find out whats causing it to hang?
Or a solution, it can stop the query if it takes longer than one second, that wont matter.
As long as it resumes after the timeout limit thats fine
If i reboot the PC the problem normally goes, so something get screwed up somewere
Dim gcnConnect As ADODB.Connection
Dim rsRecordset As ADODB.Recordset
Dim rsRecordset2 As ADODB.Recordset
Dim rsRecordset3 As ADODB.Recordset
Dim rsRecordset4 As ADODB.Recordset
Dim rsRecordset5 As ADODB.Recordset
Dim rsRecordset6 As ADODB.Recordset
Dim rsRecordset7 As ADODB.Recordset
Dim rsRecordset8 As ADODB.Recordset
Dim dNext As Date
Sub StartDoingIt()
Dim DSMAXTS As String
Dim NVMAXTS As String
Dim sConnect As String
Dim sSQL As String
Dim iCount As Integer
rsRecordset7.Open "select TIMESTAMP from DEMANDSPREAD WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP)FROM DEMANDSPREAD)", gcnConnect
DSMAXTS = rsRecordset7.Fields(0).Value
rsRecordset7.Close
rsRecordset8.Open "select TIMESTAMP from NETVOLUMES WHERE TIMESTAMP=(SELECT MAX(TIMESTAMP)FROM NETVOLUMES)", gcnConnect
NVMAXTS = rsRecordset8.Fields(0).Value
rsRecordset8.Close
DoEvents
sSQL = "select ESDSTotal,ER2DSTotal,TOTALMM4CENTSASK,MMASKNRAT,TOTALMM4CENTSBID,MMBIDNRAT,"
sSQL = sSQL & "SPY10CASK,SPY10CACTIVEASK,SPY10CBID,SPY10CACTIVEBID,"
sSQL = sSQL & "IWM10CASK,IWM10CACTIVEASK,IWM10CBID,IWM10CACTIVEBID,"
sSQL = sSQL & "MMORDERS10CBID,MMORDERS10CASK,SPYORDERS10CBID,SPYORDERS10CASK,IWMORDERS10CBID,IWMORDERS10CASK,"
sSQL = sSQL & "ST7510CASK,ST7510CACTIVEASK,ST75ORDERS10CASK,ST7510CBID,ST7510CACTIVEBID,ST75ORDERS10CBID"
sSQL = sSQL & " from DEMANDSPREAD WHERE TIMESTAMP=" & "'" & DSMAXTS & "'"
rsRecordset.Open sSQL, gcnConnect
Sheet1.Range("A7") = rsRecordset.Fields(0).Value ' ESDSTotal
Sheet1.Range("B7") = rsRecordset.Fields(1).Value ' ER2DSTotal
Sheet1.Range("D67") = rsRecordset.Fields(2).Value ' TOTALMM4CENTSASK
Sheet1.Range("F67") = rsRecordset.Fields(3).Value ' MMASKNRAT
Sheet1.Range("J67") = rsRecordset.Fields(4).Value ' TOTALMM4CENTSBID
Sheet1.Range("L67") = rsRecordset.Fields(5).Value ' MMBIDNRAT
Sheet1.Range("D69") = rsRecordset.Fields(6).Value ' SPY10CASK
Sheet1.Range("F69") = rsRecordset.Fields(7).Value ' SPY10CACTIVEASK
Sheet1.Range("J69") = rsRecordset.Fields(8).Value ' SPY10CBID
Sheet1.Range("l69") = rsRecordset.Fields(9).Value ' SPY10CACTIVEBID
Sheet1.Range("D71") = rsRecordset.Fields(10).Value ' IWM10CASK
Sheet1.Range("F71") = rsRecordset.Fields(11).Value ' IWM10CACTIVEASK
Sheet1.Range("J71") = rsRecordset.Fields(12).Value ' IWM10CBID
Sheet1.Range("l71") = rsRecordset.Fields(13).Value ' IWM10CACTIVEBID
Sheet1.Range("M67") = rsRecordset.Fields(14).Value ' MMORDERS10CBID
Sheet1.Range("N67") = rsRecordset.Fields(15).Value ' MMORDERS10CASK
Sheet1.Range("M69") = rsRecordset.Fields(16).Value ' SPYORDERS10CBID
Sheet1.Range("N69") = rsRecordset.Fields(17).Value ' SPYORDERS10CASK
Sheet1.Range("M71") = rsRecordset.Fields(18).Value ' IWMORDERS10CBID
Sheet1.Range("N71") = rsRecordset.Fields(19).Value ' IWMORDERS10CASK
Sheet1.Range("D73") = rsRecordset.Fields(20).Value ' ST7510CASK
Sheet1.Range("F73") = rsRecordset.Fields(21).Value ' ST7510CACTIVEASK
Sheet1.Range("N73") = rsRecordset.Fields(22).Value ' ST75ORDERS10CASK
Sheet1.Range("J73") = rsRecordset.Fields(23).Value ' ST7510CBID
Sheet1.Range("L73") = rsRecordset.Fields(24).Value ' ST7510CACTIVEBID
Sheet1.Range("M73") = rsRecordset.Fields(25).Value ' ST75ORDERS10CBID
rsRecordset.Close
' Set rsRecordset = Nothing
DoEvents
sSQL = "select ESNetvolume,ESAskSMblock,ESBidSMblock,ESAskMDblock,ESBidMDBlock,ESAskLGBlock,ESBidLGBlock,ER2netVolume,ER2AskSMblock,ER2 BidSMblock,ER2AskMDblock,ER2BidMDBlock,ER2AskLGBlock,ER2BidLGBlock,ESorderflow,ER2orderflow,NetVolume75,UpVolume75,DownV olume75,ESupvolume,ESdownvolume,ER2upvolume,ER2downvolume,EStradeask,EStradebid,ER2tradeask,ER2tradebid,ADTRIN75,AD75,AD VWAP75,ADV75,"
sSQL = sSQL & "fastcash75,fastcash3in1, filt75netvol from NetVolumes WHERE TIMESTAMP=" & "'" & NVMAXTS & "'"
rsRecordset2.Open sSQL, gcnConnect
Sheet1.Range("A3") = rsRecordset2.Fields(0).Value ' ESNetvolume
Sheet1.Range("B3") = rsRecordset2.Fields(1).Value ' ESAskSMblock
Sheet1.Range("C3") = rsRecordset2.Fields(2).Value ' ESBidSMblock
Sheet1.Range("D3") = rsRecordset2.Fields(3).Value ' ESAskMDblock
Sheet1.Range("E3") = rsRecordset2.Fields(4).Value ' ESBidMDBlock
Sheet1.Range("F3") = rsRecordset2.Fields(5).Value ' ESAskLGBlock
Sheet1.Range("G3") = rsRecordset2.Fields(6).Value ' ESBidLGBlock
Sheet1.Range("A5") = rsRecordset2.Fields(7).Value ' ER2netVolume
Sheet1.Range("B5") = rsRecordset2.Fields(8).Value ' ER2AskSMblock
Sheet1.Range("C5") = rsRecordset2.Fields(9).Value ' ER2BidSMblock
Sheet1.Range("D5") = rsRecordset2.Fields(10).Value ' ER2AskMDblock
Sheet1.Range("E5") = rsRecordset2.Fields(11).Value ' ER2BidMDBlock
Sheet1.Range("F5") = rsRecordset2.Fields(12).Value ' ER2AskLGBlock
Sheet1.Range("G5") = rsRecordset2.Fields(13).Value ' ER2BidLGBlock
Sheet1.Range("K3") = rsRecordset2.Fields(14).Value ' ESorderflow
Sheet1.Range("K5") = rsRecordset2.Fields(15).Value ' ER2orderflow
Sheet1.Range("A9") = rsRecordset2.Fields(16).Value ' NetVolume75
Sheet1.Range("B9") = rsRecordset2.Fields(17).Value ' UpVolume75
Sheet1.Range("C9") = rsRecordset2.Fields(18).Value ' DownVolume75
Sheet1.Range("L3") = rsRecordset2.Fields(19).Value ' ESupvolume
Sheet1.Range("M3") = rsRecordset2.Fields(20).Value ' ESdownvolume
Sheet1.Range("L5") = rsRecordset2.Fields(21).Value ' ER2upvolume
Sheet1.Range("M5") = rsRecordset2.Fields(22).Value ' ER2downvolume
Sheet1.Range("N3") = rsRecordset2.Fields(23).Value ' EStradeask
Sheet1.Range("O3") = rsRecordset2.Fields(24).Value ' EStradebid
Sheet1.Range("N5") = rsRecordset2.Fields(25).Value ' ER2tradeask
Sheet1.Range("O5") = rsRecordset2.Fields(26).Value ' ER2tradebid
Sheet1.Range("D9") = rsRecordset2.Fields(27).Value ' ADTRIN75
Sheet1.Range("E9") = rsRecordset2.Fields(28).Value ' AD75
Sheet1.Range("F9") = rsRecordset2.Fields(29).Value ' ADVWAP75
Sheet1.Range("G9") = rsRecordset2.Fields(30).Value ' ADV75
Sheet1.Range("A52") = rsRecordset2.Fields(31).Value ' fastcash75
Sheet1.Range("B52") = rsRecordset2.Fields(32).Value ' fastcash3in1
Sheet1.Range("H9") = rsRecordset2.Fields(33).Value ' filt75netvol
rsRecordset2.Close
DoEvents
End Sub
Sub DoItAgain()
Call StartDoingIt
' dNext = Now + TimeValue("00:00:01") 'every 2 seconds
dNext = Now + Sheet1.Range("A21") 'every 2 seconds
Application.OnTime dNext, "DoItAgain"
DoEvents
End Sub
'Sub DoIt()
' MsgBox "Hello!"
'End Sub
Sub StopDoingIt()
On Error Resume Next
Application.OnTime dNext, "DoItAgain", schedule:=False
End Sub
'Then this function is your update forever loop:
Public Sub KeepUpdated()
Dim PauseTime, Start, Finish, TotalTime As Integer
' Create the connection string.
' Base connection string syntax.
sConnect = "Provider=SQLOLEDB;" & _
"Data Source=xxx-xxx\SQLEXPRESS;" & _
"Initial Catalog=xxxx;" & _
"User ID=xxx;" & _
"Password=xxx;"
' Retrieve the data using ADO.
Set gcnConnect = New ADODB.Connection
Set rsRecordset = New ADODB.Recordset
Set rsRecordset2 = New ADODB.Recordset
Set rsRecordset3 = New ADODB.Recordset
Set rsRecordset4 = New ADODB.Recordset
Set rsRecordset5 = New ADODB.Recordset
Set rsRecordset6 = New ADODB.Recordset
Set rsRecordset7 = New ADODB.Recordset
Set rsRecordset8 = New ADODB.Recordset
gcnConnect.ConnectionString = sConnect
' Keep the connection open for as long as we're running
gcnConnect.Open
'gcnConnect.Close
DoItAgain
End Sub
January 15, 2008 at 7:49 pm
If the problem is on the database server I'd suggest two things
1. Use SQL Profiler to see what else is happening on the server when the query takes longer than expected. Perhaps you can use some NOLOCK locking hints to speed things up, or perhaps your inputs change so indices on the server aren't as effective.
2. To terminate after one second add a timeout clause to your connection string. I cannot remember the exact syntax but it's possible to do. Failing that I think the ADO recordset object has such a property.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply