Professional Web Applications Themes

Error in select - Microsoft SQL / MS SQL Server

Hi there, I have this select statement and I don't understand why is not working: DECLARE TableName varchar(255) DECLARE howmany int DECLARE TableCursor CURSOR FOR SELECT table_name FROM TABLE_MAINTENANCE WHERE Processed=0 OPEN TableCursor FETCH NEXT FROM TableCursor INTO TableName WHILE FETCH_STATUS = 0 BEGIN select howmany=count(*) from TableName PRINT 'Reindexing ' + TableName DBCC DBREINDEX(TableName,' ') Update Table_Maintenance set Rows=howmany,Processed=1 where Table_name=TableName FETCH NEXT FROM TableCursor INTO TableName END CLOSE TableCursor DEALLOCATE TableCursor The error that I get is : Must declare the variable 'TableName' and this is coming from this line :select howmany=count(*) from TableName If somebody can help ...

  1. #1

    Default Error in select

    Hi there,
    I have this select statement and I don't understand why
    is not working:

    DECLARE TableName varchar(255)
    DECLARE howmany int

    DECLARE TableCursor CURSOR FOR
    SELECT table_name FROM TABLE_MAINTENANCE
    WHERE Processed=0

    OPEN TableCursor

    FETCH NEXT FROM TableCursor INTO TableName
    WHILE FETCH_STATUS = 0
    BEGIN

    select howmany=count(*) from TableName

    PRINT 'Reindexing ' + TableName
    DBCC DBREINDEX(TableName,' ')

    Update Table_Maintenance set Rows=howmany,Processed=1
    where Table_name=TableName

    FETCH NEXT FROM TableCursor INTO TableName
    END

    CLOSE TableCursor

    DEALLOCATE TableCursor


    The error that I get is :

    Must declare the variable 'TableName' and this is coming
    from this line :select howmany=count(*) from TableName

    If somebody can help me with this!!1


    Thanks
    Florica
    Florica Guest

  2. #2

    Default Re: Error in select

    Is howmany supposed to hold the record count for the cursor?

    If yes, have you tried using ROWCOUNT after declaring the cursor? (I'm
    not sure if it will work) I think the error is that the cursor is not a
    table, so you can't query it - SQL thinks it's a variable.


    "Florica" <net> wrote in message
    news:0c2701c34d31$6dba0670$gbl... 


    Laurie Guest

  3. #3

    Default Re: Error in select

    SQL Server cannot evaluate from TableName. One option would be to use a bit of dynamic sql. Here is an example from the pubs database that retrieves counts. You should be able to modify to suit your needs.

    USE pubs
    GO
    DECLARE cnt int, tbl varchar(50)
    CREATE TABLE #holdcnt (tbl varchar(50), cnt int)

    SET tbl = 'authors'
    INSERT INTO #holdcnt
    EXEC('SELECT '''+tbl+''', COUNT(*) FROM ' + tbl)

    SET tbl = 'titles'
    INSERT INTO #holdcnt
    EXEC('SELECT '''+tbl+''', COUNT(*) FROM ' + tbl)

    SELECT * FROM #holdcnt

    GO
    DROP TABLE #holdcnt

    --
    Keith, SQL Server MVP

    "Florica" <net> wrote in message news:0c2701c34d31$6dba0670$gbl... 
    Keith Guest

  4. #4

    Default Re: Error in select

    The reasons why

    select howmany=count(*) from TableName

    won't work are explained here:
    http://www.algonet.se/~sommar/dynamic_sql.html


    You can get the rowcount a lot quicker in the following way:

    SELECT rowcnt FROM sysindexes WHERE OBJECT_NAME(id) = TableName AND inid IN
    (0,1)

    You have to do this after you run DBCC DBREINDEX, because the rowcnt column
    in the sysindexes table is not always up to date, but it will be after DBCC
    DBREINDEX.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Florica" <net> wrote in message
    news:0c2701c34d31$6dba0670$gbl... 


    Jacco Guest

  5. #5

    Default Re: Error in select


    That system variable is CURSOR_ROWS, not ROWCOUNT.

    Sorry about that!


    "Laurie Mountjoy" <com> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    Laurie Guest

  6. #6

    Default Error in select

    DECLARE TableName varchar(255)
    DECLARE howmany int

    DECLARE TableCursor CURSOR FOR
    SELECT table_name FROM TABLE_MAINTENANCE
    WHERE Processed=0

    OPEN TableCursor

    FETCH NEXT FROM TableCursor INTO TableName
    WHILE FETCH_STATUS = 0
    BEGIN

    select howmany=count(*) from TableName

    PRINT 'Reindexing ' + TableName
    DBCC DBREINDEX(TableName,' ')

    Update Table_Maintenance set Rows=howmany,Processed=1
    where Table_name=TableName

    FETCH NEXT FROM TableCursor INTO TableName
    END

    CLOSE TableCursor

    DEALLOCATE TableCursor
     
    naveen Guest

Similar Threads

  1. Help with Select Error!!!
    By Kory in forum Dreamweaver AppDev
    Replies: 5
    Last Post: June 1st, 04:21 PM
  2. Select Error??
    By Kory in forum Dreamweaver AppDev
    Replies: 3
    Last Post: May 26th, 02:59 PM
  3. error : syntax error at or near $1 for over select rows
    By vinita bansal in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 27th, 06:51 PM
  4. Replies: 3
    Last Post: April 18th, 12:52 PM
  5. Replies: 0
    Last Post: April 15th, 01:22 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139