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
