xp_cmdshell and BLAT

[Yvonne]

Would like to implement Blat on SQL Server 2000. I'm looking for
example syntax for setting up BLAT with xp_cmdshell.
TIA

[Simon Hayes]

yv***********@uc.edu (Yvonne) wrote in message news:<89**************************@posting.google. com>...

Would like to implement Blat on SQL Server 2000. I'm looking for
example syntax for setting up BLAT with xp_cmdshell.
TIA

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_cmdshell @xp

As an alternative, you might consider xp_smtp_sendmail, 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\)]

"Yvonne" <yv***********@uc.edu> wrote in message
news:89**************************@posting.google.c om...

Would like to implement Blat on SQL Server 2000. I'm looking for
example syntax for setting up BLAT with xp_cmdshell.
TIA

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

[Jason E. Schaitel MCP]

yv***********@uc.edu (Yvonne) wrote in message news:<89**************************@posting.google. com>...

Would like to implement Blat on SQL Server 2000. I'm looking for
example syntax for setting up BLAT with xp_cmdshell.
TIA

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 's******@somewhere.com','Daily Batch Process Failed'

or

EXEC SP_BLAT 's******@somewhere.com','Greetings','I wanted to say
Hello','C*@somewhere.com','\\File-Server\Shared-Data\Blat\SomeFile.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_CMDSHELL 'IF EXIST
\\File-Server\Shared-Data\Blat\Production\BlatStoredProcedure.cmd del
\\File-Server\Shared-Data\Blat\Production\BlatStoredProcedure.cmd'
EXEC MASTER..XP_CMDSHELL 'IF EXIST
\\File-Server\Shared-Data\Blat\Production\BlatStoredProcedureMessage.tx t
del \\File-Server\Shared-Data\Blat\Production\BlatStoredProcedureMessage.tx t'
EXEC MASTER..XP_CMDSHELL 'IF EXIST
\\File-Server\Shared-Data\Blat\Production\BlatStoredProcedureToFile.txt
del \\File-Server\Shared-Data\Blat\Production\BlatStoredProcedureToFile.txt '
EXEC MASTER..XP_CMDSHELL 'IF EXIST
\\File-Server\Shared-Data\Blat\Production\BlatStoredProcedureCCFile.txt
del \\File-Server\Shared-Data\Blat\Production\BlatStoredProcedureCCFile.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\Production\BlatStoredProcedureMessage.tx t'
EXEC MASTER..XP_CMDSHELL @COMMANDSTRING
END
ELSE
BEGIN
SET @COMMANDSTRING = 'ECHO >>
\\File-Server\Shared-Data\Blat\Production\BlatStoredProcedureMessage.tx t'
EXEC MASTER..XP_CMDSHELL @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\Production\BlatStoredProcedure.cmd'
EXEC MASTER..XP_CMDSHELL @COMMANDSTRING

SET @COMMANDSTRING = 'ECHO set BLAT_TO=' + @TOEMAIL + ' >>
\\File-Server\Shared-Data\Blat\Production\BlatStoredProcedure.cmd'
EXEC MASTER..XP_CMDSHELL @COMMANDSTRING

SET @COMMANDSTRING = 'ECHO set BLAT_SUBJECT="' + @SUBJECT + '" >>
\\File-Server\Shared-Data\Blat\Production\BlatStoredProcedure.cmd'
EXEC MASTER..XP_CMDSHELL @COMMANDSTRING

SET @COMMANDSTRING = 'ECHO set
BLAT_MESSAGE=\\File-Server\Shared-Data\Blat\Production\BlatStoredProcedureMessage.tx t

\\File-Server\Shared-Data\Blat\Production\BlatStoredProcedure.cmd'

EXEC MASTER..XP_CMDSHELL @COMMANDSTRING
IF NOT @CCEMAIL IS NULL
BEGIN
SET @COMMANDSTRING = 'ECHO set BLAT_CC=' + @CCEMAIL + ' >>
\\File-Server\Shared-Data\Blat\Production\BlatStoredProcedure.cmd'
EXEC MASTER..XP_CMDSHELL @COMMANDSTRING
END

IF NOT @BCCEMAIL IS NULL
BEGIN
SET @COMMANDSTRING = 'ECHO set BLAT_BCC=' + @BCCEMAIL + ' >>
\\File-Server\Shared-Data\Blat\Production\BlatStoredProcedure.cmd'
EXEC MASTER..XP_CMDSHELL @COMMANDSTRING
END
IF NOT @ATTACHMENT IS NULL
BEGIN
SET @COMMANDSTRING = 'ECHO SET BLAT_ATTACH=' + @ATTACHMENT + ' >>
\\File-Server\Shared-Data\Blat\Production\BlatStoredProcedure.cmd'
EXEC MASTER..XP_CMDSHELL @COMMANDSTRING
IF CHARINDEX(@ATTACHMENT, '.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\Production\BlatStoredProcedure.cmd'

EXEC MASTER..XP_CMDSHELL @COMMANDSTRING

SET @COMMANDSTRING =
'\\File-Server\Shared-Data\Blat\Production\BlatStoredProcedure.cmd'
EXEC MASTER..XP_CMDSHELL @COMMANDSTRING