Handling errors in DTS packages

HI!!

I've been working for an year or so with DTS, but it still makes me mad with it's cryptic error messages!!!!

"The task reported failure on execution" is one of the "funny" error messages I retrieve. I've tried with the log option, but error messages stored there are as cryptic as the one shown on the screen!!!!!

Timothy Peterson in "MS SQL Server 2000 DTS" provides code chunks that can be used to "decode" numerical error messages into something readable and understandable, but I really don't realize where should I put that code :( It seems to work only if you are executing packages via Visual Basic, and not using the MMC

That's it, I really do need help with this!!!!!!! I beleive that there's someone out there that had faced and solved this problem !

Thanks in advance
lorenaWhy do you have to use DTS?|||I'm working on an ETL process for a Data Warehouse
Oracle "holds" the source databases and the DW is implemented on SQL Server 2000.


Originally posted by Brett Kaiser
Why do you have to use DTS?

Handling Error Messages

I am trying to run a command, say "logman start perfcollection" and the
error i receive is that the "Collection is already running"
I am running it as a SQL agent job and because the command is already
running, it fails with the error.
How can I check for that message and if its that message, than I want to
pass it as success condition.
ThanksHi Hassan
Try running the command through xp_cmdshell the return value should be
-1073738775 when the log is already running, alternatively you could always
stop it first before starting it as this will not cause an error.
John
"Hassan" wrote:

>
> I am trying to run a command, say "logman start perfcollection" and the
> error i receive is that the "Collection is already running"
> I am running it as a SQL agent job and because the command is already
> running, it fails with the error.
> How can I check for that message and if its that message, than I want to
> pass it as success condition.
> Thanks
>
>
>

Handling Error Messages

I am trying to run a command, say "logman start perfcollection" and the
error i receive is that the "Collection is already running"
I am running it as a SQL agent job and because the command is already
running, it fails with the error.
How can I check for that message and if its that message, than I want to
pass it as success condition.
Thanks
Hi Hassan
Try running the command through xp_cmdshell the return value should be
-1073738775 when the log is already running, alternatively you could always
stop it first before starting it as this will not cause an error.
John
"Hassan" wrote:

>
> I am trying to run a command, say "logman start perfcollection" and the
> error i receive is that the "Collection is already running"
> I am running it as a SQL agent job and because the command is already
> running, it fails with the error.
> How can I check for that message and if its that message, than I want to
> pass it as success condition.
> Thanks
>
>
>

Handling Error Messages

I am trying to run a command, say "logman start perfcollection" and the
error i receive is that the "Collection is already running"
I am running it as a SQL agent job and because the command is already
running, it fails with the error.
How can I check for that message and if its that message, than I want to
pass it as success condition.
ThanksHi Hassan
Try running the command through xp_cmdshell the return value should be
-1073738775 when the log is already running, alternatively you could always
stop it first before starting it as this will not cause an error.
John
"Hassan" wrote:
>
> I am trying to run a command, say "logman start perfcollection" and the
> error i receive is that the "Collection is already running"
> I am running it as a SQL agent job and because the command is already
> running, it fails with the error.
> How can I check for that message and if its that message, than I want to
> pass it as success condition.
> Thanks
>
>
>

Handling a SQL Exceptions and Custom Error Messages

Hello guys,

I need some ideas on how to handle an exception or a user defined error message.

I have a procedure that creates a new user. Lets say if the e-mail address entered is already in use. What are some of the best practices for notifying the user that the e-mail address is already in use?

This is what I was thinking...

Solution #1
-----
My proc will raise an error with a message id that is great than 50000, then my DAL will recognize this is a user defined error and spit back to the user instead of trapping it.

Solution #2
-----
The proc should have an output param ( @.CreationStatus CHAR(1) ).
If the @.CreationStatus has a value for example "E", I will have lookup the value for "E" in my app and spit back that custom error message. I don't really like this option because it is too concrete.

What are some of the ways you deal with this situation?

Your suggestions are greatly appreciated.

Thank you!

You could return a @.status value with (0=success, 1= failure and an appropriate status message @.Statusmsg ( = 'Success' if @.status = 0, custom error message if @.status = 1)

From your application you could check the value in @.status and if its not 0, then display the message from @.statusmsg. You can handle this in a number of ways, It comes down to setting up one standard way of doing it across all procs and communicating with your team and documenting it so the same logic is followed across all procs.