Professional Web Applications Themes

dynamic sql dropping and select into - Microsoft SQL / MS SQL Server

I have a stored procedure running on SQL 7.0 that works most of the time but a few times a week gets the following error: 'Could not find table named 'table id'. Check sysobjects.' The way they have designed the procedure: wrk_table -- input parameter for table name wrk_quicksaved_tablename -- new name for table If exists(select * from dbo.sysobjects where id = object_id(wrk_quicksaved_tablename) and sysstat & 0xf=3) begin select wrk_exec=' Drop Table ' select wrk_exec=wrk_exec + wrk_quicksaved_tablename Exec (wrk_exec) end If the table exists already they drop it. The next part of the procedure they create dynamic SQL to do ...

  1. #1

    Default dynamic sql dropping and select into

    I have a stored procedure running on SQL 7.0 that works
    most of the time but a few times a week gets the following
    error:

    'Could not find table named 'table id'. Check
    sysobjects.'

    The way they have designed the procedure:

    wrk_table -- input parameter for table name
    wrk_quicksaved_tablename -- new name for table
    If exists(select * from dbo.sysobjects where id =
    object_id(wrk_quicksaved_tablename) and sysstat & 0xf=3)
    begin
    select wrk_exec=' Drop Table '
    select wrk_exec=wrk_exec + wrk_quicksaved_tablename
    Exec (wrk_exec)
    end

    If the table exists already they drop it.

    The next part of the procedure they create dynamic SQL to
    do a select into wrk_quicksaved_tablename from wrk_table.

    I am wondering if they are getting spurious errors due to
    the fact that it is dynamic SQL. It doesn't happen all
    the time. I have tried to recreate the error on their
    test server and can't get the error. In their production
    system they are processing very large tables. Any ideas?
    Thanks.
    cynthia elms Guest

  2. #2

    Default Re: dynamic sql dropping and select into

    One thing you can do is to change the way they are determining if the table
    exists or not. Don't select from sysobjects just use this:

    IF OBJECT_ID(wrk_quicksaved_tablename) IS NOT NULL


    You should also make sure the table names don't have spaces in them. If
    there is a chance they will then add [] around the object names.


    --

    Andrew J. Kelly
    SQL Server MVP


    "cynthia elms" <cynthia_elmshotmail.com> wrote in message
    news:035201c3457f$70075e00$a301280aphx.gbl...
    > I have a stored procedure running on SQL 7.0 that works
    > most of the time but a few times a week gets the following
    > error:
    >
    > 'Could not find table named 'table id'. Check
    > sysobjects.'
    >
    > The way they have designed the procedure:
    >
    > wrk_table -- input parameter for table name
    > wrk_quicksaved_tablename -- new name for table
    > If exists(select * from dbo.sysobjects where id =
    > object_id(wrk_quicksaved_tablename) and sysstat & 0xf=3)
    > begin
    > select wrk_exec=' Drop Table '
    > select wrk_exec=wrk_exec + wrk_quicksaved_tablename
    > Exec (wrk_exec)
    > end
    >
    > If the table exists already they drop it.
    >
    > The next part of the procedure they create dynamic SQL to
    > do a select into wrk_quicksaved_tablename from wrk_table.
    >
    > I am wondering if they are getting spurious errors due to
    > the fact that it is dynamic SQL. It doesn't happen all
    > the time. I have tried to recreate the error on their
    > test server and can't get the error. In their production
    > system they are processing very large tables. Any ideas?
    > Thanks.

    Andrew J. Kelly Guest

Similar Threads

  1. Question Generating dynamic names for select box.
    By deepali.bhosale in forum ColdFusion
    Replies: 0
    Last Post: September 21st, 02:34 PM
  2. Dynamic Select Generation
    By rental08 in forum Coldfusion - Getting Started
    Replies: 2
    Last Post: August 12th, 08:25 PM
  3. dynamic select names
    By vijay_wv in forum Coldfusion - Advanced Techniques
    Replies: 2
    Last Post: July 15th, 12:18 PM
  4. Dynamic Select Box using a Database
    By champion_fella in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: May 19th, 03:55 PM
  5. Select form dynamic creation.
    By Jeremy Russell in forum PHP Development
    Replies: 1
    Last Post: September 24th, 05:34 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