ADOX problem

I'm trying to get information about a stored procedure using the ADOX.Procedures
collection, every thing is fine if I use an Access DB but when i change the
connection to SQL and try to get a reference to an specific command an "No
such an interface defined" error ocurr.
Please If any body have an idea of what the problem is let me know.
Thanks

"Yoel Martinez" <ymartinez@parkstonemed.com> wrote:
Hi Yoel,
First you check out this article on the web: http://www.asptoday.com/articles/20000619.htm
that explains how to retrieve stored procedures parameters using ADOX in
ASP. It was very helpful to me, but I would like to warn you about something.
You need to create connection string for the active connection to SQL Server,
and the author of the article does it like this:

connStr = "Driver={SQL Server}; Server=" & serverName & "; " & _
"Database=" & dbName & "; UID=" & sqlLogin & "; PWD=" & sqlPwd

pay attention to the DRIVER value. When I tried to use “Provider=SQLOLEDB;”
instead I’ve got an error (don’t remember which one), but with “Driver={SQL
Server};” everything worked fine.
If you have trouble reading VBScript code this is simplified VB version
of what is in that article:

Private Sub RetrieveParametersFromSP()
Dim oCat As New ADOX.Catalog
Dim oCmd As New ADODB.Command
Dim strConn As String
Dim strServerName As String
Dim strDBName As String
Dim strUID As String
Dim strPWD As String
Dim strSPName As String
Dim oProc As Procedure
Dim oPar As Parameter

strServerName = "ProductionSQLServer"
strDBName = "Test"
strUID = "sa"
strPWD = "123456"

strConn = "Driver={SQL Server}; Server=" & strServerName & "; " & _
"Database=" & strDBName & "; UID=" & strUID & "; PWD=" & strPWD & ";"
' connecting to SQL Server
oCat.ActiveConnection = strConn
'1. retrieving all stored procedures available in this Database
' and populating some List Box with stored procedure's names
For Each oProc In oCat.Procedures
lstStoredProcNames.AddItem oProc.Name
Next oProc

' 2. Then imagine that you know the name of the Stored procedure an it
is stored in strSPName
' retrieve all the parameters of that stored procedure
oCmd.ActiveConnection = strConn
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = strSPName
oCmd.Parameters.Refresh ' this will retrieve information about parameters

For Each oPar In oCmd.Parameters
Debug.Print oPar.Name
' converting parameter's direction constatn which is integer to
' "spelled out" version
Select Case oPar.Direction
Case adParamUnknown
Debug.Print "adParamUnknown"
Case adParamInput
Debug.Print "adParamInput"
Case adParamOutput
Debug.Print "adParamOutput"
Case adParamInputOutput
Debug.Print "adParamInputOutput"
Case adParamReturnValue
Debug.Print "adParamReturnValue"
End Select
' converting the type of the parameter
Select Case oPar.Type
Case adBigInt
Debug.Print "adBigInt"
Case adBinary
Debug.Print "adBinary"
Case adBoolean
Debug.Print "adBoolean"
Case adBSTR
Debug.Print "adBSTR"
Case adChapter
Debug.Print "adChapter"
Case adChar
Debug.Print "adChar"
Case adCurrency
Debug.Print "adCurrency"
Case adDate
Debug.Print "adDate"
Case adDBDate
Debug.Print "adDBDate"
Case adDBFileTime
Debug.Print "adDBFileTime"
Case adDBTime
Debug.Print "adDBTime"
Case adDBTimeStamp
Debug.Print "adDBTimeStamp"
Case adDecimal
Debug.Print "adDecimal"
Case adDouble
Debug.Print "adDouble"
Case adEmpty
Debug.Print "adEmpty"
Case adError
Debug.Print "adError"
Case adFileTime
Debug.Print "adFileTime"
Case adGUID
Debug.Print "adGUID"
Case adIDispatch
Debug.Print "adIDispatch"
Case adInteger
Debug.Print "adInteger"
Case adIUnknown
Debug.Print "adIUnknown"
Case adLongVarBinary
Debug.Print "adLongVarBinary"
Case adLongVarChar
Debug.Print "adLongVarChar"
Case adLongVarWChar
Debug.Print "adLongVarWChar"
Case adNumeric
Debug.Print "adNumeric"
Case adPropVariant
Debug.Print "adPropVariant"
Case adSingle
Debug.Print "adSingle"
Case adSmallInt
Debug.Print "adSmallInt"
Case adTinyInt
Debug.Print "adTinyInt"
Case adUnsignedBigInt
Debug.Print "adUnsignedBigInt"
Case adUnsignedInt
Debug.Print "adUnsignedInt"
Case adUnsignedSmallInt
Debug.Print "adUnsignedSmallInt"
Case adUnsignedTinyInt
Debug.Print "adUnsignedTinyInt"
Case adUserDefined
Debug.Print "adUserDefined"
Case adVarBinary
Debug.Print "adVarBinary"
Case adVarChar
Debug.Print "adVarChar"
Case adVariant
Debug.Print "adVariant"
Case adVarNumeric
Debug.Print "adVarNumeric"
Case adVarWChar
Debug.Print "adVarWChar"
Case adWChar
Debug.Print "adWChar"
Case Else
Debug.Print "other"
End Select

Debug.Print oPar.Size
Debug.Print "------------------------"
Next oPar
Set oCat = Nothing
Set oCmd = Nothing
End Sub
Try it out and see how it goes.
Igor