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?

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

x
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.
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.
Pawan KumarDatabase ExpertCommented:
Are you using dynamic string in your SP?

What the length of that dynamic string....change that to MAX.
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

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

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)
Big MontyWeb Ninja at largeCommented:
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)

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.
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.