Solved

Vistual Studio DataSet Table Adapter Does not recognize temporary tables

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

776 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