INSERT INTO OPENROWSET not working

[ZipperHeadMan]

Hi, I'd appreciate some advise as I'm not an SQL guru by any means
When I run this query it executes without error, only problem is the spreadsheet is not being populated with the data I'm expecting to see. Just the column headers are there. Is there something I'm missing?


sp_configure 'show advanced options', 1
reconfigure
go

sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
go

sp_configure 'xp_cmdshell', '1'
reconfigure
go

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\lmsout\export.xls;', 'SELECT * FROM [sheet1$]')

SELECT "EMP_JOB"."JOB", "EMP_MAST"."LASTNAME", "EMP_MAST"."FIRSTNAME", "EMP_JOB"."STATUSCD", "EMP_JOB"."HISTORIC", "EMP_JOB"."CLASSCD", "EMP_JOB"."POSTNCD", "POSTNCTR"."POSTNDESC", "POSTNCTR"."HISTORIC", "POSTNCTR"."REPTONAME", "POSTNCTR"."TREELEVEL", "EMP_MAST"."EMPNO"
FROM ("DATA"."dbo"."EMP_JOB" "EMP_JOB" INNER JOIN "DATA"."dbo"."POSTNCTR" "POSTNCTR" ON ("EMP_JOB"."HISTORIC"="POSTNCTR"."HISTORIC") AND ("EMP_JOB"."POSTNCD"="POSTNCTR"."POSTNCD")) INNER JOIN "DATA"."dbo"."EMP_MAST" "EMP_MAST" ON "EMP_JOB"."EMPNO"="EMP_MAST"."EMPNO"
WHERE ("EMP_JOB"."STATUSCD" LIKE 'H' OR "EMP_JOB"."STATUSCD" LIKE 'P' OR "EMP_JOB"."STATUSCD" LIKE 'R' OR "EMP_JOB"."STATUSCD" LIKE 'S' OR "EMP_JOB"."STATUSCD" LIKE 'T') AND "EMP_JOB"."HISTORIC"=0 AND "POSTNCTR"."HISTORIC"=0
ORDER BY "POSTNCTR"."TREELEVEL", "POSTNCTR"."REPTONAME"


go

sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure
go

sp_configure 'xp_cmdshell', '0'
reconfigure
go

sp_configure 'show advanced options', 0
reconfigure
go

 

[PWise]

first question if you run the select statment without the insert does it return records

 

[ZipperHeadMan]

Yes if I run this statement by itself, it returns the records I expect to see.

SELECT "EMP_JOB"."JOB", "EMP_MAST"."LASTNAME", "EMP_MAST"."FIRSTNAME", "EMP_JOB"."STATUSCD", "EMP_JOB"."HISTORIC", "EMP_JOB"."CLASSCD", "EMP_JOB"."POSTNCD", "POSTNCTR"."POSTNDESC", "POSTNCTR"."HISTORIC", "POSTNCTR"."REPTONAME", "POSTNCTR"."TREELEVEL", "EMP_MAST"."EMPNO"FROM ("DATA"."dbo"."EMP_JOB" "EMP_JOB" INNER JOIN "DATA"."dbo"."POSTNCTR" "POSTNCTR" ON ("EMP_JOB"."HISTORIC"="POSTNCTR"."HISTORIC") AND ("EMP_JOB"."POSTNCD"="POSTNCTR"."POSTNCD")) INNER JOIN "DATA"."dbo"."EMP_MAST" "EMP_MAST" ON "EMP_JOB"."EMPNO"="EMP_MAST"."EMPNO"WHERE ("EMP_JOB"."STATUSCD" LIKE 'H' OR "EMP_JOB"."STATUSCD" LIKE 'P' OR "EMP_JOB"."STATUSCD" LIKE 'R' OR "EMP_JOB"."STATUSCD" LIKE 'S' OR "EMP_JOB"."STATUSCD" LIKE 'T') AND "EMP_JOB"."HISTORIC"=0 AND "POSTNCTR"."HISTORIC"=0ORDER BY "POSTNCTR"."TREELEVEL", "POSTNCTR"."REPTONAME