From 07ba8e0a37daeb4304e8fede43b13e402b01dbeb Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Tue, 15 Sep 2020 18:48:36 -0400
Subject: Correct for SQL Server temp table owner

on my machine, the owner for a temp table comes out as
dbo, and i am testing against a CI machine.  im not sure
what happens on a CI machine except perhaps that it provisions
new databases is changing things.   in any case, since we
are searching the tempdb for the name, get the schema/owner also.

Also refines the test to use a single connection and a transaction
that rolls back, doesn't hang here but let's see what CI does.

Change-Id: I522596ccc526cdab14c516b9a566ff666ac57dd6
---
 lib/sqlalchemy/dialects/mssql/base.py | 68 ++++++++++++++++++++---------------
 1 file changed, 39 insertions(+), 29 deletions(-)

(limited to 'lib/sqlalchemy')

diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index 519d74d89..7564536a5 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -2938,45 +2938,54 @@ class MSDialect(default.DefaultDialect):
             return view_def
 
     def _get_internal_temp_table_name(self, connection, tablename):
-        result = connection.execute(
-            sql.text(
-                "select table_name "
-                "from tempdb.information_schema.tables "
-                "where table_name like :p1"
-            ),
-            {
-                "p1": tablename
-                + (("___%") if not tablename.startswith("##") else "")
-            },
-        ).fetchall()
-        if len(result) > 1:
-            raise exc.UnreflectableTableError(
-                "Found more than one temporary table named '%s' in tempdb "
-                "at this time. Cannot reliably resolve that name to its "
-                "internal table name." % tablename
+        # it's likely that schema is always "dbo", but since we can
+        # get it here, let's get it.
+        # see https://stackoverflow.com/questions/8311959/
+        # specifying-schema-for-temporary-tables
+
+        try:
+            return connection.execute(
+                sql.text(
+                    "select table_schema, table_name "
+                    "from tempdb.information_schema.tables "
+                    "where table_name like :p1"
+                ),
+                {
+                    "p1": tablename
+                    + (("___%") if not tablename.startswith("##") else "")
+                },
+            ).one()
+        except exc.MultipleResultsFound as me:
+            util.raise_(
+                exc.UnreflectableTableError(
+                    "Found more than one temporary table named '%s' in tempdb "
+                    "at this time. Cannot reliably resolve that name to its "
+                    "internal table name." % tablename
+                ),
+                replace_context=me,
             )
-        elif len(result) == 0:
-            raise exc.NoSuchTableError(
-                "Unable to find a temporary table named '%s' in tempdb."
-                % tablename
+        except exc.NoResultFound as ne:
+            util.raise_(
+                exc.NoSuchTableError(
+                    "Unable to find a temporary table named '%s' in tempdb."
+                    % tablename
+                ),
+                replace_context=ne,
             )
-        else:
-            return result[0][0]
 
     @reflection.cache
     @_db_plus_owner
     def get_columns(self, connection, tablename, dbname, owner, schema, **kw):
         is_temp_table = tablename.startswith("#")
         if is_temp_table:
-            tablename = self._get_internal_temp_table_name(
+            owner, tablename = self._get_internal_temp_table_name(
                 connection, tablename
             )
-        # Get base columns
-        columns = (
-            ischema.mssql_temp_table_columns
-            if is_temp_table
-            else ischema.columns
-        )
+
+            columns = ischema.mssql_temp_table_columns
+        else:
+            columns = ischema.columns
+
         computed_cols = ischema.computed_columns
         if owner:
             whereclause = sql.and_(
@@ -3016,6 +3025,7 @@ class MSDialect(default.DefaultDialect):
         )
 
         c = connection.execution_options(future_result=True).execute(s)
+
         cols = []
         for row in c.mappings():
             name = row[columns.c.column_name]
-- 
cgit v1.2.1