[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • 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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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