I have 2 SQL server databases on a single windows server. the user that is used to login has sufficient rights on both databases. The application is only able to have a single database connection but requires content from both databases. To get data from the other database views or synonyms can be used. That all works fine. The application then needs to write a result into a table that is not in the database where it is connected to. This table has an ID column that requires an unique value which comes from a sequence. The application code actually does a get next value call so it must use the sequence. see below error message from the log.
could not get next sequence value[SQL: select next value for SEQ_xxx as seq]
I found a post where somebody stated that sql server 2012 it was not possible to create a synonym for a sequence, I tried it on 2014 and 2017 without any luck. Does anybody know if it possible or will be possible in a new version?
And does anybody has a suggestion to solve this in a different way. Swapping the connection to the other database will not work, as both databases are updated with sequences.
SEQUENCEcannot be a target for aSYNONYM.DbB.dbo.SEQ_xxxto access a sequence in another database