CODEWITHSUNDEEP
c#sqllinqstored-procedurestemp-tables
Fürst des Wahnsinns
Fürst des Wahnsinns

Reputation: 3

LINQ TO SQL, stored procedure doesn't create a Temp Table

I'm coding a chat over a Webservice, running a SQL Express Server. I'm using C# on Visual Studio 2010.

I have a sproc which should create a Temp Table. When I execute the procedure in the Management Studio it creates a Temp Table. But when i execute the procedure on LINQ to SQL it goes in to the procedure makes everything correct but doesn't create the TempTable.

Here the SQL Code:

    BEGIN
      SET NOCOUNT ON;
      DECLARE @SQL NVARCHAR(MAX)
      DECLARE @ResultCode INT --1 -> good; 0 -> Fail

      SELECT @ResultCode = 1

      SET @SQL = 'CREATE Table ##' + @i_Tablename + '(
                      ID INT IDENTITY(1,1) PRIMARY KEY,
          Username VARCHAR(25) NOT NULL,
          Message VARCHAR(MAX) NOT NULL,
          Font VARCHAR(100) NOT NULL,
          ForeColor INT NOT NULL,
          BGColor INT NOT NULL,
          Date DATETIME DEFAULT GETDATE())'
      EXECUTE sp_sqlexec @SQL

      INSERT INTO Chatlist VALUES(@i_Tablename)

      IF @@error <> 0
      BEGIN
        SELECT @ResultCode = 0
      END
      RETURN @ResultCode
    END

And this is how i call the Procedure in C#:

DB.sp_CreateTempTable(Chatname);

The Procedure ist being executed, the resultcode is ok and it makes the Insert in the Table Chatlist, but no TempTable....

Upvotes: 0

Views: 1030

Answers (1)

spender
spender

Reputation: 120400

Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server.

I imagine SQL Server is deleting your table. If you want to reference it from elsewhere, you shouldn't make it temp.

Upvotes: 1

Related Questions