TEMA 1.0 Install causes Reindex job to fail

Usage and Config Security
1

(imported topic written by hmkjr91)

Hello,

It appears that the installation of TEMA 1.0 has renamed table “BFEnterprise.dbo.schema_info” to “BFEnterprise.dss.schema_info”, but did not update INFORMATION_SCHEMA.TABLES table. This is causing the BFEnterprise Full Database Reindex job to fail.

Can someone tell me if the “BFEnterprise.dbo.schema_info” table was supposed to be renamed? And if so, can I simple remove the reference from the INFORMATION_SCHEMA.TABLES table?

Thanks

Buddy

2

(imported comment written by SystemAdmin)

Hi Buddy,

This is actually due to a bug in the BFEnterprise Full Database Reindex job (the table has always been named dss.schema_info). It has been fixed for a future version of TEM. In the meantime you can do the following to resolve the error:

Go to SQL Server Management Studio / SQL Server Agent / Jobs / BFEnterprise Full Database Reindex / Properties / Steps / Edit, and replace with the following command:

use BFEnterprise declare table_cursor cursor 

for select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 
'BASE TABLE' open table_cursor   declare @tableName sysname fetch next from table_cursor into @tableName 

while @@FETCH_STATUS != -1 begin execute ( N
'dbcc dbreindex ( '
'' + @tableName + N
''
' )' ) fetch next from table_cursor into @tableName end   close table_cursor deallocate table_cursor
3

(imported comment written by hmkjr91)

Thanks John. Quick follow up question, in the code you posted, it looks like the only thing that changed is that you removed the explicit reference to '‘BFEnterprise.dbo.’ in building the table name. I’m not clear how this is going to exclude the bad table reference? I was considering just adding to the WHERE clause in the SELECT statement to exclude the table. Maybe I’m missing somehting. Let me know.

Thanks much!

Buddy

4

(imported comment written by hmkjr91)

Hi John, here is what I did. Let me know if you see any issues with it. Thanks.

declare table_cursor cursor 

for select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 
'BASE TABLE' AND TABLE_NAME != 
'schema_info' open table_cursor declare @tableName sysname fetch next from table_cursor into @tableName 

while @@FETCH_STATUS != -1 begin declare @reindexCommand nvarchar(max) set @reindexCommand = N
'dbcc dbreindex ( '
'BFEnterprise.dbo.' + @tableName + N
''
' )' execute ( @reindexCommand ) fetch next from table_cursor into @tableName end   close table_cursor deallocate table_cursor
5

(imported comment written by SystemAdmin)

Your solution looks good – dss.schema_info is always a single-row table, so it doesn’t need to be reindexed anyway.