Using Temperory tables in BI

Question

Using Temperory tables in BI

Nuts

SSCrazy

Points: 2547
More actions
November 23, 2008 at 6:10 pm

#196572

Hi Guys
I created a temp table in Management studion and when I try to access this temp table through BI using select * , it gives an error saying there is no such object in the database.
HOw do I use temp tables in BI??
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

krayknot SSCertifiable Points: 7998 More actions · Answer 1

Nuts (11/23/2008)
Hi Guys
I created a temp table in Management studion and when I try to access this temp table through BI using select * , it gives an error saying there is no such object in the database.
HOw do I use temp tables in BI??
Thanks

the pound (#) sign tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When you close this session, the table will be automatically dropped.

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

krayknot SSCertifiable Points: 7998 More actions · Answer 2

You can also create global temporary tables. These are named with two pound signs. For example, ##YakHerders is a global temporary table. Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it. These are rarely used in SQL Server.

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 3

Nuts (11/23/2008)
Hi Guys
I created a temp table in Management studion and when I try to access this temp table through BI using select * , it gives an error saying there is no such object in the database.
HOw do I use temp tables in BI??
Thanks

Personally I use table variables rather than temporary tables (search BOL for syntax), not sure that answers your question though!

benlatham SSC Eights! Points: 933 More actions · Answer 4

Hi

You should find that the query still works, it is just you get that error message because BIDS can't verify the query because the temp doesn't exist. I still use them but ignore the message.

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 5

Just my opinion; I never use temp tables with BIDS. I prefer to create staging tables at run time, then remove them after the process has completed. If I am concerned about sessions I add a row to a process management table, including the session ID. Until the "EndDate" of that row is not null, no other session can add to the temp table. Anyone think that a global temp table is better for an SSIS package then a on-demand staging table? (I realize that the staging table could be a temp table... and in certain places (in the past) I have used it as such, but I like a static table in case I need to troubleshoot an error in the process, even if the server crashes).

Ells SSCrazy Eights Points: 9901 More actions · Answer 6

I have used temp tables on bids. Created in one task in SSIS and used in another.

ie to check that the dimension product includes all the product_codes in the fact table I will create a temp table holding the distinct list of dimension members in the fact table. In another SQL task I will then ensure the dimension members that are in fact table only are inserted into the dimension table. ! Ensuring that the property 'retainsameconnection' is set to true.

I find this a fast way of doing this rather than scanning both tables.

Ells

:hehe:

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 7

mje_away,

Sounds like a good use of temporary tables. I was not aware of that flag; good to know. The issue that I have ran into though is when my packages "evolve" into multi-package processes and I spawn multiple connections... just an FYI.

Thanks for the info!

Nuts SSCrazy Points: 2547 More actions · Answer 8

Thanks guys!

So u mean I should not use the temp tables in BIDS??

The problem is I dont have the 'create table' permission on the database I use as its used by the developer guys.. I can write queries against it and use temp tables as well.

so what do i do? Just run a query against the table?? Or use the temp tables

Timothy J Hartford SSCarpal Tunnel Points: 4214 More actions · Answer 9

In my opinion, use what makes sense in your environment, just keep in mind that a local temp table (#tablename) is session based, whereas a global temp table (##tablename) is accessible until dropped or SQL Server is restarted. Also, if you set up a connection to a table that does not yet exist, be sure to specify "Delay Validation = True" in your connection properties.