Thursday 25 October 2012

Restore Office Accounting 2009 on Windows 8 and SQL communication error

A Windows 7 PC that had been happily running Office Accounting 2009 refused to boot and couldn't be repaired, although all the files could be accessed ok by slaving the hard drive off another PC.

Just to make matters interesting, the replacement PC is Windows 8. I installed SQL Express 2008 (with loads of warnings about compatibility - just agree and make sure you load SQL 2008 SP3).

When I tried to restore an accounting backup it failed with SQL communication errors. Typically;

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Also tried creating a new accounting database and got the same SQL error about not being able to communicate.

I ran through the standard procedures for enabling remote access to SQL and ensuring the SQL protocols were setup correctly. Thanks to Beth Massi for a really helpful article about installing SQL Management Studio. But still no luck.

I eventually tracked it down to the Office Accounting Data Tools (remember to Run as Admin), look in Settings and there was a blank entry for the SQL instance name. I updated to SQLEXPRESS to match the instance name installed earlier (you can see it in the Control Panel > Programs list or any of the SQL tools) and communication was established.

I was then able to restore a backup ok.

Also able to copy the SBD and SBL files over from the old drive and use the Data Tools > Advanced Tools > Attach Data File to reconnect the latest Office Accounting database so we didn't lose any transactions since the previous backup. That's covered in Microsoft's move accounting database article. Worth noting that the Data Tools now list the SQL instance as MSSMLBIZ

Trust 1&1 Internet for your domain name registration, from only £1.99/year!. Check now!