Link to home
Start Free TrialLog in
Avatar of Data-Man
Data-ManFlag for United States of America

asked on

How do I use the new datatype VARCHAR(MAX) as an output parameter in ADO using VBA?

Hello everyone,
   I'm having a little bit of an issue and need a some help.

   Here's the situation.  I have an Access database (2003) which connects to SQL Server 2005 using ADO 2.8.  I just finished giving them the capability to send HTML emails and that works great as long as the email isn't over 8000 characters.  Well guess what, they want to send one today which is just over 11000 characters.  
    I have a procedure that takes their email and embeds it between the company header and footer and then sends it back to them so they can view it on the client before sending.  I have no problem sending up an email that is larger than 8K using the adLongVarWChar when I create the parameter.  My problem is in the output parameter.  If I set it to adLongVarWChar, I get an error telling me that the datatype is a deprecated large datatypes and that they can't be used as output parameters.  

    How do I return my VARCHAR(MAX) from my command object?

    I did a little reading and came across this wonderful paper from Microsoft and thought it was the answer to my prays, but low and behold, it doesn't seem to work. (

    I've modified my connection string to use the SQL Native Client and set the compatibility flag but it still doesn't work.
   Here is my code.

    With cmdTemp
        .ActiveConnection = cn
        .CommandText = "pEmailCustomMailingView"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@strProject", adVarChar, adParamInput, 12, Form_frmProspectCustomMailing.cboProject.Value)
        .Parameters.Append .CreateParameter("@strSubject", adVarChar, adParamInput, 250, Left(Me.txtSubject.Value, 250))
        .Parameters.Append .CreateParameter("@strHTML", adLongVarWChar, adParamInput, -1, Me.txtEmailBody.Value)
        .Parameters.Append .CreateParameter("@strProc", adVarChar, adParamInput, 200, Form_frmProspectCustomMailing.Tag)
        .Parameters.Append .CreateParameter("@strHTMLOutput", adLongVarWChar, adParamOutput, 25000)
        Set rstTemp = .Execute
        Debug.Print rstTemp.RecordCount
    End With

    I'm heading out of the office, but will be back in a few hours to try and wrap this up and upload the new version for them.  I have to catch a plane in the morning.  

Thanks in advance,
Avatar of chapmandew
Flag of United States of America image

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim str As String

cn.Open "Provider=SQLNCLI;Data Source=.;Integrated Security=SSPI;DataTypeCompatibility=80;MARS Connection=True;"
cn.Execute "create procedure testproc(@paramin nvarchar(max)) as begin select @paramin end"
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "testproc"
str = "testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!! testing !!!!"
cmd.Parameters.Append cmd.CreateParameter("@param1", adLongVarWChar, adParamInput, -1, str)
Set rs = cmd.Execute
MsgBox rs.Fields(0).Value
cn.Execute "drop procedure testproc"
Set cmd = Nothing

here is a link to an msdn article on it
Avatar of flavo
Flag of Australia image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Data-Man


I should have thought about the recordset...was too hung up on the output param.
It's great that the new version supports these new datatypes.

Thanks Flavo,
Glad to help, mate.

I tried using ADO 2.8 & SQL Server Native Client 10.0 but same problem is there. It is not returning nVarChar(Max) or VarChar(Max) to outparameters whoes type adLongVarChar OR adLongVarWChar.

I know that if we return recordset it returns value, but returnin single record value through ADO parameters is so much faster then recordset appx. 10 times in Local Area Network or over internet.

Please view my code and help me for solution.

Sanjay Shah

ALTER PROCEDURE [dbo].[SSP_ItemMaster_Select]
 @pItemCode AS nVarCahr(15) = '',
 @pItemDescription AS nVarChar(Max) = '' OUTPUT,
 @pSalesRate AS Float = 0 OUTPUT

 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.

        -- Insert statements for procedure here
 @pItemDescription = ItemDescription,
 @pSalesRate = SalesRate
 FROM   dbo.FaItemMaster
 WHERE  ItemCode = @pItemCode

-- End of Procedure 

3. Following is my code of procedure assume

Private Sub Command1_Click()
Dim cnn as new adodb.connection
Dim cmd as New adodb.command
Dim prm as New adodb.parameters

' Opening Connection "Provider = SQLOLEDB; Server =; Database=MyDatabase; User 
ID=sa; Password=mypass; "

' Initialise Command
set cmd.ActiveConnection = cnn

' Append ItemCode Paremeters
Set prm = cmd.CreateParameters("@pItemCode", advarWChar, adParamInput, 15)
cmd.Parameters.Append Prm

' Append Rate Paremeters
Set prm = cmd.CreateParameters("@pSalesRate", adDouble , adParamOutput)
cmd.Parameters.Append Prm

' Assign Command Text
cmd.CommandText = "dbo.SSP_ItemMaster_Select"
cmd.CommandType = adCmdStoredProc
cmd.Parameters("@pItemCode").Value = "0101001"
cmd.Execute , , adExecuteNoRecords

' Display Description
MsgBox "Item Description " & vbcrlf & 
End Sub

Open in new window

    Are you sure the recordset is that much slower?  That has not been my experience.  You can still use the command object.  Just assign the .Execute to a recordset object and modity your proc to return a single record.  When creating your ADO recordset, make sure it is set to snapshot, forwardonly, readonly, ,etc.  
     My solution with the recordset has worked perfectly for years now.
     Do no quote me on this, but the problem with returning nvarchar(max) from a proc with ADO is that ADO 2.8 came out before the 'max' datatype and I'm not sure that the ADO knows how to handle it.  Just my two cents.

Yes Mike,
Returning Single Record Recordset Value to output parameters is appx. 10 times faster then returning recordset. I tried returning recordset it is working fine. But in case of remote connection through internet speed is important. I tried to return single recordset through internet whose fields is more than 125 & recordset length is over 1500 bytes (excl. Memo nVarChar) it then takes avg. 2 seconds but if I return through parameters it takes 0.14 seconds.

You also try this.

Sanjay Shah
I'm under a deadline on a project and don't have enough free time to play with this.  If I get  chance, I'll try to look at this specific issue this weekend.

     Have you isolated the specific code that is responsible for the difference in the time?

I have not isolated any code. This is sample application I had generated to plan out how to modify main application for speed over internet.

Sanjay Shah