Comparing results from 2 queries
Hi,
I am looking for a vbscript that compares the result of 2 sql queries and if the result matches, it succeeds and the rest of the DTS package continues. However if the results do not match then to fail. This will be a standalone script and will be a step in the overall DTS. Any ideas much appreciated.
Re: Comparing results from 2 queries
So.. a change of requirement.. Rather than comparing two sets of sql, I think it would be easier if i create the sql in a view.. The VBSCRIPT is to then check the value of the single field "Discrepancy" in this view and either be successful if the value of the field is 0 or fall over if it is not 0. Hope someone has some ideas.
Re: Comparing results from 2 queries
In your first post, what do the 2 queries return?
In your second post have you gotten the view SQL figured out?
Re: Comparing results from 2 queries
Hey Mark.. going with the 2nd post.
So the sql in the view will return a single value or 1 or 0. 0 indicating all is well and to quit with success and 1 indicating there is a problem and quit with failure.
Re: Comparing results from 2 queries
So back to the last question, do you have the view working? If so what is the view name and field name?
Re: Comparing results from 2 queries
view and field is below in the sql statement
SELECT DISCREPANCY FROM dbo.MDS_RecordCount_Vs_FolderCount
Re: Comparing results from 2 queries
Still not sure what you are after but maybe this is heading in the right direction.
Code:
Const adForwardOnly = 0
Const adReadOnly = 1
Const adUseServer = 2
Function Main()
If Descripancy Then
Main = DTSTaskExecResult_Failure
else
Main = DTSTaskExecResult_Success
End If
End Function
Function Descripancy()
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
With objConnection
.ConnectionString = "" '<TODO>
.Open
End With
With objRecordset
.ActiveConnection = objConnection
.CursorLocation = adUseServer
.CursorType = adForwardOnly
.LockType = adReadOnly
.Source = "SELECT DISCREPANCY FROM dbo.MDS_RecordCount_Vs_FolderCount"
.Open
End With
If objRecordset("DISCREPANCY") = 0 Then
Descripancy = false
Else
Descripancy = true
End If
objRecordset.Close
Set objRecordset = Nothing
objConnection.Close
Set objConnection = Nothing
End Function
Re: Comparing results from 2 queries
I've kinda conjured up the below and it appears to work. Looks roughly similar to yours. Ok, what my goal is to have a vbscript that will look at the value for the field 'discrepancy' in the table dbo.MDS_RecordCount_Vs_FolderCount. Thereafter if the value is '0', the script is to do nothing, and quit. Please note the script will be in an activex task in a DTS. So if the result is 0 the script is successful and the DTS will move onto the next step. If the value is not 0, then the script is to quit with a failure and the DTS will stop there and not continue processing next task.
In what I have below, I have added a email function to be called upon if the script fails. It all appears to work. Now however I need this script to run after a delay of a minute. Reason being the step before this script is writing to a location and lots of other processing is taking place. Therefore when my sql (view) runs in this script it runs before the previous task (although has completed successfully) it is still processing. Hope that makes sense. I've been looking and there appears to be shell sleep method to make it delay for a minute but not sure where to incorporate it.
Dim connEWORK
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdText = &H0001
Const adOpenDynamic = 2
Const adOpenKeyset = 1
Function Main()
Dim SQL
Dim strRecipients
Dim CheckForDiscrepancy
Set connEWORK = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
connEWORK.Provider = ""
connEWORK.Properties("Data Source").Value = ""
connEWORK.Properties("Initial Catalog").Value =""
connEWORK.Properties("User ID").Value = ""
connEWORK.Properties("Password").Value = ""
connEWORK.Properties("Integrated Security").Value = ""
connEWORK.ConnectionTimeout = 5
connEWORK.commandtimeout = 5
connEWORK.Open
SQL = "SELECT DISCREPANCY FROM dbo.MDS_RecordCount_Vs_FolderCount"
RS.Open SQL, connEWORK, adOpenKeyset, adLockReadOnly, adCmdText
CheckForDiscrepancy = RS.getString
RS.Close
Set RS = Nothing
connEWORK.Close
Set connEWORK = Nothing
If (CheckForDiscrepancy = 0) Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
strRecipients = "[email protected]"
Call sendEmail(strRecipients)
End If
End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Name: Function Send_Email
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function sendEmail(strRecipients)
Dim iMsg
Set iMsg = CreateObject("CDO.Message")
With iMsg
.To = "[email protected]"
.From = "[email protected]"
.Subject = "MDS Failure"
' Content of email
.TextBody = "DTS has errored in Step 9, the count of folders created does not match the count of records for the month" & vbCrLf & vbCrLf &_
"DTS to review and troubleshoot is 'MDS-1-Data Transformations and Supplier Reports Creation'" & vbCrLf & vbCrLf &_
"GBR00WRKD1"
.Send
End With
Set iMsg = Nothing
End Function
1 Attachment(s)
Re: Comparing results from 2 queries
Quote:
Originally Posted by
Baz_Singh
I need this script to run after a delay of a minute. Reason being the step before this script is writing to a location and lots of other processing is taking place. Therefore when my sql (view) runs in this script it runs before the previous task (although has completed successfully) it is still processing. Hope that makes sense.
If I understand you, this can be done by setting the workflow of the package. To do this, select the 2 tasks that you need to run in sequence and then click the workflow menu option and select the option you need. By doing this you can set what order your tasks will run.
Re: Comparing results from 2 queries
yup that is one option, but i believe in workflow 'on completion' will make the next step run even if the preceeding step fails..
Re: Comparing results from 2 queries