Jump to content

  •  

Photo

INSERT Record From MS SQL Server


  • Please log in to reply
9 replies to this topic

#1 bfonnemann  member

bfonnemann
  • Members
  • 7 posts
  • FM Application:13 Advance
  • Platform:Cross Platform
  • Skill Level:Intermediate
  • Time Online: 9h 43m 17s

Posted 21 July 2014 - 09:34 AM

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.


  • 0

#2 Wim Decorte  Carpal Tunnel

Wim Decorte
  • Moderators
  • 3,601 posts
  • LocationToronto
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 11, 12, 13
  • Membership:TechNet
  • Time Online: 35d 3h 15m 23s

Posted 21 July 2014 - 10:33 AM

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.


  • 0

#3 bfonnemann  member

bfonnemann
  • Members
  • 7 posts
  • FM Application:13 Advance
  • Platform:Cross Platform
  • Skill Level:Intermediate
  • Time Online: 9h 43m 17s

Posted 21 July 2014 - 10:47 AM

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


  • 0

#4 Wim Decorte  Carpal Tunnel

Wim Decorte
  • Moderators
  • 3,601 posts
  • LocationToronto
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 11, 12, 13
  • Membership:TechNet
  • Time Online: 35d 3h 15m 23s

Posted 21 July 2014 - 11:58 AM

 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?


  • 0

#5 bfonnemann  member

bfonnemann
  • Members
  • 7 posts
  • FM Application:13 Advance
  • Platform:Cross Platform
  • Skill Level:Intermediate
  • Time Online: 9h 43m 17s

Posted 21 July 2014 - 12:26 PM

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.


  • 0

#6 bfonnemann  member

bfonnemann
  • Members
  • 7 posts
  • FM Application:13 Advance
  • Platform:Cross Platform
  • Skill Level:Intermediate
  • Time Online: 9h 43m 17s

Posted 22 July 2014 - 12:03 PM

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.


  • 0

#7 Wim Decorte  Carpal Tunnel

Wim Decorte
  • Moderators
  • 3,601 posts
  • LocationToronto
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 11, 12, 13
  • Membership:TechNet
  • Time Online: 35d 3h 15m 23s

Posted 22 July 2014 - 12:31 PM

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


  • 0

#8 bfonnemann  member

bfonnemann
  • Members
  • 7 posts
  • FM Application:13 Advance
  • Platform:Cross Platform
  • Skill Level:Intermediate
  • Time Online: 9h 43m 17s

Posted 22 July 2014 - 12:47 PM

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...


  • 0

#9 bfonnemann  member

bfonnemann
  • Members
  • 7 posts
  • FM Application:13 Advance
  • Platform:Cross Platform
  • Skill Level:Intermediate
  • Time Online: 9h 43m 17s

Posted 23 July 2014 - 09:48 AM

Wim, Powershell solved my problem. Thanks!


  • 0

#10 Wim Decorte  Carpal Tunnel

Wim Decorte
  • Moderators
  • 3,601 posts
  • LocationToronto
  • FM Application:13 Advance
  • FMGo:iPhone / iPod Touch, iPad
  • Platform:Cross Platform
  • Skill Level:Expert
  • Certification:7, 8, 9, 10, 11, 12, 13
  • Membership:TechNet
  • Time Online: 35d 3h 15m 23s

Posted 23 July 2014 - 12:11 PM

You're welcome.  Thanks for posting back.


  • 0




FMForum Advertisers