May 4, 2011 at 9:26 am
USING Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) and Microsoft office professional plus 2010 version 14.0.5128.5000 (32- Bit)
SQL is installed in English, while office and the O/S is installed in German, beyond making it harder for me to work with, I don't know what impact that has. :crazy:
I am looking to export data to an excel spreadsheet, usually I go with openrowset or SSIS.
There is no jet provider for the openrowset (that I can find) and there is no ssis with express.
Any ideas would be most welcome.
Thanks in advance
Ian
May 4, 2011 at 1:42 pm
440692 I am just a number (5/4/2011)
USING Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) and Microsoft office professional plus 2010 version 14.0.5128.5000 (32- Bit)SQL is installed in English, while office and the O/S is installed in German, beyond making it harder for me to work with, I don't know what impact that has. :crazy:
I am looking to export data to an excel spreadsheet, usually I go with openrowset or SSIS.
There is no jet provider for the openrowset (that I can find) and there is no ssis with express.
Any ideas would be most welcome.
Thanks in advance
Ian
I believe this is the equivalent of the Jet provider that should work here
Try looking for information about using it with OpenRowSet by searching on Microsoft Office 12 access database engine openrowset
This might also be a good resource for you http://www.sqlservercentral.com/Forums/Topic931754-391-1.aspx
If those don't work, can you use CLRs in Express?
Have you tried linking to the SQL Server database from Excel?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 5, 2011 at 2:02 am
Thank you Stefan.
I will follow up on these links.
A first try with installing the AccessDatabaseEngine was a little painfull.
I can only install the 32 bit version (while office is 32 bit), but the 64 bit SQL does not find the driver.
the CLR option is blocked by business rules (and another battle) I will look to put this into excel functionality as a last resort.
Ian
May 5, 2011 at 7:05 am
440692 I am just a number (5/5/2011)
Thank you Stefan.I will follow up on these links.
A first try with installing the AccessDatabaseEngine was a little painfull.
I can only install the 32 bit version (while office is 32 bit), but the 64 bit SQL does not find the driver.
the CLR option is blocked by business rules (and another battle) I will look to put this into excel functionality as a last resort.
Ian
Does this need to be a regular, automated job? If it is a one-off and you get desperate, you can always do a cut and paste from the SSMS results window. Not a great solution, but it gets it done.
Funny that SQL isn't finding the driver, that's the one I use with 64 bit SSIS. Let me check and see if there's a different driver to use with 64 bit SQL Server.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 5, 2011 at 7:25 am
There's a 64 bit version of the driver available here
but I don't know if that'll connect to your 32 bit office apps.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 5, 2011 at 8:14 am
Stefan Krzywicki (5/5/2011)
There's a 64 bit version of the driver available herebut I don't know if that'll connect to your 32 bit office apps.
The last 64 bit version I tried would not install while there was a 32 bit version on the machine, I will try again.
I really appreciate your time with this 🙂 Thanks
(& the cut and paste, it will be a nightly job)
May 5, 2011 at 8:19 am
440692 I am just a number (5/5/2011)
Stefan Krzywicki (5/5/2011)
There's a 64 bit version of the driver available herebut I don't know if that'll connect to your 32 bit office apps.
The last 64 bit version I tried would not install while there was a 32 bit version on the machine, I will try again.
I really appreciate your time with this 🙂 Thanks
(& the cut and paste, it will be a nightly job)
Ugh, nightly makes cut & paste not an option.
I'm guessing you're not able to buy a version of SQL Server that comes with SSIS?
Do you have Visual Studio? I suppose you could write a C# or VB.Net app that'll do the extract for you. I know both of those suggestions are somewhat ridiculous, but I'm just trying to think of ways for you to get the data out if you can't get the drivers to work.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 5, 2011 at 8:37 am
Stefan Krzywicki
I'm guessing you're not able to buy a version of SQL Server that comes with SSIS?
DOH! No SQL Server Agent jobs in express, looks like I am raiding that piggy bank.
May 5, 2011 at 8:46 am
440692 I am just a number (5/5/2011)
Stefan Krzywicki
I'm guessing you're not able to buy a version of SQL Server that comes with SSIS?
DOH! No SQL Server Agent jobs in express, looks like I am raiding that piggy bank.
Sorry. Sometimes that does end up being the only real solution.
Have you used SSIS to create exports?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 5, 2011 at 9:38 am
Thanks.
I built the original solution with an ssis export, then find out that in the production environment we would only have access to sql express.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply