SQL Server connection context using temporary table cannot be used in stored procedures called with SqlDataAdapter.Fill

Question

I want to have some information available for any stored procedure, such as current user. Following the temporary table method indicated here, I have tried the following:

1) create temporary table when connection is opened

        private void setConnectionContextInfo(SqlConnection connection)
        {
            if (!AllowInsertConnectionContextInfo)
                return;

            var username = HttpContext.Current?.User?.Identity?.Name ?? "";

            var commandBuilder = new StringBuilder($@"
CREATE TABLE #ConnectionContextInfo(
    AttributeName VARCHAR(64) PRIMARY KEY, 
    AttributeValue VARCHAR(1024)
);

INSERT INTO #ConnectionContextInfo VALUES('Username', @Username);
");

            using (var command = connection.CreateCommand())
            {
                command.Parameters.AddWithValue("Username", username);
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// checks if current connection exists / is closed and creates / opens it if necessary
        /// also takes care of the special authentication required by V3 by building a windows impersonation context
        /// </summary>
        public override void EnsureConnection()
        {
            try
            {
                lock (connectionLock)
                {
                    if (Connection == null)
                    {
                        Connection = new SqlConnection(ConnectionString);
                        Connection.Open();
                        setConnectionContextInfo(Connection);
                    }

                    if (Connection.State == ConnectionState.Closed)
                    {
                        Connection.Open();
                        setConnectionContextInfo(Connection);
                    }
                }
            }  
            catch (Exception ex)
            {
                if (Connection != null && Connection.State != ConnectionState.Open)
                    Connection.Close();

                throw new ApplicationException("Could not open SQL Server Connection.", ex);
            }
        }

2) Tested with a procedure which is used to populate a DataTable using SqlDataAdapter.Fill, by using the following function:

    public DataTable GetDataTable(String proc, Dictionary<String, object> parameters, CommandType commandType)
    {
        EnsureConnection();

        using (var command = Connection.CreateCommand())
        {
            if (Transaction != null)
                command.Transaction = Transaction;

            SqlDataAdapter adapter = new SqlDataAdapter(proc, Connection);
            adapter.SelectCommand.CommandTimeout = CommonConstants.DataAccess.DefaultCommandTimeout;
            adapter.SelectCommand.CommandType = commandType;

            if (Transaction != null)
                adapter.SelectCommand.Transaction = Transaction;

            ConstructCommandParameters(adapter.SelectCommand, parameters);

            DataTable dt = new DataTable();
            try
            {
                adapter.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                var err = String.Format("Error executing stored procedure '{0}' - {1}.", proc, ex.Message);
                throw new TptDataAccessException(err, ex);
            }
        }
    }

3) called procedure tries to get the username like this:

