Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
Scottw2 Absent Member.
Absent Member.
564 views

accessing two databases on the same server with MSSQL

Jump to solution

Hi am trying to read tables from one database and create basically temporary tables in another database on the same server. I have tried the severname.databasename.owner.tablename notation and it doesn't work. It works fine if I use the same database. 

When I try to use the 2nd DB it just creates a vision file. No translation to acu4gl. I have set the filename_HOST to MSSQL for each file, there also doesn't seem to be any acu4gl messages in the error file when I turn file trace on for the 2nd DB. There is if I just use one database.

Can anyone or has anyone been able to communicate to two databases successfully using acu4gl?

Thanks

Tags (3)
0 Likes
1 Solution

Accepted Solutions
DougP Outstanding Contributor.
Outstanding Contributor.

RE: accessing two databases on the same server with MSSQL

Jump to solution

Scott, your approach is essentially correct. You make an alias:
OTHERTABLE databasename.owner.OTHERTABLE
(Since it is on the same server you don't need to fully qualify out that far, but it won't hurt if you do.)
What is missing is the _host setting that can be applied to your alias. You must set DEFAULT_HOST to MSSQL for this to work. 
Note that you also need the stored procedures installed in each database you will access.

View solution in original post

0 Likes
4 Replies
PHenskens Absent Member.
Absent Member.

RE: accessing two databases on the same server with MSSQL

Jump to solution

Scot,  as far as I know you can only access one (1) database through Acu4GL for .... (MSSQL in your ase)  simultaneously,   if you look at the configuration variables for Acu4GL for MSSL you need to set  you will have to set A_MSSQL_DATABASE and you can only set it once in a configuration file:

A_MSSQL_DATABASE configuration variable

A_MSSQL_DATABASE specifies the name of the particular database to be accessed. You cannot open any database files until you have set this variable.

Example

A_MSSQL_DATABASE stores   indicates the "stores" database is to be accessed.

The only way to possibly do what you're trying to do is use both Acu4GL for MSSQL  and also Acu4GL for ODBC  and set them up so you read from one database with Acu4GL  and write to the second database through an Acu4GL for ODBC connection.

I have never ever tried it but I have read/ written data to Vision, MSSQL Server and Oracle database  simultaneously before.

Take care,

Piet

0 Likes
GeorgeK Absent Member.
Absent Member.

RE: accessing two databases on the same server with MSSQL

Jump to solution
Scot and Piet, you can reset A_MSSQL_DATABASE variable as you would any other environment variable. Accept the variable from environment, change the value in working storage and the issue the command to reset it. I do believe it is with the assign statement. If you establish the connection to first database, read it into memory, close that connection, reassign the variable to the other db, connect to that db, then move it and write it you should be able to do it. However, if you want to copy one table in one database to the same table in another database, the sql connection from one database to the other will be faster. Copy table is pretty quick.
0 Likes
DougP Outstanding Contributor.
Outstanding Contributor.

RE: accessing two databases on the same server with MSSQL

Jump to solution

Scott, your approach is essentially correct. You make an alias:
OTHERTABLE databasename.owner.OTHERTABLE
(Since it is on the same server you don't need to fully qualify out that far, but it won't hurt if you do.)
What is missing is the _host setting that can be applied to your alias. You must set DEFAULT_HOST to MSSQL for this to work. 
Note that you also need the stored procedures installed in each database you will access.

View solution in original post

0 Likes
Scottw2 Absent Member.
Absent Member.

RE: accessing two databases on the same server with MSSQL

Jump to solution
Thanks all, the last suggestion is closer to what I am trying to do. I need to access tables in the main database but create temporary tables in another database on the same server either in the tempdb or other database. Along with also dealing with some vision files that are not in the database. The purpose for this is to have separation due to replication and to not over populate the existing database. I will be using a script to copy these temporary tables data to the main database tables.

Thanks
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.