Professional Web Applications Themes

[SQL 6.5] Cannot access temporary tables from a stored procedure - Microsoft SQL / MS SQL Server

I have this stored procedure in which I create temporary tables. When I try to insert something into the temporary tables, I get a '#tmpTable not found' error. All goes well when I am not in a stored procedure. The minute I encapsulate the code in a stored procedure and try to exec it through an SP, the SP fails. This is about sql server 6.5 service pack 5a. This is a part of the code: ---8<------------ create procedure dbo.pAanbestedingenSearch pStrBouwfaseCodes varchar(255) = null , pStrGebouwCodes varchar(255) = null , pStrGebouwRubrieken varchar(255) = null , pStrRegios varchar(255) = null , ...

  1. #1

    Default [SQL 6.5] Cannot access temporary tables from a stored procedure

    I have this stored procedure in which I create temporary tables. When I try
    to insert something into the temporary tables, I get a '#tmpTable not found'
    error.

    All goes well when I am not in a stored procedure. The minute I encapsulate
    the code in a stored procedure and try to exec it through an SP, the SP
    fails. This is about sql server 6.5 service pack 5a.

    This is a part of the code:

    ---8<------------

    create procedure dbo.pAanbestedingenSearch

    pStrBouwfaseCodes varchar(255) = null
    , pStrGebouwCodes varchar(255) = null
    , pStrGebouwRubrieken varchar(255) = null
    , pStrRegios varchar(255) = null
    , pStrRamingsGroepen varchar(255) = null
    , pStrTrefwoord varchar(255) = '_'
    , pStrBestekcode varchar(255) = '_'
    , pStrPlaatsnaam varchar(255) = '_'
    , pStrMinPostcode varchar(255) = '0000'
    , pStrMaxPostcode varchar(255) = '9999'
    , pIntPublicatiePeriode int = null

    as

    declare dteMinPubDate datetime
    declare dteMaxPubDate datetime

    create table #tmpBouwfase
    (
    bouwfaseCode int
    )

    declare strProcessing varchar(255)
    select strProcessing = pStrBouwfaseCodes

    -- Bouwfases inpassen

    while charIndex(',', strProcessing) > 0
    begin
    insert into
    #tmpBouwFase
    (
    bouwFaseCode
    )
    values
    (
    convert(int,substring(strProcessing, 1, charIndex(',', strProcessing)-1))
    )
    select strProcessing = substring(strProcessing, charIndex(',',
    strProcessing)+1, len(strProcessing) - charIndex(',', strProcessing))
    end

    insert into
    #tmpBouwFase
    (
    bouwFaseCode
    )
    values
    (
    convert(int,strProcessing)
    )


    beyond Guest

  2. #2

    Default Re: [SQL 6.5] Cannot access temporary tables from a stored procedure

    Perhaps your SQL Server has a case-sensitive character set/sort order.
    Try specifing the case-sensitive object name (#tmpBouwfase) in the
    subsequent statements.

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "beyond cool" <com> wrote in message
    news:3f3096e4$0$49115$xs4all.nl... 
    I try 
    found' 
    encapsulate 
    SP 
    strProcessing)-1)) 
    strProcessing)) 


    Dan Guest

  3. #3

    Default Re: [SQL 6.5] Cannot access temporary tables from a stored procedure

    > Perhaps your SQL Server has a case-sensitive character set/sort order. 

    It was a stupid mistake :( The user had no rights to the tempDb. That's what
    you get for usually making logins and making them dbo ...


    beyond Guest

Similar Threads

  1. cf 7 and mysql 5 stored procedures and temporary tables
    By gnurulz in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 02:58 AM
  2. MS Access Stored Procedure
    By Roland Hall in forum ASP Database
    Replies: 4
    Last Post: January 31st, 07:05 PM
  3. Temporary Tables
    By Phil Jackson in forum IBM DB2
    Replies: 2
    Last Post: August 22nd, 05:05 AM
  4. URGENT Access Stored Procedure Help
    By Jordan Marton in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 17th, 06:50 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