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 Stored Procedures with high level user permissions

I need to run a stored procedure with admin rights inside the stored procedure like so

Exec xp_cmdshell "c:\start.exe"

i need this to have administrator access so when it is called upon by an asp.net web page that executes the stored procedure it is not run by Network Service user but Administrator. How would i do this.it's the account that runs sqlserver serice that you need|||i am sorry i still don't understand if you could give an example stored procedure or

are you stating that the user that stored the stored procedure is the user level it is run at.|||ohh i forgot to add thank you for replying so fast last time|||what i am saying is that you need to make sure that the account that is used to start sqlserver service is an account that is given enough priveleges to perform the task you need.|||so if the service is started by user Administrator which it alwasy is. That is the user used to start any programs in the stored procedure.

Exec procedures inside other procedures

This should be pretty simple but I am not getting an luck getting what I need by googling.

I am executing a few stored procedures inside of another stored procedure. One of stored procedures returns a value that the other 2 procedures will use how do I get that get and use that variable?

Example

ALTERPROCEDURE [dbo].[ins_mainproc_sp]

AS

(

parameters

)

BEGIN

this procedure returns an ID

exec ins_procedure1_sp 'Value', 'Value'

these 2 procedures will use the value returned by the above procedure.

exec ins_procedure2_sp id,'Value'

exec ins_procedure3_sp id,'Value'

END

I was going to break this into 2 different procedures. Run ins_procedure1_sp get the value then pass it into ins_mainproc_sp but I was hoping to not make 2 calls.

You can use the OUTPUT parameter here.

Code Snippet

ALTER PROCEDURE [dbo].[ins_mainproc_sp]

AS

(

parameters

)

Begin

Declare @.Id as int

exec ins_procedure1_sp 'Value', 'Value', @.Id OUTPUT

..

...

....

--Reuse the @.Id on the SP calls

End

Go

Alter Proc ins_procedure1_sp

(

other parameters,

@.Id int OUTPUT

)

as

Begin

Insert ......

Set @.Id = <inserted Value>;

End

exec in a function

I am creating a dynamic query and using exec to execute it inside of a function. This query will return only one value. How can I get the value the query returns into a variable?
Functions can not call stored procedures, and they can not use temporary tables.
Thanks muchI don't think you can do Dynamic SQL in a user defined fuction.

Tim S

Exception handling in Triggers.

Can anybody point me to a good text about
exception handling in triggers (errors and rollbacks) ?
From 'Inside' :
ROLLBACK (because of a fatal error or an
explicit ROLLBACK command), the entire batch is aborted.
As I read this :
From within a trigger any FK or relational constraint
violation results in a fatal error aborting the complete
batch (and complete transaction).
Is this correct ?
What if there was no transaction start ?
In the QA, does the QA supply a transaction
(increase the transaction count) if no transaction
was started ?
When sending several statements, it appears
that all statements are rolled back. (Are handled
as one transaction).
Does something similar happen when doing
similar statements from application programs ?
Thanks for your attention,
ben brugman.
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23VPXOAQkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> As I read this :
> From within a trigger any FK or relational constraint
> violation results in a fatal error aborting the complete
> batch (and complete transaction).
> Is this correct ?
Not just a constraint violation; ANY error will abort (and consequently
rollback) the entire batch.

> What if there was no transaction start ?
Every DML operation in SQL Server uses a transaction, even if you don't
explicitly request it.
So:
UPDATE Tbl
SET Val = 0
WHERE SomeCol = 1
will use a transaction. That way, if there are two rows where SomeCol =
1, and the first was successful but the second had an error, the entire
batch will roll back to the initial state.

> When sending several statements, it appears
> that all statements are rolled back. (Are handled
> as one transaction).
Do you have IMPLICIT_TRANSACTIONS turned on for your session?
This option causes SQL Server to treat multistatement batches as if they
were wrapped in a transaction. But it's not turned on by default in Query
Analyzer or any connection library that I know of...

