1

I have MSSQL stored procedure that takes 1 input parameter. When I execute it in SQL client it returns a set of rows and columns:

+-------------+----------+
|         text|        dt|
+-------------+----------+
|Sample Text 1|2019-01-01|
|Sample Text 2|2019-03-01|
|Sample Text 3|2019-06-01|
|Sample Text 4|2019-02-01|
+-------------+----------+

I tried to simulate the same behavior using pyodbc but none of the solutions I found worked.

I tried this code and many of its variations:

cnxn = pyodbc.connect(f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={host};DATABASE={db};UID={user};PWD={password}')
sql = """\
SET NOCOUNT ON;
DECLARE @return_value nvarchar(max);
EXEC    @return_value = [dbo].[procname] 'someInputParam';
SELECT  'Return Value' = @return_value;
"""
crsr = cnxn.execute(sql)
data = crsr.fetchall()
print(data)
crsr.nextset()
data = crsr.fetchall()
print(data)

which returns 

[]
[('0', )]

Is there any way I can extract data from both columns using pyodbc?

Byrdziu
  • 107
  • 1
  • 9
  • @Parfait A solution like this is posted on pyodbc GH so it can't be an issue, the stored procedure runs fine, I simply can't see its results. https://github.com/mkleehammer/pyodbc/wiki/Calling-Stored-Procedures – Byrdziu Aug 05 '19 at 15:04
  • I am unable to reproduce your issue. Does your stored procedure begin with `SET NOCOUNT ON;` as well? – Gord Thompson Aug 05 '19 at 15:10
  • @GordThompson No, I have no right to edit the stored procedure and even view its source code but I believe it doesn't contain 'SET NOCOUNT ON'. Is this an issue? – Byrdziu Aug 05 '19 at 15:12
  • 1
    It appears not. I just verified that my test code works okay with or without `SET NOCOUNT ON;` in the SP itself provided that I have it in the anonymous code block. – Gord Thompson Aug 05 '19 at 15:20

0 Answers0