EXEC(@SQL) And Unicode Result Bug

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 i
s
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
TrisTris (Tris@.discussions.microsoft.com) writes:
> 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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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
"Tris" <Tris@.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|||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

exec sp_executesql vs. sp_executesql and performance

This is a odd problem where a bad plan was chosen again and again, but
then not.

Using the profiler, I identified an application-issued statement that
performed poorly. It took this form:

exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @.Parm1)',
N'@.Parm1 int', @.Parm1 = 8609

t2 is a foreign key column, and is indexed.

I took the statement into query analyzer and executed it there. The
query plan showed that it was doing a scan of the primary key index,
which is clustered. That's a bad choice.

I then fiddled with it to see what would result in a good plan.

1) I changed it to hard code the query value (but with the parm
definition still in place. )
It performed well, using the correct index.
Here's how it looked.
exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS
[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],
cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],
cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],
cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],
cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROM
cbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@.Parm1 int',
@.Parm1 = 8609

After doing this, re-executing the original form still gave bad
results.

2) I restored the use of the parm, but removed the 'exec' from the
start.
It performed well.

After that (surprise!) it also performed well in the original form.

What's going on here?elRoyFlynn (lit@.twcny.rr.com) writes:
> t2 is a foreign key column, and is indexed.
> I took the statement into query analyzer and executed it there. The
> query plan showed that it was doing a scan of the primary key index,
> which is clustered. That's a bad choice.

Sometimes it is, sometimes it's not. This is a delicate choice that
the optimizer have to make. Non-clustered index + bookmark lookup, or
clustered index scan? The first strategy fantastic if there are only
a few hits, but disastrous if you hit, say, 30% of the rows. Many page
will be accessed more than once, and it will be a lot slower than a CI
scan.

> 2) I restored the use of the parm, but removed the 'exec' from the
> start.
> It performed well.
> After that (surprise!) it also performed well in the original form.

Probably parameter sniffing. SQL Server caches the query plan for the
query, and the cached plan is built from the parameter value that
query first was run for. That value may have been handled best with
a CI scan.

But it might also be that the statistics were poor initially, and caused
SQL Server to make an incorrect estimate. But SQL Server has auto-
statistics, so it could be that statistics were updated, and the plan
was flushed, and a new plan built.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In this case, the "bad plan" clearly is: a 6-second response time vs.
a sub-second response when the "best" plan is used.

Problem is, the application generated this query in the "exec sp..."
form hundreds of times, getting the bad result each time. It was while
the app was still running that I worked in query analyzer. I executed
the problem sql multiple times, duplicating the bad result, before
trying it the other way. The first time I did it the other way, it
worked well, which also immediately fixed the application. Coincidence
, unrelated to the different execution form, that just at that moment
mss figured out that the other plan was better? I'm skeptical. I
think that something about 'exec sp_.." vs. plain "sp_..." had an
unintended effect.

But thanks for the response, I'll think about it.|||elRoyFlynn (lit@.twcny.rr.com) writes:
> In this case, the "bad plan" clearly is: a 6-second response time vs.
> a sub-second response when the "best" plan is used.

It should be admitted that this is quite common. The optimizer seems to
be overly conservative with regards to non-clustered indexes.

> Coincidence , unrelated to the different execution form, that just at
> that moment mss figured out that the other plan was better? I'm
> skeptical. I think that something about 'exec sp_.." vs. plain "sp_..."
> had an unintended effect.

It could be that the misisng "exec" triggered a recompile of the query,
but from what I know about how the cache works, I can't really see that
it would matter.

