Professional Web Applications Themes

Reindexing Stored Procedures - Microsoft SQL / MS SQL Server

I got some SP's from a book called "The Guru's Guide to T-SQL" and I get this error: Executed as user: myserver\Administrator. Incorrect syntax near the keyword 'and'. [SQLSTATE 42000] (Error 156) Associated statement is not prepared [SQLSTATE HY007] (Error 0) Incorrect syntax near the keyword 'and'. [SQLSTATE 42000] (Error 156). The step failed. Any ideas on whats wrong? Thx, Dave create PROC sp_Reindex_all dbname sysname='%' AS SET NOCOUNT ON DECLARE Databases CURSOR FOR SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE NOT (CATALOG_NAME IN ('tempdb','master','msdb','model')) AND CATALOG_NAME LIKE dbname DECLARE execstr varchar(8000), tablename sysname OPEN Databases FETCH Databases INTO dbname IF (FETCH_STATUS<>0) ...

  1. #1

    Default Reindexing Stored Procedures

    I got some SP's from a book called
    "The Guru's Guide to T-SQL"
    and I get this error:

    Executed as user: myserver\Administrator. Incorrect syntax near the keyword
    'and'.
    [SQLSTATE 42000] (Error 156) Associated statement is not prepared [SQLSTATE
    HY007] (Error 0)
    Incorrect syntax near the keyword 'and'. [SQLSTATE 42000] (Error 156). The
    step failed.


    Any ideas on whats wrong?

    Thx,
    Dave

    create PROC sp_Reindex_all dbname sysname='%'

    AS
    SET NOCOUNT ON
    DECLARE Databases CURSOR FOR
    SELECT CATALOG_NAME
    FROM INFORMATION_SCHEMA.SCHEMATA
    WHERE NOT (CATALOG_NAME IN ('tempdb','master','msdb','model'))
    AND CATALOG_NAME LIKE dbname
    DECLARE execstr varchar(8000), tablename sysname

    OPEN Databases

    FETCH Databases INTO dbname
    IF (FETCH_STATUS<>0) BEGIN
    CLOSE Databases
    DEALLOCATE Databases
    PRINT 'No databases were found that match "' + dbname + '"'
    RETURN 0
    END

    WHILE (FETCH_STATUS=0) BEGIN
    --PRINT CHAR(13)+'Rebuilding indexes in database: ' + dbname
    --PRINT CHAR(13)
    SET execstr='EXEC '+dbname+'..sp_Reindex'
    EXEC(execstr)
    FETCH Databases INTO dbname
    END
    CLOSE Databases
    DEALLOCATE Databases
    RETURN 0



    Create PROCEDURE sp_Reindex
    tablename sysname='%'
    AS
    SET NOCOUNT ON

    DECLARE execstr varchar(8000)
    DECLARE Tables CURSOR FOR
    SELECT [name]
    FROM [sysobjects]
    WHERE OBJECTPROPERTY(OBJECT_ID([name]),'IsUserTable')=1
    AND [name] LIKE tablename
    OPEN Tables
    FETCH Tables INTO tablename
    WHILE (FETCH_STATUS=0) BEGIN
    --PRINT CHAR(13) + 'Rebuilding indexes for: ' + tablename
    SET execstr = 'DBCC DBREINDEX(' + tablename + ') WITH NO_INFOMSGS'
    EXEC(execstr)
    FETCH Tables INTO tablename
    END
    CLOSE Tables
    DEALLOCATE Tables
    RETURN 0



    Dave Guest

  2. #2

    Default Re: Reindexing Stored Procedures

    I just ran the script and it worked fine for me. I just had to put a GO in
    between the two stored procedure scripts.

    --
    HTH,
    Vyas, MVP (SQL Server)
    http://vyaskn.tripod.com/
    What hardware is your SQL Server running on?
    http://vyaskn.tripod.com/poll.htm


    "Dave" <com> wrote in message
    news:phx.gbl...
    I got some SP's from a book called
    "The Guru's Guide to T-SQL"
    and I get this error:

    Executed as user: myserver\Administrator. Incorrect syntax near the keyword
    'and'.
    [SQLSTATE 42000] (Error 156) Associated statement is not prepared [SQLSTATE
    HY007] (Error 0)
    Incorrect syntax near the keyword 'and'. [SQLSTATE 42000] (Error 156). The
    step failed.


    Any ideas on whats wrong?

    Thx,
    Dave

    create PROC sp_Reindex_all dbname sysname='%'

    AS
    SET NOCOUNT ON
    DECLARE Databases CURSOR FOR
    SELECT CATALOG_NAME
    FROM INFORMATION_SCHEMA.SCHEMATA
    WHERE NOT (CATALOG_NAME IN ('tempdb','master','msdb','model'))
    AND CATALOG_NAME LIKE dbname
    DECLARE execstr varchar(8000), tablename sysname

    OPEN Databases

    FETCH Databases INTO dbname
    IF (FETCH_STATUS<>0) BEGIN
    CLOSE Databases
    DEALLOCATE Databases
    PRINT 'No databases were found that match "' + dbname + '"'
    RETURN 0
    END

    WHILE (FETCH_STATUS=0) BEGIN
    --PRINT CHAR(13)+'Rebuilding indexes in database: ' + dbname
    --PRINT CHAR(13)
    SET execstr='EXEC '+dbname+'..sp_Reindex'
    EXEC(execstr)
    FETCH Databases INTO dbname
    END
    CLOSE Databases
    DEALLOCATE Databases
    RETURN 0



    Create PROCEDURE sp_Reindex
    tablename sysname='%'
    AS
    SET NOCOUNT ON

    DECLARE execstr varchar(8000)
    DECLARE Tables CURSOR FOR
    SELECT [name]
    FROM [sysobjects]
    WHERE OBJECTPROPERTY(OBJECT_ID([name]),'IsUserTable')=1
    AND [name] LIKE tablename
    OPEN Tables
    FETCH Tables INTO tablename
    WHILE (FETCH_STATUS=0) BEGIN
    --PRINT CHAR(13) + 'Rebuilding indexes for: ' + tablename
    SET execstr = 'DBCC DBREINDEX(' + tablename + ') WITH NO_INFOMSGS'
    EXEC(execstr)
    FETCH Tables INTO tablename
    END
    CLOSE Tables
    DEALLOCATE Tables
    RETURN 0




    Narayana Guest

  3. #3

    Default Re: Reindexing Stored Procedures

    I would love to ask him, but find him

    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:%phx.gbl... 
    > keyword 
    > [SQLSTATE 
    > The 
    >
    >[/ref]


    Dave Guest

  4. #4

    Default Re: Reindexing Stored Procedures

    Sometimes I run it its fine. Sometime I get the error


    "Narayana Vyas Kondreddi" <com> wrote in message
    news:phx.gbl... 
    keyword 
    [SQLSTATE 
    The 


    Dave Guest

  5. #5

    Default Re: Reindexing Stored Procedures

    Dave,

    http://www.khen.com/feedback.htm

    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "Dave" <com> wrote in message
    news:%phx.gbl... 
    > > keyword 
    > > [SQLSTATE 
    > > The 
    > >
    > >[/ref]
    >
    >[/ref]


    Dinesh.T.K Guest

  6. #6

    Default Re: Reindexing Stored Procedures

    Thanks



    "Dinesh.T.K" <mail.tkdinesh.com> wrote in message
    news:OVNP%phx.gbl... [/ref][/ref]
    his [/ref][/ref]
    156). [/ref][/ref]
    NO_INFOMSGS' 
    > >
    > >[/ref]
    >
    >[/ref]


    Dave Guest

  7. #7

    Default Re: Reindexing Stored Procedures

    Dave, I talked to Ken and there are apparently problems trying to reach him
    through that website. If you are still having problems with this, please
    send me email (com) and I'll put you in touch with him.

    --
    Thanks,
    Stephen Dybing

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Please reply to the newsgroups only, thanks.

    "Dave" <com> wrote in message
    news:phx.gbl... [/ref]
    > his [/ref][/ref]
    the [/ref]
    > 156). [/ref]
    > NO_INFOMSGS' 
    > >
    > >[/ref]
    >
    >[/ref]


    Stephen Guest

Similar Threads

  1. Stored Procedures
    By Anthony M. Davis in forum Coldfusion - Advanced Techniques
    Replies: 28
    Last Post: February 9th, 03:17 PM
  2. dt_ Stored Procedures
    By chopper in forum ASP Database
    Replies: 2
    Last Post: July 20th, 04:06 PM
  3. New to ASP and Stored Procedures
    By John Berman in forum ASP Database
    Replies: 6
    Last Post: February 28th, 01:37 AM
  4. Stored Procedures
    By Vladi in forum Microsoft SQL / MS SQL Server
    Replies: 9
    Last Post: August 13th, 01:52 PM
  5. Need help about stored procedures
    By Tahir in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 23rd, 10:17 AM

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