Connecting to MSSQL 2005

[Hans Jakob Prydz]

Newbie question:

I've set up in my Datasource (ODBC) a link to our companys MSSQL 2005 (Native and normal SQL ODBC link) from my computer to the SQL server. Testet it OK (both with Datasource admin tool and in Excel). I've also turned on ODBC/JDBC in Filemaker but when I try to connect nothing happens - i do not get the tables in the dialog (I've read through the documentation, online help etc).

Does anyone know what to do ?

(I've heard some rumours about than FM does not use port 1433 (sql standard port for communication with ODBC - but another port ? Does anyone know ? - I have access to the SQL server as well.)

Hans Jakob - frustrated FM user...

(I've enclosed two screendumps)

[TimD]

Hans --

I think you're on the right track. Here's what you need to do to access the SQL Server data from within FM9 using ESS...

1. Setup the ODBC connection on the client machine. (I believe you've got this working already.)

2. In the FileMaker database that you want to connect from, go to File / Manage / External Data Sources.

3. Create a new Data Source. For the name, give it anything you want. Type should be ODBC, and you should select the DSN that you setup in step 1. For authentication, you can either choose to be prompted to login each time that you access the SQL data or enter it one time. In the Filter Tables area, set the Catalog Name to the name of the SQL database that you are accessing. Note that this is the name of the database -- not the name of the database server itself. Leave the schema and table name fields empty. Make sure that the "Filter By Type" has tables and views checked. Click OK to save this info.

4. You should now be able to access the SQL tables. Go to File / Manage / Database, and switch to the Relationships graph. Add a new table occurence. From the Data Source drop down menu, select the datasource that you setup in Step 3. You should see a list of tables and views that are available in the SQL database. Simply drag out the tables and views that you want to access, and you should be all set.

I hope this helps. Let us know if you need additional help.

-- Tim

[Hans Jakob Prydz]

Superb! This was nice!

The table and views + name of the database did it!

A very good tip.

It's a shame that such a small tings are so important...

Hans Jakob

Edited January 15, 2008 by Guest