November 22, 2010 at 11:45 am
SQL Server documentation says that not all of ODBC or OLE DB data sources are supported when report is published on 2008 Windows Server.
I have Microsoft Access 2003 mdb data file. Both ODBC and OLE DB data sources that connect to Access 2003 database work well in BIDS. They do not work, returning different types of errors, from OLE DB Drivers cannot be found to DSN name not found, when report is published to Windows 2008 Server. Is this behaviour by design? What should I do, is there any work around to establish connection to Access 2003 database?
Thank you.
November 23, 2010 at 6:52 am
Just out of curiosity, is this a 64-bit SQL Server? IS Bids running on a 32 bit development machine? This is most likely your issue. HAve a look at the following to try and work through your issue. Ther is a link tot he 64bit ACE driver at the bottom which may help...
http://blogs.msdn.com/b/psssql/archive/2010/01/21/how-to-get-a-x64-version-of-jet.aspx
-Luke.
November 23, 2010 at 11:38 am
Hello Luke,
Thank you very much for your quick response.
My BIDs is on Windows 2008 Server 64 bit.
My Access database is Access 2003 -- 32 bit.
From what I read 64bit version of Jet engine will
not work with older versions of Access. Is that right?
I did try a 32 bit version of Data Source Administrator on Windows 2008 to create a DSN connecting to Access 2003. I tested connection and it worked fine in ODBC Data Source Administrator. When I published my report and datasource and referred to this DSN by name, it was not recognized. Thank you again.
Rustam
November 23, 2010 at 12:28 pm
I don't have something to test this scenario with, but you need to make sure that you can create a datasource with the 32bit drivers on your server to access the older access file (I think). They'll have to run in Windows on windows to work. Perhaps you could create a shared datasource from the server and then use that datasource name to build your report.
Or you could port your data to SQL Express and get around the issue all together? Or use SSIS to extract the data from your access database and into a SQL database? I personally hate this as a solution (telling you to re-architect the entire thing), because I don't know your business requirements and whatnot, but your could create an Access ADP with the data in a backend SQL database, or your could try to upgrade the access database to one of the newer versions.
-Luke.
November 23, 2010 at 1:06 pm
Hello Luke,
I did exactly as you recommended. I created a DSN using 32 bit drivers on Win 2008 (but essentially what is the difference if I can provide a string representation of any data source without a need to run ODBC administrator). The most important is that the Windows 2008 servers should have 32 bit ODBC or OLEDB (I tested both) drivers and it does.
I will try to create a linked server on SQL 2008 to get data from Access 2003 to avoid using direct connection to Access 2003.
One other thing that I did not try, but read about is configuring IE worker process for 32 bit. Maybe this is something that prevents my datasources from working properly?
Speaking about test scenario, if you had a Windows 2008 Server with SQL 2008 R2 and BIDs all installed on one server. And your report had to pull data from Access 2003 mdb database located on another computer on one network. Will it work?
Thank you very much for all your ideas
Rustam
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply