Alexei Alexei - 15 days ago 8
SQL Question

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

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

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.

Comments