Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
New Member
Posts: 15
Registered: ‎03-05-2014

SLX 8.1: RemoteDB fails on "create index"?

[ Edited ]

Hello,

I am currently upgrading from 7.5.2 to 8.1 SNC1+2 patched; running on Oracle 10g.

 

So far everything works except creating remote user dbs.

SQL 2k8R2 native client was installed by SLX; 

manually I installed SQL 2k5 Native Client and 2k5 backwards compatibility from SLX DVD (found that in some forum, without that create remote DB didnt even start)

 

[05.06.2014 16:21:55]Error in step: stpExecSQL_CreateViews Microsoft SQL Native Client Incorrect syntax near '.'.

 

When I create the same users db on 7.5.2 (it is 1:1 copy for 8.1) it works; 

 

No idea what in detail the problem is - when trying to create DTS package and save it nothing happens (at least I dont see any new file on the whole machine plus admin says "creation completed" very - much too - fast...

 

Any idea about the DTS problem?

 

 

Back to the index problem ...

 

...only thing I can see is: file RemoteDB_DTS.sql in 7.5.2. only includes 2 system views and 2 custom views; 

in 8.1 it includes lots of views, but most of them are incomplete / messed up:

 

 

CREATE VIEW ORMKEYCOLUMN AS
SELECT TABLENAME TABLE_NAME, KEYFIELD COLUMN_NAME, 1 ORDINAL_POSITION
FROM resynctabledefs r UNION
SELECT r.TABLENAME, SECONDARYFIELD, 2
FROM resynctabledefs r
WHERE SECONDARYFIELD IS NOT NULL AND len(SECONDARYFIELD) > 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE VIEW sysdba.ACCOUNTSYNCVIEW AS SELECT
A.GLOBALSYNCID,
A.APPID,
A.TICK,
A.ACCOUNTID as ENTITYID,
A.MODIFYDATE,
0 AS ACTIONTYPE,
AOC.
GO
CREATE VIEW sysdba.ACTIVITYRESOURCEVIEW AS SELECT
rl.RESOURCEID + '-EVERYONE' as ACTIVITYRESOURCEVIEWID,
rl.RESOURCEID as RESOURCEID,
'EVERYON
GO
CREATE VIEW sysdba.AKA_ML_MGS AS SELECT
A1.ACCOUNTID,
A1.ACCOUNT,
A1.EXTERNALACCOUNTNO,
A1.STATUS,
A2.PREFIX,
A2.LASTNAME,
A2.F
GO
CREATE VIEW sysdba.AOM_AUSSENDUNG AS select b.accountid,a.baadnb Contractno, trim(a.baoaxp) MaklerID, a.bagigq AOM_Typ,a.BADZTT,
--(subst
GO

 

as you can see the views are truncated (red marked)

 

Anyone got an idea?

 

Thanks!

Marty

Bronze Elite Contributor
Posts: 514
Registered: ‎03-24-2009

Re: SLX 8.1: RemoteDB fails on "create index"?

Just found this on the SLX KB. Any good?

 

Symptom: 

“Error in step: StpExecSQL_CreateViews Microsoft SQL Native Client Incorrect syntax near…” error when creating a remote database from an Oracle host

 

Cause or Steps to Reproduce: 

Ensure that the host database does not have any Oracle linked databases. These are treated by SalesLogix as database views and will cause remote database creation to fail.

 

Solution or Workaround or Status: 

Remove any Oracle linked databases before cutting remote databases, then re-add them after creation

Regards, Adam Travers
empath-e Services Limited
New Member
Posts: 15
Registered: ‎03-05-2014

Re: SLX 8.1: RemoteDB fails on "create index"?

Hello,

Thanks a lot for that hint - I looked into the KB as well but seemed to have missed that...

 

Still I dont know what to do with that information?

Does that mean that all views which get data via a database link from other Oracle DBs are the problem? Or the database links itself or the problems? We have tons of such views and links in SLX sysdba schema Smiley Sad

 

I removed all those views for testing remote DB creation - still it does not work.

 

Plus I am really wondering why so many create views in the RemoteDB_DTS.sql are truncated - is this file relevant for the db creation or is it more like a log? 

 

Regards

Marty

Bronze Elite Contributor
Posts: 514
Registered: ‎03-24-2009

Re: SLX 8.1: RemoteDB fails on "create index"?

I would suggest you call your BP (assuming you are not one) or Swiftpage if you are. It is obviously a known issue to have made the KB

Regards, Adam Travers
empath-e Services Limited
Employee
Posts: 79
Registered: ‎06-25-2009

Re: SLX 8.1: RemoteDB fails on "create index"?

The issue looks to be with your Oracle Native connection.  You must specify a chunksize = 5000 or greater.  This should be documented in the help.

Here is a section of the help that describes it.

7. (Oracle Only) On the All tab:

a. Double-click Extended Properties.

b. In the Property Value box, type chunksize=5000.

You must set the Property Value if you plan to create custom views that will be included when you create a

remote database. If the value is not set, the SQL View is truncated on the remote database.

Paul Zeimet
Development Manager
Infor CRM
New Member
Posts: 15
Registered: ‎03-05-2014

Re: SLX 8.1: RemoteDB fails on "create index"?

Hello Paul,

Thanks a lot for that - I am getting closer Smiley Wink

 

Apparently I forgot that setting. Now I get a different error, still within createViews:


Step: stpExecSQL_CreateViews returned error: Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. ( (80040e14): )

 

I already turned chunksize to 50.000 (I am no DB expert at all - is that ok?) - so I can imagine it is still same problem or something connected to codepage / locale settings maybe? (we have some German umlauts in views).

 

What still makes me wonder: why are there so many views created in SLX 8.1? According to RemoteDB_DTS.sql more than 100 views are created (ok we have many... - but they are not enabled in DB Manager (but marked as "sync", option is greyed out so I can not change it); in 7.2 only 4 views are created with same DB.

 

Was there a change from V7.2 to 8.1 regarding views selection?

 

Thanks!

Marty

New Member
Posts: 15
Registered: ‎03-05-2014

Re: SLX 8.1: RemoteDB fails on "create index"?

update: I have removed all custom views, still getting error "Incorrect syntax near the keyword 'with'."

 

Only one view is having word "with" in RemoteDB_DTS, which is AKA_ML_MGS, a system view of SLX.

Views in RemoteDB_DTS.sql looks good, nothing truncated.

 

Any ideas?

Employee
Posts: 79
Registered: ‎06-25-2009

Re: SLX 8.1: RemoteDB fails on "create index"?

Can you please include the failing view from the RemoteDB_DTS?

Paul Zeimet
Development Manager
Infor CRM
New Member
Posts: 15
Registered: ‎03-05-2014

Re: SLX 8.1: RemoteDB fails on "create index"?

Hello Paul,

 

Via trial & error I am now narrowing down all the problems.

Most are within our own views which use Oracle commands like "to_date" which can not be processed in MSSQL, but e.g. one view is from SLX and does not work as well: "AKA_ML_MGS"

 

What my main problem is: why are all these views packed into the remote DB - they are NOT in resynctabledefs - so I am wondering why SLX creates them.

 

If they are not created I have no probems anymore. That would solve all my problems.

 

How can I make SLX not create them? Is that a feature / bug in 8.1? (in 7.4.2 only 4 views were created, now 100+)

 

Thx!

Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: SLX 8.1: RemoteDB fails on "create index"?

AKA_ML_MGS view ????

 

AFAIK it's not in an OOTB system. I just looked. So it looks like it may be a custom one. I suggest you go in an "decode" the view to see just what it really is.

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html