DISCLAIMER.
This is a how-to article for troubleshooting common connectivity issues between the Hach WIMS Client and Microsoft SQL Server ONLY. Most of the steps outlined here do NOT apply to Oracle.
SYMPTOM:
In certain version of WIMS, when you start the client, it immediately crashes with "Run-time error '91': Object variable or With block not set":
CHECK:
If connecting to SQL Server 2005/2008, make sure the SQL Server Native Client is installed. To see if it's installed, check "Programs" from Control Panel:
The listing doesn't have to be EXACT. It could say "Native Client 2005", or "Native Client 2008" or "Native Client 2008 R2".
RESOLUTION:
If it's not present, you must install it. For SQL Server 2005, The file comes in two varieties: sqlncli.msi for 32-bit systems and sqlncli_x64.msi for 64-bit OS's. For SQL Server 2008/R2, both versions are named sqlncli.msi.
You always have to match the version of the native client against the version of the SQL Server you're connecting to.
- If installing the SQL Server Native Client 2005, either download it from Microsoft or install it off the WIMS DVD.
- Download - (if link doesn't work, look for "Microsoft SQL Server 2005 Feature Pack" on Google): http://www.microsoft.com/downloads/en/details.aspx?FamilyID=536FD7D5-013F-49BC-9FC7-77DEDE4BB075 - download the appropriate file
- Install from WIMS DVD - the file is located under the Sql2005Exp\x86 or SQL2005Exp\x86_64 for 32-bit and 64-bit respectively
- If installing the SQL Server Native Client 2008, you have to download it - we don't ship it on the WIMS DVD. Look for either the "Microsoft SQL Server 2008 Feature Pack" or the "Microsoft SQL Server 2008 R2 Feature Pack" and download sqncli.msi from there.
Keep in mind that Microsoft download links may change. The "Feature Packs" have dates associated with them - for our purposes, it shouldn't matter, but always try to download from the latest date.
SYMPTOM:
You get an error saying "Hach WIMS failed to connect to Microsoft SQL" with an error message "Hach WIMS failed to connect to Microsoft SQL" and an empty error detail:
CLIENT-SIDE CHECK:
- Make sure your connection string is correct - check the Hach_WIMS_Client.ini file and see if the "HACHWIMS_CONNECTION_DATASOURCE" setting is correct.
- If you think it is, perform a "UDL test" to see if the searver is reachable. The important bit to realize here is that we are NOT using ANY Hach software - this on a low-level, windows + driver world only. If we can't connect using the UDL test, we cannot connect with the WIMS client and it now becomes either a network issue or a SQL Server Configuraiton/Security issue and needs troubleshooting on the server. To perform a UDL test:
- Make sure Windows is not hiding extensions to know files. On XP-generation OS's, open up Explorer and go Tools -> Folder Options -> View -> uncheck "Hide extensions to known file types"
- On Vista and later, open up Explorer and go Organize -> Folder And Search Options -> View -> uncheck "Hide extensions to known file types"
- Create a file with an extension ".udl", double-click it to open it
- In the provider tab, make sure you select the correct driver! Note: if using MSOLEDBSQL Driver the provider in this tab should be Microsoft OLE DB for SQL Server. In this example, the 2005 Native Client is selected.
- Under the Connection tab, fill out the server host and SQL Server instance name just like in the WIMS Client INI file, fill in the OPSDBA credentials and do NOT save the password. Hit "Test Connection":
If successful, click OK. Then, open the UDL file in notepad (don't double-click it, that will open it in "UDL" mode!):
Make sure the "Provider=" string matches what is in the WIMS Client ini file.
SERVER-SIDE CHECK:
- Make sure the SQL Server you're connecting to is running (Start -> Run -> "services.msc")
In this example, the SQL Server instance we're connecting to (OPSSQL) is stopped and needs to be started.
- Make sure the database owner is set to OPSDBA
SYMPTOM:
You get an error saying "Hach WIMS failed to connect to Microsoft SQL" with an error message "Hach WIMS failed to connect to Microsoft SQL" and an error detail "TCP Provider: No connection could be makde because the target machine actively refused it"
SERVER-SIDE CHECK:
- Make sure the database instance is started and running (see above)
- Make sure there is no firewall on the server blocking our connection attempts (either disable the firewall completely or add an exception for our SQL Server services)
SYMPTOM:
You get an error saying "Hach WIMS failed to connect to Microsoft SQL" with an error message "Hach WIMS failed to connect to Microsoft SQL" and an error detail "SQL Server Network Interfaces: Error locating Server/Instance Specified [xFFFFFFFF]":
SERVER-SIDE CHECK:
- Make sure the database instance is started and running (see above)
- Make sure there is no firewall on the server blocking our connection attempts (either disable the firewall completely or add an exception for our SQL Server services)
- Make sure the instance name is spelled correctly in the WIMS Client ini file
- Start the SQL Server Browser service, if stopped (this is also needed in the case of multiple SQL Server instances on the same server):
SYMPTOM:
You get an error saying "Hach WIMS failed to connect to Microsoft SQL" with an error message "Hach WIMS failed to connect to Microsoft SQL" and an error detail "SQL Server Network Interfaces: Server doesn't support requested protocol [xFFFFFFFF]":
SERVER-SIDE CHECK:
- Start the SQL Server Configuration Manager utility on the server
- Check if TCP/IP is disabled:
RESOLUTION:
Enable TCP/IP and restart the service
SYMPTOM:
You get an error saying "Hach WIMS failed to connect to Microsoft SQL" with an error message "Hach WIMS failed to connect to Microsoft SQL" and an error detail "Login failed for user 'OPSDBA'. The user is not associated with a trusted SQL Server connection":
SERVER-SIDE CHECK:
From SQL Server Management Studio, right-click and select properties on the server and check if the SQL Server is set to "SQL Server and Windows Authentication mode":
RESOLUTION:
Enabled mixed-mode authentication if not set
SYMPTOM:
After attempting to login, the Client displays the following error: "Cannot open database '...' requested by the login. The login failed":
SERVER-SIDE CHECK:
In SQL Server Management studio, right-click on the facility you can't login to and make sure the database file is owned by OPSDBA:
RESOLUTION:
Set the owner to OPSDBA, if not set
REF:
Here is an in depth discussion of connection errors: msdn blog It has a discussion of what each error number means.