Skip to main content

accessing two databases on the same server with MSSQL

  • August 6, 2018
  • 4 replies
  • 1 view
D

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


#mssql
#database
#Acu4GL

    4 replies

    D

    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


    #mssql
    #database
    #Acu4GL

    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

    D

    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


    #mssql
    #database
    #Acu4GL
    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.
    D

    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


    #mssql
    #database
    #Acu4GL

    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.

    D

    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


    #mssql
    #database
    #Acu4GL
    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

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement