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.

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

[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.

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

[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

    sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    RECONFIGURE
    GO
 

Thanks

[sangeethass]

Hi,
run the following script and then execute your query

    sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    sp_configure 'Ad Hoc Distributed Queries', 1
    RECONFIGURE
    GO
 

Thanks

Hi,

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

Sangeetha.S