0

hi I want to reproduce the sql command

SP_HELPTEXT SP_CIW_STEP1

in cursor.execute in pyodb, using ? as parameter marker .

import pyodbc

ch = pyodbc.connect('DRIVER={SQL Server};SERVER=xxx;DATABASE=yyy;Trusted_Connection=True')
cur = ch.cursor()


cur.execute("sp_helptext '?'", 'SP_CIW_STEP1')

yields error:

ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameters were supplied', 'HY000')

And (thanks @ryugie)

cur.execute("sp_helptext ?", "'SP_CIW_STEP1'")

also causes error:

[SQL Server]The object ''SP_CIW_STEP1'' does not exist in database 'xxx' or is invalid for this operation

While 

cur.execute("? 'SP_CIW_STEP1'", 'sp_helptext')

works, yielding

Out[28]: <pyodbc.Cursor at 0x9c21db0>

So it seems the single quotation ruins the parameter marker. I tried putting \ and adding r in front of the string. Doesn't work. Any help's appreciated here. Thanks-

YJZ
  • 3,934
  • 11
  • 43
  • 67

1 Answers1

2

Parameter markers shouldn't be quoted, and neither should parameter values. 

cur.execute("sp_helptext ?", "SP_CIW_STEP1")

should work just fine.

cco
  • 5,873
  • 1
  • 16
  • 21
  • oh nice! In fact the sql command `SP_HELPTEXT your_stored_proc` doesn't require quotation mark on the name of the stored procedure in the first place~ – YJZ Mar 28 '17 at 15:59