InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 09:15:22 GMT20http://www.sqlservercentral.com/Forums/Topic1369716-392-1.aspxHi All, I'm running the query below in SSMS 2008 R2 and it takes longer than expected and when I include the Actual Query Execution Plan I get the error above and then management studion shuts down.I don't know what could be wrong with the script that's causing the error or maybe it needs to be simplyfied. Please help. please let me know if i should post sample data as well.ALTER PROCEDURE [dbo].[spReport_RequisitionDetail] @StartDate datetime = NULL, @EndDate datetime = NULL, @LocationID int = NULL, @LineofserviceID int = NULL, @RecruiterID int = NULL, @BusinessUnitID int = NULLAS declare @sql nvarchar(4000) -- Requisition Fields are built up dynamically. Build temporary table with data in a fixed structure -- for use in select lower down create table #tmpRequisitionData (intRequisitionId int) -- Table to store dynamic SQL statements for execution create table #tmpCreateTableSQL (SQL nvarchar(4000)) -- Load all fields available in requisition form -- Use data type in refRequisition to determine column type. if none given set to nvarchar insert into #tmpCreateTableSQL select distinct 'alter table #tmpRequisitionData add [' + r.nvcDisplay + '] ' + case isnull(substring(r.nvcControlName, 1, 3), 'lbl') when 'lbl' then 'nvarchar(256)' when 'ddl' then 'nvarchar(256)' when 'txt' then 'nvarchar(1000)' when 'chk' then 'bit' when 'ddl' then 'int' when 'cld' then 'datetime' end from refRequisition r where r.bitDeleted = 0 and r.intPageID = 3 and --requisition details page isnull(r.nvcGroupName, '') = '' --grouped items should not be a column each rather use the heading delete from #tmpCreateTableSQL where [SQL] is null or [SQL] = '' set @sql = (select top 1 [SQL] from #tmpCreateTableSQL) print '@sql: ' + @sql while @sql is not null begin exec sp_sqlexec @sql delete from #tmpCreateTableSQL where [SQL] = @sql set @sql = (select top 1 [SQL] from #tmpCreateTableSQL) print '@sql: ' + @sql end -- select * from #tmpRequisitionData -- load in data from dtlDynamicFormFieldValue to new rpt table set @sql = 'insert into #tmpRequisitionData(intRequisitionId)' + ' select distinct rr.intRequisitionId ' + ' from relRequisitions rr inner join dtlRequisition r on rr.intRequisitionId = r.intRequisitionId' + ' where rr.bitDeleted=0 and r.bitDeleted=0 and r.isComplete=0 ' print '@sql: ' + @sql --Implement filtering if selected, having to do some hard coding here as it's just not feasible to dynamically work this out if (@LocationID is not null) begin declare @LocationRefID varchar(64) set @LocationRefID = (select RefRequisitionID from refRequisition where vchRefTableName = 'refLocation' and intPageID=3) set @sql = @sql + ' and rr.intRequisitionId in (select intRequisitionId from relRequisitions where bitDeleted=0 and intRefID = ' + @LocationRefID + ' and nvcValue = ' + convert(varchar, @LocationID) + ')' print '@sql: ' + @sql end if (@LineofserviceID is not null) begin declare @LineOfServiceRefID varchar(64) set @LineOfServiceRefID = (select RefRequisitionID from refRequisition where vchRefTableName = 'refLineOfService' and intPageID=3) set @sql = @sql + ' and rr.intRequisitionId in (select intRequisitionId from relRequisitions where bitDeleted=0 and intRefID = ' + @LineOfServiceRefID + ' and nvcValue = ' + convert(varchar, @LineofserviceID) + ')' print '@sql: ' + @sql end if (@RecruiterID is not null) begin set @sql = @sql + ' and r.intRecruiterId = ' + convert(varchar, @RecruiterID) print '@sql: ' + @sql end if (@BusinessUnitID is not null) begin declare @BusinessUnitRefID varchar(64) set @BusinessUnitRefID = (select RefRequisitionID from refRequisition where vchRefTableName = 'refDivision' and intPageID=3) set @sql = @sql + ' and rr.intRequisitionId in (select intRequisitionId from relRequisitions where bitDeleted=0 and intRefID = ' + @BusinessUnitRefID + ' and nvcValue = ' + convert(varchar, @BusinessUnitID) + ')' print '@sql: ' + @sql end --Dates Filter if (@StartDate is not null and @EndDate is not null) begin set @sql = @sql + ' and r.CreateDate between convert(datetime, ''' + convert(varchar, @StartDate, 103) + ''', 103) and convert(datetime, ''' + convert(varchar, @EndDate, 103) + ''', 103)' print '@sql: ' + @sql end --print '@sql: ' + @sql exec sp_executesql @sql print '@sql: ' + @sql insert into #tmpCreateTableSQL select case isnull(substring(r.nvcControlName, 1, 3), 'lbl') when 'ddl' then 'update #tmpRequisitionData set [' + r.nvcDisplay + '] = ' + --populate int and value for dropdowns case when rr.nvcValue is not null then '(select [' + r.vchRefReportColumn + '] from ' + r.vchRefTableName + ' where ' + r.vchRefPrimaryKey + ' = ''' + rr.nvcValue + ''')' end + ' where intRequisitionId = ''' + convert(nvarchar(16), rr.intRequisitionId) + '''' when 'lbl' then 'update #tmpRequisitionData set [' + r.nvcDisplay + '] = ' + --populate value for checkbox grouping case when isnull(childrr.nvcValue, '') = '' then 'null' else '''' + childrr.nvcValue + '''' end + ' where intRequisitionId = ''' + convert(nvarchar(16), childrr.intRequisitionId) + '''' else 'update #tmpRequisitionData set [' + r.nvcDisplay + '] = ' + case isnull(substring(r.nvcControlName, 1, 3), 'lbl') when 'txt' then case when len(rr.nvcValue) > 1000 then 'substring(' else '' end + 'N''' + substring(replace(isnull(rr.nvcValue, 'null'), '''', ''''''), 1, 1000) when 'chk' then '' + isnull(rr.nvcValue, 'null') when 'cld' then case isnull(rr.nvcValue, '') when '' then 'null' when '01/01/0001 00:00:00' then 'null' else 'convert(datetime, substring(''' + rr.nvcValue end end + case isnull(substring(r.nvcControlName, 1, 3), 'lbl') when 'txt' then '''' + case when len(rr.nvcValue) > 1000 then ', 1, 1000)' else '' end when 'chk' then '' when 'cld' then case isnull(rr.nvcValue, '') when '' then '' when '01/01/0001 00:00:00' then '' else ''', 1, 10), 103)' end end + ' where intRequisitionId = ' + convert(nvarchar(16), rr.intRequisitionId) end from refRequisition r left join relRequisitions rr on r.RefRequisitionID = rr.intRefID and rr.bitDeleted = 0 left join refRequisition childr on childr.intParentID = r.RefRequisitionID left join relRequisitions childrr on childr.RefRequisitionID = childrr.intRefID where r.bitDeleted = 0 and isnull(r.nvcGroupName, '') = '' and --grouped items should not be a column each rather use the heading r.intPageID = 3 --requisition details page delete from #tmpCreateTableSQL where [SQL] is null or [SQL] = '' --select * from #tmpCreateTableSQL set @sql = (select top 1 [SQL] from #tmpCreateTableSQL) while @sql is not null begin exec sp_sqlexec @sql delete from #tmpCreateTableSQL where [SQL] = @sql set @sql = (select top 1 [SQL] from #tmpCreateTableSQL) print '@sql: ' + @sql end drop table #tmpCreateTableSQL select req.nvcRequisitionName, trd.*, isnull(approver1.LastName, '') + ', ' + isnull(approver1.FirstName, '') as [First Approver], case isnull(approver1status.name, '') when '' then 'Waiting' when 'Yes' then 'Approved' else 'Denied' end as [First Approver Status], isnull(approver2.LastName, '') + ', ' + isnull(approver2.FirstName, '') as [Second Approver], case isnull(approver2status.name, '') when '' then 'Waiting' when 'Yes' then 'Approved' else 'Denied' end as [Second Approver Status], isnull(approver3.LastName, '') + ', ' + isnull(approver3.FirstName, '') as [Third Approver], case isnull(approver3status.name, '') when '' then 'Waiting' when 'Yes' then 'Approved' else 'Denied' end as [Third Approver Status], isnull(approver4.LastName, '') + ', ' + isnull(approver4.FirstName, '') as [Fourth Approver], case isnull(approver4status.name, '') when '' then 'Waiting' when 'Yes' then 'Approved' else 'Denied' end as [Fourth Approver Status], isnull(approver5.LastName, '') + ', ' + isnull(approver5.FirstName, '') as [Fifth Approver], case isnull(approver5status.name, '') when '' then 'Waiting' when 'Yes' then 'Approved' else 'Denied' end as [Fifth Approver Status] from dtlRequisition req inner join #tmpRequisitionData trd on trd.intRequisitionID = req.intRequisitionID left join Admin approver1 on req.intFirstApproverID = approver1.Id left join Admin approver2 on req.intSecondApproverID = approver2.Id left join Admin approver3 on req.intThirdApproverID = approver3.Id left join Admin approver4 on req.intFourthApproverID = approver4.Id left join Admin approver5 on req.intFifthApproverID = approver5.Id left join refIncremental approver1status on req.intFirstApproverStatus = approver1status.value left join refIncremental approver2status on req.intSecondApproverStatus = approver2status.value left join refIncremental approver3status on req.intThirdApproverStatus = approver3status.value left join refIncremental approver4status on req.intFourthApproverStatus = approver4status.value left join refIncremental approver5status on req.intFifthApproverStatus = approver5status.value drop table #tmpRequisitionDataThanks TeeeMon, 08 Oct 2012 05:00:50 GMTTeee