I am coding a large VB project, using Win2k, SQL Server 2000 SP1,
COM+, and VB6SP5. We have been having some problems with transactions
not being rolled back when an error was raised, and we have traced it
to the following situation. I'm looking for strategies of error
handling for this situation.
Sometimes (most times?) when there is a deadlock, Err.Number (HRESULT)
is 80004005 (-2147467259), but SOMETIMES IT IS 0 (that is, no error).
So because my code was not told about the error, it kept on
processing, but the problem is, THE DTC TRANSACTION HAS BEEN ABORTED,
so all further updates are not in a transaction. Usually DTC wakes up
sooner or later, but by then it might be too late - I may have made
updates thinking I was in a transaction when I was not. These updates
cannot be rolled back, and my database is left in an inconsistent
state. (FYI, the error I get from DTC are 8509 in the profiler,
reflected as 3704 in VB, if it is detected during processing. If it
is not detected until the root object is exiting, I see 8004e002.)
In particular, so far we have seen only ONE section of code where it
sometimes returns 0. All other areas of the code so far have returned
80004005. Also, in the place that returns 0, it only SOMETIMES
returns 0. Other times, it returns 80004005. There does not seem to
be any rhyme or reason to when it returns 0 and when it returns
80004005 (not that I've discovered yet, though). In all cases, the
error information in the profiler trace either:
Error: 1205, Severity: 13, State: 8
or
Error: 1205, Severity: 13, State: 50
There doesn't seem to be any correlation between the state=8 or the
state=50 errors.
All the error handling samples in MSDN use the "on error goto" trick,
but obviously that doesn't work if Err.Number = 0. Is it safe to just
check cn.Errors? Would there ever be any "informational" or "warning"
errors in there that I really should just leave alone and not report
an error? Is Deadlock the only one that could return 0 that really
should be aborted?
Thanks for any strategies you might know about!
Christine Wolak -- Senior Programmer
www.axiom-corp.comkaligrrl@.yahoo.com (Christine) wrote in message news:<d6363ccb.0309120837.1fc9d3e2@.posting.google.com>...
> I am coding a large VB project, using Win2k, SQL Server 2000 SP1,
> COM+, and VB6SP5. We have been having some problems with transactions
> not being rolled back when an error was raised
Okay, we have the answer. SET NOCOUNT ON in your stored procedure.
Always. In any stored procedure that you will be calling from a
client. Here is a link to a KB article:
http://support.microsoft.com/?kbid=240882 . How it can be "by design"
that an error gets raised and is not reported back to the caller, I
don't know, but apparently Microsoft does not plan to address this
issue any time soon. This article also says "a SQL Server trace may
reveal excessive attentions and rollbacks". We saw NONE of that.
NOTHING excessive - only EXPECTED stuff.
In our particular case, we had a deadlock earlier in the process,
during a stored procedure call. But since we were not told about it
(via Err.Number), our code kept processing. We did not have really
"unexpected" problems, because the stored procedure was a "fastpath",
and if for any reason the fastpath cannot be taken, then the code
takes the long path. And the stored procedure return code of 0 just
happens to indicate that the long path was necessary. So the code
just merrily went along, continuing to process.
Which for some reason DTC/COM+/MTS/whatever, let it keep doing. But
it's no longer in a transaction, and any updates at this point are
COMMITTED. At some point later, DTC would wake up and say "hey,
you're not supposed to still be doing stuff!" At this point, we might
see 3704, or 8004d00a (-2147168246), or, if it got all the way to the
very end and is exiting the root object, 8004e002 (-2147164158).
We had several stored procedures that work similarly, and only one had
the problem, and even then not every single time. The difference
would be the amount of informational messages passed back to the
client (DONE_IN_PROC messages).
So just take it from me - always use SET NOCOUNT ON in all stored
procedures called from VB/C++ except for the specific parts of data
that you want returned to the caller, and realize that, if you don't,
you could at any point stop receiving errors from SQL Server.
Christine Wolak -- www.axiom-corp.com
Showing posts with label transactions. Show all posts
Showing posts with label transactions. Show all posts
Thursday, March 29, 2012
Deadlocks problem when database files growing
We are running a very busy SQL Server 2000 Enterprise in the cluster "passive active" environment. Hundreds of transactions are going through and all of them are
logged in to a special database we've created. For an each real transaction we are getting around 10 records inserted in to this database.
I found that whenever the database grows its files, especially the log file, we're getting a lot of deadlocks which we are able to resolve only by failing over to another node.
Any suggestions would be appreciated.
Thanks,
DanHowdy
Is this a recent problem of a long term issue?
Sounds like an application design issue....I doubt he growing logfiles would cause the problem - they would just be a symptom of how busy the system is. Also, if the logs grow really quickly, its possible the app is holding open tables etc too long and then causing the deadlocks. Shorter tansactuions may help. I have used locking hint TABLOCKX to get around a lot of problems, but it MAY NOT be the best solution for you. Sounds very application specific.........
I assume you have plenty of disk space for the TEMPDB and the database files?
More info / background would be useful.
Cheers,
SG
logged in to a special database we've created. For an each real transaction we are getting around 10 records inserted in to this database.
I found that whenever the database grows its files, especially the log file, we're getting a lot of deadlocks which we are able to resolve only by failing over to another node.
Any suggestions would be appreciated.
Thanks,
DanHowdy
Is this a recent problem of a long term issue?
Sounds like an application design issue....I doubt he growing logfiles would cause the problem - they would just be a symptom of how busy the system is. Also, if the logs grow really quickly, its possible the app is holding open tables etc too long and then causing the deadlocks. Shorter tansactuions may help. I have used locking hint TABLOCKX to get around a lot of problems, but it MAY NOT be the best solution for you. Sounds very application specific.........
I assume you have plenty of disk space for the TEMPDB and the database files?
More info / background would be useful.
Cheers,
SG
Tuesday, March 27, 2012
DEADLOCKS
Hi:
I have a .net web application and I am getting a lot of deadlocks. My
system processes a lot of real-time transactions daily. In addition ,
there are like 3 DTS packages running. The problem is that I am getting a
lot of deadlock and based on the history of the pakages I would say that is
happening because the packages take the control and kill the processes that
are tying to run at the same time.
Is there a way to try to reduce this dead-lock assigning especific features
to the packages.
ThanksI don't think there are any issues specific to DTS packages that cause
deadlocking. A package could be excuting almost anything, so provide more
details about what type of selects / updates / inserts the package is
performing and also the nature of the transactions that are being blocked.
Read up in Books Online about "set transaction isolation level". Consider
using a lower isolation level, like read uncommitted. Basically, this allows
a process to read data currently locked by another transaction. Just
understand what "dirty reads", "nonrepeatable reads", etc. are and whether
or not they would present a significant problem in the specific case of your
queries.
Also, for what it's worth, here are my bookmarks for SQL Server deadlocking:
Deadlocking
http://msdn.microsoft.com/library/d... />
a_8i93.asp
INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/defaul...kb;en-us;169960
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
Minimizing Deadlocks
http://msdn.microsoft.com/library/d... />
a_3hdf.asp
SQL Server technical bulletin - How to resolve a deadlock
http://support.microsoft.com/defaul...kb;en-us;832524
"Gina Hernandez" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:%23OfWoX19FHA.4004@.TK2MSFTNGP14.phx.gbl...
> Hi:
> I have a .net web application and I am getting a lot of deadlocks. My
> system processes a lot of real-time transactions daily. In addition ,
> there are like 3 DTS packages running. The problem is that I am getting
> a lot of deadlock and based on the history of the pakages I would say that
> is happening because the packages take the control and kill the processes
> that are tying to run at the same time.
> Is there a way to try to reduce this dead-lock assigning especific
> features to the packages.
> Thanks
>
I have a .net web application and I am getting a lot of deadlocks. My
system processes a lot of real-time transactions daily. In addition ,
there are like 3 DTS packages running. The problem is that I am getting a
lot of deadlock and based on the history of the pakages I would say that is
happening because the packages take the control and kill the processes that
are tying to run at the same time.
Is there a way to try to reduce this dead-lock assigning especific features
to the packages.
ThanksI don't think there are any issues specific to DTS packages that cause
deadlocking. A package could be excuting almost anything, so provide more
details about what type of selects / updates / inserts the package is
performing and also the nature of the transactions that are being blocked.
Read up in Books Online about "set transaction isolation level". Consider
using a lower isolation level, like read uncommitted. Basically, this allows
a process to read data currently locked by another transaction. Just
understand what "dirty reads", "nonrepeatable reads", etc. are and whether
or not they would present a significant problem in the specific case of your
queries.
Also, for what it's worth, here are my bookmarks for SQL Server deadlocking:
Deadlocking
http://msdn.microsoft.com/library/d... />
a_8i93.asp
INF: Analyzing and Avoiding Deadlocks in SQL Server
http://support.microsoft.com/defaul...kb;en-us;169960
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
Minimizing Deadlocks
http://msdn.microsoft.com/library/d... />
a_3hdf.asp
SQL Server technical bulletin - How to resolve a deadlock
http://support.microsoft.com/defaul...kb;en-us;832524
"Gina Hernandez" <pdwhitt@.nospam.wdsinc.com> wrote in message
news:%23OfWoX19FHA.4004@.TK2MSFTNGP14.phx.gbl...
> Hi:
> I have a .net web application and I am getting a lot of deadlocks. My
> system processes a lot of real-time transactions daily. In addition ,
> there are like 3 DTS packages running. The problem is that I am getting
> a lot of deadlock and based on the history of the pakages I would say that
> is happening because the packages take the control and kill the processes
> that are tying to run at the same time.
> Is there a way to try to reduce this dead-lock assigning especific
> features to the packages.
> Thanks
>
Monday, March 19, 2012
deadlock due to transactions within a single SPID(syslockinfo table)
I got a deadlock scenario and so I took a snap shot of the syslockinfo table. I found out an interesting scenario where the deadlock is because of two transactions within the same spid.
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
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000
For the first one the lock was granted(transactionID:96462284)
but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock.
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
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000
For the first one the lock was granted(transactionID:96462284)
but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock.
I just wanted some clarifications.
1) what is the significance of req_transactionID column in syslockinfo table?
2) what is the relationship between req_transactionId column and spid column?
3) In case of nested transactions what will be the values of these 2 columns and what will be the relationship between them in that scenario?
4) In this case the deadlock is occurring while executing a SP(the nested level of calls go till 3rd level).
5) Will there be contention for locks between transactions within a single spid.
it is something like this:
sp_cache
sp_cache1
while
sp_cache2
end while
the deadlock occurred when executing sp_cache 2.
At that time there were only two transactionId values in the syslockinfo table for this spid,They were:
1) 96462284
2)96594607
If some body could please help me it would be really helpful for me.
Thanks in advance!!
have you got a deadlock trace set on your server, or a deadlock graph from profiler (if on 2005)?
either would give alot more diagnostic info
|||
Hi, Yes this the exact situation i am facing here as well.
Point to note here is the database is TEMPDB. (i.e. rsc_dbid = 2).
To see the below text properly, please copy from here and paste in notepad then it would be easier to understand.
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
1:25339 0x00088243378C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 149 0 1 87573395 00000000-0000-0000-0000-000000000000
1:25339 0x00088243378C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 58219975 149 0 1 87685718 00000000-0000-0000-0000-000000000000
As suggested by rb1n I have enabled the traces and the profiler logs as well but nothing much helpful exists there.
Basically it really requires understanding about the field "req_transactionID " in syslockinfo table. Not much information is available about this filed on Microsoft websites.
Is it right to say:
- In a normal situation one SPID will have same "req_transactionID " in syslockinfo table?
Reason for asking this is: On normal days when my SP executes (lest say under SPID X) without any problem the syslockinfo table has same "req_transactionID " for all transaction under SPID X . i.e. "req_transactionID " never changes. Days, when deadlock happens the "req_transactionID " is different and have same kind of situation as posted above.
- If not agree with the above understanding then can someone tell me in what situation the "req_transactionID " can be different for same SPID? Or simply what is the significance of column "req_transactionID ”?
- Is this something Microsoft is aware of?
Thanks very much for you time on this.|||did you get a deadlock graph logged in sql profiler? (in the TextData)|||The deadlock occurred in sql server 2000.
I have attached the error log below:
Wait-for graph
Lock
Node:1
PAG: 2:4:103776 CleanCnt:2 Mode: X Flags: 0x0
Grant List 1::
Owner:0x4b713220 Mode: X Flg:0x0 Ref:1 Life:00000000 SPID:112 ECID:0
SPID: 112 ECID: 0 Statement Type: CREATE INDEX Line #: 1
Input Buf: RPC Event: gsa_proc_homepage_cache_refresh;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:112 ECID:0 Ec
deadlock due to transactions within a single SPID(syslockinfo table)
I got a deadlock scenario and so I took a snap shot of the syslockinfo table. I found out an interesting scenario where the deadlock is because of two transactions within the same spid.
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
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000
For the first one the lock was granted(transactionID:96462284)
but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock.
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
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 113 0 1 96462284 00000000-0000-0000-0000-000000000000
1:31840 0x00060200607C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 67108864 113 0 1 96594607 00000000-0000-0000-0000-000000000000
For the first one the lock was granted(transactionID:96462284)
but for the next one(transactionId:96594607) it was put in wait state and SQL server detected it as a deadlock.
I just wanted some clarifications.
1) what is the significance of req_transactionID column in syslockinfo table?
2) what is the relationship between req_transactionId column and spid column?
3) In case of nested transactions what will be the values of these 2 columns and what will be the relationship between them in that scenario?
4) In this case the deadlock is occurring while executing a SP(the nested level of calls go till 3rd level).
5) Will there be contention for locks between transactions within a single spid.
it is something like this:
sp_cache
sp_cache1
while
sp_cache2
end while
the deadlock occurred when executing sp_cache 2.
At that time there were only two transactionId values in the syslockinfo table for this spid,They were:
1) 96462284
2)96594607
If some body could please help me it would be really helpful for me.
Thanks in advance!!
have you got a deadlock trace set on your server, or a deadlock graph from profiler (if on 2005)?
either would give alot more diagnostic info
|||
Hi, Yes this the exact situation i am facing here as well.
Point to note here is the database is TEMPDB. (i.e. rsc_dbid = 2).
To see the below text properly, please copy from here and paste in notepad then it would be easier to understand.
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
1:25339 0x00088243378C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 1 1 0 0 149 0 1 87573395 00000000-0000-0000-0000-000000000000
1:25339 0x00088243378C00000100000000000000 0x00000000000000000000000000000000 2 0 0 6 0 5 3 1 0 58219975 149 0 1 87685718 00000000-0000-0000-0000-000000000000
As suggested by rb1n I have enabled the traces and the profiler logs as well but nothing much helpful exists there.
Basically it really requires understanding about the field "req_transactionID " in syslockinfo table. Not much information is available about this filed on Microsoft websites.
Is it right to say:
- In a normal situation one SPID will have same "req_transactionID " in syslockinfo table?
Reason for asking this is: On normal days when my SP executes (lest say under SPID X) without any problem the syslockinfo table has same "req_transactionID " for all transaction under SPID X . i.e. "req_transactionID " never changes. Days, when deadlock happens the "req_transactionID " is different and have same kind of situation as posted above.
- If not agree with the above understanding then can someone tell me in what situation the "req_transactionID " can be different for same SPID? Or simply what is the significance of column "req_transactionID ”?
- Is this something Microsoft is aware of?
Thanks very much for you time on this.|||did you get a deadlock graph logged in sql profiler? (in the TextData)|||The deadlock occurred in sql server 2000.
I have attached the error log below:
Wait-for graph
Lock
Node:1
PAG: 2:4:103776 CleanCnt:2 Mode: X Flags: 0x0
Grant List 1::
Owner:0x4b713220 Mode: X Flg:0x0 Ref:1 Life:00000000 SPID:112 ECID:0
SPID: 112 ECID: 0 Statement Type: CREATE INDEX Line #: 1
Input Buf: RPC Event: gsa_proc_homepage_cache_refresh;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:112 ECID:0 Ec
Thursday, March 8, 2012
Dead Lock
Hello !!!
I have 2 transactions, the first one has MANY updates to the table A and it finishes with a commit or rollback (ONLY AT THE END), the second one has only one insert into the table A that finishes with a commit or rollback, the problem is that the update process takes a long time to finish, and the insert process could be thrown during the first process, there's where I get everything locked cause the table A is locked and my java aplication gets stuck.
Note: When I execute each transaction independient I have no problems.
Is there any possibility to lock table A completly for the first transaction and release It for second one ??
Could you give me any suggestion of what to do step by step ?
Thanks !!!Are you running these processes by submitting SQL commands from your Java App, or are you just executing existing Stored Procedures?|||begin tran
select 1 from tableA (tablock) where 1=2
update...
if @.@.error != 0 begin
...
rollback tran
return (1)
end
...
commit tran
I have 2 transactions, the first one has MANY updates to the table A and it finishes with a commit or rollback (ONLY AT THE END), the second one has only one insert into the table A that finishes with a commit or rollback, the problem is that the update process takes a long time to finish, and the insert process could be thrown during the first process, there's where I get everything locked cause the table A is locked and my java aplication gets stuck.
Note: When I execute each transaction independient I have no problems.
Is there any possibility to lock table A completly for the first transaction and release It for second one ??
Could you give me any suggestion of what to do step by step ?
Thanks !!!Are you running these processes by submitting SQL commands from your Java App, or are you just executing existing Stored Procedures?|||begin tran
select 1 from tableA (tablock) where 1=2
update...
if @.@.error != 0 begin
...
rollback tran
return (1)
end
...
commit tran
Wednesday, March 7, 2012
DDL in Transactions
Hello,
I know that it is possible to put DDL statements (i.e.
CREATE TABLE, DROP TABLE etc...) in transactions but I
have found a peculiarity that I am trying to get around.
I issued the following:
BEGIN TRANSACTION
CREATE VIEW TempView AS select * from tempTable
COMMIT TRANSACTION
It gave the following error message:
Server: Msg111, level 15, State 1, Line 2
'CREATE VIEW' must be the first statement in a query batch
Can anyone find a way around this using the simple T-SQL
code above?
Thanks in advance
Jamie
P.S. Why is there no microsoft.public.sqlserver.tsql
newsgroup?Hello Jamie !
Sorry but this aint the way it goes. DDL Statements such as
alter,create,drop fires an Implicit commit to send the changes directly to
the database.
HTH, Jens Süßmeyer.|||Jens,
Thats what I always thought too. But if I try this:
BEGIN TRANSACTION
create table temptable (col1 int)
ROLLBACK TRANSACTION
the rollback works (i.e. the table isn't created). Try it! There is even a server level setting that indicates whether you can allow DDL in transactions or not (see sp_server_info, number 110).
So, I can have DDL in a transaction but not CREATE VIEW it seems. Why not?
Regards
Jamie
>--Original Message--
>Hello Jamie !
>Sorry but this aint the way it goes. DDL Statements such as
>alter,create,drop fires an Implicit commit to send the changes directly to
>the database.
>HTH, Jens S=FC=DFmeyer.
>
>.
>|||Hi Jens,
That is not true, DDL does not do an implicit commit and can be included in
a multi statement transaction. The only issue there is, is the error Jamie
got: CREATE VIEW/PROCEDURE and a few others have to be the first statement
in a batch. There is an easy way around that, as a transaction can span
multiple batches:
BEGIN TRAN
GO
CREATE VIEW....
COMMIT TRAN
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jens Süßmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
news:OEoreq$ZDHA.3768@.tk2msftngp13.phx.gbl...
> Hello Jamie !
> Sorry but this aint the way it goes. DDL Statements such as
> alter,create,drop fires an Implicit commit to send the changes directly to
> the database.
> HTH, Jens Süßmeyer.
>|||DDL does not issue an implicit commit in SQL Server, although this may
be the case with some other RDBMS vendors.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Jens Süßmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
news:OEoreq$ZDHA.3768@.tk2msftngp13.phx.gbl...
> Hello Jamie !
> Sorry but this aint the way it goes. DDL Statements such as
> alter,create,drop fires an Implicit commit to send the changes
directly to
> the database.
> HTH, Jens Süßmeyer.
>|||DDL for textual objects (views, procedures, etc.) must be in a separate
batch so that SQL Server can determine where the CREATE statement ends.
Multiple batches may be executed in a single transaction. Try:
BEGIN TRANSACTION
GO
CREATE VIEW TempView AS select * from tempTable
GO
COMMIT TRANSACTION
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Jamie Thomson" <jamie.thomson@.int21.com> wrote in message
news:008301c367f9$f337e4b0$a301280a@.phx.gbl...
> Hello,
> I know that it is possible to put DDL statements (i.e.
> CREATE TABLE, DROP TABLE etc...) in transactions but I
> have found a peculiarity that I am trying to get around.
> I issued the following:
> BEGIN TRANSACTION
> CREATE VIEW TempView AS select * from tempTable
> COMMIT TRANSACTION
> It gave the following error message:
> Server: Msg111, level 15, State 1, Line 2
> 'CREATE VIEW' must be the first statement in a query batch
> Can anyone find a way around this using the simple T-SQL
> code above?
> Thanks in advance
> Jamie
>
> P.S. Why is there no microsoft.public.sqlserver.tsql
> newsgroup?|||Thanks Gents,
Dan's suggestion works perfectly.
i.e. :
BEGIN TRANSACTION
GO
CREATE VIEW TempView AS select * from tempTable
GO
COMMIT TRANSACTION
GO
Thanks for the advice.
Regards
Jamie
>--Original Message--
>DDL does not issue an implicit commit in SQL Server, although this may
>be the case with some other RDBMS vendors.
>-- >Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=3D800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"Jens S=FC=DFmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
>news:OEoreq$ZDHA.3768@.tk2msftngp13.phx.gbl...
>> Hello Jamie !
>> Sorry but this aint the way it goes. DDL Statements such as
>> alter,create,drop fires an Implicit commit to send the changes
>directly to
>> the database.
>> HTH, Jens S=FC=DFmeyer.
>>
>
>.
>|||> BEGIN TRANSACTION
> GO
> CREATE VIEW TempView AS select * from tempTable
> GO
> COMMIT TRANSACTION
> GO
As an aside, you couldn't do this inside the definition of a stored
procedure; you'd have to use dynamic SQL, I suppose. But that doesn't seem
to be an issue for the OP.
I know that it is possible to put DDL statements (i.e.
CREATE TABLE, DROP TABLE etc...) in transactions but I
have found a peculiarity that I am trying to get around.
I issued the following:
BEGIN TRANSACTION
CREATE VIEW TempView AS select * from tempTable
COMMIT TRANSACTION
It gave the following error message:
Server: Msg111, level 15, State 1, Line 2
'CREATE VIEW' must be the first statement in a query batch
Can anyone find a way around this using the simple T-SQL
code above?
Thanks in advance
Jamie
P.S. Why is there no microsoft.public.sqlserver.tsql
newsgroup?Hello Jamie !
Sorry but this aint the way it goes. DDL Statements such as
alter,create,drop fires an Implicit commit to send the changes directly to
the database.
HTH, Jens Süßmeyer.|||Jens,
Thats what I always thought too. But if I try this:
BEGIN TRANSACTION
create table temptable (col1 int)
ROLLBACK TRANSACTION
the rollback works (i.e. the table isn't created). Try it! There is even a server level setting that indicates whether you can allow DDL in transactions or not (see sp_server_info, number 110).
So, I can have DDL in a transaction but not CREATE VIEW it seems. Why not?
Regards
Jamie
>--Original Message--
>Hello Jamie !
>Sorry but this aint the way it goes. DDL Statements such as
>alter,create,drop fires an Implicit commit to send the changes directly to
>the database.
>HTH, Jens S=FC=DFmeyer.
>
>.
>|||Hi Jens,
That is not true, DDL does not do an implicit commit and can be included in
a multi statement transaction. The only issue there is, is the error Jamie
got: CREATE VIEW/PROCEDURE and a few others have to be the first statement
in a batch. There is an easy way around that, as a transaction can span
multiple batches:
BEGIN TRAN
GO
CREATE VIEW....
COMMIT TRAN
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jens Süßmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
news:OEoreq$ZDHA.3768@.tk2msftngp13.phx.gbl...
> Hello Jamie !
> Sorry but this aint the way it goes. DDL Statements such as
> alter,create,drop fires an Implicit commit to send the changes directly to
> the database.
> HTH, Jens Süßmeyer.
>|||DDL does not issue an implicit commit in SQL Server, although this may
be the case with some other RDBMS vendors.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Jens Süßmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
news:OEoreq$ZDHA.3768@.tk2msftngp13.phx.gbl...
> Hello Jamie !
> Sorry but this aint the way it goes. DDL Statements such as
> alter,create,drop fires an Implicit commit to send the changes
directly to
> the database.
> HTH, Jens Süßmeyer.
>|||DDL for textual objects (views, procedures, etc.) must be in a separate
batch so that SQL Server can determine where the CREATE statement ends.
Multiple batches may be executed in a single transaction. Try:
BEGIN TRANSACTION
GO
CREATE VIEW TempView AS select * from tempTable
GO
COMMIT TRANSACTION
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Jamie Thomson" <jamie.thomson@.int21.com> wrote in message
news:008301c367f9$f337e4b0$a301280a@.phx.gbl...
> Hello,
> I know that it is possible to put DDL statements (i.e.
> CREATE TABLE, DROP TABLE etc...) in transactions but I
> have found a peculiarity that I am trying to get around.
> I issued the following:
> BEGIN TRANSACTION
> CREATE VIEW TempView AS select * from tempTable
> COMMIT TRANSACTION
> It gave the following error message:
> Server: Msg111, level 15, State 1, Line 2
> 'CREATE VIEW' must be the first statement in a query batch
> Can anyone find a way around this using the simple T-SQL
> code above?
> Thanks in advance
> Jamie
>
> P.S. Why is there no microsoft.public.sqlserver.tsql
> newsgroup?|||Thanks Gents,
Dan's suggestion works perfectly.
i.e. :
BEGIN TRANSACTION
GO
CREATE VIEW TempView AS select * from tempTable
GO
COMMIT TRANSACTION
GO
Thanks for the advice.
Regards
Jamie
>--Original Message--
>DDL does not issue an implicit commit in SQL Server, although this may
>be the case with some other RDBMS vendors.
>-- >Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=3D800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"Jens S=FC=DFmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
>news:OEoreq$ZDHA.3768@.tk2msftngp13.phx.gbl...
>> Hello Jamie !
>> Sorry but this aint the way it goes. DDL Statements such as
>> alter,create,drop fires an Implicit commit to send the changes
>directly to
>> the database.
>> HTH, Jens S=FC=DFmeyer.
>>
>
>.
>|||> BEGIN TRANSACTION
> GO
> CREATE VIEW TempView AS select * from tempTable
> GO
> COMMIT TRANSACTION
> GO
As an aside, you couldn't do this inside the definition of a stored
procedure; you'd have to use dynamic SQL, I suppose. But that doesn't seem
to be an issue for the OP.
Subscribe to:
Posts (Atom)