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

  • Thread starter Thread starter Keith G Hicks
  • Start date Start date
K

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
 
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
 
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.
 
Keith said:
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.
Click to expand...

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.
Click to expand...

Using ADO.NET with SQL Server T-SQL Stored Procedures, Linq-2-SQL,
nHibernate or ADO.NET Entity Framework with VB or C#.NET.
 
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)
 
Back
Top