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 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 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 package that should accomplish the following task: - Loop on all files in a given directory. - Before proccessing the current file in the Enumerator, it should be moved to a folder called "importing" - Load the content of the file into a destination DB. - After a successfull load, the file is moved from the "importing" to the "success" folder. - If anything went wrong during the load process, the file should be moved from the "importing" to the "error" folder and an e-mail should be sent out to a certain admin account.
What I have done so far: My control flow looks like this: Foreach Loop containing the following tasks: - File System Task: moving the current file from its original path to the "importing" folder - Data Flow Task1: performing some transformations and insertions into the DB and raw files. - Data Flow Task2: performing some transformations and insertions into the DB and raw files. - Data Flow Task3: performing some transformations and final insertions into the DB. - File System Task: moving the current file from the "importing" to the "success" folder
Question: I do not know how to catch the event that one of my three Data Flow Tasks has failed and in this case perform two simple tasks, namely... - File System Task: moving the current file from the "importing" to the "error" folder - Send Mail Task: sending a configured e-mail message to a ceratin administrator.
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.
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 am running the following query, which deletes around 800 million rows from the table.
I would like to introduce transactions with in this code and also, if success entire deletion process should be committed and number of rows deleted, table name and success should be inserted to the log table.
If there is a failure, transaction should be rolled back and table name and error message should be inserted to the same log file
Select 1 While @@ROWCOUNT > 0 Begin DELETE Top(100000) FROM [dbo].[Table1] FROM [dbo].[Table2] INNER JOIN [dbo].[Table3] ON [Table2].[PracticeID] = [Table3].[PracticeID] INNER JOIN [dbo].[Table1] ON [Table3].[InputDevicePracticeID] = [Table1].[InputDevicePracticeID] WHERE [Table2].PracticeID =55; End
I am reviewing some code we have inherited (riddled with multiple nested cursors) and in the process of re-writing some of the code. I came across this and it has me puzzled.
As I understand it, if you declare a variable and then try to re-declare a variable of the same name an error is generated. If I do this inside a While loop this does not seem to be the case. What ever is assigned is kept and just added to (in the case of a table variable)
I understand things are in scope for the batch currently running but I would expect an error to return (example 1 and 2)
--Table var declaration in loop SET NOCOUNT ON DECLARE @looper INT = 0 WHILE @looper <= 10 BEGIN DECLARE @ATable TABLE ( somenumber INT )
I am using a cursor (i know - but this is actually something that is a procedural loop).
So effectively i have a table of names of stored procedures. I now have a store proc that loops around these procs and runs each one in order.
Now i am thinking i would like to be able to set the table it loops around in a variable at the start - is it possible to do this? So effectively use a tablename in a variable to use in the sql to define a cursor?
Need getting the below Cursor query convert to a Recursive CTE or with a while loop as I do not want to use a cursor.
Declare @Companyname Nvarchar (400) declare @str nvarchar(MAX) TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry] DECLARE GLEntry_cursor CURSOR FOR SELECT REPLACE Name FROM Company where Name <> 'AAAAA' OPEN GlEntry_cursor
I'm trying to build a simple cursor to understand how they work. From the temp table, I would like to print out the values of the table, when I run my cursor it just keeps running the output of the first row infinitely. I just want it to print out the 7 rows in the table ...
IF OBJECT_ID('TempDB..#tTable','U') IS NOT NULL DROP TABLE #tTable CREATE TABLE #tTable
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 a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.
I've written a couple blocks but I have no idea when it comes to this one. Create a PL/SQL block to retrieve the last name and department ID if each employee from the EMPLOYEES table for those employees whose EMPLOYEE_ID is less than 114. From the values retreived from the employees table, populate two PL/SQL tables, one to store the records of the employee last names and the other to store the records of their department IDs. Using a loop, retreive the employee name information and salary infromation from the PL/SQL tables and doisplay it in the window, using DBMS_OUTPUT.PUT_LINE. Display these details for the first 15 employees in the PL/SQL tables. Any help helps
If I've got a 64-bit OS such as Windows XP Professional x64 Edition running and I have the SQL Server 2005 Express Edition, is AES permittable or does TRIPLEDES still need to be used?
