ADO 2.8 - Automation Error

JackieLee
JackieLee used Ask the Experts™
on
I have a SQL Server 2008 stored procedure that receives two xml strings as nText parameters.   I get an automation error when I attempt to execute it from an ADO 2.8 command object (see code snipet).

The SP works fine with the same parameters when executed from the sql managent studio and I know the connection properties are all correct because I can get it to work with simple queries.

I am attempting to execute the code using VBA in an excel 2007 spreadsheet.  The code is virtually identical to VB6 code.

What am I doing wrong here?
Private Sub CommandButton1_Click()
 
    Dim cmd As ADODB.Command
    Dim cn As ADODB.Connection
    
    Set cn = New ADODB.Connection
    
    Dim strConnectionString As String
    
    strconnectinstring = "Provider=SQLOLEDB.1;Persist Security Info=true;Initial Catalog=raiseV5.0;Data Source=(Local);Network Library=dbmssocn"
   
    With cn
        .Provider = "SQLOLEDB"
        .IsolationLevel = adXactIsolated
        .Mode = adModeReadWrite
        .ConnectionTimeout = 30
        .Open strconnectinstring, "devdba", "DASSAULT"
    End With
    
    Set cmd = New ADODB.Command
    
    Dim strStepXML As String
    Dim strCheckXML As String
    strStepXML = "<xml><steps></steps></xml>"
    strCheckXML = "<xml><checks></checks></xml>"
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "ATEExecuteTest"
    
    Dim p1 As ADODB.Parameter
    Dim p2 As ADODB.Parameter
    
    Set p1 = cmd.CreateParameter("StepsXML", adLongVarWChar, adParamInput, Len(strStepXML), strStepXML)
    Set p2 = cmd.CreateParameter("ChecksXML", adLongVarWChar, adParamInput, Len(strCheckXML), strCheckXML)
    
    cmd.Parameters.Append p1
    cmd.Parameters.Append p2
    
    cmd.ActiveConnection = cn
    
    Dim rs As ADODB.Recordset
    
    Set rs = cmd.Execute
 
    Set cmd = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I think the size of the parameters should be double because I'm using NText.  But I've treid it with the following definition and it didn't work then either.  same error.

    Set p1 = cmd.CreateParameter("StepsXML", adLongVarWChar, adParamInput, Len(strStepXML) * 2, strStepXML)
    Set p2 = cmd.CreateParameter("ChecksXML", adLongVarWChar, adParamInput, Len(strCheckXML) * 2, strCheckXML)
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
please change the procedure to use nvarchar(max) instead of ntext.
should simplify the problem

Author

Commented:
I can't.  i need more than 8000 characters.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Where I've researched this online I've come across people who have corrected this issue by using the adLongVarWChar datatype with nText.  it should work according to those examples.

As an experiment I just changed the data type of the stored procedure to VarChar(8000) and specified a type of adLongVarChar in the vb code and I still get the same automation error.

Author

Commented:
Also tried adVarChar with Varchar(8000) and again I get the same error.

Author

Commented:
OK, the parameters are definitely a red herring.  I just removed the parameters from the stored procedure and changed the code to not include any parameters and I still get the automation error!
Private Sub CommandButton1_Click()
 
    Dim cmd As ADODB.Command
    Dim cn As ADODB.Connection
    
    Set cn = New ADODB.Connection
    
    Dim strConnectionString As String
    
    strconnectinstring = "Provider=SQLOLEDB.1;Persist Security Info=true;Initial Catalog=raiseV5.0;Data Source=(Local);Network Library=dbmssocn"
   
    With cn
        .Provider = "SQLOLEDB"
        .IsolationLevel = adXactIsolated
        .Mode = adModeReadWrite
        .ConnectionTimeout = 30
        .Open strconnectinstring, "devdba", "DASSAULT"
    End With
    
    Set cmd = New ADODB.Command
    
    Dim strStepXML As String
    Dim strCheckXML As String
    strStepXML = "<xml><steps></steps></xml>"
    strCheckXML = "<xml><checks></checks></xml>"
    
    
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "ATEExecuteTest"
    
    Dim p1 As ADODB.Parameter
    Dim p2 As ADODB.Parameter
    
    'Set p1 = cmd.CreateParameter("StepsXML", adVarChar, adParamInput, 8000, strStepXML)
    'Set p2 = cmd.CreateParameter("ChecksXML", adVarChar, adParamInput, 8000, strCheckXML)
    
    
    'Set p1 = cmd.CreateParameter("StepsXML", adLongVarWChar, adParamInput, Len(strStepXML) * 2, strStepXML)
    'Set p2 = cmd.CreateParameter("ChecksXML", adLongVarWChar, adParamInput, Len(strCheckXML) * 2, strCheckXML)
    
'    cmd.Parameters.Append p1
 '   cmd.Parameters.Append p2
    
    cmd.ActiveConnection = cn
    
    Dim rs As ADODB.Recordset
    
    Set rs = cmd.Execute
 
    Set cmd = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Open in new window

Author

Commented:
I know it isn't a connection problem because when I change the CommandType property to adCmdText and issue a simple sql query as the CommandText it works fine.  Doesn't seem to like stored procedures.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>I can't.  i need more than 8000 characters.
nvarchar(max) is not nvarchar(4000). it can handle up to 2GB of data!

Author

Commented:
Ok.  but it doesn't make any difference. I get the same error.
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
given that you found that even with the parameter removed, the error persiting, it's normal :)

I see this (a dot in the database name):
Initial Catalog=raiseV5.0

can you try this instead, please:
Initial Catalog=[raiseV5.0]

Author

Commented:
My instance of SQL management studio was connected to the wrong database.  My stored procs therefore did not exist in the db that ADO was connecting to.  It worked when I used a simple query because the table referenced was an old one that exists on both dbs.

Thanks for your help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial