problem with sql

I've been having problems with the following query. It falls over on the first BEGIN but I can't se why.

Any sugguestions?

Set transaction isolation level read uncommitted

Declare @.Date DateTime
Declare @.Msg VarChar(255)
set @.Date = Getdate()-44

If (Select z.lastamendedby from dbo.intray as z
Where z.lastamendedby in
(select x.userid from dbo.useractivity as x left join dbo.users as y on x.userid = y.user_id
where x.lastactivityTime < @.Date))
--and y.ntSuspended = 0)
--and z.lastamended > @.Date
Begin

set @.Msg = "These users have done work since the 44 day period, don't suspend"
Print @.Msg
End

Else

Begin
Select z.lastamendedby from dbo.intray as z
Where lastamendedby in
(select x.userid from dbo.useractivity as x left join dbo.users as y on x.userid = y.user_id
where x.lastactivityTime < @.Date
and y.ntSuspended = 0)
Set @.Msg = "These users haven't done any work since the 44 day period, suspend"
Print @.Msg

End

Cheers,

Jim

Because you didn't finish your IF.

If you reduce it down, maybe you'll see:

IF (SELECT lastamendedby)
BEGIN

Just out of curiosity, what company do you work for? Cause I'm definately interested in finding one that only suspends you if you haven't done anything for 44 days ;-p

Problem with SET TRANSACTION ISOLATION LEVEL

I found that SET TRANSACTION ISOLATION LEVEL only sets the isolation level for the current CONNECTION.

1. What if I want to change the isolation level of the whole database? Which command is it ?

2. What if I want to use IsolationLevel of Serializable only with a transaction ( a part within a stored procedure)?

for ex. this is from a stored procedure

/* Blah Blah Blah Blah the T-SQL before the transaction */

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

/* Blah Blah Blah Blah the T-SQL after the transaction */

Thanks every body!!!

Pi

Hi Pi.

Answers:

1. No way to change the default isolation level for an entire database (more accurately, all connections to a database). With Sql 2005 you have the option of specifying that the usual default isolation level (read committed) use row-versioning instead of a lock-based isolation by enabling the READ_COMMITTED_SNAPSHOT database option, but that is all you can do there.

2. To achieve what you are trying to do, simply run the appropriate SET TRANSACTION ISOLATION LEVEL statements before and after you begin/end you transaction, as follows:

/* Blah Blah Blah Blah the T-SQL before the transaction */

-- ADD THIS HERE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

-- AND THIS HERE

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

/* Blah Blah Blah Blah the T-SQL after the transaction */

Hope that helps,

|||

Is there a way to set the TRANSACTION ISOLATION LEVEL SNAPSHOT for view. So all SQL for the View would run under SNAPSHOT isolation mode.

|||

In SQL Server, the transaction isolation is not tied to a specific transaction, it is a session property. So if you want do a query under a given isolation level, then simply set the session transaction isolation level to that one before you run your query. In your case, if you want the view to be accessed using SNAPSHOT isolation level, then simply set it before accessing the view, and change the isolation back to your default isolation level after the access to the viwe if you want.

There is no way currently in SQL Server to tie up a isolation level to a given object, like table, queue or a database either.

Thanks!

Problem with SET TRANSACTION ISOLATION LEVEL

I found that SET TRANSACTION ISOLATION LEVEL only sets the isolation level for the current CONNECTION.

1. What if I want to change the isolation level of the whole database? Which command is it ?

2. What if I want to use IsolationLevel of Serializable only with a transaction ( a part within a stored procedure)?

for ex. this is from a stored procedure

/* Blah Blah Blah Blah the T-SQL before the transaction */

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

/* Blah Blah Blah Blah the T-SQL after the transaction */

Thanks every body!!!

Pi

Hi Pi.

Answers:

1. No way to change the default isolation level for an entire database (more accurately, all connections to a database). With Sql 2005 you have the option of specifying that the usual default isolation level (read committed) use row-versioning instead of a lock-based isolation by enabling the READ_COMMITTED_SNAPSHOT database option, but that is all you can do there.

2. To achieve what you are trying to do, simply run the appropriate SET TRANSACTION ISOLATION LEVEL statements before and after you begin/end you transaction, as follows:

/* Blah Blah Blah Blah the T-SQL before the transaction */

-- ADD THIS HERE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

-- AND THIS HERE

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

/* Blah Blah Blah Blah the T-SQL after the transaction */

Hope that helps,

|||

Is there a way to set the TRANSACTION ISOLATION LEVEL SNAPSHOT for view. So all SQL for the View would run under SNAPSHOT isolation mode.

|||

In SQL Server, the transaction isolation is not tied to a specific transaction, it is a session property. So if you want do a query under a given isolation level, then simply set the session transaction isolation level to that one before you run your query. In your case, if you want the view to be accessed using SNAPSHOT isolation level, then simply set it before accessing the view, and change the isolation back to your default isolation level after the access to the viwe if you want.

There is no way currently in SQL Server to tie up a isolation level to a given object, like table, queue or a database either.

Thanks!

sql

Problem with SET TRANSACTION ISOLATION LEVEL

I found that SET TRANSACTION ISOLATION LEVEL only sets the isolation level for the current CONNECTION.

1. What if I want to change the isolation level of the whole database? Which command is it ?

2. What if I want to use IsolationLevel of Serializable only with a transaction ( a part within a stored procedure)?

for ex. this is from a stored procedure

/* Blah Blah Blah Blah the T-SQL before the transaction */

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

/* Blah Blah Blah Blah the T-SQL after the transaction */

Thanks every body!!!

Pi

Hi Pi.

Answers:

1. No way to change the default isolation level for an entire database (more accurately, all connections to a database). With Sql 2005 you have the option of specifying that the usual default isolation level (read committed) use row-versioning instead of a lock-based isolation by enabling the READ_COMMITTED_SNAPSHOT database option, but that is all you can do there.

2. To achieve what you are trying to do, simply run the appropriate SET TRANSACTION ISOLATION LEVEL statements before and after you begin/end you transaction, as follows:

/* Blah Blah Blah Blah the T-SQL before the transaction */

-- ADD THIS HERE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

-- AND THIS HERE

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

/* Blah Blah Blah Blah the T-SQL after the transaction */

Hope that helps,

|||

Is there a way to set the TRANSACTION ISOLATION LEVEL SNAPSHOT for view. So all SQL for the View would run under SNAPSHOT isolation mode.

|||

In SQL Server, the transaction isolation is not tied to a specific transaction, it is a session property. So if you want do a query under a given isolation level, then simply set the session transaction isolation level to that one before you run your query. In your case, if you want the view to be accessed using SNAPSHOT isolation level, then simply set it before accessing the view, and change the isolation back to your default isolation level after the access to the viwe if you want.

There is no way currently in SQL Server to tie up a isolation level to a given object, like table, queue or a database either.

Thanks!