SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sql Server 2008 R2 Error: An error occurred while executing batch. Error message is: Error creating...


Sql Server 2008 R2 Error: An error occurred while executing batch. Error message is: Error creating window handle.

Author
Message
Teee-SQL
Teee-SQL
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 229
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
Sparky*
Sparky*
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 3
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
LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10263 Visits: 13559
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...).



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14908 Visits: 18591
Try using
SET SHOWPLAN_XML ON; 


to get the execution plan, could be a parsing error when displaying the plan.
Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search