SQL data to Excel - - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

SQL data to Excel -

Author

Topic

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2010-08-19 : 14:56:25

I want to be able to export a table from SQL to EXCEL and I would think this would be rather simple task (not really) I am experimenting with the following code:

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

However i am getting this error message:
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

I have downloaded every imaginable file from Microsoft and i am still getting this error - is the "Microsoft.Jet.OLEDB.4.0" suppose to be reg/installed on the database server or my desktop? I am thinking the DB server.

anyone out there have a solution?

thanks, pizzazzz

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-19 : 15:07:11

see

http://support.microsoft.com/kb/321686

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2010-08-19 : 16:03:18

Thanks - however everything on this site refers to exporting from Excel to SQL table - I want to do the opposite; from SQL table to Excel from a SQL script/Query.

thanks, pizzazzz

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-19 : 16:43:02

have you looked into bcp?


exec xp_cmdshell @Query=N'BCP "SELECT * FROM YourTable" queryout "YourPath\testing.xls"  -e""YourPath\errorlog.log" -T -S"YourSourceServer"',@NoOutput=N'No_Output'

or just try it in DOS cmd window


BCP "SELECT * FROM YourTable" queryout "YourPath\testing.xls"  -e"YourPath\errorlog.log" -T -S"YourSourceServer"

If you don't have the passion to help people, you have no passion

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2010-08-19 : 17:55:20

utimately i want a stored procedure to loop through some logic and place a series of excel files in a folder. if just tried running this and got the following error: I guess I do not have permissions to exec xp_cmdshell (argh)

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-19 : 18:08:33

SSIS package is your friend. did you try the bcp stuff

If you don't have the passion to help people, you have no passion

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-20 : 05:08:18

Also read this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2010-08-20 : 11:55:23

yes i have throughly read this posting and it would be the best solution for me, BUT, i am getting this error -
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

i have downloaded every possible file from Microsoft but nothing works - maybe someone can set me straight on how to register this OLE component. i am working with Excel 2007 - and SQL Server 2008.

thanks - pizzazzz

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 11:58:43

which version are you using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2010-08-20 : 12:02:44

which version of what? excel? sql server? office?

Excel 2007 - sql Server 2008 office 2007... is this what you were asking?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-20 : 13:28:01

As i suspected your OLEDB provider is wrong. Excel 2007 means you need to use Microsoft.ACE.OLEDB.12.0.
try like


SELECT * FROM
OPENROWSET
(          
            'Microsoft.ACE.OLEDB.12.0',
            'Data Source=C:\testing.xlsx;Extended Properties="Excel 12.0;HDR=YES"',
            'SELECT * FROM [SheetName$]'
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2010-08-20 : 17:19:22

Thanks, i really appreciate your help, I tried that and now getting this error message:
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

this method is very attractive to me - it would fit into my process perfectly.

is this component to be registered on my machine or the DBserver? I have downloaded (supposedly) the correct component from microsoft on my local machine.

I am going to start asking our DBA folks for help! unless someone knows how I can resolve this! help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-21 : 13:24:15

http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2010-08-23 : 13:16:27

Again, i am getting "The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered"

again, do you think this is suppose to be registered on the DB server and not on my local machine?

thanks, pizzazzz

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 13:19:51

it should be registered on server and not on your local machine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pizzazzz
Yak Posting Veteran

55 Posts

Posted - 2010-08-23 : 17:17:05

Well, there-in lays the problem...

i'll have to beg the dba to install this component on the server, dba's are a different breed you know! :)

thanks, for all your help!

beach-bum from san diego!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources