yv***********@u c.edu (Yvonne) wrote in message news:<89******* *************** ****@posting.go ogle.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******@somewh ere.com','Daily Batch Process Failed'
or
EXEC SP_BLAT 's******@somewh ere.com','Greet ings','I wanted to say
Hello','C*@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
\\File-Server\Shared-Data\Blat\Produ ction\BlatStore dProcedure.cmd'
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