DECLARE @username VARCHAR(128) = (select AttributeValue FROM #ConnectionContextInfo where AttributeName = 'Username')

but #ConnectionContextInfo is no longer available in the context.

I have put a SQL profiler against the database, to check what is happening:

• temporary table is created successfully using a certain SPID
• procedure is called using the same SPID

Why is temporary table not available within the procedure scope?

In T-SQL doing the following works:

• create a temporary table
• call a procedure that needs data from that particular temporary table
• temporary table is dropped only explicitly or after current scope ends

Thanks.

Answer 1

As was shown in this answer, ExecuteNonQuery uses sp_executesql when CommandType is CommandType.Text and command has parameters.

The C# code in this question doesn't set the CommandType explicitly and it is Text by default, so most likely end result of the code is that CREATE TABLE #ConnectionContextInfo is wrapped into sp_executesql. You can verify it in the SQL Profiler.

It is well-known that sp_executesql is running in its own scope (essentially it is a nested stored procedure). Search for "sp_executesql temp table". Here is one example: Execute sp_executeSql for select...into #table but Can't Select out Temp Table Data

So, a temp table #ConnectionContextInfo is created in the nested scope of sp_executesql and is automatically deleted as soon as sp_executesql returns. The following query that is run by adapter.Fill doesn't see this temp table.

---

What to do?

Make sure that CREATE TABLE #ConnectionContextInfo statement is not wrapped into sp_executesql.

In your case you can try to split a single batch that contains both CREATE TABLE #ConnectionContextInfo and INSERT INTO #ConnectionContextInfo into two batches. The first batch/query would contain only CREATE TABLE statement without any parameters. The second batch/query would contain INSERT INTO statement with parameter(s).

I'm not sure it would help, but worth a try.

If that doesn't work you can build one T-SQL batch that creates a temp table, inserts data into it and calls your stored procedure. All in one SqlCommand, all in one batch. This whole SQL will be wrapped in sp_executesql, but it would not matter, because the scope in which temp table is created will be the same scope in which stored procedure is called. Technically it will work, but I wouldn't recommend following this path.

---

Here is not an answer to the question, but suggestion to solve the problem.

To be honest, the whole approach looks strange. If you want to pass some data into the stored procedure why not use parameters of this stored procedure. This is what they are for - to pass data into the procedure. There is no real need to use temp table for that. You can use a table-valued parameter (T-SQL, .NET) if the data that you are passing is complex. It is definitely an overkill if it is simply a Username.

Your stored procedure needs to be aware of the temp table, it needs to know its name and structure, so I don't understand what's the problem with having an explicit table-valued parameter instead. Even the code of your procedure would not change much. You'd use @ConnectionContextInfo instead of #ConnectionContextInfo.

Using temp tables for what you described makes sense to me only if you are using SQL Server 2005 or earlier, which doesn't have table-valued parameters. They were added in SQL Server 2008.

Answer 2

MINOR ISSUE: I am going to assume for the moment that the code posted in the Question isn't the full piece of code that is running. Not only are there variables used that we don't see getting declared (e.g. AllowInsertConnectionContextInfo), but there is a glaring omission in the setConnectionContextInfo method: the command object is created but never is its CommandText property set to commandBuilder.ToString(), hence it appears to be an empty SQL batch. I'm sure that this is actually being handled correctly since 1) I believe submitting an empty batch will generate an exception, and 2) the question does mention that the temp table creation appears in the SQL Profiler output. Still, I am pointing this out as it implies that there could be additional code that is relevant to the observed behavior that is not shown in the question, making it more difficult to give a precise answer.

THAT BEING SAID, as mentioned in @Vladimir's fine answer, due to the query running in a sub-process (i.e. sp_executesql), local temporary objects -- tables and stored procedures -- do not survive the completion of that sub-process and hence are not available in the parent context.

Global temporary objects and permanent/non-temporary objects will survive the completion of the sub-process, but both of those options, in their typical usage, introduce concurrency issues: you would need to test for the existence first before attempting to create the table, and you would need a way to distinguish one process from another. So these are not really a great option, at least not in their typical usage (more on that later).

Assuming that you cannot pass in any values into the Stored Procedure (else you could simply pass in the username as @Vladimir suggested in his answer), you have a few options:

1. The easiest solution, given the current code, would be to separate the creation of the local temporary table from the INSERT command (also mentioned in @Vladimir's answer). As previously mentioned, the issue you are encountering is due to the query running within sp_executesql. And the reason sp_executesql is being used is to handle the parameter @Username. So, the fix could be as simple as changing the current code to be the following:

   string _Command = @"
        CREATE TABLE #ConnectionContextInfo(
        AttributeName VARCHAR(64) PRIMARY KEY, 
        AttributeValue VARCHAR(1024)
        );";
   
   using (var command = connection.CreateCommand())
   {
       command.CommandText = _Command;
       command.ExecuteNonQuery();
   }
   
   _Command = @"
        INSERT INTO #ConnectionContextInfo VALUES ('Username', @Username);
   ");
   
   using (var command = connection.CreateCommand())
   {
       command.CommandText = _Command;
   
       // do not use AddWithValue()!
       SqlParameter _UserName = new SqlParameter("@Username", SqlDbType.NVarChar, 128);
       _UserName.Value = username;
       command.Parameters.Add(_UserName);
   
       command.ExecuteNonQuery();
   }
   

   Please note that temporary objects -- local and global -- cannot be accessed in T-SQL User-Defined Functions or Table-Valued Functions.

2. A better solution (most likely) would be to use CONTEXT_INFO, which is essentially session memory. It is a VARBINARY(128) value but changes to it survive any sub-process since it is not an object. Not only does this remove the current complication you are facing, but it also reduces tempdb I/O considering that you are creating and dropping a temporary table each time this process runs, and doing an INSERT, and all 3 of those operations are written to disk twice: first in the Transaction Log, then in the data file. You can use this in the following manner:

   string _Command = @"
       DECLARE @User VARBINARY(128) = CONVERT(VARBINARY(128), @Username);
       SET CONTEXT_INFO @User;
        ";
   
   using (var command = connection.CreateCommand())
   {
       command.CommandText = _Command;
   
       // do not use AddWithValue()!
       SqlParameter _UserName = new SqlParameter("@Username", SqlDbType.NVarChar, 128);
       _UserName.Value = username;
       command.Parameters.Add(_UserName);
   
       command.ExecuteNonQuery();
   }
   

   And then you get the value within the Stored Procedure / User-Defined Function / Table-Valued Function / Trigger via:

   DECLARE @Username NVARCHAR(128) = CONVERT(NVARCHAR(128), CONTEXT_INFO());
   

   That works just fine for a single value, but if you need multiple values, or if you are already using CONTEXT_INFO for another purpose, then you either need to go back to one of the other methods described here, OR, if using SQL Server 2016 (or newer), you can use SESSION_CONTEXT, which is similar to CONTEXT_INFO but is a HashTable / Key-Value pairs.

   Another benefit of this approach is that CONTEXT_INFO (at least, I haven't yet tried SESSION_CONTEXT) is available in T-SQL User-Defined Functions and Table-Valued Functions.

3. Finally, another option would be to create a global temporary table. As mentioned above, global objects have the benefit of surviving sub-processes, but they also have the drawback of complicating concurrency. A seldom-used to get the benefit without the drawback is to give the temporary object a unique, session-based name, rather than add a column to hold a unique, session-based value. Using a name that is unique to the session removes any concurrency issues while allowing you to use an object that will get automatically cleaned up when the connection is closed (so no need to worry about a process that creates a global temporary table and then runs into an error before completing, whereas using a permanent table would require cleanup, or at least an existence check at the beginning).

   Keeping in mind the restriction that we cannot pass any value into the Stored Procedure, we need to use a value that already exists at the data layer. The value to use would be the session_id / SPID. Of course, this value does not exist in the app layer, so it has to be retreived, but there was no restriction placed on going in that direction.

   int _SessionId;
   
   using (var command = connection.CreateCommand())
   {
       command.CommandText = @"SET @SessionID = @@SPID;";
   
       SqlParameter _paramSessionID = new SqlParameter("@SessionID", SqlDbType.Int);
       _paramSessionID.Direction = ParameterDirection.Output;
       command.Parameters.Add(_UserName);
   
       command.ExecuteNonQuery();
   
       _SessionId = (int)_paramSessionID.Value;
   }
   
   string _Command = String.Format(@"
     CREATE TABLE ##ConnectionContextInfo_{0}(
       AttributeName VARCHAR(64) PRIMARY KEY, 
       AttributeValue VARCHAR(1024)
     );
   
     INSERT INTO ##ConnectionContextInfo_{0} VALUES('Username', @Username);", _SessionId);
   
   using (var command = connection.CreateCommand())
   {
       command.CommandText = _Command;
   
       SqlParameter _UserName = new SqlParameter("@Username", SqlDbType.NVarChar, 128);
       _UserName.Value = username;
       command.Parameters.Add(_UserName);
   
       command.ExecuteNonQuery();
   }
   

   And then you get the value within the Stored Procedure / Trigger via:

   DECLARE @Username NVARCHAR(128),
           @UsernameQuery NVARCHAR(4000);
   
   SET @UsernameQuery = CONCAT(N'SELECT @tmpUserName = [AttributeValue]
        FROM ##ConnectionContextInfo_', @@SPID, N' WHERE [AttributeName] = ''Username'';');
   
   EXEC sp_executesql
     @UsernameQuery,
     N'@tmpUserName NVARCHAR(128) OUTPUT',
     @Username OUTPUT;
   

   Please note that temporary objects -- local and global -- cannot be accessed in T-SQL User-Defined Functions or Table-Valued Functions.

4. Finally, it is possible to use a real / permanent (i.e. non-temporary) Table, provided that you include a column to hold a value specific to the current session. This additional column will allow for concurrent operations to work properly.

   You can create the table in tempdb (yes, you can use tempdb as a regular DB, doesn't need to be only temporary objects starting with # or ##). The advantages of using tempdb is that the table is out of the way of everything else (it is just temporary values, after all, and doesn't need to be restored, so tempdb using SIMPLE recovery model is perfect), and it gets cleaned up when the Instance restarts (FYI: tempdb is created brand new as a copy of model each time SQL Server starts).

   Just like with Option #3 above, we can again use the session_id / SPID value since it is common to all operations on this Connection (as long as the Connection remains open). But, unlike Option #3, the app code doesn't need the SPID value: it can be inserted automatically into each row using a Default Constraint. This simplies the operation a little.

   The concept here is to first check to see if the permanent table in tempdb exists. If it does, then make sure that there is no entry already in the table for the current SPID. If it doesn't, then create the table. Since it is a permanent table, it will continue to exist, even after the current process closes its Connection. Finally, insert the @Username parameter, and the SPID value will populate automatically.

   // assume _Connection is already open
   using (SqlCommand _Command = _Connection.CreateCommand())
   {
       _Command.CommandText = @"
          IF (OBJECT_ID(N'tempdb.dbo.Usernames') IS NOT NULL)
          BEGIN
             IF (EXISTS(SELECT *
                        FROM   [tempdb].[dbo].[Usernames]
                        WHERE  [SessionID] = @@SPID
                       ))
             BEGIN
                DELETE FROM [tempdb].[dbo].[Usernames]
                WHERE  [SessionID] = @@SPID;
             END;
          END;
          ELSE
          BEGIN
             CREATE TABLE [tempdb].[dbo].[Usernames]
             (
                [SessionID]  INT NOT NULL
                             CONSTRAINT [PK_Usernames] PRIMARY KEY
                             CONSTRAINT [DF_Usernames_SessionID] DEFAULT (@@SPID),
                [Username]   NVARCHAR(128) NULL,
                [InsertTime] DATETIME NOT NULL
                             CONSTRAINT [DF_Usernames_InsertTime] DEFAULT (GETDATE())
             );
          END;
   
          INSERT INTO [tempdb].[dbo].[Usernames] ([Username]) VALUES (@UserName);
               ";
   
       SqlParameter _UserName = new SqlParameter("@Username", SqlDbType.NVarChar, 128);
       _UserName.Value = username;
       command.Parameters.Add(_UserName);
   
       _Command.ExecuteNonQuery();
   }
   

   And then you get the value within the Stored Procedure / User-Defined Function / Table-Valued Function / Trigger via:

   SELECT [Username]
   FROM   [tempdb].[dbo].[Usernames]
   WHERE  [SessionID] = @@SPID;
   

   Another benefit of this approach is that permanent tables are accessible in T-SQL User-Defined Functions and Table-Valued Functions.

Answer 3

"There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server. 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." -- from here

so the answer to your problem is put ## instead of # to make the local temporary table to global.