Why The Try/Catch Block Cannot Capture This Kind Of Deadlock?
Feb 26, 2008
Hi everybody:
I wanted to handle with the deadlock issue by using Try/Catch, the the try/Catch block cannot capture this kind of deadlock:
code in connection1:
declare @i int
set @i = 0
WHILE (1=1)
begin
ALTER PARTITION SCHEME PartationedTableA NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION PartationedTableA () SPLIT RANGE (14661)
ALTER PARTITION FUNCTION PartationedTableA () MERGE RANGE (14661)
end
code in connection2:
declare @rowcount int
while 1=1
begin
begin try
select @rowcount =count(0) from PartationedTableA where col1=50021
end try
begin catch
print error_number()
end catch
end
run them at the same time in SSMS, the code in cn2 would get a error in several seconds:
Msg 1205, Level 13, State 55, Line 10
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I tried using TRY/CATCH to capture anoter deadlock whose Error_State is 45, and it works fine.
Does anybody know why Try/Catch cannot capture my deadlock, does the different state make senses?
Ive started using try/catch in my t-sql code now and I rather like it, since im a C# developer. I read that some errors with an error code below 10 will not cause the catch block to be entered. What kind of errors does this include ?
HI, i'm trying to execute some sql using the Try.. Catch blocks.
Following code does not execute in Catch Block
Begin begin try insert into dbo.Test values (1,'aaa') -- here we are inserting int value in identity field... END TRY Begin catch PRINT 'TEST' SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage; END Catch End GO
Whereas the following block works fine and the Catch block executes.
Begin begin try Select 1/0 --This causes an error. END TRY Begin catch PRINT 'TEST' SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage; END Catch End GO
one of our study group members noticed a strange behavior and has the following question. Any thoughts are appreciated. I am unable to understand as to why the CATCH block is not executed when an INSERT is made On table T3 which is dropped after the first transaction.
The severity of Insert into t3 values (3) is Msg 208, Level 16, State 1, Line 2 Invalid object name 't3'.
BOL says TRY€¦CATCH constructs do not trap the following conditions: Warnings or informational messages with a severity of 10 or lower. Errors with severity of 20 or higher that terminate the SQL Server Database Engine task processing for the session. If an error occurs with severity of 20 or higher and the database connection is not disrupted, TRY€¦CATCH will handle the error.
Here is the script.
use tempdb go
create table t1 (a int) create table t2 (b int) create table t3 (c int)
Begin tran
Insert into t1 values (1) Insert into t2 values (2) Insert into t3 values (3)
IF @@error <> 0 Rollback tran else commit tran -------------------------------------------------------------
Select * from t1 Select * from t2 Select * from t3 -------------------------------------------------------------
Insert into t1 values (1) Insert into t2 values (2)
save tran insertNow
Insert into t3 values (3) commit tran insertNow End try
Begin Catch
IF (XACT_STATE()) = -1 BEGIN PRINT 'The transaction is in an uncommittable state.' + ' Rolling back transaction.' ROLLBACK TRANSACTION insertNow END;
-- Test if the transaction is active and valid. IF (XACT_STATE()) = 1 BEGIN PRINT 'The transaction is committable.' + ' Committing transaction.' COMMIT TRANSACTION insertNow END
I guess this is a common problem because I ran into a lot of threads concerning the matter. Unfortunately, none of them helped my situation.I am throw a RAISERROR() in my sql and my vb.net try catch block is not detecting an error. SELECT '3' RAISERROR('testerror',10,5) Dim con As New SqlConnection Dim _sqlcommand As New SqlCommand con = New SqlConnection(Spiritopedia.Web.Globals.Settings.General.ConnectionString) _sqlcommand.CommandType = Data.CommandType.StoredProcedure _sqlcommand.CommandText = "TestFunction" _sqlcommand.Connection = con
'The value to be returned Dim value As New Object 'Execute the command making sure the connection gets closed in the end
Try
'Open the connection of the command _sqlcommand.Connection.Open() 'Execute the command and get the number of affected rows 'value = _sqlcommand.ExecuteScalar().ToString()
value = _sqlcommand.ExecuteScalar()
Catch ex As SqlException Throw ex Finally
'Close the connection _sqlcommand.Connection.Close() End Try
Is there any way to clear out the exception from a previous Try/Catch block if I am nesting another Try/Catch block within it. I am executing a SQL Command and based on the Error number coming back decide whether or not to execute various other Sql commands. Now the Outer exception seems to be taking precedence over the Inner try block, and even though the code is stepped through for the inner try block it is never executed due to the Parent Exception. Is there any way to clear the exception received from the outer Try block? Here is a snippet of code:
I encountered a werid bug that I can't figure it out in my stored procedure.Here's some sample code the can represent the scenario
Create Proc sp_test @DeptID Int as Begin Declare @i int=0 Declare @Count int=(Select count(*) from Total) while(@i<@Count)
[code]....
In the above code Total is a table that has employee name and its department ID and row_number info.The above code should list all employee info that belongs to one DEpt.but after I placed a try catch block the select statement returns no records.If I removed the try catch block it behaves correct.For example If three records reside in the Total table for a certain DeptID.
I expect the outPut will be Name age salary Mike 35 $60006 Tom 50 $75000 Frank 55 $120000
Can someone please help me figure out why this trigger causes a "Timeout expired" error after the first try? What I'm trying to do is create an incrementing default value for the projectid field. I have a unique index on the field and for some reason, the INSERT statement won't run inside the TRY block, even though the WHILE loop creates a unique id. It's acting like the loop isn't incrementing, but the PRINT statements prove that the loop does work, but the INSERT statement doesn't work after a succesful first try (i.e. NEWPROJ-1 is inserted correctlly). Anyways, any help would be appreciated.
Code Snippet GO /****** Object: Trigger [Projects].[CreateID] Script Date: 05/25/2008 14:16:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
ALTER TRIGGER [Projects].[CreateID] ON [Projects].[tblProjects] INSTEAD OF INSERT AS DECLARE @projectid varchar(25) DECLARE @projectname varchar(100) DECLARE @projectepskey int DECLARE @maxcount int SELECT @maxcount = 1
BEGIN SET NOCOUNT ON; SELECT @projectname = projectname FROM inserted SELECT @projectepskey = projectepskey FROM inserted
SELECT projectid FROM Projects.tblProjects WHERE (projectid = 'NEWPROJ')
IF (@@RowCount = 0) BEGIN SELECT @projectid = 'NEWPROJ' INSERT INTO Projects.tblProjects(projectepskey, projectid, projectname) VALUES (@projectepskey, @projectid, @projectname) END ELSE BEGIN WHILE @maxcount >= 1 BEGIN
BEGIN TRY SELECT @projectid = 'NEWPROJ-' + CONVERT(varchar, @maxcount) PRINT @projectid INSERT INTO Projects.tblProjects(projectepskey, projectid, projectname) VALUES (@projectepskey, @projectid, @projectname) BREAK END TRY BEGIN CATCH SELECT @maxcount = @maxcount + 1 PRINT CONVERT(varchar, @maxcount) CONTINUE END CATCH; END END END
I have a Stored Proc which populates a table and then uses BCP to output the table into a flat file and lastly ftp the file out to a remote site.
I'm trying to update the error handling as I first wrote this script on SQL2000 and it has now moved to SQL2008r2. The stored proc looks something like this:
BEGIN TRY BEGIN TRANSACTION <A whole bunch of inserts and updates> COMMIT TRANSACTION END TRY BEGIN CATCH <Error handling> ROLLBACK TRANSACTION END CATCH BEGIN <xp_cmdshell, BCP, FTP stuff> END
What I need to do is jump to the end of the script if an error invokes the CATCH block, so the xp_cmdshell stuff is not exicuted. Can I simply put a GOTO statement to take it to the end in the CATCH block, or do I have to set a variable in the CATCH block then test the variable outside the CATCH block or indeed is there a better way to simply terminate the script following the ROLLBACK?
When a catchable error occurs in a try block, control is passed to theassociated catch block. While I am then able to examine properties ofthe error using such functions as ERROR_NUMBER() in the catch block,the error-logging scheme used in my company requires that the actualerror be raised so that it can be picked up by components in the nexttier. I appear to be unable to do that -- I can raise some other errorthat has all of the properties of the original except the error number.For example, division by zero raises error 8134. If the code thatproduces that error is enclosed in a try block, I seem to be unable toraise that error from the catch block. Of course, if I never used thetry block to begin with this wouldn't be a problem, but then wewouldn't have the advantages of this structure as it applies to othererrors, some of which we may wish to handle differently. WhileTry-Catch looks great as a way of allowing us to handle errors in acustomized way and to avoid having all errors be passed up to ourmiddle tier, it seems that we are unable to pass ANY such errorsforward, and that is a problem for us, too. Is my understanding ofthis situation correct, or is there some way around this problem, e.g.,is there any way I could have raised error 8134, in the above example?Thanks.
I use a CLR external function to call a payment gateway, for some reason, I could not get any status sometime, check the following code, I expect I get either -2 or -3 in status no matter what error happens - include the gateway error or the CLR error since I have try catch there. Do you think the following code could miss any problem?
update link_payments set ccagentcode=@ccagentcode,status=-2,postresult=@postresult where id=@paymentid
.... end try begin catch select @message= '<br>ErrorNumber: '+cast(ERROR_NUMBER() as varchar)+char(13)+ '<br>ErrorSeverity: '+cast(ERROR_SEVERITY() as varchar)+char(13)+ '<br>ErrorState: '+cast(ERROR_STATE() as varchar)+char(13)+ '<br>ErrorProcedure: '+isnull(ERROR_PROCEDURE(),'')+char(13)+ '<br>ErrorLine: '+cast(ERROR_LINE() as varchar)+char(13)+ '<br>ErrorMessage: '+isnull(ERROR_MESSAGE(),'')+char(13)
update link_payments set status=-3,postresult=@message where id=@paymentid return -1 end catch
I was just debugging a stored procedure visual studio and I was surprised with what I was watching.
I'm running SQL 2005 and visual studio 2005. I have a set of nested try/catch blocks.
It fails on the first try and drops into the catch block just as it should. Within the catch block a dynamic sql statement is created. A try block begins and attempts to exec(@sql). It then drops to the catch block. However, I copy the statement into SQL Management Studio and it runs without error everytime.
Why is it dropping into the catch block? Logically it should just complete executing the statement continue without going into the catch block.
Do I have to reset the raiserror values between try blocks or something?
Hello, I have stored procedure that when executed it will check to see if a given name is found in the database, if the name is found, I would like to have it continue on to do its work, however if the name is not found, I would like it to raise an error and then stop execution at that point, however, the way it is currently working is, if the name is not found, it catches the error, raises it and then continues on and tries to do its work, which then it bombs out because it can't. I wasn't sure if there was a way to stop the execution of the procedure in the catch statement. I don't think I want to raise the error level to 20-25 because I don't want to drop the connection to the database per say, I just want to halt execution.
Here is a simple example of what I have:
Code Snippet begin try
if not exists (select * from sys.database_principals where [name] = 'flea')
raiserror('flea not found', 16, 1) end try begin catch
@ErrorMessage = error_message(), @ErrorSeverity = error_severity(); raiserror(@ErrorMessage, @ErrorSeverity, 1); end catch go
begin
print 'hello world' end
At this point, if the user name (flea) is not found, I don't want it ever to get to the point of 'Hello World', I would like the error raised and the procedure to exit at this point. Any advice would be appreciated on how to best handle my situation!
I'm having trouble with a Script Component in a data flow task. I have code that does a SqlCommand.ExecuteReader() call that throws an 'Object reference not set to an instance of an object' error. Thing is, the SqlCommand.ExecuteReader() call is already inside a Try..Catch block. Essentially I have two questions regarding this error:
a) Why doesn't my Catch block catch the exception? b) I've made sure that my SqlCommand object and the SqlConnection property that it uses are properly instantiated, and the query is correct. Any ideas on why it is throwing that exception?
I have the following stored procedure to test scope of variables
alter proc updatePrereq @pcntr int, @pmax int as begin
[Code] ....
In the above script @i is declare in the if block only when the @pcntr value is 1. Assume the above stored procedure is called 5 times from this script
declare @z int set @z = 1 declare @max int set @max = 5 while @z <= @max begin exec dbo.updatePrereq @z, @max set @z = @z + 1 end go
As i said earlier `@i` variable exists only when `@pcntr` is `1`. Therefore when i call the stored procedure for the second time and so forth the control cannot enter the if block therefore @i variable wouldn't even exist. But the script prints the value in `@i` in each iteration, How comes this is possible should it throw an error saying `@i` variable does not exist when `@pcntr` values is greater than `1`?
Is there a way to send out an email woth deadlock information (victim query, winner query, process id's and resources on which the deadlock occurred) as soon as a deadlock occurs in a database or at instance level?I currently has trace flag 1222 turned on. And also created an alert that send me an email whenever a deadlock occurs. but it just says that a deadlock occurred and I log into sql server error log and review the information.
All of a sudden my application started crashing when trying execute dml statements on sql server mobile database (sdf file). Frustating thing is that whole application crashes without any error message. this happens for all kinds for DML statement but not all the time. Sometimes it would fail for delete statement such as delete from table; or for insert into statement
my problem catch does not catch the error. There is no way to find out teh what is causing this error
SqlCeConnection sqlcon = new SqlCeConnection("
Data Source = '\Program Files\HISSymbol\HISSymboldb.sdf';"
);
SqlCeCommand sqlcmd = new SqlCeCommand();
sqlcmd.CommandText = Insert into company('AA', 'Lower Plenty Hotel');
sqlcmd.Connection = sqlcon;
SqlCeDataReader sqldr = null;
try
{
sqlcon.Open();
//use nonquery if result is not needed
sqlcmd.ExecuteNonQuery(); // application crashes here
}
catch (Exception e)
{
base.myErrorMsg = e.ToString();
}
finally
{
if (sqlcon != null)
{
if (sqlcon.State != System.Data.ConnectionState.Closed)
I'm not looking for details or code, just general direction I should go. Here is my goal: I have a SQL stored procedure that returns data which I will need to add spaces to, then end up with a space delimited text file. This txt file will then be needed to be saved and emailed. Also, I would like to be able to schedule this in the Task Scheduler for once a month execution. Is this all just one big crazy idea? Thanks for any help. Andrew
i want to take 70-028 test. but i still have some questions about administration. can anybody help me? Question: (NY1-----T1-----NY2)-----56kbps-----(Seattle-----T1-----SanFrancisco-----T1-----Los Angeles) NY1=NewYork1,NY2=NewYork2,SF=SanFrancisco,LA=Los Angeles A. NY1=pub/dist; NY2,Seattle,SF,LA as subscriber B.NY1=pubs; NY2=dist;Seattle,SF,LA as subscriber C.NY1=pubs; SF=subs/dist/pubs; NY2,Seattle,LA as subscriber D.NY1=pubs; LA=dist; something not workable which one is correct answer, and why? How to do the questions like this?
In the company that I work there are two offices, one is outside the city and the other is in the city. They are connected trough a DS0 using frame relay. The thing is that the comunication is very slow (the SQL server is in the city) and we want to add a new server at the office outside the city and we want at the end of the day replicate the data between the two servers so every server has the actual data. We didn't use primary keys, instead of that we used identities (autoincremental value administered by the server). What kind of replication should I use and why? (I'm new with SQL Server). There are 3 kinds: Snapshot, Transactional and Merge.
Hello, I created web site using VWD 2005 Express, and I use 'Create New user account' control for signin up. I need to know what kind of database is ASPNETDB.MDF ' in order to set it on server? I created ASPNETDB.MDF (database?) when I first created user in ASP.Net Web Site Administration Tool . What tipe is ASPNETDB.MDF database? . I have my web site on server, and DB is not working, because it is not set on server.I have this databases available on my server:Microsoft Access Unlimited Microsoft SQL Server 2000 1 x MySQL 1 x PostgreSQL 1 xDSN/ODBC Unlimited SQL Database Users Unlimited Which one you recommend should I choose for members database (ASPNETDB.MDF)?Thank you, Beezgetz
I have a problem in a Stored Procedure SQL query im writing, basically what I want is something that works like this
WHERE intField IN (@strCommaSepList_Of_Integers)
First I tried this:
SELECT idComp_Clie_Bran_User, strComp_Clie_Bran_User_FirstName, strComp_Clie_Bran_User_Surname FROM dbo.tblComp_Clie_Bran_Users WHERE (fk_idCompany = @fk_idCompany) AND (fk_idComp_Client = @fk_idComp_Client) AND (fk_idComp_UserGroup IN (@strListUserGroupsAllowed))
But if I passed a 25,26 value to the last paramenet is gave an error saying:
Syntax error converting the varchar value '25,36' to a cllumn of data type int.
How can I get this to work??
If for example I manually put the WHERE IN values it works, ike this I mean:
SELECT idComp_Clie_Bran_User, strComp_Clie_Bran_User_FirstName, strComp_Clie_Bran_User_Surname FROM dbo.tblComp_Clie_Bran_Users WHERE (fk_idCompany = @fk_idCompany) AND (fk_idComp_Client = @fk_idComp_Client) AND (fk_idComp_UserGroup IN (25, 36))
But thats no use as I have to pass the list of values dynamically...
Hi, I am unable to understand how can I create a new filegroup? I know how to move certain text/ntext/images column to a new file group - thru design table but my problem is how to create a new filegroup? Do I have to use Alter Table/Alter Database for this or there is any other better way? TIA Jai.
Now, the root level items will have a ParentID of NULL, and the child nodes will have it's parent's ID in it's ParentID column (a simple self-referencing relationship).
However, when I select it, I would like to Order by ID, but then have all it's children right after it. For example, if this is the unsorted view:
Code:
ID ParentID Name ---------------------- 1 <NULL> Test 2 1 Test Child 1 3 <NULL> Test 2 4 1 Test Child 2 5 3 Test 2 Child 1
I would like the sorted to look like:
Code:
ID ParentID Name ---------------------- 1 <NULL> Test 2 1 Test Child 1 4 1 Test Child 2 3 <NULL> Test 2 5 3 Test 2 Child 1
Is this possible with my current structure? If not, how could I change it?
Hey all! This may sound like a weird one but i couldn't think of a better audience to ask!
If you are a SQL Server DBA (or were, or aspire to be, or play one on T.V., etc.) and the CIO of your 2.something billion $ a year company has offered you an 1 hour forum to sit down and ask him anything you want with the pretense of getting a straight answer, what would you ask?
I know this offers an excellent opportunity to cut up a bit...and do so if you must (keeping us entertained around here is important too!)...but i'm looking for work related, SQL related, direction, strategy, etc, etc type stuff. Not: 'Whats it like to drive a $250k 'benz to work everyday?' OR 'Can i have .5% of your $2,000,000 bonus this year?'
While i genuinely have those questions in my mind...i'm not looking to waste his $5,000 an hour ass's time - and I only have 1 hour to chew his ear off.
Just can't figure this out. I'll try not to give a long-winded explanation (I hope), let's say this is the table, for example: [Table1] RowNumber | Value 1 | 4 2 | 6 3 | 3 4 |10 5 | 6 6 | 5 7 | 8 8 | 8 9 | 2 10 | 6
I want to write a query that creates a column whose values are similar to (ROW_NUMBER -1), and resets each time a value is present. Using the above example, 6 will be the test value. So, I want to know how many rows it takes till 6 is repeated.
I think PARTITION BY can be used somewhere, with the ORDER BY on the [RowNumber] - but I'm just not sure on which ranking function to use, and how to reset the Ranking on the number 6 (above example).
Any help would be appreciated. This has been a mind-teaser for me, and I give
