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! DBCC CHECKDB - consistency errors

Hello All,
After restoring the SQL Server 2000 user database backup into one of the SQL
Server 2000 instances I get database consistency errors. I restored this
database a couple of times and every time I get the errors; however they are
not consistent - different types of errors, different tables and indexes get
affected. I restored the same database backup into a different server and
ran dbcc checkdb over it - no errors occurred. Something causes corruption
in the database on one particular server.
Here are the errors:
DBCC results for 'tTREE_COMPONENTS'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1467308437, index ID 0: Page (1:251481) could not be processed.
See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1467308437, index ID 0, page (1:251481), row 44. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 87 and 57.
There are 663 rows in 16 pages for object 'tTREE_COMPONENTS'.
DBCC results for 'tACCUMULATORS'.
There are 10234724 rows in 97149 pages for object 'tACCUMULATORS'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'tACCUMULATORS' (object ID 1467308437).
DBCC results for 'WATSTARTDATETP'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1905493917, index ID 4: Page (3:224866) could not be processed.
See other errors for details.
Server: Msg 8941, Level 16, State 1, Line 1
Table error: Object ID 1905493917, index ID 4, page (3:224866). Test (sorted
[i].offset >= PAGEHEADSIZE) failed. Slot 159, offset 0x1 is invalid.
Server: Msg 8942, Level 16, State 1, Line 1
Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
(sorted[i].offset >= max) failed. Slot 0, offset 0x9f overlaps with the prior
row.
There are 4 rows in 1 pages for object 'WATSTARTDATETP'.
DBCC results for 'ACCRUALTRAN'.
There are 5919148 rows in 150538 pages for object 'ACCRUALTRAN'.
CHECKDB found 0 allocation errors and 3 consistency errors in table
'ACCRUALTRAN' (object ID 1905493917).
Could it be that our IO subsystem is causing corruption? I am thinking
about running SQLIOSim and SQLIOStress..
If you could please take a look at the errors and give me your thoughts it
would be greatly appreciated.
Thank you in advance,
JuliaJulia,
My only suspicion, after reading your description, is that you are probably
running into a bad disk or array. And, if so, you need to determine if that
is true and fix it right now.
RLF
"DBCC CHECKDB - consistency errors"
<DBCCCHECKDBconsistencyerrors@.discussions.microsoft.com> wrote in message
news:94E38ACE-9F29-4B76-8B3A-2CF1467AC9B2@.microsoft.com...
> Hello All,
> After restoring the SQL Server 2000 user database backup into one of the
> SQL
> Server 2000 instances I get database consistency errors. I restored this
> database a couple of times and every time I get the errors; however they
> are
> not consistent - different types of errors, different tables and indexes
> get
> affected. I restored the same database backup into a different server and
> ran dbcc checkdb over it - no errors occurred. Something causes
> corruption
> in the database on one particular server.
> Here are the errors:
> DBCC results for 'tTREE_COMPONENTS'.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1467308437, index ID 0: Page (1:251481) could not be processed.
> See other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 1467308437, index ID 0, page (1:251481), row 44.
> Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 87 and 57.
> There are 663 rows in 16 pages for object 'tTREE_COMPONENTS'.
> DBCC results for 'tACCUMULATORS'.
> There are 10234724 rows in 97149 pages for object 'tACCUMULATORS'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'tACCUMULATORS' (object ID 1467308437).
> DBCC results for 'WATSTARTDATETP'.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1905493917, index ID 4: Page (3:224866) could not be processed.
> See other errors for details.
> Server: Msg 8941, Level 16, State 1, Line 1
> Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
> (sorted
> [i].offset >= PAGEHEADSIZE) failed. Slot 159, offset 0x1 is invalid.
> Server: Msg 8942, Level 16, State 1, Line 1
> Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
> (sorted[i].offset >= max) failed. Slot 0, offset 0x9f overlaps with the
> prior
> row.
> There are 4 rows in 1 pages for object 'WATSTARTDATETP'.
> DBCC results for 'ACCRUALTRAN'.
> There are 5919148 rows in 150538 pages for object 'ACCRUALTRAN'.
> CHECKDB found 0 allocation errors and 3 consistency errors in table
> 'ACCRUALTRAN' (object ID 1905493917).
> Could it be that our IO subsystem is causing corruption? I am thinking
> about running SQLIOSim and SQLIOStress..
> If you could please take a look at the errors and give me your thoughts it
> would be greatly appreciated.
> Thank you in advance,
> Julia
>
>|||Russell, thank you so much for such a quick response. In my company in order
to make the server administrators to run Dell hardware diagnostics I
basically need to prove that itâ's a bad disk issue, but there are no errors
in the Event Viewer log and I donâ't think we have the smoking gun in SQL
Server 2000 that would point to the cause of the consistency error?
"Russell Fields" wrote:
> Julia,
> My only suspicion, after reading your description, is that you are probably
> running into a bad disk or array. And, if so, you need to determine if that
> is true and fix it right now.
> RLF
> "DBCC CHECKDB - consistency errors"
> <DBCCCHECKDBconsistencyerrors@.discussions.microsoft.com> wrote in message
> news:94E38ACE-9F29-4B76-8B3A-2CF1467AC9B2@.microsoft.com...
> > Hello All,
> >
> > After restoring the SQL Server 2000 user database backup into one of the
> > SQL
> > Server 2000 instances I get database consistency errors. I restored this
> > database a couple of times and every time I get the errors; however they
> > are
> > not consistent - different types of errors, different tables and indexes
> > get
> > affected. I restored the same database backup into a different server and
> > ran dbcc checkdb over it - no errors occurred. Something causes
> > corruption
> > in the database on one particular server.
> >
> > Here are the errors:
> >
> > DBCC results for 'tTREE_COMPONENTS'.
> > Server: Msg 8928, Level 16, State 1, Line 1
> > Object ID 1467308437, index ID 0: Page (1:251481) could not be processed.
> > See other errors for details.
> > Server: Msg 8944, Level 16, State 1, Line 1
> > Table error: Object ID 1467308437, index ID 0, page (1:251481), row 44.
> > Test
> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 87 and 57.
> > There are 663 rows in 16 pages for object 'tTREE_COMPONENTS'.
> >
> > DBCC results for 'tACCUMULATORS'.
> > There are 10234724 rows in 97149 pages for object 'tACCUMULATORS'.
> > CHECKDB found 0 allocation errors and 2 consistency errors in table
> > 'tACCUMULATORS' (object ID 1467308437).
> >
> > DBCC results for 'WATSTARTDATETP'.
> > Server: Msg 8928, Level 16, State 1, Line 1
> > Object ID 1905493917, index ID 4: Page (3:224866) could not be processed.
> > See other errors for details.
> >
> > Server: Msg 8941, Level 16, State 1, Line 1
> > Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
> > (sorted
> > [i].offset >= PAGEHEADSIZE) failed. Slot 159, offset 0x1 is invalid.
> >
> > Server: Msg 8942, Level 16, State 1, Line 1
> > Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
> > (sorted[i].offset >= max) failed. Slot 0, offset 0x9f overlaps with the
> > prior
> > row.
> > There are 4 rows in 1 pages for object 'WATSTARTDATETP'.
> >
> > DBCC results for 'ACCRUALTRAN'.
> > There are 5919148 rows in 150538 pages for object 'ACCRUALTRAN'.
> > CHECKDB found 0 allocation errors and 3 consistency errors in table
> > 'ACCRUALTRAN' (object ID 1905493917).
> >
> > Could it be that our IO subsystem is causing corruption? I am thinking
> > about running SQLIOSim and SQLIOStress..
> >
> > If you could please take a look at the errors and give me your thoughts it
> > would be greatly appreciated.
> >
> > Thank you in advance,
> >
> > Julia
> >
> >
> >
>
>|||Julia,
Last time I had a similar problem, I finally proved it by running:
CHKDSK D:
It spat out errors that none of the monitoring tools had found. In my case,
it was not the disk, but the RAID array that had gone bad. There were no
Event log errors, no (for us) HP / Compaq warning messages, etc. But the
array was still bad.
Of course, I could be wrong, but this is my best guess based on the
symptoms.
RLF
(Going home now, so I cannot follow up any more tonight.)
"DBCC CHECKDB - consistency errors"
<DBCCCHECKDBconsistencyerrors@.discussions.microsoft.com> wrote in message
news:064B60C9-080E-4B5D-9657-23FDBD6B0C1A@.microsoft.com...
> Russell, thank you so much for such a quick response. In my company in
> order
> to make the server administrators to run Dell hardware diagnostics I
> basically need to prove that it's a bad disk issue, but there are no
> errors
> in the Event Viewer log and I don't think we have the smoking gun in SQL
> Server 2000 that would point to the cause of the consistency error?
> "Russell Fields" wrote:
>> Julia,
>> My only suspicion, after reading your description, is that you are
>> probably
>> running into a bad disk or array. And, if so, you need to determine if
>> that
>> is true and fix it right now.
>> RLF
>> "DBCC CHECKDB - consistency errors"
>> <DBCCCHECKDBconsistencyerrors@.discussions.microsoft.com> wrote in message
>> news:94E38ACE-9F29-4B76-8B3A-2CF1467AC9B2@.microsoft.com...
>> > Hello All,
>> >
>> > After restoring the SQL Server 2000 user database backup into one of
>> > the
>> > SQL
>> > Server 2000 instances I get database consistency errors. I restored
>> > this
>> > database a couple of times and every time I get the errors; however
>> > they
>> > are
>> > not consistent - different types of errors, different tables and
>> > indexes
>> > get
>> > affected. I restored the same database backup into a different server
>> > and
>> > ran dbcc checkdb over it - no errors occurred. Something causes
>> > corruption
>> > in the database on one particular server.
>> >
>> > Here are the errors:
>> >
>> > DBCC results for 'tTREE_COMPONENTS'.
>> > Server: Msg 8928, Level 16, State 1, Line 1
>> > Object ID 1467308437, index ID 0: Page (1:251481) could not be
>> > processed.
>> > See other errors for details.
>> > Server: Msg 8944, Level 16, State 1, Line 1
>> > Table error: Object ID 1467308437, index ID 0, page (1:251481), row 44.
>> > Test
>> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 87 and 57.
>> > There are 663 rows in 16 pages for object 'tTREE_COMPONENTS'.
>> >
>> > DBCC results for 'tACCUMULATORS'.
>> > There are 10234724 rows in 97149 pages for object 'tACCUMULATORS'.
>> > CHECKDB found 0 allocation errors and 2 consistency errors in table
>> > 'tACCUMULATORS' (object ID 1467308437).
>> >
>> > DBCC results for 'WATSTARTDATETP'.
>> > Server: Msg 8928, Level 16, State 1, Line 1
>> > Object ID 1905493917, index ID 4: Page (3:224866) could not be
>> > processed.
>> > See other errors for details.
>> >
>> > Server: Msg 8941, Level 16, State 1, Line 1
>> > Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
>> > (sorted
>> > [i].offset >= PAGEHEADSIZE) failed. Slot 159, offset 0x1 is invalid.
>> >
>> > Server: Msg 8942, Level 16, State 1, Line 1
>> > Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
>> > (sorted[i].offset >= max) failed. Slot 0, offset 0x9f overlaps with the
>> > prior
>> > row.
>> > There are 4 rows in 1 pages for object 'WATSTARTDATETP'.
>> >
>> > DBCC results for 'ACCRUALTRAN'.
>> > There are 5919148 rows in 150538 pages for object 'ACCRUALTRAN'.
>> > CHECKDB found 0 allocation errors and 3 consistency errors in table
>> > 'ACCRUALTRAN' (object ID 1905493917).
>> >
>> > Could it be that our IO subsystem is causing corruption? I am thinking
>> > about running SQLIOSim and SQLIOStress..
>> >
>> > If you could please take a look at the errors and give me your thoughts
>> > it
>> > would be greatly appreciated.
>> >
>> > Thank you in advance,
>> >
>> > Julia
>> >
>> >
>> >
>>|||> I restored the same database backup into a different server and
> ran dbcc checkdb over it - no errors occurred. Something causes
> corruption
> in the database on one particular server.
I agree with Russell's analysis. I think the facts that the corruption only
occurs on one server and manifests itself in different ways ought be enough
proof for the server admins to run hardware diagnostics.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"DBCC CHECKDB - consistency errors"
<DBCCCHECKDBconsistencyerrors@.discussions.microsoft.com> wrote in message
news:064B60C9-080E-4B5D-9657-23FDBD6B0C1A@.microsoft.com...
> Russell, thank you so much for such a quick response. In my company in
> order
> to make the server administrators to run Dell hardware diagnostics I
> basically need to prove that itâ's a bad disk issue, but there are no
> errors
> in the Event Viewer log and I donâ't think we have the smoking gun in SQL
> Server 2000 that would point to the cause of the consistency error?
> "Russell Fields" wrote:
>> Julia,
>> My only suspicion, after reading your description, is that you are
>> probably
>> running into a bad disk or array. And, if so, you need to determine if
>> that
>> is true and fix it right now.
>> RLF
>> "DBCC CHECKDB - consistency errors"
>> <DBCCCHECKDBconsistencyerrors@.discussions.microsoft.com> wrote in message
>> news:94E38ACE-9F29-4B76-8B3A-2CF1467AC9B2@.microsoft.com...
>> > Hello All,
>> >
>> > After restoring the SQL Server 2000 user database backup into one of
>> > the
>> > SQL
>> > Server 2000 instances I get database consistency errors. I restored
>> > this
>> > database a couple of times and every time I get the errors; however
>> > they
>> > are
>> > not consistent - different types of errors, different tables and
>> > indexes
>> > get
>> > affected. I restored the same database backup into a different server
>> > and
>> > ran dbcc checkdb over it - no errors occurred. Something causes
>> > corruption
>> > in the database on one particular server.
>> >
>> > Here are the errors:
>> >
>> > DBCC results for 'tTREE_COMPONENTS'.
>> > Server: Msg 8928, Level 16, State 1, Line 1
>> > Object ID 1467308437, index ID 0: Page (1:251481) could not be
>> > processed.
>> > See other errors for details.
>> > Server: Msg 8944, Level 16, State 1, Line 1
>> > Table error: Object ID 1467308437, index ID 0, page (1:251481), row 44.
>> > Test
>> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 87 and 57.
>> > There are 663 rows in 16 pages for object 'tTREE_COMPONENTS'.
>> >
>> > DBCC results for 'tACCUMULATORS'.
>> > There are 10234724 rows in 97149 pages for object 'tACCUMULATORS'.
>> > CHECKDB found 0 allocation errors and 2 consistency errors in table
>> > 'tACCUMULATORS' (object ID 1467308437).
>> >
>> > DBCC results for 'WATSTARTDATETP'.
>> > Server: Msg 8928, Level 16, State 1, Line 1
>> > Object ID 1905493917, index ID 4: Page (3:224866) could not be
>> > processed.
>> > See other errors for details.
>> >
>> > Server: Msg 8941, Level 16, State 1, Line 1
>> > Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
>> > (sorted
>> > [i].offset >= PAGEHEADSIZE) failed. Slot 159, offset 0x1 is invalid.
>> >
>> > Server: Msg 8942, Level 16, State 1, Line 1
>> > Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
>> > (sorted[i].offset >= max) failed. Slot 0, offset 0x9f overlaps with the
>> > prior
>> > row.
>> > There are 4 rows in 1 pages for object 'WATSTARTDATETP'.
>> >
>> > DBCC results for 'ACCRUALTRAN'.
>> > There are 5919148 rows in 150538 pages for object 'ACCRUALTRAN'.
>> > CHECKDB found 0 allocation errors and 3 consistency errors in table
>> > 'ACCRUALTRAN' (object ID 1905493917).
>> >
>> > Could it be that our IO subsystem is causing corruption? I am thinking
>> > about running SQLIOSim and SQLIOStress..
>> >
>> > If you could please take a look at the errors and give me your thoughts
>> > it
>> > would be greatly appreciated.
>> >
>> > Thank you in advance,
>> >
>> > Julia
>> >
>> >
>> >
>>