Problems Linking to external SQL database

[freewill]

Hi

We are having a problem with the links that we have created to allow us to access data in an SQL database outside JDE.

The table that we are linking to was created outside JDE. We then defined the specs for the table in JDE and generated the table and finally deleted the generated table and created a view in SQL mapping the fields.

When this was first created and tested we were able to see the data in UTB but once the users got hold of it and started testing we lost our ability to see the data.

I checked the field definitions and thought I had spotted the problem - one of the fields was a char in JDE but a 10 char string in SQL. However, changing the table has failed to bring about any improvement.

To add to the confusion, we have a number of tales created in this way and are still able to see the data in some of them. The common factor seems to be that the tables in which we cannot see data all have a field of type [uniqueidentifier]. Since this has no direct equivalent in JDE we have mapped it to a 40 character string.

I have found a few discussions on here about how to view / access data outside JDE but have not managed to find anything about similar problems that people have experienced.

So, to cut a long story (looking at the above) still quite long.... Has anyone out there had similar problems and, if so, did you manage to sort them out.

We are running Xe and SQL server 2000

Thanks in advance


Ste

 

[Larry_Jones]

Ste,

If you are trying to INSERT or UPDATE data in these tables from JDE then the views should NOT include the [uniqueidentifier] field.

If you are only reading data from them ... again, why include the [uniqueidentifer] field. Its basically a GUID for the the database that has meaningless values for the end-user - when converted to text looks sorta like: 6F9619FF-8B86-D011-B42D-00C04FC964FF

If you must include these because your tables are linked using these fields (shudder) then in your view definition replace the [uniqueidentifer] field name with the below (replacing "YourFieldName")

CONVERT(varchar(255), YourFieldName)

Then you'll need to replace the corresponding fields in your JDE virtual tables with a DD field that is VARCHAR and 255 in length. Look at alias TESTDESC for an example.

Now for the caveat. I haven't tried this. We're an Oracle shop. But 10 min of googling says this should work.

Good Luck,

 

[freewill]

Hi Larry

I tried as you suggested and changed my view to CONVERT the uniqueidentifier field to a varchar. Sadly this did not work, and neither did CASTing it as a varchar

Checking in jdedebug and jde.log, the sql statements are being created (and can be run if cut and pasted out of jdedebug into SQL query analyzer) but jde.log is giving me this error "[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. - SQLSTATE: 37000".

The only reference I could find to this message through google was a link back to a thread on here discussing problems that someone was having, which turned out to be due to incorrect OCM mappings but I have checked the mappings here and they are all as they should be.

Any thoughts from anyone would be most welcome.



Ste