Capturing First Error Message

Hi All,

For the following code, I am getting two difference errors. I need to
capture the first error message. Please advise how to capture the
first error message. I need to log this info in Error_Log table.

CODE
=====

drop table authors
go
CREATE TABLE Authors (
AuthorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)
GO

ALTER TABLE Authors
ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
GO

print @@error

================

Thanks in Advance,
Guru
0
Guru
1/21/2010 8:10:51 AM
sqlserver.programming 1873 articles. 0 followers. Follow

3 Replies
568 Views

Similar Articles

[PageSpeed] 15

Hi
Can you use BEGIN ..TRY ..BEGIN CATCH ... to capture the errors?

"Guru" <guruprasathb@gmail.com> wrote in message 
news:d0729226-30bd-499a-86e5-76f1793e6b23@f12g2000yqn.googlegroups.com...
> Hi All,
>
> For the following code, I am getting two difference errors. I need to
> capture the first error message. Please advise how to capture the
> first error message. I need to log this info in Error_Log table.
>
> CODE
> =====
>
> drop table authors
> go
> CREATE TABLE Authors (
> AuthorID INT NOT NULL PRIMARY KEY,
> Name VARCHAR(100) NOT NULL
> )
> GO
>
> ALTER TABLE Authors
> ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
> GO
>
> print @@error
>
> ================
>
> Thanks in Advance,
> Guru 


0
Uri
1/21/2010 9:03:27 AM
Guru (guruprasathb@gmail.com) writes:
> For the following code, I am getting two difference errors. I need to
> capture the first error message. Please advise how to capture the
> first error message. I need to log this info in Error_Log table.
> 
> CODE
>=====
> 
> drop table authors
> go
> CREATE TABLE Authors (
> AuthorID INT NOT NULL PRIMARY KEY,
> Name VARCHAR(100) NOT NULL
> )
> GO
> 
> ALTER TABLE Authors
> ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
> GO
> 
> print @@error

BEGIN TRY
   ALTER TABLE Authors
   ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
END TRY
BEGIN CATCH
   SELECT error_message(), error_severity(), error_number()
END CATCH   

This requires SQL 2005. If you are on SQL 2000, you have two options: 1) 
have a client that caputers the error. 2) Upgrade to SQL 2005/2008.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
1/21/2010 11:26:22 AM
I think TRY CATCH gets only the second error as well. The only solution I have used is using client side code. Plus this 
error you cannot catch at the same level of execution, it has to be at a higher level, like this:

BEGIN TRY
    EXEC('ALTER TABLE Authors
          ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)');
END TRY
BEGIN CATCH
    SELECT error_message(), error_severity(), error_number();
END CATCH

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
1/21/2010 3:01:10 PM
Reply:

Similar Artilces:

Could not synchronise record error.
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange I'm running Entourage Web Services Edition, and syncing with Exchange 2008. It all appears to be working, but I keep getting an error message: <br><br>--- <br> Could not synchronize record: Returned mail: User unknown to Exchange server: Exchange <br> --- <br><br>It registeres in the log about every half an hour, even though it's set to send/receive every 5 mins. <br><br>Has anyone else struck this? Anyone know the cause/solution? ...

cannot read text message from incoming mail
I cannot read the incoming mail's text message. I only can read it when I press "reply", then i can read all my messages. ...

email capture
I'm having a problem with Outlook not pulling the mail off my pop server. When I do a send and receive the mail is identified, "grabs" the messages like it's doing a download but when it reaches the end I'm getting an unknown error message. Any thoughts? 1) First, try to set up Outlook Express to download the email set. Sometimes it has better luck. 2) Another way is to clear the queue manually. This is technical, and not really recommended by anyone, but you may have a stuck message... Telnet to get it... (this method permanently deletes messages before you c...

Message Tracking Center #4
I'm looking at several messages from a specific domain in the message tracking center, and the inbound messages are listed. However, when I highlight them and the mtc performs a transaction search it displays the message: "Unknown event with : x.400 event no. 1039. The messages are NOT delivered to the mailbox store, and that message is all that is present. I queried this error, and mostly came up with replication issues, not performing any replication here. Does anyone know why this is occuring? TIA ...

Median of Even Set; How to Capture them?
Hi everyone, Say I have a dat set such as: 4 5 6 7 The median is (5+6)/2=5.5! However, I am not interested in the answer here, but the 5 and the 6!!! Is there an excel function or way to do so? Thanks alot, Mike Do you want the output in one single cell or in 2 separate cells? Mike Wrote: > Hi everyone, > > Say I have a dat set such as: > > 4 > 5 > 6 > 7 > > The median is (5+6)/2=5.5! > > However, I am not interested in the answer here, but the 5 and the > 6!!! > > Is there an excel function or way to do so? > > Thanks alot, ...

how can i start using excel for the first time?
i cant figure out how to get excel to work for me and im a first time user of it? i Cant get nothing to work on it? can anyone please help me here Paul Can you get Excel to start up? Can you get a blank workbook to open via File>New? For basics on Excel see.......... http://www.usd.edu/trio/tut/excel/index.html http://www.baycongroup.com/el0.htm Microsoft Training Courses. http://office.microsoft.com/en-us/training/CR061831141033.aspx Gord Dibben Excel MVP On Wed, 15 Dec 2004 15:39:02 -0800, "Paul Scheffer" <Paul Scheffer@discussions.microsoft.com> wrote: >i ...

error during install
When trying to load software from Verizon and logged on as administrator, the following error message appears "Install shield engine (ikernel.exe) could not be launched". Software has loaded successfully on other machines. Any suggestions? Address this question to the appropriate Windows NG. -- Brandon IT Director Office Equipment & Supplies at http://www.presentationsdirect.com "Bill Sims" <simsbill@hotmail.com> wrote in message news:037501c35601$84cb0700$a101280a@phx.gbl... > When trying to load software from Verizon and logged on as > administra...

Microsoft Visual C++ Runtime Library Error Message
I have a user who was in Outlook, and upon exiting, he would get the Microsoft Visual C++ Runtime Library Dialog box that pops up, and it says "C:\Program....outlook.exe. Abnormal Program Termination." Any ideas on what could be causing this to come up. How do I fix it? Please help! ...

Capture user selection of font
If I put a font combo box on a Ribbonbar is there a way for me to capture the user's font choice? I need to capture that choice and use it in a macro. Whate *exactly* are you trying to achieve with your macro? -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "dplaut"...

