Solved

Vistual Studio DataSet Table Adapter Does not recognize temporary tables

Posted on 2013-12-17
3
407 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSMS Opening Mode 9 20
SQL create line numbers for data sampling 11 30
query optimization 6 15
job schedule 8 21
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

821 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