Hi All,
I have been working on the following problem for more than two w
s
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 w
s.
I thank you in advance million times for your help.
Best regards,
mamunMay be there are files where the name include spaces, in these cases you hav
e
to enclose the path between double quote.
Example:
exec master..xp_cmdshell 'ren "\temp\test 1.txt" test1.txt'
go
AMB
"microsoft.public.dotnet.languages.vb" wrote:
> Hi All,
> I have been working on the following problem for more than two w
s
> 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 w
s.
> I thank you in advance million times for your help.
> Best regards,
> mamun
>|||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))+co
nvert(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,@.fieldpositi
on+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,@.fieldpositio
n,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(ta
rget,1,50) filename FROM
ftplogs.dbo.ftplogs where logid = '+ convert(char,@.logId)
exec master.dbo.xp_sendmail @.recipients = 'mamun@.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,@.fieldpositi
on+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,@.fieldpositio
n,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,@.fieldpositi
on+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,@.fieldpositi
on+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
