G
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 (e-mail address removed)
www.gesecurity.com
791 Park of Commerce Blvd., Suite 100
Boca Raton, FL, 33487, U.S.A.
GE Security, Inc.
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 (e-mail address removed)
www.gesecurity.com
791 Park of Commerce Blvd., Suite 100
Boca Raton, FL, 33487, U.S.A.
GE Security, Inc.