By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,357 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

how to export sql server-2005 table data into excel sheet

P: 4
How to export sql server-2005 table data into excel sheet ..

Can u tell me how can i do this both my manually and by programmewise using perl

please help me...

thanks
Sangeetha.S
Mar 20 '08 #1
Share this Question
Share on Google+
5 Replies


deepuv04
Expert 100+
P: 227
How to export sql server-2005 table data into excel sheet ..

Can u tell me how can i do this both my manually and by programmewise using perl

please help me...

thanks
Sangeetha.S
Hi,
Assume the table is Users.

use the following query to export data into excel sheet.
Make sure that the excel sheet must be on the server and the excel sheet has appropriate column headings at the first row.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO
  2.     OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
  3.                 'Excel 8.0;Database=D:\Test.xls',
  4.                 'SELECT UserId, UserName, FirstName,LastName FROM [Sheet1$]')
  5.     SELECT UserId, UserName, FirstName,LastName FROM Users 
  6.  
Mar 20 '08 #2

P: 4
Hi,

thanks for ur reply..
But if i try ur coding the below mentioned error msg is apperring..
So wat i have to do to rectify this.
thnks
Sangeetha.S
P.S.our Db is centralised...


Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.









Hi,
Assume the table is Users.

use the following query to export data into excel sheet.
Make sure that the excel sheet must be on the server and the excel sheet has appropriate column headings at the first row.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO
  2.     OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
  3.                 'Excel 8.0;Database=D:\Test.xls',
  4.                 'SELECT UserId, UserName, FirstName,LastName FROM [Sheet1$]')
  5.     SELECT UserId, UserName, FirstName,LastName FROM Users 
  6.  
Mar 21 '08 #3

ck9663
Expert 2.5K+
P: 2,878
By default OPENROWSET is not enabled in most sql-server installation. Talk to your sys-ad or dba to enable it.

-- CK
Mar 21 '08 #4

deepuv04
Expert 100+
P: 227
Hi,

thanks for ur reply..
But if i try ur coding the below mentioned error msg is apperring..
So wat i have to do to rectify this.
thnks
Sangeetha.S
P.S.our Db is centralised...


Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
Hi,
run the following script and then execute your query

Expand|Select|Wrap|Line Numbers
  1.     sp_configure 'show advanced options', 1
  2.     RECONFIGURE
  3.     GO
  4.     sp_configure 'Ad Hoc Distributed Queries', 1
  5.     RECONFIGURE
  6.     GO
  7.  
Thanks
Mar 24 '08 #5

P: 4
Hi,
run the following script and then execute your query

Expand|Select|Wrap|Line Numbers
  1.     sp_configure 'show advanced options', 1
  2.     RECONFIGURE
  3.     GO
  4.     sp_configure 'Ad Hoc Distributed Queries', 1
  5.     RECONFIGURE
  6.     GO
  7.  
Thanks



Hi,

It is very helpful to me..
Thanks for ur help...

Sangeetha.S
Mar 24 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.