Clean up old stored procedures

Is there a way to find out if a stored procedure has not been run in a
while?
Mike W wrote:
> Is there a way to find out if a stored procedure has not been run in a
> while?
Not directly. The best you can do is create a server-side trace that
traps the SP:Starting event. You do not need to bind the TextData, only
the ObjectID, DatabaseID. Let it run until you feel you'll get the best
collection of procedures. You can resolve the object names against the
sysobjects table in each database or use object_name() for the currently
selected database.
I would obviously test the removal in the deve environment before
removing any procedures from production.
David Gugick
Imceda Software
www.imceda.com

Clean up old stored procedures

Is there a way to find out if a stored procedure has not been run in a
while?Mike W wrote:
> Is there a way to find out if a stored procedure has not been run in a
> while?
Not directly. The best you can do is create a server-side trace that
traps the SP:Starting event. You do not need to bind the TextData, only
the ObjectID, DatabaseID. Let it run until you feel you'll get the best
collection of procedures. You can resolve the object names against the
sysobjects table in each database or use object_name() for the currently
selected database.
I would obviously test the removal in the deve environment before
removing any procedures from production.
David Gugick
Imceda Software
www.imceda.comsqlsql

Clean up old stored procedures

Is there a way to find out if a stored procedure has not been run in a
while?Mike W wrote:
> Is there a way to find out if a stored procedure has not been run in a
> while?
Not directly. The best you can do is create a server-side trace that
traps the SP:Starting event. You do not need to bind the TextData, only
the ObjectID, DatabaseID. Let it run until you feel you'll get the best
collection of procedures. You can resolve the object names against the
sysobjects table in each database or use object_name() for the currently
selected database.
I would obviously test the removal in the deve environment before
removing any procedures from production.
David Gugick
Imceda Software
www.imceda.com

Classic Nest SP with Transaction Question

I have 2 Stored Procedures, "Parent" and "Child". The Parent SP calls the
Child SP, but also the Child may be called directly.
If the Child returns an error (which occurs whenever it is passed a value of
2), I want all updates to be rolled out.
I have a couple of working version of these 2 SPs, but what I am looking for
is "What is a the best (or a good way) of doing this?"
This is what I understand about transactions:
1) Performing a BEGIN TRAN increments @.@.TRANCOUNT
2) Performing an END TtRAN decrements @.@.TRANCOUNT
3) A ROLLBACK TRAN returns @.@.TRANCOUNT to 0
4) If @.@.TRANCOUNT is = X in Parent when CHild is called, it must be = X
immediately after returning from the CHILD call.
I've played around with SAVE POINTs within a transaction, but I do not have
a sample here.
I am looking for the simpilist, most intuative sane and hopefully common
approach to take here, I'm not sure I like what I've done-It's seems counter
intuitive.
Please alter my example as you would code it. Many thanks.
--****************EXAMPLE 1
--Setup: Create a table
CREATE TABLE [dbo].[Table1] (
[col1] [int] NULL ,
[col2] [int] NULL
) ON [PRIMARY]
GO
--Throw a rec into it
INSERT INTO table1 (col1,col2) values (1,1)
--Create the Parent SP
CREATE procedure dbo.parent
as
begin
declare @.res int
begin transaction
update table1 set col1 = 1
update table1 set col1 = 2
exec @.res = child 2
if @.Res = -1
begin
rollback transaction
return @.res
end
commit transaction
return 0
end
--CREATE CHILD SP -
CREATE procedure Child
@.col2 int
as
begin
begin transaction
update table1 set col1 = @.col2
update table1 set col2 = @.col2
if @.col2 = 2 --DONT PASS 2!! It's an error!
begin
IF @.@.Trancount = 1
Rollback TRANSACTION
else
Commit transaction --needs to be the same value as when we entered this
SP
PRINT 'TRANCOUNT IN CHILD ' + CAST(@.@.TRANCOUNT AS VARCHAR(10))
return -1
end
commit transaction
return 0
end
----
--
--****************EXAMPLE 2
ALTER procedure dbo.parent
as
begin
declare @.res int
begin transaction
update table1 set col1 = 1
update table1 set col1 = 2
exec @.res = child 2
if @.Res = -1
begin
rollback transaction
return @.res
end
commit transaction
return 0
end
ALTER procedure Child
@.col2 int
as
begin
begin transaction
update table1 set col1 = @.col2
update table1 set col2 = @.col2
if @.col2 = 2 --DONT PASS 2!! It's an error!
begin
if @.@.trancount > 1 --outer SP started the transaction
commit transaction --leave it to the parent to rollback outer trans
else
rollback transaction
return -1
end
commit transaction
return 0
endThere's a few things you should know:
First: always check for errors after each DML statement or SP call within a
transaction, because it is possible for an early DML statement to fail, and
later ones to pass which causes an insidious data consistency bug that is
extremely difficult to find. Here's what I do:
DECLARE @._ERROR INT
BEGIN TRANSACTION
UPDATE t1 set col1 = @.col1
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
UPDATE t2 set col2 = @.col2
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
COMMIT TRANSACTION
RETURN 0
ERROR:
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
RETURN @._ERROR
This approach should make your inquiry moot, since @.@.ERROR is set on exit
from a procedure if @.@.TRANCOUNT is less than what it was upon entry.
I only use save points if I want to roll back only part of a transaction,
here's what I do:
DECLARE @._TRANCOUNT INT SET @._TRANCOUNT = @.@.TRANCOUNT
DECLARE @._ERROR INT
IF @._TRANCOUNT = 0
BEGIN TRANSACTION savePoint
ELSE
SAVE TRANSACTION savePoint
UPDATE t1 set col1 = @.col1
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
UPDATE t2 set col2 = @.col2
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
IF @._TRANCOUNT = 0
COMMIT TRANSACTION savePoint
RETURN 0
ERROR:
IF @.@.TRANCOUNT > @._TRANCOUNT
ROLLBACK TRANSACTION savePoint
RETURN @._ERROR
"Chad" wrote:

> I have 2 Stored Procedures, "Parent" and "Child". The Parent SP calls the
> Child SP, but also the Child may be called directly.
> If the Child returns an error (which occurs whenever it is passed a value
of
> 2), I want all updates to be rolled out.
> I have a couple of working version of these 2 SPs, but what I am looking f
or
> is "What is a the best (or a good way) of doing this?"
> This is what I understand about transactions:
> 1) Performing a BEGIN TRAN increments @.@.TRANCOUNT
> 2) Performing an END TtRAN decrements @.@.TRANCOUNT
> 3) A ROLLBACK TRAN returns @.@.TRANCOUNT to 0
> 4) If @.@.TRANCOUNT is = X in Parent when CHild is called, it must be = X
> immediately after returning from the CHILD call.
> I've played around with SAVE POINTs within a transaction, but I do not hav
e
> a sample here.
> I am looking for the simpilist, most intuative sane and hopefully common
> approach to take here, I'm not sure I like what I've done-It's seems count
er
> intuitive.
> Please alter my example as you would code it. Many thanks.
> --****************EXAMPLE 1
> --Setup: Create a table
> CREATE TABLE [dbo].[Table1] (
> [col1] [int] NULL ,
> [col2] [int] NULL
> ) ON [PRIMARY]
> GO
> --Throw a rec into it
> INSERT INTO table1 (col1,col2) values (1,1)
> --Create the Parent SP
> CREATE procedure dbo.parent
> as
> begin
> declare @.res int
> begin transaction
> update table1 set col1 = 1
> update table1 set col1 = 2
> exec @.res = child 2
> if @.Res = -1
> begin
> rollback transaction
> return @.res
> end
> commit transaction
> return 0
> end
> --CREATE CHILD SP -
> CREATE procedure Child
> @.col2 int
> as
> begin
> begin transaction
> update table1 set col1 = @.col2
> update table1 set col2 = @.col2
> if @.col2 = 2 --DONT PASS 2!! It's an error!
> begin
> IF @.@.Trancount = 1
> Rollback TRANSACTION
> else
> Commit transaction --needs to be the same value as when we entered this
> SP
> PRINT 'TRANCOUNT IN CHILD ' + CAST(@.@.TRANCOUNT AS VARCHAR(10))
> return -1
> end
>
> commit transaction
> return 0
> end
> ----
--
> --****************EXAMPLE 2
> ALTER procedure dbo.parent
> as
> begin
> declare @.res int
> begin transaction
> update table1 set col1 = 1
> update table1 set col1 = 2
> exec @.res = child 2
> if @.Res = -1
> begin
> rollback transaction
> return @.res
> end
> commit transaction
> return 0
> end
>
> ALTER procedure Child
> @.col2 int
> as
> begin
> begin transaction
> update table1 set col1 = @.col2
> update table1 set col2 = @.col2
> if @.col2 = 2 --DONT PASS 2!! It's an error!
> begin
> if @.@.trancount > 1 --outer SP started the transaction
> commit transaction --leave it to the parent to rollback outer tra
ns
> else
> rollback transaction
> return -1
> end
>
> commit transaction
> return 0
> end
>
>|||Thank you for the response. I don't fully understand.
In my example, I wanted to be able to call a ChildSP directly, or call a
ParentSP which calls the ChildSP, and if an error occurs in the child,
everything gets rolled back. Your exaple only included one stored proc, so I
was a little unclear.
I tried to create a 2 SP example using your style. In your error handler,
you check to see if @.@.TranCount > 0. If so, you know that there was an error
above. Howver, if we take this approach in the ChildSP, performing a
Rollback would cause the @.@.TranCount to be set to zero, and when you return
to the ParentSP, we find that @.@.TranCount is now 0, but it was 1 perform we
called ChildSP, and so we ge the error:
Server: Msg 50000, Level 16, State 1, Procedure ChildSP, Line 10
an error was raised
Server: Msg 266, Level 16, State 2, Procedure ChildSP, Line 26
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count = 0.
Try running the code below.
I would be very much indebted if you could take the 2 SP example and modify
it to a approach that works and is sane.
CREATE TABLE [dbo].[Table1] (
[col1] [int] NULL ,
[col2] [int] NULL
) ON [PRIMARY]
CREATE procedure ParentSP
as
begin
DECLARE @._ERROR INT
BEGIN TRANSACTION
exec @._ERROR = ChildSP 1
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
COMMIT TRANSACTION
RETURN 0
ERROR:
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
RETURN @._ERROR
end
CREATE procedure ChildSP
(@.RaiseError bit)
as
begin
DECLARE @._ERROR INT
BEGIN TRANSACTION
if (@.RaiseError = 1)
RAISERROR ('an error was raised', 16, 1)
ELSE
UPDATE table1 set col1 = 1
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
UPDATE table1 set col2 = 2
SELECT @._ERROR = @.@.ERROR
IF @._ERROR != 0 GOTO ERROR
COMMIT TRANSACTION
RETURN 0
ERROR:
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
RETURN @._ERROR
end
"Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
news:97330484-71EA-4142-9B25-DAE902EA8836@.microsoft.com...
> There's a few things you should know:
> First: always check for errors after each DML statement or SP call within
> a
> transaction, because it is possible for an early DML statement to fail,
> and
> later ones to pass which causes an insidious data consistency bug that is
> extremely difficult to find. Here's what I do:
> DECLARE @._ERROR INT
> BEGIN TRANSACTION
> UPDATE t1 set col1 = @.col1
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> UPDATE t2 set col2 = @.col2
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> COMMIT TRANSACTION
> RETURN 0
> ERROR:
> IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
> RETURN @._ERROR
> This approach should make your inquiry moot, since @.@.ERROR is set on exit
> from a procedure if @.@.TRANCOUNT is less than what it was upon entry.
>
> I only use save points if I want to roll back only part of a transaction,
> here's what I do:
> DECLARE @._TRANCOUNT INT SET @._TRANCOUNT = @.@.TRANCOUNT
> DECLARE @._ERROR INT
> IF @._TRANCOUNT = 0
> BEGIN TRANSACTION savePoint
> ELSE
> SAVE TRANSACTION savePoint
> UPDATE t1 set col1 = @.col1
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> UPDATE t2 set col2 = @.col2
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> IF @._TRANCOUNT = 0
> COMMIT TRANSACTION savePoint
> RETURN 0
> ERROR:
> IF @.@.TRANCOUNT > @._TRANCOUNT
> ROLLBACK TRANSACTION savePoint
> RETURN @._ERROR
>
>
> "Chad" wrote:
>|||The code I provided will work when called directly or from another stored
procedure. Use it as a template for both the parent and the child
procedure--in fact use this mechanism in all of your procedures.
You should declare an additional variable, @.RC, in the parent procedure to
receive the return code from the stored procedure call. Otherwise you will
lose the error code that originally caused the failure, for example:
DECLARE @.RC INT, @._ERROR INT
EXEC @.RC = ChildProc
SET @._ERROR = @.@.ERROR
IF @.RC != 0 OR @._ERROR != 0 GOTO ERROR
The key to this approach is that any error, regardless of the reason
(Constraint violation, out of memory, Deadlock victim, etc.) is detected and
handled immediately after it occurs, and the error handling code rolls back
the transaction. When an error occurs in the child procedure, it rolls back
any pending transaction and returns the error code to the caller. The paren
t
procedure detects that an error occurred by examining the return code, and
transferrs control to its own error handler. Since the transaction had
already been rolled back in the child procedure, @.@.TRANCOUNT is zero and thu
s
a rollback in the parent's error handler would cause an additional error.
The condition IF @.@.TRANCOUNT > 0 prevents this. (It also prevents an
additional error in the event the procedure is chosen as a deadlock victim.)
I often extend this mechanism to detect concurrency problems. For example:
DECLARE @._ERROR INT, @._ROWCOUNT INT
BEGIN TRANSACTION
UPDATE t1 SET col1 = @.col1 where key1 = @.Key and ver1 = @.version
SELECT @._ERROR = @.@.ERROR, @._ROWCOUNT = @.@.ROWCOUNT
IF @._ERROR != 0 OR @._ROWCOUNT = 0 GOTO ERROR
COMMIT TRANSACTION
RETURN 0
ERROR:
IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
IF @._ERROR = 0 AND @._ROWCOUNT = 0
RETURN -1 -- indicate that a record was changed by another
user
ELSE
RETURN @._ERROR
END
ver1 is a rowversion (timestamp) column, which is changed any time a record
is changed. If another user changes the record after the time it was read,
then ver1 will be different than @.version, the update statement will not
affect any rows, and consequently @.@.ROWCOUNT will be zero.
"Chad" wrote:

> Thank you for the response. I don't fully understand.
> In my example, I wanted to be able to call a ChildSP directly, or call a
> ParentSP which calls the ChildSP, and if an error occurs in the child,
> everything gets rolled back. Your exaple only included one stored proc, so
I
> was a little unclear.
> I tried to create a 2 SP example using your style. In your error handler,
> you check to see if @.@.TranCount > 0. If so, you know that there was an err
or
> above. Howver, if we take this approach in the ChildSP, performing a
> Rollback would cause the @.@.TranCount to be set to zero, and when you retur
n
> to the ParentSP, we find that @.@.TranCount is now 0, but it was 1 perform w
e
> called ChildSP, and so we ge the error:
> Server: Msg 50000, Level 16, State 1, Procedure ChildSP, Line 10
> an error was raised
> Server: Msg 266, Level 16, State 2, Procedure ChildSP, Line 26
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 1, current count = 0.
> Try running the code below.
> I would be very much indebted if you could take the 2 SP example and modif
y
> it to a approach that works and is sane.
>
> CREATE TABLE [dbo].[Table1] (
> [col1] [int] NULL ,
> [col2] [int] NULL
> ) ON [PRIMARY]
>
> CREATE procedure ParentSP
> as
> begin
> DECLARE @._ERROR INT
> BEGIN TRANSACTION
> exec @._ERROR = ChildSP 1
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> COMMIT TRANSACTION
> RETURN 0
> ERROR:
> IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
> RETURN @._ERROR
> end
>
> CREATE procedure ChildSP
> (@.RaiseError bit)
> as
> begin
> DECLARE @._ERROR INT
> BEGIN TRANSACTION
> if (@.RaiseError = 1)
> RAISERROR ('an error was raised', 16, 1)
> ELSE
> UPDATE table1 set col1 = 1
>
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> UPDATE table1 set col2 = 2
> SELECT @._ERROR = @.@.ERROR
> IF @._ERROR != 0 GOTO ERROR
> COMMIT TRANSACTION
> RETURN 0
> ERROR:
> IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
> RETURN @._ERROR
> end
>
> "Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
> news:97330484-71EA-4142-9B25-DAE902EA8836@.microsoft.com...
>
>|||Brian,
Thank you again for your feedback. I appreciate the tip, in particular on
handling concurrency problems using RowVersion, and I believe understand the
thrust of your points.
However, I would like to place a spot light on a point I originally made
that I feel may not have been addressed:
*** If @.@.TRANCOUNT is = X in ParentSP when ChildSP is called, it must be = X
immediately after returning from the CHILD call. , else an error results***
If feel that this is the situation in the example you proposed.
If the ParentSP BEGINs a TRANSACTON (Transaction count is now 1), then calls
ChildSP, which does a ROLLBACK within Child, TranCount will be 0 when
control is returned to the Parent. Since TranCount was 1 just prior to
calling the Child and it is zero immeditely after returning, this result in
an ERROR:

