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
391 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

816 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now