0

I'm new to stored procedure & my application requirement is to collect data in global temporary table & bulk copy using bcp.

I'm calling stored procedure, which copies data into global temporary table. When I execute stored procedure it throws an error

Invalid object name 'tempdb..qaw_temp'

Code for stored procedure:

CREATE table tempdb..qaw_temp
  (id      char(12),
   i_test    int))

print 'Before Insert'
  insert into tempdb..qaw_temp
  values('0411',
    3077,

print 'After Insert'

This work's if we execute code from Aqua Data Studio. But When I get executed in the stored procedure, it throws above mentioned error.

Stored procedure code

create PROC Xtest
as 
Begin
 << Code >>
End

Why does the stored procedure execution fail?

Please help, thank you.

migrated from serverfault.com Aug 27 '16 at 7:28

This question came from our site for system and network administrators.

0

There are 2 types of temporary tables in SQL Server. Local and Global. Differenence between these 2 are:-

1. Local temporary table starts with #, Global temporary table starts with ##

2. Local temporary table exists for a session only. Global temporary table alwyas exists in tempdb.

3. Because life of local temporary table is within a session so only the session can query on that. Multiple session can query Global temporary table, but data is limited to a session.

Here in you post you are using table to insert value (Not global or local temporary table).

Try bellow query.

CREATE table ##qaw_temp
  (id      char(12),
   i_test    int)

print 'Before Insert'
  insert into ##qaw_temp
  values('0411',
    3077)

print 'After Insert'

Thanks

0

The accepted answer is incorrect with regards to several of its statements concerning Global Temporary Tables. The characteristics of Global Temp Tables are:

  1. They are indicated by two pound signs -- ## -- as the first two characters of their name (unlike the single pound sign -- # -- for Local Temp Tables).

  2. Global Temp Tables are exactly that: global. They can be accessed by sessions outside of the session that created the object (unlike Local Temporary Tables).

  3. They can be both read from and modified by all sessions.

  4. They are marked for automatic deletion when the session that created the object ends. The actual DROP will occur then there are no more active transactions referencing the object. Meaning: any session (even multiple sessions) that has a lock on the table (even for a SELECT) will still have access to the Global Temp Table, even after the originating session has ended, until that lock is released.

    This is not an issue for Local Temp Tables since no other session can access them, hence there can't be any locks on them from other sessions that would keep them around.

  5. They survive being created in a subprocess and will continue to exist in the parent process once the subprocess ends. For example:

    EXEC(N'CREATE TABLE ##Global (ID INT);');
    SELECT ID FROM ##Global;
    

    works, whereas the following:

    EXEC(N'CREATE TABLE #Local (ID INT);');
    SELECT ID FROM #Local;
    

    does not.

0

I'm answering my own question to close and to share root cause of issue.

This is permission model of database, in case of aqua data studio all operation are done in one session by one user. But in case of stored procedure execution create table operation done by user in one session and used in another session

Updated tempdb permission for user and resolved issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.