Hi All,

I have been working on the following problem for more than two weeks
but without any luck.

We have ftp server on windows 2003 exchange server with IIS 6.0. There
is a DSN to connect to SQL Server 2000 databases from the FTP site.

When a file is ftped, it populates the database with file name and the
group name. Stored procedures run every minute to check whether the
database has been populated with the file information. Then it tries to
rename the file with timestamp.
When I use the rename command in the query analyzer, it can rename the
file but from the stored procedure the same code is not successful in
renaming the file.

Here is the rename portion of the code in the stored procedure.
select @renamestring = 'rename \\W2K3\data\' + rtrim(@oldfilename) + '
'+ rtrim(@newfilename)
declare @result int
declare @querystring char(200)
EXEC @result = master.dbo.xp_cmdshell @renamestring



here is the same in the QA to test , which renames really fine.
master..xp_cmdshell 'rename \\w2k3\data\ftp\test.txt test.sql'


I know it is hard to understand the problem looking at the portion of
the problem. But if you need I can follow-up with the complete code.

I will highly appreciate if you could help me. I have been working on
this for the last two weeks.

I thank you in advance million times for your help.

Best regards,

mamun

May be there are files where the name include spaces, in these cases you have
to enclose the path between double quote.

Example:

exec master..xp_cmdshell 'ren "\temp\test 1.txt" test1.txt'
go


AMB

Show quoteHide quote

HI AMB,

Here is my complete codes of the two sps. I am still without any luck.
Could anyone see any errors in the following code?

As always I am thankful for your help.

best regards,

mamun


SP1:


CREATE Procedure sp_FTPNotify_New (@logId int)
As
declare @recips varchar(255)
declare @msg varchar(250)
declare @sub varchar(75)
DECLARE @cmd varchar(56)
Declare @txtPtr varbinary(16)
declare @new int, @old int
declare @txt varchar(255)
declare @email varchar(50)
declare @newfilename varchar(255)
declare @oldfilename varchar(255)
declare @renamestring varchar(255)
declare @fieldposition int
declare @NewTarget varchar(255)
declare @newprefix varchar(20)
declare @FailedFlag varchar(255)
declare @LogTime datetime

Select @NewTarget=Target , @newprefix =
rtrim(rtrim(convert(char,logtime,12))+convert(char,LogID)),
    @FailedFlag = Operation,@LogTime = LogTime
    from FTPLogs l , FTPNotify n
    where LogID = @logId and
    lower(l.username) = lower(n.username)
select @fieldposition=0
WHILE @fieldposition  < 225
BEGIN
    select @fieldposition = @fieldposition+1

    if substring(@FailedFlag,@fieldposition,1) = ']'
        break

END
if lower(substring(@FailedFlag,@fieldposition+1,255-@fieldposition))
='created'
begin
select @fieldposition=0
WHILE @fieldposition  < 225
BEGIN
    select @fieldposition = @fieldposition+1

    if substring(@NewTarget,@fieldposition,1) = '.'
        break

END
Select
@NewTarget=rTrim(substring(@NewTarget,1,@fieldposition-1))+rtrim(@newprefix+rTrim(substring(@NewTarget,@fieldposition,255)))
Select @msg = 'This is an automatically generated FTP notification
message:'
delete from texttab
INSERT into texttab
select 'This message was generated on ' +
convert(varchar(25),getdate()) + char(13) + replicate ('_', 45) +
char(13)
select
    @recips = ' + rtrim(notify) + ',
    @txt =
    'The file from ' + rtrim(description) + ' has arrived. This file is
located on the server W2K3-S1 ' +
    'under the DATA\' + rtrim(l.username) + ' directory.' + char(13) +
char(13) +
    'The file name: ' + rtrim(@NewTarget) + char(13) +
    'Date Received: ' + convert(varchar(25),LogTime) + char(13) +
    'File Size: ' + convert(char(20), BytesRecvd) ,
    @sub = 'FTPLog Notification from "  + rtrim(description) + ',
    @newfilename = rtrim(@NewTarget),
    @oldfilename = rtrim(l.username)+'\'+ rtrim(Target)
    from FTPLogs l , FTPNotify n
    where LogID = @logId and
    lower(l.username) = lower(n.username)
select @txtptr = textptr(c1) from texttab
UPDATETEXT texttab.c1 @txtptr NULL 0 with log @txt
SELECT @cmd = 'SELECT c1 FROM FTPLogs.dbo.texttab'
exec  master.dbo.xp_sendmail
    @recipients = ' + @recips + ',
    @message = ' + @msg + ',
    @query =  '+ @cmd + ',
    @subject = ' + @sub + ',
    @no_header = 'TRUE', @width = 2500
delete  from texttab
select @renamestring = 'rename \\W2k3-S1\data\' + rtrim(@oldfilename) +
' '+ rtrim(@newfilename)
print @renamestring
declare @result int
declare @querystring char(200)
EXEC @result = master.dbo.xp_cmdshell @renamestring
if (@result = 1)
begin
select @querystring ='SELECT logid,substring(username,1,20)
username,logtime,bytesrecvd,substring(target,1,50) filename FROM
ftplogs.dbo.ftplogs where logid = '+ convert(char,@logId)
exec master.dbo.xp_sendmail @recipients = 'ma***@inc.com',
    @query = '" + @querystring + " ' ,
    @subject ='Failed Rename',
    @message ='The following file could not be renamed.',
    @attach_results = 'FALSE', @width = 250
end
end
Else


if lower(substring(@FailedFlag,@fieldposition+1,255-@fieldposition)) =
'closed'
begin
select @fieldposition=0
WHILE @fieldposition  < 225
BEGIN
    select @fieldposition = @fieldposition+1

    if substring(@NewTarget,@fieldposition,1) = '.'
        break

END
Select
@NewTarget=rTrim(substring(@NewTarget,1,@fieldposition-1))+rtrim(@newprefix+rTrim(substring(@NewTarget,@fieldposition,255)))
Select @msg = 'This is an automatically generated FTP notification
message:'
delete from texttab
INSERT into texttab
select 'This message was generated on ' +
convert(varchar(25),getdate()) + char(13) +
    replicate ('_', 45) + char(13)
select
    @recips = ''' + rtrim(notify) + ''',
    @txt = char(13)+
    'THE ATEMPTED FTP FILE TRANSFER TO  SERVICES '+ char(13)
    +'ON ' +UPPER(convert(varchar(25),LOGTIME)) + ' FROM ' +
upper(rtrim(description)) + char(13)
    +'WAS NOT SUCCESSFULLY RECEIVED.'+ char(13)+ char(13)
    +'IF NECESSARY PLEASE CONTACT THE APPROPRIATE PARTY' + char(13)
    +'TO HAVE THE FILE RESENT.' ,
    --@sub = 'FTPLog Notification ALERT from '  + rtrim(description) + '''
    @sub = 'FTPLog Notification ALERT from '  + rtrim(description) + ''

    from FTPLogs l , FTPNotify n
    where LogID = @logId and
    lower(l.username) = lower(n.username)
select @txtptr = textptr(c1) from texttab
UPDATETEXT texttab.c1 @txtptr NULL 0 with log @txt
SELECT @cmd = 'SELECT c1 FROM FTPLogs.dbo.texttab'
exec master.dbo.xp_sendmail
    @recipients = " + @recips + ",
    @message = " + @msg + ",
    @query =  "+ @cmd + ",
    @subject = " + @sub + ",
    @no_header = 'TRUE', @width = 2500
delete  from texttab
end



GO




SP2:

CREATE Procedure sp_MailNotify_New
As
Declare @id int
Declare @CStatus int
Declare @fieldposition int
declare @FailedFlag varchar(255)
Declare C_getLog cursor for
select LogId,Operation from FTPLogs where notified = 0 order by LogID
Open C_getLog
Fetch Next from C_getLog into @id,@FailedFlag
select @CStatus = @@FETCH_STATUS
select @fieldposition=0
WHILE @fieldposition  < 225
BEGIN
    select @fieldposition = @fieldposition+1

    if substring(@FailedFlag,@fieldposition,1) = ']'
        break

END
select @FailedFlag =
lower(substring(@FailedFlag,@fieldposition+1,255-@fieldposition))
while (@CStatus <> -1 and (@FailedFlag = 'closed' or @FailedFlag =
'created'))
begin
    select 'THE ID is '  + convert(char(8),@id)
    execute sp_FTPNotify_new @id
    update FTPLogs set notified = 1 where LogId = @id
    Fetch Next from C_getLog into @id,@FailedFlag
    select @CStatus = @@FETCH_STATUS
    select @fieldposition=0
    WHILE @fieldposition  < 225
    BEGIN
        select @fieldposition = @fieldposition+1

        if substring(@FailedFlag,@fieldposition,1) = ']'
            break

    END

    select @FailedFlag =
lower(substring(@FailedFlag,@fieldposition+1,255-@fieldposition))
end
update FTPLogs set notified = 1 where LogId <= @id
Close C_getLog
Deallocate C_getLog

GO


In the scheduled jobs: exec exec sp_MailNotify_New

Strange Timeout problem.
Book Recommendations
Question about functions
Implicit conversion from string to date
Speeding up queries using TOP