How can I use CREATE PROCEDURE (and related commands) inside a script passed by pyodbc to SQL?
I have a pure T-SQL procedure (named "#CreateCsvResultSet") that produces a "CSV format" result set, complete with CSV header line. I am NOT allowed to use SQL stored procedures so every script must contain (and use) the definition of that temporary procedure.
Scripts that contain and use that temporary procedure definition execute without problems when run from within Microsoft SSMS 2012.
After making the simple edits needed to receive parameters from pydobc (see sample below), the scripts get copied into their own files and are run as needed. This approach has been working for years, the only thing new is my attempt at including and using the #CreateCsvResultSet procedure in a sample script.
When invoked by pyodbc that script fails at the "CREATE PROCEDURE #CreateCsvResultSet" line (even when it is the first line of the script), returning this "one-line" error (broken apart for clarity) :
pyodbc.ProgrammingError: ('42000', '
Incorrect syntax near the keyword \'PROCEDURE\'. (156) (SQLExecDirectW);
Must declare the scalar variable "@TableName". (137);
Must declare the scalar variable "@TableName". (137);
Must declare the scalar variable "@IncludeHeaderLine". (137);
Statement(s) could not be prepared. (8180)
')
with all 5 error lines prefixed by "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]".
The 3 "Must declare..." errors are explainable since they are input parameters to the #CreateCsvResultSet procedure.
If SQL (invoked via pyodbc) would allow "CREATE PROCEDURE", etc. (at least for temporary tables) I wouldn't have a problem. Am I missing something obvious?
Background information
The environment is:
- 64 bit Win 7 Enterprise SP1
- 64 bit Python 3.6.4 (v3.6.4:d48eceb, Dec 19 2017, 06:54:40) [MSC v.1900 64 bit (AMD64)] with pyodbc plugin : 4.0.23
- Database : MS SQL Server 2012
- 64 bit Eclipse is used to run the python program. It's version information is: Eclipse Oxygen.3a Release (4.7.3a), Build id: 20180405-1200, using pydev plugin : 6.3.3.201805051638
I am aware of how to call stored procedures from pyodbc (wiki page: https://github.com/mkleehammer/pyodbc/wiki/Calling-Stored-Procedures) and have read the investigation mentioned on that page: (https://github.com/mkleehammer/pyodbc/issues/184). For completeness, Microsoft's OBDC instructions are here: https://learn.microsoft.com/en-us/sql/relational-databases/native-client-odbc-how-to/running-stored-procedures-call-stored-procedures
My situation is a bit different from that covered by the wiki. In my case using the suggested approach results in a 'chicken-and-egg" Catch-22.
Using cursor.execute( "{CALL #CreateCsvResultSet (?, ?, ? ) }", ParameterList ) in the python code below won't work because #CreateCsvResultSet will NOT exist until the script is executed by SQL.
And with that approach there doesn't seem to be a way to pass in the script (the contents of SQL_COMMANDS) that defines the procedure!
I've even tried converting the mainline of the passed in script into a procedure then calling that using the wiki method - but that just causes another occurance of my original problem.
The python 3.X code that loads and invokes the SQL script is straightforward:
Database = pyodbc.connect( Driver = SQL_Driver, # = '{SQL Server}'
Server = SQL_Server, # = 'ServerId.---.ca\\ai1'
Database = SQL_Database, # = 'DatabaseName' (= SQL's "USE DatabaseName" command )
Trusted_Connection = TrustWorthyNess # = 'yes'
, APP = 'Python report generator' # suggested as good idea by https://code.google.com/archive/p/pyodbc/wikis/FAQs.wiki
, autocommit = True # see https://github.com/mkleehammer/pyodbc/wiki/Calling-Stored-Procedures
)
cursor = Database.cursor( )
ParameterList = ( 'National', '2018-05-01 00:00:00.000', 2018-05-31 23:59:59.000 )
Textfile = open( SQL_QueryName, 'r' )
SQL_COMMANDS = Textfile.read()
''' At this point the definition of #CreateCsvResultSet (kept in it's own file) can be merged into SQL_COMMANDS '''
cursor.execute( SQL_COMMANDS, ParameterList )
The #CreateCsvResultSet procedure is structured as per the accepted answer to this question: Use of temporary functions or procedure within a script. (See also accepted answer to this Python - pyodbc call stored procedure with parameter name and this wiki page: https://code.google.com/archive/p/pyodbc/wikis/Cursor.wiki.)
Here's a skeleton of the entire script passed to SQL (inside the SQL_COMMANDS parameter):
CREATE PROCEDURE #CreateCsvResultSet -- very first line of the script
(
@TableName as nvarchar( MAX ), -- produce a "CSV file format" version of this result set
@OrderedBy as nvarchar( MAX ) = '', -- ORDER BY clause applied to @TableName
@IncludeHeaderLine as nvarchar( 1 ) = 'Y' -- default to including a CSV column header line
)
AS
BEGIN
-- Details omitted. Note that this procedure uses dynamic sql invoked by using EXECUTE( @SqlCommandString )
-- as well as the CREATE TABLE #X, ALTER TABLE #X, DELETE FROM #X, INSERT INTO #X and SELECT INTO #X statements
RETURN
END
-------------------------------------------
--
-- Mainline of script (has worked for years, still works if above procedure and
-- the EXECUTE line below are commented out.
--
SET NOCOUNT ON -- supress the unwanted affected row counts
-- These 3 variables receive the parameters received from Python
DECLARE @target_Site nvarchar( 8 )
DECLARE @StartAtString nvarchar( 30 )
DECLARE @EndAtString nvarchar( 30 )
SET @target_Site = ? -- e.g. 'Montreal'
SET @StartAtString = ? -- e.g. '2018-05-01 00:00:00.000'
SET @EndAtString = ? -- e.g. '2018-05-31 23:59:59.000'
.... ( details of query definition omitted, note that it uses UPDATE @<a temporary table>
-- Get (a much simplified version of) the final result set; results in random order
select day_of_month,
@target_location as Site,
CONVERT( nvarchar( 19 ), @LowLimit, 120 ) as ReportPeriodStart,
CONVERT( nvarchar( 19 ), @HighLimit, 120 ) as ReportPeriodEnd
INTO #ResultSet
from @monthly_counts
-- Returns the "CSV formatted" result set in proper order but WITHOUT the CSV header line.
--SELECT * from #ResultSet -- returns the result set in correct order
--ORDER BY day_of_month ASC
-- (When invoked from within SQL server) the next line produces the same result
-- set as the above query, optionally including a CSV hdader line.
EXECUTE #CreateCsvResultSet #ResultSet, 'ORDER BY day_of_month ASC', 'Y'
One other thing of note:
According to this question Python: Pyodbc execute stored procedure with parameters pyodbc defaults to "autocommit = False" whereas everything I've read says that "autocommit = True" is MS-SQL's default setting. To get round this difference I added"autocommit = True" to the pyodbc.connect call.
BTW, the only obviously related question suggested by stackoverflow was SQL query fails when using pyodbc, but works in SQL. The accepted answer suggested breaking up the scripts into multiple parts and executing each separately. It worked in that case because a persistent database was being created and then used. In my case that approach won't work because I must use temporary objects which immediately become undefined once the SQL script exits.
