Help with Index Assistants

Failure to connect to the lookup database

The SQL Index Assistant can connect to a number of different databases not just SQL.  It is possible to connect to MS SQL, Oracle databases using the database client’s software directly, other type of data sources can be connect to using an ODBC connection.  Creating an ODBC data source is not covered here

The ‘Connection string’ field in the Index Assistant Configuration screen is populated with details of the database name, table name and security details.  A file called a Universal Data Link can be created with this information.

To create a Universal Data Link, create an empty file in a folder and rename it giving it an extension of .UDL.  Open this file and select the Provider tab, ensure that the provider is correct for the database you are connecting to.

Note: If the database you are connecting to is an Oracle dataset, ensure that the 64 bit version of the client is installed on the server and the 32 bit version of the client is installed on the clients.

Click on the Connection tab, enter the server name in 1, security type into 2 and select the database from the server in 3.  If no list of database appears in 3 and the Test Connection button results in an error, then verify that the user credentials are set up correctly on the SQL server.

Click OK and open the file again using Notepad or another text editor.  Copy and paste the line starting with Provider= into the Connection the Connection String box in the Index Assistant, omitting the Provider=<provider>; part of the string.

Once the connection string is entered, clicking on ‘Test Connection’ will verify that the connection.  However, this will not verify security permissions.

Failure to log into the database

Any Index Assistant can be run either form the Server or from the Client.  If the connection string has the setting for Windows Authentication, when the SQL Query is executed the Windows credentials of the executing client is used to determine if the user has access to the database.  This is set by changing the Execution setting in the Index Assistant Configuration screen.

The login properties for the users that will be using the Index Assistant should be Public and db_datareader as a minimum unless the SQL query demands more.

Failure to query the database

If creating a query, write the query in SQL Management Studio and then copy it into the SQL Query are in the Index Assistant.  Any Select query using a view or table will work.  Using joins and other SQL statement can work but creating a view would be a better option.

A typical statement would look like this

/****** Script for SelectTopNRows command from SSMS  ******/

SELECT TOP 1000 [PersonCode]

      ,[AccountName]

      ,[AccountCode]

      ,[BrokerCode]

      ,[AccountType]

  FROM [DataStore_Extended].[dbo].[vwClientFile]

When copied into the SQL query box in Index Assistant, the database name in the WHERE clause and the TOP 1000 can be removed, references to connect the field in the database to the fields in DSX will need to be added along with an additional WHERE clause.  After the additions the query will look like this

SELECT [PersonCode] as <Client Code>

          ,[AccountName] as <Client Name>

          ,[BrokerCode] as <FM Code>

          ,[AccountType] as <Document Type>

  FROM [dbo].[vwClientFile]

WHERE [PersonCode] = <Client Code>

The text is green is the field mapping that maps the content of the database fields to the fields in DSX.

If there is a problem with an existing SQL Query, the reverse can be done.  Take the query and run it in SQL Server Management Studio.  The only amendment would be to replace the < and > characters with [ and ]