How many times have I needed to do this for reporting purposes? I remember coming up with some convoluted process that did the trick but was in no way as elegant as the solution suggested in this post (or the one in the comments).
Thinking of migrating the database portion of your Access-based (JET SQL) solution to SQL Server (T-SQL)? Well Jeff Smith just posted a quick MS Access (JET SQL) to SQL Server (T-SQL) Cheatsheet to help you in this process.
Stephen Swoyer has published a brief analysis at TDWI on why the SQL Server installed base seems to be growing relatively slowly. Bottom line: the learning curve is steep and corporations cannot easily replace prior SQL Server versions running mission-critical applications.
You’ve got to love his quote of Adam Machanic (SQL Server MVP) saying that “comparing DTS to SSIS… would be like comparing a pea shooter to a neutron bomb[…]”. I’ve been rather more involved with Analysis Services than Integration Services, but this is exactly the way I’ve felt about the OLAP side of things…
As far as desktop data management goes, the big news with Microsoft’s latest release has got to be the free SQL Server Express Edition. From a data storage & processing standpoint, this is a interesting alternative to Microsoft Access. You may still need the latter (or another Office component such as Excel) as a graphical interface into your data, but if you’re expecting your requirements to grow, this will be much easier to scale to an enterprise version when required.
Updated: Learning curve or not, this version of Microsoft’s database offering is worth the effort, according to some.