eliminating the usage of XP_CMDSHELL

Question

eliminating the usage of XP_CMDSHELL

surya-236315

SSC Eights!

Points: 850
More actions
June 3, 2005 at 9:09 pm

#60351

Hi all! please read this procedure and suggest me is there anyway that I can eliminate the usage of XP_CMDSHELL? I hope DTS can do that so, it is here. Any suggestions will be appreciated.
THANKS
CREATE PROCEDURE dbo.proc_ver_tp_files_pull ( @row_date as smalldatetime
= null )
AS
BEGIN
/*************************************************************************************************************************************************************************************
If the Date Parameter is NULL, then assign today's date to @row_date
otherwise accept date entered as a parameter.
************************************************************************************************************************************************************************************/
IF ( @row_date IS NULL )
BEGIN
Print 'Select Rundate as today because no Rundate was supplied'
SELECT @row_date = convert(char(10),getdate(),101)
END
DECLARE
@row_count int,
@rec_count int,
@done int, /* Flag which indicates end of While loop for loading each
table */
@wait_state_cntr int, /* Wait Loop counter used to check whether the Wait
Delay has surpassed 3 hours */
@dos_date smalldatetime,
@master_dos_date smalldatetime, /* Stores the Master dos date associated
with each text file's system time/date stamp */
@processdate smalldatetime,
@date_created smalldatetime,
@site as char(3)
/*********************************************************************************************************************************************************************************/
Print 'EHO Started'
Print 'Select done = 0, wait_state_cntr = 0, site = EHO'
SELECT
@done = 0,
@wait_state_cntr = 0,
@site = 'eho'
Print 'Execute Proc_Tp_Ftp_Pull ACE, EHO, Row_date to generate rundates.cmd
and ace.cmd'
EXECUTE proc_tp_ace_ftp_pull 'ACE', 'EHO', @row_date
WHILE (@done = 0 )
BEGIN
PRINT 'Execute FTP using rundates.cmd for EHO'
<script></script> EXECUTE MASTER..xp_cmdshell 'd:\boc\bcp\ftp\ftp_rundates > d:
\boc\log\tp\rundates.log'
PRINT 'Delete EHO files from bocadmin..ver_tp_files'
DELETE FROM bocadmin..ver_tp_files WHERE substring(rundate, 40,16) LIKE
'%' + @site + '%'
Print 'Dir Rundateseho into dosdate.bcp'
EXECUTE MASTER..xp_cmdshell 'DIR d:\boc\bcp\ftp\rundateseho.txt > d:
\boc\bcp\ftp\dosdate.bcp'
Print 'BCP dosdate.bcp into bocadmin..ver_tp_files for Rundateseho'
EXECUTE MASTER..xp_cmdshell 'BCP bocadmin..ver_tp_files in d:
\boc\bcp\ftp\dosdate.bcp -SNJROS1BVD0038\DEV_MIS -Ucctech1 -Pcctech1 -c -F6
-L6'
/* Shows output for development purposes only --- remove then stored proc.
has been completed */
/*
SELECT
substring(rundate, 1,8) as 'rundate',
substring(rundate, 9,8) as 'Time',
substring(rundate, 17,22) as 'File_Size',
substring(rundate, 40,16) as 'File_Name'
FROM
bocadmin..ver_tp_files
*/
Print 'Select master_dos_date, dos_date, and rec_count'
Print 'Then Select row_count'
SELECT DISTINCT
@master_dos_date = substring(rundate, 1,8),
@dos_date = substring(rundate, 1,8),
@rec_count = count(*)
FROM
bocadmin..ver_tp_files
WHERE
substring(rundate, 40,16) LIKE 'rundates'+@site+'%' and
cast(substring(rundate, 28, 3) + substring(rundate, 32, 3) +
substring(rundate, 36, 3)as int) > 0
GROUP BY
substring(rundate, 1,8)
SELECT @Row_Count = @@ROWCOUNT
IF ( @Row_Count = 1 )
BEGIN
Print 'When row_count = 1'
Print 'Delete from bocadmin..rundates'
DELETE FROM bocadmin..rundates
Print 'BCP rundateseho.txt into bocadmin..rundates'
EXECUTE MASTER..xp_cmdshell 'bcp bocadmin..rundates in d:
\boc\bcp\ftp\rundateseho.txt -fd:\boc\fmt\rundates.fmt
-SNJROS1BVD0038\DEV_MIS -Ucctech1 -Pcctech1 -ed:\boc\log\TP\rundateseho<script></script> .err
-T'
Print 'Select processdate from bocadmin..rundates'
SELECT DISTINCT @processdate = processdate FROM rundates
IF ( @processdate <> @row_date )
BEGIN
Print 'When processdate does not equal rowdate go to done'
GOTO DONE
END
Print 'Select done = 1'
SELECT @done = 1
END
ELSE
BEGIN
Print 'EHO rundates.cmd is not available'
PRINT 'GOING INTO 15 MINUTE SLEEP MODE for rundateseho'
WAITFOR DELAY '000:15:00'
Print 'The wait_state_cntr is being incremented by 1'
SELECT @wait_state_cntr = @wait_state_cntr + 1
IF @wait_state_cntr > 12
BEGIN
Print 'The wait_state_cntr is greater than 12 go to done'
GOTO DONE
END
END
END
/*************************************************************************************************************************************************************************/
Print 'Select done = 0, wait_state_cntr = 0'
SELECT
@done = 0,
@wait_state_cntr = 0
WHILE (@done = 0 )
BEGIN
Print 'Delete EHO files from bocadmin..ver_tp_files except rundates'
DELETE FROM bocadmin..ver_tp_files WHERE substring(rundate, 40,16) LIKE
'%' + @site + '%' AND substring(rundate, 40,16) NOT LIKE '%rundateseho%'
Print 'Execute FTP using ace.cmd for EHO'
EXECUTE MASTER..xp_cmdshell 'd:\boc\bcp\ftp\ftp_ace > d:
\boc\log\tp\ace_eho.log'
Print 'Dir Batchcdeho into dosdate.bcp'
EXECUTE MASTER..xp_cmdshell 'DIR d:\boc\bcp\ftp\batchcdeho.txt > d:
\boc\bcp\ftp\dosdate.bcp'
Print 'BCP dosdate.bcp into bocadmin..ver_tp_files for Batchcdeho'
EXECUTE MASTER..xp_cmdshell 'BCP bocadmin..ver_tp_files in d:
\boc\bcp\ftp\dosdate.bcp -SNJROS1BVD0038\DEV_MIS -Ucctech1 -Pcctech1 -c -F6
-L6'
Print 'Dir Requesteho into dosdate.bcp'
EXECUTE MASTER..xp_cmdshell 'DIR d:\boc\bcp\ftp\requesteho.txt > d:
<script></script>\boc\bcp\ftp\dosdate.bcp'
Print 'BCP dosdate.bcp into bocadmin..ver_tp_files for Requesteho'
EXECUTE MASTER..xp_cmdshell 'BCP bocadmin..ver_tp_files in d:
\boc\bcp\ftp\dosdate.bcp -SNJROS1BVD0038\DEV_MIS -Ucctech1 -Pcctech1 -c -F6
-L6'
Print 'Dir Peopleeho into dosdate.bcp'
EXECUTE MASTER..xp_cmdshell 'DIR d:\boc\bcp\ftp\peopleeho.txt > d:
\boc\bcp\ftp\dosdate.bcp'
Print 'BCP dosdate.bcp into bocadmin..ver_tp_files for Peopleeho'
EXECUTE MASTER..xp_cmdshell 'BCP bocadmin..ver_tp_files in d:
\boc\bcp\ftp\dosdate.bcp -SNJROS1BVD0038\DEV_MIS -Ucctech1 -Pcctech1 -c -F6
-L6'
Print 'Dir Activityeho into dosdate.bcp'
EXECUTE MASTER..xp_cmdshell 'DIR d:\boc\bcp\ftp\activityeho.txt > d:
\boc\bcp\ftp\dosdate.bcp'
Print 'BCP dosdate.bcp into bocadmin..ver_tp_files for Activityeho'
EXECUTE MASTER..xp_cmdshell 'BCP bocadmin..ver_tp_files in d:
\boc\bcp\ftp\dosdate.bcp -SNJROS1BVD0038\DEV_MIS -Ucctech1 -Pcctech1 -c -F6
-L6'
Print 'Dir Prusubjeho into dosdate.bcp'
EXECUTE MASTER..xp_cmdshell 'DIR d:\boc\bcp\ftp\prusubjeho.txt > d:
\boc\bcp\ftp\dosdate.bcp'
Print 'BCP dosdate.bcp into bocadmin..ver_tp_files for Prusubjeho'
EXECUTE MASTER..xp_cmdshell 'BCP bocadmin..ver_tp_files in d:
\boc\bcp\ftp\dosdate.bcp -SNJROS1BVD0038\DEV_MIS -Ucctech1 -Pcctech1 -c -F6
-L6'
Print 'Select dos_date, and rec_count'
Print 'Then Select row_count'
SELECT DISTINCT
@dos_date = substring(rundate, 1,8),
@rec_count = count(*)
FROM
bocadmin..ver_tp_files
WHERE
substring(rundate, 40,16) LIKE '%' + @site + '%' and
cast(substring(rundate, 28, 3) + substring(rundate, 32, 3) +
substring(rundate, 36, 3)as int) > 0
GROUP BY
substring(rundate, 1,8)
SELECT @Row_Count = @@ROWCOUNT
IF ( @Row_Count = 1 AND @rec_count = 6 AND @master_dos_date = @dos_date)
<script></script> BEGIN
Print 'When row_count = 1 and rec_count = 6 and master_dos_date =
dos_date then load remaining ACE files'
Print 'Delete from bocadmin..batchcode'
DELETE FROM bocadmin..batchcode
Print 'BCP batchcdeho.txt into bocadmin..batchcode'
EXECUTE MASTER..xp_cmdshell 'bcp bocadmin..batchcode in d:
\boc\bcp\ftp\batchcdeho.txt -fd:\boc\fmt\batch.fmt -SNJROS1BVD0038\DEV_MIS
-Ucctech1 -Pcctech1 -ed:\boc\log\TP\batchcdeho.err -T'
Print 'Delete from bocadmin..request'
DELETE FROM bocadmin..request
Print 'BCP requesteho.txt into bocadmin..request'
EXECUTE MASTER..xp_cmdshell 'bcp bocadmin..request in d:
\boc\bcp\ftp\requesteho.txt -fd:\boc\fmt\request.fmt
-SNJROS1BVD0038\DEV_MIS -Ucctech1 -Pcctech1 -ed:\boc\log\TP\requesteho.err
-T'
Print 'Delete from bocadmin..people'
DELETE FROM bocadmin..people
Print 'BCP peopleeho.txt into bocadmin..people'
EXECUTE MASTER..xp_cmdshell 'bcp bocadmin..people in d:
\boc\bcp\ftp\peopleeho.txt -fd:\boc\fmt\people.fmt -SNJROS1BVD0038\DEV_MIS
-Ucctech1 -Pcctech1 -ed:\boc\log\TP\peopleeho.err -T'
Print 'Delete from bocadmin..activity'
DELETE FROM bocadmin..activity
Print 'BCP activityeho.txt into bocadmin..activity'
EXECUTE MASTER..xp_cmdshell 'bcp bocadmin..activity in d:
\boc\bcp\ftp\activityeho.txt -fd:\boc\fmt\activity.fmt
-SNJROS1BVD0038\DEV_MIS -Ucctech1 -Pcctech1 -ed:\boc\log\TP\activityeho.err
-T'
Print 'Delete from bocadmin..prusubj'
DELETE FROM bocadmin..prusubj
Print 'BCP prusubjeho.txt into bocadmin..prusubj'
EXECUTE MASTER..xp_cmdshell 'bcp bocadmin..prusubj in d:
\boc\bcp\ftp\prusubjeho.txt -fd:\boc\fmt\prusubj.fmt
-SNJROS1BVD0038\DEV_MIS -Ucctech1 -Pcctech1 -ed:\boc\log\TP\prusubjeho.err
-T'
Print 'Select done = 1'
Select @done = 1
END
ELSE
BEGIN
Print 'EHO batchcd.txt, request.txt, people.txt, activity.txt,
prusubj.txt are not available'
PRINT 'GOING INTO 15 MINUTE SLEEP MODE for ACE_eho'
WAITFOR DELAY '000:15:00'
Print 'The wait_state_cntr is being incremented by 1'
SELECT @wait_state_cntr = @wait_state_cntr + 1
IF @wait_state_cntr > 12
BEGIN
Print 'The wait_state_cntr is greater than 12 go to done'
GOTO DONE
END
END
END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Aaron West SSC Eights! Points: 808 More actions · Answer 1

