Connect to Server dialog

[kare]

Hello,

I'm evaluating EA and I'm having trouble with the connec to server dialog. I'm trying to connect into a MS SQL server (i've also tried with Oracle 10g), but I always get the following two errors:
1:
Invalid datasource...
Check configuration.
2:
You must supply a connection name.

Despite the fact that the connection is verified in the Datalink Properties dialog.

I've tried using a datasource defined in WinXP Control panel and also by using a connection string. Data sources always work, but EA is unable to connect.

I'm using version 6.1.789.

Help appreciated!

Cheers,
Kare

[«Midnight»]

Hi Kare,

You have to do some preparatory work first. I case you have not yet performed these steps, here they are:

You first need to set up a data source in your target DBMS. This requires setting up a new databae in SQL Server, or a new user in Oracle. In either case you now have a new, named schema for EA to use.

You now need to execute the appropriate script to create tables etc. These can be downloaded from Sparx. The Oracle 9i script worked just fine when I did this in 10g, and the SQL Server 2000 script worked with 2005. Remember to set the appropriate target first (I'm so embarrassed).

Finally - and this is the one that's easiest to miss - you need to do a data transfer. This loads basic background information into the new, empty model you have created. Without this stuff EA just simply won't work. Open EA - you do not need to open a model - and from the main menu choose Tools / Data Management / Data Transfer. Select the EAP to DBMS option, and point to a base file (perhaps an empty one you have created on the desktop). You will also have to go through the connection dialogs to connect to your new DBMS schema. Then do the transfer.

Once all the above is done you should be able to connect. If not, please let me know if this does not work, and where the failure is. I'll try to come up with a fix.

HTH, David

[kare]

Hello!

I've completed all the steps except  the Data Transfer and when I open the "Full Model Data Transfer" and click Target model "..." button. "Data Link Properties" dialog opens up. I'm able to complete that dialog successfully and test the connection to the database. When I close "Data Link Properties" dialog, I'm prompted for a  "SQL Server Login" I type in the password (once again) and dialog closes and no errors are displayed. But when I hit "Transfer Data" button I get the following error: "Could not open target database.". Tables are there and user has required permissions. Nothing gets written to the logfile defined in "Full Model Data Transfer".

I also tried "Compare table record counts" but nothing happens when I click "Compare Models" button (I have succefully completed source and target models).

Cheers,
Kare

[«Midnight»]

If you have not done so, please try setting the security to Integrated Security (or whatever it is called) rather than SQL Server security. While all versions of SQL Server since 2000 (and 6.x as well I believe) have supported this, and it is very strongly urged by MS, for some reason it defaults to the old model in the MS dialogs.

I've found that this removes the need for an additional password, which sometimes helps. I know we can overcome this through automation - there is an additional call to open a model with credentials - but it might be tripping you up in the UI somehow.

If this does not work, please let me know the version of SQL Server you are using, and we'll try walking through the dialogs together.

For reference, my EA machine is running EA 6.1 build 790, WinXP Pro SP2 fully patched, and SQL Server 2000 latest SP (4?) fully patched. I have SQL Server 2005 Express in both 'raw' and SP1 forms, but have only the 'raw' one running and have not done anything with it on this machine.

David

[kare]

Do you refer to "Trusted connection" setting in MS SQL Login dialog? I've checked it and get the following error:
Connection failed:
SQLState '28000'
SQL Server Error: 18452
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

I also retried with Oracle 10g Express and got the following error earlier than with MS SQL Server. Connection test fails in "Data Link Properties" dialog with the following error message:
Test connection failed because of an error in initializing provider. ORA-12154: TNS:colud not resolve the connect identifier specified.
I can, however, successfully test the oracle connection from WinXP Data Source configuration.

Cheers,
Kare

[«Midnight»]

Hi Kare,

Are you doing this on a single machine, or across a network. SQL Server configuration used to want each server to be 'registered.' If not, errors like yours were sure to result.

It's been some time since I had this happen, and I don't have an appropriate test network handy. I do remember that I was able to read the help - more than once - and then perform the required procedure. After that the problem evaporated.

Oracle TNS connections can present a similar problem. It's been some time since I had to contend with them, since more recent Oracle setup utilities seem to get it right more often. However, I do recall seeing some cautions and work arounds in the Oracle 10g release notes. If memory serves, these are particularly important if you have an earlier version of Oracle set up, or an Oracle client on your system before the 10g installation.

I'd try the SQL Server registration tweak first, and if that does not work look at the Oracle release notes.

David

[kare]

Hello!

After a little hiatus I'm investigating this problem again.

Currently I'm running 6.1.791.

And from the "Full Model Data Transfer" dialog I get the following error when I click on "Transfer Data" button.

"Could not open target database"

This error appears immediately after the click. So it seems that it is unable to open the database connection properly. I'm able to access the database with MS SQL client. The MS SQL server is running on a dedicated server and I'am accessing it over the network.

Cheers,
Kare

[«Midnight»]

Are you using the "..." button on the Full Model Data Transfer dialog to set up the target database?

If so, can you successfully follow through the resulting dailogs, including those produced by Windows, to use, or create, a data source?

Finally, can you reach the "Test" button for the data source in these dialogs - they are from Windows, not EA - and get a successful test?

Even if all the above work correctly, do you use Windows security or SQL Server security. The former (Windows) is strongly advised; I don't know if the latter will work in all network environments.

David

[Dave_Bullet]

Found the answer.  Same problem happens to me.

Use the OLE DB driver - not the ODBC one (it's stuffed).

So the steps are:
1. Create an empty SQL server DB.  Make sure you have DBO equivalent permissions
2. Run the Sparx supplied script on the new database to create the base tables an EA model requires
3. Do the DBMS to DBMS transfer - using the OLEDB connection type

For some reason the ODBC one is broken.  Not sure if it is an EA thing.  (FYI I am on 6.1.790)

Cheers,
David.

[Dave_Bullet]

PS: you don't need to transfer in an empty EAP file before you do your DBMS to DBMS transfer.

David.

[«Midnight»]

Dave,

I think you are correct about ODBC being broken, at least for certain repository types - I've only tried this on SQL Server and Oracle.

There was something in the release notes - way back, so I really don't know when - about OLEDB becomming available. I don't know if Sparx ever 'officially' switched, but I doubt it; that would break all kinds of things. The intent might be to use the OLEDB ODBC driver, but that is not spelled out in either the release notes, or documentation.

I notice with interest that the Import DB Schema from ODBC option remains documented as just that, ODBC. I've tried this via OLEDB with the above databases and it seems to work there, but have not done an import through ODBC for some time. This machine has no old drivers (hardwre configuration dates back to Q4 2005), nor have I chosen ODBC connectivity as an option on any installations, so it is a poor test platform

I think this should be issued to Sparx as a bug. In this case the Requests and Suggestions forum is appropriate, since the replies from Sparx (whether this is by design, or whatever) would be of interest to the community. At the very least the documentation and menus should be updated to reflect the current feature set.

I'll leave you to do this, since you can probably give a specific example, which may be helpful to Sparx.

David

[Dave_Bullet]

Good idea.  I'll need to note my O/S platform version, MDAC and ODBC versions, SQL Server driver version to give Sparx the platform to test... or they may just point out I'm not running the right versions which they can then add to the documentation (better check myself it isn't already documented which versions of the above are required!)

BTW - I also found the info you get from an ODBC vs. OLE DB import is different... ODBC import may have worked in build 789, or I might have changed the setup on my PC to break things... need to revalidate

Cheers,
David.