Professional Web Applications Themes

Errors with SELECT INTO - Microsoft SQL / MS SQL Server

I have a script that I run that executes a series of dynamic 'select into' queries. Sometimes the script generates a "Server: Msg 8630, Level 16, State 77, Line 2 Internal Query Processor Error: The query processor encountered an unexpected error during execution." and sometimes it executes without any errors. I've read the knowledge base articles concerning this topic and have tried everything they suggest--from executing the dynamic queries under the context of the target database to using the 'KEEP PLAN' option for a TSQL statement and a myriad of other combinations. Of course, nothing has worked consistently thus far, ...

  1. #1

    Default Errors with SELECT INTO

    I have a script that I run that executes a series of
    dynamic 'select into' queries. Sometimes the script
    generates a "Server: Msg 8630, Level 16, State 77, Line 2
    Internal Query Processor Error: The query processor
    encountered an unexpected error during execution." and
    sometimes it executes without any errors.

    I've read the knowledge base articles concerning this
    topic and have tried everything they suggest--from
    executing the dynamic queries under the context of the
    target database to using the 'KEEP PLAN' option for a TSQL
    statement and a myriad of other combinations. Of course,
    nothing has worked consistently thus far, being the reason
    I am posting here.

    Any and all help would be greatly appreciated. For those
    interested, the knowledge base article that refers to this
    problem is at:
    [url]http://support.microsoft.com/default.aspx?scid=kb;en-[/url]
    us;323586

    Thanks and best regards,

    Jeremy Reed

    Jeremy Reed Guest

  2. #2

    Default Re: Errors with SELECT INTO

    can you please post the script?
    "Jeremy Reed" <jp_reed> wrote in message
    news:097b01c34010$30cde480$a101280aphx.gbl...
    > I have a script that I run that executes a series of
    > dynamic 'select into' queries. Sometimes the script
    > generates a "Server: Msg 8630, Level 16, State 77, Line 2
    > Internal Query Processor Error: The query processor
    > encountered an unexpected error during execution." and
    > sometimes it executes without any errors.
    >
    > I've read the knowledge base articles concerning this
    > topic and have tried everything they suggest--from
    > executing the dynamic queries under the context of the
    > target database to using the 'KEEP PLAN' option for a TSQL
    > statement and a myriad of other combinations. Of course,
    > nothing has worked consistently thus far, being the reason
    > I am posting here.
    >
    > Any and all help would be greatly appreciated. For those
    > interested, the knowledge base article that refers to this
    > problem is at:
    > [url]http://support.microsoft.com/default.aspx?scid=kb;en-[/url]
    > us;323586
    >
    > Thanks and best regards,
    >
    > Jeremy Reed
    >

    Partha Mandayam Guest

  3. #3

    Default Re: Errors with SELECT INTO

    (Reiterating a theme from a recent thread.)

    Could it be that people start a new thread
    whenever they don't like the answer they heard
    in their present thread?

    "Partha Mandayam" <parthabsgc.com> wrote in message
    news:OsFJHjBQDHA.3768tk2msftngp13.phx.gbl...
    > can you please post the script?
    That would be much better use of one's time (and others) then starting a new
    thread.
    > "Jeremy Reed" <jp_reed> wrote in message
    > news:097b01c34010$30cde480$a101280aphx.gbl...
    > > Any and all help would be greatly appreciated.
    It can be helpful to help the people trying to help;
    rather than, tyring to avoid that responsibility via starting a new thread.

    Having "ignored" the title question of your previous thread,
    I have already made a suggestion for this thread.

    Did you try it?
    What was the outcome?

    How about doing the SELECT INTO-s in one proc,
    and INSERT INTO-s another proc?

    Consider:
    You might be having a different problem then the link was written about.

    Do you still get the error with the above suggestion?
    Either way would be very telling.
    It would be nice if you would post which.

    If you still get the error and are permitted,
    post the procedure along with at least the
    declaration of the tables needed to plan the query.
    That would allow others to review your implementation
    of the workarounds and suggestions.
    It would also allow other to make more informated suggestions.
    It would also allow others a chance to suggest
    simpler ways to do the larger picture
    and likely avoid the problem whatever it might be.
    It would also give others a chance to try further identify/verify
    what conditions trigger the problem.

    Bye,
    Delbert Glass


    Delbert Glass Guest

  4. #4

    Default Re: Errors with SELECT INTO

    Sure, here's the sp and script. Obviously the table names
    and whatnot have been changed and I have only included a
    couple of the select intos--all of which are relatively
    the same. All the SELECT INTOs retrieve data from
    different tables, although when an error occurs, it's not
    on any particular table conisistently. The sp
    sp_ADVCreateDb works fine, it returns the name of a
    database based on current date in the form 'ADVMMDDYYYY'.

    ----begin script main----
    declare dbname as varchar(50)
    exec sp_ADVCreateDb dbname = dbname OUTPUT
    select dbname
    exec sp_ADVCopyDataOld dbname
    ----end main script

    ----begin sp_ADVCopyDataOld----
    CREATE PROCEDURE sp_ADVCopyDataOld (dbname as varchar
    (50)) as
    begin
    declare sql as varchar(1000)
    print dbname
    --get Codes1 date
    set sql = 'use ' + dbname + '
    select [p].[CodeID], [p].[Code], [p].[Description], [p].
    [UserEditable], [p].[EditDate], [p].[RecordDate], [p].
    [Active] into [' + dbname + '].[dbo].[Table1] from
    [ADVDB].[dbo].[Table1] p option (keep plan)'
    print sql
    exec (sql)

    --get Codes2
    set sql = 'use ' + dbname + '
    select [p].[CodeID], [p].[Code], [p].[Description], [p].
    [UserEditable], [p].[EditDate], [p].[RecordDate], [p].
    [Active] into [' + dbname + '].[dbo].[Table2] from
    [ADVDB].[dbo].[Table2] p option (keep plan)'
    print sql
    exec (sql)

    --get Codes3
    set sql = 'use ' + dbname + '
    select [p].[CodeID], [p].[Code], [p].[Description], [p].
    [UserEditable], [p].[EditDate], [p].[RecordDate], [p].
    [Active] into [' + dbname + '].[dbo].[Table3] from
    [ADVDB].[dbo].[Table3] p option (keep plan)'
    print sql
    exec (sql)

    end
    GO

    -----end----
    Jeremy Reed Guest

  5. #5

    Default Re: Errors with SELECT INTO

    Try puting all the statements into one string and the do "exec (sql)" once.

    Bye,
    Delbert Glass

    "Jeremy Reed" <jp_reed> wrote in message
    news:052e01c34098$8e14a470$a001280aphx.gbl...
    > Sure, here's the sp and script. Obviously the table names
    > and whatnot have been changed and I have only included a
    > couple of the select intos--all of which are relatively
    > the same. All the SELECT INTOs retrieve data from
    > different tables, although when an error occurs, it's not
    > on any particular table conisistently. The sp
    > sp_ADVCreateDb works fine, it returns the name of a
    > database based on current date in the form 'ADVMMDDYYYY'.
    >
    > ----begin script main----
    > declare dbname as varchar(50)
    > exec sp_ADVCreateDb dbname = dbname OUTPUT
    > select dbname
    > exec sp_ADVCopyDataOld dbname
    > ----end main script
    >
    > ----begin sp_ADVCopyDataOld----
    > CREATE PROCEDURE sp_ADVCopyDataOld (dbname as varchar
    > (50)) as
    > begin
    > declare sql as varchar(1000)
    > print dbname
    > --get Codes1 date
    > set sql = 'use ' + dbname + '
    > select [p].[CodeID], [p].[Code], [p].[Description], [p].
    > [UserEditable], [p].[EditDate], [p].[RecordDate], [p].
    > [Active] into [' + dbname + '].[dbo].[Table1] from
    > [ADVDB].[dbo].[Table1] p option (keep plan)'
    > print sql
    > exec (sql)
    >
    > --get Codes2
    > set sql = 'use ' + dbname + '
    > select [p].[CodeID], [p].[Code], [p].[Description], [p].
    > [UserEditable], [p].[EditDate], [p].[RecordDate], [p].
    > [Active] into [' + dbname + '].[dbo].[Table2] from
    > [ADVDB].[dbo].[Table2] p option (keep plan)'
    > print sql
    > exec (sql)
    >
    > --get Codes3
    > set sql = 'use ' + dbname + '
    > select [p].[CodeID], [p].[Code], [p].[Description], [p].
    > [UserEditable], [p].[EditDate], [p].[RecordDate], [p].
    > [Active] into [' + dbname + '].[dbo].[Table3] from
    > [ADVDB].[dbo].[Table3] p option (keep plan)'
    > print sql
    > exec (sql)
    >
    > end
    > GO
    >
    > -----end----

    Delbert Glass Guest

Similar Threads

  1. Form Select Box Resetting on errors
    By harrillj in forum Dreamweaver AppDev
    Replies: 9
    Last Post: March 7th, 11:41 PM
  2. Replies: 1
    Last Post: October 7th, 01:51 PM
  3. Replies: 0
    Last Post: September 24th, 03:24 AM
  4. Replies: 0
    Last Post: September 11th, 12:19 AM
  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