Export table SQL 2005 -> Excel 2003

[royalcheese]

Dear all

I want to export a table (SQL 2005) to Excel (2003) I have tried to use the code below

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=D:\testing.xls;', 
'SELECT * FROM [sheet1$]') select * from dbo.test_table

but get this error

Msg 7399, Level 16, State 1, Line 4
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 4
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Please can anyone help me / give me some pointers

Thanks in advance

Chris

 

[mrdenny]

Use the Import/Export wizzard to export the data. If you need to do this on a scheule use SSIS to build a package.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[royalcheese]

mrdenny thanks for the reply , I wanted to use the above so i could run this through a sp

I have made a package (.dtsx) can i run this through a Stored procedure ?

Thanks


Chris

 

[mrdenny]

You would need to use xp_cmdshell to run the SSIS package from a stored procedure. This isn't recommended how ever.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com