0

  • I have an Issue in executing stored procedure from Python. It only gets executed partially. However, when I execute the same from MSSQL server, I have no Issues. I've reviewed my stored procedure several times following inputs from SQL Stored Procedures not finishing when called from Python.

  • I am unable figure out as to why pyodbc would treat below SP any differently.

Stored Procedure 

CREATE PROCEDURE 
    [dbo].[IVRP_Nodes]
AS
BEGIN
        SET NOCOUNT ON;
        DECLARE @Id nchar(16);
        DECLARE @_id nchar(16); 
        DECLARE @number int;
        DECLARE Nodes_Cursor CURSOR FOR 
        SELECT r._id,r.Number FROM  Room r
        OPEN Nodes_Cursor
        FETCH NEXT FROM Nodes_Cursor into @_id,@number; 

        WHILE @@FETCH_STATUS = 0
        BEGIN 

            DECLARE @RETN as nchar(16);
            Exec   SP_GetId Nodes , @RETN OUTPUT;
            set @Id = @RETN;
            INSERT INTO [dbo].[Nodes]([_Id],[RoomNumber],[NodeAddress], 
            [NodeType],[NodeState],[_tsUpd])
            VALUES(@Id ,@number,'1','114','0',getdate());
            FETCH NEXT FROM Nodes_Cursor into @_id,@number; 

        END; 

        CLOSE Nodes_Cursor; 
        DEALLOCATE Nodes_Cursor; 


END

In Python, I am using following code snippet:

        mydb_lock = pyodbc.connect('Driver={SQL Server Native Client 11.0};'
                               'Server=localhost;'
                               'Database=InterelRMS;'
                               'Trusted_Connection=yes;'
                               'MARS_Connection=yes;'
                               'user=sa;'
                               'password=Passw0rd;')

        mycursor_lock = mydb_lock.cursor()
        sql_nodes = "Exec IVRP_Nodes"
        mycursor_lock.execute(sql_nodes)
        mydb_lock.commit()

Any assistance or help regarding above matter would be appreciated.

Thanks.

Hisham
  • 1
  • 1
    can you be more specific when you say, `it only executes partially` – gold_cy Mar 17 '19 at 13:55
  • Above stored procedure, when executed from MS SQL server, inserts 150 records in the Database (Node table). But, when I execute, this from my Python App, it inserts only 70 odd records. – Hisham Mar 17 '19 at 14:01
  • I could not get it to work using above approach. As a workaround, I used "subprocess" instead. Precisely, I used below: subprocess.call('sqlcmd -Q "exec RegisterInsertSP" -S localhost -d InterelRMS') to solve above Issue. – Hisham Mar 20 '19 at 11:22

0 Answers0