Yikes!

First of all, what does this do, and when and in what conditions does it run? You need to determine that first. You've provided little information to go on. Where are the FMT files? Where is the schema of the destination tables? Where is the contents of d:\boc\bcp\ftp\ftp_rundates and of d:\boc\bcp\ftp\ftp_ace, for example?

If you are the author, or know exactly what this does, I would suggest you rewrite it from scratch using a Perl script. Or C (Visual C++ console mode WIN32 app). Or Visual Basic. Then, presuming this stored proc is run at a scheduled time by SQL Agent, instead schedule a task using SCHTASKS or AT or Control Panel -> Scheduled Tasks. You might have to create a batch file that runs the new program you just wrote. That's all assuming this is run periodically, rather than based on a trigger or some event. (If it's run on a trigger, ugh!)

If you are not the author, and don't know how this works, pick one small piece at a time and dissect it until you know how it works. When you're done, rewrite the whole thing. I bet a 100 line Perl script could replicate what's being done here. Perl has built-in operators for checking file times, or use the stat function. (See http://www.activestate.com)

If you must do this in SQL, you might try sp_OACreate. eg: http://dbforums.com/t322430.html

But note that "sp_OA* extended stored procedure calls can only be executed by members of the sysadmin fixed role" ( http://www.dotnetjunkies.com/WebLog/thomasswilliams/archive/2004/09/01/23873.aspx&nbsp

PS. I hope those -P passwords aren't your real passwords.

Roy Ashbrook SSChasing Mays Points: 632 More actions · Answer 2

why perl as opposed to vbscript of javascript? or why in the world c vs something else?

doesn't look like the script does much special by it's looks. i would suspect dts wouldn't be a problem. i would agree with the rewriting from scratch theory if you have to remove the xp_cmdshell functionality.

Fred Williams-134192 SSCrazy Points: 2482 More actions · Answer 3

All the "bcp tablename in" portions can be replaced by using the bulk insert SQL statement.

For the others, either a rewrite as a client program, in a language that has more facilities for dealing with the filesystem, etc., or, a rewrite of the other components as well, so as to make dealing with the filesystem un-necessary seems logical.