March 22, 2006 at 9:20 am
I am testing a converted SQL 2000 database on SQL 2005 Developer Edition (Database has been tested with 8.0 and 9.0 compatibility options). We have several stored procedures which run on publish which edit text and ntext fields in the tables (stripping out stored html which our reporting system can't handle).
I have had several problems after using
sp_tableoption '[tablename]', 'text in row', '0'
and then executing the following
SELECT
datalength([fieldname]),
[primarykeyfield],
PATINDEX('%<BR><BR>%',
[fieldname])-1, 8
from
[tablename]
WHERE
PATINDEX('%<BR><BR>%', [fieldname]) > 0
which populates a table variable with the primary key id's of the records to be edited.
I get errors indicating data corruption
'Database ID 12, page (1:228720), slot 3 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE.
'
after using dbcc checktable('tablename',repair_allow_data_loss) which deletes the text or ntext data the sp runs fine (at that point it has no data identified to select). This same sp runs without incident on sql2000. Anyone knwo what I may need to change? Or should this work without issue.
I do know that the recommendation with sql 2005 is to use varchar(max) or nvarchar(max) instead of text or ntext but I am attempting to maintain code that will work on both versions in this transtiional phase.
Full code:
-------------------------------------------------------------------------------------
declare @currentdb varchar(50)
set @currentdb = db_name()
declare @isbulkcopy varchar(10)
select @isbulkcopy = case databaseproperty(@currentdb, 'IsBulkCopy') when 1 then 'true' else 'false' end
if @isbulkcopy = 'false'
begin
print 'Turning on bulk copy for these operations, original setting was (' + @isbulkcopy + ')'
print ''
EXEC sp_dboption @currentdb, 'select into/bulkcopy', 'true'
end
declare @tableinfo table
(
reccounter int identity,
table_name varchar(50),
column_name varchar(50),
data_type varchar(50),
primary_key_column varchar(50),
table_type varchar(50)
)
insert into @tableinfo
SELECT
c.table_name,
c.column_name,
c.data_type,
cu.column_name primary_key_column,
t.table_type
from
information_schema.tables t
left join
information_schema.columns c
on c.table_name = t.table_name
left join
(
select a.table_name, a.column_name
from
vwindexlist a
inner join
information_schema.columns b
on
b.table_name = a.table_name and
b.column_name = a.column_name
where b.data_type = 'INT'
group by a.table_name, a.column_name
having
max(convert(int,primary_key)) = 1
) cu
on cu.table_name = c.table_name
where t.table_type = 'base table' and (c.data_type = 'ntext' or c.data_type = 'text')
and cu.column_name is not null
and c.column_name <> 'Comments'
declare @table_name varchar(50)
declare @column_name varchar(50)
declare @primary_key_column varchar(50)
declare @data_type varchar(50)
declare @counter int
declare @maxcounter int
select @counter=1, @maxcounter = (select ISNULL(max(reccounter),1) from @tableinfo) + 1
while @counter < @maxcounter
begin
select @table_name = table_name,
@column_name = column_name,
@primary_key_column = primary_key_column,
@data_type = data_type,
@counter = reccounter + 1
from @tableinfo
where reccounter = @counter
Print 'Processing ' + @table_name + '.' + @column_name
exec (
'
set nocount on
DECLARE @TEXTINROWLIMIT INT
SELECT @TEXTINROWLIMIT = OBJECTPROPERTY(OBJECT_ID(''' + @table_name + '''), ''TableTextInRowLimit'')
EXEC sp_tableoption ''' + @table_name + ''', ''text in row'', ''off''
DECLARE @IDTABLE TABLE
(
RECCOUNTER INT IDENTITY,
DATALENGTH int,
TABLEID INT,
BRINDEX INT,
BRLENGTH INT
)
INSERT INTO @IDTABLE
SELECT
datalength([' + @column_name + ']),
[' + @primary_key_column + '],
PATINDEX(''%<BR><BR>%'',
[' + @column_name + '])-1, 8
from
[' + @table_name + ']
WHERE
PATINDEX(''%<BR><BR>%'', [' + @column_name + ']) > 0
DECLARE @COUNTER INT
DECLARE @MAXCOUNTER INT
DECLARE @ptrval binary(16)
DECLARE @TABLEID INT
DECLARE @BRINDEX INT
DECLARE @BRLENGTH INT
DECLARE @INSTANCECOUNT INT
SET @INSTANCECOUNT = 0
SELECT @COUNTER = 1, @MAXCOUNTER = (SELECT ISNULL(MAX(RECCOUNTER),0) FROM @IDTABLE) + 1
WHILE @COUNTER < @MAXCOUNTER
BEGIN
SELECT @BRINDEX = BRINDEX, @BRLENGTH=BRLENGTH, @TABLEID=TABLEID, @COUNTER = RECCOUNTER + 1 FROM @IDTABLE WHERE RECCOUNTER = @COUNTER
SELECT @PTRVAL = TEXTPTR([' + @column_name + '])
FROM [' + @table_name + ']
WHERE [' + @table_name + '].[' + @primary_key_column + '] = @TABLEID
IF @PTRVAL IS NOT NULL
BEGIN
WHILE @BRINDEX > 0
BEGIN
UPDATETEXT [' + @table_name + '].[' + @column_name + '] @PTRVAL @BRINDEX @BRLENGTH ''<BR> <BR>''
SET @INSTANCECOUNT = @INSTANCECOUNT + 1
SELECT @BRINDEX = PATINDEX(''%<BR><BR>%'', [' + @column_name + '])-1 FROM ' + @table_name + ' WHERE [' + @primary_key_column + '] = @TABLEID
END
END
END
PRINT '' Updated ('' + convert(varchar,@maxcounter-1) + '') records, ('' + convert(varchar,@instancecount) + '') occurrences in ' + @table_name + '.' + @column_name + '''
PRINT ''''
EXEC sp_tableoption ''' + @table_name + ''', ''text in row'', @TEXTINROWLIMIT
'
)
end
if @isbulkcopy = 'false'
begin
print 'Returning bulk copy setting for this database to (' + @isbulkcopy + ')'
print ''
EXEC sp_dboption @currentdb, 'select into/bulkcopy', @isbulkcopy
end
March 23, 2006 at 10:56 am
Could LOB_COMPACTION on the indexes have anything to do with this?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply