WX12 - Import SQL Server DB into Analysis (oleD

Hi,

I'm trying to import a SQL server database description (JDEdwards) into the analysis using the SQL Server oledb driver. I always get the following error after having selected some files from the list in the wizard (I am able to connect to the database and list all tables, but I am unable to import the descriptions):

Impossible d'ouvrir la table 'F0002'
Erreur de l'accès OLE DB.
Numéro d'erreur = 170129 
Echec de l'ouverture du fichier.
Détail de l'erreur système :
	Description = Invalid object name 'F0002'.
	Source = Microsoft OLE DB Provider for SQL Server
	SQL State = 42S02
	Error Number = -2147217865
	Native Error Number = 208

The reason is pretty obvious (I think). The tables are organized within the initial SQL Server database catalog into subcatalogs (CTRLDTA and TESTDTA). Within the wizard I have no idea how to include this parameter. Can any of you SQLServer guru's on this forum shed a light on this?

Thanks,

Peter



Edited 1 time(s). Last edit at 07/27/2008 11:34AM by Peter H..

Nobody?

An update...

The issue has to do with the ownership of the tables (which is different from dbo): ServerName>>DatabaseName>>OwnerName.TableName. In e.g. Access or Excel you can just import or link any of these SQL Server database tables and query them in a second. I noticed that in e.g. Access the owner is prefixed (with an underscore) to the table name.

When using the import wizard from the Analysis Editor however, this ownership seems not to be included when starting the import of the database definition and that's why it fails.

This could be an important sales argument for WX in this (global engineering company), but as long as I am unable to do even a simple import of a database definition (that can be done just like that in any other tool I tried), this will certainly be a no-go.

I hope any of you guys can help me out here since I am not a SQL Server expert at all.

Cheers,

Peter

Hi Mate,

Apologies for only seeing this now - was out of commission for a while.

As a start, you dont actually import the tables, you are creating a framework within Windev - which are the analysis.

Lets start with a workaround:-
1. Dump the tables into a script, and create a new empty database (In the Enterprise manager, select all tables, right click)
2. Edit the script and change all owners to dbo
3. Run the script on the new database
4. Import and create the analysis

After this is done, you should be able to change your connection settings to the live database with a valid username that have read/write privileges on the specific database, and you should be ok from this point onwards.

Alternatively, you could run the following on the relevant database to change owners:-
sp_changeobjectowner 'tablename', 'dbo'

I wouldnt recommend the second option though, since it might affect other systems which might use the structure

Thanks Ben for the excellent feedback!
In the mean time I got the owner and password from the dba so I was able to import the structures through the standard WD wizard. If that didn't work I was thinking about your workaround. Thanks for showing me in the right direction.

Cheers,

P.

hi Mr Peter Holemans,

Please I have the same problem. can you explain to me what do you mean by "I got the owner and password from the dba". because me too I have tables that had a schema different than dbo