{"id":264,"date":"2010-11-15T23:22:42","date_gmt":"2010-11-15T13:22:42","guid":{"rendered":"http:\/\/jcrawfor74.wordpress.com\/?p=264"},"modified":"2013-01-24T01:31:01","modified_gmt":"2013-01-23T14:31:01","slug":"how-to-create-a-linked-server-connection-from-sql-server-to-filemaker-pro-6-0","status":"publish","type":"post","link":"https:\/\/ntsblog.homedev.com.au\/index.php\/2010\/11\/15\/how-to-create-a-linked-server-connection-from-sql-server-to-filemaker-pro-6-0\/","title":{"rendered":"How to Create a linked server connection from SQL Server to Filemaker Pro 6.0"},"content":{"rendered":"<div id=\"ntsbl-2492010986\" class=\"ntsbl-before-content ntsbl-entity-placement\"><script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js?client=ca-pub-6288941070289539\" crossorigin=\"anonymous\"><\/script><ins class=\"adsbygoogle\" style=\"display:inline-block;width:728px;height:90px;\" \ndata-ad-client=\"ca-pub-6288941070289539\" \ndata-ad-slot=\"9356781486\"><\/ins> \n<script> \n(adsbygoogle = window.adsbygoogle || []).push({}); \n<\/script>\n<\/div><p>Ok,<\/p>\n<p>This one may be a bit obscure but I had to do this for data conversion from a legacy system the other day and the FileMaker documentation was pretty light on, but I eventually got it to work.<\/p>\n<p>The problem is that you want to extract data from a FileMaker Pro 6.0 database by creating a linked server connection from SQL Server.<\/p>\n<p>Here are the steps with one Caveat. I was only able to make this work on a 32-bit machine. I had a 64-bit machine, and I could run the 32-bit odbc datasource tool and set-up and test the connection but I could not get my 64-bit version of Sql Management studio to find the 32-bit System DSN I had created, so I jumped over to a 32-bit virtual machine with SQL 2008 on it and everything worked more easily.<\/p>\n<p><strong>1. Install FileMaker Pro<\/strong><\/p>\n<p>Ensure that you have FileMaker pro installed on the box that you want to create the linked server connection on. This installs the 32-bit filemaker pro driver to allow you to create an ODBC data source.<\/p>\n<p><strong>2. Create on ODBC Datasource System DSN <\/strong><\/p>\n<ul>\n<li> Go start :: administrative tools :: Data Source (ODBC)<\/li>\n<li>Switch to the System DSN tab<\/li>\n<li>Click Add<\/li>\n<li>Choose &#8220;FileMaker Pro&#8221;<\/li>\n<li>Click Finish<\/li>\n<li>On the General Tab give the connection a generic name, (can be anything), i used FMPro<\/li>\n<li>Tick &#8220;Use Remote Connection&#8221; and add the loop back IP address, 127.0.0.1<\/li>\n<li>Click OK.<\/li>\n<\/ul>\n<p><strong>3. Create a linked server connection<\/strong><\/p>\n<ul>\n<li>Launch SQL Server Management Studio<\/li>\n<li>Expand to &#8220;Server Objects&#8221; :: &#8220;Linked Servers&#8221;<\/li>\n<li>Right Click &#8220;New Linked Server&#8221;<\/li>\n<li>In the provider choose &#8220;Microsoft OLE DB Provider for ODBC Drivers&#8221;<\/li>\n<li>In the following fields, enter &#8220;FMPRO&#8221;\n<ul>\n<li>Linked Server<\/li>\n<li>Product Name<\/li>\n<li>Data Source<\/li>\n<\/ul>\n<\/li>\n<li>Click OK.<\/li>\n<\/ul>\n<p><strong>4. Load FileMaker Database<\/strong><\/p>\n<p>The linked server connection only works via the local client, so for the linked server to work you have to have your Filemaker Pro database loaded and remotely accessible.<\/p>\n<ul>\n<li>Launch FileMaker Pro<\/li>\n<li>Open your database, this can be a FM database on a remote server over TCP\/IP, connect up as you normally would to access the file.<\/li>\n<li>Choose File :: Sharing<\/li>\n<li>Set to Multiple and enable Local and remote connections.<\/li>\n<\/ul>\n<p>Note: During my linked server sessions, I have sometimes lost the connection or get connection errors. I just close filemaker, and repeat the above steps and it all comes good again.<\/p>\n<p><strong>5. How to query FileMaker<\/strong><\/p>\n<p>What is the name of the FileMaker Table? select * from what?<\/p>\n<p>Turns out the name of the filemaker database is the table name, so assume your db is called &#8220;fmdatabase&#8221; you would write the following (Note: I generally use &#8220;OpenQuery&#8221; statements to run my linked server queries):<\/p>\n<p>[sql]select * from openQuery(FMPRO, &#8216;Select * from fmdatabase&#8217;)[\/sql]<\/p>\n<p>but what if your filemaker database has a space in the name? like &#8220;fm database&#8221;<\/p>\n<p>well the trick here is documented away deep in the FileMaker help file use `<\/p>\n<p>this is the punctuation on the tilde (~) key.<\/p>\n<p>so your query becomes<br \/>\n[sql]<br \/>\nselect * from openQuery(FMPRO, &#8216;Select * from `fm database`&#8217;)<br \/>\n[\/sql]<\/p>\n<p>Now that should all be clear.<\/p>\n<p>Hope it helped.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Ok, This one may be a bit obscure but I had to do this for data conversion from a legacy system the other day and [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[7],"tags":[],"class_list":["post-264","post","type-post","status-publish","format-standard","hentry","category-general-apps"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/264","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/comments?post=264"}],"version-history":[{"count":0,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/posts\/264\/revisions"}],"wp:attachment":[{"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/media?parent=264"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/categories?post=264"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ntsblog.homedev.com.au\/index.php\/wp-json\/wp\/v2\/tags?post=264"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}