October 8, 2012 at 5:00 am
Hi 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 = NULL
AS
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 #tmpRequisitionData
Thanks
Teee
May 18, 2014 at 8:46 am
Hi Teee,
I did not run your procedure but with large queries and/or lots of outputs you should first set "Results to Text".
Otherwise you run into this error, which basically means you are running out of memory. :w00t:
Edit: MS also encourages to SET NOCOUNT ON in procedures.
Enjoy,
Sparky
May 18, 2014 at 10:23 am
I don't think it's an issue of the queries being "large".
More likely the two while loops *cough* generate a huge number of execution plans being generated and displayed leading to the error message.
Regardless of the original question: there's most likely more than one alternative to speed up this process and to get rid of the loops.
But this would require something to test against (DDL, sample data, expected result...).
May 18, 2014 at 10:27 am
Try using
SET SHOWPLAN_XML ON;
to get the execution plan, could be a parsing error when displaying the plan.
😎
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply