Best SQL IDE

Anyone have any thoughts on the best IDE for SQL Server development? I'd
like object explorer, drag-and-drop, code formatting, code completion, code
analysis, execution, query builder, the WORKS! I've tried RapidSQL, having
gotten excited by their online demo, but I have not found it well-behaved.
It completely crashed no less than 5 times in as many minutes. D'oh!
TIA!
RobertPersonally, I like Management Studio (SQL Server 2005) with PromptSQL (which
gives you IntelliSense in SSMS, Query Analyzer, etc). I'm pretty religious
about code formatting, and I have no need for a "query builder" (especially
those with bugs or really nasty side effects).
"Best" is going to be pretty subjective, since everyone has their own
opinions, criteria, etc. Like what is the best car, I can't even count how
many variables go into that, and why you get as many answers as there are
people.
A
"Robert Davis" <radbase@.yahoo.com> wrote in message
news:eP5T2VIwFHA.724@.TK2MSFTNGP14.phx.gbl...
> Anyone have any thoughts on the best IDE for SQL Server development? I'd
> like object explorer, drag-and-drop, code formatting, code completion,
> code analysis, execution, query builder, the WORKS! I've tried RapidSQL,
> having gotten excited by their online demo, but I have not found it
> well-behaved. It completely crashed no less than 5 times in as many
> minutes. D'oh!
> TIA!
> Robert
>|||I havn't had time to try this out myself, but there is a 3rd party tool for
enabled T-SQL intellisense style code completion. It claims to work with
Query Analyzer, VS 2003 and 2005 Enterprise Manager.
http://www.promptsql.com/
I don't know about formatting. I have my own style of structured indenting
and comes so natural I don't even think about it when typing.
"Robert Davis" <radbase@.yahoo.com> wrote in message
news:eP5T2VIwFHA.724@.TK2MSFTNGP14.phx.gbl...
> Anyone have any thoughts on the best IDE for SQL Server development? I'd
> like object explorer, drag-and-drop, code formatting, code completion,
> code analysis, execution, query builder, the WORKS! I've tried RapidSQL,
> having gotten excited by their online demo, but I have not found it
> well-behaved. It completely crashed no less than 5 times in as many
> minutes. D'oh!
> TIA!
> Robert
>|||Hi
You may want to look through the Readers choices in the September SQL Server
Magazine
http://www.windowsitpro.com/SQLServ.../786/Index.html
John
"Robert Davis" <radbase@.yahoo.com> wrote in message
news:eP5T2VIwFHA.724@.TK2MSFTNGP14.phx.gbl...
> Anyone have any thoughts on the best IDE for SQL Server development? I'd
> like object explorer, drag-and-drop, code formatting, code completion,
> code analysis, execution, query builder, the WORKS! I've tried RapidSQL,
> having gotten excited by their online demo, but I have not found it
> well-behaved. It completely crashed no less than 5 times in as many
> minutes. D'oh!
> TIA!
> Robert
>|||I've had good success with ApexSQLEdit - has intellsense, execution
plans [not as graphic as SQL Q/A - i.e., in a flowchart view, but not
with icons for ss, scans, etc., but has the text to explain it], and
integration with source control.
www.apexsql.com
They have other good tools as well.
Robert Davis wrote:

>Anyone have any thoughts on the best IDE for SQL Server development? I'd
>like object explorer, drag-and-drop, code formatting, code completion, code
>analysis, execution, query builder, the WORKS! I've tried RapidSQL, having
>gotten excited by their online demo, but I have not found it well-behaved.
>It completely crashed no less than 5 times in as many minutes. D'oh!
>TIA!
>Robert
>
>|||Here is another query builder to keep an eye on:
http://151.100.3.84/technicalpreview/
Pamela

Best Query/Search Method

Hi,

I'm wondering about the following:

I have come across an InfoPath Forms application who's code is scripted in javascript and who's data seems to be in XML files.
An analyst at that company told me they suspect the data is ALSO in SQL Server... somewhere. They can't seem to find it though. I have
reviewed the .js code and some methods are called for which I can find no source. I believe those methods execute OK because they're found
inside some DLL.

I'm thinking I would enter a new record using the form in InfoPath using some datavalue that I can expect will be unique.. like a lastname who's first three chars is ZZZ or something like that. Subsequently, I'd search each column in each table in each DB on the server to see if I can locate it somewhere.

So, my question is what is the best approach for this? I have access to the db, table and column names. I know I can write a small vb.net piece of code to execute my search. But, is there some better way using some sql procedure (or using the full text catalog) instead or any other tool(s)?

Thanks in advance for your advise.

Stewart

Go into sql server management studio and open up a new query window, then set the query window to the the database in question.

Execute this query:

select 'union select ''[' + colu.table_schema + '].[' + colu.table_name + '].[' + colu.column_name + ']'' as "Schema.Table.Column" '
+ ',[' + colu.column_name + '] as "Value" '
+ ' from [' + colu.table_schema + '].[' + colu.table_name + '] '
+ ' where [' + colu.column_name + '] like ''ZZZ%'' '
from INFORMATION_SCHEMA.columns as colu
where colu.data_type in ('varchar','nvarchar','char','nchar','text','ntext')

Below the query will be the results, one sql statement per text-based column.

You can click, then right-click on the top-left button-looking box in the grid header and copy the text into the buffer.

Paste it into a new query window.

Delete the first "union" on the first line and execute the query.

It will return one row per column value per table that matches ZZZ%.

Depending upon the number of tables/columns in the database, and the number of rows in the table, you might need to split the results into multiple, smaller queries.

Enjoy!

|||

Hi David,

Thanks very much for the assistance. It worked perfectly!

Regards,
Stewart

|||

The views in the master database are very powerful. Try the information_schema views first, and switch to the sys... views if the information_schema views don't have what you need.

Glad it helped!

sql

Best Practice/recommendation dev data maint plans

We are working on converting to SQL 2005 database. During the conversion we are having to rewrite a lot of code and doing a lot of intital testing and development on development data. This is causing our transaction logs to really big. I have created a maint plan that runs nightly that does a back up of database and tran log but throughtout the day the tran logs are getting really big and eating up a ton of disk space. Does anyone have suggestions on what sort of maint plan I can setup to run on my developement data where as at this point I am not concened about being able to roll back the database just keep is small as possible and "healthly"

All ideas are appreciated

Thanks

Chris

Hi,

If your database has recovery model set to FULL you can schedule to take a T-Log backup on half/hourly to keep it in shape.

BTW are you doing Re-Indexing / Bulk Insertion-Updation !!!

Hemantgiri S. Goswami

|||

Would you recommend running the hourly t-log backups by creating a maint plan?

I have a maint plan running nightly that is perfoming reindexing, update stats, and shrink the db. We are also converting in data via text files and bulk inserting but when we do this we are shutting off as much of the logging as possible.

Would you happen to know of any articles, white papers or anything like that I could read up on?

Thanks for your time.

Chris

|||

Hi,

As you said that you have a maint plann running nightly which does ReIndexing... Re Indexing actually keep your T-Log file growing, what is a plan you have for shrinking!!! Refer http://hemantgirisgoswami.blogspot.com/2006/03/cause-for-t-log-become-full-and-how-to.html

Regards

Hemantgiri S. Goswami

|||Thanks for the suggestions. The articles are very helpfull.

Best practice advice for efficient SQL connection code

Hi,

I have an application which is similar to the following example

