Hi Board!

 

We've just installed Sage 200; from Sage 50.

So, now we have an SQL platform database to run our company on, moving up!

 

We use FM and FMS to add function and output. Sage 200 will take over some of this; but we have Shipping and Inspection systems we've built that need to read data from Sage.

 

The DSN has been setup and is working. The usr and pwd work. I can add a table to my FM database schema, go and select the data source and place/add a table. But there is no data inside any of the tables; and there should be.

 

Anybody got any tips for me?

 

Thank you!

21 minutes ago, Harry said:

Hi Board!

I can add a table to my FM database schema, go and select the data source and place/add a table. But there is no data inside any of the tables; and there should be.

 

Not sure I follow the sequence you are describing especially about first adding a table then going to the data source:

- if you add a table to FM, it will always start out empty - maybe that's the one you are looking at?

- you add an external data source and point it to the DSN, then you will see the SQL tables and views.  If you pick one it will add a table occurrence to your graph, and if you then go to a layout based on that Table Occurrence you should see the records from that SQL table or view.

Yes, it's the second part of the second part that isn't working for me.

 

I can add a TO to my Graph, go to that layout based on that TO, but there are no records.

 

I am opening lots of tables from this Sage 200 DSN that's been setup but they're all empty.

 

I'm also just asking our SQL developer (who's looking at another part of another system, integrating it into S200) if he can verify table contents etc, but i'm looking at it and it should be full of 000's of records.

So I'm doing this via FMP on the actual FMS box. It is a dedicated virt machine.

 

How do I add this DSN source in FMS 'natively' ? Is there a way? I don't understand how to do it....  ODBC is enabled, but 0 connections....

Thanks, Wim.

 

H.

 

 

 

Now i've just had this error:

 

********

The temporary file "FMS_1FE267BDEE100C87BEF18BE1FA263140.fch" could not be created ont he system disk. Use a different name, make more room on the disk, or unlock it.

********

 

When trying to add a TO from that DSN into my Graph. Acknowledge the error clears it and proceeds with no issue that I can see.

To use ESS (External SQL Source) databases in FileMaker, they need to be on the list of supported databases. Check the knowledge base article here: http://help.filemaker.com/app/answers/detail/a_id/6420

If not on the list, you may still be able to work with them via ODBC by importing or writing the SQL statements directly to the driver, but having ESS is nice when you can get it. 

I think Sage uses a MS SQL backend, so you will want to use the right driver for the supported version of FM and SQL, then add a system DSN and you should then be able to add it to FM as an external data source.

Mike

Yes, that is what we have done. Although, when we view those external SQL tables inside FileMaker, there is no data.

 

 

Running -

 

Microsoft ODBC Driver for SQL Server Version 12.00.4219

 

and I've run  through the SQL Server ODBC Data Source Test and it says it's completed successfully.

 

Microsoft ODBC Driver for SQL Server Version 12.00.4219

Data Source Name: SAGE200
Data Source Description: Connection to Sage 200 data on FM server
Server: FM
Use Integrated Security: No
Database: Billingham
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes
Application Intent: READONLY

Edited July 18, 20169 yr by Harry

The tables are listed as 'dbo.StockItemUnit' for example. They all have the dbo. prefix in their name.

 

My SQL developer is seeing a table marked 'STOCK' but I am not seeing it at all on the list in FM via External Data Sources or through Specify Table in the Graph.

 

2 hours ago, Harry said:

 

How do I add this DSN source in FMS 'natively' ? Is there a way? I don't understand how to do it....  ODBC is enabled, but 0 connections....

 

I think you are mixing a few things up.  ODBC enabled in FMS is for incoming connections, not for what you want to do (outgoing connection to a SQL source).

To set up ESS like you want you need a system DSN set up in the Windows ODBC Data Sources control panel, using the right driver.  I think you are using the wrong driver.  Check the supported drivers here:

http://help.filemaker.com/app/answers/detail/a_id/6420/~/which-odbc-data-sources%2Fdrivers-are-supported-with-external-sql-data-sources%3F

 

 

OK, that looks like a table our Sage developers created for migration.... thank you!