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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Sql Server 2008 R2 Error: An error occurred...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Teee

Posted Monday, October 08, 2012 5:00 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 1:23 AM
Points: 31, Visits: 121

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

« Prev Topic | Next Topic »

Permissions