Private Sub Start()
For a as int16 = 1 to 300
lstResults.items.add(GetPriceFromItem(a))
Next
End Sub

Private Function GetPriceFromItem(byval item as int16) as String
'Connect to SQL
'Execute "SELECT Price FROM Table WHERE Item='" & item.tostring & "'"
'Close Database connection
'Return Price
End Function

I want to know if there is a more efficeint way of doing this, i.e. i'm concerned that the routine creates 300 SqlConnection instances, 300 open/closes and 300 queries

Would a better way be to connect to SQL once, get the entire table then do the 300 "lookups" locally somehow, perhaps put it all into a DataTable, but can you query a datatable in this way, or could you suggest another control.

Best Regards

Ben

You might try one SQL statement which returns all of your needed records in one resultset, with a query like this:

SELECT Price, Item FROM Table WHERE Item BETWEEN 1 AND 300

(I suggest changing the data type of your Item column to integer.)|||

If lstResults is a listbox, then I would use tmorton's SELECT statement with a SqlDataSource control to fill the listbox instead of coding it. Unless of course, you want all of the items, then just don't put anything in the WHERE clause at all.

if lstResults is just a list, then use tmorton's SELECT statement with a datareader to fill the list all at once.

|||

Hi,

I just used lstResults to simplyfy my example, in the actual application these queries form part actually a DataTable which is built on the fly.

Most of the columns are populated with values coming from the Ebay API, then for the last column I take the value of column 0 which is ItemID and lookup to a SQL DB (approx 300 records)

Then datatable is bounded to a datagridview

|||

In that case, use the datareader and tmorton's SELECT statement, but you will need to reverse your logic. Read each record from the SQL Database, then find the row in the datatable that it corresponds to (if any).

Or, you can use a datareader, and stuff the result into a collection/dictionary, then iterate through the datatable, and use the itemID to retrieve the value from the collection/dictionary.

|||

Hi, yes thats the idea that I had. But what is a collection/dictionary?

|||

dim x as new collection

x.add("value1","key1")

x.add("value2","key2")

x.add("MyValue","Mykey")

debug.print x("key1") -- prints value1
debug.print x("Mykey") -- prints MyValue
debug.print x("key2") -- prints value2

A collection/dictionary is basically a key/value pair that allows you to store the value into an object and then quickly retrieve the value based on the key. Most implementations use a hashed key AND/OR binary tree structure so that retrieving the value is pretty fast, much faster than say iterating through an array looking for a key. Just be careful when you retrieve values from the collection as the default collection requires a string key. If you ask for a numeric key, then it'll act more like an array and give you back the nth entry in the collection rather than the value of that key.
So...
debug.print x(1) -- will retrieve the first value
debug.print x(cstr(1)) -- will retrieve the value that has a key of "1"

A dictionary is very similiar, as it stores and retrieves keys and values. In .NET dictionaries are a generic form of collection as far as I know, but with a few different methods, so the following will still work:
dim x as new generic.dictionary(Of string,string)
x.add("value","key")
debug.print x("key")

But you can't retrieve things by index like you can in a collection, so the following will NOT work:
debug.print x(1)

I think dictionaries are a bit faster than collections too.

|||

Hi

You could process just one sql statement (much more efficient use of the query engine) by using the IN statement eg.

select * from table

where tablefield IN ("a", "b", "c")

Hope this helps

Chris Seary

Behavior of OPTION

we have migrated code from SQL7 to SQL2K and in some stored procs OPTION
clause used as : OPTION (KEEPFIXED PLAN, FORCE ORDER)
now there is a complaint that these procs are running quite slow. while
trying to figure out the problem for the sluggishness
of the procedure, i noticed that commenting this line produced different
plan while uncommenting this line produced quite a
different one. Also, the usage of indexes varied drastically between these
plans (commented v/s uncommented OPTION clause)
can anybody throw some light on why using or NOT using OPTION would change
the execution plan?
thx in advanceHi
I prefer to remove hints when upgrading significant versions as things like
changes to the Query Optimizer may mean you are not using specific features
and as you have found they may be slower.
It is also a good idea to benchmark the system before and after the upgrade
to make sure that it is performing better!
It also usually a good time to review the code for general "good practices"
such as declaring temporary tables a the start of a procedure, reducing the
use of unnecessary temporary tables, owner prefixes for tables and stored
procedures, correct error and transaction handling etc...
Make sure that indexes and statistics are in place and rebuilt.
You can then look at the code and the query plans for the slower procedures.
John
"paraa" wrote:

> we have migrated code from SQL7 to SQL2K and in some stored procs OPTION
> clause used as : OPTION (KEEPFIXED PLAN, FORCE ORDER)
> now there is a complaint that these procs are running quite slow. while
> trying to figure out the problem for the sluggishness
> of the procedure, i noticed that commenting this line produced different
> plan while uncommenting this line produced quite a
> different one. Also, the usage of indexes varied drastically between these
> plans (commented v/s uncommented OPTION clause)
> can anybody throw some light on why using or NOT using OPTION would change
> the execution plan?
> thx in advance
>|||parasda
Firstly , BOL has a pretty good explanation about all options that you can
use
Actually ,FORCE ORDER option specifies that the join order indicated by the
query syntax is preserved during query optimization. In fact , query
optimizer is free to decide in what order (join) ( believe me, it is smart
enough) to execute the query im more efficient way ,so by using this option
you limit the optimizer to create an efficient execution plan
"paraa" <paraa@.discussions.microsoft.com> wrote in message
news:12F9AE7A-C7F4-4228-88C1-C1C5E35FA47B@.microsoft.com...
> we have migrated code from SQL7 to SQL2K and in some stored procs OPTION
> clause used as : OPTION (KEEPFIXED PLAN, FORCE ORDER)
> now there is a complaint that these procs are running quite slow. while
> trying to figure out the problem for the sluggishness
> of the procedure, i noticed that commenting this line produced different
> plan while uncommenting this line produced quite a
> different one. Also, the usage of indexes varied drastically between these
> plans (commented v/s uncommented OPTION clause)
> can anybody throw some light on why using or NOT using OPTION would change
> the execution plan?
> thx in advance
>

beginning SQL

Hello, I would like to be able to construct a small SQL data base with Visual Basic.Net code for practice. I want to be able to add to the data base, subtract from the data base, perform all Sql procedures ( Select, delete, etc.) I would also like to include the ExecuteNonQuery, ExecuteScalar, and ExecuteReader methods. Can anybody out there recommend a good tutorial for me? Thank you so much. MarcAbelson_2000@.yahoo.com

These resources should be useful for your learning:

http://www.asp.net/learn/

http://www.asp.net/learn/data-access/

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx

http://msdn.microsoft.com/vstudio/express/sql/starterkit/default.aspx

beginner setting up to code in VB

Hey everyone. I'm trying to set up the free microsoft sql 2000 server desktop engine so I can create databases in visual basic using ADO. I would like to do this locally, not over the network or anything. I'm on windows 98. the steps I took are:

