xp_cmdshell and BLAT

**Simon Hayes** · Jul 20 '05, 12:55 AM

Re: xp_cmdshell and BLAT

yvonne.dishon@u c.edu (Yvonne) wrote in message news:<8928af42. 0307170540.23d7 a539@posting.go ogle.com>...[color=blue]
> Would like to implement Blat on SQL Server 2000. I'm looking for
> example syntax for setting up BLAT with xp_cmdshell.
> TIA[/color]

I'm not familiar with blat, but have a look at example E in the Books
Online entry for xp_cmdshell - it shows how to use a SQL variable in a
call to xp_cmdshell. You simply need to build a valid command string
and execute it:

set @xp = 'blat -p ' + @parameter + ' -v ' + @value + ...
exec master..xp_cmds hell @xp

As an alternative, you might consider xp_smtp_sendmai l, which is an
extended stored procedure so can be called directly from TSQL with no
need to use a command shell:

http://sqldev.net/xp.htm

Simon

**Greg D. Moore \(Strider\)** · Jul 20 '05, 12:56 AM

Re: xp_cmdshell and BLAT

"Yvonne" <yvonne.dishon@ uc.edu> wrote in message
news:8928af42.0 307170540.23d7a 539@posting.goo gle.com...[color=blue]
> Would like to implement Blat on SQL Server 2000. I'm looking for
> example syntax for setting up BLAT with xp_cmdshell.
> TIA[/color]

xp_cmdshell 'd:\blat\blat d:\blat\message s\tapefail.txt -s "DB FOO has a
tap failure" -t foo@bar.com'

**Jason E. Schaitel MCP** · Jul 20 '05, 12:56 AM

Re: xp_cmdshell and BLAT

yvonne.dishon@u c.edu (Yvonne) wrote in message news:<8928af42. 0307170540.23d7 a539@posting.go ogle.com>...[color=blue]
> Would like to implement Blat on SQL Server 2000. I'm looking for
> example syntax for setting up BLAT with xp_cmdshell.
> TIA[/color]

I am doing the same thing here.
This is the source code of my stored proc. Perhaps you will find it
usefull.

Here is how you would use it in your scripts:

EXEC SP_BLAT 'someguy@somewh ere.com','Daily Batch Process Failed'

or

EXEC SP_BLAT 'someguy@somewh ere.com','Greet ings','I wanted to say
Hello','CC@some where.com','\\F ile-Server\Shared-Data\Blat\SomeF ile.txt'

CREATE PROCEDURE SP_BLAT

@TOEMAIL VARCHAR(8000)
,@SUBJECT VARCHAR(1000)
,@BODY VARCHAR(8000) = NULL --Optional Parameter
,@CCEMAIL VARCHAR(8000) = NULL --Optional Parameter
,@ATTACHMENT VARCHAR(1000) = NULL --Optional Parameter
,@BCCEMAIL VARCHAR(8000) = NULL --Optional Parameter

AS
DECLARE @COMMANDSTRING VARCHAR(1000)
DECLARE @ATTACHTEXT BIT

SET @ATTACHTEXT = 0

--*************** *************** *************** *************** *************** **
--*** BEGIN DELETE EXISTING FILES
--*************** *************** *************** *************** *************** **
EXEC MASTER..XP_CMDS HELL 'IF EXIST
\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedure.cmd del
\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedure.cmd'
EXEC MASTER..XP_CMDS HELL 'IF EXIST
\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedureMessa ge.txt
del \\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedureMessa ge.txt'
EXEC MASTER..XP_CMDS HELL 'IF EXIST
\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedureToFil e.txt
del \\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedureToFil e.txt'
EXEC MASTER..XP_CMDS HELL 'IF EXIST
\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedureCCFil e.txt
del \\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedureCCFil e.txt'
--*************** *************** *************** *************** *************** **
--*** END DELETE EXISTING FILES
--*************** *************** *************** *************** *************** **

--*************** *************** *************** *************** *************** **
--*** BEGIN BUILD MESSAGE BODY TEXT FILE
--
--*** If the @BODY parameter if null then we still need to create the
Body
--*** message file but it needs to be blank.
--*************** *************** *************** *************** *************** **
IF NOT @BODY IS NULL
BEGIN
SET @COMMANDSTRING = 'ECHO ' + @BODY + ' >>
\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedureMessa ge.txt'
EXEC MASTER..XP_CMDS HELL @COMMANDSTRING
END
ELSE
BEGIN
SET @COMMANDSTRING = 'ECHO >>
\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedureMessa ge.txt'
EXEC MASTER..XP_CMDS HELL @COMMANDSTRING
END

--*************** *************** *************** *************** *************** **
--*** END BUILD MESSAGE BODY TEXT FILE
--*************** *************** *************** *************** *************** **

SET @COMMANDSTRING = 'ECHO ::This File is created by sp_blat, a system
stored procedure on all the CRM servers. >>
\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedure.cmd'
EXEC MASTER..XP_CMDS HELL @COMMANDSTRING

SET @COMMANDSTRING = 'ECHO set BLAT_TO=' + @TOEMAIL + ' >>
\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedure.cmd'
EXEC MASTER..XP_CMDS HELL @COMMANDSTRING

SET @COMMANDSTRING = 'ECHO set BLAT_SUBJECT="' + @SUBJECT + '" >>
\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedure.cmd'
EXEC MASTER..XP_CMDS HELL @COMMANDSTRING

SET @COMMANDSTRING = 'ECHO set
BLAT_MESSAGE=\\ File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedureMessa ge.txt[color=blue][color=green]
>> \\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedure.cmd'[/color][/color]
EXEC MASTER..XP_CMDS HELL @COMMANDSTRING

IF NOT @CCEMAIL IS NULL
BEGIN
SET @COMMANDSTRING = 'ECHO set BLAT_CC=' + @CCEMAIL + ' >>
\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedure.cmd'
EXEC MASTER..XP_CMDS HELL @COMMANDSTRING
END

IF NOT @BCCEMAIL IS NULL
BEGIN
SET @COMMANDSTRING = 'ECHO set BLAT_BCC=' + @BCCEMAIL + ' >>
\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedure.cmd'
EXEC MASTER..XP_CMDS HELL @COMMANDSTRING
END

IF NOT @ATTACHMENT IS NULL
BEGIN
SET @COMMANDSTRING = 'ECHO SET BLAT_ATTACH=' + @ATTACHMENT + ' >>
\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedure.cmd'
EXEC MASTER..XP_CMDS HELL @COMMANDSTRING
IF CHARINDEX(@ATTA CHMENT, '.txt') > 0
BEGIN
SET @ATTACHTEXT = 1
END
END

SET @COMMANDSTRING = 'ECHO \\File-Server\Shared-Data\Blat\blat. exe
%BLAT_MESSAGE% -subject %BLAT_SUBJECT% -to %BLAT_TO%'

IF NOT @CCEMAIL IS NULL
BEGIN
SET @COMMANDSTRING = @COMMANDSTRING + ' -cc %BLAT_CC%'
END

IF NOT @ATTACHMENT IS NULL
BEGIN
IF @ATTACHTEXT = 1
BEGIN
SET @COMMANDSTRING = @COMMANDSTRING + ' -attacht %BLAT_ATTACH%'
END
ELSE
BEGIN
SET @COMMANDSTRING = @COMMANDSTRING + ' -attach %BLAT_ATTACH%'
END
END

IF NOT @BCCEMAIL IS NULL
BEGIN
SET @COMMANDSTRING = @COMMANDSTRING + ' -bcc %BLAT_BCC%'
END

SET @COMMANDSTRING = @COMMANDSTRING + ' >>
\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedure.cmd'

EXEC MASTER..XP_CMDS HELL @COMMANDSTRING

SET @COMMANDSTRING =
'\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedure.cmd'
EXEC MASTER..XP_CMDS HELL @COMMANDSTRING

xp_cmdshell and BLAT

xp_cmdshell and BLAT

Comment

Comment

Comment