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. (
http://msdn2.microsoft.com/en-us/library/ms130978.aspx)
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.Refresh
.Parameters.Append .CreateParameter("@strProj
ect", adVarChar, adParamInput, 12, Form_frmProspectCustomMail
ing.cboPro
ject.Value
)
.Parameters.Append .CreateParameter("@strSubj
ect", 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_frmProspectCustomMail
ing.Tag)
.Parameters.Append .CreateParameter("@strHTML
Output", 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,
Mike