SQL execution. Sometimes can , sometimes cannot.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL execution. Sometimes can , sometimes cannot.

Author

Topic

liangtp@hotmail.com
Starting Member

27 Posts

Posted - 2008-08-08 : 02:31:37

Hi

I constructed some SQL statements in VB, and have it sent to the SQL for execution. In the code below I issued the command "mobjQMSDBM.RunSQL(strSQL)" twice.

The problem is with the second RunSQL execution. Sometime it runs successfull (and quick enough) but at other times, it returns an error after a long while.

FYI, the same code if run in Query Analyzer, no problem.

May I know why?

Thanks and regards,

-- Code below.
strFirstOfMonthYear = FormatSQLLiteral(mobjQMSError, mobjQMSDBM, dtFirstOfMonthYear, DT_DATETIME)

If lngUserID = 0 Then 'the Teller/FG/User is not specified
strWhichBookingVol = "BookingVol"
strNullOrNot = " IS NULL "
Else
strWhichBookingVol = "FGSpecifiedBookingVol"
strNullOrNot = " IS NOT NULL "
End If

strSQL = "DECLARE @Date AS DATETIME " & _
"SET @Date = CONVERT(DATETIME, " & strFirstOfMonthYear & ", 120) " & _
"CREATE TABLE #temp " & _
" (ApptDate DATETIME, JoinField NVARCHAR(1)); " & _
"WHILE DATEDIFF(m, @Date, CONVERT(DATETIME, " & strFirstOfMonthYear & ", 120)) = 0 " & _
"BEGIN " & _
"INSERT INTO #temp SELECT CONVERT(DATETIME, @Date, 120), 'J' " & _
"SET @Date = DATEADD(day, 1, @Date) " & _
"END "

If mobjQMSDBM.RunSQL(strSQL) <> eSuccessful Then GoTo QuitOnFalse

strSQL = "DECLARE @tblOneMonth TABLE ( " & _
"ApptDate DATETIME, " & _
strWhichBookingVol & " INT, " & _
"Reserved INT, " & _
"FreeSlot INT) " & _
"INSERT INTO @tblOneMonth (ApptDate, " & strWhichBookingVol & ", Reserved , FreeSlot) " & _
"SELECT ApptDate, " & strWhichBookingVol & ", 0 AS Reserved, " & strWhichBookingVol & " AS FreeSlot " & _
"FROM #temp tT " & _
"Left Join " & _
"( " & _
"SELECT SUM(" & strWhichBookingVol & ") AS " & strWhichBookingVol & ", 'J' AS JoinField " & _
"From tblApptSlot " & _
") AS tAS " & _
"on tAS.JoinField = tT.JoinField "

strSQL = strSQL & _
"DECLARE @tblWithAppt TABLE " & _
"(ApptDate DATETIME, " & strWhichBookingVol & " INT, Reserved INT, FreeSlot INT) " & _
"INSERT INTO @tblWithAppt (ApptDate, " & strWhichBookingVol & ", Reserved , FreeSlot) " & _
"SELECT tA.ApptDate, tT2." & strWhichBookingVol & ", COUNT(*) AS Reserved, " & _
"tT2." & strWhichBookingVol & " - COUNT(*) AS FreeSlot " & _
"FROM tblAppt AS tA " & _
"INNER JOIN " & _
"( " & _
"SELECT ApptDate, " & strWhichBookingVol & ", 0 AS Reserved, " & _
strWhichBookingVol & " AS FreeSlot " & _
"FROM #temp tT1 " & _
"LEFT JOIN " & _
"( " & _
"SELECT SUM(" & strWhichBookingVol & ") AS " & strWhichBookingVol & ", 'J' AS JoinField " & _
"FROM tblApptSlot " & _
") AS tAS " & _
"ON tAS.JoinField = tT1.JoinField " & _
") AS tT2 " & _
"ON tA.ApptDate = tT2.ApptDate " & _
"AND DATEDIFF(m, tA.ApptDate, convert(DATETIME, " & strFirstOfMonthYear & ", 120)) = 0 " & _
"AND tA.UserID " & strNullOrNot & _
"GROUP BY tA.ApptDate, tT2." & strWhichBookingVol & " "

strSQL = strSQL & "CREATE TABLE #temp2 " & _
"(ApptDate DATETIME, " & strWhichBookingVol & " INT, Reserved INT, FreeSlot INT); " & _
"INSERT INTO #temp2 "

strSQL = strSQL & "SELECT * " & _
"FROM @tblOneMonth " & _
"WHERE " & _
"ApptDate NOT IN " & _
"( " & _
"SELECT ApptDate " & _
"FROM @tblWithAppt " & _
") " & _
"UNION " & _
"SELECT * " & _
"FROM @tblWithAppt "

lngRes = mobjQMSDBM.RunSQL(strSQL) <== this is where the problem happens.

liangtp@hotmail.com
Starting Member

27 Posts

Posted - 2008-08-08 : 02:38:40

Guys,

I think it is probably due to timeout, because I checked my error log and it said "14:33:40, QUtil:CDBM:RunSQL, -2147217871:Query timeout expired "

Thanks anyway.

Perhaps you can comment on my coding.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources