Exporting SQL Server Records To Excel
Oct
14
Written by:
10/14/2009 4:09 PM
Exporting SQL Server Records To Excel
We have a large amount of data that we want to provide to our users through Excel files downloaded from our website. We found that by using the Openrowset command in a stored procedure afforded us the most versatility and the easiest redundant execution than the other options. For a nice explanation of the process and a full listing of the variables available take a look at MSSQLTips.
The final code looks like this:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT * From dbo.MyTable Where Date = GetDate()
GO
There was a slight error with this implementation at one point in the day where we received this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Cannot open database ''.
A number of sites point you to the temp folder of the Administration login on the SQL Server. This is a good place to check read/write privileges for the account you are logged in as. But we found that restarting the SQL Server Agent correct the issue.
5 comment(s) so far...
Re: Exporting SQL Server Records To Excel
nice to know..Thank you so much for sharing!
By film på nett on
10/19/2009 3:17 PM
|
Re: Exporting SQL Server Records To Excel
Very useful. The code is also easy and simple to implement. Thanks for the valuable information.
By david on
11/23/2009 2:52 AM
|
Exporting SQL Server Records To Excel
Hi,
Combined with the free connection technique for database and report, you can easily import the data of Excel file, and then submit the data to database after checking data validity and user rights.
By mountain tents on
11/28/2009 1:08 AM
|
Re: Exporting SQL Server Records To Excel
Exporting data from SQL Server to Excel can be achieved in a variety of ways. Some of these options include Data Transformation Services , SQL Server Integration Services and Bulk Copy . Data Transformation Services and SQL Server Integration Services offers a GUI where widgets can be dragged and dropped Each option has advantages and disadvantages, but all can do the job.
By Venice Hotels on
8/16/2010 5:33 AM
|
Re: Exporting SQL Server Records To Excel
Exporting data from SQL Server to Excel seems like a reasonably simple request. SQL Server offers many ways to Import/Export the data into any heterogeneous data sources. Exporting the data into Excel can be done with BCP also.
By Heathrow Taxi on
7/24/2011 11:47 PM
|