Change Data Capture: Preserve capture instance data when adding co
When a new column is added to table that is configured for change data capture (cdc), the capture instance table will not have the new column until cdc is disabled and re-enabled for the source table. In the process the existing capture instance is dropped. I thought I could copy existing data out to a temp table and then copy back using the following SQL. However, other CDC meta information, such as the cdc.change_tables.start_lsn, becomes invalid. How can the capture instance history be preserved, using the same capture instance name, if at all? Thanks, Rich /*...

DLL Locking Window Paints/Messages
Hi, I am currently in the process of converting code for a dll wrote in Delphi, to a dll written in C++, and am experiencing a strange problem. I have pretty much completed the task, and all the functionality is identically replicated in most respects and works correctly. The problem comes when doing a large transfer over a COM port. On the delphi dll, RS232 communications are handled by a bought component, but, even though an event driven system is available, the previous developer has chosen the method of a tight loop waiting for incoming bytes and sending out bytes, which 'maxes out&#...

error message #5
installed publisher and when i try to start it, i get the following message: MSPUB caused an invalid page fault in module KERNEL32.DLL at 017f:bff87ed3. Registers: EAX=008afe28 CS=017f EIP=bff87ed3 EFLGS=00010207 EBX=008afe28 SS=0187 ESP=007b0000 EBP=007b0130 ECX=007b01b4 DS=0187 ESI=81747bd8 FS=114f EDX=bff76855 ES=0187 EDI=007b01dc GS=0000 Bytes at CS:EIP: 55 a1 e0 9c fc bf 8b ec 83 ec 6c 53 56 57 8b 30 Stack dump: ffecbad7 007b0030 007b0014 00000006 00000006 c000001d 00000000 00000000 00000017 00000000 c1631b00 cce42f70 0001001f 00000000 00000000 00000000 what do i need to do? What ...

Errors Configuring Outlook to POP3 Server
I am trying to configure my Outlook to a POP3 server. When I send a message to my Outlook, this is the error message I am receiving: Unable to logon on to server using Distributed Password Authentication. Server response: ERR authorization first. Does anyone have an idea what I am doing wrong. Does the server support Distributed Password Authentication? If you are not sure, try removing that check mark and then try again. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be...

#NAME? Error on Entire Worksheet
I have an excel worksheet that I use for scheduling. One worksheet i pretty much all formulas that look like, =IF(BK193<>"",IF(K193<>"",IF(BK193<>"",WORKDAY(BK193,-K193,Holidays),""),""),"") where WORKDAY and Holidays are defined on another worksheet within th same workbook. This morning I opened the workbook and every single cell with a formul was outputting the #NAME? error. Any ideas on how I can fix this or even where to begin looking? I hav exhausted the excel help options. Thanks, Denis -- Message po...

Moving in a column by first letter of data in cells
Hi to all , I have a large worksheet (only a few thousand rows...) which makes it really cumbersome to move/scroll. I want to move around in one column by the first letter of word to enter data in the other columns (The same way we scroll in contacts of mobile phone by their first letter). I have sorted it alphabetically but still it's slow to move/scroll due to some codes and pivottables present in my workbook which have made my workbook quite sizeable. Regards. Say your names are in column A and that column B is available. In B1 enter: =LEFT(A2,1) and copy down. If then then...

You are not authorized to view this page error...
You are not authorized to view this page error occurs when i try to access CRM 1.2 from the client pc using authenticated users. can anyone let me know what might have caused the error Have you added the crm home page to your list of trusted sites in ie? "Rich Reynolds" wrote: > Have you added the crm home page to your list of trusted sites in ie? Rich, Thanks for the reply. well i have added CRM home page to the list of trusted site but it didn't solve the above error, also i have given access to all users for the same page, still the same problem. CRM works well...

First spreadsheet... forumla help
OK so I've got a big chart of lots of numbers and I need to apply some formulas to them so I can get what I need from them. Here's a sample of some of them. What I need from this particular list is 6(A) + (B/10) - 2(C) + (D/10) + 6(E). A B C D E 17 1335 0 441 1 14 892 2 187 1 16 1696 0 170 4 13 1128 2 169 0 9 1548 0 483 0 13 1518 2 578 2 9 1074 4 228 0 3 812 0 703 6 12 1635 0 103 1 7 819 3 109 0 13 1188 4 588 1 5 1315 4 235 2 5 1133 1 180 1 7 1006 0 116 0 12 1454 4 84 0 7 1163 0 275 1 2 479 0 112 0 4 673 0 189 0 12 1697 0 245 ...

Create a custom mailbox limit quota message for Exchange v5.5
How can we modify (add our own verbiage) the mailbox limit quota message for Exchange v5.5 Thanks, Rich "Rich Robbins" <r.robbins@cdcixis-na.com> wrote in news:0edb01c4a0d9 $972eca70$a401280a@phx.gbl: > How can we modify (add our own verbiage) the > mailbox limit quota message for Exchange v5.5 You can't do this easily. You can contact Microsoft Consulting and they will do it for a hefty fee... Regards, -- Arlo Clizer Exchange MVP FAQ: http://www.exchangefaq.org Archives: http://groups.google.com ...

Lame Excel XP Startup Error
I'm having a problem with Excel on one of my users machines. Wheneve she opens Excel XP, Visual Basic opens and gives an error. I'v attached a picture showing the exact error. She can just hit OK an close VB, and everything in Excel appears to work okay. This reall isn't a big deal, it's just a PITA and she keeps bugging me about it. Any ideas would be greatly appreciated -- Message posted from http://www.ExcelForum.com Here is the error jpeg. Attachment filename: excel error.jpg Download attachment: http://www.excelforum.com/attachment.php?post...

query syntax error
SELECT [EventsID], [EventName] FROM Events WHERE (([Catagory]=(Event Catagory[EvtCatID]))); The above is what I have in my query builder Events is a fld Datatype autonumber EventName is a fld Datatype text FROM Events iis the Table WHERE Cataory is a fld datatype Number Event Catagory is a tbl EvtDCatID is a fld Datatype autonumber The fld the result is to go in is ChurchService Datatype number Properties combobox tbl query rowsource is the above query builder bound column 1 column count 2 column width 0",1" Iget a syntax error with the where...

Cannot view messages
Scenario: Upgrade of legal office hw/sw from Windows 95/98 Pentium I and Pentium II machines using Outlook Express 5/6 with NT 4.0 server, to Windows XP Pro Pentium 4 machines using Outlook 2000 with SBS 2003 server running Exchange 2003. To xfer each person's email messages from old pc to new pc I copied their .wab and .dbx files to a folder on the new server. Then after joining their new pc to the new domain I ran Outlook Express 6 on the new pc, imported the address and message files into it. Then I ran Outlook 2000, imported the addresses, messages, and account settings f...

How can I dowload messages from the office??
Hi everyone: Here is my situation, I'm using Exchange 2000 from my SBS 2000. We have a company that hosts our domain name, website/emails and we use exchange in the office to download the messages using the pop3 connector from exchange and the SMPT to send mesages withing/outside the office. The company here wanted to keep the external service as a backup just in case the exchange server fails. (Urgh!!double the work for me). Everything works fine internally but I have one user who sometimes works from home with his windows 98 pc and outlook express. He wants to download all the his messa...

How to handle @@error=1785
I'm trying to add a FK constraint to a table and get error 1785 " Introducing FOREIGN KEY constraint 'fk_blah' on table my_table' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. " I've tried put the alter table statement in a tran and roll it back if @@error=1785 but I still get the error. Not sure if there is any way to handle this. If there was it would be good because I've actually got 100 similar alter table statements to be run in and my list stops at the f...

Message box when DOB meets certain criteria
Hi all, I am a relative novice to the ways of Access, I am currently setting up a database in Access 2003. I have a form field (DOB) that if the date of birth entered makes the person between 2 and 16 years old I would like a message box to appear notifying the user (and be able to turn it off). Similarly if an existing record is opened and a child has it's second birthday I would also like the message to appear.. .I have posted a similar request and have got thus far: Private Sub DOB_BeforeUpdate(Cancel As Integer) If DateAdd("yyyy", 2, [DOB]) > Date Or Date...

New Messages Not In Bold? Why?
I use Outlook 2002, as a new user configured on my company system. Last night before I left the office, I customized it so that messages from my boss come to me appearing in read, and messages only to me appearing in blue. However, this morning, I see that none of the new messages that arrived to me today are in bold. This is not good, because I really need the bolding to help me organize my messages in my Inbox. What might I have done to cause this, and how can i change it back? I checked F1 and couldn;t find anything on it. Thanks. Chuck ...