1. I downloaded the ms 2000 server desktop engine (msde2000a.exe)
2. Downloaded the .net framework and installed that (version 1.1)
3. Extracted the desktop engine setup to a directory
4. browsed to that directory in DOS prompt and typed 'setup SAPWD="AStrongSAPwd" ... and then it installed
5. opened ODBC Data sources in the control panel
6. in the user DSN tab i highlighted "MQIS" and then pressed 'configure'
7. Pressed 'next' with the default values for all the fields: (name=MQIS, Description=SQL Server' Server=(local) )
8. with the radio button "with sql server authentication.." selected i click on 'client configuration'
9. after i click on that a box comes up saying 'add network library configuration'.

so.. i'm confused on this part.. What should i put for 'server alias' and 'server name'? I want to do this locally. I tried putting my local IP address and the word (local) but after i press 'OK' and then 'Next' (in that previous window that displays the client configuration button) i get an error that says:

Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect())
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied

So.. how do I set up my computer to be able to use the engine locally? I have the vbcode I want to work. It consists of creating a database using the "provider=SQLOLEDB..." line. I can submit the code if anyone wants to see. Any help would be greatly appreciated. Thanks!By the way, i DO NOT have SQL Server installed. I was thinking the engine would sort of be like MS Access where you don't actually need Microsoft Access installed to do database manipulation because the Access Jet Engine is built into windows. So I figured installing the SQL Server engine would do the same thing for me. Is this correct? Or is it mandatory that I have SQL Server installed?|||You will definitely need to have SQL Server installed, it won't ship as part of the OS until LongHorn. You can buy the developer edition for about $50, and you get MSDE (the engine alone, like Jet is to MS-Access) included with any of Microsoft's Professional level developer products.

-PatP

Beginner question Searching table

I have a stored procedure that takes 5 parameters. Based on what parameters are passed i want to return a result set to the calling code. Not all of the parameters are required so there will be some combinations on the entered parameters.

Do i need to dynamic build the query with the IF statements to check if the input parameter is NULL or not ?

Thanks

You can use the isnull function like so:

-- Using the northwind database in sql server 2k

declare @.categoryname varchar(100)
declare @.categoryID varchar(100)

--set @.categoryname = 'Produce'
set @.CategoryID = 4

select * from categories
where isnull(@.categoryname, categoryname) = categoryname
and isnull(@.categoryID, categoryID) = categoryID

|||For additional reading on this subject, check this article for a full description of was to do this: http://www.sommarskog.se/dyn-search.html|||

Hi,

Instead of running the below one

select * from categories
where isnull(@.categoryname, categoryname) = categoryname
and isnull(@.categoryID, categoryID) = categoryID

Running this one may be faster

select * from categories
where (@.categoryname is null or categoryname = @.categoryname)
and (@.categoryID is null or categoryID = @.categoryID)

Since during the execution plan creation the engine may decide a better way since it knows that "@.categoryname is null" is true for every record. But a similar approach is also valid for the first one too. Actually this is related with how wise the sql server engine is.

Eralper

http://www.kodyaz.com

|||

Thanks all for helping. It works great now and it runs fast for me. Thanks so much for you help.

Guess i need to start reading up on my T SQL.

|||

The query optimizer doesn't quite optimize the conditions using the multiple variables like you described. The problem is that the value of only @.categoryname or @.categoryid will be fixed at compile time depending on the first execution and the plan primed into the cache will not be that efficient for the other invocations. Erland's article that Louis posted goes through lot of techniques and their various pros/cons. The recompilation whitepaper below is also a good read on how plan caching works:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Beginner prob: generating string

Help!

I'm new to T-SQL and trying to code a solve for the following problem, without a good idea of how I can make T-SQL jump through hoops. Here is the problem.

I need to populate a single string with values from a series of possible fields.

eg. a vehicle can be 4 wheel drive (4WD) and have air conditioning (Aircon).

So for each stock record I want to test each of a range of values (all or none can be true) in a column for a match. Based on that, add to the output string (that would look like "4wd ac ab abs").

So far I have :

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE proc [dbo].[qry_EXPERIMENT_02-12-2003]

--provides information for the 'notes' field of the results screen.
--default value: NULL
@.notesString nvarchar (30) = NULL

as

SELECT
tblStockExtra.StockID,

(
SELECT
CASE WHEN tblExtra.ExtraHeader = '4WD' THEN '4WD ' END,
CASE WHEN tblExtra.ExtraHeader = 'Aachk' THEN 'aa ' END,
CASE WHEN tblExtra.ExtraHeader = 'Aircon' OR tblExtra.ExtraHeader = 'DualAircon' OR tblExtra.ExtraHeader = 'ClimateAirCon' THEN 'ac ' END,
CASE WHEN tblExtra.ExtraHeader = 'ABS' THEN 'abs ' END,
CASE WHEN tblExtra.ExtraHeader = 'AlloyWheel' OR tblExtra.ExtraHeader = 'FactoryAlloyWheel ' THEN 'aw ' END,
CASE WHEN tblExtra.ExtraHeader = 'SRSAirBag' OR tblExtra.ExtraHeader = 'DualSRSAirBag' THEN 'ab ' END,
CASE WHEN tblExtra.ExtraHeader = 'CD' OR tblExtra.ExtraHeader = 'CDShuttle' THEN 'cd ' END

FROM tblExtra
INNER JOIN tblStockExtra ON tblExtra.ExtraID = tblStockExtra.ExtraID
INNER JOIN tblStock ON tblStockExtra.StockID = tblStock.StockID
) AS notes

FROM tblStockExtra, tblExtra

GROUP BY tblStockExtra.StockID, tblExtra.Extraheader

ORDER BY tblStockExtra.StockID

This doesn't work, as I assume a subquery can only return 1 value. I'm hoping someone knowledgable can see what I'm attempting to do and enlighten me!

Do I need to be using Dynamic SQL?

Many thanks for your thoughts and time,

KC.You are right, this will not work. It would be easier for us if you post :

a) table descriptions of tblStock, tblExtra, tblStockExtra
b) the foreign keys between those tables (that way we can have an idea about the relationships between them)
c) some sample data
d) an example of how you would like to output to be formatted|||Originally posted by cvandemaele
You are right, this will not work. It would be easier for us if you post :

a) table descriptions of tblStock, tblExtra, tblStockExtra
b) the foreign keys between those tables (that way we can have an idea about the relationships between them)
c) some sample data
d) an example of how you would like to output to be formatted

A) Many to many realtionship between tblStock and tblExtra with tblStockExtra as a bridging table
B) Referential integrity not enforced (Don't ask! Its not mine :) )
C)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblExtra]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblExtra]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblStock]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblStock]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblStockExtra]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblStockExtra]
GO

CREATE TABLE [dbo].[tblExtra] (
[ExtraID] [int] NOT NULL ,
[ExtraDesc] [nvarchar] (255) NULL ,
[ExtraAbbrv] [nvarchar] (255) NULL ,
[ExtraHeader] [nvarchar] (50) NULL ,
[Group] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblStock] (
[StockID] [int] NOT NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblStockExtra] (
[StockExtraID] [int] IDENTITY (1, 1) NOT NULL ,
[StockID] [int] NULL ,
[ExtraID] [int] NULL
) ON [PRIMARY]
SET IDENTITY_INSERT [dbo].[tblStockExtra] ON
GO

ALTER TABLE [dbo].[tblStockExtra] WITH NOCHECK ADD
CONSTRAINT [PK_tblStockExtra] PRIMARY KEY NONCLUSTERED
(
[StockExtraID]
) ON [PRIMARY]
GO

INSERT INTO tblStock
(StockID)
VALUES (64);
INSERT
INTO tblStock(StockID)
VALUES (65);
INSERT
INTO tblStock(StockID)
VALUES (467);
INSERT
INTO tblStock(StockID)
VALUES (487);
INSERT
INTO tblStock(StockID)
VALUES (944);

