While looping trough te "Steps" collection of a 'Package-object' with the variable "objStep" the call in VBscript o

"objStep.GetExecutionErrorInfo lngErrorCode , ErrSource, ErrDescription" results in an error message

" Type mismatch: 'objStep.GetExecutionErrorInfo'

Does anybody know what is wrong

(The loop performs well when the line is quoted out.
(Pre- condition(s) that is usesed
" IF objStep.ExecutionStatus = DTSStepExecStat_Completed THE
IF objStep.ExecutionResult = DTSStepExecResult_Failure THEN "

Niels Belonje

If you look at the parameter specification for GetExecutionErrorInfo you
can see that it uses by reference parameters of types other than variant.
For by reference to work, you must pass in (and out) the exact type
specified. Since VB script only supports the variant type, you just cannot
do this.

You need to use a "proper" language that supports strong types, such as VB
or any other COM compliant language with types.

--
Darren Green
http://www.sqldts.com

"Niels Belonje" <niels.belonje (AT) philips (DOT) com> wrote

Thank you very much for the information
The problem realy drove me crazy
I already feared that this and tried to 'initialize' the variables by assigning them a value (of the proper type) on beforehand
However, this did not work, although it changed the results of the vartype() function

Thanks again

Niels

In message <EC320911-0808-4CDE-96D2-932B69EEA41B (AT) microsoft (DOT) com>, Niels
Belonje <anonymous (AT) discussions (DOT) microsoft.com> writes
Nothing will really work in VBscript. Using the conversion functions
such as CLng and CStr will suppress errors when you pass the variables
into the function, but since they are ByRef they still do not come back
out properly since they are still not the correct type underneath, so no
point really.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Although not really happy with the outcome, of course, I now know that I have to accept the situation
Somehow a releaf

Thanks again

I have experienced this type of problem before in vbscript and also in the sp_OA% SQL Server Object Automation procedures.

Rather than giving in to these problems I tend to create simple VB6 ActiveX DLLs to work around them.

Here is the source for a replacement method to avoid the problem:

Public Function GetExecutionErrorInfo(ByVal objStep As Variant) As String

Dim lngErrNum As Long
Dim strSource As String
Dim strDescr As String

' Get the step error information and return message.

objStep.GetExecutionErrorInfo lngErrNum, strSource, strDescr

GetExecutionErrorInfo = strDescr

End Function

(You could return more than the description if desired.)

Below is the vbscript used to invoke the above method:

Set oDTSsupp = CreateObject("DTS_Support.DTSsupport")
' Using your assigned DLL Name and Class Name to the above VB6 code

For i = 1 To oPackage.Steps.Count
If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
Set oStep = oPackage.Steps(i)
MsgBox oStep.Name & " failed" & vbCrLf & oDTSsupp.GetExecutionErrorInfo(oStep)
End If
Next


In regards to the SQL Server Object Automation procedures, the most generic DLL I have developed allows a stored procedure to traverse a collection returned by a method. Another one provides a wrapper to Msxml2 allowing SQL Server functions and procedures to make direct web queries.

Cheers, Murray.

---
Posted using Wimdows.net NntpNews Component -

Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.