Exception handling in Triggers.

Can anybody point me to a good text about
exception handling in triggers (errors and rollbacks) ?
From 'Inside' :
ROLLBACK (because of a fatal error or an
explicit ROLLBACK command), the entire batch is aborted.
As I read this :
From within a trigger any FK or relational constraint
violation results in a fatal error aborting the complete
batch (and complete transaction).
Is this correct ?
What if there was no transaction start ?
In the QA, does the QA supply a transaction
(increase the transaction count) if no transaction
was started ?
When sending several statements, it appears
that all statements are rolled back. (Are handled
as one transaction).
Does something similar happen when doing
similar statements from application programs ?
Thanks for your attention,
ben brugman."ben brugman" <ben@.niethier.nl> wrote in message
news:%23VPXOAQkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> As I read this :
> From within a trigger any FK or relational constraint
> violation results in a fatal error aborting the complete
> batch (and complete transaction).
> Is this correct ?
Not just a constraint violation; ANY error will abort (and consequently
rollback) the entire batch.
> What if there was no transaction start ?
Every DML operation in SQL Server uses a transaction, even if you don't
explicitly request it.
So:
UPDATE Tbl
SET Val = 0
WHERE SomeCol = 1
will use a transaction. That way, if there are two rows where SomeCol =1, and the first was successful but the second had an error, the entire
batch will roll back to the initial state.
> When sending several statements, it appears
> that all statements are rolled back. (Are handled
> as one transaction).
Do you have IMPLICIT_TRANSACTIONS turned on for your session?
This option causes SQL Server to treat multistatement batches as if they
were wrapped in a transaction. But it's not turned on by default in Query
Analyzer or any connection library that I know of...|||If we can get away from the trigger for a second.
One of the least documented aspects on how transactions
work are to do with the process id that its running from.
So if you open up QA do transaction mock statement, open
up another window in QA and do rollback you will get an
error, why, becasue the process in the second window is
different to the first.
The reason why this is important is that a rollback as you
found out will effect EVERY transaction in that process
even though it maybe nested. Try this yourself, you will
get some very interesting answers.
So to get back to your question.
SQL Server will automatically rollback transactions if the
error is serious enough.
If there is no transaction then there will be no
transaction count, there is a handy little global variable
called @.@.TRANCOUNT you may want to look at. So if that is
set to anything but a 0 it will automatically rollback the
transaction irrespective on whether it is done in trigger
or something else.
From application programs then it depends on the error. If
you had a connection object and you application (not SQL)
failed then it would be up to your application to repair
the DB by sending it up a rollback.
If you application using the same connection as the begin
transaction sent some SQL that caused an error then yes it
would automatically roll back.
Anyway that it. I sugest you have a play on QA.
If you have any questions then don't hesitate to email me
on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
busy tonight)
Peter
"You can always count on Americans to do the right thing -
after they've tried everything else."
Winston Churchill
>--Original Message--
>Can anybody point me to a good text about
>exception handling in triggers (errors and rollbacks) ?
>From 'Inside' :
>ROLLBACK (because of a fatal error or an
>explicit ROLLBACK command), the entire batch is aborted.
>
>As I read this :
>From within a trigger any FK or relational constraint
>violation results in a fatal error aborting the complete
>batch (and complete transaction).
>Is this correct ?
>What if there was no transaction start ?
>In the QA, does the QA supply a transaction
>(increase the transaction count) if no transaction
>was started ?
>When sending several statements, it appears
>that all statements are rolled back. (Are handled
>as one transaction).
>Does something similar happen when doing
>similar statements from application programs ?
>Thanks for your attention,
>ben brugman.
>
>.
>|||My email is
stbraslenscap@.lenscaphiscom.nl
(Both lenscaps should be removed).
I tried to reply to your mail, but that bounced,
so now in the thread.
thanks in advance,
ben brugman
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4ff601c49104$6e437d70$a301280a@.phx.gbl...
> If we can get away from the trigger for a second.
> One of the least documented aspects on how transactions
> work are to do with the process id that its running from.
> So if you open up QA do transaction mock statement, open
> up another window in QA and do rollback you will get an
> error, why, becasue the process in the second window is
> different to the first.
> The reason why this is important is that a rollback as you
> found out will effect EVERY transaction in that process
> even though it maybe nested. Try this yourself, you will
> get some very interesting answers.
> So to get back to your question.
> SQL Server will automatically rollback transactions if the
> error is serious enough.
> If there is no transaction then there will be no
> transaction count, there is a handy little global variable
> called @.@.TRANCOUNT you may want to look at. So if that is
> set to anything but a 0 it will automatically rollback the
> transaction irrespective on whether it is done in trigger
> or something else.
> From application programs then it depends on the error. If
> you had a connection object and you application (not SQL)
> failed then it would be up to your application to repair
> the DB by sending it up a rollback.
> If you application using the same connection as the begin
> transaction sent some SQL that caused an error then yes it
> would automatically roll back.
> Anyway that it. I sugest you have a play on QA.
> If you have any questions then don't hesitate to email me
> on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
> busy tonight)
> Peter
> "You can always count on Americans to do the right thing -
> after they've tried everything else."
> Winston Churchill
>
>
> >--Original Message--
> >Can anybody point me to a good text about
> >exception handling in triggers (errors and rollbacks) ?
> >
> >From 'Inside' :
> >ROLLBACK (because of a fatal error or an
> >explicit ROLLBACK command), the entire batch is aborted.
> >
> >
> >As I read this :
> >From within a trigger any FK or relational constraint
> >violation results in a fatal error aborting the complete
> >batch (and complete transaction).
> >Is this correct ?
> >
> >What if there was no transaction start ?
> >In the QA, does the QA supply a transaction
> >(increase the transaction count) if no transaction
> >was started ?
> >When sending several statements, it appears
> >that all statements are rolled back. (Are handled
> >as one transaction).
> >Does something similar happen when doing
> >similar statements from application programs ?
> >
> >Thanks for your attention,
> >
> >ben brugman.
> >
> >
> >.
> >

