EXEC(@SQL) And Unicode Result Bug

9 Jun 2006 10:28 AM

Tris

I've got a dynamic SQL query that is generated inside a SP, and is run using
the

EXEC (@SQL)

or

EXEC sp_executesql @SQL

This has worked fine until now, where we are now using a database for
unicode charachters to support Japanese language.

All fixed code stored procedures return data correctly in the unicode
format. However, i have had to use string splicing in certain situations to
generate a fully customisable query. These queries all run using EXEC /
sp_executesql from inside the SP. However, i have discovered that all data is
return '?' instead of unicode charachters.

This is a cause of some serious issues, and i hope someone can tell me if
there is a solution for this!

Cheers

Tris

9 Jun 2006 11:04 AM

Erland Sommarskog

Tris (T***@discussions.microsoft.com) writes:
Show quoteHide quote

> I've got a dynamic SQL query that is generated inside a SP, and is run
> using the
>
> EXEC (@SQL)
>
> or
>
> EXEC sp_executesql @SQL
>
> This has worked fine until now, where we are now using a database for
> unicode charachters to support Japanese language.
>
> All fixed code stored procedures return data correctly in the unicode
> format. However, i have had to use string splicing in certain situations
> to generate a fully customisable query. These queries all run using EXEC
> / sp_executesql from inside the SP. However, i have discovered that all
> data is return '?' instead of unicode charachters.
>
> This is a cause of some serious issues, and i hope someone can tell me if
> there is a solution for this!

First of all, you should use sp_executesql and parameterised statements
rather than EXEC() for dynamic SQL. For a longer disucssion see
http://www.sommarskog.se/dynamic_sql.html.

As for your actual problem, it's diffcult to say without seeing the code.
But my guess would be that you have some varchar variable somewhere that
causes problems, or that you use '' for literals rather than N''. Again,
I suspect that these are problems that would go away if you always use
parameterised statements and never interpolate values into the query string.

I like to stress that this is all guessworks.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

9 Jun 2006 2:29 PM

Tris

Hi, thanks for the responses.

Yes, some of the arguments used to generate the string were VARCHAR, and
changing them to NVARCHAR has solved the problem.

Cheers

T

9 Jun 2006 11:24 AM

Dan Guzman

Can you post a script that reproduces the problem? Erland mentioned causes
of these symptoms that are not bugs but a specific case is needed to clearly
determine whether or not your issue is a defect or expected behavior.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quoteHide quote

"Tris" <T***@discussions.microsoft.com> wrote in message
news:A6E5E6A6-F96E-44C8-938F-EC0D459C7862@microsoft.com...
> I've got a dynamic SQL query that is generated inside a SP, and is run
> using
> the
>
> EXEC (@SQL)
>
> or
>
> EXEC sp_executesql @SQL
>
> This has worked fine until now, where we are now using a database for
> unicode charachters to support Japanese language.
>
> All fixed code stored procedures return data correctly in the unicode
> format. However, i have had to use string splicing in certain situations
> to
> generate a fully customisable query. These queries all run using EXEC /
> sp_executesql from inside the SP. However, i have discovered that all data
> is
> return '?' instead of unicode charachters.
>
> This is a cause of some serious issues, and i hope someone can tell me if
> there is a solution for this!
>
> Cheers
>
> Tris