Unable to get SCOPE_IDENTITY() on SQL Server 2019

[SkoZombie]

Environment

• Python: Python 3.9.0, 64bit
• pyodbc: 4.0.32
• OS: Windows 10 20H2
• DB: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19042: )
• driver: ODBC Driver 17 for SQL Server

Issue

We've recently moved to PyODBC from pymssql. We make use of SCOPE_IDENTITY() to obtain the last inserted ID.

For the following code, we would expect to see the ID of the inserted row, we actually get None (inside a tuple)

    pyo_conn = pyodbc.connect('SERVER=localhost;DATABASE=test;Trusted_Connection=yes;DRIVER={ODBC Driver 17 for SQL Server}')
    pyo_cursor = pyo_conn.execute("INSERT INTO dummy(note) VALUES (?)", "This is a test")
    pyo_cursor = pyo_conn.execute("SELECT SCOPE_IDENTITY()")
    print("PyODBC SCOPE_IDENTITY()", pyo_cursor.fetchone())

We can get @@IDENTITY however this will be changed if a trigger inserts a row so we can't rely on it.

The schema we're using is a super simple one for testing:

CREATE TABLE dummy (
    [id] BIGINT NOT NULL IDENTITY,
    [note] VARCHAR(100) NOT NULL,
    CONSTRAINT pk_dummy PRIMARY KEY ([ID])
)

[v-chojas]

The short answer is that they are not in the same scope due to the use of prepared statements. Try to put the select in the same batch as the insert.

[gordthompson]

Further to comment from @v-chojas , you can do something like this:

sql = """\
SET NOCOUNT ON;
INSERT INTO dummy(note) VALUES (?);
SELECT SCOPE_IDENTITY();
"""
pyo_cursor = pyo_conn.execute(sql, "This is a test")
print("PyODBC SCOPE_IDENTITY()", pyo_cursor.fetchone())
# PyODBC SCOPE_IDENTITY() (Decimal('2'), )

[amarynets]

I have the same issue with pyodbc 4.1 and 5.x ODBC driver 18 DB: Microsoft SQL Azure (RTM) - 12.0.2000.8 This query returns None

SET NOCOUNT ON;
INSERT INTO dummy(note) VALUES (?);
SELECT SCOPE_IDENTITY();

while this one

pyo_cursor = pyo_conn.execute("INSERT INTO dummy(note) VALUES ('This is a test')")
pyo_cursor = pyo_conn.execute("SELECT SCOPE_IDENTITY()")

works well. As you may see, if I put values directly into query - it works well