> Server: Msg 50000, Level 16, State 1, Procedure ChildSP, Line 10
> an error was raised
> Server: Msg 266, Level 16, State 2, Procedure ChildSP, Line 26
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 1, current count = 0.
This is the part that I am missing. It seems to me that the Child cannot do
the rollback if the Parent already began a Transaction.
I hope I am not trying your patience. I would really like to get this point
down.
Thanks,
Chad
"Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
news:9E615D99-D61A-4AF3-BEFB-09C2C12281D3@.microsoft.com...
> The code I provided will work when called directly or from another stored
> procedure. Use it as a template for both the parent and the child
> procedure--in fact use this mechanism in all of your procedures.
> You should declare an additional variable, @.RC, in the parent procedure to
> receive the return code from the stored procedure call. Otherwise you will
> lose the error code that originally caused the failure, for example:
> DECLARE @.RC INT, @._ERROR INT
> EXEC @.RC = ChildProc
> SET @._ERROR = @.@.ERROR
> IF @.RC != 0 OR @._ERROR != 0 GOTO ERROR
> The key to this approach is that any error, regardless of the reason
> (Constraint violation, out of memory, Deadlock victim, etc.) is detected
> and
> handled immediately after it occurs, and the error handling code rolls
> back
> the transaction. When an error occurs in the child procedure, it rolls
> back
> any pending transaction and returns the error code to the caller. The
> parent
> procedure detects that an error occurred by examining the return code, and
> transferrs control to its own error handler. Since the transaction had
> already been rolled back in the child procedure, @.@.TRANCOUNT is zero and
> thus
> a rollback in the parent's error handler would cause an additional error.
> The condition IF @.@.TRANCOUNT > 0 prevents this. (It also prevents an
> additional error in the event the procedure is chosen as a deadlock
> victim.)
> I often extend this mechanism to detect concurrency problems. For
> example:
> DECLARE @._ERROR INT, @._ROWCOUNT INT
> BEGIN TRANSACTION
> UPDATE t1 SET col1 = @.col1 where key1 = @.Key and ver1 = @.version
> SELECT @._ERROR = @.@.ERROR, @._ROWCOUNT = @.@.ROWCOUNT
> IF @._ERROR != 0 OR @._ROWCOUNT = 0 GOTO ERROR
> COMMIT TRANSACTION
> RETURN 0
> ERROR:
> IF @.@.TRANCOUNT > 0 ROLLBACK TRANSACTION
> IF @._ERROR = 0 AND @._ROWCOUNT = 0
> RETURN -1 -- indicate that a record was changed by another
> user
> ELSE
> RETURN @._ERROR
> END
> ver1 is a rowversion (timestamp) column, which is changed any time a
> record
> is changed. If another user changes the record after the time it was
> read,
> then ver1 will be different than @.version, the update statement will not
> affect any rows, and consequently @.@.ROWCOUNT will be zero.
>
> "Chad" wrote:
>|||Brian & Chad
I believe I'm having the same issue as Chad with nested stored procedures
inside a transaction.
What I'd like to do is begin a transaction in an outer SP. If all goes
well, the transaction will be committed in the outer stored procedure - no
problem there. However, if an error or other unexpected condition is
encountered, I would like to rollback as close to the error as possible (in
the statement following detection, if possible).
Problem is this might involve a transaction begun in an outer SP being
rollbed back in an inner SP. Due to the fact that on entry to inner SP
@.@.Trancount == 1 but on exit @.@.Trancount == 0, a new error, Error 266, gets
generated.
Following illustrates the problem:
-- INNER SP
create procedure InnerSP as begin
declare @.ErrNo int
/* Do something here */
select @.ErrNo = @.@.ERROR
if @.ErrNo <> 0 begin
if @.@.TRANCOUNT > 0 rollback transaction
return @.ErrNo -- on return new error (266) generated
end
return 0
end
-- OUTER SP
create procedure OuterSP as begin
declare @.ErrNo int
Begin Transaction
exec @.ErrNo = InnerSP
-- if InnerSP failed, @.@.ERROR = 266 here
if @.ErrNo <> 0 begin
if @.@.TRANCOUNT > 0 rollback transaction
return @.ErrNo
end
commit transaction
return 0
end
I could hold off on performing the rollback until OuterSP examines the
return value from InnerSP, but this is not ideal:
(1) Immediately after the error I may want to do some logging or
other fixup. If these are done before the rollback, they will be wiped out
by the rollback.
(2) In code subsequent to ther error there is always the possibility
that execution will be terminated due to a severe error, preventing the
enclosing SP from ever executing the rollback. While the lack of a
subsequent COMMIT will ultimately lead to the transaction being rolled back,
I would have no control over when the rollback occurs.
I can avoid this problem via the kludge of a new "Begin Transaction"
statement just before returning the error code from InnerSP to OuterSP. Is
there a cleaner way to resolve this problem (beyond waiting for SQL Server
2005 try...catch blocks)?
Ron Strong
"Chad" <chad.dokmanovich@.unisys.com> wrote in message
news:cv7rc2$h6a$1@.trsvr.tr.unisys.com...
> Brian,
> Thank you again for your feedback. I appreciate the tip, in particular on
> handling concurrency problems using RowVersion, and I believe understand
the
> thrust of your points.
> However, I would like to place a spot light on a point I originally made
> that I feel may not have been addressed:
> *** If @.@.TRANCOUNT is = X in ParentSP when ChildSP is called, it must be =
X
> immediately after returning from the CHILD call. , else an error
results***
> If feel that this is the situation in the example you proposed.
> If the ParentSP BEGINs a TRANSACTON (Transaction count is now 1), then
calls
> ChildSP, which does a ROLLBACK within Child, TranCount will be 0 when
> control is returned to the Parent. Since TranCount was 1 just prior to
> calling the Child and it is zero immeditely after returning, this result
in
> an ERROR:
>
>
> This is the part that I am missing. It seems to me that the Child cannot
do
> the rollback if the Parent already began a Transaction.
> I hope I am not trying your patience. I would really like to get this
point
> down.
> Thanks,
> Chad
>
> "Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
> news:9E615D99-D61A-4AF3-BEFB-09C2C12281D3@.microsoft.com...
stored
to
will
and
error.
another
a
handler,
perform
0.
fail,
that
calls
=
not
entered
--
outer
>|||PMFJI, but if your child proc is using an explicit tran, then it can be
coded as follows:
create proc dbo.ChildProc
as
set nocount on
declare @.trancount int
set @.trancount = @.@.TRANCOUNT
if @.trancount > 0
begin tran ChildProcTran
else
save tran ChildProcTran
/*
Do some stuff
*/
if @.@.ERROR > 0
begin
raiserror ('We have a problem.', 16, 1)
rollback ChildProcTran
return
end
else if @.trancount = 0 -- began our own
commit tran
go
This way, only the child proc's txn will be rolled back by the child proc.
The parent proc will be unaffected.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ron Strong" <rstrong@.DoNotSpamerols.com> wrote in message
news:ulKZzJtFFHA.1528@.TK2MSFTNGP09.phx.gbl...
Brian & Chad
I believe I'm having the same issue as Chad with nested stored procedures
inside a transaction.
What I'd like to do is begin a transaction in an outer SP. If all goes
well, the transaction will be committed in the outer stored procedure - no
problem there. However, if an error or other unexpected condition is
encountered, I would like to rollback as close to the error as possible (in
the statement following detection, if possible).
Problem is this might involve a transaction begun in an outer SP being
rollbed back in an inner SP. Due to the fact that on entry to inner SP
@.@.Trancount == 1 but on exit @.@.Trancount == 0, a new error, Error 266, gets
generated.
Following illustrates the problem:
-- INNER SP
create procedure InnerSP as begin
declare @.ErrNo int
/* Do something here */
select @.ErrNo = @.@.ERROR
if @.ErrNo <> 0 begin
if @.@.TRANCOUNT > 0 rollback transaction
return @.ErrNo -- on return new error (266) generated
end
return 0
end
-- OUTER SP
create procedure OuterSP as begin
declare @.ErrNo int
Begin Transaction
exec @.ErrNo = InnerSP
-- if InnerSP failed, @.@.ERROR = 266 here
if @.ErrNo <> 0 begin
if @.@.TRANCOUNT > 0 rollback transaction
return @.ErrNo
end
commit transaction
return 0
end
I could hold off on performing the rollback until OuterSP examines the
return value from InnerSP, but this is not ideal:
(1) Immediately after the error I may want to do some logging or
other fixup. If these are done before the rollback, they will be wiped out
by the rollback.
(2) In code subsequent to ther error there is always the possibility
that execution will be terminated due to a severe error, preventing the
enclosing SP from ever executing the rollback. While the lack of a
subsequent COMMIT will ultimately lead to the transaction being rolled back,
I would have no control over when the rollback occurs.
I can avoid this problem via the kludge of a new "Begin Transaction"
statement just before returning the error code from InnerSP to OuterSP. Is
there a cleaner way to resolve this problem (beyond waiting for SQL Server
2005 try...catch blocks)?
Ron Strong
"Chad" <chad.dokmanovich@.unisys.com> wrote in message
news:cv7rc2$h6a$1@.trsvr.tr.unisys.com...
> Brian,
> Thank you again for your feedback. I appreciate the tip, in particular on
> handling concurrency problems using RowVersion, and I believe understand
the
> thrust of your points.
> However, I would like to place a spot light on a point I originally made
> that I feel may not have been addressed:
> *** If @.@.TRANCOUNT is = X in ParentSP when ChildSP is called, it must be =
X
> immediately after returning from the CHILD call. , else an error
results***
> If feel that this is the situation in the example you proposed.
> If the ParentSP BEGINs a TRANSACTON (Transaction count is now 1), then
calls
> ChildSP, which does a ROLLBACK within Child, TranCount will be 0 when
> control is returned to the Parent. Since TranCount was 1 just prior to
> calling the Child and it is zero immeditely after returning, this result
in
> an ERROR:
>
>
> This is the part that I am missing. It seems to me that the Child cannot
do
> the rollback if the Parent already began a Transaction.
> I hope I am not trying your patience. I would really like to get this
point
> down.
> Thanks,
> Chad
>
> "Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
> news:9E615D99-D61A-4AF3-BEFB-09C2C12281D3@.microsoft.com...
stored
to
will
and
error.
another
a
handler,
perform
0.
fail,
that
calls
=
not
entered
--
outer
>|||Typo:
create proc dbo.ChildProc
as
set nocount on
declare @.trancount int
set @.trancount = @.@.TRANCOUNT
if @.trancount = 0 -- No existing tran
begin tran ChildProcTran
else -- Existing tran
save tran ChildProcTran
/*
Do some stuff
*/
if @.@.ERROR > 0
begin
raiserror ('We have a problem.', 16, 1)
rollback ChildProcTran
return
end
else if @.trancount = 0 -- began our own
commit tran
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%232UHhStFFHA.3972@.TK2MSFTNGP15.phx.gbl...
PMFJI, but if your child proc is using an explicit tran, then it can be
coded as follows:
create proc dbo.ChildProc
as
set nocount on
declare @.trancount int
set @.trancount = @.@.TRANCOUNT
if @.trancount > 0
begin tran ChildProcTran
else
save tran ChildProcTran
/*
Do some stuff
*/
if @.@.ERROR > 0
begin
raiserror ('We have a problem.', 16, 1)
rollback ChildProcTran
return
end
else if @.trancount = 0 -- began our own
commit tran
go
This way, only the child proc's txn will be rolled back by the child proc.
The parent proc will be unaffected.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ron Strong" <rstrong@.DoNotSpamerols.com> wrote in message
news:ulKZzJtFFHA.1528@.TK2MSFTNGP09.phx.gbl...
Brian & Chad
I believe I'm having the same issue as Chad with nested stored procedures
inside a transaction.
What I'd like to do is begin a transaction in an outer SP. If all goes
well, the transaction will be committed in the outer stored procedure - no
problem there. However, if an error or other unexpected condition is
encountered, I would like to rollback as close to the error as possible (in
the statement following detection, if possible).
Problem is this might involve a transaction begun in an outer SP being
rollbed back in an inner SP. Due to the fact that on entry to inner SP
@.@.Trancount == 1 but on exit @.@.Trancount == 0, a new error, Error 266, gets
generated.
Following illustrates the problem:
-- INNER SP
create procedure InnerSP as begin
declare @.ErrNo int
/* Do something here */
select @.ErrNo = @.@.ERROR
if @.ErrNo <> 0 begin
if @.@.TRANCOUNT > 0 rollback transaction
return @.ErrNo -- on return new error (266) generated
end
return 0
end
-- OUTER SP
create procedure OuterSP as begin
declare @.ErrNo int
Begin Transaction
exec @.ErrNo = InnerSP
-- if InnerSP failed, @.@.ERROR = 266 here
if @.ErrNo <> 0 begin
if @.@.TRANCOUNT > 0 rollback transaction
return @.ErrNo
end
commit transaction
return 0
end
I could hold off on performing the rollback until OuterSP examines the
return value from InnerSP, but this is not ideal:
(1) Immediately after the error I may want to do some logging or
other fixup. If these are done before the rollback, they will be wiped out
by the rollback.
(2) In code subsequent to ther error there is always the possibility
that execution will be terminated due to a severe error, preventing the
enclosing SP from ever executing the rollback. While the lack of a
subsequent COMMIT will ultimately lead to the transaction being rolled back,
I would have no control over when the rollback occurs.
I can avoid this problem via the kludge of a new "Begin Transaction"
statement just before returning the error code from InnerSP to OuterSP. Is
there a cleaner way to resolve this problem (beyond waiting for SQL Server
2005 try...catch blocks)?
Ron Strong
"Chad" <chad.dokmanovich@.unisys.com> wrote in message
news:cv7rc2$h6a$1@.trsvr.tr.unisys.com...
> Brian,
> Thank you again for your feedback. I appreciate the tip, in particular on
> handling concurrency problems using RowVersion, and I believe understand
the
> thrust of your points.
> However, I would like to place a spot light on a point I originally made
> that I feel may not have been addressed:
> *** If @.@.TRANCOUNT is = X in ParentSP when ChildSP is called, it must be =
X
> immediately after returning from the CHILD call. , else an error
results***
> If feel that this is the situation in the example you proposed.
> If the ParentSP BEGINs a TRANSACTON (Transaction count is now 1), then
calls
> ChildSP, which does a ROLLBACK within Child, TranCount will be 0 when
> control is returned to the Parent. Since TranCount was 1 just prior to
> calling the Child and it is zero immeditely after returning, this result
in
> an ERROR:
>
>
> This is the part that I am missing. It seems to me that the Child cannot
do
> the rollback if the Parent already began a Transaction.
> I hope I am not trying your patience. I would really like to get this
point
> down.
> Thanks,
> Chad
>
> "Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
> news:9E615D99-D61A-4AF3-BEFB-09C2C12281D3@.microsoft.com...
stored
to
will
and
error.
another
a
handler,
perform
0.
fail,
that
calls
=
not
entered
--
outer
>|||This will take care of what is done in the child, but what I want to do is
rollback the entire outer transaction - the one initiated in the outer SP.
My example may have been too brief -- the outer SP may be making calls to
several child SPs. What I would like is that any error, whether
encountered in the outer SP or its child SPs, results in an immediate
rollback of all the work performed within the transaction initiated in the
outer SP.
The rule, enforced by the raising of error 266, that entry Trancount = exit
Trancount, seems to preclude doing this.
Ron Strong
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#2UHhStFFHA.3972@.TK2MSFTNGP15.phx.gbl...
> PMFJI, but if your child proc is using an explicit tran, then it can be
> coded as follows:
> create proc dbo.ChildProc
> as
> set nocount on
> declare @.trancount int
> set @.trancount = @.@.TRANCOUNT
> if @.trancount > 0
> begin tran ChildProcTran
> else
> save tran ChildProcTran
> /*
> Do some stuff
> */
> if @.@.ERROR > 0
> begin
> raiserror ('We have a problem.', 16, 1)
> rollback ChildProcTran
> return
> end
> else if @.trancount = 0 -- began our own
> commit tran
> go
> This way, only the child proc's txn will be rolled back by the child proc.
> The parent proc will be unaffected.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Ron Strong" <rstrong@.DoNotSpamerols.com> wrote in message
> news:ulKZzJtFFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Brian & Chad
> I believe I'm having the same issue as Chad with nested stored procedures
> inside a transaction.
> What I'd like to do is begin a transaction in an outer SP. If all goes
> well, the transaction will be committed in the outer stored procedure - no
> problem there. However, if an error or other unexpected condition is
> encountered, I would like to rollback as close to the error as possible
(in
> the statement following detection, if possible).
> Problem is this might involve a transaction begun in an outer SP being
> rollbed back in an inner SP. Due to the fact that on entry to inner SP
> @.@.Trancount == 1 but on exit @.@.Trancount == 0, a new error, Error 266,
gets
> generated.
> Following illustrates the problem:
> -- INNER SP
> create procedure InnerSP as begin
> declare @.ErrNo int
> /* Do something here */
> select @.ErrNo = @.@.ERROR
> if @.ErrNo <> 0 begin
> if @.@.TRANCOUNT > 0 rollback transaction
> return @.ErrNo -- on return new error (266) generated
> end
> return 0
> end
> -- OUTER SP
> create procedure OuterSP as begin
> declare @.ErrNo int
> Begin Transaction
> exec @.ErrNo = InnerSP
> -- if InnerSP failed, @.@.ERROR = 266 here
> if @.ErrNo <> 0 begin
> if @.@.TRANCOUNT > 0 rollback transaction
> return @.ErrNo
> end
> commit transaction
> return 0
> end
>
> I could hold off on performing the rollback until OuterSP examines the
> return value from InnerSP, but this is not ideal:
> (1) Immediately after the error I may want to do some logging or
> other fixup. If these are done before the rollback, they will be wiped out
> by the rollback.
> (2) In code subsequent to ther error there is always the
possibility
> that execution will be terminated due to a severe error, preventing the
> enclosing SP from ever executing the rollback. While the lack of a
> subsequent COMMIT will ultimately lead to the transaction being rolled
back,
> I would have no control over when the rollback occurs.
> I can avoid this problem via the kludge of a new "Begin Transaction"
> statement just before returning the error code from InnerSP to OuterSP.
Is
> there a cleaner way to resolve this problem (beyond waiting for SQL Server
> 2005 try...catch blocks)?
>
> Ron Strong
> "Chad" <chad.dokmanovich@.unisys.com> wrote in message
> news:cv7rc2$h6a$1@.trsvr.tr.unisys.com...
on
> the
=
> X
> results***
> calls
> in
0.
> do
> point
> stored
procedure
> to
> will
detected
rolls
> and
had
and
> error.
> another
not
call
> a
child,
proc,
> handler,
an
> perform
> 0.
message
> fail,
> that
on
entry.
> calls
a
be
> =
> not
seems
> entered
>
> --
> outer
>|||Well, you could nevertheless have the child manage itself, since it never
knows how it will be called. When it throws its error, it returns to the
calling proc, which then decides to rollback and exit, without calling any
more procs.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ron Strong" <rstrong@.DoNotSpamerols.com> wrote in message
news:O2r1EmtFFHA.3648@.TK2MSFTNGP09.phx.gbl...
This will take care of what is done in the child, but what I want to do is
rollback the entire outer transaction - the one initiated in the outer SP.
My example may have been too brief -- the outer SP may be making calls to
several child SPs. What I would like is that any error, whether
encountered in the outer SP or its child SPs, results in an immediate
rollback of all the work performed within the transaction initiated in the
outer SP.
The rule, enforced by the raising of error 266, that entry Trancount = exit
Trancount, seems to preclude doing this.
Ron Strong
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#2UHhStFFHA.3972@.TK2MSFTNGP15.phx.gbl...
> PMFJI, but if your child proc is using an explicit tran, then it can be
> coded as follows:
> create proc dbo.ChildProc
> as
> set nocount on
> declare @.trancount int
> set @.trancount = @.@.TRANCOUNT
> if @.trancount > 0
> begin tran ChildProcTran
> else
> save tran ChildProcTran
> /*
> Do some stuff
> */
> if @.@.ERROR > 0
> begin
> raiserror ('We have a problem.', 16, 1)
> rollback ChildProcTran
> return
> end
> else if @.trancount = 0 -- began our own
> commit tran
> go
> This way, only the child proc's txn will be rolled back by the child proc.
> The parent proc will be unaffected.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "Ron Strong" <rstrong@.DoNotSpamerols.com> wrote in message
> news:ulKZzJtFFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Brian & Chad
> I believe I'm having the same issue as Chad with nested stored procedures
> inside a transaction.
> What I'd like to do is begin a transaction in an outer SP. If all goes
> well, the transaction will be committed in the outer stored procedure - no
> problem there. However, if an error or other unexpected condition is
> encountered, I would like to rollback as close to the error as possible
(in
> the statement following detection, if possible).
> Problem is this might involve a transaction begun in an outer SP being
> rollbed back in an inner SP. Due to the fact that on entry to inner SP
> @.@.Trancount == 1 but on exit @.@.Trancount == 0, a new error, Error 266,
gets
> generated.
> Following illustrates the problem:
> -- INNER SP
> create procedure InnerSP as begin
> declare @.ErrNo int
> /* Do something here */
> select @.ErrNo = @.@.ERROR
> if @.ErrNo <> 0 begin
> if @.@.TRANCOUNT > 0 rollback transaction
> return @.ErrNo -- on return new error (266) generated
> end
> return 0
> end
> -- OUTER SP
> create procedure OuterSP as begin
> declare @.ErrNo int
> Begin Transaction
> exec @.ErrNo = InnerSP
> -- if InnerSP failed, @.@.ERROR = 266 here
> if @.ErrNo <> 0 begin
> if @.@.TRANCOUNT > 0 rollback transaction
> return @.ErrNo
> end
> commit transaction
> return 0
> end
>
> I could hold off on performing the rollback until OuterSP examines the
> return value from InnerSP, but this is not ideal:
> (1) Immediately after the error I may want to do some logging or
> other fixup. If these are done before the rollback, they will be wiped out
> by the rollback.
> (2) In code subsequent to ther error there is always the
possibility
> that execution will be terminated due to a severe error, preventing the
> enclosing SP from ever executing the rollback. While the lack of a
> subsequent COMMIT will ultimately lead to the transaction being rolled
back,
> I would have no control over when the rollback occurs.
> I can avoid this problem via the kludge of a new "Begin Transaction"
> statement just before returning the error code from InnerSP to OuterSP.
Is
> there a cleaner way to resolve this problem (beyond waiting for SQL Server
> 2005 try...catch blocks)?
>
> Ron Strong
> "Chad" <chad.dokmanovich@.unisys.com> wrote in message
> news:cv7rc2$h6a$1@.trsvr.tr.unisys.com...
on
> the
=
> X
> results***
> calls
> in
0.
> do
> point
> stored
procedure
> to
> will
detected
rolls
> and
had
and
> error.
> another
not
call
> a
child,
proc,
> handler,
an
> perform
> 0.
message
> fail,
> that
on
entry.
> calls
a
be
> =
> not
seems
> entered
>
> --
> outer
>

