+1 vote

Dear all,

For a customer we changed the DB User name, in order to make it worked, we have :
1- change the DB Schema (following the post : https://overflow.efficy.com/?qa=807/mssql-server-how-to-change-owner-name )
2- change the Efficy Admin name in ACC_ACCOUNTS
3- change the login in Alias manager

We then kill the com+ and we tried to do an upgrade DB but we encountered issues with the SQL function because they are not prefixed with the schema.

We look at a local DBUpgrade script and in my local environnment the function are prefixed for instance below we can see ADMINR14832.GETOPPORTUNICOMPETITORS_VAL ... on the customer database we dont have the prefix.

So my question is how the UpgradeDb.exe tool is retrieving the database Schema, in order for us to check if we missed something in DB ?

create view R_OPPORTUNITIES as select OPPORTUNITIES.*,
(select FULLNAME from ACC_ACCOUNTS L1 where L1.K_USER=OPPORTUNITIES.MANAGER) as R_MANAGER,
(select STATUS from LK_OPPO_STATUS L2 where L2.K_OPPO_STATUS=OPPORTUNITIES.STATUS) as R_STATUS,
(select NATURE from LK_OPPO_NATURE L3 where L3.K_OPPO_NATURE=OPPORTUNITIES.NATURE) as R_NATURE,
ADMINR14832.GET_OPPORTUNI_COMPETITORS_VAL(COMPETITORS) as R_COMPETITORS,
(select COMPETITOR from LK_OPPO_COMPET L4 where L4.K_OPPO_COMPET=OPPORTUNITIES.LOSTAGAINST) as R_LOSTAGAINST,
(select CODE from LK_CURRCY L5 where L5.K_CURRCY=OPPORTUNITIES.CURRCY) as R_CURRCY from OPPORTUNITIES
go
asked in Database by (1.9k points)
edited by
Sure there is only one Alias.ini in use? No virtual store version that is used by accident?
We checked with Alexis but we have only 1 alias.ini
I don't think it's related to the schema name change, because the name that it used by the DbUpgrade "ADMINR14832" is not equal to the previous schema I assume.
I think you missed understand, the ADMINR14832 is from my local computer, I tested it on a standard Efficy and the schema is correct. On the customer we don't have the schema here is the script we have :

create view R_COMPANIES as select COMPANIES.*,
(select RELATION from LK_COMP_COMP_RLT L1 where L1.K_COMP_COMP_RLT=COMPANIES.KIND) as R_KIND,
(select LEGALKIND from LK_COMP_LEGALKIND L2 where L2.K_COMP_LEGALKIND=COMPANIES.LEGALKIND) as R_LEGALKIND,
(select SECTORNAME from LK_COMP_SECTOR L3 where L3.K_COMP_SECTOR=COMPANIES.SECTORCODE) as R_SECTORCODE,
(select COUNTRY from LK_COUNTRY L4 where L4.K_COUNTRY=COMPANIES.COUNTRY) as R_COUNTRY,
(select F_VALUE from LK_COMP_TYPE L5 where L5.K_COMP_TYPE=COMPANIES.F_TYPE) as R_F_TYPE,
(select F_VALUE from LK_COMP_INVESTOR L6 where L6.K_COMP_INVESTOR=COMPANIES.F_INVESTOR) as R_F_INVESTOR,
(select F_VALUE from LK_COMP_INV_ORIGIN L7 where L7.K_COMP_INV_ORIGI=COMPANIES.F_INVESTOR_O) as R_F_INVESTOR_O,
GET_COMPANIES_F_CATEGORY_VAL(F_CATEGORY) as R_F_CATEGORY,
(select F_VALUE from LK_COMP_INV_TYPE L8 where L8.K_COMP_INV_TYPE=COMPANIES.F_INVESTOR_TYPE) as R_F_INVESTOR_TYPE from COMPANIES
go
Indeed, I was not aligned ;-)
I would always come back to the Alias.ini. Either it's using another file or the configuration is not OK. I would try:
1. Delete/rename the alias.ini and kill COM+
2. Execute Dbupgrade. If it still connects, it gets the Alias info from another location
3. Else, delete the alias and add it again from the beginning
I renamed the Alias.ini and kill the com+ and now I cannot get any alias in the UpgradeDb.exe
Any difference between Conficy upgrade and DbUpgrade?
I cannot test yet on conficy, I only have access to DB from the Services server, not the app server

1 Answer

+1 vote
Best answer

For SQL Server the database owner is obtained with the following query:

  select cast(sysusers.name as varchar(30)) from sysobjects, sysusers
    where sysobjects.type = 'U' and sysobjects.name = 'SYS_DATABASE' and sysobjects.uid = sysusers.uid

Basically we retrieve the owner of SYS_DATABASE.
If this is empty, there must be something wrong in the system tables (sysobjects, sysusers).

answered by (1.2k points)
selected by
Dear Robert,

I think we have an issue on this query, I explain.
For a customer I have the issue that the Schema cannot be retrieve from Efficy because the user doesn't exist anymore in Database. So I looked into MSSQL documentation, and I found that the sysobjects.uid seems to be the id of the Schema not the user ... so I think the following query should be used to find the SCHEMA in Efficy


select cast(sys.schemas.name as varchar(30)) from sysobjects, sys.schemas
    where sysobjects.type = 'U' and sysobjects.name = 'SYS_DATABASE' and sysobjects.uid = sys.schemas.schema_id


PS: I also remarked that there is a table sys.objects which has a column schema_id ... it is may be better to use this table than the current sysobjects table used today ?
Thanks, we'll investigate this.
The query above has been working fine for the past 15 years on countless databases, changing it will require some very convincing arguments :).
1,249 questions
1,519 answers
1,859 comments
328 users