Link to home
Start Free TrialLog in
Avatar of mirghani
mirghani

asked on

Runt-Time Error '3251'

hi All,
i'm using VB6,SQL2k,MDAC 2.7,COM+(DLL's),Win2k Adv Server.
Before Importing my Dll's into COM+ Component Services Retrieving Recordsets was working properly and MyRs.NextRecordSet was working properly.
when importing the DLL's as a components i started getting this Run-Time Error '3251' Current Provider does not support returning multiple recordsets...
but there is something strange if i open my Component Services that's in Win2k Administrative Tools and Right Click MyPackage Properties --- Ativation tab---and change the Activation Type From Server Application To Library Application , this run-time error will not appear and it will work fine !!!!!???
Any Help?
here is my DataAccess Component Code:
'Copyright (C) 2002 Sudatel Corporation
'All rights reserved.
'
'Date    - 01/03/03 .... 10:05 am
'Author - Meer
Option Explicit
Implements ObjectControl
Implements IObjectConstruct

Public Type paraList
  VarValue As Variant
  DataType As Variant
  Size As Integer
  Direction As Integer
End Type

Public Enum QUERYOPTIONS
    QO_UseTable = ADODB.adCmdTable
    QO_UseText = ADODB.adCmdText
    QO_UseStoredProc = ADODB.adCmdStoredProc
    QO_UseUnknown = ADODB.adCmdUnknown
    QO_UseDefault = -1
End Enum

Public Enum LOCKTYPES
    LT_ReadOnly = ADODB.adLockReadOnly
    LT_Optimistic = ADODB.adLockOptimistic
    LT_Pessimistic = ADODB.adLockPessimistic
    LT_BatchOptimistic = ADODB.adLockBatchOptimistic
End Enum

Public Enum CURSORLOCATION
    CURL_Client = ADODB.adUseClient
    CURL_Server = ADODB.adUseServer
End Enum

Public Enum CURSORTYPE
    CURT_OpenKeySet = ADODB.adOpenKeyset
End Enum

Public Enum E_ConnType
     Local_Conn = 0
     Remote_Conn = 1
End Enum

Private Const OBJNAME = "DataAccess Component"
Private Cnn As ADODB.Connection
Private Cmd As ADODB.Command
Private Outernalconnstr As String

'Connection String that Contains the Lookups Data Source
'Private Const Internalconnstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SBOS EH;Data Source=SBOS_SERVER"
Private Const Internalconnstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SBOS EH;Data Source=SBOS_SERVER"

Private Sub IObjectConstruct_Construct(ByVal pCtorObj As Object)
Dim mConstructString As IObjectConstructString
Set mConstructString = pCtorObj
Outernalconnstr = mConstructString.ConstructString
'MsgBox Outernalconnstr
End Sub

'=======================================================================
'Synopsis:              This function closes a previously opened connection
'Function input:        none
'Function output:       Returns TRUE if the function succeeds
'=======================================================================
Public Function CloseConnection() As Boolean
    If Not Cnn Is Nothing Then
'        Cnn.Close
        Set Cnn = Nothing
    End If

End Function

'=======================================================================
'Synopsis:              This function creates and opens a new connection
'Function input:        Connect string
'Function output:       Returns TRUE if the function succeeds
'=======================================================================
Public Function OpenConnection(ByVal Connect As String) As Boolean
Dim lErrNo      As Long
Dim sErrDesc    As String

    'assume failure
    OpenConnection = False
   
    'enable error handler
    On Error GoTo ErrorHandler
   
    'establish the transaction if Connection String specified
    If Connect <> vbNullString Then
        CloseConnection
        Set Cnn = New ADODB.Connection
        Cnn.Open Connect
        'MsgBox "Connected"
    End If
   
    OpenConnection = True
    Exit Function
   
ErrorHandler:

    'store incoming values and raise error
    lErrNo = Err.Number
    sErrDesc = Err.Description
    On Error GoTo 0
    Err.Raise lErrNo, OBJNAME, sErrDesc

End Function

'Private Sub ObjectControl_Activate()
''Nothing Occur here
'End Sub

