You can use the Database Lookup functoid by taking the following steps:
1. Click the Toolbox, and then click the Database Functoids tab. On the map surface, in between the source and destination schemas, drag and drop a Database Lookup functoid.
2. Connect the left side of the Database Lookup functoid to the inbound document node that will specify the value used in the search.
3. Configure the input parameters of the Database Lookup functoid requires four parameters to be specified either through mapping the inbound source data to the functoid or through setting constants in the functoid.
a. For the first input parameter, verify that the inbound node, connected in step 2, is the first value in the list of properties. This is a value to be used in the search criteria. It’s basically the same as the value used in a SQL WHERE clause.
b. Set the second parameter, which is the connection string for the database. The connection string must be a full connection string with a provider, machine name, database, and either account/password or a flag indicating the use of Trusted Security mode in SQL. The connection string must include a data provider attribute. A lack of data provider attribute will generate a connection error when the map tries to connect to the database.
c. Set the third parameter, which is the name of the table used in search.
d. Set the fourth parameter, which is the name of the column in the table to be used in search.
4. Again, click the Toolbox, and click the Database Functoids tab. On the map surface, after the Database Lookup functoid, drag and drop the Error Return functoid.
5. Connect the right side of the Database Lookup functoid to the left side of the Error Return functoid. Connect the right side of the Error Return functoid to the outbound schema node that is a placeholder for error messages.
6. Again, click the Toolbox, and click the Database Functoids tab. On the map surface, above the Error Return functoid, drag and drop the Value Extractor functoid for each extracted value from the return recordset. For example, if you are returning five values in the recordset, you would need five Value Extractor functoids.
7. For each Value Extractor functoid, connect the left side of the functoid to the right side of the Database Lookup functoid.
8. Configure the properties of each Value Extractor functoid to retrieve the appropriate value by specifying the column name of the extracted value. For example, if the value returned resides in a column named FirstName, you would create a constant in the Value Extractor functoid named FirstName.
9. For each Value Extractor functoid, connect the right side of the functoid to the appropriate target schema outbound node.
If you receive the below error, Please follow the below steps.
"[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied
1. Create UDL file using the below steps.
UDL File CreationThe way a UDL file is initially created depends on your Windows installation. This can be checked from the Folder shortcut menu.
If no Microsoft Data Link is listed, the following steps should fix this.
1. Click the Toolbox, and then click the Database Functoids tab. On the map surface, in between the source and destination schemas, drag and drop a Database Lookup functoid.
2. Connect the left side of the Database Lookup functoid to the inbound document node that will specify the value used in the search.
3. Configure the input parameters of the Database Lookup functoid requires four parameters to be specified either through mapping the inbound source data to the functoid or through setting constants in the functoid.
a. For the first input parameter, verify that the inbound node, connected in step 2, is the first value in the list of properties. This is a value to be used in the search criteria. It’s basically the same as the value used in a SQL WHERE clause.
b. Set the second parameter, which is the connection string for the database. The connection string must be a full connection string with a provider, machine name, database, and either account/password or a flag indicating the use of Trusted Security mode in SQL. The connection string must include a data provider attribute. A lack of data provider attribute will generate a connection error when the map tries to connect to the database.
c. Set the third parameter, which is the name of the table used in search.
d. Set the fourth parameter, which is the name of the column in the table to be used in search.
4. Again, click the Toolbox, and click the Database Functoids tab. On the map surface, after the Database Lookup functoid, drag and drop the Error Return functoid.
5. Connect the right side of the Database Lookup functoid to the left side of the Error Return functoid. Connect the right side of the Error Return functoid to the outbound schema node that is a placeholder for error messages.
6. Again, click the Toolbox, and click the Database Functoids tab. On the map surface, above the Error Return functoid, drag and drop the Value Extractor functoid for each extracted value from the return recordset. For example, if you are returning five values in the recordset, you would need five Value Extractor functoids.
7. For each Value Extractor functoid, connect the left side of the functoid to the right side of the Database Lookup functoid.
8. Configure the properties of each Value Extractor functoid to retrieve the appropriate value by specifying the column name of the extracted value. For example, if the value returned resides in a column named FirstName, you would create a constant in the Value Extractor functoid named FirstName.
9. For each Value Extractor functoid, connect the right side of the functoid to the appropriate target schema outbound node.
If you receive the below error, Please follow the below steps.
"[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied
1. Create UDL file using the below steps.
UDL File CreationThe way a UDL file is initially created depends on your Windows installation. This can be checked from the Folder shortcut menu.
- Right-click on the Desktop or in the folder where you want to create the file.
- Select New from the shortcut menu.
- Select Microsoft Data Link if it is listed.
- Windows 2000 will most likely not have Microsoft Data Link listed. If this is the case, please refer to the next section.
If no Microsoft Data Link is listed, the following steps should fix this.
- Right-click on the desktop, or in the folder where you want to create the file.
- Select New, then Text Document.
- Give the Text Document any name with a .udl extension ("Show file extensions" must be enabled in folder options).
- A window will pop up warning that "If you change a filename extension, the file may become unusable. Are you sure you want to change it?" Select Yes.
- You have now successfully created a UDL file.
2. double-click it. You will see The Data Link Properties dialog appears.
3. On the Provider tab, select the OLE DB provider for your platform from the OLE DB Providers list.
4. Click Next.The Connections tab appears.
5. Enter or select from the list the name of the data source.
6. Enter appropriate values in the User name and Password fields that will be used only to test the connection, not at runtime.
7. In the Select initial catalog to use field, enter the database to be used.
8. Click Test Connection. If test successful, Click OK to save the connection string to the Universal Data Link (.udl) file.
9. Open the file in notepad and use the connection string in 3.b
The above connection string should resolve the database connection issues.
