November 22, 2005 at 12:46 pm
need help
-------------
need help to complete puzzle !!!
i have same code i need to put all together
but i must to save all the old files in a backup folder
with a UNIQ file name so like this i save evry 30 minuth
-------------------------------------------
----for -- using xp_fileexists
declare @Path varchar(128) ,
@FileName varchar(128)
select @Path = 'C:\' ,
@FileName = 'myfile.txt'
declare @i int
declare @File varchar(1000)
select @File = @Path + @FileName
exec master..xp_fileexist @File, @i out
if @i = 1
print 'exists'
else
print 'not exists'
------------------------------------
----for the Email
exec master.dbo.xp_SMTPSendMail80
@address='138.134.29.65',
@subject='bbbbbbbbbbbbbbbbbbbbbbbbb',
@recipient='mail@cxg.co.il',
@from='mailb@cxg.co.il',
@attachments = 'c:\1.xls' ,
@html=1, -- Send the results as html
@Body='<html><header><table border=0 align=center dir=rtl>
<tr>
<td><div align=center>
<p align=center><font face=Arial><strong>xxxxxxxxxxxxxxxxxx</strong></font></p>
</div></td>
</tr>
</table></header><body>new development DLL FILE - automatic agent </html></body>',
@qTableCaption='<p align=center><font color="#FF0000" face=Arial><strong>vvvvvvvvvvvvvvvvvv</strong></font></p>',
@qTableAttribute=' border=1 align=center cellpadding=0 cellspacing=0 bordercolor=#0033FF dir=rtl',
@copy_recipients='uhx0e@iec.co.il'
-- put your emailing code here...
END
ELSE
print 'no email today'
-----------------------------------------------
--for move file
EXEC master..xp_cmdshell 'move C:\1.xls C:\Matrix\'
----------------------------------------------
---------for rename
declare @cmd varchar(1000)
declare @NewFile varchar(50)
set @NewFile = ' afile' + replace(replace(convert(varchar(10), getdate(), 20), '-', ''), ' ', '') + '.txt'
SET @Cmd = 'ren C:\1.txt' + @NewFile
EXEC MASTER..xp_cmdshell @Cmd, NO_OUTPUT
-----------------------------------------------
thnks for all
ilan
November 23, 2005 at 4:18 am
Ilan,
It is not clear to me if this is a question or a solution you give us.
The only problem I have with xp_cmdShell MOVE & xp_cmdshell REN is that there is no errorhandling.
Here are some TSQL stored procedures that can raise an error. Although I believe they are a bit clumsy. I believe something like this can be written a lot nicer in .net in SQL2K5!
Jan
CREATE PROCEDURE sp_renfile @sourcefilepath sysname, @targetfilepath sysname=NULL
AS
SET NOCOUNT ON
-- check first if file exists
CREATE TABLE #fileexists ( exist int, dir int, parent int)
CREATE TABLE #cmd_result (output varchar(8000))
DECLARE @cmdstr varchar(8000)
INSERT #fileexists
EXEC master..xp_fileexist @sourcefilepath
IF EXISTS (SELECT * FROM #fileexists WHERE exist = 1)
BEGIN
EXEC master..xp_sprintf @cmdstr OUTPUT, 'ren "%s" "%s"',@sourcefilepath, @targetfilepath
INSERT #cmd_result
EXEC master..xp_cmdshell @cmdstr
SELECT * FROM #cmd_result
IF NOT EXISTS(SELECT * FROM #cmd_result WHERE output like '%cannot find%')
BEGIN
SET @cmdstr='The file rename operation "'+ @cmdstr +'" was successful'
PRINT @cmdstr
EXEC master..xp_logevent 50001, @cmdstr, 'INFORMATIONAL'
END
ELSE RAISERROR('File rename failed',16,1)
END
ELSE RAISERROR('File does not exist',16,1)
DROP TABLE #fileexists
DROP TABLE #cmd_result
RETURN 0
CREATE PROCEDURE sp_copyfile @sourcefilepath sysname, @targetfilepath sysname=NULL
AS
SET NOCOUNT ON
DECLARE @cmdstr varchar(8000)
CREATE TABLE #cmd_result (output varchar(8000))
EXEC master..xp_sprintf @cmdstr OUTPUT, 'copy "%s" "%s"',@sourcefilepath, @targetfilepath
INSERT #cmd_result
EXEC master..xp_cmdshell @cmdstr
SELECT * FROM #cmd_result
IF EXISTS(SELECT * FROM #cmd_result WHERE output like '%file(s) copied%')
BEGIN
SET @cmdstr='The file copy operation "'+ @cmdstr +'" was successful (at least one file was copied)'
PRINT @cmdstr
EXEC master..xp_logevent 50001, @cmdstr, 'INFORMATIONAL'
END
ELSE RAISERROR('File copy failed',16,1)
DROP TABLE #cmd_result
RETURN 0
November 28, 2005 at 12:01 pm
Try to think in a different light ... these activities can all be executed out of SQL Server SPs. But a more natural/logical choice would be another scripting language (VB, Perl, ksh) ... then you'd just have your 5 steps listed in something like:
exec master..xp_cmdshell 'file_exists.vbs'
exec master..xp_cmdshell 'send_email.vbs'
exec master..xp_cmdshell 'move_file_to_backup_with_rename.vbs'
With this as a Scheduled Task on the SQL Server.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply