how to create TRANSACTION for all this

Question

how to create TRANSACTION for all this

midan1

SSCertifiable

Points: 5100
More actions
November 22, 2005 at 2:18 pm

#92258
1. Check if file exists in folder
2. send it by mail with (xp_SMTPSendMail80)
3. move the file to backup folder c:\B\uniq _file_name.txt
4. save the file with uniq file name !!
5. evry 30 minute
put all and create TRANSACTION !
fix my code
------------
BEGIN TRANSACTION
----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 'send email'
else
  print 'not exists'
IF @@Error <> 0
GOTO ErrorHandler
----for the Email
exec master.dbo.xp_SMTPSendMail80
@address='199.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='uuu@zxc.co.il'
-- put your emailing code here...
END
ELSE
print 'no email today'
IF @@Error <> 0
GOTO ErrorHandler
DECLARE @fn varchar(20),@fnb varchar(20),@fnc varchar(20),@cmd varchar(100)
set @fnb=REPLACE(convert(char(10),getdate(),14),':','')
SET @fn =REPLACE(convert(char(8),getdate(),1),'/','')
set @fnc=@fn+@fnb
SET @cmd ='Move C:\1.txt c:\x\'+@fnc+'.txt'
EXEC master..xp_cmdshell @cmd
IF @@Error <> 0
GOTO ErrorHandler
COMMIT TRANSACTION
ErrorHandler:
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
RETURN
-----------------------------------
thnks
ilan

Viewing 2 posts - 1 through 1 (of 1 total)

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

Vladan SSC-Insane Points: 22072 More actions · Answer 1

Huh.. I'm not sure what you expect the transaction to do...? If the transaction is rolled back, mails are already sent and file copied, how would you expect to get into other people's mailboxes and delete the mail? IMHO the only thing you can rollback are SQL actions (inserting or updating data, changing DDL etc.). I don't see how transaction could help in your case of file manipulation.

Please try to explain what is your expected result, maybe there is some other way to achieve it.