SQL- ADO Error via Automation (Only on some PC's)

[Rock6431]

ERROR: Class does not support Automation or does not support expected interface?

Besides the other objects needed for other functions, I added the following object references for the following
functions.

Reference
Objects: OLE Automation (STDole2.tlb),
MS ActiveX Data Obj 2.5 Lib (msado25.tlb)

I have an ActX-dll object that interfaces between my webpages, SQL, and various other systems. I can get the following code to work properly on the development PC and on a few remote PC's as well, but the majority I can not.

The above error is being returned on the PC's where I can not get a stored procedure to execute from the dll using either a trusted or user/pwd connection. I have troubleshooted the error and have determined that it is not in the connection to the database.

Each table, stored procedure, and user defined function has enabled permissions in SQL with exception to delete. So I know it is not a SQL permission issue. What should I look for in IE or in Windows that would be causing this problem...

Code Listed below for the call, connection, disconnect, stored procedure, recordset disconnect, and the SQL SP.

Can anyone give me any advise.

Rock6431


Function GetSP_GetIPROMPTS(lngKEY) As Boolean
Err.Clear
On Error GoTo GetSP_GetIPROMPTS_Error


Set CMD = New ADODB.Command
Set rs = New ADODB.Recordset

CMD.ActiveConnection = CNN
CMD.CommandText = "dbo.getiprompts"
CMD.CommandType = adCmdStoredProc
Set prm = CMD.CreateParameter("@auto", adInteger, adParamInput)
CMD.Parameters.Append prm
prm.Value = lngKEY
Set rs = CMD.Execute()
rs.MoveFirst
GetSP_GetIPROMPTS = True
Exit Function

GetSP_GetIPROMPTS_Error:

' Error Occurred
Debug.Print "GetSP_GetIPROMPTS_Error"
TEST3 = "SP IPROMPTS= " & Err.Description
GetSP_GetIPROMPTS = False

End Function



Private Function cmdConnect(ServerName, DatabaseName, ByVal UserName, ByVal Password) As Boolean
' Connect to server through SQL Server OLE DB Provider.

'strDBC = cmdConnect("Audrey", "RampIT", "", "&quot

On Error GoTo ErrorConnect:
RampERROR = 0
' Set connection properties.
CNN.ConnectionTimeout = 25
CNN.Provider = "sqloledb"
CNN.Properties("Data Source&quot.Value = ServerName
CNN.Properties("Initial Catalog&quot.Value = DatabaseName
' Decision code for login authorization type: WinNT or SQL Server.
If Len(UserName) = 0 And Len(Password) = 0 Then
CNN.Properties("Integrated Security&quot.Value = "SSPI"
Else
CNN.Properties("User ID&quot.Value = UserName
CNN.Properties("Password&quot.Value = Password
End If
' Open the database.
CNN.Open
'Response = MsgBox(MsgConnSucc, vbOKOnly, Title)
cmdConnect = True
Exit Function

ErrorConnect:

' Error checking. Connection successful.
If CNN.State = adStateOpen Then
RampERROR = 0
cmdConnect = True
End If
' Error checking. Connection unsuccessful.
If CNN.State <> adStateOpen Then
RampERROR = 25
cmdConnect = False
End If
End Function


Private Function cmdDisconnect()
' Disconnect from a connected server.
If CNN.State = adStateOpen Then
CNN.Close
Set CNN = Nothing
End If
End Function


Private Function rsDisconnect()
' Disconnect from a connected server.
If rs.State = adStateOpen Then
rs.Close
Set rs = Nothing
End If
End Function



----SQL PROC----

CREATE PROC dbo.[GetIPROMPTS]
@AUTO integer
AS
SELECT *
FROM dbo.LONIIPROMPT
WHERE (AUTO = @AUTO)
GO

 

[MarkSweetland]

The error 'Class does not support Automation or does not support expected interface?' also shows up when the client machine does not have the same version of the MDAC used on/in the development environment machine. The inclusion of the msado25.tlb in your object reference prevents you from using an interface for MDAC's greater than 2.5, even though you may have a later version (up to 2.7 now?) installed on your development machine. It also then, would require MDAC 2.5 or later to be installed on the client machine. Check the clients and see if the version is 2.5+. Chances are they may be 2.1 ...





Mark