06-05-2014 07:43 AM - edited 06-05-2014 07:44 AM
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
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON
CREATE VIEW sysdba.ACCOUNTSYNCVIEW AS SELECT
A.ACCOUNTID as ENTITYID,
0 AS ACTIONTYPE,
CREATE VIEW sysdba.ACTIVITYRESOURCEVIEW AS SELECT
rl.RESOURCEID + '-EVERYONE' as ACTIVITYRESOURCEVIEWID,
rl.RESOURCEID as RESOURCEID,
CREATE VIEW sysdba.AKA_ML_MGS AS SELECT
CREATE VIEW sysdba.AOM_AUSSENDUNG AS select b.accountid,a.baadnb Contractno, trim(a.baoaxp) MaklerID, a.bagigq AOM_Typ,a.BADZTT,
as you can see the views are truncated (red marked)
Anyone got an idea?
06-05-2014 07:48 AM
Just found this on the SLX KB. Any good?
“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
06-06-2014 01:25 AM
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
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?
06-06-2014 01:37 AM
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
06-06-2014 04:51 PM
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.
06-11-2014 05:24 AM
Thanks a lot for that - I am getting closer
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?
06-11-2014 06:11 AM
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.
06-12-2014 12:49 AM
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+)
06-12-2014 05:57 AM
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.