Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

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

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
tf842
Asked:
tf842
  • 5
  • 4
1 Solution
 
Jay ToopsCommented:
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
 
Jay ToopsCommented:
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
 
tf842Author Commented:
Thanks Jay. How do I count the lines DTS'd at the time of the failure and send that back?
Sami
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Jay ToopsCommented:
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
 
tf842Author Commented:
I apologize for the delay.

Please include a code sample for teh @@rowcount and writing the completed step 1 message.
0
 
Jay ToopsCommented:
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
 
tf842Author Commented:
Thank you jltoops.

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

I really appreciate it.
Sami
0
 
tf842Author Commented:
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
 
Jay ToopsCommented:
cool
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now