ms sql # temp table - invalid object name in data source

[Keith G Hicks]

vb.net 2008

I have a MS SQL stored procedure that uses # temp tables in the following
manner:

create #counties (countyname varchar(20))
insert into #counties ......

select firstname, lastname, address
from personlist pl inner join #counties c on pl.county = c.countyname

A string of countys is passed into the procedure and turned into a temp
table for filterin the personlist. I've been using this strategy for years
and it's always worked fine. I use these procs as the source for MS Access
reports, Delphi reports, etc. Never a problem.

Now I'm trying to create a data source in a VS.net vb project. I select the
connection I need, find the list of stored procedures and check off the one
that I need. It happens to use # temp tables as in the example above. When I
try to "finish" the data source I get this error:

<DLNP.dbo.sp_RptAdjLetter_4_RunLetter> Invalid object name '#counties'.

This procedure currently runs an MS Access report without a hitch. I can run
it just fine from a query window in SSMS. In addtion, there is no dynamic
sql in the procedure. The # temp tables are all "created" as in the example
above. I also tried using ## temp tables just for kicks but got the same
results.

How is this handled as using # temp tables in MS SQL stored procedures is
very common.

Thanks,

Keith

 

[Rich P]

I had a similar problem a ways back -- also dealing with a #temp table.
I ended up writing the stored procedure and straight sql within my app.
Here is a sample of what I did with in my vb.net code

if conn.State = ConnectionState.Closed Then conn.Open()
'--clear out any pre-existing #temp tables in this connection
da.SelectCommand.CommandText = "If (object_id('tempdb..#temp1A') is not
null) drop table #temp1A"
da.SelectCommand.ExecuteNonQuery()

strSql = "Create Table #temp1A(ListNo varchar(2), CoId varchar(50))
da.SelectCommand.CommandText = strSql
da.SelectCommand.ExecuteNonQuery()

Basically, you will be running a series of .ExecuteNonQuery()'s. Just
make sure you don't close your connection object because that basically
kills your #temp tables. Then when you have executed all of your action
queries you will write the last query as the fill query to pickup the
desired dataset to place into a .Net table in your app.

Rich

 

[Keith G Hicks]

Thanks Rich.

This is astonishing that vb.net can't deal with SQL temp tables. I've not
run into a front end that has this problem. Holy cow microsoft! What's the
deal here? Since I posted this I ran into a couple of other related threads,
one of which states from someone at MS that this is a shortcoming of vb.net.

I think I'll just alter these to procs that I'm using to use permanent temp
tables instead for the final select. I'm guessing that #temp tables that are
really just used temorarily inside the proc are not a problem, just those
that are used in the final output. I can avoid that with permanent temp
tables. I've got 4 or 5 temp tables that are used in inner joins in the
final select result of a fairly long proc. I can't move the proc to the
front end because it's used in an Access front end also and I don't want to
have to maintain 2 nearly identical procs.

Thanks again for the info.

What's everyone else doing? This has to be a common problem.

 

[Mr. Arnold]

Thanks Rich.

This is astonishing that vb.net can't deal with SQL temp tables. I've not
run into a front end that has this problem. Holy cow microsoft! What's the
deal here? Since I posted this I ran into a couple of other related threads,
one of which states from someone at MS that this is a shortcoming of vb.net.

What does VB.NET have to do with this? VB.NET uses ADO.NET to access
databases. ADO.NET is the database provider.

What's everyone else doing? This has to be a common problem.

Using ADO.NET with SQL Server T-SQL Stored Procedures, Linq-2-SQL,
nHibernate or ADO.NET Entity Framework with VB or C#.NET.

 

[Keith G Hicks]

Nothing but it's still microsoft's bs. I was frustrated and hastily got this
in the wrong NG. I meant to put it in an ado group. Same complaint
regardless. I'm moving this to the dotnet.framework.adonet group tomorrow
(unless there's another ado.net group that I'm not aware of)