Issue with stored procedure | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Issue with stored procedure

I have an issue with a stored procedure which is part of some other processes needed to get ‘showcontig’ information and then reindex a database. I’m getting these errors when trying to run it: Server: Msg 207, Level 16, State 3, Procedure USP_Create_Reindex_Script, Line 17
Invalid column name ‘DBNAME’.
Server: Msg 207, Level 16, State 1, Procedure USP_Create_Reindex_Script, Line 18
Invalid column name ‘OBJECTNAME’.
Server: Msg 207, Level 16, State 1, Procedure USP_Create_Reindex_Script, Line 19
Invalid column name ‘INDEXNAME’. Here is the stored procedure: SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE USP_Create_Reindex_Script
AS
SET ARITHABORT ON
DECLARE @DBNAME VARCHAR(50),
@OBJECTNAME VARCHAR(100),
@INDEXNAME VARCHAR(100),
@SQL VARCHAR(2000),
@THEID INT
SELECT @THEID = MIN(THEID) FROM INDEX_INFO_DAILY WHERE THEID > 0
WHILE @THEID IS NOT NULL
BEGIN
SELECT @DBNAME = DBNAME FROM INDEX_INFO_DAILY WHERE THEID = @THEID
SELECT @OBJECTNAME = OBJECTNAME FROM INDEX_INFO_DAILY WHERE THEID = @THEID
SELECT @INDEXNAME = INDEXNAME FROM INDEX_INFO_DAILY WHERE THEID = @THEID
SET @SQL = ‘USE ‘+@DBNAME+’; DBCC DBREINDEX (‘+@OBJECTNAME+’,’+@INDEXNAME+’,80);’
INSERT INTO DEFRAGTEXT(THETEXT) VALUES (@SQL)
–exec(@sql) –Comment out if you don’t want to automate the reindex
SELECT @THEID = MIN(THEID) FROM INDEX_INFO_DAILY WHERE THEID > 0 AND THEID > @THEID
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO Appreciate help.
Welcome to forums.
From the error I feel that the stored procedure is trying to access some columns from INDEX_INFO_DAILY where it is unable to find it.
  1. Do you have these columns in the table?
  2. Do the user who executes the procedure has access to the columns?
As you didn’t get an error on the line "SELECT @THEID = MIN(THEID) FROM INDEX_INFO_DAILY WHERE THEID > 0", I suspect one of the above as the issue.
INDEX_INFO_DAILY looks like user generated table, so my question is did you write this TSQL on your own or got it from anyone else?
Just as an aside. I found this to be a much "better" way to dynamically change database context in a stored procedure:
Code:
DECLARE @DBName sysname;
DECLARE @DBContext sysname;
DECLARE @sql nvarchar(MAX);
SELECT
    @DBName = 'put in your database name here',
    @sql = 'SELECT * FROM sys.tables;',
    @DBContext = @DBName + '.sys.sp_ExecuteSQL'
EXEC @DBContext @sql;
No USE, no context switching. You stay in the current database, but can get information from any other database on that instance. This makes it an ideal candidate for common DBA routines on a server centralised in one DBA database.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |