Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82

    Unanswered: Index of table '#t' does not exist

    Hello,

    I need this kind of code to put in my stored proc, I need
    index for better searching/updating etc.:

    ...
    create table #t ( id numeric(9,0), ud numeric(9,0) null )

    insert into #t ( id, ud ) select id, ud from some_data_table
    where some condition....

    create nonclustered index cp on #t (id)

    select * from #t (index cp) where id = 123

    ...

    But I got this error....:




    Server Message: Number 307, Severity 10
    Server 'U00025_D', Line 8:
    Index 'cp' specified as optimizer hint in the FROM clause of
    table '#t' does not exist. Optimizer will choose another
    index instead.


    But if I run this code step by step everything is OK. Index
    cp is used... Is there possibility how to ad index on
    temporary table in stored proc code?


    Thankx

    DAvid

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You may use batches:
    create table #t ( id numeric(9,0), ud numeric(9,0) null )

    insert into #t ( id, ud ) select id, ud from some_data_table
    where some condition....

    create nonclustered index cp on #t (id)

    GO

    select * from #t (index cp) where id = 123

Posting Permissions

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