I would like to use FME to handle an execution plan from MS SQL. The `SET SHOWPLAN_XML ON` function has to be run in it own batch. The normal way would be to us the `GO` operator, however this is not part of t-sql but is interpreted by the sqlcmd and osql utilities and therefore the FME SQL interpreter does not understand it. I've tried running it in one SQL Executor transform followed by the query in the next SQL Executor, but each transform seems to create it's own connection. This means that the settings don't persist from one transformer to the next.
Question
In SQL transformers, is it possible to use Set SHOWPLAN_XML ON?
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+5- Contributor
- 16 replies
Had a brainwave and have managed to use the python transformer to run it using
pyodbc. This can execute queries in their own batch, whilst maintaining a single connection.
My Python Caller transformer goes like this:
import fmeobjects
import pyodbc
class FeatureCreator(object):
def __init__(self):
pass
def input(self,feature):
newFeature = fmeobjects.FMEFeature()
connectionStr = f"DRIVER={{SQL Server}};SERVER={FME_MacroValues['Server']};Trusted_Connection=yes;"
if FME_MacroValues['Database'] != '':
connectionStr += f"DATABASE={FME_MacroValues['Database']};"
connection = pyodbc.connect(connectionStr)
cursor = connection.cursor()
cursor.execute("SET SHOWPLAN_XML ON;")
cursor.commit()
cursor.execute(feature.getAttribute('query'))
results = cursor.fetchall()[0][0]
newFeature.setAttribute("showplan", results)
self.pyoutput(newFeature)
def close(self):
pass