'This Function Executes a StoredProcedure and Return RS ByRefrence and Boolean
'if Done Successfully.
Public Function ExecuteSp(ByRef rs As ADODB.Recordset, ByVal spName As String, ByVal ConnType As Integer, ByRef AttachedParameters() As paraList) As Boolean
Dim i As Long
Dim lErrNo      As Long
Dim sErrDesc    As String

On Error GoTo ErrorHandler
   ' If RS Is Nothing Then
        Set rs = New ADODB.Recordset
   ' End If
 
    Set Cmd = New ADODB.Command

    If rs.State = adStateOpen Then
        rs.Close
    End If
With rs
    .CURSORTYPE = CURT_OpenKeySet
    .LockType = LT_Optimistic
    .CURSORLOCATION = CURL_Client
End With
  ExecuteSp = True
    'Is used for DataBase Connection String
    If ConnType = Remote_Conn Then
      OpenConnection (Outernalconnstr)
    Else
    'Is used for Cashing Connection String
      OpenConnection (Internalconnstr)
    End If
     Cmd.CommandTimeout = 3600
     Cmd.ActiveConnection = Cnn
     Cmd.CommandType = QO_UseStoredProc
     Cmd.CommandText = Trim(spName)
        If (UBound(AttachedParameters) > 0) Then
           'Cmd.Parameters.Refresh
            For i = 1 To UBound(AttachedParameters)
                If AttachedParameters(i).VarValue = "" Then
                    Cmd.Parameters(i).Value = Null
                Else
                    Cmd.Parameters(i).Type = AttachedParameters(i).DataType
                    Cmd.Parameters(i).Direction = AttachedParameters(i).Direction
                    Cmd.Parameters(i).Size = AttachedParameters(i).Size
                    Cmd.Parameters(i).Value = AttachedParameters(i).VarValue
                End If
            Next i
        End If
        ReDim AttachedParameters(0)
     rs.Open Cmd
     Exit Function
ErrorHandler:
    'store incoming values
    lErrNo = Err.Number
    sErrDesc = Err.Description
   
    'close down what we can and raise error
    On Error Resume Next
        Set rs = Nothing
    On Error GoTo 0
        ExecuteSp = False
        Set rs.ActiveConnection = Nothing
        Err.Raise lErrNo, OBJNAME, sErrDesc
End Function

Private Sub ObjectControl_Activate()

End Sub

Private Sub ObjectControl_Deactivate()
    'clean up any outstanding connections
    CloseConnection
    Set Cmd = Nothing
End Sub

Private Function ObjectControl_CanBePooled() As Boolean
    ObjectControl_CanBePooled = False
End Function


'Private Sub Class_Terminate()
''clean up any outstanding connections
'    CloseConnection
'    Set Cmd = Nothing
'End Sub


Rgrds Meer.
Avatar of AzraSound
AzraSound
Flag of United States of America image

What account does the server application run under?  Does it make any difference whether it is an administrator account or not?
Avatar of mirghani
mirghani

ASKER

hi AzraSound,
it's runnning under Administrator account, i didn't check it with another account.
What is the Relation of Security with the Run-Time Error i'm getting ????
Well, it was just a shot in the dark.  Your error didn't seem to make much sense.  The only difference between a server application and a library application is that the server application runs in its own dedicated process space.  I don't see how that relates to the provider not supporting a particular attribute.  So, I thought it might be a security issue, but it doesn't appear that way.

SQL Server OLEDB provider should definitely support multiple recordsets.  I'm not sure what the issue might be then.  If I have some time, I will try and do a little more research.
i thought that MyRs.NextRecordSet doesn't work on a Disconnected RecordSet i checked not to set MyRs=Nothing after retrieving the RecordSet but still i'm getting the Error !!!.
the amazing thing that Package switching from Server Application to Library Application makes NextRecordSet works Fine, even if the RecordSet is  Disconnected RecordSet!!!!!???
I think that it's not the matter of SQL Server OLEDB Provider as it was working fine before importing into Component Services .
Meer.
There may be another internal error within the stored proc itself relating to switching between server and library applications.  See this KB article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q313861
hi AzraSound,
i have already gone through this article without reaching to any solution,actually all my Stored Proc doesn't use RaiseError at all.
Could we say that this Error is not real(i mean it's behind another error but i'm getting this Run-Time Error!!!???).
Strange!!!
what is the main difference of library application and server application? what is the main factor that let me decide which application i should use?
Meer.
The main difference is that the server application will run within its own process space.
Taken from the MSDN Q189948
BUG: NextRecordset Fails on Remoted or Disconnected Records
Is there any Relation Betwaeen Library Application/Server Application and Disconnected Recordset?
Meer.
Yes, I had been across that article before, but your statement before about how you were using it successfully with disconnected recordsets threw me.  Really, all I have ever seen as being the big difference between a library application and the server application is that the server application runs in its own process space.  It also allows you to specify the identity to run under while the library application is confined to the current user credentials launching it (since it will run in the process space of the caller).

