Problem getting results from SQLServer Database (adodbapi)

[Golawala, Moiz M (GE Infrastructure)]

Hi All, 

Could someone please help me with an issue I am having. I am having problem returning values from a Stored Procedure that creates a dynamic table (table variable) inserts values during a procedure and then I select from that dynamic table to furnish values to python.

This does not work
MY PYTHON CODE IS:

import adodbapi
connStrSQLServer = r"Provider=SQLOLEDB.1; User ID=sa; Password=tester; Initial Catalog=someDB;Data Source=someSource" 
db = adodbapi.connect(connStrSQLServer)    
cursor = db.cursor()
 
sql = """test_procedure """

cursor.execute(sql)
results = cursor.fetchall()

MY TRANSACT SQL CODE IS:

ALTER  PROCEDURE dbo.test_procedure

AS
	DECLARE @dynamicTable TABLE  (col1 int)
	INSERT INTO @dynamicTable
		VALUES (123)
	SELECT * FROM @dynamicTable

THIS IS THE ERROR I GET

   results = cursor.fetchall()
  File "C:\Python23\Lib\site-packages\adodbapi\adodbapi.py", line 692, in fetch
ll
    return self._fetch()
  File "C:\Python23\Lib\site-packages\adodbapi\adodbapi.py", line 623, in _fetc

    self._raiseCursorError(Error,None)
  File "C:\Python23\Lib\site-packages\adodbapi\adodbapi.py", line 407, in _rais
CursorError
    eh(self.conn,self,errorclass,errorvalue)
  File "C:\Python23\Lib\site-packages\adodbapi\adodbapi.py", line 38, in standa
dErrorHandler
    raise errorclass(errorvalue)
Error: None
[]

This works
The python code is the same
The SQL Code is:

ALTER  PROCEDURE dbo.test_procedure

AS
	INSERT INTO somePhysicalTable		
		VALUES (123)
	SELECT * FROM somePhysicalTable


Notice that the only difference between the 2 procedures is that in one case I am using a dynamic (table variable) table and in the other I am using an actual physical table. Does anyone have a clue why one works and the other does not.

Any help appreciated! Thanks in advance

Moiz Golawala
GE Infrastructure, Security
Software Engineer
Enterprise Solutions

T 561 912 5972
F 561 994 6572
E moiz.golawala at ge.com 
www.gesecurity.com

791 Park of Commerce Blvd., Suite 100
Boca Raton, FL, 33487, U.S.A.
GE Security, Inc.