INSERT Record From MS SQL Server

[bfonnemann]

Greetings all,

 

I am attempting to insert a record into a Filemaker 13 Solution hosted by FMSA 13 from the context of a MS SQL 2012 Server. Thus far I have been unsuccessful using openquery and also attempting to do a standard insert using a fully qualified db name. I have the linked server setup using the Microsoft OLE DB Provider for ODBC Drivers. The linked server will return SELECT queries successfully. Has anybody had any luck inserting from SQL? Any help would be greatly appreciated.

[Wim Decorte]

Not sure I follow how you have it set up.  In order to talk to FMSA you need to have the FileMaker ODBC driver installed and used in a DSN.

[bfonnemann]

Sorry for the confusion. I have set a System DSN up using FM as the name. However when I add the linked server to SQL, I must choose a provider. The only provider that I have successfully added was Microsoft OLE DB Provider for ODBC drivers. FM ODBC is not listed. Perhaps I am choosing the wrong provider? 

 

FYI, SELECT works fine using the OLE DB Provider

[Wim Decorte]

 when I add the linked server to SQL

 

That's the part I don't follow.  What is the linked server?

Why not just query FM through the DSN you set up that uses the FM driver?

[bfonnemann]

Thanks for the reply. Forgive my ignorance but I am unsure of how to query FM using the DSN w/o a linked server. I've done this before using JDBC but not from MS SQL.

[bfonnemann]

Wim, Have you ever successfully connected MS SQL to FileMaker with Filemaker being the data source? Specifically I'm most interested in inserting records from the context of MS SQL to FM.

[Wim Decorte]

I have, but I need a better definition of "connect MS SQL to FM".  I've done this from powershell and from a .NET application.  In both cases I had two connections: one to FM and one to MS SQL

[bfonnemann]

Specifically, I am attempting to execute an INSERT from SQL Management Studio 2012 using a linked server. I have a MS SQL database that will execute stored procedures to populate records within FM. You have indicated that a linked server likely isn't the way to go and based on my experience thus far, I can confirm that. Powershell seems like a promising alternative should this not be possible...

[bfonnemann]

Wim, Powershell solved my problem. Thanks!

[Wim Decorte]

You're welcome.  Thanks for posting back.