Error inside trigger

hi

there is a programing flow that I want to know exactly what will happen

1- A database which has 3 table T1,T2,T3

2-a Storeprocedure called STP

3- Inside stp we insert to T1 and T2

4-T2 has 2 triggers TRG1,TRG2

5 - TRG2 check some thing and raise error .

so, I want to know at this case all rows which inserted will rollback or no

You are inserting in more than one table, so if you want to rollback the whole transaction inside the trigger, you have to initiate an explicit transaction using "begin transaction" (before start inserting), if not, just the insert on T2 will be rolled back.

AMB

|||

TRG2 will ROLLBACK the INSERT into T2 ONLY.

As Alejandro indicated, if you wish to ROLLBACK ALL activites, you need to start a TRANSACTION inside STP.

|||

I did think like u said befor but !!! you try this :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[Cola] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Colb] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

go

exec sys.sp_addmessage @.msgnum= 50001, @.severity= 16, @.msgtext= N'User error', @.replace=N'replace'

go

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

createTRIGGER dbo.trg1

ON dbo.table_1

AFTER INSERT,UPDATE

AS

BEGIN

SETNOCOUNTON;

raiserror(50001,16,1)

END

GO

they make u a table (Table_1) and a trigger (Trg1)

as u can see in Trg1 an error will raise so if we insert any row it will be rollbacked. so try to insert :

INSERTINTO [dbo].[Table_1]

([Cola]

,[Colb])

VALUES

('aaa'

,'bbb')

then u can see a row added !!!

sql

Error in sql 2000: Incorrect syntax near ')'.

I get this error:
Server: Msg 102, Level 15, State 1, Line 62
Incorrect syntax near ')'.
Here is the request that I put inside my cursor:

exec ('sp_grantdbaccess @.loginame=' + @.newLoginParam )
set @.sqlGrant='grant update on mosaikdb741.dbo.loginlistInput to ' + @.newLoginParam
EXEC (@.sqlGrant)

Is there any thing wrong?
thank you

Replace the [EXEC] with [PRINT] and examine the statement. I think you will then see the issue.|||

Would you be able to provide the value of @.newLoginParam that is causing the problem?

Chris

|||As I indicated earlier, if you were to PRINT and examine the value of @.sqlGrant, you would readily see that there 'may' be a problem with quotes.|||

