asked on

Foreign character parameter not being executed by SQL 2005 stored procedure as expected

Hi folks,

I have a classic ASP page that passes parameters to a stored procedure in SQL 2005. It's working well except for when the parameter contains foreign characters.

Profiler indicates the stored procedure is being called correctly:
exec mydb.dbo.sp_mysp N'',N'',N'>A:20',N'',N'',N''

Yet no records are returned. When I do a stored procedure trace, I see that the parameter with foreign characters is changed to several question marks :
address like N'%??????%'
                                                  OR country like N'%??????%'
                                                  OR city like N'%??????%'
                                                  OR state like N'%??????%'
                                                  OR zip like N'%??????%')

Any suggestions?

Guy Hengel [angelIII / a3]

does the procedure parameters have NVARCHAR instead of VARCHAR data types?

hennessym

ASKER

Yes, all the parameters are nvarchar.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hennessym

ASKER

Thanks, angelll. I should have mentioned in my question that I do have the Response.CodePage to no avail.

Response.CodePage = 65001
Response.CharSet = "utf-8"

I think the problem is on the SQL side of things, as profiler indicates the value of the foreign character parameter is also changed to a bunch of question marks when executed from SSMS.

So,

exec mydb.dbo.mysp N'',N'',N'MyForeignChars',N'',N'',N''

is executed as:
address like N'%??????%'
                                                  OR country like N'%??????%'
                                                  OR city like N'%??????%'
                                                  OR state like N'%??????%'
                                                  OR zip like N'%??????%'

hennessym

ASKER

Aargh, the answer hit me like a ton of bricks. I foolishly had my @strSQL variable that's building the strings declared as a varchar instead of a nvarchar.

I'm still giving you the points angell, as your response was right-on given the information I gave you to work with.

Thanks again!