MatchUp Software:SQL Troubleshooting Guide: Difference between revisions
No edit summary |
No edit summary |
||
| Line 1: | Line 1: | ||
[[MatchUp Software|← MatchUp Software]] | |||
{{CustomTOC}} | |||
==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 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 / Personator probably can not connect to the database \ table. | |||
Open up your SQL Server Configuration Manager (via Start menu >... SQL Server Config Tools...). | 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. | 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. | ||
[[File: | [[File: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. | 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...). | 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. | 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... | NOTE 64-bit users: Currently, only a 32 bit SQL Native Client will be recognized. | ||
[[File:SFT_MU_Alias-64bit.png]] | |||
It should be noted that the configured Alias Server name should match the SQL instance exactly as shown below. | |||
[[File: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… | |||
[[File: | [[File: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). | 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. | 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. | ||
| Line 44: | Line 55: | ||
''' | ===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. | |||
==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. | 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. | 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. | 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. | 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. | 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. | ||
[[MatchUp Software|Back to MatchUp Main Page]] | [[MatchUp Software|Back to MatchUp Main Page]] | ||
Revision as of 20:54, 21 July 2016
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 / Personator 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.
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.
It should be noted that the configured Alias Server name should match the SQL instance exactly as shown below.
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…
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?
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.
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.