Q:
Outputting results of SQL SELECT statements into a spreadsheet
I am using OPENROWSET to output the result of a number of SQL SELECT statements into a spreadsheet. It works for most of the SELECT statements, but there are 4 random queries out of 82 that take more than an hour to execute. This is the code:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:MGTempESWIConvAuditQueriescnvWriteOff.xls;',
'SELECT Phone, Member, FullName, TransAmount, TransType, WOAmount, WOCode, WODate FROM [PhoneMemberNotInSerord$]')
select Phone, Member, FullName, TransAmount, TransType, WOAmount, WOCode, WODate from cnvWriteOff_SWI a
where not exists (select * from cnvSerord_SWI b
where a.Phone = b.Phone
and a.Member = b.Member)
Any thoughts?
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:MGTempESWIConvAuditQueriescnvWriteOff.xls;',
'SELECT Phone, Member, FullName, TransAmount, TransType, WOAmount, WOCode, WODate FROM [PhoneMemberNotInSerord$]')
select Phone, Member, FullName, TransAmount, TransType, WOAmount, WOCode, WODate from cnvWriteOff_SWI a
where not exists (select * from cnvSerord_SWI b
where a.Phone = b.Phone
and a.Member = b.Member)
Any thoughts?
ASKED:
Nov 7 2008 8:54 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
select Phone, Member, FullName, TransAmount, TransType, WOAmount, WOCode, WODate from cnvWriteOff_SWI a
where not exists (select * from cnvSerord_SWI b
where a.Phone = b.Phone
and a.Member = b.Member)
You may have an indexing problem that you need to look for. If this recordset returns quickly then it is going to be an Excel problem.
