Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

Vistual Studio DataSet Table Adapter Does not recognize temporary tables

I am using temporary tables on my stored procedure using #tableName. The stored procedure will run ok but when I connect Stored proc to Visual Studio project, cannot create dataset. It does not recoginze stored proc due to #tableName invalid object name.
0
ocdc
Asked:
ocdc
1 Solution
 
HuaMinChenBusiness AnalystCommented:
You can instead use a normal table name, like
create table tab1 ...

Open in new window


and drop it at the end of SP.
0
 
JimFiveCommented:
Are you seeing the error in the design studio or are you getting an error when you execute?
0
 
Marten RuneCommented:
Are you using Another session. If so you need a global temp table.

Quote:"SQL Server allows you to create temporary tables that hold data temporarily. Local temporary tables (using one #) hold data until the session is closed and global temporary tables (using two #) hold data until all existing sessions are closed."
From: http://social.technet.microsoft.com/Forums/lync/en-US/7c70106b-f621-4e4d-a91f-100a571e9dee/equivalent-of-on-commit-preserve-rows-in-sql?forum=transactsql

You can start by checking if the temptable exists and if it does take an action f x drop it as:
if OBJECT_ID('tempdb..#test') is not null
Drop table #test

Regards Marten
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now