8.1 Windows/LAN Remote - SData portal not working after upgrade from 7.5.4 to 8.1

[mcessna]

I'm guessing it could be an issue with SECTABLEDEFS being out of sync with the host?

Thanks

Mike

[RJLedger]

NOPE.. no way... see my last post about missing SQL views. - here's where the "bug" is.

[mcessna]

Ok, I see you updated the post. Yes, that would be the issue. I'm not sure why they're not being created with the remote is cut.

Thanks

Mike

[pzeimet]

RJ, was this remote upgraded or a fresh cut on 8.1?

Not all the following views are necessary (all views ending in _ are dynamically created by the provider and do not need to exist.  The ones that concern me are the ORM views.  These existed prior to 7.5.4.

ACCOUNTSYNCVIEW  Used only for X3 sync

      IMPORTHISTORY_    This is created dynamically by the provider

      IMPORTTEMPLATE_  This is created dynamically by the provider

      NETFORMS_PLUGIN_  This is created dynamically by the provider

      ORMCOLUMN

      ORMKEYCOLUMN

      ORMTABLE

      PACKAGEKITVIEW    Not currently used

      SALESORDERSYNCVIEW   Not currently used

      SALESQUOTATIONSYNCVIEW   Not currently used

      SYNCRESULT_  This is created dynamically by the provider

[RJLedger · Accepted solution]

Hi Paul,

 NO.. It is a remote db that has been working 100% fine for several years.. right up thru it's 7.5.4 upgrade - I Talked w/Tech Support (again) this afternoon and told them I believe I found out where all of this went wrong.

 I started digging into the "bundles" that are applied and ran accross something that I raised a flag about before.. how SQL views were being "bundled".... It started back in the 7.0.x to 7.2 timeframe.. When these were created then it was impossible for the view to synch.

The DB in question  (that's failing) goes back to that time-frame. and I found the offending/defective view creation sitting right there in the 7.0.x to 7.2 upgrade bundle.

I just applied a direct fix to the remote db:

/****** Object: View [sysdba].[ORMCOLUMN] Script Date: 08/21/2014 16:59:02 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

createview [sysdba].[ORMCOLUMN] as

select TABLE_CATALOG, TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.ORDINAL_POSITION - 1 ORDINAL_POSITION, c.IS_NULLABLE, c.DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DOMAIN_NAME, DISPLAYNAME

from  information_schema.columns c

   left outer join sectabledefs std

      on(c.TABLE_NAME = std.TABLENAME AND c.COLUMN_NAME = std.FIELDNAME)

.. and it fixed the problem immediately..

As I said above.. this was traced back to a Saleslogix upgrade bundle (7.0.x to 7.2) where the view was created but in a manner that did not allow it to synch. So it was a "time-bomb" wiating to go off.. and it did as soon as I tried to hook up Outlook Sync.

Therere's definitely the same problem w/ all the remote offices this customer has (just looked) - and anyone else who has been upgrading remote & remote offices since 7.0 and not re-cutting.

Now that I've come up w/a fix.. no need to re-cut ;-)

HOWEVER, I do have a big  concern that there's no (visible) mechanism that will keeep these views up to date via sync. Maybe they need to be "auto-created views" so that when things change in resynctabledefs and sectabledefa.. and they do..

[pzeimet]

RJ, starting in 8.1 there is a new bundle action that will sync to remotes.  "Create Database Object Definition".  This will allow you to create new views, update existing views for both Oracle and SQL.  The SQL versions will apply to the remotes via sync.

[RJLedger]

Hi Paul,

 Yep.. just noticed this - thanks!

I can create the bundle I need to deal w/teh problem for my customers - BUT I hope you guye will create an "official" bundle to deal w/all the SQL views that are missing one remotes when we upgrade.

I'll post my bundle back up here for anyone upgrading remotes and/or remote offices to 8.1

FYI - as you are aware - recutting a remote office is a major nightmare since we have zero control over the cut - no subscription and - the worst thing of all no Attachment control liek we have w/(normal) remotes!

(Suggestion - sneak in the ability to cut a remote office w/out synching (physical) attachments!)

[jeltzroth]

We ran into this issue also, and confirm that remote databases not recently re-cut may be missing the ORM... views.  We created and applied this bundle to push out the views.  This solved the issue.

John

ORM views.zip