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

[sangeethass]

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

[deepuv04]

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.

[code=sql]
INSERT INTO
OPENROWSET( 'Microsoft.Jet. OLEDB.4.0',
'Excel 8.0;Database=D: \Test.xls',
'SELECT UserId, UserName, FirstName,LastN ame FROM [Sheet1$]')
SELECT UserId, UserName, FirstName,LastN ame FROM Users
[/code]

[sangeethass]

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.

[code=sql]
INSERT INTO
OPENROWSET( 'Microsoft.Jet. OLEDB.4.0',
'Excel 8.0;Database=D: \Test.xls',
'SELECT UserId, UserName, FirstName,LastN ame FROM [Sheet1$]')
SELECT UserId, UserName, FirstName,LastN ame FROM Users
[/code]

[ck9663]

By default OPENROWSET is not enabled in most sql-server installation. Talk to your sys-ad or dba to enable it.

-- CK

[deepuv04]

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

[code=sql]
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO
[/code]
Thanks

[sangeethass]

Hi,
run the following script and then execute your query

[code=sql]
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO
[/code]
Thanks

Hi,

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

Sangeetha.S