*Only applies to versions of SmartConnect 20.18 and older

One of the most common methods of integrating data using SmartConnect are Microsoft Excel Files or flat files such as comma or tab delimited. To make connecting to these files quicker and easier, SmartConnect contains a list of Excel versions that can be selected or we can just pick Text File from the source list and pick our file.

Even though we can select from a pre-defined list, it does not mean the ODBC Driver that is used for making the connection is installed on a machine that needs to run the integration.

The same goes for using the ODBC Connection type and selecting an option during the Connection String setup. Selecting Excel 2013 from the ODBC Connections window will require the same driver as selecting Excel 2013 Data Source drop down list. The only difference is how you are able to query the data which is a topic for another time.

The most common error received when connecting to a data source that uses an ODBC Driver is “Data Source Name not found and no default driver specified”, which means the specific ODBC driver required is not installed on the current machine. The first step on fixing this issue is to determine what connection is being made and then determine which ODBC driver to install or valid on the current machine or machine that is producing the error.

NOTE: SmartConnect is a 32-Bit application so it requires the 32-Bit version of any ODBC driver to be able to make the proper connection to the ODBC Data Source.

 

Determine ODBC Driver Versions

I am sure you are wondering how do I know what drivers are installed on the machine. Each workstation where SmartConnect is running will have its own set of ODBC Drivers installed so while a map pointing to Excel on one machine works, it is possible that another machine does not have the same set of drivers loaded and the connection cannot be made. To verify the drivers that are installed, we can use Microsoft PowerShell to get the list of drivers and their versions.

NOTE: Make sure you have the correct rights to run PowerShell scripts before attempting to run these commands or get the network administrator to execute them. If the currently logged in user does not have the necessary rights, you will need to contact the IT administrator to get the proper access or run the following commands.

 

Retrieve ODBC Drivers List

If you want to get a list of all ODBC Drivers installed on the machine run this PowerShell command.

Get-OdbcDriver

Since SmartConnect is a 32-bit application, it will only work with 32-bit ODBC drivers, so we can add a parameter to look for just 32-bit drivers.

Get-OdbcDriver -Platform “32-bit”

Instead of keeping PowerShell open, we can send the list to a file.

Get-OdbcDriver -Platform “32-bit” | format-table | out-file ‘C:/Users/Public/Documents/output.txt’

You can use this file on a machine that is running SmartConnect successfully to compare it with a list from a non-working client machine.

Which ODBC Drivers are used

There are several functions within SmartConnect that use ODBC drivers within SmartConnect. This shows the function in SmartConnect, the driver and version used and what type of data for the connection.

Errors and Resolutions

Now that we know how to validate the ODBC Drivers we have installed and which should be used, we’ll take a look at some of the common error messages.

 

Excel Data Source

Connecting to Excel Files uses the 32-bit Microsoft Excel Drivers or Microsoft Access Database Drivers which can be downloaded here. Make sure to download the 32-bit version and use the /passive option per this article, if you are on a 64-bit machine. 

 

When the Access Drivers are not installed or the 64-bit version only is installed we will get an error regarding the data source not found. Verify the 32-Bit Excel ODBC driver is installed or the Microsoft Access ODBC Driver may be installed as well.

When clicking on Details, the message says “ERROR [IM002][Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”

 

An invalid odbc query syntax will generate a generic error regarding parameters or missing command. Make sure the column names are referenced correctly within the query since SmartConnect removes the spaces when displaying the data. When using the column in a query, the actual name should be used from the Excel column header. Use the following article on Microsoft SQL Syntax for writing ODBC SQL Queries in SmartConnect. 

ERROR [07002] [Microsoft][ODBC Excel Driver] Too few parameters. Expected 1.

This is an error in the Query being used. Please consult the Microsoft Excel ODBC Query page LINK to find the correct syntax

 

If the user does not have access to the file, the following error will appear when trying to Validate the connection or Preview the data

[ODBC Excel Driver] General error Unable to open registry key Temporary (volatile) Ace DSN for process”

 

Text File Data Source

The Microsoft Text File ODBC Driver is installed by Windows installation so it should automatically be there on any client machine running SmartConnect.

If the file does not exist the folder specified, the error will point to the missing file name. Move the file to the correct location.

ERROR [42S02] [Microsoft] [ODBC Text Driver] The Microsoft Jet Database engine could not find the object ‘myfile.csv’. Make sure the object exists and that you spell its name and the path name correctly.

If the user trying to execute the integration does not have access to the file or the file path, there will be a list of errors pointing to the path being invalid. Make sure the user has correct rights within the windows folders.

ERROR [HY024] [Microsoft][ODBC Text Driver] ‘(unknown)’ is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

ERROR [01000] [Microsoft][ODBC Text Driver]General Warning Unable to open registry key ‘Temporary (volatile) Jet DSN for process 0x14b0 Thread 0x1a3c DBC 0x87cd10c Text’.

When using the ODBC Connection data source, the path will be displayed in the connection string. In my example here, it is the DefaultDir. The file name is displayed in the Query window.

SQL Server Data Source

Connecting to a Microsoft SQL Server Database will attempt to use the version associated with that version of SQL Server. Usually you can select a newer SQL Native Driver version, like SQL 2012 to connect to older SQL versions.

Typical error message when the driver being used by the connection is not installed will simply be “This driver has not been installed.” The Microsoft SQL Server Native Clients can be downloaded from Microsoft. 

Additional Data Sources

Other non-Microsoft ODBC Data Sources such as MySQL or Oracle, the specific driver for that application will need to be installed and a DSN setup for the connection to be successful. Review this article for an example of connecting to MySQL. 

Typical messages will state the driver has not been installed. Please consult the specific data providers’ documentation on setting up a connection to the source data.

 

Conclusion

While SmartConnect uses ODBC for connecting to many different data sources the drivers required for those connections are going to be specific to the environment and may even differ per user machine and server. Go through the steps of validating the file location, user access to the file and then the driver versions.