We have an Oracle Database (19.21 SE2) with several heterogenous services connections to MS-SQL-Server databases. On the MS-SQL-Databases I’ve created a view giving information about the Version of this SQL-Server, looking like this:
create view [dbo].[db_info] as
select
convert(nvarchar(128),SERVERPROPERTY('ServerName')) as ServerName
, convert(nvarchar(128),SERVERPROPERTY('Edition')) as Edition
, convert(nvarchar(128),SERVERPROPERTY('ProductVersion')) as ProductVersion
and granted the select-right for this view to the AD-User, which is used for the heterogenous services connection. For most MS-SQL-databases it is possible to query this view from within the Oracle database:
SQL> select * from "dbo"."db_info"@HM_ON_SQL1;
ServerName Edition ProductVersion
------------ ----------------------------- --------------
SQL1 Standard Edition (64-bit) 16.0.4095.4
But for only one SQL-Server I get an error message:
SQL> select count(*) from "dbo"."db_info"@HM_ON_SQL2;
select count(*) from "dbo"."db_info"@HM_ON_SQL2
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-02063: preceding line from HM_ON_SQL2
The strange thing in my opinion is that there is only an ORACLE error message. When I try to query a view or table, that does not exist on SQL2, I receive an error message from the Microsoft side as well:
SQL> select count(*) from "dbo"."x"@HM_ON_SQL2;
select count(*) from "dbo"."x"@HM_ON_SQL2
*
ERROR at line 1:
ORA-00942: table or view does not exist
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Ungültiger Objektname "dbo.x". {42S02,NativeErr = 208}[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Anweisung(en) konnte(n) nicht vorbereitet werden. {42000,NativeErr = 8180}
ORA-02063: preceding 2 lines from HM_ON_SQL2
and in general it is possible to access views or tables on this specific server via the database link:
SQL> select count(*) from "dbo"."spt_monitor"@HM_ON_SQL2;
COUNT(*)
----------
1
Does anyone have an idea, what is wrong with this HS-connection?