Exception handling in Triggers.

Can anybody point me to a good text about
exception handling in triggers (errors and rollbacks) ?
From 'Inside' :
ROLLBACK (because of a fatal error or an
explicit ROLLBACK command), the entire batch is aborted.
As I read this :
From within a trigger any FK or relational constraint
violation results in a fatal error aborting the complete
batch (and complete transaction).
Is this correct ?
What if there was no transaction start ?
In the QA, does the QA supply a transaction
(increase the transaction count) if no transaction
was started ?
When sending several statements, it appears
that all statements are rolled back. (Are handled
as one transaction).
Does something similar happen when doing
similar statements from application programs ?
Thanks for your attention,
ben brugman."ben brugman" <ben@.niethier.nl> wrote in message
news:%23VPXOAQkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> As I read this :
> From within a trigger any FK or relational constraint
> violation results in a fatal error aborting the complete
> batch (and complete transaction).
> Is this correct ?
Not just a constraint violation; ANY error will abort (and consequently
rollback) the entire batch.

> What if there was no transaction start ?
Every DML operation in SQL Server uses a transaction, even if you don't
explicitly request it.
So:
UPDATE Tbl
SET Val = 0
WHERE SomeCol = 1
will use a transaction. That way, if there are two rows where SomeCol =
1, and the first was successful but the second had an error, the entire
batch will roll back to the initial state.

> When sending several statements, it appears
> that all statements are rolled back. (Are handled
> as one transaction).
Do you have IMPLICIT_TRANSACTIONS turned on for your session?
This option causes SQL Server to treat multistatement batches as if they
were wrapped in a transaction. But it's not turned on by default in Query
Analyzer or any connection library that I know of...