using adodbapi - problem returning Values from Stored Procedure

[Golawala, Moiz M (GE Infrastructure)]

Hi All,

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

Thanks,
Moiz Golawala
GE Infrastructure, Security
Software Engineer
Enterprise Solutions

T 561 912 5972
F 561 994 6572
E mo***********@ge.com
www.gesecurity.com

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