Archive

Archive for the ‘General Apps’ Category

How to Create a linked server connection from SQL Server to Filemaker Pro 6.0

15 November, 2010 1 comment

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 the FileMaker documentation was pretty light on, but I eventually got it to work.

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.

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.

1. Install FileMaker Pro

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.

2. Create on ODBC Datasource System DSN

  • Go start :: administrative tools :: Data Source (ODBC)
  • Switch to the System DSN tab
  • Click Add
  • Choose “FileMaker Pro”
  • Click Finish
  • On the General Tab give the connection a generic name, (can be anything), i used FMPro
  • Tick “Use Remote Connection” and add the loop back IP address, 127.0.0.1
  • Click OK.

3. Create a linked server connection

  • Launch SQL Server Management Studio
  • Expand to “Server Objects” :: “Linked Servers”
  • Right Click “New Linked Server”
  • In the provider choose “Microsoft OLE DB Provider for ODBC Drivers”
  • In the following fields, enter “FMPRO”
    • Linked Server
    • Product Name
    • Data Source
  • Click OK.

4. Load FileMaker Database

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.

  • Launch FileMaker Pro
  • 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.
  • Choose File :: Sharing
  • Set to Multiple and enable Local and remote connections.

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.

5. How to query FileMaker

What is the name of the FileMaker Table? select * from what?

Turns out the name of the filemaker database is the table name, so assume your db is called “fmdatabase” you would write the following (Note: I generally use “OpenQuery” statements to run my linked server queries):

select * from openQuery(FMPRO, 'Select * from fmdatabase')

but what if your filemaker database has a space in the name? like “fm database”

well the trick here is documented away deep in the FileMaker help file use `

this is the punctuation on the tilde (~) key.

so your query becomes

select * from openQuery(FMPRO, 'Select * from `fm database`')

Now that should all be clear.

Hope it helped.


Advertisements
Categories: General Apps

How to install U3 Apps to your U3 Drive

22 April, 2010 Leave a comment

I have a san-disk U3 Drive and I wanted to update my version of keePass that I run on it.
http://keepass.info/

I downloaded a U3 version of keepass and do you think I could install it?

Basically you will download an archive file like a .zip file. If you extract the zip you will find folders like, data and manifest but no installer.

To add an application you do the following

  • Open the U3 Application
  • Click Add Program
  • Install from my computer
    • What you will find is it is looking for a .u3p file? Where is that?

      THE TRICK
      You need to change the file extension from .zip to .u3p
      Once you do this you can select you file from the U3 application, and just follow the wizard.

      Cheers

Categories: General Apps