deadlock in agent job

I have a job with a single t-sql step. The tsql executes a stored proc that
occasionally deadlocks.
I know i can't trap the deadlock in the stored proc, but I'd like to trap
the deadlock in the agent job, and retry the stored proc.
What is the best way to handle this?
-Rand yThis is normal behavior. A trigger is executed for each statement that cause
s
the trigger to fire and not for each row affected by the statement. See
"Multirow Considerations" in BOL.
AMB
"Randy" wrote:

> I have a job with a single t-sql step. The tsql executes a stored proc th
at
> occasionally deadlocks.
> I know i can't trap the deadlock in the stored proc, but I'd like to trap
> the deadlock in the agent job, and retry the stored proc.
> What is the best way to handle this?
> -Rand y
>
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> This is normal behavior. A trigger is executed for each statement that cau
ses
> the trigger to fire and not for each row affected by the statement. See
> "Multirow Considerations" in BOL.
>
> AMB
> "Randy" wrote:
>|||What about increasing "Retry attempts" in the advanced tab when creating or
modifing the job step.
AMB
"Randy" wrote:

> I have a job with a single t-sql step. The tsql executes a stored proc th
at
> occasionally deadlocks.
> I know i can't trap the deadlock in the stored proc, but I'd like to trap
> the deadlock in the agent job, and retry the stored proc.
> What is the best way to handle this?
> -Rand y
>
>

Dead easy string handling question

Hi there,

I'm a bit embarrassed about this question, because I'm sure that a lot of you would find it trivial, but I'm really not the best at T-SQL and especially not string handling.

I'm trying to generate a 'parent' value, by replacing characters in the 'child' field with ''. It's a classic Chart of Accounts sort of problem, for feeding into a Parent/Child dimension in AS. It's a lot easier to understand if you look at this:

This is the set I have:

GLCode GLDescription
0.-.-.- Balance S
0.0.-.- Balance S.Balance Sheet
0.0.0.- Balance S.Balance Sheet.Balance Sheet
0.0.0.5000 Balance S.Balance Sheet.Balance Sheet.Assets - Area1

0.0.0.5001 Balance S.Balance Sheet.Balance Sheet.Assets - Area2
0.0.0.5002 Balance S.Balance Sheet.Balance Sheet.Assets - Area3

This is the set I want

Ch GLCode Par GLCode GLDescription
0.-.-.- -.-.-.- Balance S
0.0.-.- 0.-.-.- Balance S.Balance Sheet
0.0.0.- 0.0.-.- Balance S.Balance Sheet.Balance Sheet
0.0.0.5000 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area1

0.0.0.5001 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area2
0.0.0.5002 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area3

That is to say,

0.0.0.- is the parent of 0.0.0.5001

0.0.-.- is the parent of 0.0.0.-

0.-.-.- is the parent of 0.0.-.-

So, what I really need to do is replace the last non '-' string with '-'. I've tried various combos of PATINDEX, CHARINDEX, REPLACE etc, but I'm really struggling. To make in more complicated, and of the strings between the . can be any length.

Any ideas?

Maybe something like this would work for you:

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
GLCode varchar(20),
[Description] varchar(100)
)

INSERT INTO @.MyTable VALUES ( '0.-.-.-', 'Balance S' )
INSERT INTO @.MyTable VALUES ( '0.0.-.-', 'Balance S.Balance Sheet' )
INSERT INTO @.MyTable VALUES ( '0.0.0.-', 'Balance S.Balance Sheet.Balance Sheet' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5000', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area1' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5001', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area2' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5002', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area3' )

SELECT
[Ch GLCode] = GLCode,
[Par GLCode] = CASE
WHEN isnumeric( parsename( GLCode, 1 )) = 1 THEN '0.0.0.-'
WHEN parsename( GLCode, 2 ) <> '-' THEN '0.0.-.-'
WHEN parsename( GLCode, 3 ) <> '-' THEN '0.-.-.-'
WHEN parsename( GLCode, 4 ) <> '-' THEN '-.-.-.-'
END,
[Description]
FROM @.MyTable
ORDER BY GLCode

Ch GLCode Par GLCode Description
-- - -

