Solved

How do I get error code and messages from DTS or Stored Procedure and write to a log file

Posted on 2004-09-24
9
395 Views
Last Modified: 2012-08-13
All help appreciated. Simple sample code is best.

How do I generate more detailed error code and messages from DTS or Stored Procedure, and pass it to the calling job or write to a log file?

Currently MS SQL 7, moving to MS SQL 2k over the next 2 months

I manually start the job that call the DTS and Stored Procedures.

NOTE: Send email messages is already coded and working. I also want to include the error message, description and line's read or line# in the email. (SENDING THE EMAIL IS NOT part of this request.)

Mostly interested in retrieving the DTS error with line# and possibly part of the line's text back to the job so I can send an email with it. This will help me locate and correct the corrupt text quicker.

I have 2 large fixed format text files, 2gig+ that I dts into a 2 seperate tables. Frequently some of the data is corrupt.

I use KWEdit to open these large text files and regex to try to find the corrupt data. (This is very time consuming and sort of like finding a needle in a haystack!) The whole thing is compounded and additionally time consuming b/c I log in through pc-anywhere. (Cable modem, but still slow!)

Thank you,
Sami


0
Comment
Question by:tf842
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12145757
Things assigned to a job typically fail because the execute with the
SQL server agent permission and don't have access to somthing

Typically in debugging a package
id go to package -> properties -> advanced and
turn off "use transactions"
so that i can tell by looking at tables exactly what point the job fails..

you can alternatively places statements like

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDTSLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tblDTSLog] (
     [ExecDate] [datetime] NULL ,
     [Message] [varchar] (200) NULL
) ON [PRIMARY]
END

Insert Into tblDTSLog
(ExecDate, Message)
Values
(getdate(), 'Process Begins step 1.')
GO

Jay

0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12145777
Also for the SP's use the folowing to detect errors in query statements

try this after every command put
IF @@ERROR <> 0
  Insert Into tblDTSLog
(ExecDate, Message)
Values
(getdate(), 'Step XXX Reports errror')

JAY
0
 

Author Comment

by:tf842
ID: 12146050
Thanks Jay. How do I count the lines DTS'd at the time of the failure and send that back?
Sami
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

by:Jay Toops
ID: 12146544
Well basically you don't ...
you put a message like 'completed step 1'
or you could put a @@ROWCOUNT
if you want to know how many rows  the last command affected.

does that answer your question?

JAY
0
 

Author Comment

by:tf842
ID: 12237161
I apologize for the delay.

Please include a code sample for teh @@rowcount and writing the completed step 1 message.
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12249429
try this after every command put
IF @@ERROR <> 0
begin
  Insert Into tblDTSLog
(ExecDate, Message)
Values
(getdate(), 'Step XXX Reports errror')
end
else
begin
  Insert Into tblDTSLog
(ExecDate, Message)
Values
(getdate(), 'Step XXX Completed Successfully ' + @@ROWCOUNT + ' Rows afected')

end

JAY
0
 

Author Comment

by:tf842
ID: 12250205
Thank you jltoops.

It will take me a day or 2 to test this and accept it.

I really appreciate it.
Sami
0
 

Author Comment

by:tf842
ID: 12276436
jltoops,
It is Monday, 10.11.04.
If I don't test it by tomorrow, 10.12.04, I will just award the points and comment on the results later.
Thank you,
Sami
0
 
LVL 10

Accepted Solution

by:
Jay Toops earned 500 total points
ID: 12276622
cool
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question