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
389 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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Jay. How do I count the lines DTS'd at the time of the failure and send that back?
Sami
0
 
LVL 10

Expert Comment

by:Jay Toops
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:tf842
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
cool
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dynamics crm 2011 8 43
c# code 19 56
SSIS import multiple CSVs into associated tables 3 28
Azure SQL DB? 3 11
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

728 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