Normally for exporting data from SQL Server to Excel one would use DTS (SQL 2k) or SSIS (SQL Server 2005). For some reason if at all you want to do it via query read on:
Step 1: Execute the code snippet
Exec sp_configure 'show advanced options', 1;
Go
Reconfigure;
Go
Exec sp_configure 'Ad Hoc Distributed Queries', 1;
Go
Reconfigure;
Go
Step 2: Create the excel file and then add the headings in the .xls file. [Important] The heading should be the same as that of the table columns.
Insert into Openrowset ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\VadivelTesting.xls;' , 'Select * from [ProductInfo$]') Select ProductID, ProductName from dbo.tblProducts
Points which might interest you:
1. As long as the file is within your C: drive this sample would work. If at all your database is in a different machine from that .xls file you need to change Database=c:\VadivelTesting.xls; to UNC path. For example, Database=\\Servername\shareName (And need to provide appropriate permission).
2. Instead of "ProductInfo" replace it with your excel worksheet name.
Step 1: Execute the code snippet
Exec sp_configure 'show advanced options', 1;
Go
Reconfigure;
Go
Exec sp_configure 'Ad Hoc Distributed Queries', 1;
Go
Reconfigure;
Go
Step 2: Create the excel file and then add the headings in the .xls file. [Important] The heading should be the same as that of the table columns.
Insert into Openrowset ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\VadivelTesting.xls;' , 'Select * from [ProductInfo$]') Select ProductID, ProductName from dbo.tblProducts
Points which might interest you:
1. As long as the file is within your C: drive this sample would work. If at all your database is in a different machine from that .xls file you need to change Database=c:\VadivelTesting.xls; to UNC path. For example, Database=\\Servername\shareName (And need to provide appropriate permission).
2. Instead of "ProductInfo" replace it with your excel worksheet name.
Comments
I wanted to know how to reverese the Ad oc settings and the show advanced options that we enable?
Regards
Dee