To be honest, I don't really know any real significant differences between the two.  The error you are getting, and the article I posted, seemed to be the closest thing I could get to.  I thought that perhaps the second recordset being returned from your stored proc, encountered an error somewhere due to the fact that it was running as a server application, but, I would have no idea why, especially when you specified the server application to run with administrative privileges.
my Compnents Frame work is as Follows:
the VB Client Form is Invoking RoutComponent then the RoutComponent Invoke another GetRsComponent then GetRsComponent Invoke DataAccessComponent then the DataAcessComponent Open the Stored Proc and return the Record Set..i hope this gives any idea.
if needed i could mail my code.
rgrds.
Meer.
i could do something which might be helpful to u,
what if i send u the RecordSet Watch Properties(Expression,Value,Type)in case of using Library Application or Server Application.
if that's helpful i would send it via mail.
Meer.
Are all of these components separate DLLs?  Are they all being included into your COM+ package?
yep,each component is seperate DLL and all r includes in one COM+ Package.
something more while Debugging, i put a breakpoint so as to view the RS Properties and i got the Following values for the RS with the Server App:
RS.ActiveCommand value is Nothing.
RS.ActiveConnection value is Nothing.
but with the Library App:
RS.ActiveCommand value Contains Value.
RS.ActiveConnection value Contains Value.

RS with Server APP:
Rs.LockType value is adLockBatchOptimistic.
RS with Library APP:
Rs.LockType value is adLockOptimistic.

RS with Server APP:
Rs.Status value is 8
RS with Library APP:
Rs.Status value is 0

but the rest values in both r similar.
i hope that makes sense.
Meer.
When did you check the values of these properties for the recordset?  Right after issuing the rs.Open command?
actually i issue the rs.open in my DataAccess Component and it retrieve the RS byRef back to the RoutComponent and then to the ClientForm, i checked the values in the ClientForm, actually i put the breackpoint at Rs.NextRecordSet,that's mean at the ClientForm and before executing Rs.NextRecordSet.
Meer.
Debug your dll above, too, and see when the ObjectControl object fires its DeActivate event.  It may be that in the server application scenario, the DeActivate event is firing prior to control returning back to your ClientForm.
how to Debug a Dll?
Usually, you can just open up your DLL project in a new instance of VB, and click 'Run' and it will put your component into run mode.  Set breakpoints and you should be able to step through your dll and your ClientForm project simultaneously.

P.S. In your DLL project, go to Project -> Properties and on the Debugging tab, ensure that "Wait for components to be created" is selected prior to running.
i opened my ActiveX Dll vb Project and i ensured that
"Wait for components to be created" is selected prior to running then i placed the breakpoint and run the project , then i open my ClientForm Project and placethe breakpoint and run it, i can step through the ClientForm Project but i couldn't do that in my VB Dll Project !!!
Hmm, it should work.  But, I just came across an article that would probably make debugging from VB a mute point anyways:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/htm/pgdebuggingapplications_1lyd.asp

states...
"While you are debugging using Visual Basic, COM+ treats the Visual Basic components as if they belong to a library application, even if the components are registered as belonging to a server application."


They do provide these instructions for debugging using VC++:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/htm/pgdebuggingapplications_4ar7.asp
hi azrasound,
morning i will continue as it's time to leave my office.
cu.
Meer.
i started debugging my Compiled Dll,if i didn't place a breakpoint it goes smooth with library Application but if i placed any break point at the time i'm invoking a method from another DLL i'm getting this run-time error '-2147023147(800706d5)'
Automation Error
The Security Context is invalid!!!!
Meer.
Hi dude,

