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

Question

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

1) create a temporary table when opening a connection

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 using the procedure that is used to populate the DataTable using SqlDataAdapter.Fill 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) the called procedure tries to get the username as follows:

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

but #ConnectionContextInfo no longer available in context.

I put the SQL profiler in the database to check what happens:

• A temporary table is created using a specific SPID. Procedure
• called using the same SPID

Why is the temporary table not available within the scope?

T-SQL does the following:

• create temporary table
• call a procedure that needs data from this temporary table
• the temporary table is discarded only explicitly or after the end of the current region.

Thanks.

Answer 1

LESSON QUESTION: At the moment, I am going to assume that the code posted in the Question is not a complete piece of code that runs. Not only variables that we do not see in the declaration are used (for example, AllowInsertConnectionContextInfo ), but there is an obvious absence in the setConnectionContextInfo method: the command object was created, but its CommandText property was never set before commandBuilder.ToString() , therefore, it is an empty SQL package. I am sure that this is actually handled correctly, since 1) I believe that sending an empty batch will raise an exception, and 2) the question mentions that creating the temp table appears in the output of SQL Profiler. However, I point this out because it means that additional code can be added that is related to the observed behavior that is not shown in the question, making it difficult to give an exact answer.

WHAT SAID, as pointed out by @Vladimir fine answer , because of a query executed in a subprocess (i.e. sp_executesql ), local temporary objects - tables and stored procedures - cannot withstand the completion of this subprocess and are therefore not available in the parent context .

Global temporary objects and permanent / non-temporary objects will be sustained after the subprocess finishes, but both of these options, in their typical use, present concurrency problems: you will need to first check for existence by trying to create a table, and you will need a way to distinguish one process from another. Thus, this is not a very good option, at least not in their typical use (more on this later).

Assuming you cannot pass any values ​​to the Stored procedure (otherwise you could just pass username as @Vladimir suggested in his answer), you have several options:

• The simplest solution, given the current code, would be to separate the creation of the local temporary table from the INSERT (also mentioned in @Vladimir's answer). As mentioned earlier, the problem you are facing is related to the query running inside sp_executesql . And the reason why sp_executesql is used is to process the @Username parameter. Thus, a fix can be as simple as changing the current code:

   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(); } 

  Note that temporary objects — local and global — cannot be accessed in custom T-SQL functions or in table functions.

• The best solution (most likely) would be to use CONTEXT_INFO , which is essentially session memory. This value is VARBINARY(128) , but changes to it are saved in any subprocess since it is not an object. This not only eliminates the current complexity that you are encountering, but also reduces the number of tempdb I / O, given that you create and drop a temporary table every time this process is executed and perform an INSERT , and all three of these operations are written to disk twice: first in the transaction log, then in the data file. You can use it as follows:

   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 in the stored procedure / user-defined function / table function / trigger with:

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

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

  Another advantage of this approach is that CONTEXT_INFO (at least I haven't tried SESSION_CONTEXT yet) is available in custom T-SQL functions and table functions.

• Finally, another option would be to create a global temporary table. As mentioned above, global objects have the advantage of surviving subprocesses, but they also have the disadvantage of complicating concurrency. Rarely used to take advantage without a disadvantage is to provide a temporary object with a unique session-based name, rather than adding a column to hold a unique session-based value. Using a unique name for the session eliminates any concurrency problems, allowing you to use an object that will be automatically cleaned when the connection is closed (so there is no need to worry about the process that creates the global temporary table, and then an error occurs before executing the error, while using a constant the table requires cleaning, or at least checking for existence at the beginning).

  Bearing in mind that we cannot pass any value to the Stored Procedure, we need to use a value that already exists at the data level. The value used will be session_id / SPID. Of course, this value does not exist in the application layer, so it needs to be restored, but there were no restrictions on movement in this 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 in the Stored Procedure / Trigger with:

   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; 

  Note that temporary objects — local and global — cannot be accessed in custom T-SQL functions or in table functions.

• Finally, you can use a real / permanent (i.e. not temporary) table, provided that you include a column to store a value specific to the current session. This additional column will allow you to work with parallel operations.

  You can create a table in tempdb (yes, you can use tempdb as a regular DB, it doesn't have to be only temporary objects starting with # or ## ). The advantages of using tempdb are that the table does not match everything else (after all, these are just temporary values ​​and do not need to be restored, therefore tempdb using the SIMPLE recovery model is ideal) and it clears when the instance is restarted (FYI: tempdb is created as a new instance of model every time you start SQL Server).

  As in the case of option No. 3, we can again use the session_id / SPID value, since it is common to all operations in this connection (while the connection remains open). But, unlike option No. 3, the application code does not need the SPID value: it can be automatically inserted into each line using the default restriction. This simplifies the operation a bit.

  The concept here is to first check if a persistent table exists in tempdb . If so, make sure there is no entry in the current SPID. If not, create a table. Since this is a persistent table, it will continue to exist, even after the current process completes the join. Finally, insert the @Username parameter and the SPID will be automatically populated.

   // 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 in the stored procedure / user-defined function / table function / trigger with:

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

  Another advantage of this approach is that persistent tables are available in custom T-SQL functions and in table functions.

Answer 2

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

The C # code in this question does not explicitly CommandType and Text by default , so most likely the end result of the code is that CREATE TABLE #ConnectionContextInfo wrapped in sp_executesql . You can check it in SQL Profiler.

It is well known that sp_executesql works in its own field (in fact, it is a nested stored procedure). Find "sp_executesql temporary table". Here is one example: Run sp_executeSql to select ... in #table, but cannot select the tempo table data

So, the temporary table #ConnectionContextInfo is created in the nested sp_executesql and is automatically deleted as soon as sp_executesql returned. The following query executed by adapter.Fill does not see this temp table.

---

What to do?

Ensure that the CREATE TABLE #ConnectionContextInfo not enclosed in sp_executesql .

In your case, you can try to split one batch containing both CREATE TABLE #ConnectionContextInfo and INSERT INTO #ConnectionContextInfo into two batches. The first packet / query will contain only the CREATE TABLE statement without any parameters. The second packet / request would contain an INSERT INTO with parameters (s).

I'm not sure if this will help, but worth a try.

If this does not work, you can create one T-SQL batch that creates a temporary table, inserts data into it, and calls your stored procedure. All in one SqlCommand, all in one batch. All of this SQL will be wrapped in sp_executesql , but it does not matter, because the area in which the temporary table is created will be the same area in which the stored procedure is called. Technically, this will work, but I would not recommend following this path.

---

There is no answer to the question, but a proposal to solve the problem.

Honestly, the whole approach looks strange. If you want to pass some data to a stored procedure, why not use the parameters of this stored procedure. This is what they are intended for - to transfer data to the procedure. There is no real need to use a temporary table for this. You can use the table-valued parameter ( T-SQL ,. NET ) if the data you pass is complex. This is definitely overkill if it's just a Username .

In your stored procedure, you need to know the temporary table, it must know its name and structure, so I don’t understand what the problem is with the explicit table parameter. Even your procedure code has not changed much. You would use @ConnectionContextInfo instead of #ConnectionContextInfo .

Using temporary tables for what you described makes sense to me only if you are using SQL Server 2005 or earlier, which does not have table parameters. They were added in SQL Server 2008.

Answer 3

"There are two types of temporary tables: local and global. They differ from each other by their names, their visibility and their accessibility. Local temporary tables have one number sign (#) as the first character of their names, they are visible only to the current connection for the user and 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 per table are disconnected from the instance of SQL Server. " - from here

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