• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

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?
0
hennessym
Asked:
hennessym
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
does the procedure parameters have NVARCHAR instead of VARCHAR data types?
0
 
hennessymAuthor Commented:
Yes, all the parameters are nvarchar.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need to specify the Reponse.CodePage, if I remember correctly:
http://www.asp-dev.com/main.asp?page=96
0
 
hennessymAuthor Commented:
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'%??????%'
0
 
hennessymAuthor Commented:
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!

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

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now