473,392 Members | 1,487 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,392 software developers and data experts.

xp_cmdshell and BLAT

Would like to implement Blat on SQL Server 2000. I'm looking for
example syntax for setting up BLAT with xp_cmdshell.
TIA
Jul 20 '05 #1
3 7623
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
Jul 20 '05 #2

"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'
Jul 20 '05 #3
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
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: paolo | last post by:
is there anyone who has used blat? I have problem when sending a mail from a cgi-script. Any errors arent displayed on the html-page, but the mail is not sent. the code looks like this:...
3
by: Terri | last post by:
I'm using xp_cmdshell to output a text file from a trigger like this CREATE TRIGGER ON tblApplications FOR INSERT AS DECLARE @FirstName varchar(75) DECLARE @LastName varchar(75) Declare...
4
by: Joel Thornton | last post by:
Whenever something is inserted to a given table, I want to run some shell commands using xp_cmdshell. Would it be a bad idea to put this xp_cmdshell in the INSERT trigger of this table? I...
5
by: Narine | last post by:
Hi Folks, I am using an xp_cmdshell sp to start a C# app. This app downloads data files and imports them into DB. It has a logging functionality built-in and when executed from the command line...
2
by: Lauren Quantrell | last post by:
I have created a view named viewOutput that shows one column in a table. I insert a row into the table and then I'm using this code to create a file with the text in the single row. This code...
1
by: Micah Gentry | last post by:
Is there any way to allow a user to use the xp_cmdshell extended stored procedure without giving that user execute permissions to xp_cmdshell in SQL server 6.5? Let me clarify. Lets say I (as the...
1
by: TC | last post by:
Hi Steve Some time ago I told you about the "blat" SMTP mailer. You said you would look into it. I'm now planning on using it myself. I've tried the DLL version, & am having a problem with the...
0
by: Jay | last post by:
Not sure who is familiar with the blat program, but I would like to send an email using blat by executing a commmand line using the Shell method. here's my code: /================ Dim RetVal...
0
by: misra | last post by:
Hi, I am using a C# code to call blat and send mail. When I send mails with long sentences, the mail automatically is send with multiple lines. However, certain times the body and format of the...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.