SP code not working, maybe wrong parameter

I have code that takes a nvarchar(max) value to run a SP.
If I run the SP directly on SSMS it works fine, the SP does the job. But using ASP Classic code to pass the parameter which is a very long XML string to the SP is giving errors saying the data is getting truncated. Here is the code we currently have:

set sp_usp_xmlparse = Server.CreateObject("ADODB.Command")
    sp_usp_xmlparse.ActiveConnection = MM_bluedot_STRING
    sp_usp_xmlparse.CommandText = "dbo.usp_XMLParse"
    sp_usp_xmlparse.CommandType = 4
    sp_usp_xmlparse.CommandTimeout = 0
    sp_usp_xmlparse.Prepared = true
    sp_usp_xmlparse.Parameters.Append sp_usp_xmlparse.CreateParameter("@RETURN_VALUE", 3, 4)
    sp_usp_xmlparse.Parameters.Append sp_usp_xmlparse.CreateParameter("@blob", 200, 1, -1, qnrBlobXML)
    sp_usp_xmlparse.Parameters.Append sp_usp_xmlparse.CreateParameter("@UserID", 3, 1, -1, UpdateStatus__MMColParam)
    sp_usp_xmlparse.Parameters.Append sp_usp_xmlparse.CreateParameter("@FirmID", 3, 1, -1, UpdateStatus__MMColParam2)
    sp_usp_xmlparse.Execute()

Open in new window


Any ideas what could be wrong?
LVL 1
AleksAsked:
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.

Pawan KumarDatabase ExpertCommented:
can you show you SP.. ?

You need to check for the Datatypes and the length we are using? One/more length may be smaller than what data we are inserting.
0
AleksAuthor Commented:
The data type for @blob is nvarchar(max) in the DB, if I run the SP locally it works fine, so its not the size of the string but the ASP code above.
0
Pawan KumarDatabase ExpertCommented:
Are you using dynamic string in your SP?

What the length of that dynamic string....change that to MAX.
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.

AleksAuthor Commented:
This is how the variables are declared in the SP. It is already MAX.

	@blob varchar(max),
	@UserID int,
	@FirmID int

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Can you show you SP ?

There must be a dynamic string there..?

Also What is this 200?

sp_usp_xmlparse.Parameters.Append sp_usp_xmlparse.CreateParameter("@blob", 200, 1, -1, qnrBlobXML)
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you're telling your SP to only allow the @blob parameter to accept 200 characters, even though it's a nvarchar(max). Change it to use the length of your qnrBlobXML variable and you should be ok:

sp_usp_xmlparse.Parameters.Append sp_usp_xmlparse.CreateParameter("@blob", Len( qnrBlobXML ), 1, -1, qnrBlobXML)
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
AleksAuthor Commented:
We appreciate both inputs.
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
Web Development

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.