Concepts – Linking SiteFX to SQL Server

Concepts – Linking SiteFX to SQL Server - Overview

Method 1 – Direct to SQL Server Database

In this method, SiteFX links directly to the SQL Server database.

§         create a new text file (e.g., use NotePad)

§         add a DSN-less connection string, for example:

ODBC;DRIVER={SQL Server};DATABASE=demodb001;SERVER=COMP1\SQLEXPRESS;Trusted_Connection=yes;

or

ODBC;DRIVER={SQL Server};DATABASE=demodb001;SERVER=Server1;UID=utest;PWD=password

§         For more information and examples on connection strings, consult your database documentation or there are volumes of information on the internet. A good place to start is www.connectionstrings.com

§         save the text file with an .odbc extension

§         Start SiteFX

§         Choose ‘Open Database’

§         Select the .odbc file you just created.

Method 2 – Through an Access Database that has been linked to SQL Server

In this method, SiteFX links to an Access MDB which has in turn been linked to SQL Server. SiteFX picks up the DNS information from the Access database, so performance should be equivalent to method 1 above, however, this has not been thoroughly tested.

One benefit of this method is that the user will still have the ability to open an Access database and make changes directly to the SQL Server back-end database. Also, it may also be easier for the user to test various connection strings using this method until they have a valid connection string worked out.

Linking an Access database to SQL Server - Method 2A

§         This can be done in Access by going to File > Get External Data > Link Tables and choosing a DSN.

§         Note – this method may create table name prefixes that would need to be removed prior to linking with SiteFX

Linking an Access database to SQL Server - Method 2B

§         use EarthFX Linker tools – using these tools, you would first create an .odbc file (see Method 1), and then point the linker tool to this file.

§         When using this method, the linker tools will link to any tables prefixed with D_,R_ or S_

§         Also, the linker tools remove any SQL Server table name prefixes automatically

§         the linker tool is on the EarthFX FTP site under the SiteFX / tools folder