exec ('sp_grantdbaccess @.loginame= ''' + @.newLoginParam + '''')

...but work only if you dont have ' into @.newLoginParam

My suggestion is to use sp_executesql with separate parameters settings.

Mauro

Error in sql 2000: Incorrect syntax near ')'.

I get this error:
Server: Msg 102, Level 15, State 1, Line 62
Incorrect syntax near ')'.
Here is the request that I put inside my cursor:

exec ('sp_grantdbaccess @.loginame=' + @.newLoginParam )
set @.sqlGrant='grant update on mosaikdb741.dbo.loginlistInput to ' + @.newLoginParam
EXEC (@.sqlGrant)

Is there any thing wrong?
thank you

Replace the [EXEC] with [PRINT] and examine the statement. I think you will then see the issue.|||

Would you be able to provide the value of @.newLoginParam that is causing the problem?

Chris

|||As I indicated earlier, if you were to PRINT and examine the value of @.sqlGrant, you would readily see that there 'may' be a problem with quotes.|||

exec ('sp_grantdbaccess @.loginame= ''' + @.newLoginParam + '''')

...but work only if you dont have ' into @.newLoginParam

My suggestion is to use sp_executesql with separate parameters settings.

Mauro

Error in mdx querys with quotation marks

The problem:
When I try to execute a mdx with quotation mark inside, I get an error!
Why?
I know that (') is from mdx language.
Will I have to erase this characters from my databases?
I don't think it's good.
In my javascript code(asp):
var cst = new ActiveXObject("ADOMD.Cellset");
...
cst.Source = strSource + " CELL PROPERTIES FORMATTED_VALUE, BACK_COLOR,
FORE_COLOR";
cst.open();
strSource = " 'with ...'
Select {[Programa].[Sigla].[Todos os Programas].[Ajuda
r 'A INDUSTRIA],
[Programa].[Sigla].[Todos os Programas].[APOIO A PROJ SOCIAL
],[Programa].
[Sigla].[Todos os Programas].[APOIO ENSINO SUPERIO]}
on rows,{[Measures].[Libera'es]} ON COLUMNS FROM [dwe_cubo
_pird] CELL
PROPERTIES FORMATTED_VALUE, BACK_COLOR, FORE_COLOR"
The error occur when I execute cst.open()!
Microsoft? OLE DB Provider for Analysis Services error '80040e14'
Syntax error, expecting SELECT, near: 'A INDUSTRIA],[Programa].[Sigl
a].
[Todos...
Message posted via http://www.droptable.comYou need to double up quotation marks within quotes:

strSource = " 'with ...'
Select {[Programa].[Sigla].[Todos os Programas].[Ajudar
''A INDUSTRIA],
[Programa].[Sigla].[Todos os Programas].[APOIO A PROJ
SOCIAL],[Programa].
[Sigla].[Todos os Programas].[APOIO ENSINO SUPERIO]}
on rows,{[Measures].[Libera'es]} ON COLUMNS FROM [dwe_cubo
_pird] CELL
PROPERTIES FORMATTED_VALUE, BACK_COLOR, FORE_COLOR"[vbcol=seagreen]
Here's an earlier thread that discusses this:
http://groups-beta.google.com/group...erver.olap/msg/
5b1f83a30f0203eb[vbcol=seagreen]
Newsgroups: microsoft.public.sqlserver.olap
From: "George Spofford"
Date: Sat, 1 Jun 2002 07:33:16 -0700
Subject: MDX Error with Current Year's
One of those silly things: double it up inside.
With member [measures].[abc] as
'count(descendants([category]._[all Category].[current
year''s].[actuals],,leaves))'
HTH
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab
http://www.dsslab.com
geo...@.dsslab.com
ISVs & IT organizations: Find out how DSS Lab can speed
your development!
[vbcol=seagreen]
>--Original Message--
>Hello,
>the following MDX fails:

>The error comes because the uniquename is ..year's]

>With member [measures].[abc] as
>'count(descendants([category]_.[all Category].[current
>year's].[actuals],,leaves))'

>How can i write a valid mdx?
[vbcol=seagreen]
>Thanks for reading this
>Jrg
>.
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||There is another error!
Microsoft? OLE DB Provider for Analysis Services error '80040e14'
Formula error - cannot find dimension member ("[Programa].[Sigla].&#
91;Todos os
Programas].[APOIO ''A INDUSTRIA]") - in a name-binding function
/Server_1.asp, line 520
"Deepak Puri" wrote:

> You need to double up quotation marks within quotes:
>
> strSource = " 'with ...'
> Select {[Programa].[Sigla].[Todos os Programas].[Ajud
ar ''A INDUSTRIA],
> [Programa].[Sigla].[Todos os Programas].[APOIO A PROJ
> SOCIAL],[Programa].
> [Sigla].[Todos os Programas].[APOIO ENSINO SUPERIO]}
> on rows,{[Measures].[Libera'es]} ON COLUMNS FROM [dwe_cu
bo_pird] CELL
> PROPERTIES FORMATTED_VALUE, BACK_COLOR, FORE_COLOR"
>
> Here's an earlier thread that discusses this:
> http://groups-beta.google.com/group...erver.olap/msg/
> 5b1f83a30f0203eb
> Newsgroups: microsoft.public.sqlserver.olap
> From: "George Spofford"
> Date: Sat, 1 Jun 2002 07:33:16 -0700
> Subject: MDX Error with Current Year's
> One of those silly things: double it up inside.
> With member [measures].[abc] as
> 'count(descendants([category].-[all Category].[current
> year''s].[actuals],,leaves))'
> HTH
> --
> George Spofford
> Microsoft MVP
> Chief Architect / OLAP Solution Provider
> DSS Lab
> http://www.dsslab.com
> geo...@.dsslab.com
> ISVs & IT organizations: Find out how DSS Lab can speed
> your development!
>
>
>
>
>
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Maybe you used double-quote characters (Ascii 34), instead of 2
single-quote characters (Ascii 39) - that's the only way I could create
the error message you got?
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Did you copy your code from your application source and paste it here?
The reason I ask is that in my Internet Explorer, the so-called single
quote characters in the member names do not appear as single quotes but
rather as unicode gibberish. The single quotes surrounding calculation
value definitions appear as they should.
The fact your mdx runs on some applications and does not on others leads
me to be farely certain that the member names in your cube do not
contain regular single quote characters but other characters, and that
is what causes your problems.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Error in Locale when passing datetime parameter?

Hello!
I am using RS supplied with SQl Server 2005. I have one report and two
subreports inside it. One of the subreports is embedded and another one can
be navigated using hypertext link. All reports have one datetime parameter
which is entered by user in the main report and passed to subreports. But
when in the main report I enter 1-st of november 2006 in subreports it
becomes 10-th of january 2006. All reports have language = default. When
debugging report (F5) everything is ok. The problem comes out when publishing
report to report server.
Any suggestions/advises are welcome.What is the build number of the Report Server?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
message news:63910111-34C6-4B1C-A34E-E52FCFA0CE00@.microsoft.com...
> Hello!
> I am using RS supplied with SQl Server 2005. I have one report and two
> subreports inside it. One of the subreports is embedded and another one
> can
> be navigated using hypertext link. All reports have one datetime parameter
> which is entered by user in the main report and passed to subreports. But
> when in the main report I enter 1-st of november 2006 in subreports it
> becomes 10-th of january 2006. All reports have language = default. When
> debugging report (F5) everything is ok. The problem comes out when
> publishing
> report to report server.
> Any suggestions/advises are welcome.|||Hello Lev
Can not say for sure - I looked bin folder - version of most libraries is
9.00.2047.00
others are have 9.00.1399.00.
"Lev Semenets [MSFT]" wrote:
> What is the build number of the Report Server?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
> message news:63910111-34C6-4B1C-A34E-E52FCFA0CE00@.microsoft.com...
> > Hello!
> >
> > I am using RS supplied with SQl Server 2005. I have one report and two
> > subreports inside it. One of the subreports is embedded and another one
> > can
> > be navigated using hypertext link. All reports have one datetime parameter
> > which is entered by user in the main report and passed to subreports. But
> > when in the main report I enter 1-st of november 2006 in subreports it
> > becomes 10-th of january 2006. All reports have language = default. When
> > debugging report (F5) everything is ok. The problem comes out when
> > publishing
> > report to report server.
> >
> > Any suggestions/advises are welcome.
>
>|||Lets figure out details. Could you please reply via e-mail?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
message news:13DAE456-72AB-4F13-999F-E23165227726@.microsoft.com...
> Hello Lev
> Can not say for sure - I looked bin folder - version of most libraries is
> 9.00.2047.00
> others are have 9.00.1399.00.
> "Lev Semenets [MSFT]" wrote:
>> What is the build number of the Report Server?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
>> message news:63910111-34C6-4B1C-A34E-E52FCFA0CE00@.microsoft.com...
>> > Hello!
>> >
>> > I am using RS supplied with SQl Server 2005. I have one report and two
>> > subreports inside it. One of the subreports is embedded and another one
>> > can
>> > be navigated using hypertext link. All reports have one datetime
>> > parameter
>> > which is entered by user in the main report and passed to subreports.
>> > But
>> > when in the main report I enter 1-st of november 2006 in subreports it
>> > becomes 10-th of january 2006. All reports have language = default.
>> > When
>> > debugging report (F5) everything is ok. The problem comes out when
>> > publishing
>> > report to report server.
>> >
>> > Any suggestions/advises are welcome.
>>

Error in DB

I have the next problem:
when i try to insert a row inside a table there ir the
next error message:
Could not allocate space for object '<Table Name>' in
database '<DB Name>' because the 'PRIMARY' filegroup is
full.
How can i solve this problem ?
Thanks in advanceTHe problem could either be.
1. lack of disk space on the drive where the primary filegroup resides. (As
another poster suggests ) or
2. The filegroup may have a max size set... In SQL Enterprise Manager, right
click your database ->Properties, and check the data and log tab...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Enrico" <ezerilli@.csc.com> wrote in message
news:0da001c3db7c$e64030e0$a301280a@.phx.gbl...

quote:

---

> I have the next problem:
> when i try to insert a row inside a table there ir the
> next error message:
> Could not allocate space for object '<Table Name>' in
> database '<DB Name>' because the 'PRIMARY' filegroup is
> full.
> How can i solve this problem ?
> Thanks in advance

---

|||

quote:

---

>--Original Message--
>THe problem could either be.
>1. lack of disk space on the drive where the primary

---

filegroup resides. (As

quote:

---

>another poster suggests ) or
>2. The filegroup may have a max size set... In SQL

---

Enterprise Manager, right

quote:

---

>click your database ->Properties, and check the data and

---

log tab...

quote:

---

>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),

---

Charlotte, NC

quote:

---

>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server

---

(PASS) and it's

quote:

---

>community of SQL Server professionals.
>www.sqlpass.org
>"Enrico" <ezerilli@.csc.com> wrote in message
>news:0da001c3db7c$e64030e0$a301280a@.phx.gbl...
>
>.
>

---

I have solve the problem check the option Unrestricted
file Growth under DB properties/Transaction Log.
So i dont have any error about PRIMARY Filegroup.
Thanks a lot to every body.
Bye

Error in DB

I have the next problem:
when i try to insert a row inside a table there ir the
next error message:
Could not allocate space for object '<Table Name>' in
database '<DB Name>' because the 'PRIMARY' filegroup is
full.
How can i solve this problem ?
Thanks in advanceCheck the disk (drive) where your primary filegroup is
located and see how much space is left... try to free up
space (clean up files you dont need) if not create a
second filegroup...
>--Original Message--
>I have the next problem:
>when i try to insert a row inside a table there ir the
>next error message:
>Could not allocate space for object '<Table Name>' in
>database '<DB Name>' because the 'PRIMARY' filegroup is
>full.
>How can i solve this problem ?
>Thanks in advance
>.
>|||THe problem could either be.
1. lack of disk space on the drive where the primary filegroup resides. (As
another poster suggests ) or
2. The filegroup may have a max size set... In SQL Enterprise Manager, right
click your database ->Properties, and check the data and log tab...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Enrico" <ezerilli@.csc.com> wrote in message
news:0da001c3db7c$e64030e0$a301280a@.phx.gbl...
> I have the next problem:
> when i try to insert a row inside a table there ir the
> next error message:
> Could not allocate space for object '<Table Name>' in
> database '<DB Name>' because the 'PRIMARY' filegroup is
> full.
> How can i solve this problem ?
> Thanks in advance|||>--Original Message--
>THe problem could either be.
>1. lack of disk space on the drive where the primary
filegroup resides. (As
>another poster suggests ) or
>2. The filegroup may have a max size set... In SQL
Enterprise Manager, right
>click your database ->Properties, and check the data and
log tab...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>"Enrico" <ezerilli@.csc.com> wrote in message
>news:0da001c3db7c$e64030e0$a301280a@.phx.gbl...
>> I have the next problem:
>> when i try to insert a row inside a table there ir the
>> next error message:
>> Could not allocate space for object '<Table Name>' in
>> database '<DB Name>' because the 'PRIMARY' filegroup is
>> full.
>> How can i solve this problem ?
>> Thanks in advance
>
>.
>
I have solve the problem check the option Unrestricted
file Growth under DB properties/Transaction Log.
So i dont have any error about PRIMARY Filegroup.
Thanks a lot to every body.
Bye

error in --> EXEC xp_cmdshell ''bcp "SELECT * FROM tbl a where a.flag=N" queryout

some one can tell if i can make a "where" clause inside the BCP , when i say : select * FROM tbl where flag=N , usually the where flag='N' works with the ' ' , but it gives no error when save the store procedure without it ' ' saves nicely the SP,

but

like this it saves well "SELECT * FROM tbl where flag=N" and

when i execute it, give a sql statement error "Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name N."

i dont now , any help

PS: perhaps at the end of the BCP > -c -T , must have some more or less

Here the solution,

You have to use the escape sequence (Like \' or \" in other programming langauages).

SQL escape sequence for single quote is consecutive 2 single quotes ''.

EXEC xp_cmdshell 'bcp "SELECT * FROM tbl a where a.flag=''N''"

|||

Manivannan.D.Sekaran wrote:

Here the solution,

You have to use the escape sequence (Like \' or \" in other programming langauages).

SQL escape sequence for single quote is consecutive 2 single quotes ''.

EXEC xp_cmdshell 'bcp "SELECT * FROM tbl a where a.flag=''N''"

yes i tryed both ways with 'N' and "N" and it says the same error message

with this

'bcp "SELECT * FROM delta.dbo.tblRANGEL_O_STOCKS where updt="N"" queryout "'

Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name 'N'.

with this says

'bcp "SELECT * FROM delta.dbo.tblRANGEL_O_STOCKS where updt= 'N' " queryout "'

Msg 102, Level 15, State 1, Procedure spDELTA_P1_RANGEL_STOCKS, Line 59

Incorrect syntax near ' " queryout "'.

|||

Please copy and paste the following code,

Instead of 2 single quotes you are trying with 1 double quote.

Code Snippet

EXEC xp_cmdshell 'bcp "SELECT * FROM tbl a where a.flag=''N''" queryout F.txt -c -T'

|||

THANK YOU A LOT

and it was only a little thing like a '

error handling OPENROWSET

If, for example, i have inside a procedure the next statement:
EXEC ('SELECT TOP 0 id1 as id1, *
FROM OPENROWSET(''MSDASQL'', ''Driver={Microsoft Visual FoxPro Driver};
UID=;PWD=;SourceDB=' + @.path + ';
SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;
Collate=Machine;Null=Yes;Deleted=Yes;'', ''
SELECT * FROM ' + @.file + '
'')')

in some ocasions it will generate errors
- if no file or path,
- if no column, ...
how can i handle those errors and continue the procedure without generating an error.Error for "no column" when does that occur ? Only way i cna think of is if the file doe not exist.

To check if the file or path exists

declare @.Path varchar(128)
declare @.FileName varchar(10)

--i used 10 chars because foxpro2.6 allows only length of 10 for a filename and i am not sure what visual fp uses (also fp2.6 doesn't like spaces in path if that matters in your situation)

select @.Path = 'C:\', @.FileName = 'myfile.dbf'
declare @.i int
declare @.File varchar(1000)

select @.File = @.Path + @.FileName
exec master..xp_fileexist @.File, @.i out
if @.i = 1
EXEC ('SELECT TOP 0 id1 as id1, *
FROM OPENROWSET(''MSDASQL'', ''Driver={Microsoft Visual FoxPro Driver};
UID=;PWD=;SourceDB=' + @.path + ';
SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;
Collate=Machine;Null=Yes;Deleted=Yes;'', ''
SELECT * FROM ' + @.file + ''')')
else
print 'no file'

Error Handling in Activation Procs

I am kinda curious how the rest of you are doing your error handling inside your activation stored procedures...best practices says you should not rollback the part of your transaction that receives the record off of the queue...but using a try...catch block will only allow you to rollback the entire transaction. I tried using savepoints and starting the try...catch after the savepoint and the proc still gives me the error: "

The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction." when the error scenario is encountered.

I tried using @.@.Error and checking for errors at the statement level which would allow me to do a partial rollback, but the type of errors I receive (i.e. invalid data types etc) are aborting the entire batch instead of passing the error and continuing.

Can other people on this forum using service broker give me an idea of how you are getting around this issue?

Hmmm...interesting. Where did you read the best practice about not rolling back the part of your code that receives the record off of the queue. I am not sure that makes sense to me. I would want to put the message back onto the queue if I know I have ran into an error.|||The idea is to avoid Poison Messaging where possible by accounting for whatever errors you know you will encounter (foreign key errors, Invalid Data types)...these types of errors will never be able to run and will therefore, bring down your queue every time if you rollback. The idea is to never roll back the receive, unless there is some type of fatal error that causes the batch to abort. In that case, as long as your receive is in a transaction, it will be rolled back automatically. If it is a handled error, you need to do something else with that message to move it off the queue.|||

On one of our systems we use seperate transactions for recieving the message and processing the message. The transaction around the receive simply removes the message from the queue and logs it to an audit table. If this step fails then we rollback and put the message back onto the queue. If we successfully receive and log the message we then begin another transaction (if applicable) for the processing of the message. Therefore if this fails for some reason (ie constraints) then we just rollback the processing and not the receive from the queue - we can then get the message from the audit table to re-process if required. Our idea is to keep the code in the transaction that includes the receive from the queue as simple as possible to avoid failures and prevent rollbacks - therefore avoiding poison messages. I'm not sure if this would be applicable in your case.

