MatchUp Software:SQL Troubleshooting Guide

From Melissa Data Wiki
Revision as of 21:33, 21 July 2016 by Admin (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

← MatchUp Software


Try First

To Connect to Another Table or Database

Can you connect to another table or database?

You can try the Northwind SQL demo. If you can connect to another table, it may be permission issues.

If permission issues do not seem to be the problem, see below.


Connection

Connection Issues

Connection issues often show themselves if you can’t see a dropdown list of servers, databases and or tables. Are you typing any of them in? If you do not get any dropdown choices, MatchUp / Genderwise probably can not connect to the database \ table.

Open up your SQL Server Configuration Manager (via Start menu >... SQL Server Config Tools...). Check the settings for the Client and Server Configurations. Make sure the client protocols match the server's protocol (protocol names and order) and port number.

SFT MU client protocols.png


Try creating an Alias

We have seen this problem resolved by creating an ALIAS to the server in the Configuration Manager. MatchUp is sometimes able to connect to the alias when it can not connect to the direct instance of the server. NOTE: Try an ALIAS with a single name, letters only.

Open up your SQL Server Configuration Manager (via Start menu >... SQL Server Config Tools...).

Again, check the settings under the Client Configuration for the Alias you have created. Make sure they match the server's protocol (protocol names and order) and port number. Also try creating another Alias - where one has the naming convention SERVER and the other has the convention SERVER\MSSQLSERVER. reboot. Basically, setting this up can be trial and error, as one may work on certain configured machines while the other works on different configurations.

NOTE 64-bit users: Currently, only a 32 bit SQL Native Client will be recognized.

SFT MU Alias-64bit.png

It should be noted that the configured Alias Server name should match the SQL instance exactly as shown below.

SFT MU SQLTrouble01.png

Of the three Alias’ below – you will not be able to connect to the second and third, ie. you will not see them on your server dropdown list in MatchUp…

SFT MU SQLTrouble02.png


Reading the Structure

If the connection is not the problem, it may actually be a problem reading the structure.

Check the datatypes of some of the fields in your SQL tables. MatchUp does have trouble reading and writing certain field types (some users have changed decimal to integer).

Finally, if you are processing a view (as opposed to table) why not create a view that is simpler in structure? - less fields means less processing time, and less areas for potential errors.

There may be problems with the view because while MatchUp can process some views, ones created by a join of tables can not. A view created from various tables can be ambiguous as to what records or fields would be updated - as in a view which may have a many to one relationship or Data in a view may have been tabulated or derived from multiple tables. Even without creating an output table, MatchUp still has to know which individual record it is reading because it uses each record to build the analyzer and reports.

When you go to add the SQL table do you automatically get an error, or a message saying the client is not installed, or do you actually see prompts for Trusted Connection / Server / database/ table?

SFT MU SQLConnectClient.png


Search for NtwDdLib.dll

Do a search for NtwDbLib.dll. MatchUp needs this dll to speak with SQL. Microsoft stopped shipping this dll with SQl 2000. You can hunt and get it easily on the internet, or we can get one to the customer. Even if the file is present, make sure that Windows can locate it. That is, it is in your Windows folder, the System folder, the System32 folder, or any folder listed in your Path= statement.

The easiest location to place this dll is directly in your MatchUp (or Genderwise) folder.


Table Limitations

On a whim I tried connecting to a different table than the one I needed, and it worked. There are some tables I can access, and some I can’t. Are there any limitations as far as the number of columns in a table? The particular table I want to use (view actually) has 247 columns.

MatchUp does not have any limitations to the number of columns, although writing them out can cause problems when opening up in a program which cannot read that many.

There may be problems with the view because while MatchUp can process some views, ones created by a join of tables cannot. A view created from various tables can be ambiguous as to what records or fields would be updated - as in a view which may have a many to one relationship or Data in a view may have been tabulated or derived from multiple tables.

Check Field Data Types

Also, check the data types of some of the fields in your SQL tables. MatchUp does have trouble reading and writing certain field types (like decimal). See the help file section Reference | Overriding MatchUp’s Default Field Types for tips how to overcome this.

Simplify the Structure

Finally, if you are processing a view (as opposed to table) why not create a view that is simpler in structure? - less fields means less processing time, and less areas for potential errors.