A few questions:

1. Your connection string is using SSPI, okay when you setup the COM+ package, does the user under which it runs have valid credentials within SQL Server Login? To access the data you are trying to retrieve.

2. When you had it selected to Server Application, and it didn't work, that could mean that some version of maybe MDAC is not current compared to your version on your client. Make sure that the server has the same versions of ADO as your client does. Because when you selected Library Application, all application support comes from the client application itself. Plus when using library application you are removing all COM+ support, with the exception of role based security, because the runtime environment isn't used.

3. Try this bit of debugging, on the server set the DLLs back to running in Server Application. Set the MTSTransaction Mode of each class in your DLLs to 1 - No Transactions, then rebuild each DLL as Binary Compatible. Place into COM+ package, leave default Server Application. Export the package as a library application proxy to your client via the Export->MSI. Install the proxy, on the client. Now open up each DLL project on the server, place debug points in the classes, then CTRL+F5. Open up the client project on your client PC, compile and start, now the DLLs will break at the set breakpoints on being called from your client app.

4. In the general tab for the method (ExecuteSp) that does your db work, have you set the property 'Automatically deactivate this object when this method returns'? Because this would cause the connection object to be nuked, when the method ExecuteSp returns.







hi RainUK,
still i didn't Export My package as Library application Proxy as i'm still working on one Developement Machine,this problem occurs before i install the Proxy on any client.
even if i want to test my Server Applications in the Developement Machine should i install the Proxy ?
still the user who is developing the Application is the user who is testing(i'm and i'm administrator for both my Developement Machine and the SQL Server Machine).
while Debugging at the DLL's i'm getting the Above Automation Error 'The Security Context is invalid'!!!!
what about the error '3251'.
rgrds Meer.
'The Security Context is invalid' - okay try this

