My understanding is that in a stored procedure (or any code for that
matter) if an error occurs you can detect it by checking @@error
variable and raise your own error with raiserror statement.
The problem is that the original error is not suppressed. For example
I received the following output from a stored procedure from the same
error:
Server: Msg 547, Level 16, State 1, Procedure spUpdateSecurityMaster,
Line 49
INSERT 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, Procedure
spUpdateSecurityMaster, Line 57
Unable to insert into "SM_mm_Exchange" table
The statement has been terminated.
So why should we bother to use raiseerror if the orginal error is
going to be given to the client anyways? The end result is two error
messages.
Hello guys,I need some ideas on how to handle an exception or a user defined error message.I have a procedure that creates a new user. Lets say if the e-mail address entered is already in use. What are some of the best practices for notifying the user that the e-mail address is already in use?This is what I was thinking....Solution #1--------------My proc will raise an error with a message id that is great than 50000, then my DAL will recognize this is a user defined error and spit back to the user instead of trapping it.Solution #2--------------The proc should have an output param ( @CreationStatus CHAR(1) ).If the @CreationStatus has a value for example "E", I will have lookup the value for "E" in my app and spit back that custom error message. I don't really like this option because it is too concrete.What are some of the ways you deal with this situation?Your suggestions are greatly appreciated.Thank you!
Hi all, We are displaying an SQL Server 2005 Reporting Services report in our ASP.NET 1.1 application by accessing the report through URL and embedding the same in an iframe HTML web control. The user can export the report contents into CSV format by making use of the export functionality provided by Reporting Services. Now we have been asked to display a warning mesage to the user when the no. of records in the report exceeds 1 million i.e. if the no. of records in the report exceeds 1 million and the user tries to export the report to CSV format by clicking on the "Exprot" link button, we need to display a warning message to the user. My doubt is whether this can be done in Reporting Services. Are there any programmatic interfaces exposed by Reporting Services which might help us implement this requirement ?
'...we can attach an event handler to the package...and this one event handler will catch all events raised of that event type by every container in the package...'
Fine. Now what I want to do is catch all OnInformation events generated for the package, and disgard all apart from a subset, whose message contains a particular string. So the Q is, how do I/can I interrogate the message for the event that my handler has just caught?
Michael Coles has come up with a neat method of overriding sp_dts_addlogentry, (http://blogs.sqlservercentral.com/blogs/michael_coles/archive/2007/10/09/3012.aspx), but I want to do this within the package, not outside, to avoid the change becoming global to every package writing to the same sysdtslog90.
i'm using a foreach loop container to read a group of excel files and pass their information to a Sql Server database. The process runs well but sometimes there could be some excel files that may not be processed correctly so i'm using transacctions to continue to process on the other files, But i'd like to generate a message everytime an excel file is or not processed. I thought that i could generate a flat file to do it, but is there any other way to accomplish this? I'm also generating a log file (on xml format), but It seems too much information for an end-user.
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
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
[OLE DB Destination [255]] Error: The "input "OLE DB Destination Input" (268)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (268)" specifies failure on error. An error occurred on the specified object of the specified component.
I've condensed the useful information in the statement down to the following:
"An error occured."
I'd like to also provide a plain english paraphrase.
An error occured somewhere to something. This means that something somewhere didn't work right. The cause of the thing not working right is an error of some sort. We'd like to provide you with the following piece of diagnostic information: we know that an error occured somewhere to something because the error row disposition tells us this. We hope that helps. Thank you, and have a nice day.
Now, could anyone translate this into Klingon? I think it would be easier to understand and just as useful.
Hi, I'm storing errorcode and errorcolumn values in a separate table called errorrows, I am using the standard ssis add-on reporting package including the logging database.
When using the OLE DB destination adapter I would like to log the error message (reason). These kind of messages are OLE DB specific and won't be logged in my standard logging (error rows and package logging).
Is it possible to catch the ole DB error message so I'm able to log it?
Can somebody help me how can I capture the exact error message instead of SystemMessages Ex:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Table1'.
Instead of
Invalid object name '%.*ls'.
I tried using Try Catch but no use
Ex:
BEGIN TRY
select * from dbo.Table1
END TRY
BEGIN CATCH
INSERT INTO dbo.ErrorLog values (ERROR_MESSAGE() )
END CATCH
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Table1'.
Strangely it does work if i try to use some other query.
Ex:
BEGIN TRY
Truncate Table dbo.NoPlayEvent_3
END TRY
BEGIN CATCH
INSERT INTO dbo.ErrorLog values (ERROR_MESSAGE() )
END CATCH
(1 row(s) affected)
REQUIREMENT:
To write a query which will try to do some stuff ( Insert/ Update/ delte/ truncate/ select etc..). If its not able to do and if an error is raised capture the error message in a table and with out quiting the SPROC skip it and go for next line of code. i.e, if an error occurs just skip it while capturing the error and go for next line.
Also advice me how can I do muliple Try Catch
I appreciate your all time Thanks a lot in advance
Could not load file or assembly 'Microsoft.SqlServer.Express.BatchParser, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. Strong name validation failed. (Exception from HRESULT: 0x8013141A) (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
Strong name validation failed. (Exception from HRESULT: 0x8013141A)
------------------------------ BUTTONS:
OK ------------------------------
I just opened CMD. and did the following
Command Prompt
cd D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinn sqlservr.exe -s
and it returned the following
Error : 17112, Severity : 16, State: 1. The error is printed in terse mode because there was error durring formatting. Tracing, ETW, Notifications etc are skipped.
I have translated my ReportViewer by setting myReportViewer.Messages to my own implementation. This helps me with the translation of most of the interface. However, errormessages such as "The value provided for the report parameter 'SomeDateParameter' is not valid for it's type" is not translated. Is there any way to fix the translation of such error messages?
for ex. In a table I am adding a row which is voilating primary key constarint ..I do not want system message for that ..
Server: Msg 2627, Level 14, State 1, Line 0 Violation of PRIMARY KEY constraint 'PK_b'. Cannot insert duplicate key in object 'b'. The statement has been terminated.
I can write a user defined message for this purpose but I am not able to supress system message .
The procedure I am using is
CREATE PROCEDURE add_b @num int,@empno int, @name varchar(6) AS INSERT INTO B (num, empno, ds ) values (@num,@empno,@name) -- Test the error value. IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
PRINT "An error occurred loading the new author information"
RETURN(99)
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT "The new author information has been loaded"
I'm using a unique index on a table with ignore_dup_key to get around using distinct in a stored procedure. In Query analyser, when I issue the stored procedure, you get a message and also the data in the Grid. In a stored procedure call, initiated from MS Access, the message means I have to put additional client side programming to work around it. I know that duplicate keys are ignored; that is why I've created the table with this feature (unique index in combination with ignore_dup_key). So what's with the message. If I delete the Error message 3604 will I still get an error message?
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.
