How to create Excel sheet dynamically in SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to create Excel sheet dynamically in SQL

Author

Topic

Rupali
Starting Member

2 Posts

Posted - 2008-08-19 : 08:08:29

Hi,
I have written one cursor which do some processing on each row of temp table and inserting the resultset into on Excel sheet. I have written only one insert rowset stmt to insert the result into excel sheet.I am generating sheetname as dynamicaly passing over insert.

e.g.
declare @n int
set @n=1
-- loop starts here
-- do some processing on 1st row n then insert result into shhet

SET @Insert = 'INSERT INTO openrowset (''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=D:\data\Result.xls'' , ''SELECT * FROM [Sheet'+convert(varchar,@n)+']'')'+
'(name,id,Add)'+
'select * from #Temp'

EXEC (@Insert)
set @n=@n+1

-- loops end here

I want that code should insert the each result into Sheet1, sheet2..n so on respectively...!! Can anyone help in that..?
I able to create sheet name as Sheet1, Sheet2 in code but the abot code gives err as
"[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object 'sheet1'", but i already have created that sheets with same name..