Importing text into SQL Server from FileMaker via ODBC

Question

Our client is trying to export their data from their FileMaker 10 database into a MS SQL system via ODBC.

We have the DSNs set up, and can import date and number fields, but text fields fail with:

“requested conversion is not supported” Cannot get the current row value of column “[TQG6]...[Referral].ReferralAccountName” from OLE DB provider for MSDASQL for linked server “TQG6”

We've updated the ODBC drivers to SequeLink 6.0 Also tried with a temporary FileMaker 11 server and related driver.

It looks like there is a problem converting from FileMaker's rather generic 'Text' data type to SQL Server's more specific Char, Varchar or Text fields (and their unicode equivalents, nchar, etc.).

Answer 1

I don't quite understand how you've set up the connection. Technically there are the following options:

1. Connect to FileMaker via ODBC and import from FileMaker.
2. Add the MS SQL database to FileMaker as an external data source and then transfer data between tables from within FileMaker. Here you'll import into this external table from an existing internal table.
3. (For completeness) Have FileMaker to push records one by one using the Execute SQL script step, where FileMaker will connect to MS SQL via ODBC.

It seems that you're using option 1; could you try option 2 instead?

Answer 2

Is this a once off or a regular process you need to do? If it is a once off you could just try exporting to Access or even CSV format and importing into SQL Server.