First Question:
I have a script that can run successfully via the Query Analyzer even though it reports a few errors with error number 8152
"Server: Msg 8152, Level 16, State 2, Procedure IndexDensity, Line 85
String or binary data would be truncated.
The statement has been terminated."
Why does the script continue to finish in the Query Analyzer but fail immediately when it encounters the same error as a scheduled Job?
Second Question:
The serverity is 16 (user managed) so I would like to simply skip over the problem code/row and contiue with the next row to be modified.
How can I handle the error and yet have the script continue when it is scheduled as a job?
Additional Background:
I would like to do something like this:
If this line...
"SELECT @scan_dnsty = convert(decimal(5,2),RTRIM(substring(density_str, charindex('%',density_str) - 6, 6)))
FROM #Density_Out_Tbl"
...generates the error, then simply (skip it and move on to the next row)Fetch the next row.
Error: Server: Msg 8152, Level 16, State 9, Procedure Statement_proc, Line 97 String or binary data would be truncated. The statement has been terminated.
@Startdate As datetime, @Enddate As datetime, @Customer_no As char(15)
As Begin
declare @loanno as char(15) declare @transaction_date as datetime declare @transaction_type as char(3) declare @reference as varchar(20) declare @notes as varchar(255) declare @transaction_amount as decimal (9,2) declare @transaction_description as varchar(50)
declare @debit_amount as decimal (9,2) declare @credit_amount as decimal (9,2) declare @counter as int declare @balance as decimal (9,2) declare @user_changed as char(8)
Declare c2 CURSOR FOR SELECT distinct(loan_no) FROM loan where customer_no = @Customer_No ORDER BY loan_no
OPEN c2 FETCH NEXT FROM c2 INTO @loanno
-- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN
declare c1 CURSOR FOR SELECT transaction_record.loan_no, transaction_record.transaction_date, transaction_record.transaction_type, transaction_record.reference_no, transaction_record.notes, transaction_record.transaction_amount, transaction_type.[description] FROM transaction_record inner join transaction_type on transaction_type.transaction_type = transaction_record.transaction_type where loan_no = @loanno and transaction_Date between @startdate and @enddate and transaction_amount <> 0 ORDER BY transaction_date
OPEN c1 FETCH NEXT FROM c1 INTO @loanno, @transaction_date, @transaction_type, @reference, @notes, @transaction_amount, @transaction_description
-- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN If (@transaction_amount < 0) Begin set @credit_amount = @transaction_amount set @debit_amount = 0 End Else Begin set @debit_amount = @transaction_amount set @credit_amount = 0 End
If (@counter = 0) Begin set @balance = @transaction_amount End Else Begin set @balance = @balance + @transaction_amount End
Having a table with a varchar(10) column, I try to insert a row where the data for that column is more than 10 characters long. No surprise, I get a MSG 8152 error.
What I want is to get the overlong data clipped to the maximum column width, and no error. Is that possible?
I am experiencing some frustration in regards to parsing an email that I need to work with. (I don't have control over the format of the email I am receiving)
Any help would be appreciated. Thank you Jake
Here is the error I get, when I use the SQL Query Analyzer to process my Stored Procedure. (Not every email gets this error, just some, others process just fine.)
String or binary data would be truncated. The statement has been terminated. Stored Procedure: eResponse.dbo.spParser Return Code = -6
My Code is as follows:
CREATE PROCEDURE spParser @_id nvarchar(50) AS --assumes the email is no longer than 4000 characters. if it might be longer, bump this number up. -- Common Varriables DECLARE @body nvarchar(4000) DECLARE @i int DECLARE @phone nvarchar(50) DECLARE @phonestart int DECLARE @phoneend int DECLARE @email nvarchar(50) DECLARE @emailstart int DECLARE @emailend int DECLARE @comments nvarchar(3000) DECLARE @commentsstart int DECLARE @commentsend int DECLARE @OrigID varchar(50) -- Jose Ole Variables DECLARE @firstname nvarchar(50) DECLARE @firstnamestart int DECLARE @firstnameend int DECLARE @lastname nvarchar(50) DECLARE @lastnamestart int DECLARE @lastnameend int DECLARE @address1 nvarchar(50) DECLARE @address1start int DECLARE @address1end int DECLARE @address2 nvarchar(50) DECLARE @address2start int DECLARE @address2end int DECLARE @city nvarchar(50) DECLARE @citystart int DECLARE @cityend int DECLARE @state nvarchar(5) DECLARE @statestart int DECLARE @stateend int DECLARE @zip nvarchar(15) DECLARE @zipstart int DECLARE @zipend int DECLARE @country nvarchar(50) DECLARE @countrystart int DECLARE @countryend int DECLARE @phone2 nvarchar(50) DECLARE @phone2start int DECLARE @phone2end int DECLARE @productname nvarchar(50) DECLARE @productnamestart int DECLARE @productnameend int DECLARE @upccode nvarchar(50) DECLARE @upccodestart int DECLARE @upccodeend int DECLARE @datecode nvarchar(50) DECLARE @datecodestart int DECLARE @datecodeend int DECLARE @purchaseat nvarchar(50) DECLARE @purchaseatstart int DECLARE @purchaseatend int DECLARE @purchasecity nvarchar(50) DECLARE @purchasecitystart int DECLARE @purchasecityend int DECLARE @datepurchased nvarchar(50) DECLARE @datepurchasedstart int DECLARE @datepurchasedend int DECLARE @dateopened nvarchar(50) DECLARE @dateopenedstart int DECLARE @dateopenedend int DECLARE @subject nvarchar(50) DECLARE @subjectstart int DECLARE @subjectend int -- Windosr Variables DECLARE @name nvarchar(50) DECLARE @namestart int DECLARE @nameend int DECLARE @company nvarchar(50) DECLARE @companystart int DECLARE @companyend int
--First replace all ASCII carriage returns and line feeds and assign the whole chunk of data to a variable. SET @body = (select replace( REPLACE(convert(varchar(4000), message),CHAR(10),' '),CHAR(13),'') from message where mailid = @_id) --below is the sample syntax for the individual replacements of line feeds and carriage returns, but it's combined into one statement above. --SELECT REPLACE(@jb,CHAR(10),'') as firstpass --SELECT REPLACE(@jb,CHAR(13),'') as secondpass IF left(@body,47) = 'Fromubject:contact info sent from samplesite.com' --If this is true.. then it is a sample company. BEGIN --The 12 in the start is compensating for the 12 Characters of "First Name: " --Below is collecting First Name SET @firstnamestart = (CHARINDEX('First name:',@body) + 11) SET @firstnameend = (CHARINDEX('Last name:',@body) - 1) SET @firstname = SUBSTRING(@body,@firstnamestart,@firstnameend-@firstnamestart) ----Below is collecting Last Name SET @lastnamestart = (CHARINDEX('Last name:',@body) + 10) SET @lastnameend = (CHARINDEX('Address 1:',@body) - 1) SET @lastname = SUBSTRING(@body,@lastnamestart,@lastnameend-@lastnamestart) --Below is collecting Address1 SET @address1start = (CHARINDEX('Address 1:',@body) + 10) SET @address1end = (CHARINDEX('Address 2:',@body) - 1) SET @address1 = SUBSTRING(@body,@address1start,@address1end-@address1start) --Below is collecting Address2 SET @address2start = (CHARINDEX('Address 2:',@body) + 10) SET @address2end = (CHARINDEX('City:',@body) - 1) SET @address2 = SUBSTRING(@body,@address2start,@address2end-@address2start) --Below is collecting city information SET @citystart = (CHARINDEX('City:',@body) + 5) SET @cityend = (CHARINDEX('State/Prov',@body) -1) SET @city = SUBSTRING(@body,@citystart,@cityend-@citystart) --Below is collecting state information SET @statestart = (CHARINDEX('State/Province:',@body) + 15) SET @stateend = (CHARINDEX('Zip/Postal',@body) -1) SET @state = SUBSTRING(@body,@statestart,@stateend-@statestart) --Below is collecting zip information SET @zipstart = (CHARINDEX('Zip/Postal Code:',@body) + 16) SET @zipend = (CHARINDEX('Country',@body) -1) SET @zip = SUBSTRING(@body,@zipstart,@zipend-@zipstart) --Below is collecting country information SET @countrystart = (CHARINDEX('Country:',@body) + 8) SET @countryend = (CHARINDEX('E-mail address',@body) -1) SET @country = SUBSTRING(@body,@countrystart,@countryend-@countrystart) --Below is collecting email information SET @emailstart = (CHARINDEX('E-mail address:',@body) + 15) SET @emailend = (CHARINDEX('Daytime',@body) -1) SET @email = SUBSTRING(@body,@emailstart,@emailend-@emailstart) --Below is collecting phone information SET @phonestart = (CHARINDEX('Daytime Phone:',@body) + 14) SET @phoneend = (CHARINDEX('Evening Phone:',@body) -1) SET @phone = SUBSTRING(@body,@phonestart,@phoneend-@phonestart) --Below is collecting phone2 information SET @phone2start = (CHARINDEX('Evening Phone:',@body) + 14) SET @phone2end = (CHARINDEX('Product Name',@body) -1) SET @phone2 = SUBSTRING(@body,@phone2start,@phone2end-@phone2start) --Below is collecting Product Name information SET @productnamestart = (CHARINDEX('Product Name:',@body) + 12) SET @productnameend = (CHARINDEX('UPC Code:',@body) -1) SET @productname = SUBSTRING(@body,@productnamestart,@productnameend-@productnamestart) --Below is collecting UPC Code information SET @upccodestart = (CHARINDEX('UPC Code:',@body) + 9) SET @upccodeend = (CHARINDEX('Date Code:',@body) -1) SET @upccode = SUBSTRING(@body,@upccodestart,@upccodeend-@upccodestart) --Below is collecting Date Code information SET @datecodestart = (CHARINDEX('Date Code:',@body) + 10) SET @datecodeend = (CHARINDEX('Purchased At',@body) -1) SET @datecode = SUBSTRING(@body,@datecodestart,@datecodeend-@datecodestart) --Below is collecting Purchase At information SET @purchaseatstart = (CHARINDEX('Purchased At',@body) + 26) SET @purchaseatend = (CHARINDEX('Purchase City:',@body) -1) SET @purchaseat = SUBSTRING(@body,@purchaseatstart,@purchaseatend-@purchaseatstart) --Below is collecting Purchase City information SET @purchasecitystart = (CHARINDEX('Purchase City:',@body) + 14) SET @purchasecityend = (CHARINDEX('Date Purchased',@body) -1) SET @purchasecity = SUBSTRING(@body,@purchasecitystart,@purchasecityend-@purchasecitystart) --Below is collecting Date Purchased information SET @datepurchasedstart = (CHARINDEX('Date Purchased:',@body) + 15) SET @datepurchasedend = (CHARINDEX('Date Opened',@body) -1) SET @datepurchased = SUBSTRING(@body,@datepurchasedstart,@datepurchasedend-@datepurchasedstart) --Below is collecting Date Opened information SET @dateopenedstart = (CHARINDEX('Date Opened:',@body) + 12) SET @dateopenedend = (CHARINDEX('E-mail Subject:',@body) -1) SET @dateopened = SUBSTRING(@body,@dateopenedstart,@dateopenedend-@dateopenedstart) --Below is collecting Email Subject information SET @subjectstart = (CHARINDEX('E-mail Subject:',@body) + 15) SET @subjectend = (CHARINDEX('Comments:',@body) -1) SET @subject = SUBSTRING(@body,@subjectstart,@subjectend-@subjectstart) --Below is collecting message information SET @commentsstart = (CHARINDEX('Comments:',@body) + 10) SET @commentsend = len(@body) SET @comments = SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart) --below is the part that actually writes the data out to the Output table INSERT INTO OutputEmails (FirstName, LastName, Address1, Address2, City, State, Zip, Country, eMail, Phone, Phone2, ProductName, UPCCode, DateCode, PurchaseAt, PurchaseCity, DatePurchased, DateOpened, Subject, Comments, OrigID) SELECT ltrim(SUBSTRING(@body,@firstnamestart,@firstnameend-@firstnamestart)) as FirstName, ltrim(SUBSTRING(@body,@lastnamestart,@lastnameend-@lastnamestart)) as LastName, ltrim(SUBSTRING(@body,@address1start,@address1end-@address1start)) as Address1, ltrim(SUBSTRING(@body,@address2start,@address2end-@address2start)) as Address2, ltrim(SUBSTRING(@body,@citystart,@cityend-@citystart)) as City, ltrim(SUBSTRING(@body,@statestart,@stateend-@statestart)) as State, ltrim(SUBSTRING(@body,@zipstart,@zipend-@zipstart)) as Zip, ltrim(SUBSTRING(@body,@countrystart,@countryend-@countrystart)) as Country, ltrim(SUBSTRING(@body,@emailstart,@emailend-@emailstart)) as eMail, ltrim(SUBSTRING(@body,@phonestart,@phoneend-@phonestart)) as Phone, ltrim(SUBSTRING(@body,@phone2start,@phone2end-@phone2start)) as Phone2, ltrim(SUBSTRING(@body,@productnamestart,@productnameend-@productnamestart)) as ProductName, ltrim(SUBSTRING(@body,@upccodestart,@upccodeend-@upccodestart)) as UPCCode, ltrim(SUBSTRING(@body,@datecodestart,@datecodeend-@datecodestart)) as DateCode, ltrim(SUBSTRING(@body,@purchaseatstart,@purchaseatend-@purchaseatstart)) as PurchaseAt, ltrim(SUBSTRING(@body,@purchasecitystart,@purchasecityend-@purchasecitystart)) as PurchaseCity, ltrim(SUBSTRING(@body,@datepurchasedstart,@datepurchasedend-@datepurchasedstart)) as DatePurchased, ltrim(SUBSTRING(@body,@dateopenedstart,@dateopenedend-@dateopenedstart)) as DateOpened, ltrim(SUBSTRING(@body,@subjectstart,@subjectend-@subjectstart)) as Subject, ltrim(SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart)) as Comments, @_id as OrigID FROM dbo.message WHERE (mailId = @_id) END ELSE BEGIN --below prints the whole value in the debugger (SQL Query Analyzer) --print @body --below prints the length of the whole value --print len(@body) --below prints the location of 'Callers Name:' --Print CHARINDEX('Name: ',@body) --Below is collecting the Name information --The 6 in the start is compensating for the 6 Characters of "Name: " SET @namestart = (CHARINDEX('Name:',@body) + 5) SET @nameend = (CHARINDEX('Email:',@body) - 1) SET @name = SUBSTRING(@body,@namestart,@nameend-@namestart) --Below is collecting email information SET @emailstart = (CHARINDEX('Email:',@body) + 6) SET @emailend = (CHARINDEX('Title:',@body) -1) SET @email = SUBSTRING(@body,@emailstart,@emailend-@emailstart) --Below is collecting Company information SET @companystart = (CHARINDEX('Company:',@body) + 8) SET @companyend = (CHARINDEX('Phone Number:',@body) -1) SET @company = SUBSTRING(@body,@companystart,@companyend-@companystart) --Below is collecting phone information SET @phonestart = (CHARINDEX('Phone Number:',@body) + 13) SET @phoneend = (CHARINDEX('Comments:',@body) -1) SET @phone = SUBSTRING(@body,@phonestart,@phoneend-@phonestart) --Below is collecting message information SET @commentsstart = (CHARINDEX('Comments:',@body) + 9) SET @commentsend = len(@body) SET @comments = SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart) --below is the part that actually writes the data out to the Output table INSERT INTO OutputEmails (Name, eMail, Company, Phone, Comments, OrigID) SELECT ltrim(SUBSTRING(@body,@namestart,@nameend-@namestart)) as Name, ltrim(SUBSTRING(@body,@emailstart,@emailend-@emailstart)) as eMail, ltrim(SUBSTRING(@body,@companystart,@companyend-@companystart)) as Company, ltrim(SUBSTRING(@body,@phonestart,@phoneend-@phonestart)) as Phone, ltrim(SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart)) as Comments, @_id as OrigID FROM dbo.message WHERE (mailId = @_id) END --At the very end when you're satisfied that the data has been processed properly, delete the appropriate message record from the message table DELETE FROM MESSAGE Where message.mailID = @_ID GO
Sample Data that has problems
Fromubject:contact info sent from samplesite.comBody:First name: Sample Last name: Name Address 1: 123 Testing Road Address 2: Don't filled City: Park Forest State/Province: FL Zip/Postal Code: 12345-1234 Country: USA E-mail address: validemail@email.com Daytime Phone: 123-123-1234 Evening Phone: Don't filled Product Name: Sample Product with Cheese UPC Code: 12345-89521 Date Code: 1251237 D PST . 5290 Purchased At (Store Name): StoreName Purchase City: Store City Date Purchased: 12/8/2007 Date Opened: 12/14/2007 E-mail Subject: nail in product Comments: when I purchased your product, there was something in it. Obviously I am not happy about this. Please contact me. Thank you John Q Public
Hi All,I want to catch the next MSSQL error in my SQL code with following continuecalculationsServer: Msg 17, Level 16, State 1, Line 1SQL Server does not exist or access denied.If REMOTE_SERVER_1 is inaccessible (as in (a) below) the executing of SQLwill not continue with (b) - I need the code in (b) to run despite whetherthe previous exec was successful or not - Any ideas?begin transaction(a) exec REMOTE_SERVER_1...bankinsert '1' , '1' , 1 , 0 , 0(b) print @@errorcommit transactionwhere REMOTE_SERVER_1 is link to server created byEXEC sp_addlinkedserver @server = 'REMOTE_SERVER_1', @srvproduct = '',@provider = 'SQLOLEDB', @datasrc = 'MYCOMP1', @catalog = 'mirror2'EXEC sp_addlinkedsrvlogin @rmtsrvname = 'REMOTE_SERVER_1', .....Exec sp_serveroption 'REMOTE_SERVER_1', 'data access', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'rpc', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'rpc out', 'true'Exec sp_serveroption 'REMOTE_SERVER_1', 'collation compatible', 'true'Any help will be greatly appreciated
I would like to supress an Informational error that SQL is returning when I run a stored proc that I created. The error message returned is:
Warning: Null value eliminated from aggregate.
The values returned from the stored proc are the results from a 'select * from #tmp_tbl". Before returning the values, I simply create the temp table, populate it and then run the select statement. Prior to getting my results, I get the error message. Can I suppress it and how?
Hi there, Can anyone help me in catching @@error value. I have a stored procedure which return @@error value, I need to read that in my dataaccesslayer and act according to it. how do I catch the return value from stored procedure in my dataaccesslayer. if I am not wrong @@error return a bool value
dear friends i am writng a store procedure to insert into a two table .table 1 data inserted but when i inserting into a table2 it have some error at that time table 1 data also want to delete.give suggestions
I am writing a stored procedure that loads transaction logs to a database and I am having trouble trapping meaningful error messages. When ever the load fails, it gives me two error messages, the first one is meaningful and the second one just states that the load ended abnormally. Unforunately, when I capture the error using @@error after the load statement, it is the second error message that I am getting.
Is there anyway to trap the first error message as well?
I currently have defined a source server, a transformation, and a destination server using DTS. How and where do I create an error routine that would capture any and all errors that would occur. This would include connection error, transformation errors etc. I know the errors can be written out to a text file but I would like them written to a table on my destination server.
The DTS package will be called from an external program using the xp_cmdshell extended stored procedure. We are using it this way as a flag so if one transformation fails it will return a 1. If all are successful it will return a O. These will be our flags to check the errors table in the destination server.
Could someone tell me where this code is to go and what the code will look like. Samples would be the most help.
is there any way to do sometype of Error handling with in a Stored Proc? Example, If I have a deadlock, can I trap that error and execute other sql code or will always simply kill the stored proc?
I have a composite unique key on the audit_hub table that includes TimeStamp, UID, Type, Mailbox all as part of the unique key.
I’m trying to do inserts, and know that in some cases I will violate unique index.
I’m using stored procedure, and want to handle the error gracefully there – just move on.
Reading from text-based log files via .vbs and get back the nasty popup window.
Looks like I have it right but obviously I don’t considering how it’s (not) working.
I thought error handling would be the simplest way to avoid dupe records. Might be necessary to run the same log in more than once, and only want to add the new records since the last insert session.
Here’s what I have. It obviously simply halts and complains immediately after the insert attempt.
ALTER procedure eml_HubInsert @TimeStamp bigint ,@UID varchar(255) ,@Type varchar(255) ,@MailBox varchar(255) ,@ServerID varchar(50) as insert into audit_hub (TimeStamp,UID,Type,Mailbox,ServerID) values (@TimeStamp,@UID,@Type,@MailBox,@ServerID)
1 question: What Error-Handling msut i mplement in a trigger, who is updating permanently two tables in a database (with the follow commands: insert, delete and update) ???
I have some stored procedures that insert information from a third party plugin that hooks into our database (so I can't do any client side validiation). The problem I am encountering is that some of the fields I use need to be in DateTime format. In order for SQL SERVER 2000 to be able to insert this field, the Date has to be in the correct syntax or an error is thrown.
Is there any way to do something like this in SQL SERVER 2000
Code:
DELCARE @SomeDate DateTime
Try Set @SomeDate = CONVERT(DateTime, Parameter1, 101) Catch return custom error describing what field is formatted wrong and exit stored procedure END
I am running some bcp copies through a Sql job. I am copying 35 tables in individual steps. However, sometimes the bcp step fails to copy the data, and I want the step to fail if the data is not copied properly. Is that possible? If so, how? Any help is greatly appreciated.
I have this stored procedure that I need to add error handling to. How would I do that??
REATE PROCEDURE November2000 AS SELECT TM#, LASTNAME, FIRSTNAME, FINALSUITDONE FROM dbo.EmployeeGamingLicense WHERE (FINALSUITDONE BETWEEN CONVERT(DATETIME, '2000-11-01 00:00:00', 102) AND CONVERT(DATETIME, '2000-11-30 00:00:00', 102))
i wrote this sql, seems v.straightforward to me. It works first time round, sets the foreigh key, but second time round should error out to the handler - but it just reports the errors to the message out and dosn't seem to fire the errhandler.
Its gotta be a simple mistake - perhaps you could show me.
Can i any one help regd error handling while executing stored procedure.
Sample Sequence of execution :
CREATE procedure RBI_Control_sp as begin set nocount on
begin try BEGIN TRANSACTION --Truncating the Table in ramcovm392(fin_ods) exec fin_ods..trun_sp
--Data Transfer From the Live Server to Dw-Server exec fin_ods..RBI_Data_Transfer_sp
insert into fin_wh..Status_report([object_name],row,[date]) select 'Data Tranfer','SUCCESS',getdate()
COMMIT TRANSACTION end try
begin catch <b> [i need to insert the type of error in status report table]</b> rollback transaction insert into fin_wh..Status_report([object_name],row,[date]) select 'Data Tranfer','FAILURE',getdate() end catch
I am calling some stored procedures from ASP. These strored procedures have to deal with lots of deletes and updates. So i have thought of implementing transaction commits and rollbacks. But if a rollback occurs in these stored procedures, i want to get a value back to asp page, based on this value i will run the next stored procedure.
hello. below is a very simple SP i have coded to insert a user into my database. i have created a unique index for the username column on my database table. if the username already i want to convey this to my C# code which is attempting the insert. i am using a combination of TRY/CATCH, RAISERROR and @@ERROR here to achieve my aim. could somebody please look at my code and tell me if the error handling code is OK, or is it overly complicated?
AS
SET NOCOUNT ON
DECLARE @Error int;
BEGIN
BEGIN TRY INSERT INTO Users( Username, Password, FirstName, Surname, DateBirth, Email, Id_Country) VALUES( @Username, @Password, @FirstName, @Surname, @DateBirth, @Email, @Id_Country) END TRY BEGIN CATCH
need example on how to add event handling condetion in a package using ssis. my package runs in a loop. the loop creates a connection to diff servers and runs a dynamic query. i want to add a feature which would let the loop run in the event of connection failure for one server.
I have a stored procedure that inserts a new record into a table. The table it inserts into has a unique constraint that utilizes an identity column. Occassionally, this constraint gets violated due to the fact that the app running my stored procedure isn't the only application inserting into the table. Ideally, we would want to change the design to avoid this scenario but these are legacy applications that will eventually be sunsetted. My question is this; is there a way to handle the unique key constraint violation and prevent an exception being thrown in the app calling the stored proc? I know I can check for the error code and handle it within the stored proc but my app still gets an exception caught by the calling method. Can I prevent this from happening? I want for the insert to attempt again and if successful continue processing and not produce the exception. Any help would be greatly appreciated.
in analysis services when i proccess a cube i got an error that data from the fact table isnt exists in the dimenstion table for example fact table sales has column suplier that one of its row has data that doesnt match with the dimension table how can i handle this error ? can i insert instead someyhing else so that the proccess can continue and not failed ?
Hi, I'm trying to decide on the best method for dealing with errors in a DTS package. It is sufficient to retrieve Step failure information after package execution but I have tried both methods. Specifically, these methods are detailed in http://support.microsoft.com/kb/240221. Another article - http://support.microsoft.com/default.aspx?scid=kb;en-us;321525 - details the need to establish a single event sink to avoid "unexpected behaviour". I have used the code as described but noticed very little difference between post execution checking using GetExecutionErrorInfo and the PackageEventSink Interface. Using the Event Sink does retrieve one extra type of error, ie.
"Error at Destination for Row number 2. Errors encountered so far in this task: 1". This error will always give the row number as the last row in the file, in this case an excel file source. This is not really useful, but I don't want to detach the Event Sink because of the possibility of "unexpected behaviour". Could somebody advise? First is it possible to retrieve the line number, and secondly can anyone detail what an example of this unexpected behaviour might be? Finally, is there a way to retrieve a unique key constraint violation from a package if it occurs? It is only caught in the generalised way and produced as "Error at Destination...". Thanks in advance
Hello, I'm mainly a .net dev and now I want to create a TV function using the CLR inside SQL Server but I have some doubt about the error handling in the function itself and in t-sql.
I have a SP (that I suppose will have a BEGIN TRY ... END TRY BEGIN CATCH ...END CATCH) that call my TV function to get a table made of one row and 2 columns.
My TV function calls some private functions that uses .net classes so I want to handle the exception the potentially could happens.
But these expection shouldn't thrown the error to the caller SP because the SP must continue iven if there is a error but I would to inform somehow the SP that the fields in the table are null because something went wrong
Public Shared Function GetDates(ByVal UTCInputDate As Date) As IEnumerable
Dim StartDate As Date
Dim EndDate As Date
Dim rowArray As ArrayList = New ArrayList
Try
Dim GetDates (1) As Object
GetDates = Function1(UTCInputDate) 'Function that can cause exception directly or indirectly
GetDates (0) = StartDate
GetDates (0) = EndDate
rowArray.Add(GetDates)
Catch ex As Exception
????
End Try
Return rowArray
End Function
Private Shared Sub FillDatesRow(ByVal obj As Object, <Out()> ByRef StartDate As SqlDateTime, <Out()> ByRef EndDate As SqlDateTime)
Try
If obj IsNot Nothing Then
Dim row As Object() = CType(obj, Object())
If row(0) IsNot Nothing Then
StartDate = CType(row(0), DateTime)
Else
StartDate = SqlDateTime.Null
End If
If row(0) IsNot Nothing Then
EndDate = CType(row(1), DateTime)
Else
EndDate = SqlDateTime.Null
End If
Else
StartDate = SqlDateTime.Null
EndDate = SqlDateTime.Null
End If
Catch ex As Exception
Throw
End Try
End Sub
What I have to write in the GetDates' s catch block to avoid that the exception is thrown to the BEGIN CATCH END CATCH of the caller SP but inform anyway the caller??
My flow is build with in the beginning an sql command in an OLE DB source, this command I use to create an incremental update which works faster than an SCD task. Now when I debug the package runs but stops after a short time. One control flow with that sql statements rus perfectly but the other gives an error. The errors I get are:
- [OLE DB Source 1 [2946]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
- [DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source 1" (2946) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
The dataflow task starts with that sql statement where afterwords I do a data conversion and then I store it in with an OLE DB destination in a table.
Now i don't know why one statement works while the other isn't working... Does anyone has an idea?
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
I am building a Bulk Insert statement dynamically passing in filename location, etc.
I have coded error handling via the @@ERROR. To test the error handling, I am forcing the file that is bulk inserted to be missing.
The statement is created in a declared variable, IE @SQL:
EXEC(@SQL)
IF @@ERROR <> 0
BEGIN GOTO ErrorHandler END
Since the file is missing it causes an error, and the Stored Procedure gives the
Server: Msg 4860, Level 16, State 1, Line 1 Could not find the file etc. and aborts aborts the whole stored proc at that point. In other words the error handling IF @@ERROR doesn't trap the error and send the process to the error handler routine.
Is there anyway to get the error back from the EXEC, or maybe something has to be set to trap the error because it is fatal? Because of the way the Bulk Insert statement is created dynamically, it appears you have to EXEC the statement once it is built (EXEC(@SQL)).
Hello to all I am having a SQLDataSource on my web page to connect with database. Now If it generates any error then where to capture that errors ?? Means at which place(which event or any other place) should i write code for handeling errors ???
I want to load a smallint field with values from a varchar field; e.g. CAST(field as SMALLINT). This is in a bulk copy insert so of course there are the few fields that have '1773A' for example and error out the CAST. Anyone know of a way to return 0 (or 1) if CAST errors on conversion ?
This is the test script that's trying to convert 3750 records (an unknown minority of records contain alpha characters):
create table alstest (field1 smallint) insert into alstest (field1) select CAST(conf_number AS SMALLINT) from conference