What could matter, though, is whether you changed somehting inside
the query. With regards to single queries, the cache is both case-
and space-sensitive. (But the part "EXEC sp_executesql" is not in
the cache, only the first argument to sp_executesql is.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Exec SP_Executesql @Selectstring

Hi,
How do I assign the select statement result to a variable?
Select @.Myvariable = Exec SP_Executesql @.Selectstring
It donesn't work.
Any ideas?
Thanksanswered in another newsgroup - please don't post the same message
independently to multiple newsgroups.

Exec SP_Executesql @Selectstring

Hi,
How do I assign the select statement result to a variable?
Select @.Myvariable = Exec SP_Executesql @.Selectstring
It donesn't work.
Any ideas?
Thanksanswered in another newsgroup - please don't post the same message
independently to multiple newsgroups.

Exec SP_Executesql @Selectstring

Hi,
How do I assign the select statement result to a variable?
Select @.Myvariable = Exec SP_Executesql @.Selectstring
It donesn't work.
Any ideas?
Thanks
answered in another newsgroup - please don't post the same message
independently to multiple newsgroups.

EXEC in SQL Functions

Hi,

I need to pass a table name and id to a function and return a row count
I need to use EXEC or SP_EXECUTESQL to run dynamic SQL
It wont work in functions. Following is my function

alter FUNCTION [dbo].[GetRowCount] (@.TblName NVARCHAR(25) , @.Itemid INT)
RETURNS INT
AS BEGIN
DECLARE @.RowCnt INT
set @.RowCnt = 0
DECLARE @.Sqlstring nvarchar(2000)

set @.Sqlstring = 'SELECT @.RowCnt = COUNT(*) FROM ['+ @.TblName +'] WHERE Itemid = '+ convert(varchar(10),@.Itemid)
EXEC @.Sqlstring

RETURN @.RowCnt
END

while executing this I get the following error ....
"Only functions and extended stored procedures can be executed from within a function." and "Incorrect syntax near the keyword 'EXEC' "

does anyone have any ideas of this ?
Thanks.
vidhya

Moving to the T-SQL forum.|||

You can't use sp_executesql inside functions.

Why would you want to do this? Perhaps you can change the calling mechanism?

|||You cannot execute a command with exec or sp_executesql nor can execute a stored procedure in a function.
HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

instead of using sp_execute, write another function and pass that variable value into that function.

u can call function into another function.

|||

You cannot use an exec statement with in a user defined function. What i can see in your code is you are returning single integer value from your function which you can very well do in a stored procedure using return statement there as well.

I think you should do it in a stored procedure

sql

EXEC in SQL Functions

Hi,

I need to pass a table name and id to a function and return a row count
I need to use EXEC or SP_EXECUTESQL to run dynamic SQL
It wont work in functions. Following is my function

alter FUNCTION [dbo].[GetRowCount] (@.TblName NVARCHAR(25) , @.Itemid INT)
RETURNS INT
AS BEGIN
DECLARE @.RowCnt INT
set @.RowCnt = 0
DECLARE @.Sqlstring nvarchar(2000)

set @.Sqlstring = 'SELECT @.RowCnt = COUNT(*) FROM ['+ @.TblName +'] WHERE Itemid = '+ convert(varchar(10),@.Itemid)
EXEC @.Sqlstring

RETURN @.RowCnt
END

while executing this I get the following error ....
"Only functions and extended stored procedures can be executed from within a function." and "Incorrect syntax near the keyword 'EXEC' "

does anyone have any ideas of this ?
Thanks.
vidhya

Moving to the T-SQL forum.|||

You can't use sp_executesql inside functions.

Why would you want to do this? Perhaps you can change the calling mechanism?

|||You cannot execute a command with exec or sp_executesql nor can execute a stored procedure in a function.
HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

instead of using sp_execute, write another function and pass that variable value into that function.

u can call function into another function.

|||

You cannot use an exec statement with in a user defined function. What i can see in your code is you are returning single integer value from your function which you can very well do in a stored procedure using return statement there as well.

I think you should do it in a stored procedure

EXEC / Sp_executesql with Dynamic SQL string exceeding 4000 - SQL Server 2000

This is regarding the sp_executesql and the sql statement parameter, in processing a dynamic SQL on SQL Server 2000, in my stored procedure.

I have my SQL string exeeding more than 4000 characters.
The sp_executesql expects its parameters to be declared as nvarchar/ntext.
ntext cannot be declared for a local variable and nvarchar has a maximum limit of 4000 characters.

The following was the original approach.
===============
DECLARE @.sql nvarchar(4000),

--
select @.sql=@.sql+'select.................'
--
--
INSERT INTO #Temp
EXEC sp_executesql @.SQL,@.paramlist

===============================

The above sql was split into two sql variables and tried in the following way.
==============================
DECLARE @.sql nvarchar(4000), @.sql1 nvarchar(4000),@.paramlist nvarchar(4000)

--
select @.sql=@.sql+'select.................'
select @.sql1=@.sql1+'from table1, table2.........'
--
--

INSERT INTO #Temp
exec('EXEC sp_executesql ''' + @.sql + @.sql1 + ''',@.paramlist')
====================

However, it compiles correctly but during the execution, the error indicates as syntax error.
I ran the resulting SQL string (@.sql + @.sql1) separately, and it runs fine without any syntax errors returning rows, but when execute through
sp_executesql, it gives me error

Appreciate if anyone can suggest a workaround for this situation.

Try the other version of executing dynamic SQL the simple one....

EXEC(@.sql + @.Sql1)

Because i have used this 2 one place where my Dynamic SQl even exceeds 1lakh characters....|||

Thanks Mandip. It's working. Appreciate your help.