asked on

ADO 2.8 - Automation Error

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

JackieLee

ASKER

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]

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

JackieLee

ASKER

I can't. i need more than 8000 characters.

JackieLee

ASKER

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.

JackieLee

ASKER

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

JackieLee

ASKER

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

JackieLee

ASKER

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]

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

JackieLee

ASKER

Ok. but it doesn't make any difference. I get the same error.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JackieLee

ASKER

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