0.-.-.- -.-.-.- Balance S
0.0.-.- 0.-.-.- Balance S.Balance Sheet
0.0.0.- 0.0.-.- Balance S.Balance Sheet.Balance Sheet
0.0.0.5000 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area1
0.0.0.5001 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area2
0.0.0.5002 0.0.0.- Balance S.Balance Sheet.Balance Sheet.Assets - Area3

|||

Thanks for that Arnie,

I did something a bit stupid in my example - I implied that the source dataset is a lot simpler than it acually is. It's a 13,000 row Chart of Accounts, and I just gave you the top 6 rows. If we look further down, and resample the data, we can see members like this:

3.R.W.4501 3.S.-.- 3.S.A.- 3.S.A.0001 3.S.A.0003 3.S.A.0005 3.S.A.0102 3.S.A.0110

In this case, 3.S.A.0001 , 3.S.A.0003 , 3.S.A.0005 , 3.S.A.0102 , 3.S.A.0110 are the children of parent 3.S.A.-

3.S.A.- is the child of parent 3.S.-.-

3.S.-.- is the child of parent 3.-.-.-

(and we can therefore infer that the top member, 3.R.W.4501 is the child of 3.R.W.-

so I don't think your approach of hardcoding the parent into the CASE would work. But your approach was perfectly reasonable, given the lame example you had to work with!

You used ISNUMERIC, which I didn't think of, and PARSENAME, which I haven't used before, so I'll see if I can use those in a more dynamic solution.

As you've probably guessed, I'm a long way from being a T-SQL ninja, so if anyone has any clever ideas on how I should approach this, I'd be interested to know...

|||

The approach I supplied previously seems to work just fine on the additional values you supplied.

(And should as long as the forth part is always a number.)

|||

Maybe I'm missing something, but I don't quite understand how hardcoding '0's into the output is going to help where the code is something like '2.H.A.003'. But you've given me some ideas, so thanks for your help.

SELECT
[Ch GLCode] = GLCode,
[Par GLCode] = CASE
WHEN isnumeric( parsename( GLCode, 1 )) = 1 THEN '0.0.0.-'
WHEN parsename( GLCode, 2 ) <> '-' THEN '0.0.-.-'
WHEN parsename( GLCode, 3 ) <> '-' THEN '0.-.-.-'
WHEN parsename( GLCode, 4 ) <> '-' THEN '-.-.-.-'
END,
[GLDescription]
FROM dbo.WRK_CoA
ORDER BY GLCode

Ch GLCode Par GLCode GLDescription
1.S.U.0380 0.0.0.- Academy of F&H Education.Planning
1.S.U.0600 0.0.0.- Academy of F&H Education.Planning and Develop
2.-.-.- -.-.-.- Adult Academy
2.B.-.- 0.-.-.- Adult Academy.Creative & Cultural Industries
2.B.S.- 0.0.-.- Adult Academy.Creative & Cultural Industri
2.B.S.0130 0.0.0.- Adult Academy.Creative & Cultural Indus
2.H.-.- 0.-.-.- Adult Academy.Employment Services
2.H.A.- 0.0.-.- Adult Academy.Employment Services.Bu
2.H.A.0001 0.0.0.- Adult Academy.Employment Services.Busin
2.H.A.0003 0.0.0.- Adult Academy.Employment Services.Busines
2.H.A.0005 0.0.0.- Adult Academy.Employment Services.Busine
2.H.A.0102 0.0.0.- Adult Academy.Employment Services.Business
2.H.A.0110 0.0.0.- Adult Academy.Employment Services.Business

|||

Sam,

Sorry, I should have gone into more detail. You can use PARSENAME() to deconstruct the values, AND you can use

PARSENAME() to re-construct the values.

Hopefully this will give you the guidance you need.

DECLARE @.MyTable table
( RowID int IDENTITY,
GLCode varchar(20)

)

INSERT INTO @.MyTable VALUES ( '0.-.-.-' )
INSERT INTO @.MyTable VALUES ( '0.0.-.-' )
INSERT INTO @.MyTable VALUES ( '0.0.0.-' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5000' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5001' )
INSERT INTO @.MyTable VALUES ( '0.0.0.5002' )
INSERT INTO @.MyTable VALUES ( '3.R.W.4501' )
INSERT INTO @.MyTable VALUES ( '3.S.-.-' )
INSERT INTO @.MyTable VALUES ( '3.S.A.-' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0001' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0003' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0005' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0102' )
INSERT INTO @.MyTable VALUES ( '3.S.A.0110' )

SELECT
[Ch GLCode] = GLCode,
[Par GLCode] = CASE
WHEN isnumeric( parsename( GLCode, 1 )) = 1
THEN parsename( GLCode, 4 ) + '.' + parsename( GLCode, 3 ) + '.' + parsename( GLCode, 2 ) + '.-'
WHEN parsename( GLCode, 2 ) <> '-'
THEN parsename( GLCode, 4 ) + '.' + parsename( GLCode, 3 ) + '.-.-'
WHEN parsename( GLCode, 3 ) <> '-'
THEN parsename( GLCode, 4 ) + '.-.-.-'
WHEN parsename( GLCode, 4 ) <> '-' THEN '-.-.-.-'
END

FROM @.MyTable
ORDER BY GLCode

Ch GLCode Par GLCode

-- -
0.-.-.- -.-.-.-
0.0.-.- 0.-.-.-
0.0.0.- 0.0.-.-
0.0.0.5000 0.0.0.-
0.0.0.5001 0.0.0.-
0.0.0.5002 0.0.0.-
3.R.W.4501 3.R.W.-
3.S.-.- 3.-.-.-
3.S.A.- 3.S.-.-
3.S.A.0001 3.S.A.-
3.S.A.0003 3.S.A.-
3.S.A.0005 3.S.A.-
3.S.A.0102 3.S.A.-
3.S.A.0110 3.S.A.-

|||

i dunno, here's my attempt:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table

( RowID int IDENTITY,

GLCode varchar(20),

[Description] varchar(100)

)

INSERT INTO @.MyTable VALUES ( '3.-.-.-', 'Balance S' )

INSERT INTO @.MyTable VALUES ( '3.S.-.-', 'Balance S.Balance Sheet' )

INSERT INTO @.MyTable VALUES ( '3.S.A.-', 'Balance S.Balance Sheet.Balance Sheet' )

INSERT INTO @.MyTable VALUES ( '3.S.A.0001', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area1' )

INSERT INTO @.MyTable VALUES ( '3.S.A.5001', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area2' )

INSERT INTO @.MyTable VALUES ( '3.S.A.5002', 'Balance S.Balance Sheet.Balance Sheet.Assets - Area3' )

SELECT

CASE

WHEN CHARINDEX('-', GLCode) = 0 THEN LTRIM(RTRIM(LEFT(GLCode, LEN(GLCode) - CHARINDEX('.', REVERSE(GLCode)))))

WHEN CHARINDEX('-', GLCODE) = 3 THEN '-.-.-.-'

ELSE LTRIM(RTRIM(LEFT(GLCode, CHARINDEX('.-', GLCode))))

END

FROM @.MyTable

edit: the above isn't correct, but perhaps it'll offer some help...|||

Thanks guys - I can see that I'll be able to pick apart what you've done and come up with a solution.

DDL create user in T-SQL stored proc/trigger

Hello SQL Server programming gurus:

I am trying to create a trigger that fires after a user logon and logoff and does the following:

creates a new user
deletes data from old temp table

Then I need to create a stored procedure that executes dynamically to
drop old users
remove the old user account access

We are running SQL Server 2000.
Since I am new to T-SQL programming could anyone help point me in the right direction? Can I write dynamic SQL in a trigger to do these things?I really don't think you want to try that using a trigger. It sounds like a really bad idea to me.

First and foremost, I don't know of any way to launch a trigger on a login or logout event. You could probably approximate this using either SQL Profiler or a "watchdog" process, but the fundamental idea isn't supported by SQL Server itself, so you'll need some kind of "helper" to get the job done.

Next, I'd be really leary of having users created "on the fly" by anyone, much less what you seem to be describing where every login has the ability to create users! I can't imagine a scenario where that idea would appeal to me, unless it was to torment some dba that was going to inherit that code after I'd left. The thought positively gives me the willies!

The temp tables really aren't any problem... SQL Server simply drops temp tables after the user logs off. You really don't need to do any "care and feeding" of them, they are completely expendable.

If you can explain what you are trying to do (in English, not Transact-SQL), I'd be willing to bet that someone here can offer you some ideas that will help a bunch!

-PatP