I'm having an problem with a SQL Server 2000 update in a VBScript program executing in a DTS Package.
Source extract and results
'***** set Connection
Set cnChangepoint = CreateObject("ADODB.Connection")
cnChangepoint.Open "Provider=SQLOLEDB;Server=" & _
DTSGlobalVariables("gvSQLServerName").Value & ";UID=" & DTSGlobalVariables("gvdbUserId") & _
";PWD=" & DTSGlobalVariables("gvdbPassword") & ";Database=*********"
'*** Set Variables
strResourceText1 = DTSGlobalVariables("gvResourceText1").Value
strResourceText2 = DTSGlobalVariables("gvResourceText2").Value
strResourceText3 = DTSGlobalVariables("gvResourceText3").Value
strCHRISResourceId = DTSGlobalVariables("gvCHRISResourceId").Value
strErrorCode = "500,"
If strResourceText1 <> strUDFCompany Then
strCHQResources = "Update CHQ_Resources Set ErrorCodes = '" & strErrorCode & "'" & " where CHRISResourceId = '" & strCHRISResourceId & "'"
cnChangepoint.Execute (strCHQResources)
If Err.Number <> 0 Then
'**** For Debugging purpose
strData = "UDF Company Error " & VBCrlf & _
"Value of Err Number is " & Err.Number & VbCrLf & _
"Value of Err Description is " & Err.Description & VbCrLf & _
"Value of strChqResources is " & strCHQResources & VbCrLf & _
"Value of strResourceText1 is " & strResourceText1 & VbCrLF & _
"Value of strUDFCompany is " & strUDFCompany & VbCrLf & _
"Value of strErrorCode is " & strErrorCode & VbCrLf & _
"Value of gvCHRISResourceId is " & DTSGlobalVariables("gvCHRISResourceId").Value
Call WriteToFile(strWriteToFile, strData, 2)
End If
End If
'*** Results
UDF Company Error
Value of Err Number is 438
Value of Err Description is Object doesn't support this property or method
Value of strChqResources is Update CHQ_Resources Set ErrorCodes = '500,' where CHRISResourceId = '{A8C820C0-382B-4A3A-9393-1C49BE92EA0F}'
Value of strResourceText1 is 2
Value of strUDFCompany is 1
Value of strErrorCode is 500,
Value of gvCHRISResourceId is {A8C820C0-382B-4A3A-9393-1C49BE92EA0F}
Source extract and results
'***** set Connection
Set cnChangepoint = CreateObject("ADODB.Connection")
cnChangepoint.Open "Provider=SQLOLEDB;Server=" & _
DTSGlobalVariables("gvSQLServerName").Value & ";UID=" & DTSGlobalVariables("gvdbUserId") & _
";PWD=" & DTSGlobalVariables("gvdbPassword") & ";Database=*********"
'*** Set Variables
strResourceText1 = DTSGlobalVariables("gvResourceText1").Value
strResourceText2 = DTSGlobalVariables("gvResourceText2").Value
strResourceText3 = DTSGlobalVariables("gvResourceText3").Value
strCHRISResourceId = DTSGlobalVariables("gvCHRISResourceId").Value
strErrorCode = "500,"
If strResourceText1 <> strUDFCompany Then
strCHQResources = "Update CHQ_Resources Set ErrorCodes = '" & strErrorCode & "'" & " where CHRISResourceId = '" & strCHRISResourceId & "'"
cnChangepoint.Execute (strCHQResources)
If Err.Number <> 0 Then
'**** For Debugging purpose
strData = "UDF Company Error " & VBCrlf & _
"Value of Err Number is " & Err.Number & VbCrLf & _
"Value of Err Description is " & Err.Description & VbCrLf & _
"Value of strChqResources is " & strCHQResources & VbCrLf & _
"Value of strResourceText1 is " & strResourceText1 & VbCrLF & _
"Value of strUDFCompany is " & strUDFCompany & VbCrLf & _
"Value of strErrorCode is " & strErrorCode & VbCrLf & _
"Value of gvCHRISResourceId is " & DTSGlobalVariables("gvCHRISResourceId").Value
Call WriteToFile(strWriteToFile, strData, 2)
End If
End If
'*** Results
UDF Company Error
Value of Err Number is 438
Value of Err Description is Object doesn't support this property or method
Value of strChqResources is Update CHQ_Resources Set ErrorCodes = '500,' where CHRISResourceId = '{A8C820C0-382B-4A3A-9393-1C49BE92EA0F}'
Value of strResourceText1 is 2
Value of strUDFCompany is 1
Value of strErrorCode is 500,
Value of gvCHRISResourceId is {A8C820C0-382B-4A3A-9393-1C49BE92EA0F}