Skip to main content

Is it possible to use a File DSN as ODBC Data Source for the MS SQL Connector

  • June 2, 2021
  • 4 replies
  • 2 views
A
  • Anonymous

Setting up a connection to an MS SQL Server instance using an ODBC Data Source defined as User DSN or System DSN works fine. But in an environment with dozens of server environments and hundreds if not thousands of clients, User DSNs and System DSNs are hard to manage and distribute.

File DSNs could be a solution to that problem, they are easy to manage and distribute, but ... I can't seem to get the Uniface MS SQL Connector to connect to an MS SQL Server using a File DSN. The Internet is full of examples of how to create a File DSN, but I couldn't find any example showing how to use a File DSN in e.g. Excel either.

Anybody having success with File DSN's for the Uniface MS SQL connection? If so: what does the MSS path definition in your .asn file look like?

4 replies

Peter Beugel
Forum|alt.badge.img+1

Setting up a connection to an MS SQL Server instance using an ODBC Data Source defined as User DSN or System DSN works fine. But in an environment with dozens of server environments and hundreds if not thousands of clients, User DSNs and System DSNs are hard to manage and distribute.

File DSNs could be a solution to that problem, they are easy to manage and distribute, but ... I can't seem to get the Uniface MS SQL Connector to connect to an MS SQL Server using a File DSN. The Internet is full of examples of how to create a File DSN, but I couldn't find any example showing how to use a File DSN in e.g. Excel either.

Anybody having success with File DSN's for the Uniface MS SQL connection? If so: what does the MSS path definition in your .asn file look like?

To use a file datasource, add the prefix "FILEDSN=" to the datasource name.
In that case, the name can be either an unqualified datasource name (in
which case it will be loaded from the default ODBC datasource
directory), or a fully qualified filename. If you use a fully qualified
filename, it can NOT contain a colon (:) because that is used as the
separator between the datasource and database names. Should you need to
specify a fully-qualified filename, use the UNC format to avoid the
colon. In all cases, the ".dsn" suffix is optional and can be omitted.

Examples:

; Remote file datasource
$def mss:FILEDSN=\\\\sharename\\folder\\mydsn:mydatabase|dbuser|?

; Local datasource in c:\\data - use UNC format
$def mss:FILEDSN=\\\\sharename\\c$\\data\\mydsn:mydatabse|dbuser|?

; This one does not work (two colons)
$def mss:FILEDSN=c:\\data\\mydsn:mydatabse|dbuser|?


Example content mydsn file
[ODBC]
DRIVER=ODBC Driver 17 for SQL Server
UID=worktodo
TrustServerCertificate=No
WSID=MYMACHINE
APP=Microsoft® Windows® Operating System
Trusted_Connection=No
SERVER=MYMACHINE\\SQLEXPRESS2019
Description=Fildedsn using ODBC 17



Hope this helps

A
  • Anonymous
  • June 3, 2021

Setting up a connection to an MS SQL Server instance using an ODBC Data Source defined as User DSN or System DSN works fine. But in an environment with dozens of server environments and hundreds if not thousands of clients, User DSNs and System DSNs are hard to manage and distribute.

File DSNs could be a solution to that problem, they are easy to manage and distribute, but ... I can't seem to get the Uniface MS SQL Connector to connect to an MS SQL Server using a File DSN. The Internet is full of examples of how to create a File DSN, but I couldn't find any example showing how to use a File DSN in e.g. Excel either.

Anybody having success with File DSN's for the Uniface MS SQL connection? If so: what does the MSS path definition in your .asn file look like?

Peter, thanks a zillion!

I wonder how I could have discovered this syntax, but it works fine for me. 
I recommend to add this to the MS SQL Server Connector documentation, as well as the generic ODBC connector and other ODBC based connectors.

Peter Beugel
Forum|alt.badge.img+1

Peter, thanks a zillion!

I wonder how I could have discovered this syntax, but it works fine for me. 
I recommend to add this to the MS SQL Server Connector documentation, as well as the generic ODBC connector and other ODBC based connectors.

Henk I will check why this is not documented.

Roger Wallin
Forum|alt.badge.img
  • Roger Wallin
  • Participating Frequently
  • June 29, 2021

Peter, thanks a zillion!

I wonder how I could have discovered this syntax, but it works fine for me. 
I recommend to add this to the MS SQL Server Connector documentation, as well as the generic ODBC connector and other ODBC based connectors.

Very nice, it would have been very nice to know this struggling for years with distributing the odbc-source to the clients (registery).

However, I suppose that the odbc client dll-files have to be available on the client pc?


Recent badge winners

Explore all badges
Terms & ConditionsAccessibility statement