Outlook

Hi,
try to use xp_sendmail. but you don't need the database in
single user to run dbcc checkdb, you only need single user
mode if you use the repair option.
CMLC

>--Original Message--
>Folks,
>Can you point me to where I can look for more info to
complete this task.
>There is a proprietary app running with SQL 2000 as
backend. Everynight the
>app put DB in single user mode and runs DBCC itself to
check data. That
>runs well except few times it failed to get exclusive
login in order to
>clear single user mode. It causes the app fails to
open. I think I may
>want to run a script at my computer querying the DB and
notify someone by
>using Outlook on my computer. Is there any info that can
tell me how to
>open Outlook from in inside sql script?
>Thanks,
>
>.
>
hi,
to use xp_sendmail we must have SQL mail configured on the SQL box. This is
not my case because I can't touch the box. What I wanted is to use Outlook
on my pc to send email out. Is it possible?

> you don't need the database in single user to run dbcc checkdb, you only
> need single user mode if you use the >repair option
the app is proprietary so ... no touching. It seems that it would repair
data if possible.
"CMLC" <anonymous@.discussions.microsoft.com> wrote in message
news:196d01c4a188$bc87d820$a401280a@.phx.gbl...[vbcol=seagreen]
> Hi,
> try to use xp_sendmail. but you don't need the database in
> single user to run dbcc checkdb, you only need single user
> mode if you use the repair option.
> CMLC
>
> complete this task.
> backend. Everynight the
> check data. That
> login in order to
> open. I think I may
> notify someone by
> tell me how to

Out in email.

I am using extended stored procedure xp_sendmail to send the email now my
question is, is there any way in can write the out of following query into
one file and send it to through email? or output directly goes to email..
restore FILELISTONLY from disk = 'c:\fullpath.bak'
Thanks
Probably a few ways but a couple off the top of my head -
Execute the command using osql and specify an output file in
the osql command.
Execute the command within a job - for the job step doing
the restore filelistonly, go to the advanced tab and specify
an output file.
You can then send the output file as an attachment using
xp_sendmail.
-Sue
On Mon, 30 Oct 2006 08:20:28 -0500, "Rogers"
<naissani@.hotmail.com> wrote:

>I am using extended stored procedure xp_sendmail to send the email now my
>question is, is there any way in can write the out of following query into
>one file and send it to through email? or output directly goes to email..
>restore FILELISTONLY from disk = 'c:\fullpath.bak'
>Thanks
>
|||Thanks Sue...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0ladk2d0g0n0gm69qv3ot2tgq5fagd7vkr@.4ax.com...
> Probably a few ways but a couple off the top of my head -
> Execute the command using osql and specify an output file in
> the osql command.
> Execute the command within a job - for the job step doing
> the restore filelistonly, go to the advanced tab and specify
> an output file.
> You can then send the output file as an attachment using
> xp_sendmail.
> -Sue
> On Mon, 30 Oct 2006 08:20:28 -0500, "Rogers"
> <naissani@.hotmail.com> wrote:
>

Otain error details from a DTS package through T-SQL. Was(xp_sendmail on script

SQL Server 2000

Good day all,

I've been asked to create a DTS package that will execute 2 other DTS packages (yeah, I know... personally I want to write a sp for it *shrug*) and send an e-mail out reporting a failure and failure details.

So I have a couple of questions for you;

How do I pick up the error number (and description?) from a failed DTS and pass it to my sendmail task?
Is it possible to send anything other than plain text e-mails?
Can I change the e-mail priority to "high"?

I appreciate any help you can offer me :)USE master
GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'S' AND name = 'sp_dtsFailureEmail') BEGIN
DROP PROCEDURE sp_dtsFailureEmail
END
GO

CREATE PROCEDURE sp_dtsFailureEmail
@.dtsName varchar(64)
, @.errorNumber int = 0
AS

IF @.errorNumber <> 0 BEGIN
DECLARE @.message varchar(2000)
, @.subject varchar(255)
, @.errorDesc nvarchar(255)

SET @.errorDesc = (SELECT description FROM sysmessages WHERE error = @.errorNumber)
SET @.subject = '***DTS FAILURE*** ' + @.dtsname + ' ***DTS FAILURE***'
SET @.message = 'DTS Package:' + Char(9) + Char(9) + @.dtsName
+ Char(13) + 'Failure Date:' + Char(9) + Char(9) + Convert(varchar, GetDate(), 0)
+ Char(13) + 'Error Number:' + Char(9) + Char(9) + Convert(varchar, @.errorNumber)
+ Char(13) + 'Error Description:' + Char(9) + @.errorDesc

PRINT ''
PRINT @.subject
PRINT ''
PRINT @.message
PRINT ''

EXEC xp_sendmail
@.recipients = '<enter your e-mail address>'
, @.subject = @.subject
, @.message = @.message
END
ELSE BEGIN
PRINT 'No Error'
END
GO

SELECT 1/0

EXEC sp_dtsFailureEmail '<enter dtsName>', @.@.Error

DROP PROCEDURE sp_dtsFailureEmail

I'm getting close, but I still don't know how to pass the error number from a DTS to... anywhere!!|||EXEC xp_cmdshell 'DTSRun /S "SQL-XXXX-XXXXX" /N "<dtsname>" /G "{5A14F80F-8EE2-477D-BE95-DC90A343D2A3}" /W "0" /E '
SELECT @.@.Error

Returns no error number when the DTS fails... So this suggests that this cannot be done!
Any ideas?|||As you know I know stuff all about DTS but you are not getting much help here.

1) Should you change the title of the thread? The problem does not really have anything to do with xp_sendmail.
2) Have you tried:

Declare @.error_return AS INT
EXEC @.error_return = xp_cmdshell 'DTSRun /S "SQL-XXXX-XXXXX" /N "<dtsname>" /G "{5A14F80F-8EE2-477D-BE95-DC90A343D2A3}" /W "0" /E '
SELECT @.error_return ??
3) Have you considered setting up a global variable, trapping the error in the DTS script and setting the global. Then test the global when the DTS script has finished.

HTH|||1) Wilco... my q2&3 were sp_sendmail though..

2) I'm just working on that! It returns a bit value declaring whether there was an error or not.

3) Never used global variables in DTS before - will look up if I run into a dead end with point 2.

Here's my current working code.

USE master
GO

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'S' AND name = 'sp_dtsFailureEmail') BEGIN
DROP PROCEDURE sp_dtsFailureEmail
END
GO

CREATE PROCEDURE sp_dtsFailureEmail
@.dtsName varchar(64)
AS

DECLARE @.message varchar(8000)
, @.subject varchar(255)
, @.errorDesc nvarchar(255)

-- SET @.errorDesc = (SELECT description FROM sysmessages WHERE error = @.errorNumber)
SET @.subject = '***DTS FAILURE*** ' + @.dtsname + ' ***DTS FAILURE***'
SET @.message = 'DTS Package:' + Char(9) + Char(9) + @.dtsName
+ Char(13) + 'Failure Date:' + Char(9) + Char(9) + Convert(varchar, GetDate(), 0)
-- + Char(13) + 'Error Number:' + Char(9) + Char(9) + Convert(varchar, @.errorNumber)
-- + Char(13) + 'Error Description:' + Char(9) + @.errorDesc

PRINT ''
PRINT @.subject
PRINT ''
PRINT @.message
PRINT ''

-- EXEC xp_sendmail
-- @.recipients = '<enter your email>'
-- , @.subject = @.subject
-- , @.message = @.message
-- END
GO

DECLARE @.err bit
DECLARE @.dts varchar(64)
DECLARE @.cmd varchar(1024)

SET @.dts = 'test'
SET @.cmd = 'DTSRun /S "SQL-LIVE-IT038" /N "' + @.dts + '" /W "0" /E'

EXEC @.err = xp_cmdshell @.cmd
IF @.err <> 0 BEGIN
EXEC sp_dtsFailureEmail @.dts
END

DROP PROCEDURE sp_dtsFailureEmail

Returns:

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147217900 (80040E14)
Error string: Divide by zero error encountered.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

Error Detail Records:

Error: -2147217900 (80040E14); Provider Error: 8134 (1FC6)
Error string: Divide by zero error encountered.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun: Package execution complete.
NULL

***DTS FAILURE*** test ***DTS FAILURE***

DTS Package: test
Failure Date: Sep 5 2007 12:16PM

This raises another question: Is it possible to retrieve the output information from xp_cmdshell?|||This raises another question: Is it possible to retrieve the output information from xp_cmdshell?You mean stuff printed to the window? No.

How come? Are you hoping to get the details?

As said I don't know DTS but can you get the details within the package? If so then you can write all this to a table and check that.|||You mean stuff printed to the window?

Yep.

How come? Are you hoping to get the details?

Yep.

Basically the aim is to e-mail a group of users with the error details of a DTS package. But I'm startign to think it's more trouble than it's worth ;)|||GeorgeV on DTS: Basically the aim is to e-mail a group of users with the error details of a DTS package. But I'm startign to think it's more trouble than it's worth ;)

Strong candidate for my new sig ;)

That, for me, is DTS through and through.|||Yeeeeappp...
I'd love to rewrite the whole script in T-SQL; but can't...

I don't know how to do absolutely everything the DTS does
It's classed as a "major change" and I really cba going through all that just to do it.

A colleague of mine wrote a couple of hefty SSIS packages a while back which took a few CSV's from a 3rd party product and transformed them into our databases... All was fine until the column order changed on the CSV file. So even SSIS does the simplest of jobs badly.|||A colleague of mine wrote a couple of hefty SSIS packages a while back which took a few CSV's from a 3rd party product and transformed them into our databases... All was fine until the column order changed on the CSV file. So even SSIS does the simplest of jobs badly.To be fair unless you write bullet proof dynamic sql or have some sort of config file T-SQL will fair no better. I imagine Pat would start muttering something about "contracts" upon reading this. You change the contract between two systems and you can't really expect things to run smoothly.|||Actually, to resolve this an interim DTS package was created which produces a "clean" file. DTS packages remember the mapping.

The 3rd party product cost a total of $99 - we got what we paid for :p