Unable to cut remote database in Version 8.1 [ Edited ]

[steve_garner]

Checked all paths and passwords.

Get message:

[2/6/2014 2:30:07 PM]Detaching remote db...
[2/6/2014 2:30:07 PM]Detaching remote DB error: The database 'SLX_TestRemote' does not exist. Supply a valid database name. To see available databases, use sys.databases
Database creation cancelled.

No other error messages that I know of.

Does anyone have any ideas?

We are on Oracle db and have enabled UTF-8 with AL32UTF8 characterset.

All Oracle views created.

[steve_garner]

Hi All,

I resolved this error:  The issue seems to be that the SQL Native Client was not installed.  We had installed the full SQLServer 2008 R2 and selected it during the SLX install (Oracle is our master database).

As soon as I installed the SQL Express, I got by this error.

Now there is a new issue in the DB Cut process.  We get to the create database views section of the cut and it errors as follows:

[2/13/2014 9:35:51 AM]Starting step: stpExecSQL_CreateViews
[2/13/2014 9:35:51 AM]Error in step: stpExecSQL_CreateViews Microsoft SQL Native Client The multi-part identifier "A.MODIFYDATE" could not be bound.
[2/13/2014 9:35:51 AM]Finished step: stpExecSQL_CreateViews
[2/13/2014 9:35:51 AM]

Step: stpExecSQL_CreateViews returned error: The multi-part identifier "A.MODIFYDATE" could not be bound. (Microsoft SQL Native Client (80040e14): The multi-part identifier "A.ACCOUNTID" could not be bound.) (Microsoft SQL Native Client (80040e14): The multi-part identifier "A.TICK" could not be bound.) (Microsoft SQL Native Client (80040e14): The multi-part identifier "A.APPID" could not be bound.) (Microsoft SQL Native Client (80040e14): The multi-part identifier "A.GLOBALSYNCID" could not be bound.)

I got this error on another system, and resolved by systematically removing the views until I found the OA_ oracle views seemed to be the culprit.  The view in this message is obviously the AccountSyncView.  In the working system I was able to replace this view and still be able to cut the database.

On my new test system, nothing I do seems to fix things and allow a db cut to finish.  I even truncated and copied the views and resynctabledefs data from  the good system to the new test system to no avail.  DB cut is still failing at create views.

I am stumped and will call SLX support today.  Anyone have any ideas or things to try?

[RJLedger]

If you are using SQL2008R2 then you need:

     2008R2 Native Client driver

     2005 Native Client Driver

      .. backwards compat

Make sure you install ALL the above on ALL systems (client and Saleslogix Server) 

[steve_garner]

Hi RJ,

Thanks, but I have tried that.  Just called SLX and they suggested it too. It is the oddest thing that System A works but System B does not.  I have compared them and they seem to be the same.  I can cut a DB on System A admin box for System A db, but can't cut a DB on System A admin box for System B db.  Both to the same SQLServer Instance. Copying views and resynctabledefs from A to B also did not work.  

Everything points to a System B db issue, but I cannot find any diffs, and I followed the same upgrade path and applied all the bundles and oracle scripts from 7.2.x to 7, 5, 7.5x to 8.0 and 8.0 to 8.1.  I even applie the new HF1 for 8.1.

My call to SLX informs me that Oracle is not yet certified on 8.1.  Although 8.1 solves many other issues, we may have to revert to 8.0.  This would be too bad.

Thanks for you input.  If you hae any other tricks, I'm all ears.

Steve

[RJLedger]

Correct.. Oracle is NOT yet "cert" for 8.1.. so you should have not upgraded.

also.. this is a bit troubling:

"...Copying views and resynctabledefs from A to B also did not work.  ..."

This is not a good thing to do. resynctabledefs is a meta-data table that depends on values in Sectabledefs, joindata, and the profiles table.. it is not a "constant" between db's and can totally trash the target db.. this is something one should NEVER do.

[steve_garner]

Of course, this table copy was a last ditch effort on one of our 3 cloned devs.

As far as the version goes, we missed the "TBD" in the Oracle column of the requirements, or we never would have gone this way. That said, version 8.1 did solve several issues discovered in our 8.0 testing.

I believe I have discovered the DB cut issue.  It seems that the script cuts off at a certain size.  Tracing with SQL, I see the last partial sql command executed is cut off.  When I edited this view, changed 0 to 0.0, the script cut off 2 characters shorter.  Also, the error make sense based on the execution of this partial sql.

Last night I got a db cut by removing some views.  Working with SLX on a resolution.  Will most likely roll back to 8.0 for our deployment.

[RJLedger]

You posted:

"... Last night I got a db cut by removing some views. ..."

Any chance these views do not properly reflect the table(s) they were "created from"?

[pzeimet]

Steve,

Check your native connection to Oracle.  Cutting a remote requires that the Chunksize property be set to something around 3000.  I believe this is documented.

Without that setting Oracle will truncate large blob/memo data retrieved from their system tables.

[steve_garner]

Hi Paul,

You hit the nail on the head.  I had missed that setting in the documentation.

I set the chunksize to 8000 and cut 3 DBs successfully.

Still had to remove 6 of 9 of my custom views that were Oracle-based (not enabled through the database manager) .  These work fine in 7.2.1 because I believe the sync ignores views not enabled.  Not so in 8.1.  Also took out the obsolete OA_ views.

Cutting Remote office now.  Fingers crossed.

We still may roll back to 8.0, but are working with SLX support for the time being.

Thanks for all the help from everyone.

Steve

[cloudstack]

Hi All-

I also met an issue when created remote user database with Oracle DB. this issue about create views. this issue's details as bellows:

[2/24/2014 7:59:56 PM]Error in step: stpExecSQL_CreateViews Microsoft SQL Native Client Incorrect syntax near '.'.
[2/24/2014 7:59:56 PM]Finished step: stpExecSQL_CreateViews
[2/24/2014 7:59:56 PM]Step: stpExecSQL_CreateViews returned error: Incorrect syntax near '.'.

We had installed the following software:

2008R2 Native Client driver

2005 Native Client Driver

...backward compat

we tried it with Oracle 10g and 11gR2 database. Anyone have any ideas or things to try?