Error in SP

I am getting an error on the Stored Procedure below.

All values are integer other than the last one which is a varchar(MAX). I think that is the one that is causing the error.


Dim sp_cloneblob__Firmid
sp_cloneblob__Firmid = "0"
if(Session("Firmid")  <> "") then sp_cloneblob__Firmid = Session("Firmid") 

Dim sp_cloneblob__CaseId
sp_cloneblob__CaseId = "0"
if(Session("CaseIDOpened")  <> "") then sp_cloneblob__CaseId = Session("CaseIDOpened") 

Dim sp_cloneblob__Userid
sp_cloneblob__Userid = "0"
if(Request("Newuserid")  <> "") then sp_cloneblob__Userid = Request("Newuserid") 

Dim sp_cloneblob__Activityid
sp_cloneblob__Activityid = "0"
if("0"  <> "") then sp_cloneblob__Activityid = "0"

Dim sp_cloneblob__Blobdata
sp_cloneblob__Blobdata = ""
if(Formscatalog("blobdata")   <> "") then sp_cloneblob__Blobdata = Formscatalog("blobdata")  


set sp_cloneblob = Server.CreateObject("ADODB.Command")
sp_cloneblob.ActiveConnection = MM_bluedot_STRING
sp_cloneblob.CommandText = "dbo.cloneblob"
sp_cloneblob.Parameters.Append sp_cloneblob.CreateParameter("@RETURN_VALUE", 3, 4)
sp_cloneblob.Parameters.Append sp_cloneblob.CreateParameter("@Firmid", 3, 1,4,sp_cloneblob__Firmid)
sp_cloneblob.Parameters.Append sp_cloneblob.CreateParameter("@CaseId", 3, 1,4,sp_cloneblob__CaseId)
sp_cloneblob.Parameters.Append sp_cloneblob.CreateParameter("@Userid", 3, 1,4,sp_cloneblob__Userid)
sp_cloneblob.Parameters.Append sp_cloneblob.CreateParameter("@Activityid", 3, 1,4,sp_cloneblob__Activityid)
sp_cloneblob.Parameters.Append sp_cloneblob.CreateParameter("@Blobdata", 200, 1,999999,sp_cloneblob__Blobdata)
sp_cloneblob.CommandType = 4
sp_cloneblob.CommandTimeout = 0
sp_cloneblob.Prepared = true


Open in new window

Perhaps the parameters for this type of data are incorrect ?

sp_cloneblob.Parameters.Append sp_cloneblob.CreateParameter("@Blobdata", 200, 1,999999,sp_cloneblob__Blobdata)
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Karthik RamachandranCommented:
You should use VARBINARY(MAX) for BLOB types.
AleksAuthor Commented:
I changed it to varbinary(MAX). It is going to store a very long XML string, can you confirm this is the best data type for that ?

I am still getting the error:

osoft OLE DB Provider for ODBC Drivers error '80040e21'

[Microsoft][ODBC SQL Server Driver]String data, right truncation

/bluedot/Intranet/cases/clone_SPadddefaults.asp, line 423

I am moving data from one record to another on the same table and column, so it SHOULD fit, the problem seems like my stored procedure parameters for that data type are incorrect. Does anyone knows what I should put in there ?

This is the line:

sp_cloneblob.Parameters.Append sp_cloneblob.CreateParameter("@Blobdata", 200, 1,999999,sp_cloneblob__Blobdata) 

Open in new window

Karthik RamachandranCommented:

Use adLongVarChar.

And  the parameters to sp_cloneblob.Parameters.Append sp_cloneblob.CreateParameter() are;
"@Blobdata", 201, 1,sp_cloneblob__Blobdata.Length,sp_cloneblob__Blobdata

Open in new window

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
AleksAuthor Commented:
I would like to know why this data type is best for an XML string, why LongVarchar and not varchar(MAX) or varbinary(MAX)

This seems to have worked

sp_cloneblob.Parameters.Append sp_cloneblob.CreateParameter("@Blobdata", 201, 1,99999999,sp_cloneblob__Blobdata)

Open in new window

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
Query Syntax

From novice to tech pro — start learning today.