June 28, 2022 at 2:03 pm
I have two servers, ServA and ServB. ServA is set up as a linked server from ServB. The connection tests OK.
On ServA, I have a database DbA that is in the catalog for the linked server on ServB. Only a subset of tables show in the catalog, for examplet TblA is in catalog, TblB is not in the catalog. There is no difference in permissions between the two tables.
From a ServB connection, including on the server, I am able to query ServA.DbA.dbo.TblA, but I get the error:
The OLE DB provider "MSOLEDBSQL" for linked server "ServA" does not contain the table ""DbA"."dbo"."TblA"". The table either does not exist or the current user does not have permissions on that table.
This started after a recent upgrade from SQL2017 to SQL2019, which included a move to new servers.
I have verified that this behavior is consistent to ANY table that is in DbA, those in the catalog can be queried, those not in the catalog get this error.
Does anyone have an idea how to correct this? Again, there are no special permissions at the table level, and I have tested giving the User for the linked server sys_admin privileges, and that has not made a difference. I can find no way to add a table to the catalog.
June 28, 2022 at 3:04 pm
Update:
There is a linked server on ServA that points to ServB. That works correctly. Through testing, I found that the Catalog on ServB matched the database list on SERVB. For some reason, even though it was connecting to ServA, it was picking up the Linked Server catalog on A for itself as the catalog, not the databases on A. Since A had some databases with the same name, it masked the problem.
Once I deleted the original linked server pointing to ServA from ServB, and created a new one with a different name, everything started to work correctly.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply