I am new to T-SQL programming. I need to write a main procedures to execute multiple transactions. How could I structure the program so that each transaction will not abort if failed. Instead, the next transaction will keep running. At the very end the procedure will output the error and report them back to the main program in the output parameters. Looking for pseudo code.
View 6 RepliesHello everyone,
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.
Thanks in advance...
Regards,
Samar
OLEDB transaction is making difference in my migrated application powerbuilder 10.5 which has new oledb driver which is replaced by MSS driver in previous Powerbuilder version.
1.)I am facing the below error on update/save this was not faced during my previous versino drive MSS in powerbuilder.This is oocure only after the change to oledb driver.
"Row change between retrieve and update.".
in front end of my application the error is poped as
"(STD1007) This row has been modified by another user. Save is canceled! ".
Please help to resolve this error.
I am running an environment as below
ADO 2.7
SQL Sever OLE DB Provider
VB 6
SQL Server 6.5 SP 5 A Update
If I run a stored procedure that contains a Print statement or gives a warning. Then any error that occurs after that is ignored. No error state is raised in VB and the Errors collection only contains the print or warning.
Can anyone tell me why?
Thanks
Chris
SQL Server has stopped a critical task, with the following error log. I have executed the task again and the same symptons occur.
--
EXCEPTION_ACCESS_VIOLATION raised, attempting to create sympton dump
***BEGIN STACK TRACE***
0x0040DC1A in SQLSERVR.EXE, ubfree() + 0x009A
...
...
--
Has anyone any had a simliar experience or have any suggestions for investigation?
Cheers
Jasper
I'm setting up a routine to do some reindexing. To get the info I need I'm trying to use the new function. The DB I think is causing the problem does pass DBCC CHECKDB. I'm not sure why I'm getting this but can not find anyone else that is getting this same error.
When I run this....
SELECT *
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, 'detailed')
I get this....
Location: qxcntxt.cpp:954
Expression: !"No exceptions should be raised by this code"
SPID: 236
Process ID: 1060
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
If I run only for the DB I think is causing the problem I get this...
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I'm trying to get to grips with the formview control and have a problem regarding where an exception is raised if SQL server raises an error when an insert is taking place. I am new to both asp.net & c#
I have put the following code in the SqlDataSource2_Inserted & Formview1_Inserted events as a way of determining where the exception is raised:
if (e.ExceptionHandled){ string exceptionmessage = e.Exception.Message; lblStatus.Text = exceptionmessage;}
These two events are the only one's that have the ExceptionHandled and Exception properties.
I run the code with data that will raise a duplicate key error from SQL Server but the error is not being caught by either of these two handlers. All I get is a page that starts with
Server Error in '/SourceCode' Application.
Violation of UNIQUE KEY constraint 'IX_reftblAntigens'. Cannot insert duplicate key in object 'reftblAntigens'.The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'IX_reftblAntigens'. Cannot insert duplicate key in object 'reftblAntigens'.The statement has been terminated....
Is there somewhere else I should be looking to handle this sort of thing? I thought using the databound controls would make life easier but I'm actually finding it more of a pain than coding everything myself.
Hi,
I am using sql2k5. I just wanted to throw an error from stored procedure with some message to C# to rollback my transaction.
Here is how i wnated to do ( in sequence )
C#
=====
Open a connection
Begin the transaction
Execute the command
In the Stored Proc
===========
do multiple operations one by one
if error
stop processing further
Throw the error
C#
========
if exception
rollback the transaction
else
commit the transaction
I have tried using raise error in stored proc but never thrown exception
Can any one let me know how to achieve this scenario??
~Mohan Babu
We have a required to run multiple procedures in Single Go . And Error Occurred in any Procedure the it will rollback all the changes( Either all Proc run or None)
DECLARE @StartTime DATETIME=getdate(), @EndTime DATETIME=getdate()-1 , @Message VARCHAR(400)
BEGIN TRY
SET XACT_ABORT ON
BEGIN TRANSACTION
EXEC PROC1 @StartTime,@EndTime,@Message OUTPUT --[ Error Handling done here]
EXEC PROC2 @StartTime,@EndTime,@Message OUTPUT --[ Error Handling done here]
[Code] ....
Problem Statement is its not capturing the Error Message from Either Proc1 or Proc 2., its Capturing the Flat Message (The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction). How do i capture the Error Occurred in Proc 1 or Proc 2 into Log Tables.
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).
The statement has been terminated.
DDL of the target table:
CREATE
TABLE [dbo].[GiftCard](
[CardNumber] [varchar](50) NOT NULL,
[Year] [int] NULL,
[Month] [int] NULL,
[Period] [int] NULL,
[TransAmount] [decimal](10, 2) NULL,
[Code] ....
the idea is to put together a folder that is in some place in the computer, like "c:excel_fles" and the database full backup in a single .bak file, the database can be recovered with sql server management studio(but no the folder), the restoration of the program can be done with a program, I think it can be done with msdb, but I'm a little lost in the road.
View 4 Replies View RelatedHi,
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?
Hope someone could help.
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
We have a stored procedure that calculates the floor nr for users at our company campus using their office location. The calculation is done by a function that returns an integer. Unfortunately, not all users enter their information correctly so the function sometimes raises an error. Below is the code of that stored procedure.
UPDATE PERSONS
SET FLOORNR = dbo.FloorNR(OFFICELOCATION)
WHERE OFFICELOCATION IS NOT NULL
IF(@@ERROR <> 0 OR @@ROWCOUNT = 0)
BEGIN
RAISERROR ('Failed to calculate the floor number', 16, 1 ) with nowait
END
However, when the function dbo.FloorNR fails, it doesn't raise our error, but it seems to raise the error that comes from dbo.FloorNR.
How can we catch errors that come from dbo.FloorNr so that we can raise our own error? Our company still uses SQL 2000, so we cannot use the SQL 2005 try/catch option.
Hi,
I have the following stored procedure which is added to the windows scheduler. When it is run I'm getting an "Invalid use of null error", therefore I need to capture and any errors but I'm not sure how to do this. Can someone help?
Thanks.
Code:
CREATE PROCEDURE [dbo].[usp_Reminders] AS
DECLARE @ReminderSent datetime
DECLARE @getRecords CURSOR
DECLARE @err int
SELECT E.[RL Staff No], E.Forename, E.Surname, C.CourseName, V.CourseDate , E.Email, V.ReminderSent
FROM empdetails.dbo.v_Employee E
INNER JOIN Validation V ON E.[RL Staff No] = V.[RL Staff No]
INNER JOIN empdetails.dbo.v_Course C ON V.CourseCode = C.CourseCode
WHERE V.Completed Is Null AND V.ReminderSent Is Null AND V.CourseDate <= dateadd(dd, -3, getdate())
order by e.[rl staff no]
SET @getRecords = CURSOR FOR
SELECT V.ReminderSent
FROM empdetails.dbo.v_Employee E
INNER JOIN Validation V ON E.[RL Staff No] = V.[RL Staff No]
INNER JOIN empdetails.dbo.v_Course C ON V.CourseCode = C.CourseCode
WHERE V.Completed IS NULL AND V.ReminderSent IS NULL AND V.CourseDate <= dateadd(dd, -3, getdate())
ORDER BY e.[rl staff no]
OPEN @getRecords
FETCH NEXT FROM @getRecords INTO @ReminderSent
WHILE @@FETCH_STATUS= 0
BEGIN
UPDATE Validation
SET ReminderSent = GetDate()
WHERE CURRENT OF @getRecords
FETCH NEXT FROM @getRecords INTO @ReminderSent
END
CLOSE @getRecords
DEALLOCATE @getRecords
GO
Hi everyone, I am using an SQL extended stored procedure to send emails in aDTS package using a cursor that goes through each row in a table.Email sending code below======================exec master.dbo.xp_smtp_sendmail@FROM = @sFrom,@FROM_NAME = @sFrom,@TO = @sRecepients,@subject = @sSubject,@message = @sBody,@type = N'text/html',@codepage = 0,@server =N'MYMAILSERVER'======================Fetch Next From EmailCursor ...Now the problem I have is that if an individual email address in invalidthen an error occurs and the whole DTS package falls over. What I would liketo be able to do is "catch the error", something like this (C# code used asexample)try{exec master.dbo.xp_smtp_sendmail@FROM = @sFrom,@FROM_NAME = @sFrom,@TO = @sRecepients,@subject = @sSubject,@message = @sBody,@type = N'text/html',@codepage = 0,@server =N'MYMAILSERVER'} catch {exec master.dbo.xp_smtp_sendmail@FROM = "arealaddress@mybusiness.com",@FROM_NAME = @sFrom,@TO = @sRecepients,@subject = @sSubject,@message = @sBody,@type = N'text/html',@codepage = 0,@server =N'MYMAILSERVER'}Is this possible??? Normally I would do all the email validation before theemail is entered into the database but unfortunately, I do not have accessto the application code so I am stuck doing it this way.Thanks in advanceMark
View 2 Replies View Related
Trying to update my local developer version of SQL Server 2005 SP1 to SP2. The setup program terminates with the following information dumped from the logs. Not sure if it is related, but I recently installed the SQL Server 2005 Compact Edition (tools, runtime, etc) on the same development machine. It is a little too deep for me to figure out
Darryl
Here are the last few lines of the HotFix.Log
04/24/2007 14:46:48.374 MSP Error: 29527 The setup has encountered an unexpected error in datastore. The action is RestoreSetupParams. The error is : Source File Name: datastorecachedpropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:27:59 2006
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 138
----------------------------------------------------------
Failed to read property "InstallIds" {"MachineConfiguration", "", "DUTTONDARRYL1"} from cache
Source File Name: datastorecachedpropertycollection.cpp
Compiler Timestamp: Wed Jun 14 16:27:59 2006
Function Name: CachedPropertyCollection:etProperty
Source Line Number: 164
----------------------------------------------------------
Unable to write property into cache: IsClustered
Source File Name: datastoredatastorecacheschema.cpp
Compiler Timestamp: Wed Jun 14 16:28:00 2006
Function Name: DataStoreCacheSchema::writeProperty
Source Line Number: 115
----------------------------------------------------------
Unable
04/24/2007 14:46:48.694 MSP returned 1603: A fatal error occurred during installation.
04/24/2007 14:46:48.790 Registry: Opened registry key "SoftwarePoliciesMicrosoftWindowsInstaller"
04/24/2007 14:46:48.790 Registry: Cannot read registry key value "Debug"
04/24/2007 14:46:48.806 Copy Engine: Error, unable to install MSP file: c:f01fc31fd0988e3807HotFixSQLFilessqlrun_sql.msp
04/24/2007 14:46:48.822 The following exception occurred: Unable to install Windows Installer MSP file Date: 04/24/2007 14:46:48.822 File: depotsqlvaultstablesetupmainl1setupsqlsesqlsedllcopyengine.cpp Line: 800
******************************************************
Now the SQL9_Hotfix_KB921896_sqlrun_sql.msp.log shows this as the last few lines
MSI (s) (A4:38) [14:46:48:630]: Product: Microsoft SQL Server 2005 - Update 'Service Pack 2 for SQL Server Database Services 2005 ENU (KB921896)' could not be installed. Error code 1603. Additional information is available in the log file C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log.
MSI (s) (A4:38) [14:46:48:630]: Note: 1: 1729
MSI (s) (A4:38) [14:46:48:630]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:630]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:646]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:646]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:646]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:646]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:646]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:646]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:646]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:646]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:646]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:662]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:678]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:678]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:678]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:678]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:678]: Transforming table Error.
MSI (s) (A4:38) [14:46:48:678]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (A4:38) [14:46:48:678]: Product: Microsoft SQL Server 2005 -- Configuration failed.
MSI (s) (A4:38) [14:46:48:678]: Attempting to delete file c:WINDOWSInstaller6749ee3.msp
MSI (s) (A4:38) [14:46:48:678]: Unable to delete the file. LastError = 32
MSI (s) (A4:38) [14:46:48:694]: Cleaning up uninstalled install packages, if any exist
MSI (s) (A4:38) [14:46:48:694]: MainEngineThread is returning 1603
MSI (s) (A4:2C) [14:46:48:694]: Destroying RemoteAPI object.
MSI (s) (A4:04) [14:46:48:694]: Custom Action Manager thread ending.
=== Logging stopped: 4/24/2007 14:46:48 ===
MSI (c) (58:E4) [14:46:48:694]: Decrementing counter to disable shutdown. If counter >= 0, shutdown will be denied. Counter after decrement: -1
MSI (c) (58:E4) [14:46:48:694]: MainEngineThread is returning 1603
=== Verbose logging stopped: 4/24/2007 14:46:48 ===
*************************************************************
The Summary.txt looks like this
Time: 04/24/2007 14:46:48.854
KB Number: KB921896
Machine: DUTTONDARRYL1
OS Version: Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Package Language: 1033 (ENU)
Package Platform: x86
Package SP Level: 2
Package Version: 3042
Command-line parameters specified:
Cluster Installation: No
**********************************************************************************
Prerequisites Check & Status
SQLSupport: Failed
**********************************************************************************
Products Detected Language Level Patch Level Platform Edition
Database Services (MSSQLSERVER) ENU SP1 2005.090.2047.00 x86 DEVELOPER
Analysis Services (MSSQLSERVER) ENU SP1 2005.090.2047.00 x86 DEVELOPER
Reporting Services (MSSQLSERVER) ENU SP1 9.00.2047.00 x86 DEVELOPER
Notification Services ENU SP1 9.00.2047.00 x86 DEVELOPER
Integration Services ENU SP1 9.00.2047.00 x86 DEVELOPER
SQL Server Native Client ENU 9.00.2047.00 x86
Client Components ENU SP1 9.1.2047 x86 DEVELOPER
MSXML 6.0 Parser ENU 6.00.3890.0 x86
SQLXML4 ENU 9.00.2047.00 x86
Backward Compatibility ENU 8.05.1704 x86
Microsoft SQL Server VSS Writer ENU 9.00.2047.00 x86
**********************************************************************************
Products Disqualified & Reason
Product Reason
**********************************************************************************
Processes Locking Files
Process Name Feature Type User Name PID
**********************************************************************************
Product Installation Status
Product : Database Services (MSSQLSERVER)
Product Version (Previous): 2047
Product Version (Final) :
Status : In Progress
Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfixSQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29527
Hey,Here's yet another question for you more knowledgeable than me Up to this point I have been using a try/catch statement when dealing with SQL, for exampleint result = 0;try{result = Int32.Parse(command.ExecuteScalar().ToString());}catch{result = 0;}But I read up one some error handling and I have no idea how to solve this anymore. Since I'll be using the catch block to catch exceptions, something like thiscatch (SqlException){throw;}So I was wondering what is the good, standard practice for dealing with this since I need the catch statement to set result to zero or I would end up with an error.Thanks in advance,Sixten
View 2 Replies View RelatedI'm looking for a discussion of the pros and cons of using TRY/CATCH as an error handler in a standard fashion with Stored Procedures. I've been using SQL Server for some time, and am accustomed to an approach of error handling in SPs that returns control from an SP via a common exit routine.
Has anyone defined an approach they would consider sharing? I realize that the reasons for standardizing on an approach includes requirements to your specific situation, so there are likely no perfect answers. I understand the basics of TRY/CATCH in SQL Server. Specifically, I'm looking for an approach where a common "Catch Handler" is used, paying attention to the issues around COMMIT/ROLLBACK.
If there are any threads that discuss this, let me know; I've performed a seach and found nothing so far.
Thanks,
Chuck
why we use sql exceptions ...
what the program will do if we caught that exception .. i need some suggestions ... i got this exception(String or binary data would be truncated.
The statement has been terminated.).. will it affect the functionality of the program...
hiow can i avoid this exception..
hello!
im new to sql... what i'm trying to do is catch the error on insert or update statment of sql.. sound simple but please..
this is the sample table design...
tbl_Customer
CustomerID int(4) Primary AutoIncrement
CustomerCode nvarchar(25)
CustomerName nvarchar(25)
..
..
Deleted bit(1)
what i'm trying to do is when a record is deleted, it's not actually deleted
in the table but only marked 1 (true) the Deleted field.. because i don't want
to lose the relationship...
it's easy to do this on insert statement like this..
Create Procedure InsertCustomer(@param1 ....) AS
IF NOT EXIST (SELECT * FROM tbl_Customer WHERE DELETED = 0) THEN
// do insert statement here
ELSE
// Do nothing
GO
this is also easy if i create a index constraints on the table.. but this will violate my design idea..
so anybody can help me to create the procedure in update statement and insert statement
hi,
I try to write a function which includes a statement:
SELECT @dateReturn = CAST(@dateString As datetime)
to convert a string to datetime.
When it runs, sometime it will generate :
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
and all related are terminated.
Can I find a way to catch this error and don't let it terminate the whole thing? For
example, when this happens, I want to get datetime as NULL instead of just being
terminated.
Thanks.
Greetings all,
When an error occurs it is written to a log file (Assuming you have loggin on).
Anyone know of a way to catch the error in a variable?
When an error occurs I send an email explaining where there error happened and to view the logfile. I would like to include the last error in the email. Saves having to go view the log...
Thanks
Hi all,
Just wondering what would be the normal or more efficient practice to insert/update a record.
1. Check for existence of primary record (using SELECT in stored procedure)
2. Capture error and handling
My problem is that I try to execute an stored procedure from a VB client.. but unable to capture the errors in SP, it just prompts the error and thats it, not responding to my "SELECT @err = @@ERROR" after the insert statement.
so now, I'm thinking of capturing the error on the client (which I am able to do) and handle it from there.. or to make sure that RI is enforced by 'searching' for Pks in the primary tables before executing the INSERT statement in my stored procedure.
Any advise would be appreciated..
Cyherus
hello,
i am trying to figure out how to check for failure or success AFTER the script task has ran.
its a piece of cake to write script logic that runs before the task but how do i check things and decide to retry AFTER a script task has ran?
i want to check for an error after a large table replication and if it detects that there was an error i want to RETRY.
dts does not seem to have this one specific piece of functionality. am i overlooking something?
I am trying to use a simple BEGIN TRY and END TRY in my SP. It is giving a compile time error such as
Line 13: Incorrect syntax near 'try'.
Why is this, can somebody help me out. Yes i am sure it is SQL Server 2005 on my machine.
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?
--Thanks--
Hi,
please have look into the code and let me know the solution plz.
string ProID;
try
{
using(SqlConnection conn=new SqlConnection(source))
{
conn.Open();
DataSet ds=new DataSet();
DDLProject.Items.Clear();
SqlCommand cmd=new SqlCommand("SP_ProjectSelect",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("Name",SqlDbType.NChar,30,"@Name");
cmd.UpdatedRowSource=UpdateRowSource.None;
cmd.Parameters["Name"].Value=DDLProductLine.SelectedItem;
SqlDataReader dr=cmd.ExecuteReader(); ------------->>>> Here iam getting Following Error.
while(dr.Read())
{ Response.Write(dr["ID"].ToString());
}
}
}
catch(System.Exception ex)
{
Response.Write(ex);
}
Error:
System.InvalidCastException: Object must implement IConvertible. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at MIS.UI.ResourceList.DDLProductLine_SelectedIndexChanged(Object sender, EventArgs e) in c:inetpubwwwrootdotnet pgmsmisui
esourcelist.aspx.cs:line 121
Any suggestion plzz,where went wrong.
Thanks in advance
Regards
Mahesh Reddy
my database is 4.1. When I attempt to install i get:
'/usr/local/mysql-5.0/data/mysql.sock'
To me this indicates 5.0
I'm stumped!!!!
All help is appreciated.
Thanks
Hi All,
I have the same question and error that Chopaka is getting:
"I have a SQL 2005 job that calls a stored proc. The job step returns the message "Query timeout expired....Message 7412...The step succeeded." The proc never actually ever did anything due to the query timeout, and the job continued on to other steps. I'm going to address the timeout issue eventually, but first I'd like to trap the timeout problem and force the job to end.
It appears that the query timeout isn't really an error, just a message. I've tried TRY-CATCH in the SP but the situation isn't caught, again probably due to the interpretation that it isn't an error."
I have reduced the "Remote Query time out" to 1 sec, in order to catch the error and to prevent the job from running, but the error is not caught.
Is there a way to catch this in the SQL or in the job step to prevent the job from continuing?
This is the script that I'm using without any luck
BEGIN TRY
BEGIN Transaction
Create table #tmpSummaryTable
(
)
insert into #tmpSummaryTable
select * from CDRServer01.iXtemp.dbo.gx_tbFTRSummary_test
COMMIT Transaction
END TRY
BEGIN CATCH
DECLARE @err int
SELECT @err = @@error
PRINT '@@error: ' + ltrim(str(@err))
SELECT ERROR_NUMBER() ERNumber,
ERROR_MESSAGE() Error_Message
ROLLBACK
Return
END CATCH
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
declare @ErrorMessage nvarchar(4000);
declare @ErrorSeverity int;
select
@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!
Thanks,
Flea
Hi,I have an application that causes a dead lock at random. The issue I amhaving is, when the deadlock occurs, my applications is not recievingany errors from the DB. ie, during the deadlock SQLServer is returningan empty recordset and user is seeing a blank screen. The app logicdoes not go into the Try Catch statment in the C# code. I can'tunderstand why my app is not receiveing 1205 error from SQL server whendead lock occurs.Any ides why this is happening?Thanks_GJK
View 3 Replies View RelatedI'm receiving the below error when trying to implement Execute SQL Task.
"The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran'
I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work.
Anyone know of the reason?