Store Operations generating odd database connection failure messages

(0) Share

Report

Posted on by Paul Masters

Bit of background........

We have RMS running on a windows 7 pc which acts as our Till and is also where the database resides. We also have 2 back office Windows XP PCs running RMS Operations Manager.

We had a hard drive failure on the Till pc about a month ago which required everything to be re-installed/restored from scratch.

(I think) this problem dates from then.

This may or may not be relevant but in restoring the Till I have ended up with RMS using the SQL server default instance rather than an instance named “RMS” as it was previously – this obviously required changing the database server in Store Operations Administrator on each back office pc but that has been done and (almost) everything seems to be working fine, except....

The problem:

Both of the back office pcs can access the database on the Till, run reports, make changes to the database etc..

However, despite being clearly connected to the database, one (but not the other) of the back office pcs throws the following error message EVERY time I try to make a change to a product (e.g. click on product properties, change a price, click ok).

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 sql interfaces, error:26 Error locating server/instance specified.)

The egg-timer shows for around 15secs and then this error appears. Even more strangely, when I click ok and inspect the properties of the item, the change HAS been made – again proving that the database is connected. [The other back office pc works as expected]

None of the 3 pcs in question have a firewall enabled

The second back office pc works perfectly as expected.

It seems to me that either the server (Till) is treating the 2 back office pcs differently, or there is some difference in configuration between the 2 pcs, but I can't find any differences.

Can anyone suggest possible causes?

*This post is locked for comments

All responses (5)

Answers (0)

Jeff @ Check Point ... 13,380 on at

Like (0)

Report

Re: Store Operations generating odd database connection failure messages

Did you install the version of SQL that came on the RMS disk? Win 7 doesn't like it too much. Below are the steps for upgrading to SQL2k8 R2.

If you have SQL2k8 installed, check the settings I mention below - start reading after the section;

... After the restart, Start | All Programs | Microsoft SQL Server 2008 and delete the folder. Its duplicated with Microsoft SQL Server 2008 R2.

and check on all machines.

You should really change your configuartion around though. Put the database on a office machine and let the Register be a slave to it. That way you can run in Offline mode if the database server were to fail.

SQL 2k5 to 2k8 R2 Express upgrade steps

Download your Express version from here; www.microsoft.com/express/Database/InstallOptions.aspx You really only need the Database Only version for RMS.

You can only upgrade a 32 bit version with a 32 bit version or 64 to 64. If you want to move from 32 bit to 64 bit, it must be a new install.

If installing on XP, Vista, Server 2003 or 2008, make sure you have the Windows Installer 4.5 installed (look in Add/Remove Programs), otherwise grab it here;support.microsoft.com/.../942288 You will need to restart the machine.

First screen of the install asks for a new or Upgrade. Select Upgrade from SQL Server 2000, 2005 or 2008.

The installer will go out and check for any issues that must be fixed before the upgrade, if no issues, accept the license and optionally select the Send info to MS box.

Next screen asks you select the SQL instance to upgrade, the one to select for RMS is the MSSQLSERVER one (that is the default name that RMS installs, if you've named it something else, select that one).

Next screen asks to send more installation info to MS.

Last screen completes or give you the errors that need fixing. If no errors, you're done. I suggest a restart after the install.

If standalone (single user), you're done, except we always setup for networked environments as you never know what the future brings..

After the restart, Start | All Programs | Microsoft SQL Server 2008 and delete the folder. Its duplicated with Microsoft SQL Server 2008 R2.

Start | All Programs | MS SQl Server R2 | Configuration Tools | SQL Services | right click on SQL Server and Stop it, do the same for Server Browser.

Double click SQL Server Network Configuration | double on Protocols | make sure that Shared Memory, Named Pipes and TCP/IP are all enabled.

Double click on SQL Native Client 10.0 Configuration on the left | double on Client Protocols on the right, right click on Named Pipes | Order | on the right side, click the down arrow once so that Named Pipes is on the top.

Click back on the SQL Server Services on the left, right click each SQL Server aand Browser and restart them.

Start | All Programs | MS Dynamics RMS | Client Network Utility | move Named Pipes and TCP/IP to the right with Named Pipes on top.. On the Alias tab | Add | make sure Named Pipes is selected and enter either the Server's name or IP address

Go into SO or HQ Admin | log into th edatabase | Query | New enter the following, but change the "databasename" for your install.

EXECUTE SP_CONFIGURE 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

EXECUTE SP_CONFIGURE 'Ad Hoc Distributed Queries', '1'

RECONFIGURE WITH OVERRIDE

EXECUTE SP_CONFIGURE 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

ALTER DATABASE "databasename" SET RECOVERY SIMPLE

EXEC sp_dboption 'databasename', 'autoclose', 'FALSE'

Press the F5 button or select the green sideways triangle on the top.

FYI: The "show advanced options", AD Hoc, Show advanced turns on the old OPENROWSET that 2005 had, "Alter database" sets it to a Simple backup mode so the log files don't grow, and the "autoclose" turns off the autoclose function of Express to stop it from going to sleep.
Paul Masters 90 on at

Like (0)

Report

Re: Store Operations generating odd database connection failure messages

I have disabled firewalls on all of the computers including the server
Mr. Pluton 275 on at

Like (0)

Report

Re: Store Operations generating odd database connection failure messages

Does your server have the Windows firewall up? If so is port 1433 opened?
Paul Masters 90 on at

Like (0)

Report

Re: Store Operations generating odd database connection failure messages

Hi,

thanks for the suggestions -

I have increased the timeout from 30 to 99 seconds and enabled tcp/ip and named pipes. Unfortunately the message still appears after around 15secs of clicking ok on a product change.

The RMS version is 2.0.0150
Community Member Microsoft Employee on at

Like (0)

Report

Re: Store Operations generating odd database connection failure messages

HI Paul,

Try increasing the database time out from Store administrator. Also under RMS client connectivity, make TCP/IP and Named pipe enabled. Which version are you in RMS.