--4wd
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (41,'4 wheel drive', '4WD', '4WD', 14)

--aachk
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (52, 'AA check', 'AA', 'Aachk', 17)

--aircon
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (8, 'air-conditioning', 'AC', 'AirCon', 2)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (9, 'climate air-conditioning', 'AAC', 'ClimateAirCon', 2)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (10, 'dual air condtioning', 'DAC', 'DualAirCon', 2)

--abs
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (39, 'antilock braking system', 'ABS', 'ABS', 12)

--alloys
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (27, 'alloy wheels', 'AW', 'AlloyWheel', 9)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (28, 'factory alloy wheels', 'FAW', 'FactoryAlloyWheel', 9)

--airbags
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (25, 'SRS airbag', 'AB', 'SRSAirBag', 8)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (26, 'dual SRS airbag', 'DAB', 'DualSRSAirBag', 8)

--CD
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (13, 'CD', 'CD', 'CD', 3)
INSERT INTO tblExtra
(ExtraID, ExtraDesc, ExtraAbbrv, ExtraHeader, [Group])
VALUES (14, 'CD shuttle', 'CDS', 'CDShuttle', 3)

INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1112,64,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1113,64,12)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1115,64,23)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1116,64,15)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1117,64,60)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1118,64,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1118,64,59)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1119,65,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1120,65,39)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1121,65,14)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1122,65,23)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1123,65,26)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1124,65,16)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1125,65,15)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1126,65,43)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1127,65,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (1128,65,12)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4357,467,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4358,467,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4359,467,11)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4465,487,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4466,487,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4467,487,11)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (4468,487,67)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9188,944,7)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9189,944,8)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9190,944,12)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9191,944,16)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9192,944,15)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9193,944,20)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9194,944,23)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9195,944,28)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9196,944,64)
INSERT INTO tblStockExtra
(StockExtraID, StockID, ExtraID)
VALUES (9197,944,41)

D) I want the output as: "Extra1 Extra2 Extra3"

Thanks for your help!

KC.|||As the list of extras linked to an item in StockId is unlimited, the list of extras is returned as a large string, seperated by a single character.

First of all, create the following function

create function dbo.fGetOptionList (@.CarId int, @.Separator char(1))
returns nvarchar(510)
as
begin

-- cursor that will browse throught the list of extra for the given car
declare cOptionList
cursor for
select
options.extraabbrv as OptionAbbrv
from tblstock as cars
inner join tblstockextra as cars_options
on cars.stockid = cars_options.stockid
inner join tblextra as options
on options.extraid = cars_options.extraid
where Cars.stockid = @.CarId

declare @.OptionAbbrv nvarchar(510)
declare @.AllOptions nvarchar(510)

open cOptionList
fetch next from cOptionList into @.OptionAbbrv
while @.@.fetch_status = 0
begin
set @.AllOptions = rtrim(isnull(@.AllOptions,'')) + rtrim(isnull(@.OptionAbbrv,'')) + @.Separator
fetch next from cOptionList into @.OptionAbbrv
end
close cOptionList
deallocate cOptionList

return @.AllOptions

end

Afterwards, just :
SELECT
stockid,
dbo.fgetoptionlist(stockid, ';') as OptionList
FROM tblstock

It will return :
stockid OptionList
---- -----
64 AC;
65 AC;ABS;CDS;DAB;
467 AC;
487 AC;
944 AC;FAW;4WD;

(5 row(s) affected)|||Wow!

Thanks for your time and energy! I truly wasn't expecting the solution on a plate! Please accept my sincerest thanks, you've saved me a great deal of effort!

I feel disappointed I can't reward you with a commendation as in other forums, but you've made my day!

:) :) :)

KC.

Beginner @IDENTITY question

Hello,
When I execute the following code on my localhost the INSERT statement is successfull and my ID is entered. But for some reason when I uploaded to my remote host the exact same database (MS SQL) and code it keeps trying to enter a NULL value into the ID column- which causes an error? I don't understand becuase it is the exact same app and database but at a remote location.

Is @.IDENTITY somehow saved in the session and my remote servers session settings?? are off?

strSql = "insert into Login (UserName,Password) VALUES ('" + UserName.Text.Trim() + "','" + Password.Text.Trim() + "') select @.ID = @.@.IDENTITY";

Thanks in advance for any responses.
-WileyHi Wiley,

First, you should have a ; between the statements:

strSql = "insert into Login (UserName,Password) VALUES ('" + UserName.Text.Trim() + "','" + Password.Text.Trim() + "'); select @.ID = @.@.IDENTITY";

Second, you should probably be using SCOPE_IDENTITY instead of @.@.IDENTITY. The two are similar, but SCOPE_IDENTITY returns values inserted only within the current scope. The problem with @.@.IDENTITY is that you might get the ID value from another insert operation. Check out SQL Server Books Online for a more complete description of the issue.

But that doesn't explain your problem. My best guess is that the ID field isn't defined as an identity field in the database on the remote server. That's the first thing to check.

There is a slight chance that SET IDENTITY_INSERT is off in the database. I don't recall that you can set that as the normal setting, but something in the connection might be setting it. Definitely a longshot.

Third,DON'T USE DYNAMIC SQL THIS WAY!!!!! It opens up SQL injection attacks, particularly for fields that are obviously user input. If you don't know the issues, just ask. You are setting yourself up to have your app hacked and attacked.

Don|||Hello,
Part of my problem was that I created the database locally from a .dat file in a database called Resume. Everthing worked great. When I upoaded the tables to my remote assigned database which is called "mydatabase" not Resume I ran into permission problems becuase locally the owner was dbo and remotely the owner was "mydatabase". Also some of the ID fields were not set to IDENTITY.

I basically have my application working(sort of ) now but I have to allow NULLS on every column that allows them in every table for anything to (sort of ) work -which I DID NOT have to do locally and I can't figure out why, maybe someone can shed some light on that for me.

Also, in reference to SQL injection attacks that you mentioned above , maybe you can point me to a link on ms sql security and explain to me what is wrong with using the dynamic sql statement in the way i did. I definitely don't want to start using asp.net and start off on the wrong foot opening myself up for attacks.

thanks,
-Wiley|||there's a lot of info about sql inject that you can find on the web. some of them are:

http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=533341

http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

http://www.nextgenss.com/papers/more_advanced_sql_injection.pdf

google rocks!|||wrong text on the first link. my mistake.

BEGIN......COMMIT TRANSACTION

I found an issue in our code but I am not sure what the effects are. What
happens if you have a named transaction and the being transaction has a
different name as the commit? Example
begin transaction test
CODE HERE
commit transaction test1
I found a case where there is a typo and the name in the being does not
match the name in the commit. The code in the transaction does commit and n
o
errors are returned. Is the transaction still open at this point?The BOL in 'nested transactions' suggests that name of the COMMIT
TRANSACTION is ignored, so I suppose it shouldn't affect your database.
Peter
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:DB52A984-7136-473A-BCF5-F6E0664E08CD@.microsoft.com...
>I found an issue in our code but I am not sure what the effects are. What
> happens if you have a named transaction and the being transaction has a
> different name as the commit? Example
> begin transaction test
>
> CODE HERE
> commit transaction test1
> I found a case where there is a typo and the name in the being does not
> match the name in the commit. The code in the transaction does commit and
> no
> errors are returned. Is the transaction still open at this point?|||Correct, and this is easy to test:
BEGIN TRAN X
SELECT @.@.TRANCOUNT
COMMIT TRAN Y
SELECT @.@.TRANCOUNT
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rogas69" <rogas69@.no_spamers.o2.ie> wrote in message
news:%236AVWMhQGHA.5036@.TK2MSFTNGP12.phx.gbl...
> The BOL in 'nested transactions' suggests that name of the COMMIT TRANSAC
TION is ignored, so I
> suppose it shouldn't affect your database.
> Peter
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:DB52A984-7136-473A-BCF5-F6E0664E08CD@.microsoft.com...
>

BEGIN TRAN increments @@TRANCOUNT to 2

I have a problem with the below code that seems to open 2 transactions(why
not just one) - what am I doing wrong?
Regards,
Janusz
SET IMPLICIT_TRANSACTIONS ON
GO
BEGIN TRAN
COMMIT
PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUNT)Hi,
The first transaction was opened for SET IMPLICIT_TRANSACTIONS ON the next
was opened for Begin Tran.
If you run the following code you will find the same, I am not sure what is
your requirement here.
SET IMPLICIT_TRANSACTIONS ON
GO
BEGIN TRAN
COMMIT
PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUNT)
COMMIT --This is for the the imlicit transaction on
PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUNT)
o/p
After commiting trans. Opened trans::1
After commiting trans. Opened trans::0
Vishal Khajuria
SUNGARD SCT INDIA
"rejki" wrote:

> I have a problem with the below code that seems to open 2 transactions(why
> not just one) - what am I doing wrong?
> Regards,
> Janusz
> SET IMPLICIT_TRANSACTIONS ON
> GO
> BEGIN TRAN
> COMMIT
> PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUN
T)
>|||See SET IMPLICIT_TRANSACTIONS in BOL.
It has a perfect example showing the variation in @.@.TRANCOUNT
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"rejki" <rejki@.discussions.microsoft.com> wrote in message
news:DE9B1D00-8D58-4F10-9614-866EC64BF1E5@.microsoft.com...
>I have a problem with the below code that seems to open 2 transactions(why
> not just one) - what am I doing wrong?
> Regards,
> Janusz
> SET IMPLICIT_TRANSACTIONS ON
> GO
> BEGIN TRAN
> COMMIT
> PRINT 'After commiting trans. Opened trans::' +
> convert(varchar,@.@.TRANCOUNT)
>|||Thanks for reply,
If you add print statement after "SET IMPLICIT_TRANSACTIONS ON" you will see
that it does not open transaction. @.@.TRANCOUNT goes to 2 after "BEGIN TRAN"
statement.
The problem I am having is that I do only one commit, but when I execute my
SQL script (similar to one I posted) SQL Analyzer thinks that I have still
uncommitted transaction and when I try to exit SQL Analyzer it asks me
whether I want to close open transaction.
I could add one more “commit” but I would prefer to understand what is g
oing
on there.
Regards,
Janusz
"Vishal Khajuria" wrote:
> Hi,
> The first transaction was opened for SET IMPLICIT_TRANSACTIONS ON the next
> was opened for Begin Tran.
> If you run the following code you will find the same, I am not sure what i
s
> your requirement here.
> SET IMPLICIT_TRANSACTIONS ON
> GO
> BEGIN TRAN
> COMMIT
> PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUN
T)
> COMMIT --This is for the the imlicit transaction on
> PRINT 'After commiting trans. Opened trans::' + convert(varchar,@.@.TRANCOUN
T)
> o/p
> After commiting trans. Opened trans::1
> After commiting trans. Opened trans::0
>
> Vishal Khajuria
> SUNGARD SCT INDIA
>
> "rejki" wrote:
>|||Hi,
What happens with IMPLICIT_TRANSACTIONS is this:
If there's already a transaction in progress (say at a higher scope in a cal
ling stored procedure or some such), nothing else happens; you simply join t
hat transaction.
If, however, there's no current transaction in progress, then executing any
DML/DDL statement will start a new transaction. In this case, all you need t
o do is COMMIT or ROLLBACK. A better approach even than this is to use XACT_
ABORT, which will ensure that any failures below 21 rollback the current tra
nsaction scope. That way, you can raise a suitable error instead of just rol
ling back (or worse, writing extra code to rollback and raise an error!).
The following demonstrates...
SET IMPLICIT_TRANSACTIONS ON
SET XACT_ABORT ON
-- now do some work.
SELECT|||Sorry, here's the complete demonstration
SET IMPLICIT_TRANSACTIONS ON
SET XACT_ABORT ON
-- now do some work.
UPDATE table1
SET field1 = NULL
WHERE field2 = 'some value'
IF EXISTS (
SELECT a.field1, b.field6
FROM tbl2 a
INNER JOIN tbl2 a
WHERE a.field9 > 0
)
BEGIN
RAISERROR (N'Failed to update fact table.', 16, 1)
END
-- if we got this far then we're good!
COMMIT TRANSACTION
GO|||One other thing, by way of explanation about the following
bit of SQL:

IF EXISTS (
SELECT a.field1, b.field6
FROM tbl2 a
INNER JOIN tbl2 a
WHERE a.field9 > 0
)

This is testing some condition to see if the operation was
successful. You may not care about the outcome, in which case you
can just COMMIT, but given that you're in a transaction in the
first place, I'd guess you're going to want to test some sort
of condition to see if this really worked out, before deciding
whether or not to commit your changes.
Cheers,
Tim

Begin Date and End Date

Hi all
I want my users to be able to pick between the beginning date and the end date, for Example with the code I have below they can enter in a final suit date and then they get the results through query or report or whatever, what I want for them to be able to do is enter a beginning date and an end date Between [Enter the Start Date] And [Enter the End Date]
thats how access interprets it (Jet SQL) how would I do that in SQL Server?? Help Please

SELECT TM#, LASTNAME, FIRSTNAME, CONDITIONAL, DATEOFCONDITIONAL, FINALSUITDONE
FROM dbo.EmployeeGamingLicense
WHERE (FINALSUITDONE = @.Enter_FINALSUITDONE)I would have my stored procedure accept those dates as parameters. Presuming Access is still your front end, get the values from the user via a form, then pass them to the SP.|||Yes thats the front end, but can you elaborate because I'm am not getting anywhere at all..Oh GOOD GRAVY THE FRUSTRATION LEVELS IS CLIMBING..LOL|||What exactly are you trying to accomplish? How are you calling/executing the SP?|||I am trying to get it so that users can enter the beginning of the date like 12/1/2004 to 12/31/2004

I have a parameter in an ADP that allows them to enter the FinalSuit date 12/31/2005 and they see all the final suit for that date but I was wanting to give them a range, does that make sense|||To be honest, I don't use ADP's so I'm probably not your best source of info. I don't see the advantages of them, and I know of 3 Access MVP's that recommend against them.|||But when you work for a company that refuses to shell out money for a better front end then you use what you got|||Oh, Access is fine; I use it all the time. I simply stick with MDB/MDE front ends, not ADP. They seem to handle form references differently, so I figure someone with more ADP experience will be better able to help.|||Thanks for trying Its just frustrating cause Jet SQL and Sql server can be like apples and oranges|||SELECT [Main Table].[IR Number], [Main Table].Date, [Main Table].Inspector, [Main Table].Area, [Main Table].Violation, [Main Table].[Violation Type], [Main Table].Loss, [Main Table].[Loss Type], [Main Table].Employee, [Main Table].Action, [Main Table].[Action Type], [Main Table].Notes
FROM [Main Table]
WHERE ((([Main Table].Date) Between [Enter the Start Date] And [Enter the End Date]))
ORDER BY [Main Table].[IR Number]
GO

