I'm currently running SQL Prompt 8.0.8.2086 but have been having this problem for many weeks, also on previous versions, until I finally figured out what was triggering it yesterday.
I've had a set of queries that when you run Format SQL it errors out usually with the message of:
"
Error laying out sql
SQL Prompt was unable to complete this operation.
Problem areas have been highlighted."
Usually, the highlight is on the first character of the first line and says:
"Could not find parent of type Microsoft.SQLServer.TransactSql.ScriptDom.ExecuteStatement"
Here is a simplified version of the SQL code that's causing the problem.
DECLARE @StartDate AS DATE = '2017-09-01';
DECLARE @EndDate AS DATE = '2017-09-15';
DECLARE @MonthsBack AS INT = 6;
DECLARE @SafetyPlanStart DATE;
SET @SafetyPlanStart = DATEADD(MONTH, -@MonthsBack, @StartDate);
IF OBJECT_ID('tempdb..#safetyplans') IS NOT NULL
DROP TABLE #safetyplans;
CREATE TABLE #safetyplans
(
MRN INT,
DocumentDate DATE--,
--DateStored DATE,
);
INSERT INTO #safetyplans
(
MRN,
DocumentDate--,
--DateStored
)
EXECUTE (
N'SELECT
safetyplans.MRN,
safetyplans.DocumentDate--,
--safetyplans.DateStored
FROM
OBPRD.dbo.SafetyPlansByDates (?, ?, ?, ?) safetyplans', @SafetyPlanStart, @EndDate, NULL, NULL) AT [OBDB\OBPROD];
SELECT * FROM #safetyplans;
If I comment out the INSERT INTO portion up to but not including EXECUTE then it will format without an issue.
Hi @kenneyh ,
I've had a set of queries that when you run Format SQL it errors out usually with the message of:
"Error laying out sql
SQL Prompt was unable to complete this operation.
Problem areas have been highlighted."
Usually, the highlight is on the first character of the first line and says:
"Could not find parent of type Microsoft.SQLServer.TransactSql.ScriptDom.ExecuteStatement"
Here is a simplified version of the SQL code that's causing the problem.
DECLARE @StartDate AS DATE = '2017-09-01'; DECLARE @EndDate AS DATE = '2017-09-15'; DECLARE @MonthsBack AS INT = 6; DECLARE @SafetyPlanStart DATE; SET @SafetyPlanStart = DATEADD(MONTH, -@MonthsBack, @StartDate); IF OBJECT_ID('tempdb..#safetyplans') IS NOT NULL DROP TABLE #safetyplans; CREATE TABLE #safetyplans ( MRN INT, DocumentDate DATE--, --DateStored DATE, ); INSERT INTO #safetyplans ( MRN, DocumentDate--, --DateStored ) EXECUTE ( N'SELECT safetyplans.MRN, safetyplans.DocumentDate--, --safetyplans.DateStored FROM OBPRD.dbo.SafetyPlansByDates (?, ?, ?, ?) safetyplans', @SafetyPlanStart, @EndDate, NULL, NULL) AT [OBDB\OBPROD]; SELECT * FROM #safetyplans;If I comment out the INSERT INTO portion up to but not including EXECUTE then it will format without an issue.