Okay run your application, and put a break point before you call the class method ExecuteSp. Now when the app code hits the debug point, ctrl+f5 the dll code, then step the client code and it should break in the class method ExecuteSp.
i can't get rid of the Automation Error,and i can't Debug Smoothly:-(
what about emailing my Code, maybe it's a problem of Design!!!
Meer.
ok,i'm trying to Debug....
Do u think it's something related to MDAC installations Conflicts?
i run the tool Component Checker into my Developement PC and i have got the Following Summary Report:

Item Name,Item Type,ErrorString,Status
msjtes40.dll,File,Field 'FileVersion' mismatch: Expect '4.00.2927.8' Got '4.00.4229.0',Error

Item Name,Item Type,ErrorString,Status
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\MSDASC\DefaultIcon,Registry,Field 'Value' mismatch: Expect 'C:\Program Files\Common Files\System\OLE DB\oledb32.dll^0' Got 'E:\Program Files\Common Files\System\OLE DB\oledb32.dll^0',Error

Item Name,Item Type,ErrorString,Status
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\MSDASC\shell\open\command,Registry,Field 'Value' mismatch: Expect 'Rundll32.exe C:\PROGRA~1\COMMON~1\System\OLEDB~1\oledb32.dll^OpenDSLFile %1' Got 'Rundll32.exe E:\PROGRA~1\COMMON~1\System\OLEDB~1\oledb32.dll^OpenDSLFile %1',Error


Is the errors found in my machine make any sense?
Meer.
no I'm not sure, I think it maybe the design of your component, is it necessary for you to return the recordset by reference and have to call .NextRecordset?
yep it's necessary.
why do u think that changing this way is the only solution?
Meer.
Okay can you show me the code that you use to instantiate and call the method from your client code please. Try give me the whole subroutine that its in please and how you use the recordset.
okay here is the Client Code:
Function CallMiddle(xMiddleInterface As MiddleInterfaces) As Boolean
 Dim Subscribers As ROUTBO.cROUTBO
 Dim Result As Boolean
 Select Case xMiddleInterface    '   Choose The Appropriate Interface :
       Case xGetSiteID
            Set Subscribers = New ROUTBO.cROUTBO
            Set rs = New ADODB.Recordset
            Result = Subscribers.GetSiteRegionInfo(rs)
            CallMiddle = Result
       Case X
             ......
       Case Y
             ......      
End Function

Sub GetSiteRegionInfo()
                If CallMiddle(xGetSiteID) Then
                If rs!ErrFlag = 1 Then
                    MsgBox rs!ErrMSG
                    Exit Sub
                Else
                    Set rs = rs.NextRecordset
                End If
                        If rs.RecordCount > 0 Then
                            GetIndex rs!SiteID, cboSiteID 'GetIndex Is Sub Used To Fill CombobBox
                            GetIndex rs!ExchangeID, cboExchangeID'GetIndex Is Sub Used To Fill CombobBox
                         End If
            Else
                        MsgBox "Error In Retrievin RecordSet"
            End If
                  Set Subscribers = Nothing
                  Set rs = Nothing
   End Sub


Private Sub Form_Load()
   GetSiteRegionInfo
End Sub

Here is the ROUTBO.cROUTBO Code:
Global Declarations:
Dim AttachedParameters() As PrjDataAccess.paraList

Private Const mVarChar = adVarChar
Private Const mInt = adInteger
Private Const mTinyInt = adTinyInt
Private Const mSmallInt = adSmallInt
Private Const mDateTime = adDBTimeStamp
Private Const mChar = adChar
Private Const mMoney = adCurrency
Private Const mGUID = adGUID
'Global Objects(Components)
Dim obj_SRBO As SRBO.cSRBO 'Services Registration Business Object

Dim obj_SSBO As SSBO.cSSBO 'Subscriber Services Business Object

Dim obj_DNSBO As DNSBO.cDNSBO 'DN Services Business Object

Dim obj_FCBO As FBO.cFBO 'Financial Business Object

Dim obj_GMBO As GMBO.cGMBO 'Global Methods Business Object

Dim obj_CSBO As CSBO.cCSBO 'Configuration&Settings Business Object

Dim obj_NWBO As NWBO.cNW 'NetWork Business Object

Dim obj_REPBO As REPBO.cREPBO 'Report Business Object
Dim Count As Integer

'Instantiate_Object obj_REPBO, PrjREP.clsREP

Public Sub Instantiate_Object(SourceObj_Name As Object, DestObj_Name As String)
    Set SourceObj_Name = CreateObject(DestObj_Name)
 End Sub

Public Sub Release_Object(Obj_Name As Object)
 Set Obj_Name = Nothing
End Sub

Public Function GetSiteRegionInfo(BO_RS As ADODB.Recordset) As Boolean
Dim DO_RS As ADODB.Recordset

GetSiteRegionInfo = False

' Execute stored procedure and return recordset
Instantiate_Object obj_GMBO, "GMBO.cGMBO"
ReDim AttachedParameters(0)
    If obj_GMBO.GetSiteRegionInfo(DO_RS, AttachedParameters()) Then
     Set BO_RS = DO_RS
     Set DO_RS = Nothing
     GetSiteRegionInfo = True
    End If
Release_Object obj_GMBO
End Function

Here is the GMBO.cGMBO Code:
'Global Declarations
Private Const scGET_SITEREGIONINFO = "ssGetSiteRegionInfo"
Dim obj_Da As PrjDataAccess.cDataAccess

Public Function GetSiteRegionInfo(BO_RS As ADODB.Recordset, ByRef AttachedParameters() As paraList) As Boolean
Dim DO_RS As ADODB.Recordset

GetSiteRegionInfo = False

' Execute stored procedure and return recordset
'Set obj_Da = New PrjDataAccess.cDataAccess
Set obj_Da = CreateObject("PrjDataAccess.cDataAccess")
    If obj_Da.ExecuteSp(DO_RS, scGET_SITEREGIONINFO, Remote_Conn, AttachedParameters()) Then
     Set BO_RS = DO_RS
     Set DO_RS = Nothing
     GetSiteRegionInfo = True
    End If
Set obj_Da = Nothing
End Function

Here is the Data Access Code:

Option Explicit
Implements ObjectControl
Implements IObjectConstruct

Public Type paraList
  VarValue As Variant
  DataType As Variant
  Size As Integer
  Direction As Integer
End Type

Public Enum QUERYOPTIONS
    QO_UseTable = ADODB.adCmdTable
    QO_UseText = ADODB.adCmdText
    QO_UseStoredProc = ADODB.adCmdStoredProc
    QO_UseUnknown = ADODB.adCmdUnknown
    QO_UseDefault = -1
End Enum

Public Enum LOCKTYPES
    LT_ReadOnly = ADODB.adLockReadOnly
    LT_Optimistic = ADODB.adLockOptimistic
    LT_Pessimistic = ADODB.adLockPessimistic
    LT_BatchOptimistic = ADODB.adLockBatchOptimistic
End Enum

Public Enum CURSORLOCATION
    CURL_Client = ADODB.adUseClient
    CURL_Server = ADODB.adUseServer
End Enum

Public Enum CURSORTYPE
    CURT_OpenKeySet = ADODB.adOpenKeyset
End Enum

Public Enum E_ConnType
     Local_Conn = 0
     Remote_Conn = 1
End Enum

Private Const OBJNAME = "DataAccess Component"
Private Cnn As ADODB.Connection
Private Cmd As ADODB.Command
Private Outernalconnstr As String

'Connection String that Contains the Lookups Data Source
'Private Const Internalconnstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SBOS EH;Data Source=SBOS_SERVER"
Private Const Internalconnstr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SBOS EH;Data Source=SBOS_SERVER"

Private Sub IObjectConstruct_Construct(ByVal pCtorObj As Object)
Dim mConstructString As IObjectConstructString
Set mConstructString = pCtorObj
Outernalconnstr = mConstructString.ConstructString
'MsgBox Outernalconnstr
End Sub

'=======================================================================
'Synopsis:              This function closes a previously opened connection
'Function input:        none
'Function output:       Returns TRUE if the function succeeds
'=======================================================================
Public Function CloseConnection() As Boolean
    If Not Cnn Is Nothing Then
'        Cnn.Close
        Set Cnn = Nothing
    End If

End Function

'=======================================================================
'Synopsis:              This function creates and opens a new connection
'Function input:        Connect string
'Function output:       Returns TRUE if the function succeeds
'=======================================================================
Public Function OpenConnection(ByVal Connect As String) As Boolean
Dim lErrNo      As Long
Dim sErrDesc    As String

    'assume failure
    OpenConnection = False
   
    'enable error handler
    On Error GoTo ErrorHandler
   
    'establish the transaction if Connection String specified
    If Connect <> vbNullString Then
        CloseConnection
        Set Cnn = New ADODB.Connection
        Cnn.Open Connect
        'MsgBox "Connected"
    End If
   
    OpenConnection = True
    Exit Function
   
ErrorHandler:

    'store incoming values and raise error
    lErrNo = Err.Number
    sErrDesc = Err.Description
    On Error GoTo 0
    Err.Raise lErrNo, OBJNAME, sErrDesc

End Function

'This Function Executes a StoredProcedure and Return RS ByRefrence and Boolean
'if Done Successfully.
Public Function ExecuteSp(ByRef rs As ADODB.Recordset, ByVal spName As String, ByVal ConnType As Integer, ByRef AttachedParameters() As paraList) As Boolean
Dim i As Long
Dim lErrNo      As Long
Dim sErrDesc    As String

On Error GoTo ErrorHandler
   ' If RS Is Nothing Then
        Set rs = New ADODB.Recordset
   ' End If
 
    Set Cmd = New ADODB.Command

    If rs.State = adStateOpen Then
        rs.Close
    End If
With rs
    .CURSORTYPE = CURT_OpenKeySet
    .LockType = LT_Optimistic
    .CURSORLOCATION = CURL_Client
End With
  ExecuteSp = True
    'Is used for DataBase Connection String
    If ConnType = Remote_Conn Then
      OpenConnection (Outernalconnstr)
    Else
    'Is used for Cashing Connection String
      OpenConnection (Internalconnstr)
    End If
     Cmd.CommandTimeout = 3600
     Cmd.ActiveConnection = Cnn
     Cmd.CommandType = QO_UseStoredProc
     Cmd.CommandText = Trim(spName)
        If (UBound(AttachedParameters) > 0) Then
           'Cmd.Parameters.Refresh
            For i = 1 To UBound(AttachedParameters)
                If AttachedParameters(i).VarValue = "" Then
                    Cmd.Parameters(i).Value = Null
                Else
                    Cmd.Parameters(i).Type = AttachedParameters(i).DataType
                    Cmd.Parameters(i).Direction = AttachedParameters(i).Direction
                    Cmd.Parameters(i).Size = AttachedParameters(i).Size
                    Cmd.Parameters(i).Value = AttachedParameters(i).VarValue
                End If
            Next i
        End If
        ReDim AttachedParameters(0)
     rs.Open Cmd
     Exit Function
ErrorHandler:
    'store incoming values
    lErrNo = Err.Number
    sErrDesc = Err.Description
   
    'close down what we can and raise error
    On Error Resume Next
        Set rs = Nothing
    On Error GoTo 0
        ExecuteSp = False
        Set rs.ActiveConnection = Nothing
        Err.Raise lErrNo, OBJNAME, sErrDesc
End Function

Private Sub ObjectControl_Activate()

End Sub

Private Sub ObjectControl_Deactivate()
    'clean up any outstanding connections
    CloseConnection
    Set Cmd = Nothing
End Sub

Private Function ObjectControl_CanBePooled() As Boolean
    ObjectControl_CanBePooled = False
End Function

Then we have Our SQL DataBase.

Note: the Refrences is as Follows:-
-Client have a Refrence for the ROUTBO Component.
-ROUTBO ....................... GMBO Component and the DataAccess Component(as the ROUTBO is using ParaList in DataAccess Component).
-GMBO have a Refrence for the DataAccess Component .

Tools used for Coding:
-VB 6.0
-i'm using MDAC 2.7 as a refrence for all my components.
-Win2k Advanced Server as OS.
-SQL2k SP3(the SP3 uses MDAC 2.7).

that's all.
rgrds Meer.


First recompile your dlls, and place into COM+ , now with your client code :

All your calls to COM+ components should be late bound (So for a start change all those dims to As Object and all your instantiations to Set object = CreateObject("YourCOMDLLName.className")), otherwise when you deploy on the client it won't work properly. e.g.

Dim obj_Da As object  
Set obj_Da = CreateObject("PrjDataAccess.cDataAccess")

Now in this part of your code:->

If obj_Da.ExecuteSp(DO_RS, scGET_SITEREGIONINFO, Remote_Conn, AttachedParameters()) Then
    Set BO_RS = DO_RS
    Set DO_RS = Nothing
    GetSiteRegionInfo = True
End If
Set obj_Da = Nothing

Here you pass DO_RS to the SP by reference, now when it comes back filled out you set it to BO_RS, but then you set DO_RS to nothing.

Therefore BO_RS will be set to nothing also. Because you do :

Set BO_RS = DO_RS

Does NOT mean that you are making a copy of the recordset, you are simply assigning a pointer to DO_RS.

Okay where is the call to NextRecordSet ?
I can't see it in your code calls. Anyhow, if you use a disconnected recordset you cannot use NextRecordset.
>>Therefore BO_RS will be set to nothing also

Actually, this is not true because what you have is another object instance that points to the same memory space.  When you set one to the other, you are just telling them to reference the same memory space.  When you set one of them to nothing, you just remove one of the references to that memory space.  VB will see there is still an object pointing to that memory, and will not free it.  Simple example with class object with single property:


Private Sub Command1_Click()
    Dim c       As Class1
   
   
    Set c = New Class1
    Call KillLocal(c)
   
    MsgBox c Is Nothing
    MsgBox c.Value
End Sub


Sub KillLocal(cObject As Class1)
    Dim c2      As New Class1
   
    Call AlterClass(c2)
    Set cObject = c2
    Set c2 = Nothing
End Sub


Sub AlterClass(cObject As Class1)
    cObject.Value = 5
End Sub



I would like to see what would happen if you didnt destroy the connection in your Deactivate event of object control.  I am just wondering if that is firing prior to you getting your recordset back.

At any rate, I'm outta here for a long vacation...hope you guys get it resolved.
hi RAINUK,
i followed all ur Recommendation in Creating the Object in all my components and even in my client Project.
Dim MYCLS As Object
Set MYCLS= CreateObject("Object Name")
but i'm with AzraSound in his last comment about the RecordSet pointing to the Memory Spcae.
and Import my DLL's as COM+ Package i tried the Client Project in both Cases:-
Library Application:
i'm getting this Run-Time Error
'Method '~' of object '~' failed'
Previously it was working with the Posted Code:-(

Server Application:
i'm getting this Run-Time Error '-2147023170(800706be)'
Automation Error
'The Remote Procedure Call Failed'.

Rgrds Meer.

Dim MYCLS As Object
Set MYCLS= CreateObject("Object Name")

You have to specify the class you are instantiating e.g.

Dim MYCLS As Object
Set MYCLS= CreateObject("ObjectName.ClassName")
yep RainUK that's what i did,
Dim MYCLS As Object
Set MYCLS= CreateObject("ROUTBO.cROUTBO")
i said "Object Name" just as general.
Meer.
Dim MYCLS As Object
Set MYCLS= CreateObject("Object Name")

You have to specify the class you are instantiating e.g.

Dim MYCLS As Object
Set MYCLS= CreateObject("ObjectName.ClassName")
well meer I am confused as to what is going on... need some time to think about it. Will let you know if I get a brainwave
hi RainUK,
i installed Visual Studio SP5 and reinstall my MDAC2.7,then i used the New Operator to Create the Objects.
i recompile all DLL's and Import the into Com+ Component Service.
now my Project works Properly with my components only in case of using the Activation Type "Library Applicatio".
but if i switch to Server Application i get again the error:
Run-Time Error '3251' Current Provider does not support returning multiple recordsets...
Strange!!!
Meer.
while Debugging my DataAccess Component which contain the Method ExecuteSP i put a BreakPoint at the Line RS.Open Cmd, i recognized that the RecordSet Retrieved Succfully then immediately i wrote this Line:
Set Rs = Rs.NextRecordSet
i found the RecordSet in my Watch Window as if it Set to Nothing!!!
How Comes Set Rs = Rs.NextRecordSet works as Set Rs=Nothing.
This Happens in the Case of Server Application Only but in Library Application it Works Fine.

Note: i can't proceed my work using Library Application it's an Enterprise System there should be a Middle-Tier Server Handling all the Clients Requests:-(
rgrds.
Meer.
sorry, the last Debugging Result will be Little Modified as follows:
Set Rs = Rs.NextRecordset works Fine at the Server Side(DataAccess Component)but once Rs.NextRecordSet is done at the Client Side it raise the error'3251'.
i think it's a problem with the RecordSet Properties to work with it's Properties in the Clien Side.
we r about to solve it isn't it?
rgrds Meer.
while searching i got this Link:
http://dbforums.com/arch/66/2002/11/576455
what's ur Openion it seems similar to mine!!!
Meer.
Is Marchalling RecordSet ByRef in Server Application Type might not Marchalle all the ADO RecordSet Properties?
Really it's amazing:-(
Meer.
Now, at my own risk and peril, I thought I have read somewhere under COM+ you cannot have secondary interfaces. That is: you should not be able to use the "Implement" instruction.
Correct me if I am wrong...
Cesare Imperiali
hi Cimperiali,
i'm using Secondary Interfaces  Implements ObjectControl,
Implements IObjectConstruct so as to get the variable string and in this case it's my connection string,it's a feature provided by COM+.
my problem is not on what u r pointing for.
if u need to make my problem more clear i will do it.
Meer.
i tried with another way rather than returning RecordSet ByRef, i changed my Method to return value of ADODB.RecordSet instead of Boolean but still i'm getting the error!!!
Meer.
A request for a refund has been made.  Experts, you have 72 hours to object.  If no objections, I intend to honor the request.

SpideyMod
Community Support Moderator @Experts Exchange
ASKER CERTIFIED SOLUTION
Avatar of SpideyMod
SpideyMod

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
hi RainUk, AzraSound,
pls try to go through this Ques:

https://www.experts-exchange.com/questions/20799824/Client-DeadLock-in-COM.html

Meer.