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

[Keith G Hicks]

I originally posted this to the dotnet.language.vb ng by mistake. Really
belongs here.

using vs 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 vb.net 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

 

[Keith G Hicks]

3.5 days later and no answers? This has to be a common problem with some
good solutions but I'm not finding any anywhere.

 

[sloan]

MS is about to abandon these newsgroups.

Find a post with this subject line. "Update - Microsoft Responds to the
Evolution of Community"


I'm just letting you know.



To your question.




IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
begin
drop table #MyTempTable
end



Try to use the fully qualified name. One version is above.



I think your syntax would be more of the



select A,B,C from tempdb..#MyTempTable


Just a hint, I'm kinda guessing here.