DTS errors returning to calling procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS errors returning to calling procedure

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-02-14 : 02:06:51

Hi there,

I have the following stored procedure;

CREATE PROCEDURE p_START_OOEIMPORT

AS

EXEC msdb..sp_start_job @job_name ='OOE_IMPORT_TMPPROJECTS'

that starts a scheduled event which executes a DTS package. This DTS package imports data from an uploaded text file. It has transactions enabled so if one step fails (such as the import due to incorrect data format) the next step is not executed. Is there any way I can get the error or status of the DTS package returned to this stored procedure?

The stored procedure is called from a webserver (asp page) which will have to inform the user of the status of the data import.

Any hints/tips would be appreciated.

cheers - matt.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-14 : 09:42:38

Take a look at this article Matt:

http://www.sqlteam.com/item.asp?ItemID=2290

The concept uses SQL-DMO to open another connection to the SQL Server and log the job progress in a table. Since it's a separate connection, it is not affected by a rollback within the job. So if an error occurs you can look through the progress table and see what went wrong.

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-02-14 : 20:43:30

Hi Rob,

Thanks for that - its a good article. Is there anyway I can make this stored procedure p_START_OOEIMPORT (as described in first post) execute the scheduled event and wait until the job is complete -> then check the log table as described in the article -> and return the result to the calling asp script?

The import will consist of 30 - 40 records so it is not a big job. Or is this idea asking for trouble?

thanks - matt

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-15 : 07:22:41

I think it makes more sense to put the log table check in the job itself as a separate step or steps. Then it can return success or failure to the SP. You might be able to add a line to the SP like this:

IF NOT EXISTS (SELECT * FROM logTable WHERE Status='Completed')
BEGIN
RAISERROR 'Import process did not complete', 16, 1
END

This would check for some indication that everything was successful and if not raise an error.

What about the On Success/On Failure options in the DTS package itself? I'm sure it could write the progress to the log table as well.

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-02-15 : 18:40:43

Hi rob,

Ive done exactly that - 2 execute statements in DTS that write to a log depending on success or failure. I have made the process so that the user must check the log display to determine if the upload/import was successful rather than getting an immediate response from the server.

thanks - matt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources