Hey there! Let's clear this up right away: this isn't a pyodbc bug, and your SQL logic is actually correct—the issue comes down to how SQL Server returns results for mixed update/query batches, and how pyodbc handles those results.

Why You're Seeing the Error #

When you run your SQL batch, SQL Server sends back two separate result sets:

1. First, it returns the number of rows affected by the UPDATE statement (this is default behavior for SQL Server DML statements)
2. Second, it sends the results from your final SELECT query

Pyodbc starts at the first result set, which isn't a query result—it's just the row count. When you call fetchone() on that, it throws the "No results" error because there's no actual data to fetch there.

Two Easy Fixes #

Option 1: Add SET NOCOUNT ON to Your SQL #

This tells SQL Server to skip returning the row count for DML statements, so only your SELECT result comes through. Update your query like this:

SET NOCOUNT ON;
DECLARE @UpdateOutput1 table (Oldexp_num int,Newexp_num int);
UPDATE get_exp_num SET exp_num = exp_num+1 
OUTPUT DELETED.exp_num, INSERTED.exp_num INTO @UpdateOutput1;
select * from @UpdateOutput1;

With this change, your existing pyodbc code will work as-is—cursor.fetchone() will grab the SELECT results directly.

Option 2: Skip the First Result Set in Pyodbc #

If you don't want to modify your SQL, you can tell pyodbc to move past the row-count result set before fetching data:

import pyodbc
# Replace ... with your actual connection parameters
connection = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};SERVER=your_server;DATABASE=your_db;UID=your_user;PWD=your_pw")
query = "DECLARE @UpdateOutput1 table (Oldexp_num int,Newexp_num int);UPDATE get_exp_num SET exp_num = exp_num+1 OUTPUT DELETED.exp_num, INSERTED.exp_num INTO @UpdateOutput1; select Newexp_num from @UpdateOutput1;"
cursor = connection.cursor()
cursor.execute(query)
# Skip the UPDATE's row-count result set
cursor.nextset()
# Now fetch the actual results
result = cursor.fetchone()
print(f"New value: {result[0]}")

Quick Verification Tip #

If you run your original SQL in SSMS with SET NOCOUNT OFF (the default), you'll see it shows "(1 row affected)" above your query results—this is exactly what pyodbc is seeing first. SSMS just hides that row count from you by default in the results pane, but pyodbc doesn't.

内容的提问来源于stack exchange，提问作者Shubham R