Hello all,
I have recently upgraded to SQL server 2005 from 2000, due to a server switch and licensing changes. It's running on Windows Server 2003.
On a daily basis, the SQL DBs are updated from our trading database, by way of an ODBC link.
This update starts early in the morning, once the back-ups have finished and the trading database has restarted. This can vary from 2 a.m. to 5 a.m., dependent on other matters, but is slowly increasing, as the database grows in size.
The updates are run at DTS jobs, called by the SQL server agent according to a timetable as I lack the time and experience to convert all of them to SSIS.
My issue is the code that I was using to test the DB availability. I had cobbled a short ActiveX script that worked fine in SS2000, being a tiny enquiry against the trading DB using ADODB that ran as the first DTS of the day.
If it returned a fail, I rolled all the subsequent DTS jobs by 10 minutes and added a scheduled job to retest in 10 minutes. If success, then the job ended ok and the rest of the DTS jobs kicked off at the appropriate time.
My issue is that the same ActiveX code causes the initial DTS job to hang at 'Executing' when the DB is not available; returning neither success nor failure.
I suspect Windows is reporting a problem with the ODBC link, but such is causing the DTS job to wait for a user to clear the warning.
Other than getting the DBA to update a file with the date and time of DB return, is there either a way to suppress ODBC warnings or a test I can perform that will complete with failure?
Many thanks,
Lex
soi la, soi carré
I have recently upgraded to SQL server 2005 from 2000, due to a server switch and licensing changes. It's running on Windows Server 2003.
On a daily basis, the SQL DBs are updated from our trading database, by way of an ODBC link.
This update starts early in the morning, once the back-ups have finished and the trading database has restarted. This can vary from 2 a.m. to 5 a.m., dependent on other matters, but is slowly increasing, as the database grows in size.
The updates are run at DTS jobs, called by the SQL server agent according to a timetable as I lack the time and experience to convert all of them to SSIS.
My issue is the code that I was using to test the DB availability. I had cobbled a short ActiveX script that worked fine in SS2000, being a tiny enquiry against the trading DB using ADODB that ran as the first DTS of the day.
If it returned a fail, I rolled all the subsequent DTS jobs by 10 minutes and added a scheduled job to retest in 10 minutes. If success, then the job ended ok and the rest of the DTS jobs kicked off at the appropriate time.
My issue is that the same ActiveX code causes the initial DTS job to hang at 'Executing' when the DB is not available; returning neither success nor failure.
Code:
dim myConn
dim myRecordset
dim mySQLCmdText
On error resume next
set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset")
myConn.Open = "DSN=TradingDB;UID=geno;PWD="
mySQLCmdText = "select 'taskcount' = T1.Loc from REPORT.location T1"
myRecordset.Open mySQLCmdText, myConn
If myRecordset.RecordCount = 0 then
Main = DTSTaskExecResult_Failure
Else
Main = DTSTaskExecResult_Success
End If
End FunctionI suspect Windows is reporting a problem with the ODBC link, but such is causing the DTS job to wait for a user to clear the warning.
Other than getting the DBA to update a file with the date and time of DB return, is there either a way to suppress ODBC warnings or a test I can perform that will complete with failure?
Many thanks,
Lex
soi la, soi carré