SQL Server 2008 & Office Accounting 2009 Installation
- Having an issue getting Office Accounting 2009 to use SQL Server 2008 on Vista 64 Ultimate.
The installed instance of SQL Server 2008 doesn't show up in the install of the Office Accounting 2009. Using the MSOA included data tools to set the instanct name (after installing MSOA client only) results in the following error "The database that you requested could not be opened, or access is denied"
Setting the instance name to something random produces an expected error, "The server was not found or was not accessible".
I've ensured named pipes are enabled and that TCP/IP configuration includes port 5356 under IPAll in SQL Server Configuration Manager.
Not sure where to post.
Thanks for your assistance!
--Neil
All Replies
- This link might help:
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.connect&tid=90f7ae41-e094-424e-a534-c9abf43a41df&cat=en_US_e57ac608-2c73-44d1-ad3c-f098462acfc4&lang=en&cr=US&sloc=&p=1
I'm getting ready to move our MOA 2009 database from a desktop to a full-blown 2008 SQL Server. Had sucess in the past with MOA 2007 and SQL 2005. I've run across that post before and the built in prog at Start > Programs > Microsoft Office > Microsoft Accounting Tools 2009 > Data Tools will do the same thing as a failed company install then via the prog GUI.
I think it’s an odd connection issue, permissions problem, or some other odd combination.
Thanks for the link!
I am updating my original post to include this is an install on top of Vista 64 Ultimate.- Bump. Still looking for a forum or solution?
- Another BUMP from here. Also have not found a solution.
Windows Server 2008 64-bit. - Another bump from here, also have not found a solution nor any helpful information.I'm runningWindows Server 2008 64 bit with SQL Server Express 2008 64 bit. I've installed the SQL Server instance under The Domain Administrator account, using instance name MSSMLBIZ, set to port 5356. Firewall is properly configured and one can connect to the instance and create databases etc. both locally and remotely.All further Accounting 2009 actions in this message are run under the same Domain Admin account, which is also sysadmin on the instance.Using Data Tools, the instance name is set to MSSMLBIZ, then Accounting 2009 is launched. Attempting to create a new company makes it as far as the "Finish" button, but then gives the error "The database that you requested could not be opened, or access is denied. Click here if this error message was not useful."Using Data Tools to set the instance name to [HOSTNAME]\MSSMLBIZ ends with a different error message: "Microsoft Office Accounting cannot create the company because it cannot connect to the specified location." The same error message is produced when the instance name is set to BADINSTANCE.This suggests that not only does 1) the "instance name" field not let you specify HOST\INSTANCE, but 2) even with only the instance name of a proper local instance, and that instance is the recommended MSSMLBIZ, it still fails to work.
- I may be on to something here. I haven't fully tested yet, but so far it's looking good.The fact that all of us are running 64 bit OS got me to thinking about another problem I ran into once. It appears that Accounting 2009 does not play nice with SQL Server 2008 64 bit, but it does appear to work against 32 bit. Here's what I did:Install Accounting 2009 on the server (which has a 64 bit OS) with the default options, allowing it to install its own SQL Express 2005 instance. Launch Accounting 2009 and create your company to verify it works.Detach the databases from SQL 2005. Uninstall SQL 2005. Install the SQL 2008 32-bit WOW64 package. ("SQLEXPR_x86_ENU.exe", not "SQLEXPR32_x86_ENU.exe" or "SQLEXPR_x64_ENU.exe"). Set the instance name to MSSMLBIZ. Set SQL Browser to Automatic start instead of Manual. Set both yourself and your specified SQL server service account as administrators.When the install is done, enable TCP/IP connector, set the port number for IP All to 5356, configure your firewall and restart the server. Move the database files into your SQL 2008 data directory and attach them. (FYI they're called SBD/SBL instead of MDF/LDF.)Now when you start Accounting, it should be working against the new 2008 database instance--or at least it is for me!
Ran into related problem installing Office Accounting 2009 using an existing SQL Server 2008 Express installation in that Office Accounting could not find the 2008 instance. Here is the solution that worked for me:
1. Install Office Accounting 2009 selecting the client only installation;
2. Use the Office Accounting Data Tools menu option to point the installation to the SQL Server 2008 instance; and
3. Create an ODBC entry named MSSMLBIZ that points to the SQL Server 2008 instance.
You should then be able to use the sample company and or create a new company.
-sc- Has anyone found an answer to this. I'm not able to connect Office Accounting 2009 to a Windows Server 2008 SBS server with SQL Server 2008. The above installation of SQL Express is not a workable solution for me because the SQL Server is part of the SBS server natively and uninstalling it could have an impact on the rest of the SBS functionality. It's disappointing that the issue has not been resolved yet and an update is not published. According to some of the SQL trace articles on the web, its a simple version checking issue embedded in the code that screws up when the version becomes two digits instead of one. SQL Server 2008 is SQL Server 10.0. I hope it's not that because it's an amateur programming mistake if it is. In any case, this issue needs to be resolved.
Derek Licciardi
Derek Licciardi - Lots of fun! had days onto Microsoft tech support with sbs2008. 1. When you install account 2009 do a custom install and only install client 2. When 2009 opens uinstead of using instance MSSMLBIZ use the existing instance on your SQL server = mine was SBSmonitoring.
After that, all is well My installation of OA 2009 is an upgrade from OA 2008. I'm going to try and move the database files over to SQL Server 2005 Express, then perform the upgrade on SQL 2005 Express and finally, detatch and reattach to the SBS Server to see if it works. Perhaps the only problem lies in the fact that I used the SQL Server 2005 database files attached to a SQL Server 2008 DB. That works with all sorts of other databases but apparently, the database upgrade wizard can't handle it in OA 2009. I'll report back.
Derek Licciardi- I'm experiencing the same problem and I think I have a clue as to why, but I'm afraid it's going to require a fix from MS. I'm attempting to restore a company backup in MSOA Pro 2009 SP1. I have a local SQL 2008 SP1 default (MSSQLSERVER) instance running and have set the MSOA instance name to MSSQLSERVER. While running the restore from MSOA Data Tools, I also ran a SQL trace. I can see MSOA connect to SQL and execute the following statement:
SELECT CASE WHEN patindex('%.%.%.%',@@version) > 0 THEN cast(substring(@@version, patindex('%.%.%.%',@@version)-2,charindex('.',ltrim(substring(@@version, patindex('%.%.%.%',@@version)-2,8)))) as int) ELSE 0 END
If I take this statement and run it in SSMS directly, I get an error: Conversion failed when converting the nvarchar value '10.' to data type int.
If I run this same statement against a SQL 2005 instance it returns a single value of "9". SQL 2008 has an internal version number of 10.x.x.x while SQL 2005 has an internal version number of 9.x.x.x. The substring() logic in this statement doesn't expect the version number to start with 2 digits so the statement errors out. I think MSOA reports this as the generic error: "The database that you requested could not be opened, or access denied."
Was MSOA 2009 even tested against SQL 2008?- Proposed As Answer byBrentonUnger Saturday, October 17, 2009 2:28 AM
I'm experiencing the same problem and I think I have a clue as to why, but I'm afraid it's going to require a fix from MS. I'm attempting to restore a company backup in MSOA Pro 2009 SP1. I have a local SQL 2008 SP1 default (MSSQLSERVER) instance running and have set the MSOA instance name to MSSQLSERVER. While running the restore from MSOA Data Tools, I also ran a SQL trace. I can see MSOA connect to SQL and execute the following statement:
Great find! I ran into the EXACT issue this evening. Microsoft needs to fire those foreign contractors....oh wait...they already DID! Even SP1 doesn't fix this issue...you'd think that DB connectivity and version checking would be easy in the year 2009.
SELECT CASE WHEN patindex('%.%.%.%',@@version) > 0 THEN cast(substring(@@version, patindex('%.%.%.%',@@version)-2,charindex('.',ltrim(substring(@@version, patindex('%.%.%.%',@@version)-2,8)))) as int) ELSE 0 END
If I take this statement and run it in SSMS directly, I get an error: Conversion failed when converting the nvarchar value '10.' to data type int.
If I run this same statement against a SQL 2005 instance it returns a single value of "9". SQL 2008 has an internal version number of 10.x.x.x while SQL 2005 has an internal version number of 9.x.x.x. The substring() logic in this statement doesn't expect the version number to start with 2 digits so the statement errors out. I think MSOA reports this as the generic error: "The database that you requested could not be opened, or access denied."
Was MSOA 2009 even tested against SQL 2008?
Allow me to show these folks how it is done (version independent):
select
substring(cast(SERVERPROPERTY('productversion') as varchar), 0, CHARINDEX('.', cast(SERVERPROPERTY('productversion') as varchar), 0))
Do we know if anyone at Microsoft looking at this?- As a matter of interest, does SQL Server 2008 have a registry entry in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL?If not, try adding one (eg. MSSMLBIZ RegSz MSSQL.1 or as appropriate). Does that resolve the problem?
- Indeed ServerProperty('ProductVersion') is much better than parsing @@version. How did you know that those left MOA team - late 2008 - were foreign contractors? FTE lol - this buggy code was written in 2006 or even earlier than that, I just can't recalled why I decided to use @@version - just a doh! That said, this bug and many other sql bugs should have been fix (as pri 0) long ago.
xmsft 2007. As a matter of interest, does SQL Server 2008 have a registry entry in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL?
If not, try adding one (eg. MSSMLBIZ RegSz MSSQL.1 or as appropriate). Does that resolve the problem?
I've added the registry key to the location. My instance names folder had one key already "MSSQLSERVER" reg_sz "MSSQL10.SQLSERVER", and I added another one "MSSMLBIZ" reg_sz "MSSQL10.SQLSERVER" to no avail.
I've also tried to use the data tools, but anything I set in those tools seems to have no affect whatsoever. I cannot create companies, or open an existing one.
However, none of this changes the fact that when running a trace statement against my database, that I see the error thrown from the above code...this tells me that there is indeed connectivity, just bad code being executed.
What to do now?A good chance that Microsoft.Win32.Registry.LocalMachine failed to retrieve 32 bits registry under 64bit OS, so OA check sql version but ran into cast (10.) @@version bug. Did you tried running OA in WOW32?
Also you should create new company using a 32 bit desktop w/ sql express 2005 or 2008, then attached the db to your sqlserver 2008 std/ent, thus avoid the registry & @@version bugs.I have MOA 2009 running on 32bit SQL 2008 on the standalone (AKA: second server) that comes with SBS 2008 Premium.
This weekend I did an install of SBS x64 / SQL x64 2008 on a single box.
I am able to attach the backup made from the prior server to the SQL 08 database but I am unable to get MOA to connect to SQL 2008.
I've not tried the registry hacks...with a complete instruction set I could do so but I do not understand enough of the thread to make an attempt.
If anybody comes up with a solution, please post here...I will do the same.
Ken- How ironic...I finally sat down to attempt moving our MOAP 2009 data to our SQL 2008 network and wouldn't ya know it...that very morning I get the email stating that MS is getting out of the small business accounting game. SIGH...
Forging ahead...we're running SBS 2008 (64-bit) and our SQL 2008 server (also 64-bit) is installed on a companion server (we did not name the SQL 2008 instance "MSSMLBIZ" when we installed it). We've been running custom apps and databases on the network and everything has been running fine for months.
For kicks I wanted to see if the data would even reside on our SQL machine. I backed-up my MOAP data, detached and copied it over to our SQL Server (yes I put it in the right folder). First problem encountered: You can't attach a *.sbd and *.sbl file in SQL Server 2008?. I searched the web and the MS forums looking for info on this subject...nothing very helpful.
After reading every post above at least three times...I just don't think there is an easy answer
The route Daveidmx suggests looks like a decent idea...but we're not uninstalling our SQL Server to load the 32-bit WOW64 package. We have too many things that already work great...
Installing SQL Express on the same box as SQL Server is NOT a good idea (there are countless posts out there on the nightmares some folks have encountered).
I'm thinking of setting up a small server, joining it to the domain, loading SQL Express from the MOAP install onto the new server, loading the MOAP client on several office machines and pointing the MOAP client machines to the new SQL Express/server. My primary goal here is two-fold: I need multiple clients to access the accounting software simultaneously AND I need our custom apps to pull data from accounting through queries and views.
Anyone have any thoughts? The link posted above by adam rich is a waste of time...
- Proposed As Answer bybuildvoy Tuesday, November 03, 2009 2:22 AM
- Hi Mike,
I just installed and successfully ran OA 2009 sp2 on the following configuration:
dbserver = sql2k8x64ent running on a hyper-v guest vm win2k8x64ent, sqlinstance=dbserver (default instance, no mssmlbiz ____)
client = xp 32bit pro, run OA from here accessing a company (acme) on dbserver
my .sbc file look like this:
database=acme
server=dbserver\.,1433
Note - I use sqlmangement_studio to create a backup of acme on my 'client', then restored onto 'dbserver' - feel free to change file location and its extension (.sdb -> .mdf, .sbl -> .ldf).
Maybe somehow something inspire me, then I'll install win7x64 or (lame vistax64) on my hyper-v and try to run OA 2009sp2 from there - just to test OA app runs on 64bits OS.
Rusty. - Thanks a bunch Rusty, I'll give that a try tomorrow (Saturday) and post the results.
Hi Rusty,
Saturday is here! Which version of SQL Management Studio are you running and is it installed side-by-side on the same machine as MSOA 2009? (which runs on SQL Express 2005) or are you connecting from a different machine (which I'm trying to work through now).
I did some reading and sqlex2005 doesn't play well with sqlex2008 unless you remove some of the 2005 tools before install.- Hi again Rusty,
I went ahead and tried to do the backup and restore using the SQL Management Studio 2008 that's on my laptop. Connected to the client machine and the instance of sqlex2005 no problem...now the names and locations of the files are screwing me up a bit. Couple of questions (including the one from my last post):
- Was your SQL Management Studio installed on the same machine as MSOA 2009?
- At what point do I rename the database files (.sdb -> .mdf, .sbl -> .ldf)? Before I back them up or after the restore?
- I'm missing something in the order of events or maybe I've just had too much coffee this morning. Is it detach, rename, backup then restore?
Thanks for your posts...I almost gave up on this until I saw the last post you made.
Mike Hi Mike,
Yes - sqlmangementstudio installed on OA 2009 machine (keep it simple).
Rename when restore the backup onto the server - click on 'options' page, checked 'overwrite', change files 'restoreas' location & name
If you want to chat, shoot me an email (flexdog2008 at geezmaaiill d.o.t com) w/ ur Instant-Messenger handle - Sam/Rusty will ping you.
RustyRusty was correct! Follow his posts, do a little reading and you shouldn't have any issues.
Some notes for anyone reading this post:
I was running MSOA 2009 Professional on an XP machine and had to move the database to a SQL Server 2008 Standard instance running on a 64-bit 2008 Server joined to a 64-bit SBS 2008 server.
Use SQL Management Studio 2008 (SMS2008)
SMS2008 can be loaded onto the same machine as MSOA/SQLExpress2005 (the client machine) or on a separate machine.
- If you choose to load it on the same machine, make sure you read-up on installing on a machine that's already running the 2005 version first. I read this (http://stackoverflow.com/questions/175881/sql-server-express-2008-install-side-by-side-w-sql-2005-express-fails) and opted to run it from another machine (my laptop).
- If it's on a separate machine, you'll have to ensure that SQL Express 2005 (on the client) is configured to allow remote connections. This link will help: http://support.microsoft.com/default.aspx/kb/914277
Whatever route you choose, make sure you can connect to your source instance AND the destination instance through SMS2008. Then you're ready to move the database.
Getting the database to your SQL Server
I probably did this the long way but it worked out fine. I have a friend who's going to be doing this soon so the following directions are step-by-step with him in mind:
1) In SMS2008 (connected to the source instance) detach the target database.
2) Through Windows Explorer, rename the target files as Rusty points out above (.sdb -> .mdf, .sbl -> .ldf). I made a copy of the target database files and renamed them (just as a precaution).
3) In SMS 2008 (connected to the source instance) attach the target database.
4) In SMS 2008 (connected to the source instance) backup the target database.
5) Through Windows Explorer, copy the backup file from the client machine to the Backup folder on the SQL Server.
6) In SMS2008 (connected to the target instance) restore the backup.
Modify your sbc file for Accounting
Follow Rusty's post above dated Wednesday, November 04, 2009 5:04 AM. Follow it to the letter.
Open accounting and go! No registry modifications neccessary!!!
Thank you Rusty!- Proposed As Answer byMike Paquin Saturday, November 07, 2009 11:14 PM
