Solved

Vistual Studio DataSet Table Adapter Does not recognize temporary tables

Posted on 2013-12-17
3
396 Views
Last Modified: 2014-01-14
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
Comment
Question by:ocdc
3 Comments
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39726081
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
 
LVL 15

Expert Comment

by:JimFive
ID: 39727031
Are you seeing the error in the design studio or are you getting an error when you execute?
0
 
LVL 20

Accepted Solution

by:
Marten Rune earned 500 total points
ID: 39727248
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now