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?
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
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`?
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:
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
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 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?
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 have a stored procedure that runs every 5 minutes. I have one block in the procedure that will only run if there are records in a temp table. In addition, I would like this block to run only if the current time is between 0 and 5 minutes past the hour or between 30 and 35 minutes past the hour.
Currently, my block looks like this: IF OBJECT_ID('tempdb..#tmpClosedPOs') IS NOT NULL BEGIN
I can get the current minutes of the current time by using:
Select DATEPART(MINUTE,GetDate())
I know that it should be simple, but I'm pretty new at Stored Procedures. How do I alter the IF statement to check for the time and only run the block if it's between the times I stated? I started to DECLARE @Minutes INT, but wasn't sure where to go from there.
The problem is as follows. SQL Express 2008 R2, backup is made by the means of bat file and sp. Need to have a table that would hold columns like 'date_of_backup', 'duration', 'message' (the one that appears on 'messages' tab in the result area after backup completion/failure). This is an example of real message that I want to catch:
Server: Msg 911, Level 16, State 11, Line 1 Database 'not_existing_db' does not exist. Make sure that the name is entered correctly. Server: Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.
BEGIN TRY INSERT INTO ORDERS VALUES( 1122,'BAC123' ); INSERT INTO ORDLINE VALUES( 1122,991,1 ); PRINT 'VERY GOOD' END TRY BEGIN CATCH PRINT 'FALSE' INSERT INTO LOGTAB ('BAC123',1122,991,1, ERROR_NUMBER,ERROR_MESSAGE); END CATCH
i am getting error: Msg 102, Level 15, State 1, Line 11 Incorrect syntax near 'ERROR_NUMBER'.
I'm working on a stored procedure which includes an IF statement at the end of the procedure which appends my sql call with two lines. But I'm getting an error that probably comes from the fact that I have a nested BEGIN END block in my overall procedure. Can anyone tell me if my assumption is correct and help polish of the syntax?
The error I'm getting is:
Msg 156, Level 15, State 1, Procedure payments_sp, Line 55
Incorrect syntax near the keyword 'AND'.
and the sql code looks like this:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
ALTER PROCEDURE [dbo].[payments_sp] -- Add the parameters for the stored procedure here @payment_type varchar(15), @mydate smalldatetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DECLARE @myBool1 tinyint, @myBool12 tinyint SET @myBool1 = 1 IF (@payment_type = 'CODE1' OR @payment_type = 'CODE4') SET @myBool1 = 1 ELSE -- the payment type must be 'CODE1' or 'CODE2' -- set the value to false SET @myBool1 = 0 IF (@payment_type = 'CODE3' OR @payment_type = 'CODE4') SET @myBool12 = 1 ELSE SET @myBool12 = 0
SELECT SUM(Mydatabase.dbo.[PAYMENTS].[AMT_RECD]) FROM Mydatabase.dbo.[PAYMENTS] INNER JOIN Mydatabase.dbo.[ACCT] ON Mydatabase.dbo.[PAYMENTS].[SOME_UID] = Mydatabase.dbo.[ACCT].[SOME_UID] WHERE Mydatabase.dbo.[ACCT].[CLIENT] = 'MY CLIENT' AND Mydatabase.dbo.[PAYMENTS].[DATE_RECD] = @mydate AND Mydatabase.dbo.[PAYMENTS].[BOOL] = @myBool1 AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val1' AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val2' AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val3'
IF (@payment_type = 'CODE3' OR @payment_type = 'CODE4') BEGIN AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val4' AND Mydatabase.dbo.[PAYMENTS].[SOURCE] != 'val5' END END GO
SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO
I am new to use MERGE statement. The MERGE cannot find any match Cardnumber in the target table.  It inserts row into an existing row on the target table causing SQL rejected with duplicate key not allowed. The CardNumber is defined as a primary key on the target table with no duplicate allowed. Below snippet stop when MERGE insert a row exists on the target. The source table contains multiple rows with the same Cardnumber because it is a transactional table with multiple redemptions.Â
If MERGE cannot handle many (source) to one (target) relationship, what other method that I can change to in order to update the target GiftCard table which keeps track of gift card balance?Â
Below is the error message:
Msg 2627, Level 14, State 1, Line 5 Violation of PRIMARY KEY constraint 'PK_GiftCard'. Cannot insert duplicate key in object 'dbo.GiftCard'. The duplicate key value is (63027768).
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 a script that creates and populates several tables. However I only want this to occur if one table has a row count greater than zero. I'm trying to use GOTO to script to the end of the script. However I get the message "A GOTO statment references the label 'MYLABEL' but the label has not been declared." How can I do this.
I have something similiar to the following in my script: IF (SELECT COUNT(*) FROM MYTABLE) = 0 BEGIN PRINT 'NO ROWS FOUND' GOTO MYLABEL END
Hi,I have a problem:I am writing an update script for a database and want to check for theversion and Goto the wright update script.So I read the version from a table and if it match I want to "GotoVersionxxx"Where Versionxxx: is set in the script with the right update script.Whenever I have some script which need Go commands I get error in theoutput thatA GOTO statement references the label 'Versionxxx' but the label hasnot been declared.But the label is set in the script by 'Versionxxx:'Is there a way I can solve this easily?Thanks in advance
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 have someone telling me that I should have put my AND statement in the join instead of the where part, so is there a difference in the where I put it. Is there a difference in the results in any way between the two here in the results in anyway?
Example: Given a query: But let’s take a simple one here:
Query(1) Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode From cutTableA ct1 Left join cutTableA ct2 On ct1.ID = ct2.ID Where ct1.zipcode = '14124' AND ct1.Name = 'Bob'
Query(2) Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode From cutTableA ct1 Left join cutTableA ct2 On ct1.ID = ct2.ID AND ct1.zipcode = '14124' Where ct1.Name = 'Bob'
I need to write a SQL script where a join condition is using date columns (effective_date, ineffective_date). The effective date columns can be slightly different (e.g. differ by a day) for some rows of data. I need the join condition to accommodate these date differences and return these rows of data as well.
I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I tried the SQL script below using the BETWEEN clause but it returning 6 rows instead of 3–
Select P.S,E.S,E.R from Pack P(nolock) join Exp E on P.Id=E.O on E.R is null case when E.R is not null then '' else '' end where P.s='PLT000044'
I have to query two conditions joining the tables. when E.R is NULL and when E.R is not null. but the value is coming from the join between the 2 tables :P and E.
I am supposed to delete all rows from USER and its child tables based on PracticeID = '55' filter condition.
I have dynamically generated queries to delete child table first followed by parent. Table [EncounterSignOff] si child and [User] is parent.
I would like to know, whether the Query 1 is valid for deleting child records?
Query:1
DELETE Top(100000) FROM [dbo].[EncounterSignOff] FROM [dbo].[User] INNER JOIN [dbo].[EncounterSignOff] ON [User].[UserID] = [EncounterSignOff].[UserId] WHERE [User].PracticeID = '55';
Query:2
DELETE Top(100000) FROM [dbo].[User] WHERE [User].PracticeID = '55';
I have a population split between two vendors. One gets last names between A and R, the other the rest. Now, on a given date vendor 1 gets everybody.
I can accomplish this with a case statement on the upper range (R or Z), but it seems I should be able to do this without testing at all after the turnover date.
A small bit of the code:
declare @get_date datetime = convert(char(10),getdate(),101) select top 10 pt.pt_id, pt.last_name fromsmsmir.mir_acct a join smsmir.mir_pt pt on (a.src_sys_id = pt.src_sys_id and a.pt_id = pt.pt_id and a.from_file_ind = pt.from_file_ind
[Code] ....
Seems I should be able to not test the last name after the turnover date, but I can't figure out how.
Can we use case in pivot like below? I am getting an error. I want to do Pivot on condition basis.
select ( Column1 ,Column2 ,Column3 ,Column4 ,coloumn5 from Mytable ) x pivot ( case when Column1 = 6 then sum(Column3) else max(Column4) End for coloumn5 in (' + @COLS + ') )p
select case when class_desc='OBJECT_OR_COLUMN' then 'GRANT '+permission_name+' ON '+'['+left(object_name,3)+'].'+'['+substring(object_name,5,len(object_name))+ '] TO '+username WHEN class_desc='DATABASE_ROLE' THEN EXEC sp_addrolemember N'object_name', N'MC' end from dba.dbhakyedek where username='MC'
This statement was running successfully until exec sp_addrolemember thing. I just learned that i can't call a sp in select case but i couldnt figure out how to do it.
I have a situation where I want to update a column if and only if it is null.
UPDATE Employee SET VEmployeeID = CASE WHEN E.VEmployeeID IS NULL THEN ves.VEmployeeID END FROM Employee E INNER JOIN VEmployeeStaging VES ON E.EID= VES.EID
But what happens is when I run the procedure every other time I run it, it changes everything to null. The other times it puts the VEmployeeID in.
So what is happening is the times when it is not null (where it is not supposed to do anything) it puts a null in. The next time it works.
