What is the ado parameter type (excel) for sql server stored procedure parameter type varchar(max)

I am attempting to pass xml from MS Excel to a SQL Server stored procedure. I have tested on the SQL Server side that xml can be stored in a varchar(MAX) and the only parameter type that is acceptable on the Excel side is adBSTR.

When I run the execute on the command it just passes the first char of the script.

Is there another type I should use or is there something I am doing wrong. I include a sample code
Global gconnADO As ADODB.Connection
Global gconnCMD As ADODB.Command

Private Sub CallStoredProc()
    Dim prmData As ADODB.Parameter
    Dim prmSuccess As ADODB.Parameter
    Dim prmMessage As ADODB.Parameter

    Dim strMessage As String
    Dim blSuccess As Boolean

    Dim Err_Number As Long
    Dim Err_Desc As String
    Dim Err_Source As String

    On Local Error GoTo ErrorCallStoredProc

'   Open ADO connection
    OpenConnection

'   Write Data to SQL Server
'   Define a Command object for a stored procedure.
    Set gconnCMD = New ADODB.Command
    gconnCMD.ActiveConnection = gconnADO
    gconnCMD.CommandText = "testStoredProc"
    gconnCMD.CommandType = adCmdStoredProc
    gconnCMD.CommandTimeout = 0

'   Set up new parameter for the stored procedure.
    Set prmData = gconnCMD.CreateParameter("@pv_Data", adBSTR, adParamInput)
    Set prmSuccess = gconnCMD.CreateParameter("@pv_Success", adBoolean, adParamOutput)
    Set prmMessage = gconnCMD.CreateParameter("@pv_Message", adBSTR, adParamOutput)
    
    gconnCMD.Parameters.Append prmData
    gconnCMD.Parameters.Append prmMessage
    gconnCMD.Parameters.Append prmGPK

'   This function returns a valid xml of type String
    prmData.Value = ReturnXML()

'   Execute the command.
    Set rsCommand = gconnCMD.Execute

'   Get returns from Execute
    blSuccess = prmSuccess.Value
    If Not IsNull(prmMessage.Value) Then
        strMessage = prmMessage.Value
        Err.Raise 50001, strMessage
    End If

'   Release Resources
    Set gconnXLADO = Nothing
    
    Exit Sub

ErrorCallStoredProc:
    Err_Number = Err.Number
    Err_Desc = Err.Description
    Err_Source = Err.Source

'   Release Resources
    Set gconnXLADO = Nothing
  
    MsgBox "Error Writing to Stored Procedure : " & Err_Number & ":" & Err_Desc & vbCrLf & "Source :" & Err_Source
End Sub

Open in new window

Vincent_MonaghanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
>>I have tested on the SQL Server side that xml can be stored in a varchar(MAX) <<
You can also store Xml as an Xml data type.

Here is the data type mapping for the newer data types:
varchar(MAX): adLongVarChar
nvarchar(MAX): adLongVarWChar
varbinary(MAX): adLongVarBinary
xml: adLongVarChar

Just make sure you use the SQL Server Native Client.
0
Vincent_MonaghanAuthor Commented:
To make the code work I used the size property of the ado parameter as outlined in the code.

I could not get your suggestion to work using the adLongVarChar

My connection string is :

Provider=SQLNCLI.1;Persist Security Info=True;User ID=sa;Initial Catalog=XXXX;Data Source=VINCENTPC\SQLEXPRESS;Password=xxx


Global gconnADO As ADODB.Connection
Global gconnCMD As ADODB.Command

Private Sub CallStoredProc()
    Dim prmData As ADODB.Parameter
    Dim prmSuccess As ADODB.Parameter
    Dim prmMessage As ADODB.Parameter

'********************************* NEW
    Dim strXML as String
    Dim iLenXML as Long
'********************************* NEW

    Dim strMessage As String
    Dim blSuccess As Boolean

    Dim Err_Number As Long
    Dim Err_Desc As String
    Dim Err_Source As String

    On Local Error GoTo ErrorCallStoredProc

'   Open ADO connection
    OpenConnection

'   Write Data to SQL Server
'   Define a Command object for a stored procedure.
    Set gconnCMD = New ADODB.Command
    gconnCMD.ActiveConnection = gconnADO
    gconnCMD.CommandText = "testStoredProc"
    gconnCMD.CommandType = adCmdStoredProc
    gconnCMD.CommandTimeout = 0

'   Set up new parameter for the stored procedure.
    Set prmData = gconnCMD.CreateParameter("@pv_Data", adBSTR, adParamInput)
    Set prmSuccess = gconnCMD.CreateParameter("@pv_Success", adBoolean, adParamOutput)
    Set prmMessage = gconnCMD.CreateParameter("@pv_Message", adBSTR, adParamOutput)
    
    gconnCMD.Parameters.Append prmData
    gconnCMD.Parameters.Append prmMessage
    gconnCMD.Parameters.Append prmGPK

'   This function returns a valid xml of type String

'********************************* NEW
    strXML = ReturnXML()
    iLenXML = Len(strXML)

    prmData.Size = iLenXML
    prmData.Value = strXML

'   Need to size the output also; used a default of 500
    prmMessage = 500
'********************************* NEW

'********************************* DELETE
'   prmData.Value = ReturnXML()
'********************************* DELETE

'   Execute the command.
    Set rsCommand = gconnCMD.Execute

'   Get returns from Execute
    blSuccess = prmSuccess.Value
    If Not IsNull(prmMessage.Value) Then
        strMessage = prmMessage.Value
        Err.Raise 50001, strMessage
    End If

'   Release Resources
    Set gconnXLADO = Nothing
    
    Exit Sub

ErrorCallStoredProc:
    Err_Number = Err.Number
    Err_Desc = Err.Description
    Err_Source = Err.Source

'   Release Resources
    Set gconnXLADO = Nothing
  
    MsgBox "Error Writing to Stored Procedure : " & Err_Number & ":" & Err_Desc & vbCrLf & "Source :" & Err_Source
End Sub

Open in new window

0
Anthony PerkinsCommented:
>>I could not get your suggestion to work using the adLongVarChar<<
It looks like you have not set the correct Provider (it should be SQLNCLI10) or set the DataTypeCompatibility=80
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Vincent_MonaghanAuthor Commented:
I changed the provider (SQL Server 2008 R2) to:

Provider=SQLNCLI10.1;Server=VINCENTPC\SQLEXPRESS;Initial Catalog=XXXXX;User ID=sa;Password=xxxxxx;

But, still cannot get adLongVarChar to work - it says the parameter is inconsistent.

adBSTR does work. What is the difference between the types?
0
Anthony PerkinsCommented:
>>But, still cannot get adLongVarChar to work <<
But you did not set the DataTypeCompatibility=80.  I am obviously not communicating appropriately so here is an article that should fill in the gaps:
Using ADO with SQL Server Native Client
http://msdn.microsoft.com/en-us/library/ms130978.aspx

>>adBSTR does work<<
If that works for you, than more power to you and time to move on to your next challenge.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vincent_MonaghanAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.