SQL Server Performance

Issue with stored procedure

Discussion in 'General DBA Questions' started by netwerkassist, Oct 14, 2011.

  1. netwerkassist New Member

    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.
    netwerkassist, Oct 14, 2011
    #1

  2. preethi Member

    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.
    preethi, Oct 15, 2011
    #2

  3. satya Moderator

    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?
    satya, Oct 17, 2011
    #3

  4. FrankKalis Moderator

    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.
    FrankKalis, Oct 17, 2011
    #4

Share This Page