Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sql Server 2008 R2 Error: An error occurred while executing batch. Error message is: Error creating window handle. Expand / Collapse
Author
Message
Posted Monday, October 8, 2012 5:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 13, 2014 7:52 AM
Points: 62, Visits: 222
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
Post #1369716
Posted Sunday, May 18, 2014 8:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, May 18, 2014 8:44 AM
Points: 1, 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.
Edit: MS also encourages to SET NOCOUNT ON in procedures.

Enjoy,
Sparky
Post #1572086
Posted Sunday, May 18, 2014 10:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:13 PM
Points: 6,842, Visits: 13,364
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
Post #1572094
Posted Sunday, May 18, 2014 10:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:05 AM
Points: 2,222, Visits: 6,011
Try using
SET SHOWPLAN_XML ON; 

to get the execution plan, could be a parsing error when displaying the plan.
Post #1572095
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse