Do you have permissions to see the columns? Check with the SQL DBA.
I have attached a screen shot of the table and the column via Management Studio using Windows Authentication.
I am the Administrator for the Windows 2012 R2 OS and the SQL database
The ODBC link is via a System DSN using SQL Server V6.03. and use Windows Authentication to access the table. I have tried username and password but it’s the same.
The Filemaker relationship table shows only a subset of the columns (dbo.JournalLog)
Any guidance would be helpful, thanks.
Technical Sales Manager
Mobile: +44 (0)7813 770 685 | Office: +44 (0)1628 530 380 | Fax: +44 (0)1628 532 145
how is the SQL column defined? VARCHAR, TEXT, ???
Its defined as an xml type
The XML type is not one of the data types supported by FileMaker "External SQL Sources" (ESS), so FM will not include any fields with that data type in the table occurrence (TO) that appears on the Relationship graph.
However, if you have the ability to define views in your SQL database, you can make a view that converts the XML field to a data type acceptable to ESS, like this:
create view JournalLog_v as select SecondaryObjectIdentity, cast(XmlMessage As Varchar) As XMLMessage from JournalLog
Then you would just use the JournalLog_v view from within FM.
Actual Technologies - ODBC for Mac OS X
Great information, I will try this, thanks.
What Jon said.
Just a quick question; when opening the view in FM, its ask for a unique field, but the columns in the table are not unique (they are unique to their own table) and I can’t seem to be able to SELECT the GUID (primary key) of the JournalLog, any ideas?
You have to have a field(s) that uniquely identifies each record in the view. If you don't have a single field that uniquely identifies each record, then maybe you have multiple fields that together uniquely identify the record (i.e. a compound key)?
When you say you're not able to SELECT the GUID, can you tell me what you're seeing - i.e. a syntax error when you try to create a view that includes that field, or the GUID field does not appear in the FM table occurrence?
In my test here, I'm able to see a GUID field in a SQL Server tabled added to the FM Relationships graph (in SQL Server, the type is "uniqueidentifier", in FM the type is "text").
I have managed to get the JounalLog GUID in the SELECT statement, thanks, I don’t know what I did wrong the first time!
However the xml column is too short for the full string. Its (-1) in the real JounalLog so I’m assuming it unlimited length?
I couldn’t get the cast(XmlMessage As Varchar(-1)) As XMLMessage message to work so is there another way of allowing a longer length Varchar for this view?
Instead of -1, use the keyword "max": cast(XmlMessage As Varchar(max)) As XMLMessage
Absolutely marvellous, works a treat, thanks for your patience and assistance, much appreciated.
Have a lovely Christmas!!!
Hi, I realise this thread is a bit old, but could I just ask if this method will allow me to write to the xml, or just view it?