troubleshooting Question
VB.NET/SQL Server Output to Excel - (via integration services?)
Hi All,
I'm trying to figure out the best way to output data from a query to an excel spreadsheet. I have an existing vb.net app that works closely with SQL Server 2005. I want the ability to simply push a button from my app that will export the data to excel.
I know that there are many different ways to do this, but I'm not sure the best path given my requirements:
- I actually have multiple queries and I would like to place the results of each in a differnet sheet in excel.
- I have specific headers and data tranlations/formats for my excel file - including the coloring of some of the rows and possibly conditional formating code
- I will be upgrading from SQL Server 2005 to 2008 in the next week.
I am open to any reasonable solution at this point. I have experimmented with Integration Services and seems to be a reasonable solution, but I have a few questions/isues. I don't quite understand how to transform some of the data from my database. I added a "Character Map", but that doesn't really seem to offer much. I want to do thing like contert varchar fields to text fields, money fields to $, float to % percentage, smalldatetime to short date,......... Also, when I tried to select the Table/fields that will be exported to excel, I recieved errors about data translations. the Errors complained about double preciesion. When I only selected varchar fields, everything worked.
If I do go down the integration services route, I have a few more questions. Should this integration services piece be a stand alone project or simply built into my existing VB.Net project? In either case, how can I call this "function"? Also, in order to make this generic, can I create parameters for my query or simply pass a query to use into the integration services project?
I am not sure but I'm hoping that there are new capabilities in SQL Server 2008 that will make some of this easier.
As I mentioned above, I'm open to any reasonable, extend able and maintainable solution.
Thanks,
John
I'm trying to figure out the best way to output data from a query to an excel spreadsheet. I have an existing vb.net app that works closely with SQL Server 2005. I want the ability to simply push a button from my app that will export the data to excel.
I know that there are many different ways to do this, but I'm not sure the best path given my requirements:
- I actually have multiple queries and I would like to place the results of each in a differnet sheet in excel.
- I have specific headers and data tranlations/formats for my excel file - including the coloring of some of the rows and possibly conditional formating code
- I will be upgrading from SQL Server 2005 to 2008 in the next week.
I am open to any reasonable solution at this point. I have experimmented with Integration Services and seems to be a reasonable solution, but I have a few questions/isues. I don't quite understand how to transform some of the data from my database. I added a "Character Map", but that doesn't really seem to offer much. I want to do thing like contert varchar fields to text fields, money fields to $, float to % percentage, smalldatetime to short date,......... Also, when I tried to select the Table/fields that will be exported to excel, I recieved errors about data translations. the Errors complained about double preciesion. When I only selected varchar fields, everything worked.
If I do go down the integration services route, I have a few more questions. Should this integration services piece be a stand alone project or simply built into my existing VB.Net project? In either case, how can I call this "function"? Also, in order to make this generic, can I create parameters for my query or simply pass a query to use into the integration services project?
I am not sure but I'm hoping that there are new capabilities in SQL Server 2008 that will make some of this easier.
As I mentioned above, I'm open to any reasonable, extend able and maintainable solution.
Thanks,
John
Learn from the best
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.