• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 957
  • Last Modified:

sp_OAMethod fails when calling ADODB functions in runtime from a VB6 DLL

The following T-SQL code works when the VB6 ActiveX DLL is running in Debug mode within the VB6 IDE, but not when running from the compiled DLL. I have tried various things most recently changing to late-binding (clutching at straws) so please dont add a comment like 'shouldnt you be using early-binding because its much better'. Please also dont add comments like 'isnt that a bit overcomplicatyed for what you want to do?'. I Know its overcomplicated - But if I can get this working it will allow me to then go on to do what I really want to do, which is retrieve an >8000 character XML document from a text column, manipulate it then put it back into the text column.

I have two other related questions posted on this; Answer this one and I'll close all three in your favour giving you an instant  1500 points. HELP!

T-SQL Code:

begin
      DECLARE @errorSource VARCHAR(8000)
      DECLARE @errorDescription VARCHAR(8000)
      declare @oSolveweb INT, @hr INT, @oResult VARCHAR(1000), @INstanceId VARCHAR(36), @FieldName VARCHAR(30)
      EXEC @hr = sp_OACreate 'SolvewebTest.Test', @oSolveweb OUTPUT, 5
      PRINT @hr
      IF @hr <> 0 BEGIN
            EXEC sp_displayoaerrorinfo @oSolveweb, @hr
      END
      PRINT @oSolveweb
      EXEC @hr = sp_OAMethod @oSolveweb, 'Test', @oResult OUTPUT, '5C078696-C0D6-4F8D-9F97-0056EB62721C'
      PRINT @hr
      IF @hr <> 0 BEGIN
            EXEC sp_displayoaerrorinfo @oSolveweb, @hr
      END
      PRINT @oResult
      EXEC @hr = sp_OADestroy @oSolveweb
      PRINT @hr
end

VB6 Class:

Option Explicit

Private Sub OpenDB(ByRef oDBConn As Object)
    If oDBConn Is Nothing Then
        Set oDBConn = CreateObject("ADODB.Connection")
        oDBConn.Open "Provider=sqloledb;Data Source=localhost;Initial Catalog=mydb;User Id=sa;Password=password;"
        'oDBConn.Open "Provider=sqloledb;Data Source=localhost;Initial Catalog=mydb;Integrated Security=SSPI;"
    End If
End Sub
Private Sub CloseDB(ByRef oDBConn As Object)
    If Not oDBConn Is Nothing Then
        oDBConn.Close
        Set oDBConn = Nothing
    End If
End Sub

Private Sub GetContext(ByVal objectId As String, ByRef context As String)

    Dim oDBConn As Object
    Dim oDBCmd As Object
    Dim oCmdStream As Object
    Dim oOutStream As Object
    Dim strQuery As String
    Dim strXml As String
   
    Set oDBCmd = CreateObject("ADODB.Command")
   
    OpenDB oDBConn
    oDBCmd.ActiveConnection = oDBConn
   
    strQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
    strQuery = strQuery & "<sql:header nullvalue='NULL'>"
    strQuery = strQuery & "     <sql:param name='ObjectId'>NULL</sql:param>"
    strQuery = strQuery & "</sql:header>"
    strQuery = strQuery & "<sql:query>EXEC object_internal_identify_forCom @ObjectId</sql:query>"
    strQuery = strQuery & "</ROOT>"
   
    Set oCmdStream = CreateObject("ADODB.Stream")
    oCmdStream.Open
    oCmdStream.WriteText strQuery, adWriteChar
    oCmdStream.Position = 0
   
    oDBCmd.NamedParameters = True
    'oDBCmd.CommandType = adCmdText
    Set oDBCmd.CommandStream = oCmdStream
    oDBCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
    oDBCmd.Parameters.Append oDBCmd.CreateParameter("@ObjectId", adVarChar, adParamInput, 36, objectId)
    Set oOutStream = CreateObject("ADODB.Stream")
    oOutStream.Open
    oDBCmd.Properties("Output Stream") = oOutStream
    oDBCmd.Execute , , adExecuteStream
    'oOutStream.Position = 0
    strXml = oOutStream.ReadText '(-1)
    oOutStream.Close
    Set oOutStream = Nothing
    oCmdStream.Close
    Set oCmdStream = Nothing
    Set oDBCmd = Nothing
    CloseDB oDBConn
   
    context = strXml
   
End Sub

Public Function Test(strInput As String) As String
   
    Dim strReturn As String
    GetContext strInput, strReturn
    Test = strReturn
   
End Function
0
Solveweb
Asked:
Solveweb
1 Solution
 
arbertCommented:
What messages do you get when you try and use the sp_OA procs--return codes?
0
 
SolvewebAuthor Commented:
sp_OACreate: Success (0)
sp_OAMethod: Rather unhelpful 'One or more errors occured while processing command'

Thanks Brett
0
 
SolvewebAuthor Commented:
I have solved this myself.

If anyone is interested - As soon as you start trying to execute a SQLXML dialect ADODB command the DLL can no longer be called by sp_OACreate, even though the call is legal and by the book, and works fine when called externally. This must be a bug in SQLXML.

By changing the ADODB call to a straight oDBCmd.CommandType = adCmdStoredProc I was able to get the DLL working from within an sp_OACreate call.

Can I award the points to myself?
0
 
Anthony PerkinsCommented:
>>Can I award the points to myself?<<
Sure.  It is called a refund :)  See here:
I answered my question myself. What do I do?
http://www.experts-exchange.com/help.jsp#hi70
0
 
GhostModCommented:
PAQd and 500 points refunded

GhostMod
CS Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now