Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
399 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
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)

 
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 2000 total points
ID: 12276622
cool
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

719 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