topic Re: Connecting to Databases using Data Flux ODBC Drivers in SAS Data Management

Connecting to Databases using Data Flux ODBC Drivers

Rama_V — Wed, 10 Jun 2020 23:54:55 GMT

I am looking for advice how to connect to databases from Data Flux management Studio/Server using Data Flux ODBC Drivers. I could connect using drivers provided by applications like SQL Server or Teradata driver. I am trying to figure out what steps need to be taken to use Data Flux ODBC drivers.

I have followed below documentation link, as per step 5 seems like I need to update on server side which about to connect using the driver.
https://support.sas.com/documentation/onlinedoc/dfdmstudio/2.6/dmpdmsug/Content/dfU_T_DataConnODBC.html

For test, I am using SQLSERVER Express. but I had connectivity issues.

Could any one please guide me what steps to be taken at least for SQL Server connectivity?

Thanks,

Rama

Re: Connecting to Databases using Data Flux ODBC Drivers

SASKiwi — Thu, 11 Jun 2020 01:57:25 GMT

@Rama_V - DataFlux Data Management Studio is a standalone product so you configure ODBC connections for DDMS on the same computer that it runs on as in the documentation link you provided.

Re: Connecting to Databases using Data Flux ODBC Drivers

Rama_V — Fri, 12 Jun 2020 01:12:58 GMT

Thanks @SASKiwi
I agree, I have done that and it works if I am running jobs only on Data Flux Management Studio with generic . Since I have write access on Desktop machine I can install ODBC drivers.

But I am promoting those jobs on to Data Flux Management server to execute. I do not have authority to install the ODBC drivers on to the server or even the admins are not helpful around installing drivers. Where as creating connections with already installed Data Flux ODBC drivers easy to achieve. Problem I am facing using data flux ODBC drivers it is not working.

As per the documentation link on point 5, if I am using DataFlux ODBC drivers I have to make some changes on RDBMS Server side protocols. Then I will face same problems negotiating with RDBMS admins in update the protocols.

Re: Connecting to Databases using Data Flux ODBC Drivers

SASKiwi — Fri, 12 Jun 2020 04:29:25 GMT

@Rama_V - You are correct that to run the same DataFlux jobs on the DQ server you will need to set up the same ODBC data sources. If your server administrators are not helpful then you really have a management problem. I suggest you escalate this to your manager for resolution.

Re: Connecting to Databases using Data Flux ODBC Drivers

Rama_V — Sun, 14 Jun 2020 23:23:12 GMT

Thanks, around the management suggestion I will definitely follow that.

From Technical perspective, what configurations need to be done to use Data flux ODBC drivers for connectivity?

Re: Connecting to Databases using Data Flux ODBC Drivers

SASKiwi — Mon, 15 Jun 2020 19:59:46 GMT

You configure the ODBC sources in the Windows ODBC administrator, exactly as you have already described. Since you are running the DataFlux Studio job on the server the setup is the same.

Re: Connecting to Databases using Data Flux ODBC Drivers

VincentRejany — Mon, 15 Jun 2020 22:29:23 GMT

It is one best practice to name the ODBC connections the same way between clients and servers. If not possible you will have to use macros ie "%%ODBCSQL%%" and to specify the corresponding ODBC string in your local macro file.

Re: Connecting to Databases using Data Flux ODBC Drivers

Rama_V — Mon, 15 Jun 2020 23:56:54 GMT

I will rephrase the question. As highlighted below, there are drivers provided by Microsoft (Under Company Column) works for SQL Server and there are drivers under Data flux(under Company column) doesn't work for SQL Server. Why? what to be done to make it work?

Re: Connecting to Databases using Data Flux ODBC Drivers

VincentRejany — Tue, 16 Jun 2020 11:34:25 GMT

You can find some details here
https://media.datadirect.com/download/docs/odbc/allodbc/index.html#page/odbc/the-sql-server-legacy-wire-protocol-driver.html