My Error Handler Writes Duplicate Messages To Email!
Aug 22, 2007
I am using an error handler that was provided to me from another source. However, I notice that there's something in the code that writes the error message twice. I tried to discover what it was, but could not seem to pinpoint it. Here's an example of what my email messages look like:
Is activity file current?
The Script returned a failure result.
The extracts in D:myFolder are not current! Data NOT loaded.
Is activity file current?
The Script returned a failure result.
The extracts in D:myFolder are not current! Data NOT loaded.
Obviously, I just want my email to read:
Is activity file current?
The Script returned a failure result.
The extracts in D:myFolder are not current! Data NOT loaded.
Somewhere, errorMessages is being written to more than once. Need help finding the error.
I need to build an automated email that gives the completion messageswhen a database is restored (i.e. "Executed as user: sa. ExecutingRESTORE DATABASE DB1 FROMDISK='h:ackupsDB1DB1_db_200411082056.BAK', RECOVERY [SQLSTATE01000] (Message 0) Processed 3816 pages for database 'DB1', file'DB1_Data' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1pages for database 'DB1', file 'DB1_Log' on file 1. [SQLSTATE 01000](Message 4035)")Currently, the Job History box contains it, but I'd rather get it viaemail. The base restore statement works, but it gives me a "Cannotperform a backup or restore operation within a transaction." when Itry to run it as below.works:[build @RestoreCmd]exec (@RestoreCmd)doesn't:[build @RestoreCmd]create table #Error_Finder (listing nvarchar (4000))declare @Errors smallintinsert #Error_Finder exec (@RestoreCmd)EXEC xp_sendmail @recipients = 'dba',@query = 'SELECT * from #Error_Finder',@subject = 'SQL Server Restores'drop table #Error_FinderAny suggestions? My next thought is to start selecting against systemtables in msdb. It looks because the Insert can fail, it's atransaction.
Running sql server 2000 sp4 on an 8 year old Server and a shaky SAN. We have had 3 major outages (corrupted databases) and they are finally biting the bullet and ordering a new Server. The new server will not be in place for about 9 days so until then... limping along with what we have. Is there any way to have SQL Server send an email alert when a specific error occurrs and is logged in the SQL log? These are the two errors that I want to alert on.. "Error: 823, Severity: 24, State: 2" or "I/O error (bad page ID) detected during read at offset"
how do I set up a SQL2000 server to send an alert to both an email address and a pager when a specific event occurs..please help!!! The server is in a different location and we need notification of problems!! thanks
2 questions: 1) if I place an Execute SQL Task in OnError event of the Error Handler at the Package level, will it catch all of my possible task errors? There's no need to add an Event Handler for each task in the Control flow?
2) A stupid one...I'd like to test my Event Handler (writing to custom log table in case there's an error in the Integration Service)...any ideas how to provoke an SSIS error to check my Error Handler ?
I added this line of code to my error handler script, in red.
User:criptError is a package-level string variable User:criptError has a value assigned to it when another script encounters an error condition (which I define) I added "User:criptError" as a read-only variable to the error handler script task.
Now, when I run the package, and an it encounters an error, it task just hangs, that is, it stays yellow instead of turning red... if I remove the line in red above, it works ok again.
is there an error handler in sql stored procedures? For example if i want to do a drop something and there is a lock, i want to try return if it droped it or not and if not then try again later
I need to develop code for a TRY...CATCH block in SQL Server 2008 R2 for the following error, that occurs when I get a timeout from a linked server that connects to a server in another country:
OLE DB provider "ORAOLEDB.Oracle" for linked server "MyRemoteServer" returned message "ORA-12170: TNS:Connect timeout occurred". Msg 7303, Level 16, State 1, Line 77 Cannot initialize the data source object of OLE DB provider "ORAOLEDB.Oracle" for linked server "MyRemoteServer".
Most times, it doesn't time out, but occasionally gives me a 7303 error. I can't control when timeouts happen and when they don't. I can't use RAISERROR on error 7303, as it only works on custom errors >= 50000.
My question is, how do I simulate a timeout so that I can verify my error handling code? I need a procedure that waits 10 seconds and tries again 3 times, but how do I generate the error?
i give a small fuzzle for you here , ie . i have a job which includes 6 packages in that one and while running that job any one package is fialed how can i execute that next package even that before package was failed. i am using for loop container for running my 6 packages ... is there any method to do this like on error resume next in .net
I am using the Import/Export wizard to import data from an ODBC data source. This can only be done from a query to specify the data to transfer.
When I try to create the tables, for the query, I am getting the following error:
Msg 2714, Level 16, State 4, Line 12
There is already an object named 'UserID' in the database.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
I have duplicated this error with the following script:
USE [testing]
IF OBJECT_ID ('[testing].[dbo].[users1]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users1]
CREATE TABLE [testing].[dbo].[users1] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users2]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users2]
CREATE TABLE [testing].[dbo].[users2] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
IF OBJECT_ID ('[testing].[dbo].[users3]', 'U') IS NOT NULL
DROP TABLE [testing].[dbo].[users3]
CREATE TABLE [testing].[dbo].[users3] (
[UserID] bigint NOT NULL,
[Name] nvarchar(25) NULL,
CONSTRAINT [UserID] PRIMARY KEY (UserID)
)
I have searched the "2714 duplicate error msg," but have found references to duplicate table names, rather than multiple field names or column name duplicate errors, within a database.
I think that the schema is only allowing a single UserID primary key.
Is there any differene between on-error event handler and precedence constrain failure? I have created a package and if a data flow task(flat file to DB) fails, the file has to be moved to archive folder. How I have accomplished this is Dataflow task->precedence constrain failed(red arrow)->execute process task to move the file to error folder and it worked,The same execute process task( to move the file to error folder) doesnot work when I move this to on-error event handler. Also, for the same file the on-error event is getting triggered multiple times.
DB-Library Error 10007: General SQL Server error: Check messages fromthe SQLServer.CREATE PROCEDURE [dbo].[spu_Import_Export_Image](@srvr varchar(50),@db varchar(50),@usr varchar(15),@pwd varchar(50),@tbl varchar(50),@col varchar(50),@mod varchar(1),@imgpath1 varchar(1000),@pk varchar(50))ASBEGINdeclare @path varchar(50)declare @whr varchar(200)declare @fil varchar(100)declare @cmd varchar(1000)declare @imgpath varchar(800)declare @ext varchar(5)--declare @pk varchar(50)declare @KeyValue varchar(8000)declare @image varchar(50)--declare @imgpath1 varchar(1000)declare @imgpath2 varchar(1000)declare @sellist varchar(2000)set @path = 'c: extCopy.exe'select @sellist = 'DECLARE curKey CURSOR FOR SELECT ' + @pk +' FROM '+ @tbl + ' ORDER BY ' + @pkexec (@sellist)OPEN curKeyFETCH NEXT FROM curKey INTO @KeyValueWHILE (@@fetch_status = 0)BEGINset @whr = '"where '+ @pk +' = "' + @KeyValueset @fil = @imgpath1 + '' + @KeyValue --+ @extset @cmd = @path + ' /S ' + @srvr + ' /D ' + @db + ' /U ' + @usr+ ' /P ' + @pwd+ ' /T ' + @tbl + ' /C ' + @col + ' /W ' + @whr + '/F ' + @fil+ ' /' + @modexec Master..xp_cmdShell @cmdFETCH NEXT FROM curKey INTO @KeyValueENDCLOSE curKeyDEALLOCATE curKeyENDGOAbove srcipt runs fine with image data type in one table but when irun for some other table it gives me Error MessageTEXTCOPY Version 1.0DB-Library version 8.00.194SQL Server 'WSQL01' Message 170: Line 1: Incorrect syntax near '99'.(Concerning line 1)DB-Library Error 10007: General SQL Server error: Check messages fromthe SQLServer.ERROR: Could not use database 'test1'NULL-----------Aslo it only runs on server console if i run it from workstation uingsame files and tables it gives me an error again. Can anybody help meand reply me at Join Bytes! asap.thnx,dharmesh
I am writing a tracking system. There is a table in the Sql Server 2000 database that contains a column for the user's ntid, the page they visited, the date of the last visit, a column each to track hits for the current year and a previous year column (basically for archiveing and reporting purposes), and 12 columns for hits per month (obviously, one per column). To record a hit, my unit determined we would only track one hit per day, so basically, there are 3 possible outcomes I needed to account for : 1) A user had never hit the page before, so I need to record the user's ID, the page they hit for the first time (since it won't exist yet), increment the year counter for that user on that page, and then determine what month column counter should be incremented as well. 2) A user had hit the page before, but not on this same day, so I need to update the row for that user on that page, changing the last visit field to reflect the current date, and icnrementing the appropriate counters. 3) A user had hit the page already on the same day, so basically, nothing should be changed whatsoever. No action should be taken. I wrote a stored procedure to attempt to accomplish that logic, and though it's probably not very pretty, I was surprised at how few errors I got on my first Syntax check. Here's the stored procedure : CREATE PROCEDURE sp_hitMe@ntid varchar(10),@page varchar(50),@thisHit datetimeASSET NOCOUNT ON DECLARE @tempDate datetimeDECLARE @yearCount intDECLARE @monthCount intDECLARE @inMonth varchar(20)DECLARE @monthColumn varchar(10)SET @inMonth = DATENAME(mm, @thisHit)SET @monthColumn = CASE WHEN @inMonth = 'January' THEN 'hitsInJan' WHEN @inMonth = 'February' THEN 'hitsInFeb' WHEN @inMonth = 'March' THEN 'hitsInMar' WHEN @inMonth = 'April' THEN 'hitsInApr' WHEN @inMonth = 'May' THEN 'hitsInMay' WHEN @inMonth = 'June' THEN 'hitsInJun' WHEN @inMonth = 'July' THEN 'hitsInJul' WHEN @inMonth = 'August' THEN 'hitsInAug' WHEN @inMonth = 'September' THEN 'hitsInSep' WHEN @inMonth = 'October' THEN 'hitsInOct' WHEN @inMonth = 'November' THEN 'hitsInNov' WHEN @inMonth = 'December' THEN 'hitsInDec' END DECLARE @insString varchar(500)DECLARE @updString varchar(500)SET @insString = 'INSERT INTO tblTracking (ntid, page, lastVisit, hitsThisYear, ' + @monthColumn + ') VALUES (' + @ntid + ', ' + @page + ', ' + @thisHit + ', 1, 1)' if exists(select * from tblTracking where ntid = @ntid and @page = page) begin if exists(select * from tblTracking where lastVisit = @thisHit) begin -- DO NOTHING! end else begin DECLARE @theColumn varchar (100) SET @theColumn = 'SELECT ' + @monthColumn + ' FROM tblTracking WHERE ntid = @ntid AND @page = page' SET @yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @ntid AND @page = page) + 1 SET @monthCount = (Exec @theColumn) SET @monthCount = @monthCount + 1 SET @updString = 'UPDATE tblTracking SET lastVisit = ' + @thisHit + ', hitsThisYear = ' + @yearCount + ', ' + @monthColumn + ' = ' + @monthCount + ' WHERE ntid = @ntid AND @page = page' Exec @updString end endelse begin Exec @insString endGO And to my surprise, the only 3 errors I got were : Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 39Incorrect syntax near the keyword 'end'.Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 45Incorrect syntax near the keyword 'Exec'.Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 50Incorrect syntax near the keyword 'end'. However, these are of course so vague as to be useless to me. What's wrong with the procedure? What have I missed?
Hello,I'd like to know if I use DTS. If I use it immediately, I can see the errorin the dialog box, instead, if I use it with scheduling, where Can I checkthe error ?ThanksSaimon(Florence)
Greetings I'm learning SSIS and BIDS. I have extreme difficulty making sense of the error messages that come out.
First of all, what do the numbers mean? Each column, error, etc. is assigned a number that obviously means something yet I cannot relate them to anything. For example: The output column Name (713) on output Test (15) and Component (15) -- My table doesn't have 713 columns in it...
Then there are the error codes that obviously contain something useful. For example:
DTS Error: Microsoft.SqlServer.Dts.Runtime.TaskHost/QueueFuzzyName [33]SIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (46)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (46)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Where would I look up: DTS_E_INDUCEDTRANSFORMFAILUREONERROR and 0xC020907D? I understand that it tried to convert a value in something numbered 46 (no idea what that is) and the conversion failed. But that's it. How do I transmogrify 46 to something I can look at. I'm a little fuzzy on what a Destination Input is. Isn't an output a destination?
Or this one:
Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors" Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
I have absolutely no idea what this means except that many things happened, one of them failed, I have an error of some sort and it's error code is 0x80040E21. It must be important, it's in there twice, but what does it mean?
I'm thinking that in the help somewhere all these error messages are listed and I can look them up, but I can't seem to find it anywhere. Am I supposed to be converting them to decimal first?
Any advice from you experts would be much appreciated.
I'm using SqlBulkCopy. Does anyone know how I can output what row (its column names) are throwing a duplicate primary key message when I bulkCopy.WriteToServer(datatable1)?Thanks
Have this dynamic sql statement that I'm working on. It works fine outside the execute (running the query by iteself) and prints fine but when I execute it, I get errors. Spacing is good. Here is the SQL statement. set @sql = 'insert into #participantInfo (strfirstname,strlastname,strindividualfk,strusername,dtelastlogin,blninactive,fk_intrightid,imgPhoto, stre_mail,strmiddleinitial,straddress1,straddress2,strcity,fk_intlocationid,strpostalcode,strhomephone,strbusinessphone,strmiscinfo1, strmiscinfo2,strsecretquestion,dteDateOfBirth,intgender,strsecretanswer) select p.strfirstname,p.strlastname,p.strindividualfk,l.strusername,l.dtelastlogin,p.blninactive,r.fk_intrightid,p.imgPhoto, p.stre_mail,p.strmiddleinitial,p.straddress1,p.straddress2,p.strcity,p.fk_intlocationid,p.strpostalcode,p.strhomephone,p.strbusinessphone, p.strmiscinfo1,p.strmiscinfo2,l.strsecretquestion,p.dteDateOfBirth,p.intgender,l.strsecretanswer from tblparticipants p inner join tblparticipantrights r on p.strindividualfk = r.strindividualfk inner join tblparticipantlogin l on p.strindividualfk = l.strindividualfk where p.fk_strsubgroupid = ''' + @strsubgroupid + '''' exec (@sql) Error messages are: Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'tblparticipants'.Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'tblparticipantrights'.Server: Msg 208, Level 16, State 1, Line 1Invalid object name 'tblparticipantlogin'. Anyone see what may be the cause? thanks ^_^
So far I have not been able to figure out how to get more info regarding the sql server errors. For example, I get this error from sql server query analyzer (this just an example):
----------- Server: Msg 128, Level 15, State 1, Line 5 The name 'does' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted. -----------
Is there a way to find more information regarding this error, or like Oracle has MeataLink, where you can search, if someone else already got this error and how it got resolved?
Or how you folks go about resolving errors you get? Is it based on trail and error, or experience (like already encountered this type before) etc.?
I was wondering if anybody knows where to get a complete list of SQL Server error messages. I am writing a stored procedure that scans SQL Server Logs for errors and if there are errors in the logs, I get paged.
How can I avoid certain messages from SQL Server being recorded into the Event viewer ? For example, every time I truncate the transaction log with 'Backup log with truncate_only', It is being recorded into the Event viewer as an Error. But, I know that it is not an error. How can I avoid this ?
I have a SS2K5 stored procedure that executes 2 others stored procedures sp_zero1 and sp_zero2 sp_zero1 and sp_zero2 do the same thing ... raises an Divide by zero error I need to (print / select into a database) both error messages using just one try catch block instead of 2 blocks like in the next example:
-- THIS IS THE WORKING CODE THAT I DONT WANT BEGIN TRY exec sp_zero1 END TRY BEGIN CATCH PRINT ERROR_MESSAGE() PRINT ERROR_PROCEDURE() END CATCH BEGIN TRY exec sp_zero2 END TRY BEGIN CATCH print ERROR_MESSAGE() print ERROR_PROCEDURE() END CATCH
if I try the next code
--THIS IS THE NON WORKING CODE BEGIN TRY exec sp_zero1 exec sp_zero2 END TRY BEGIN CATCH print ERROR_MESSAGE() print ERROR_PROCEDURE() END CATCH
only the first error message is printed and the execution is stopped
This is a generic example ... in reality I have a stored procedure that executes tens and hundreds of other stored procedures ... so thats the reason I need just one block of try catch instead of hundreds of blocks
My understanding is that in a stored procedure (or any code for thatmatter) if an error occurs you can detect it by checking @@errorvariable and raise your own error with raiserror statement.The problem is that the original error is not suppressed. For exampleI received the following output from a stored procedure from the sameerror:Server: Msg 547, Level 16, State 1, Procedure spUpdateSecurityMaster,Line 49INSERT statement conflicted with COLUMN FOREIGN KEY constraint'FK_SM_mm_Exchange_Exchanges'. The conflict occurred in database'Trading', table 'Exchanges', column 'IsoCode'.Server: Msg 50000, Level 14, State 1, ProcedurespUpdateSecurityMaster, Line 57Unable to insert into "SM_mm_Exchange" tableThe statement has been terminated.So why should we bother to use raiseerror if the orginal error isgoing to be given to the client anyways? The end result is two errormessages.
Hi everybody,I need to trap error messages in my stored procedures and log them. I canuse @@ERROR global variable to get the error code and look it up insysmessages table to get the description. Then using xp_logevent I log theerror.The problem is this description needs to be formatted. For example if I tryto insert NULL into a column which is not nullable, I'll get error #515. Thedescription of error #515 in sysmessages is:Cannot insert the value NULL into column '%.*ls', table '%.*ls'; columndoes not allow nulls. %ls fails.Is there a way to get the formatted message? What is the best approach totrap errors, filter them, add some additional information to the message andsend it to server's event logger?TIA,Shervin
Hi everyone,How do I get the error message?I have a very long sproc that needs to be done in one transaction. Ihave an error happening somewhere in the middle, but with a low enoughseverity it doesn't terminate the procedure. To make sure I don'tmiss any errors, I am storing @@error after every statement:If @Error<=@@error Set @Error=@@errorthat way at the end I can say if @error<>0 rollback trans.How do I get the error message? I have the number, and what I getfrom sysmessages has the wildcards in it %d and so on.Also , I can't use Xact_Abort, the web user permissions don't allowit.Or better yet is there a better way to do this? Sql has@@total_errors - since the server was started, how about since thetransaction or the sproc was started.Thanks a tonPachydermitis