Chinese chars through stored procedures...

Hi,
I have a problem with my Stored Procedures...
Recently we decided to change the type of our column in our databse from
varchar to nvarchar because of new customers (chinese).
Everything works fine EXCEPT the stored procedures... When i try to pass
chinese characters for a simple SP that those a basic insert in my table, it
inserts ? instead of chinese characters...
Did i miss something obvious?
Thanks a lot!
Etienne
p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on this
newsgroup.Etienne,
Did you also change the types of the variables in the stored procedures? If
not, then you will have conversions happening.
You might also find this article useful if the international and Chinese
world are new to you:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
news:hQqvb.16446$iT4.2055861@.news20.bellglobal.com...
> Hi,
> I have a problem with my Stored Procedures...
> Recently we decided to change the type of our column in our databse from
> varchar to nvarchar because of new customers (chinese).
> Everything works fine EXCEPT the stored procedures... When i try to pass
> chinese characters for a simple SP that those a basic insert in my table,
it
> inserts ? instead of chinese characters...
> Did i miss something obvious?
> Thanks a lot!
> Etienne
> p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on this
> newsgroup.
>|||Hi Russell,
Yep, changed the types of variables too...
I'm really confused, nowhere on the web i could find somebody with the same
problem as me...
What is strange is that with my access application, i can write, update and
select (of course!) any field that has chinese characters. But when i try
my SP with Query Analyzer, it inserts ? for every chinese character...
I'll keep looking, thank you anyway!
Etienne
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OFh7i2EsDHA.1600@.TK2MSFTNGP10.phx.gbl...
> Etienne,
> Did you also change the types of the variables in the stored procedures?
If
> not, then you will have conversions happening.
> You might also find this article useful if the international and Chinese
> world are new to you:
> http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
>
> Russell Fields
> http://www.sqlpass.org/
> 2004 PASS Community Summit - Orlando
> - The largest user-event dedicated to SQL Server!
> "Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
> news:hQqvb.16446$iT4.2055861@.news20.bellglobal.com...
> >
> > Hi,
> > I have a problem with my Stored Procedures...
> > Recently we decided to change the type of our column in our databse from
> > varchar to nvarchar because of new customers (chinese).
> > Everything works fine EXCEPT the stored procedures... When i try to
pass
> > chinese characters for a simple SP that those a basic insert in my
table,
> it
> > inserts ? instead of chinese characters...
> > Did i miss something obvious?
> > Thanks a lot!
> > Etienne
> >
> > p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on this
> > newsgroup.
> >
> >
>|||Damn! Finally found it!
I had to put the letter N (capital) in front of each of my Unicode value...
example:
exec sp_SimpleInsert N'(chinesetext1)',N'(chineseText2)',N'(chineseText3)'
Thanks a lot!
"Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
news:1Xrvb.16528$iT4.2069449@.news20.bellglobal.com...
> Hi Russell,
> Yep, changed the types of variables too...
> I'm really confused, nowhere on the web i could find somebody with the
same
> problem as me...
> What is strange is that with my access application, i can write, update
and
> select (of course!) any field that has chinese characters. But when i try
> my SP with Query Analyzer, it inserts ? for every chinese character...
> I'll keep looking, thank you anyway!
> Etienne
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:OFh7i2EsDHA.1600@.TK2MSFTNGP10.phx.gbl...
> > Etienne,
> >
> > Did you also change the types of the variables in the stored procedures?
> If
> > not, then you will have conversions happening.
> >
> > You might also find this article useful if the international and Chinese
> > world are new to you:
> > http://msdn.microsoft.com/library/default.asp?
> > url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
> >
> >
> > Russell Fields
> > http://www.sqlpass.org/
> > 2004 PASS Community Summit - Orlando
> > - The largest user-event dedicated to SQL Server!
> >
> > "Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
> > news:hQqvb.16446$iT4.2055861@.news20.bellglobal.com...
> > >
> > > Hi,
> > > I have a problem with my Stored Procedures...
> > > Recently we decided to change the type of our column in our databse
from
> > > varchar to nvarchar because of new customers (chinese).
> > > Everything works fine EXCEPT the stored procedures... When i try to
> pass
> > > chinese characters for a simple SP that those a basic insert in my
> table,
> > it
> > > inserts ? instead of chinese characters...
> > > Did i miss something obvious?
> > > Thanks a lot!
> > > Etienne
> > >
> > > p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on
this
> > > newsgroup.
> > >
> > >
> >
> >
>|||Congratulations
Russell Fields
"Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
news:B5svb.16541$iT4.2072482@.news20.bellglobal.com...
> Damn! Finally found it!
> I had to put the letter N (capital) in front of each of my Unicode
value...
> example:
> exec sp_SimpleInsert N'(chinesetext1)',N'(chineseText2)',N'(chineseText3)'
> Thanks a lot!
> "Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
> news:1Xrvb.16528$iT4.2069449@.news20.bellglobal.com...
> > Hi Russell,
> > Yep, changed the types of variables too...
> > I'm really confused, nowhere on the web i could find somebody with the
> same
> > problem as me...
> > What is strange is that with my access application, i can write, update
> and
> > select (of course!) any field that has chinese characters. But when i
try
> > my SP with Query Analyzer, it inserts ? for every chinese character...
> > I'll keep looking, thank you anyway!
> > Etienne
> >
> > "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> > news:OFh7i2EsDHA.1600@.TK2MSFTNGP10.phx.gbl...
> > > Etienne,
> > >
> > > Did you also change the types of the variables in the stored
procedures?
> > If
> > > not, then you will have conversions happening.
> > >
> > > You might also find this article useful if the international and
Chinese
> > > world are new to you:
> > > http://msdn.microsoft.com/library/default.asp?
> > > url=/library/en-us/dnsql2k/html/intlfeaturesinsqlserver2000.asp
> > >
> > >
> > > Russell Fields
> > > http://www.sqlpass.org/
> > > 2004 PASS Community Summit - Orlando
> > > - The largest user-event dedicated to SQL Server!
> > >
> > > "Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
> > > news:hQqvb.16446$iT4.2055861@.news20.bellglobal.com...
> > > >
> > > > Hi,
> > > > I have a problem with my Stored Procedures...
> > > > Recently we decided to change the type of our column in our databse
> from
> > > > varchar to nvarchar because of new customers (chinese).
> > > > Everything works fine EXCEPT the stored procedures... When i try to
> > pass
> > > > chinese characters for a simple SP that those a basic insert in my
> > table,
> > > it
> > > > inserts ? instead of chinese characters...
> > > > Did i miss something obvious?
> > > > Thanks a lot!
> > > > Etienne
> > > >
> > > > p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on
> this
> > > > newsgroup.
> > > >
> > > >
> > >
> > >
> >
> >
>|||On Fri, 21 Nov 2003 11:12:01 -0500, "Etienne M. St-Georges"
<nospam.etienne@.emstg.com> wrote:
>Hi,
>I have a problem with my Stored Procedures...
>Recently we decided to change the type of our column in our databse from
>varchar to nvarchar because of new customers (chinese).
>Everything works fine EXCEPT the stored procedures... When i try to pass
>chinese characters for a simple SP that those a basic insert in my table, it
>inserts ? instead of chinese characters...
>Did i miss something obvious?
>Thanks a lot!
G'day Etienne,
You don't mention how you are calling your stored procedures, but if you
are using ADO, note that you should use the Command object and
Parameters, and each nchar or nvarchar parameter should be created of
type adWChar or adWVarchar as appropriate.
cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander

Chinese chars through stored procedures...

Hi,
I have a problem with my Stored Procedures...
Recently we decided to change the type of our column in our databse from
varchar to nvarchar because of new customers (chinese).
Everything works fine EXCEPT the stored procedures... When i try to pass
chinese characters for a simple SP that those a basic insert in my table, it
inserts ? instead of chinese characters...
Did i miss something obvious?
Thanks a lot!
Etienne

p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on this
newsgroup.Check the source code of your stored procedure to make sure the parameter is
declared as nvarchar and not varchar.

HTH,
Dave

"Etienne M. St-Georges" <nospam.etienne@.emstg.com> wrote in message
news:MOqvb.16440$iT4.2055717@.news20.bellglobal.com ...
> Hi,
> I have a problem with my Stored Procedures...
> Recently we decided to change the type of our column in our databse from
> varchar to nvarchar because of new customers (chinese).
> Everything works fine EXCEPT the stored procedures... When i try to pass
> chinese characters for a simple SP that those a basic insert in my table,
it
> inserts ? instead of chinese characters...
> Did i miss something obvious?
> Thanks a lot!
> Etienne
> p.s.: you can email me at etienne_stgeorges@.hotmail.com or reply on this
> newsgroup.

Checking existance of record in Stored Procedure

Hi,

