Using Visual Basic .NET and SQL Server 2005
I attempt to add a query that is a call to a stored procedure in the
DataSet Designer.

The TableAdapter Query Configuration wizard finds my stored procedure
fine but generates
the following error when I attempt to add the query

Invalid object name '#temp'.

I can tell from the Profiler that VB.NET makes a call to the stored
procedure with NULL values for all the parameters when it attempts to
create the calling function. I have no problem
if I call the procedure from SQL Server Management Studio. Does VB.NET
not allow
temporary tables in Stored Procedures???

Here is the code of the Stored Procedure

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- =============================================
ALTER PROCEDURE [dbo].[Set_Switch_Capacity]
    -- Add the parameters for the stored procedure here
    @ed_market_key varchar(25),
    @month int,
    @year int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    if @ed_market_key is NULL or @month is NULL or @year is NULL
        return

    create table #temp (
        MSC_KEY varchar(25)
    )

    insert #temp
        select MSC_KEY
        from dbo.MSC_VIEW
        where ed_market_key = @ed_market_key

    -- Insert statements for procedure here
    insert dbo.[Statistics]
        select    MSC_KEY,
                @month as [month],
                @year as [year],
                2 as STAT_KEY,
                Capacity
        from dbo.Capacity
        where not exists (select s.MSC_KEY as MSC_KEY
                            from dbo.[Statistics] s join #temp t
                            on s.MSC_KEY = t.MSC_KEY
                            where [month] = @month
                                and [year] = @year
                                and STAT_KEY = 2
                        )

END

I'm not sure what your problem is, but the following comes to mind:

1.  Try a different name than #temp.  Perhaps it doesn't like that
name.
2.  Try a table variable instead of a temp table.  They use fewer
resources than a temp table and automatically go out of scope when the
proc ends.

Chris Dunaway wrote:
Thanks Chris, creating a table variable did the trick. Now the question
is why?


Thanks again,

Crazy

"VB (9) Rocks" slide show
VB.Net 2005 Treeview example required
WebBrowser
Optimizing Repeated PictureBox.Paints
padding text field with blank in MS Access vs OLEDB Jet 4.0
Getting icon from form in dynamically loaded DLL
control collection
Parsing XML strings
Threading make computer slow
Copy files and progressbar