error handling flow

i've got a stored procedure (a) that calls another stored procedure
(b). inside b i become a deadlock victim and i die. how can i "catch"
this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
using sql server 2003 so no try/catch. thanks!
arthur
You're not using SQL Server 2003, since it doesn't exists. I assume a typo and you mean 2000.
You cannot catch a deadlock at the TSQL level. You have to do it in the client. This is one of the
nice things with TRY/CATCH in 2005, that you can catch a deadlock situation at the TSQL level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"arthur" <alangham@.gmail.com> wrote in message
news:1138909906.663154.56220@.z14g2000cwz.googlegro ups.com...
> i've got a stored procedure (a) that calls another stored procedure
> (b). inside b i become a deadlock victim and i die. how can i "catch"
> this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
> using sql server 2003 so no try/catch. thanks!
> arthur
>
|||2000, right. thanks for clearing that up (not the 2000 part, the catch
part)!

error handling flow

i've got a stored procedure (a) that calls another stored procedure
(b). inside b i become a deadlock victim and i die. how can i "catch"
this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
using sql server 2003 so no try/catch. thanks!
arthurYou're not using SQL Server 2003, since it doesn't exists. I assume a typo and you mean 2000.
You cannot catch a deadlock at the TSQL level. You have to do it in the client. This is one of the
nice things with TRY/CATCH in 2005, that you can catch a deadlock situation at the TSQL level.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"arthur" <alangham@.gmail.com> wrote in message
news:1138909906.663154.56220@.z14g2000cwz.googlegroups.com...
> i've got a stored procedure (a) that calls another stored procedure
> (b). inside b i become a deadlock victim and i die. how can i "catch"
> this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
> using sql server 2003 so no try/catch. thanks!
> arthur
>|||2000, right. thanks for clearing that up (not the 2000 part, the catch
part)!

error handling flow

i've got a stored procedure (a) that calls another stored procedure
(b). inside b i become a deadlock victim and i die. how can i "catch"
this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
using sql server 2003 so no try/catch. thanks!
arthurYou're not using SQL Server 2003, since it doesn't exists. I assume a typo a
nd you mean 2000.
You cannot catch a deadlock at the TSQL level. You have to do it in the clie
nt. This is one of the
nice things with TRY/CATCH in 2005, that you can catch a deadlock situation
at the TSQL level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"arthur" <alangham@.gmail.com> wrote in message
news:1138909906.663154.56220@.z14g2000cwz.googlegroups.com...
> i've got a stored procedure (a) that calls another stored procedure
> (b). inside b i become a deadlock victim and i die. how can i "catch"
> this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
> using sql server 2003 so no try/catch. thanks!
> arthur
>|||2000, right. thanks for clearing that up (not the 2000 part, the catch
part)!