Begin and Commit Transaction statements in a trigger

I am trying to tidy up a bunch of triggers on a legacy database. The
DB is SQL 2000.
One trigger has the code to begin and commit a named transaction
within it.
Am I right in thinking this is a bad idea?
My understanding is that any commit statement will committ all
transactions regardless of the named transaction, so will this committ
everything done up to that point?
Also I am not sure what will happen if the entire transaction needs to
rollback, bearing in mind the trigger code will have already
committed?
Thanks
Paddy
Paddy,
COMMIT TRAN only backs up one level, so a BEGIN/COMMIT in a trigger should
be fine. A nested commit is not truly and fully committed until the highest
level commits.
It is ROLLBACK that bumps all transactions out and which needs careful
handling.
RLF
"Paddy" <paddymullaney@.btopenworld.com> wrote in message
news:400b44b1-c6f2-4193-b940-d4d4752ad897@.p69g2000hsa.googlegroups.com...
>I am trying to tidy up a bunch of triggers on a legacy database. The
> DB is SQL 2000.
> One trigger has the code to begin and commit a named transaction
> within it.
> Am I right in thinking this is a bad idea?
> My understanding is that any commit statement will committ all
> transactions regardless of the named transaction, so will this committ
> everything done up to that point?
> Also I am not sure what will happen if the entire transaction needs to
> rollback, bearing in mind the trigger code will have already
> committed?
> Thanks
> Paddy
>
>
>
>
|||From locking point of view all the nesting does not do anything. All the
locks belong to the transaction that was open by your connection if there was
one. There is only one transaction no matter how many begin commits are
nested.
So this trigger may control the transactions and can make some sense if and
only if whatever has triggered it was in autocommit. Then your transaction
will be
1) the dml that has caused the trigger to fire
2) whatever is inside the trigger
then it works like so
any rollback in the trigger will rollback 1) and 2) no matter where the
rollback is (in case if there are more then 1 trigger firing, have to watch
how many and how deep do they cascade). Commit in this case will happen when
top level trigger finishes the job and 1) and 2) will be committed. Anything
that has a commit in the cascading triggers etc will not release the locks
according to isolation levels (in this case default one, so yout X locks will
hang out, selects will be released as soon as the next row is
pulled/released, locks escalations will be released when the locked object is
not needed anymore for the query processing).
Thanks, Liliya
|||rollback rolls back no matter if there was a begin tran and the commit.
because there is only one transaction.
Is it working more like a flat transaction with checkpoints of some sort?
not really. Because if there is a chackpoint it matches the beginning of the
very top level transaction... It is ms sql's point of viiew what nested
transactions are and what more important what was possible to implement in
the current engine.
One of the things developers, esp. ones that came from oracle like doing is
trying to implement in a trigger or in an sp an oracle style sequence (sort
of like an identity but they want it to cover several tables, a global
sequence). what bites then is that where they crate a sequence is the table
or/and one row. so they read it, modify id+1 and then go about thier business
and you hope it is only one sequence in their logic and if many they update
those tables in the same order always. If you are not so lucky and your
developers are creative you may find yourself de bugging peculiar kids of
locking and deadlocks and trying to explin to the developed why exactly
commit does not work like any commit supposed to work in your stupid database
. I did see such a then one time with more then one client.
if you wounder what happens from transaction point of view if it is not a
trigger but an sp... sane thing pretty much.
here you can see all the locks (will ppublish a sample). in the sp's
provided there is a dbcc call that tells you isolation levels.
|||It does not hurt to check
@.@.trancount in the trigger so you know what kind of transaction context do
you have anyways. if it is 0 you have autocommit and transaction logic in the
trigger may make sense. if @.@.trancount>0, then there is already a transaction
and this trigger becomes a part of it and all the locks it puts in place
belong to the top level transaction and will be released if and only if that
top level transaction commits. rollback will affect the entire transaction,
because there is only one.
Commit in case of the nesting does not even do locks reassigning, because
they always belong to the same top level transaction.
So in case described above 1) becomes your top level transaction where ever
it has started and 2) is as is - everything that the trigger has inside and
all the cascading actions
there will be 3) too. at leases commit/close connection in case if whatever
has triggered your trigger was the last sql in the top level transaction.
Othervise 3) is the rest of your toplevel transaction and whatever it
triggeres. The locks produced by your trigger in this case will be hanging (X
ones) much longer then you could have expected - till the end of top level
transaction.
you have 2 big things to watch out for: anything that can be long
transaction. and all the cascades in your triggers in the context of the
transaction the trigger was called from.
If you have no idea what are you dealing with, you still can find out
(unless the triggers do rollback, then all your audit goes good buy).
Create yourself an audit table (id int, trn int, msg varchar(128)) or
whatever you like better
in the triggers put insert in there @.@.trancount and the name of the trigger
and the of the table. at leas you will know this way what kind of
transactions do you have and what does cascade from where. can add some more
info there or maybe if's in case if your triggers do different thing
depending in inserted/updated/deleted values etc.
|||your tables:
CREATE TABLE [dbo].[seq](
[descr] [varchar](128)
[nextval] [int] NULL DEFAULT ((0))
)
CREATE TABLE [dbo].[trnaudit](
[trnout] [int] NULL,
[trnin] [int] NULL,
[descr] [varchar](128)
)
CREATE TRIGGER [dbo].[SeqINSERT]
ON [dbo].[seq]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
begin tran trgseq
declare @.trnin int
select @.trnin=@.@.trancount
insert into trnaudit values(-1, @.trnin, 'transaction test called trigger
SeqINSERT')
commit tran trgseq
END
here is a test for you that has an example and the answer to your question
about the rollback and commit in the trigger
select * from dbo.seq
select * from trnaudit
begin tran
insert into seq values ('transactionrollback', 1)
select * from dbo.seq
select * from trnaudit
rollback
select * from dbo.seq
select * from trnaudit
here is what it returns into text
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------
descr
nextval
------ --
seq1
0
transactionrollback
1
trnout trnin descr
-- --
------
-1 2 transaction test called trigger SeqINSERT
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------
|||alter proc
_trn_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
if @.trn=0
SET IMPLICIT_TRANSACTIONS ON
else
begin tran
set transaction isolation level read uncommitted
--audit
exec @.val= _nval_test
--insert into trnaudit values(@.trn, @.trnin, '_trn_test updates the sequence')
commit
select 'sp_lock in sp exec @.val= _nval_test call and commit'
select * from master.dbo.syslockinfo
exec sp_lock
-- the first sql statement starts the transaction if begin tran was not
issued earlier
begin tran
select @.trnin=@.@.trancount
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp begin next tran,
audit')
select 'get in in sp DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
select @.trnin=@.@.trancount
SET IMPLICIT_TRANSACTIONS OFF
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp after SET
IMPLICIT_TRANSACTIONS OFF')
select 'sp_lock in sp at the end'
select * from master.dbo.syslockinfo
exec sp_lock
return @.val+1
go
alter proc
_nval_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
select @.val=nextval from seq where descr='seq1'
update seq set nextval=@.val+1 where descr='seq1'
--audit
insert into trnaudit values(@.trn, @.trn, '_trn_test in sp _nval_test')
select 'sp_lock in sp after seq update'
exec sp_lock
select * from master.dbo.syslockinfo
return @.val+1
go
|||test and the results:
TEST1
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _nval_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
exec sp_lock
select * from master.dbo.syslockinfo
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exec
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit
--select * from master.dbo.syslockinfo
RESULT:
id name
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF
|||RESULT:
id name
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
|||rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF

Begin and Commit Transaction statements in a trigger

I am trying to tidy up a bunch of triggers on a legacy database. The
DB is SQL 2000.
One trigger has the code to begin and commit a named transaction
within it.
Am I right in thinking this is a bad idea?
My understanding is that any commit statement will committ all
transactions regardless of the named transaction, so will this committ
everything done up to that point?
Also I am not sure what will happen if the entire transaction needs to
rollback, bearing in mind the trigger code will have already
committed?
Thanks
PaddyPaddy,
COMMIT TRAN only backs up one level, so a BEGIN/COMMIT in a trigger should
be fine. A nested commit is not truly and fully committed until the highest
level commits.
It is ROLLBACK that bumps all transactions out and which needs careful
handling.
RLF
"Paddy" <paddymullaney@.btopenworld.com> wrote in message
news:400b44b1-c6f2-4193-b940-d4d4752ad897@.p69g2000hsa.googlegroups.com...
>I am trying to tidy up a bunch of triggers on a legacy database. The
> DB is SQL 2000.
> One trigger has the code to begin and commit a named transaction
> within it.
> Am I right in thinking this is a bad idea?
> My understanding is that any commit statement will committ all
> transactions regardless of the named transaction, so will this committ
> everything done up to that point?
> Also I am not sure what will happen if the entire transaction needs to
> rollback, bearing in mind the trigger code will have already
> committed?
> Thanks
> Paddy
>
>
>
>|||rollback rolls back no matter if there was a begin tran and the commit.
because there is only one transaction.
Is it working more like a flat transaction with checkpoints of some sort?
not really. Because if there is a chackpoint it matches the beginning of the
very top level transaction... It is ms sql's point of viiew what nested
transactions are and what more important what was possible to implement in
the current engine.
One of the things developers, esp. ones that came from oracle like doing is
trying to implement in a trigger or in an sp an oracle style sequence (sort
of like an identity but they want it to cover several tables, a global
sequence). what bites then is that where they crate a sequence is the table
or/and one row. so they read it, modify id+1 and then go about thier business
and you hope it is only one sequence in their logic and if many they update
those tables in the same order always. If you are not so lucky and your
developers are creative you may find yourself de bugging peculiar kids of
locking and deadlocks and trying to explin to the developed why exactly
commit does not work like any commit supposed to work in your stupid database
:). I did see such a then one time with more then one client.
if you wounder what happens from transaction point of view if it is not a
trigger but an sp... sane thing pretty much.
here you can see all the locks (will ppublish a sample). in the sp's
provided there is a dbcc call that tells you isolation levels.|||From locking point of view all the nesting does not do anything. All the
locks belong to the transaction that was open by your connection if there was
one. There is only one transaction no matter how many begin commits are
nested.
So this trigger may control the transactions and can make some sense if and
only if whatever has triggered it was in autocommit. Then your transaction
will be
1) the dml that has caused the trigger to fire
2) whatever is inside the trigger
then it works like so
any rollback in the trigger will rollback 1) and 2) no matter where the
rollback is (in case if there are more then 1 trigger firing, have to watch
how many and how deep do they cascade). Commit in this case will happen when
top level trigger finishes the job and 1) and 2) will be committed. Anything
that has a commit in the cascading triggers etc will not release the locks
according to isolation levels (in this case default one, so yout X locks will
hang out, selects will be released as soon as the next row is
pulled/released, locks escalations will be released when the locked object is
not needed anymore for the query processing).
--
Thanks, Liliya|||It does not hurt to check
@.@.trancount in the trigger so you know what kind of transaction context do
you have anyways. if it is 0 you have autocommit and transaction logic in the
trigger may make sense. if @.@.trancount>0, then there is already a transaction
and this trigger becomes a part of it and all the locks it puts in place
belong to the top level transaction and will be released if and only if that
top level transaction commits. rollback will affect the entire transaction,
because there is only one.
Commit in case of the nesting does not even do locks reassigning, because
they always belong to the same top level transaction.
So in case described above 1) becomes your top level transaction where ever
it has started and 2) is as is - everything that the trigger has inside and
all the cascading actions
there will be 3) too. at leases commit/close connection in case if whatever
has triggered your trigger was the last sql in the top level transaction.
Othervise 3) is the rest of your toplevel transaction and whatever it
triggeres. The locks produced by your trigger in this case will be hanging (X
ones) much longer then you could have expected - till the end of top level
transaction.
you have 2 big things to watch out for: anything that can be long
transaction. and all the cascades in your triggers in the context of the
transaction the trigger was called from.
If you have no idea what are you dealing with, you still can find out
(unless the triggers do rollback, then all your audit goes good buy).
Create yourself an audit table (id int, trn int, msg varchar(128)) or
whatever you like better
in the triggers put insert in there @.@.trancount and the name of the trigger
and the of the table. at leas you will know this way what kind of
transactions do you have and what does cascade from where. can add some more
info there or maybe if's in case if your triggers do different thing
depending in inserted/updated/deleted values etc.|||your tables:
CREATE TABLE [dbo].[seq](
[descr] [varchar](128)
[nextval] [int] NULL DEFAULT ((0))
)
CREATE TABLE [dbo].[trnaudit](
[trnout] [int] NULL,
[trnin] [int] NULL,
[descr] [varchar](128)
)
CREATE TRIGGER [dbo].[SeqINSERT]
ON [dbo].[seq]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
begin tran trgseq
declare @.trnin int
select @.trnin=@.@.trancount
insert into trnaudit values(-1, @.trnin, 'transaction test called trigger
SeqINSERT')
commit tran trgseq
END
here is a test for you that has an example and the answer to your question
about the rollback and commit in the trigger
select * from dbo.seq
select * from trnaudit
begin tran
insert into seq values ('transactionrollback', 1)
select * from dbo.seq
select * from trnaudit
rollback
select * from dbo.seq
select * from trnaudit
here is what it returns into text
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------
descr
nextval
------ --
seq1
0
transactionrollback
1
trnout trnin descr
-- --
------
-1 2 transaction test called trigger SeqINSERT
descr
nextval
------ --
seq1
0
trnout trnin descr
-- --
------|||alter proc
_trn_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
if @.trn=0
SET IMPLICIT_TRANSACTIONS ON
else
begin tran
set transaction isolation level read uncommitted
--audit
exec @.val= _nval_test
--insert into trnaudit values(@.trn, @.trnin, '_trn_test updates the sequence')
commit
select 'sp_lock in sp exec @.val= _nval_test call and commit'
select * from master.dbo.syslockinfo
exec sp_lock
-- the first sql statement starts the transaction if begin tran was not
issued earlier
begin tran
select @.trnin=@.@.trancount
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp begin next tran,
audit')
select 'get in in sp DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
select @.trnin=@.@.trancount
SET IMPLICIT_TRANSACTIONS OFF
insert into trnaudit values(@.trn, @.trnin, '_trn_test in sp after SET
IMPLICIT_TRANSACTIONS OFF')
select 'sp_lock in sp at the end'
select * from master.dbo.syslockinfo
exec sp_lock
return @.val+1
go
alter proc
_nval_test
as
declare @.trnin int, @.trn int, @.val int
select @.trn=@.@.trancount
select @.val=nextval from seq where descr='seq1'
update seq set nextval=@.val+1 where descr='seq1'
--audit
insert into trnaudit values(@.trn, @.trn, '_trn_test in sp _nval_test')
select 'sp_lock in sp after seq update'
exec sp_lock
select * from master.dbo.syslockinfo
return @.val+1
go|||test and the results:
TEST1
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _nval_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
exec sp_lock
select * from master.dbo.syslockinfo
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exec
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit
--select * from master.dbo.syslockinfo
RESULT:
id name
--
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
--
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF|||test and the results:
TEST1
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _nval_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
exec sp_lock
select * from master.dbo.syslockinfo
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exec
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit
--select * from master.dbo.syslockinfo|||rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114074:2 0x000905009ABD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114074 0x000605009ABD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 53 0
1 593838
00000000-0000-0000-0000-000000000000
NEXTVAL
--
4
sp_lock in sp exit and commit
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
1 1 _trn_test in sp _nval_test
0 NULL _trn_test begin script,after exec sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF|||RESULT:
id name
--
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
53 1 85575343 0 TAB IS GRANT
53 5 126623494 0 RID 1:114074:2 X GRANT
53 5 142623551 0 PAG 1:114070 IX GRANT
53 5 0 0 PAG 1:114074 IX GRANT
53 5 142623551 0 RID 1:114070:0 X GRANT
53 5 142623551 0 TAB IX GRANT
53 5 126623494 0 TAB IX GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT|||TEST2
DECLARE @.err INT, @.trn INT, @.trnin int, @.nextval int
set nocount on
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
delete from trnaudit
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,
autocommit')
select id, name from sysobjects where name in ('seq', 'trnaudit') or id
=85575343
begin tran
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, begin
tran')
set transaction isolation level read committed
select 'get in the script DBCC USEROPTIONS'
DBCC USEROPTIONS
commit
begin tran
exec @.nextval= _trn_test
select @.nextval as NEXTVAL
commit
select 'sp_lock in sp exit and commit'
select * from master.dbo.syslockinfo
exec sp_lock
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script,after exe
sp')
SET IMPLICIT_TRANSACTIONS OFF
select @.trn=@.@.trancount, @.trnin=NULL
insert into trnaudit values(@.trn, @.trnin, '_trn_test begin script, SET
IMPLICIT_TRANSACTIONS OFF')
select * from trnaudit|||id name
--
------
126623494 trnaudit
142623551 seq
85575343 dt_getpropertiesbyid_vcs
get in the script DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read committed
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
--
sp_lock in sp after seq update
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
57 1 85575343 0 TAB Sch-S GRANT
57 5 126623494 0 RID 1:114072:2 X GRANT
57 5 142623551 0 PAG 1:114070 IX GRANT
57 5 0 0 PAG 1:114072 IX GRANT
57 5 142623551 0 RID 1:114070:0 X GRANT
57 5 142623551 0 TAB IX GRANT
57 5 126623494 0 TAB IX GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114072:2 0x0009050098BD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072 0x0006050098BD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000|||----
sp_lock in sp exec @.val= _nval_test call and commit
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114072:2 0x0009050098BD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072 0x0006050098BD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
57 1 85575343 0 TAB Sch-S GRANT
57 5 126623494 0 RID 1:114072:2 X GRANT
57 5 142623551 0 PAG 1:114070 IX GRANT
57 5 0 0 PAG 1:114072 IX GRANT
57 5 142623551 0 RID 1:114070:0 X GRANT
57 5 142623551 0 TAB IX GRANT
57 5 126623494 0 TAB IX GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
get in in sp DBCC USEROPTIONS
Set Option
Value
------
---
textsize
64512
language
us_english
dateformat
mdy
datefirst
7
quoted_identifier
SET
arithabort
SET
nocount
SET
ansi_null_dflt_on
SET
ansi_defaults
SET
ansi_warnings
SET
ansi_padding
SET
ansi_nulls
SET
concat_null_yields_null
SET
isolation level
read uncommitted
(14 row(s) affected)
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.|||--
sp_lock in sp at the end
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
1:114072:3 0x0009050098BD01000100030000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072:2 0x0009050098BD01000100020000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070 0x0006050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 6 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072:4 0x0009050098BD01000100040000000000
0x00000000000000000000000000000000 5 0 126623494 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114072 0x0006050098BD01000100000000000000
0x00000000000000000000000000000000 5 0 0 6 0
8 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
1:114070:0 0x0009050096BD01000100000000000000
0x00000000000000000000000000000000 5 0 142623551 9 0
5 1 0 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x000505003F4380080000000000000000
0x00000000000000000000000000000000 5 0 142623551 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
0x00050500061F8C070000000000000000
0x00000000000000000000000000000000 5 0 126623494 5 0
8 1 1 0 33554432 57 0
1 593956
00000000-0000-0000-0000-000000000000
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 5 0 0 DB S GRANT
57 5 126623494 0 RID 1:114072:3 X GRANT
57 1 85575343 0 TAB Sch-S GRANT
57 5 126623494 0 RID 1:114072:2 X GRANT
57 5 142623551 0 PAG 1:114070 IX GRANT
57 5 126623494 0 RID 1:114072:4 X GRANT
57 5 0 0 PAG 1:114072 IX GRANT
57 5 142623551 0 RID 1:114070:0 X GRANT
57 5 142623551 0 TAB IX GRANT
57 5 126623494 0 TAB IX GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
NEXTVAL
--
6|||--
sp_lock in sp exit and commit
rsc_text rsc_bin
rsc_valblk rsc_dbid rsc_indid rsc_objid rsc_type
rsc_flag req_mode req_status req_refcnt req_cryrefcnt req_lifetime req_spid
req_ecid req_ownertype req_transactionID req_transactionUOW
-- --
-- -- -- -- --
-- -- -- -- -- --
-- -- -- --
--
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 52 0
3 0
00000000-0000-0000-0000-000000000000
0x00020400000000000000000000000000
0x00000000000000000000000000000000 4 0 0 2 0
3 1 1 0 0 51 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 55 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 60 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 58 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 56 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 53 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 57 0
3 0
00000000-0000-0000-0000-000000000000
0x00020500000000000000000000000000
0x00000000000000000000000000000000 5 0 0 2 0
3 1 1 0 0 54 0
3 0
00000000-0000-0000-0000-000000000000
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
51 4 0 0 DB S GRANT
52 4 0 0 DB S GRANT
53 5 0 0 DB S GRANT
54 5 0 0 DB S GRANT
55 5 0 0 DB S GRANT
56 5 0 0 DB S GRANT
57 1 85575343 0 TAB IS GRANT
57 5 0 0 DB S GRANT
58 5 0 0 DB S GRANT
60 5 0 0 DB S GRANT
trnout trnin descr
-- --
------
0 NULL _trn_test begin script, autocommit
1 NULL _trn_test begin script, begin tran
2 2 _trn_test in sp _nval_test
1 2 _trn_test in sp begin next tran, audit
1 1 _trn_test in sp after SET IMPLICIT_TRANSACTIONS OFF
0 NULL _trn_test begin script,after exe sp
0 NULL _trn_test begin script, SET IMPLICIT_TRANSACTIONS OFF
Thanks, Liliya