- [bug] Repaired the use_scope_identity

    create_engine() flag when using the pyodbc
    dialect.  Previously this flag would be
    ignored if set to False.  When set to False,
    you'll get "SELECT @@identity" after each
    INSERT to get at the last inserted ID,
    for those tables which have "implicit_returning"
    set to False.

4 files changed, 49 insertions, 12 deletions

diff --git a/CHANGES b/CHANGES
index 88ddad8f8..c53822455 100644
--- a/CHANGES
+++ b/CHANGES
@@ -42,6 +42,15 @@ CHANGES
     passes Python unicode literals to PyODBC 
     or not.
 
+  - [bug] Repaired the use_scope_identity
+    create_engine() flag when using the pyodbc
+    dialect.  Previously this flag would be
+    ignored if set to False.  When set to False,
+    you'll get "SELECT @@identity" after each 
+    INSERT to get at the last inserted ID,
+    for those tables which have "implicit_returning"
+    set to False.
+
 - postgresql
   - [feature] Added new for_update/with_lockmode()
     options for Postgresql: for_update="read"/
diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
index e58721586..78da18711 100644
--- a/lib/sqlalchemy/dialects/mssql/base.py
+++ b/lib/sqlalchemy/dialects/mssql/base.py
@@ -146,7 +146,7 @@ Enabling Snapshot Isolation
 
 Not necessarily specific to SQLAlchemy, SQL Server has a default transaction 
 isolation mode that locks entire tables, and causes even mildly concurrent
-applications to have long held locks and frequent deadlocks.   
+applications to have long held locks and frequent deadlocks.
 Enabling snapshot isolation for the database as a whole is recommended 
 for modern levels of concurrency support.  This is accomplished via the 
 following ALTER DATABASE commands executed at the SQL prompt::
@@ -168,7 +168,7 @@ deprecated and will be removed in 0.8 - the ``s.in_()``/``~s.in_()`` operators
 should be used when IN/NOT IN are desired.
 
 For the time being, the existing behavior prevents a comparison
-between scalar select and another value that actually wants to use ``=``.  
+between scalar select and another value that actually wants to use ``=``.
 To remove this behavior in a forwards-compatible way, apply this
 compilation rule by placing the following code at the module import
 level::
@@ -176,7 +176,7 @@ level::
     from sqlalchemy.ext.compiler import compiles
     from sqlalchemy.sql.expression import _BinaryExpression
     from sqlalchemy.sql.compiler import SQLCompiler
-    
+
     @compiles(_BinaryExpression, 'mssql')
     def override_legacy_binary(element, compiler, **kw):
         return SQLCompiler.visit_binary(compiler, element, **kw)
@@ -689,18 +689,22 @@ class MSExecutionContext(default.DefaultExecutionContext):
                                         not self.executemany
 
             if self._enable_identity_insert:
-                self.cursor.execute("SET IDENTITY_INSERT %s ON" % 
-                    self.dialect.identifier_preparer.format_table(tbl))
+                self.root_connection._cursor_execute(self.cursor, 
+                    "SET IDENTITY_INSERT %s ON" % 
+                    self.dialect.identifier_preparer.format_table(tbl),
+                    ())
 
     def post_exec(self):
         """Disable IDENTITY_INSERT if enabled."""
 
+        conn = self.root_connection
         if self._select_lastrowid:
             if self.dialect.use_scope_identity:
-                self.cursor.execute(
-                "SELECT scope_identity() AS lastrowid", ())
+                conn._cursor_execute(self.cursor, 
+                    "SELECT scope_identity() AS lastrowid", ())
             else:
-                self.cursor.execute("SELECT @@identity AS lastrowid", ())
+                conn._cursor_execute(self.cursor, 
+                    "SELECT @@identity AS lastrowid", ())
             # fetchall() ensures the cursor is consumed without closing it
             row = self.cursor.fetchall()[0]
             self._lastrowid = int(row[0])
@@ -710,10 +714,11 @@ class MSExecutionContext(default.DefaultExecutionContext):
             self._result_proxy = base.FullyBufferedResultProxy(self)
 
         if self._enable_identity_insert:
-            self.cursor.execute(
+            conn._cursor_execute(self.cursor, 
                         "SET IDENTITY_INSERT %s OFF" %
                             self.dialect.identifier_preparer.
-                                format_table(self.compiled.statement.table)
+                                format_table(self.compiled.statement.table),
+                        ()
                         )
 
     def get_lastrowid(self):
diff --git a/lib/sqlalchemy/dialects/mssql/pyodbc.py b/lib/sqlalchemy/dialects/mssql/pyodbc.py
index 434cfd43c..a7cb42aac 100644
--- a/lib/sqlalchemy/dialects/mssql/pyodbc.py
+++ b/lib/sqlalchemy/dialects/mssql/pyodbc.py
@@ -242,7 +242,8 @@ class MSDialect_pyodbc(PyODBCConnector, MSDialect):
     def __init__(self, description_encoding='latin-1', **params):
         super(MSDialect_pyodbc, self).__init__(**params)
         self.description_encoding = description_encoding
-        self.use_scope_identity = self.dbapi and \
+        self.use_scope_identity = self.use_scope_identity and \
+                        self.dbapi and \
                         hasattr(self.dbapi.Cursor, 'nextset')
         self._need_decimal_fix = self.dbapi and \
                             self._dbapi_version() < (2, 1, 8)
diff --git a/test/dialect/test_mssql.py b/test/dialect/test_mssql.py
index dddc6333d..2b35ff57f 100644
--- a/test/dialect/test_mssql.py
+++ b/test/dialect/test_mssql.py
@@ -750,7 +750,8 @@ class QueryUnicodeTest(fixtures.TestBase):
         finally:
             meta.drop_all()
 
-class QueryTest(fixtures.TestBase):
+from test.lib.assertsql import ExactSQL
+class QueryTest(testing.AssertsExecutionResults, fixtures.TestBase):
     __only_on__ = 'mssql'
 
     def test_fetchid_trigger(self):
@@ -818,6 +819,27 @@ class QueryTest(fixtures.TestBase):
             con.execute("""drop trigger paj""")
             meta.drop_all()
 
+    @testing.provide_metadata
+    def test_disable_scope_identity(self):
+        engine = engines.testing_engine(options={"use_scope_identity":False})
+        metadata = self.metadata
+        metadata.bind = engine
+        t1 = Table('t1', metadata,
+                Column('id', Integer, primary_key=True),
+                implicit_returning=False
+        )
+        metadata.create_all()
+
+        self.assert_sql_execution(
+                testing.db,
+                lambda: engine.execute(t1.insert()),
+                ExactSQL("INSERT INTO t1 DEFAULT VALUES"),
+                # we dont have an event for 
+                # "SELECT @@IDENTITY" part here.
+                # this will be in 0.8 with #2459
+        )
+        assert not engine.dialect.use_scope_identity
+
     def test_insertid_schema(self):
         meta = MetaData(testing.db)
         con = testing.db.connect()