Trouble upsizing Access database to SQL Server at shared hosting provider
For the past two weeks, one of my clients has been having trouble upsizing his Microsoft Access 2003 database (MDB) into a SQL Server database at his shared hosting provider. The problem was that the tables would be created fine, but then the upsizing would fail when Access attempted to insert the data.
I attempted the same procedure on my machine, to the same database, using Access 2007 and did not have any trouble. The table and data were upsized properly.
So my initial thought was something wrong with my client’s specific installation of Access 2003. However, he installed Access 2003 on a different computer, and even tried it from another ISP, but still had the same problem.
Fortunately, our regional Developer Evangalist gave my client a free technical support ticket, which he used to contact Microsoft about the problem. I have to say that I was really impressed with what Microsoft Technical Support came back with.
It turns out that Access 2003 issues the following T-SQL command during the upsizing process:
sp_configure ‘show advanced option’,1
The reason for this is that by default, SQL Server only displays 14 basic configuration settings when running the sp_configure stored procedure. By setting the ‘show advanced option’ value on, you are able to get all 50-60 options from that stored procedure. So for some reason, the Access upsizing wizard must try to run this command to check for a specific option value set in SQL Server.
Normally, this wouldn’t be a problem, as most developers run as sysadmins on their SQL Servers. However, in this situation, my client was trying to upload to a database as a shared hosting provider, so he had limited permissions.
So it turns out that the sp_configure ‘show advanced option’,1 command is a one-time deal. It just has to be run once on the server, and then sp_configure will then show the extended list of option values for all users. Now the battle turns to convincing the host to run this magic T-SQL command that will make life easier.
If all else fails, the solution will be to just upsize to a local SQL Server Express Edition database, back up the database, upload it to the host, and restore it there. However, it would be nice for my client if he didn’t have to do that.
It’s curious, though, that the problem does not occur in Access 2007. This suggests to me that someone knew about the bug and fixed it in the 2007 release. However, what is odd is that I can’t find a KnowledgeBase article about it.
So I figured I’d blog about this hear, with the hope that this information may save someone else the time of figuring this all out.
Follow comments to this post through RSS 2.0.
Both comments and pings are currently closed.
Comments are closed.