I am new to Stored Procedures. There is a simple procedure I want to create.

The scenario is this :

There is a Table named Test. In that table is a Column named 'TestID'. Now I want to create a Stored Procedure which inserts a record in Test, but if 'TestID' passed to it already exists, it should throw an error.

I am using C# and SQL Server 2005 Express.
Some code in C# also would be helpful.

You would need to create EITHER a unique index on the column, or a TRIGGER.

With the information at hand, I would vote for a UNIQUE index, and set the column to NOT NULL (so that a value MUST be supplied).

|||Thanks for the reply.

But I want to manage it programaticaly. I know about the constraints and SQL. But I am learning T-SQL and have created a scenario by myself to work on.

Basically I want to know how to throw error from stored proc? Or any way to let the app. know of some exceptional situations, like in .Net?|||

Inside your sp put the following code..

Code Snippet

Create Procedure YouSpName(@.TestId int, other params....)

as

Begin

If Exists(Select 1 From Test Where TestId=@.TestId)

Begin

Raiserror ('Recorde Alreay Found', 16, 1)

Return

End

--Your actual code will be appended here

..

..

..

End

|||Thanks, It worked fine.|||

One of the 'hallmarks' of a competent and experienced SQL Server developer/dba, is to understand the impact of various options.

A TRIGGER executing consumes more server resources than a CONSTRAINT. A TRIGGER will, most likely, issue locks on data that could, under high usage scenarios, cause blocking situations for other users.

A CONSTRAINT has NONE of the negative impact of a TRIGGER, in fact, it is the least impactful way to force data conformance. When a CONSTRAINT is indicated, it is the 'best' option to engage. And a CONSTRAINT failure will definitely 'throw' an error that the application can catch and handle.

|||Thanks Arnie,

I can see what you are saying. I should have used the Unique Constraint. But as I said, this not the real thing. It's not part of a project or even a program. I wanted to know how to tell the application that an unexpected situation occurred in the stored procedure. Same as throwing exceptions in C#, VB or any .Net language. So, I created this hopeless scenario.

But I appreciate your concern and this information you typed might be useful to a lot of people who doesn't know this.

Thanks.

Check syntax in EM

Hello,
The "check syntax" under database->stored procedures in
the SQL 2K EM does not check the objects correctly when
you create/alter an SP. Always says "successful" even if
the table does not exists. Is this a bug?
SQL2K + SP3 on Win2003 Standard.
Thanks
Dennis
Object names referenced in an SP aren't resolved until the SP is compiled,
which means the first time it runs not when it is created. You need to test
the SP by calling it. Not a bug, although many people don't like this
feature.
David Portas
SQL Server MVP

Check syntax in EM

Hello,
The "check syntax" under database->stored procedures in
the SQL 2K EM does not check the objects correctly when
you create/alter an SP. Always says "successful" even if
the table does not exists. Is this a bug?
SQL2K + SP3 on Win2003 Standard.
Thanks
DennisObject names referenced in an SP aren't resolved until the SP is compiled,
which means the first time it runs not when it is created. You need to test
the SP by calling it. Not a bug, although many people don't like this
feature.
--
David Portas
SQL Server MVP
--

Check syntax in EM

Hello,
The "check syntax" under database->stored procedures in
the SQL 2K EM does not check the objects correctly when
you create/alter an SP. Always says "successful" even if
the table does not exists. Is this a bug?
SQL2K + SP3 on Win2003 Standard.
Thanks
DennisObject names referenced in an SP aren't resolved until the SP is compiled,
which means the first time it runs not when it is created. You need to test
the SP by calling it. Not a bug, although many people don't like this
feature.
David Portas
SQL Server MVP
--

Check Stored Procedure execution duration

Hello,
I need to check the duration of execution of some Stored
Procedures. I dont know when these stored procedures will
run, so i execute the Profiler to catch these Stored
Procedures but i dont know if im going to get the desired
result.
Am I doing it right or may i do anything else?
Best regards.
You are on the right way, Profiler is exactly the tool you need.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:15bd401c446f7$85765e10$a501280a@.phx.gbl...
> Hello,
> I need to check the duration of execution of some Stored
> Procedures. I dont know when these stored procedures will
> run, so i execute the Profiler to catch these Stored
> Procedures but i dont know if im going to get the desired
> result.
> Am I doing it right or may i do anything else?
> Best regards.
>
|||Hi,
"You are doing right."
Use Profiler - Use the template name " SQLProfilerTSQL_duration" in
profiler. In the filter you can give the procedure names you
have to trace for duration.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:15bd401c446f7$85765e10$a501280a@.phx.gbl...
> Hello,
> I need to check the duration of execution of some Stored
> Procedures. I dont know when these stored procedures will
> run, so i execute the Profiler to catch these Stored
> Procedures but i dont know if im going to get the desired
> result.
> Am I doing it right or may i do anything else?
> Best regards.
>

Check Stored Procedure execution duration

Hello,
I need to check the duration of execution of some Stored
Procedures. I dont know when these stored procedures will
run, so i execute the Profiler to catch these Stored
Procedures but i dont know if im going to get the desired
result.
Am I doing it right or may i do anything else?
Best regards.You are on the right way, Profiler is exactly the tool you need.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:15bd401c446f7$85765e10$a501280a@.phx.gbl...
> Hello,
> I need to check the duration of execution of some Stored
> Procedures. I dont know when these stored procedures will
> run, so i execute the Profiler to catch these Stored
> Procedures but i dont know if im going to get the desired
> result.
> Am I doing it right or may i do anything else?
> Best regards.
>|||Hi,
"You are doing right."
Use Profiler - Use the template name " SQLProfilerTSQL_duration" in
profiler. In the filter you can give the procedure names you
have to trace for duration.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:15bd401c446f7$85765e10$a501280a@.phx.gbl...
> Hello,
> I need to check the duration of execution of some Stored
> Procedures. I dont know when these stored procedures will
> run, so i execute the Profiler to catch these Stored
> Procedures but i dont know if im going to get the desired
> result.
> Am I doing it right or may i do anything else?
> Best regards.
>

Check Stored Procedure execution duration

Hello,
I need to check the duration of execution of some Stored
Procedures. I dont know when these stored procedures will
run, so i execute the Profiler to catch these Stored
Procedures but i dont know if im going to get the desired
result.
Am I doing it right or may i do anything else?
Best regards.You are on the right way, Profiler is exactly the tool you need.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:15bd401c446f7$85765e10$a501280a@.phx
.gbl...
> Hello,
> I need to check the duration of execution of some Stored
> Procedures. I dont know when these stored procedures will
> run, so i execute the Profiler to catch these Stored
> Procedures but i dont know if im going to get the desired
> result.
> Am I doing it right or may i do anything else?
> Best regards.
>|||Hi,
"You are doing right."
Use Profiler - Use the template name " SQLProfilerTSQL_duration" in
profiler. In the filter you can give the procedure names you
have to trace for duration.
Thanks
Hari
MCDBA
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:15bd401c446f7$85765e10$a501280a@.phx
.gbl...
> Hello,
> I need to check the duration of execution of some Stored
> Procedures. I dont know when these stored procedures will
> run, so i execute the Profiler to catch these Stored
> Procedures but i dont know if im going to get the desired
> result.
> Am I doing it right or may i do anything else?
> Best regards.
>