Session Settings

Question

Session Settings

Ron McCullough

SSC Guru

Points: 63877
More actions
January 11, 2010 at 11:49 pm

#219137

Comments posted to this topic are about the item Session Settings
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]
January 11, 2010 at 11:50 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Thanks for another question bitbucket. Had to use the noggin on this one a bit.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SRatnaparkhi SSC-Addicted Points: 474 More actions · Answer 2

Thanks bitbucket for a nice question i learnt lot from this....

nigel. SSChampion Points: 11636 More actions · Answer 3

Slight problem, I think the question is incomplete:

1. In the text the table is referred to 'QOD_Customer' (singular), but in the SQL it is 'QOD_Customers' (plural)

2. There is no mention of the CompanyName column in the question.

Nigel
How to post questions

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 4

The table name has been corrected. A complete DDL is not given. You should assume other fields mentioned do exist, but are not listed for brevity.

Cliff Jones SSChampion Points: 10547 More actions · Answer 5

Cliff Jones

SSChampion

Points: 10547

January 12, 2010 at 7:48 am

#1102430

Nice question, took some thought. Thanks.

Tom Garth SSCertifiable Points: 6193 More actions · Answer 6

Learnt sump'n. Thanks, BitBucket!

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Ernie Schlangen SSCrazy Points: 2382 More actions · Answer 7

Can someone verify for me that this is different in SQL 2008 vs SQL 2005? I get a different result using 2005 (I don't have access to a 2008 server).

Thanks!

Cliff Jones SSChampion Points: 10547 More actions · Answer 8

Ernie Schlangen (1/12/2010)
Can someone verify for me that this is different in SQL 2008 vs SQL 2005? I get a different result using 2005 (I don't have access to a 2008 server).
Thanks!

It appears that you should get the same result on SQL 2005 as SQL 2008.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 9

Ernie Schlangen
Can someone verify for me that this is different in SQL 2008 vs SQL 2005? I get a different result using 2005 (I don't have access to a 2008 server).

Just ran in SQL 2005 Developer Edition - same results

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ernie Schlangen SSCrazy Points: 2382 More actions · Answer 10

Thanks, I must have skipped something. At work now, will try again later. Thanks again!

john.arnott SSChampion Points: 11882 More actions · Answer 11

Cliff Jones (1/12/2010)
Ernie Schlangen (1/12/2010)
Can someone verify for me that this is different in SQL 2008 vs SQL 2005? I get a different result using 2005 (I don't have access to a 2008 server).
Thanks!
It appears that you should get the same result on SQL 2005 as SQL 2008.

I'm with Ernie on this. The rollback worked on 2005.

DDL and data inserted:

--drop table [QOD_Customers]

create table [dbo].[QOD_Customers](

ID int Identity

,[CompanyName] nChar(10)

,[Region] nChar(3) Null

)

insert into [QOD_Customers] (CompanyName,Region) values('ABC','YYY')

insert into [QOD_Customers] (CompanyName,Region) values('XYX', null)

Create stored procedure:

--drop procedure [QOD_Test_1]

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[QOD_Test_1]

AS

SET ANSI_DEFAULTS ON

-- Before rollback Select Statement

SELECT COUNT(CompanyName) AS 'Before rollback'

FROM [dbo].[QOD_Customers]

WHERE [dbo].[QOD_Customers].[Region] IS NULL

UPDATE Dbo.QOD_Customers

SET Region = 'XXX'

WHERE dbo.QOD_Customers.region IS NULL

-- The after update Select Statement

SELECT COUNT(CompanyName) AS 'After update'

FROM [dbo].[QOD_Customers]

WHERE [dbo].[QOD_Customers].[Region] IS NULL

ROLLBACK TRANSACTION

SET ANSI_DEFAULTS OFF

-- The after rollback Select Statement

SELECT COUNT(CompanyName) AS 'After Rollback'

FROM [dbo].[QOD_Customers]

WHERE [dbo].[QOD_Customers].[Region] IS NULL

GO

Run it:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_DEFAULTS ON

go

[dbo].[QOD_Test_1]

go

Results:

Before rollback

---------------

5

(1 row(s) affected)

(5 row(s) affected)

After update

------------

0

(1 row(s) affected)

After Rollback

--------------

5

(1 row(s) affected)

Kevin Gill SSCrazy Points: 2348 More actions · Answer 12

When ANSI_DEFAULTS is enabled (ON), this option enables the following ISO settings:...SET IMPLICIT_TRANSACTIONS. Msg 3903 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

The explanation doesn't quite make sense - I presume there are some words missing somewhere? Or is the quoting of the error message just assumed to be for information purposes? The explanation makes it look like it's one of the ISO settings 🙂

-------------------------------Oh no!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 13

Kevin Gill

From

SQL Server 2008 Books Online (November 2009)

SET ANSI_DEFAULTS (Transact-SQL)

AT:

http://msdn.microsoft.com/en-us/library/ms188340.aspx

When enabled (ON), this option enables the

In my explanation all I did was add the words "SET IMPLICIT_TRANSACTIONS" to the above quoted phrase.

the quoting of the error message just assumed to be for information purposes?

Yes it is.

Hope this clarifies it for you.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]