[Migrated content. Thread originally posted on 28 July 2006]
I'm having trouble accessing a second MSSQL server. I have the A_MSSQL_DEFAULT_CONNECTION configuration file variable set to the first server.A_MSSQL_DEFAULT_CONNECTION EDCMSDE\\SupportCA
The database in each server is the same name so the A_MSSQL_DATABASE configuration variable has been set to it.
A_MSSQL_DATABASE CAMPUSANYWARE
Later in the configuration file I have reassigned the name of the table in the second database.
tblacct-3.dat EDCCA\\MainCA.CAMPUSANYWARE..tblacct_3
Within my program I am setting the A_MSSQL_LOGIN and A_MSSQL_PASSWD for each before opening the files for each.
SET CONFIGURATION "A_MSSQL_LOGIN" TO CA-LOGIN.
SET CONFIGURATION "A_MSSQL_PASSWD" TO CA-PASSWD.
OPEN INPUT NAME-FILE.
......
SET CONFIGURATION "A_MSSQL_LOGIN" TO MIP-LOGIN.
SET CONFIGURATION "A_MSSQL_PASSWD" TO MIP-PASSWD.
OPEN INPUT ACCOUNT-DEPT-UNIT-FILE.
......
The trace on the NAME file shows this.
NAME.DAT: open input (thread710)
S236 'NAME.DATE', 196608, '0000000448,0000000448,007,0'
S8
S202, NAME.DAT
A69: 'NAME.DAT'
A2: 'NAME'
S203, 'EDCMSDE\\SupportCA.CAMPUSANYWARE..NAME'
S15
S55...
The trace on the Account/Dept/Unit file shows this.
tblacct_3.dat: open input (thread697)
>>>translated name = EDCCA\\MainCA.CAMPUSANYWARE..tblacct_3
S236 'EDCCA\\MainCA.CAMPUSANYWARE..tblacct_3', 196608, '0000000221,0000000221,001,0'
S8
S202 MainCA.CAMPUSANYWARE..tblacct_3
A69 'MainCA.CAMPUSANYWARE..tblacct_3'
A2: 'MainCA.CAMPUSANYWARE..tblacct_3'
S203, 'MainCA.CAMPUSANYWARE..tblacct_3'
S15
S14
Trying to open a connection to server MainCA
ErrorCallback: severity: 9
dberr: 10004 (Unable to connect: SQL Server is unavailable or does not exist. oserr: 53 (ConnectionOpen (Connect()).)
S201 19
S48 (MainCa)
S29
S21
>>>file status = 9D,10004
You may notice on the second trace at the S203 it has lost the volume indicator. If I switch the order, the same thing occurs, but it shows that each server is available to be connected to.
I had tried changing the default connection inside the program and not use the name reassign.
SET ENVIRONMENT 'A_MSSQL_DEFAULT_CONNECTION'
TO MAIN-CONNECTION.
I did an accept on that variable afterward to see that it would show it was changed, but in the file trace it had stayed to what was set in the configuration file. Thus it would be looking for the table in the database on the wrong server. Wouldn't find it of course.
If the volume label didn't have to be specified, there doesn't seem to be a problem. It just looks like maybe a bug in the interpreter removing the volume label for that S203 line.
So, is anyone else using multiple MSSQL servers on different volumes that could show me a solution?
Thanks.
