Help on SP: Question Reposted

Hi All,
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=3DTarget , @.newprefix =3D
rtrim(rtrim(convert(char,logtime,12))+co
nvert(char,LogID)),
@.FailedFlag =3D Operation,@.LogTime =3D LogTime
from FTPLogs l , FTPNotify n
where LogID =3D @.logId and
lower(l.username) =3D lower(n.username)
select @.fieldposition=3D0
WHILE @.fieldposition < 225
BEGIN
select @.fieldposition =3D @.fieldposition+1
if substring(@.FailedFlag,@.fieldposition,1) =3D ']'
break
END
if lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition))
=3D'created'
begin
select @.fieldposition=3D0
WHILE @.fieldposition < 225
BEGIN
select @.fieldposition =3D @.fieldposition+1
if substring(@.NewTarget,@.fieldposition,1) =3D '.'
break
END
Select
@.NewTarget=3DrTrim(substring(@.NewTarget,
1,@.fieldposition-1))+rtrim(@.newpref=
ix=AD+rTrim(substring(@.NewTarget,@.fieldp
osition,255)))
Select @.msg =3D '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 =3D ' + rtrim(notify) + ',
@.txt =3D
'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 =3D 'FTPLog Notification from " + rtrim(description) + ',
@.newfilename =3D rtrim(@.NewTarget),
@.oldfilename =3D rtrim(l.username)+''+ rtrim(Target)
from FTPLogs l , FTPNotify n
where LogID =3D @.logId and
lower(l.username) =3D lower(n.username)
select @.txtptr =3D textptr(c1) from texttab
UPDATETEXT texttab.c1 @.txtptr NULL 0 with log @.txt
SELECT @.cmd =3D 'SELECT c1 FROM FTPLogs.dbo.texttab'
exec master.dbo.xp_sendmail
@.recipients =3D ' + @.recips + ',
@.message =3D ' + @.msg + ',
@.query =3D '+ @.cmd + ',
@.subject =3D ' + @.sub + ',
@.no_header =3D 'TRUE', @.width =3D 2500
delete from texttab
select @.renamestring =3D 'rename \\W2k3-S1\data' + rtrim(@.oldfilename) +
' '+ rtrim(@.newfilename)
print @.renamestring
declare @.result int
declare @.querystring char(200)
EXEC @.result =3D master.dbo.xp_cmdshell @.renamestring
if (@.result =3D 1)
begin
select @.querystring =3D'SELECT logid,substring(username,1,20)
username,logtime,bytesrecvd,substring(ta
rget,1,50) filename FROM
ftplogs.dbo.ftplogs where logid =3D '+ convert(char,@.logId)
exec master.dbo.xp_sendmail @.recipients =3D 'ma...@.inc.com',
@.query =3D '" + @.querystring + " ' ,
@.subject =3D'Failed Rename',
@.message =3D'The following file could not be renamed.',
@.attach_results =3D 'FALSE', @.width =3D 250
end
end
Else
if lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition)) =3D
'closed'
begin
select @.fieldposition=3D0
WHILE @.fieldposition < 225
BEGIN
select @.fieldposition =3D @.fieldposition+1
if substring(@.NewTarget,@.fieldposition,1) =3D '.'
break
END
Select
@.NewTarget=3DrTrim(substring(@.NewTarget,
1,@.fieldposition-1))+rtrim(@.newpref=
ix=AD+rTrim(substring(@.NewTarget,@.fieldp
osition,255)))
Select @.msg =3D '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 =3D ''' + rtrim(notify) + ''',
@.txt =3D 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 =3D 'FTPLog Notification ALERT from ' +
rtrim(description) + '''
@.sub =3D 'FTPLog Notification ALERT from ' + rtrim(description)
+ ''
from FTPLogs l , FTPNotify n
where LogID =3D @.logId and
lower(l.username) =3D lower(n.username)
select @.txtptr =3D textptr(c1) from texttab
UPDATETEXT texttab.c1 @.txtptr NULL 0 with log @.txt
SELECT @.cmd =3D 'SELECT c1 FROM FTPLogs.dbo.texttab'
exec master.dbo.xp_sendmail
@.recipients =3D " + @.recips + ",
@.message =3D " + @.msg + ",
@.query =3D "+ @.cmd + ",
@.subject =3D " + @.sub + ",
@.no_header =3D 'TRUE', @.width =3D 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 =3D 0 order by LogID
Open C_getLog
Fetch Next from C_getLog into @.id,@.FailedFlag
select @.CStatus =3D @.@.FETCH_STATUS
select @.fieldposition=3D0
WHILE @.fieldposition < 225
BEGIN
select @.fieldposition =3D @.fieldposition+1
if substring(@.FailedFlag,@.fieldposition,1) =3D ']'
break
END
select @.FailedFlag =3D
lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition))
while (@.CStatus <> -1 and (@.FailedFlag =3D 'closed' or @.FailedFlag =3D
'created'))
begin
select 'THE ID is ' + convert(char(8),@.id)
execute sp_FTPNotify_new @.id
update FTPLogs set notified =3D 1 where LogId =3D @.id
Fetch Next from C_getLog into @.id,@.FailedFlag
select @.CStatus =3D @.@.FETCH_STATUS
select @.fieldposition=3D0
WHILE @.fieldposition < 225
BEGIN
select @.fieldposition =3D @.fieldposition+1
if substring(@.FailedFlag,@.fieldposition,1) =3D ']'
break
END
select @.FailedFlag =3D
lower(substring(@.FailedFlag,@.fieldpositi
on+1,255-@.fieldposition))
end
update FTPLogs set notified =3D 1 where LogId <=3D @.id
Close C_getLog
Deallocate C_getLog
GO=20
In the scheduled jobs: exec exec sp_MailNotify_NewHi
A quick view of your code in QA gives me an impression that the string is
not properly quoted here.. you may check meanwhile:
rtrim(description) + '''
@.sub = 'FTPLog Notification ALERT from ' + rtrim(description)
+ ''
should probably be:
rtrim(description) + ''
@.sub = 'FTPLog Notification ALERT from ' + rtrim(description)
+ ''
and also post error msg by executing it manually so as to better help you
Regards
R.D

> Hi All,
> 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(@.newprefi
x_+rTrim(substring(@.NewTarget,@.fieldpos
ition,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 = '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,@.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(@.newprefi
x_+rTrim(substring(@.NewTarget,@.fieldpos
ition,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
>|||Hi mamun
I would have expected:
@.sub = 'FTPLog Notification from " + rtrim(description) + ',
to be
@.sub = 'FTPLog Notification from "' + rtrim(description) + '"',
or
@.sub = 'FTPLog Notification from ' + rtrim(description),
:)
Other things that may be useful are
1. Using Charindex instead of looping through the strings to find a characte
r
2. Writing the code to copy with spaces in the file name
3. Add error handling
4. Make sure that your rename has worked before you send the email.
John
"microsoft.public.dotnet.languages.vb" wrote:

> Hi All,
> 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(@.newprefi
x_+rTrim(substring(@.NewTarget,@.fieldpos
ition,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 = '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,@.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(@.newprefi
x_+rTrim(substring(@.NewTarget,@.fieldpos
ition,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
>

Help Needed in writing a function to help daily nightmare with Debugging.

I debug SPS on a daily basis and I use SQL profiler to help me trace where the problem is.
Once I have established which SP is the main problem I need to debug the line of code.

What I do is Cut and Paste the SQL Profiler details and populate all the parameters,sometimes that can be 30 and more..

Now what i thought is to write an SP or Function where I pass :

SP name and Parameters that profiler genererates
and returns me Declare Statements and Set Statements with parameters filled.

EG

Profiler Returns
Customer_INSERT,20,'JO',BLOGG','5 LONDON ROAD'

I would call my new SP =PopulateSPParams and cut and paste the profiler's string
PopulateSPParams 'Customer_Insert,20,'JO',BLOGG','5 LONDON ROAD'

this will RETURN THE FOLLOWING THAT WILL IMMENSILY HELP MY DAILY PROGRAMMING.

DECLARE
@.CustomerID int,
@.CustomerName varchar(50),
@.CustomerSurname varchar(50),
@.CustomerAddress varchar(100)

SET @.CustomerID =1
SET @.CustomerName='JO'
SET @.CustomerSurname='BLOGG'
SET @.CustomerAddress='5 London Road'

Can you help in writing something that generates and populate parameters?

Check this out. It only handles three parms, but it certainly could be made to handle more:

create procedure buildParms
(
@.procName nvarchar(128),
@.parm1 varchar(max),
@.parm2 varchar(max),
@.parm3 varchar(max)
) as
begin
set nocount on

select 'DECLARE @.retval int'
union all
select ' ,' + parameters.name + ' ' + types.name +
case
when types.name like 'n%char' then '(' + cast(parameters.max_length /2 as varchar(10)) + ')'
when types.name like '%char' then '(' +cast(parameters.max_length as varchar(10)) + ')'
when types.name like '%binary' then '(' +cast(parameters.max_length as varchar(10)) + ')'
when types.name = 'numeric' then '(' + cast(parameters.precision as varchar(10)) + ',' + cast(parameters.scale as varchar(10)) + ')'
else '' end
from sys.parameters
join sys.types
on types.user_type_id = parameters.user_type_id
where object_name(object_id) = @.procName
union all
select ' '
union all

select 'SET ' + parameters.name + ' = ' +
case when types.name like '%char' then '''' else '' end +
case parameters.parameter_id --to add more parms, add to this list
when 1 then @.parm1
when 2 then @.parm2
when 3 then @.parm3
end +
case when types.name like '%char' then '''' else '' end
from sys.parameters
join sys.types
on types.user_type_id = parameters.user_type_id
where object_name(object_id) = @.procName

union all
select ' '
union all
select 'EXECUTE @.retval = ' + @.procName
union all
select ' ' + parameters.name + ' = ' + parameters.name
+ case when parameters.is_output = 1 then ' OUTPUT ' else '' end +
case when parameter_id <> (select max(parameter_id) from sys.parameters where object_name(object_id) = @.procName)
then ',' else '' end
from sys.parameters
join sys.types
on types.user_type_id = parameters.user_type_id
where object_name(object_id) = @.procName
end
go

--test with this proc

alter procedure test$paramTest
(
@.value1 int,
@.value2 nvarchar(10) output,
@.value3 numeric (6,2)
) as
select @.value1, @.value2, @.value3
go

Execute this

buildParms 'test$paramTest','1','x','200'

returns:

DECLARE @.retval int
,@.value1 int
,@.value2 nvarchar(10)
,@.value3 numeric(6,2)

SET @.value1 = 1
SET @.value2 = 'x'
SET @.value3 = 200

EXECUTE @.retval = test$paramTest
@.value1 = @.value1,
@.value2 = @.value2 OUTPUT ,
@.value3 = @.value3

|||

Fantastic.Sorry for late reply.I could not access Computer.

Let me check.Wll be back to you.

I was thinking of passing a delimited string so that it could handle all sorts of params.

Very very grateful!!!!!!!!!

|||

Thanks

This works like a dream.I beleive this only works on sql server 2005

To make it work on sql 2000 I will change the varchar(max) to varchar(8000) But still get errors on all the sys stuff.

Any more suggestions?

very very grateful for your help

|||

Try this on 2000. I used the information_schema views this time. I thought of doing this the first time, but thought it would be too much bother (actually it was about the same:)

alter procedure buildParms
(
@.procName nvarchar(128),
@.parm1 varchar(8000),
@.parm2 varchar(8000),
@.parm3 varchar(8000)
) as
begin
set nocount on

select 'DECLARE @.retval int'
union all
select ' ,' + PARAMETER_NAME + ' ' + data_type +
case
when data_type like 'n%char' then '(' + cast(CHARACTER_MAXIMUM_LENGTH /2 as varchar(10)) + ')'
when data_type like '%char' then '(' +cast(CHARACTER_MAXIMUM_LENGTH as varchar(10)) + ')'
when data_type like '%binary' then '(' +cast(CHARACTER_MAXIMUM_LENGTH as varchar(10)) + ')'
when data_type = 'numeric' then '(' + cast(NUMERIC_PRECISION as varchar(10)) + ',' + cast(NUMERIC_SCALE as varchar(10)) + ')'
else '' end
from information_schema.parameters
where SPECIFIC_NAME = @.procName
union all
select ' '

union all

select 'SET ' + PARAMETER_NAME + ' = ' +
case when data_type like '%char' then '''' else '' end +
case ordinal_position --to add more parms, add to this list
when 1 then @.parm1
when 2 then @.parm2
when 3 then @.parm3
end +
case when data_type like '%char' then '''' else '' end
from information_schema.parameters
where SPECIFIC_NAME = @.procName

union all
select ' '
union all
select 'EXECUTE @.retval = ' + @.procName
union all
select ' ' + PARAMETER_NAME + ' = ' + PARAMETER_NAME
+ case when PARAMETER_MODE = 'INOUT' then ' OUTPUT ' else '' end +
case when ordinal_position <> (select max(ordinal_position) from information_schema.parameters where SPECIFIC_NAME = @.procName)
then ',' else '' end

from information_schema.parameters
where SPECIFIC_NAME = @.procName

end
go

|||

What can I say?

You made my day ,

Thanks a lot I had started to convert it to 2000 but I had got stuck on the join between systypes and Information_schema.parameters i could not find the equivalent to join on.

thanks a lot again