Call the stored procedure on the remote sqlserver server with Python. Code fragment:
conn = pyodbc.connect(SERVER=host, UID=user, PWD=password, DATABASE=dbname,
DRIVER=driver)
cur = conn.cursor()
if not cur:
raise (NameError, 'Database connection error)
else:
cur.execute("EXEC GetLastData")
resList = list()
resList = cur.fetchall()
Execution error:
pyodbc.ProgrammingError: No results. Previous SQL was not a query.
After checking, the stored procedure can be executed normally in the sqlserver environment. It seems that there is a problem when calling pyodbc. A similar problem is found on stackoverflow. The answer is as follows:
the problem was solved by adding set NOCOUNT on; to the beginning of the anonymous code block. That statement suppresses the record count values generated by DML statements like UPDATE … and allows the result set to be retrieved directly.
The problem is solved by adding it to the beginning of the anonymous code block. This statement suppresses the record count value generated by the DML statement and allows the set result to be retrieved directly. SET NOCOUNT ON; UPDATE …
So add a sentence set NOCOUNT on to the stored procedure
CREATE proc [dbo].[GetLastData]
AS
BEGIN
SET NOCOUNT ON
declare @begindate datetime,@enddate datetime
select @begindate=CONVERT(varchar(7),GETDATE(),120)+'-01'
select @enddate=DATEADD(MONTH,1,@begindate)
