locked
Check If a Table Exists on Linked Server RRS feed

  • Question

  • I have an Issue with a Oracle Linked Server. Where in I have to get the table information from an Oracle Linked Server and update it on a Sql Server Table.

    I have a table on my Sql Serevr database

    CheckDWH (ID, DBType,DBName, TableName, TabExists, RecordCount, UpdateDate)
    Values in the ID, DBType,DBName and TableName columns are static, For Example

    1, Oracle, DWH, TableA
    2, DB2, EDW, TableB
    3,Oracle, ODS, Tablec

    All the databases are Linked servers on my Sql Server.

    What I have to do is,

    I will have to check if the table exists and if it exists get the record count and update the sql server table CheckDWH columns TabExists with 1 and Record count with the count. If the table doesnt exist I will have to update the TabExists and Record count with 0.

    Is there a way that I cann do this. All the tables that I have to check are in Oracle and DB2 and they are linked servers on my Sql Server Database.

    Thanks

    Friday, March 2, 2012 6:05 PM

Answers

    • Marked as answer by Kalman Toth Wednesday, March 14, 2012 5:43 AM
    Friday, March 2, 2012 6:43 PM
  • Declare @strSql varchar(1000),
    		@TableName varchar(100),
    		@linkedServer varchar(100)
    		
    Set		@TableName='Your_TableName'
    Set     @linkedServer='Your_LinkedServer'
    Set @strSql='Select count(1) as TabExists FROM TAB WHERE TNAME='''+@TableName+''''
    SET @strSql = N'select ''Oracle'', ''DWH'',TabExists from OPENQUERY('+@linkedServer+', ''' + REPLACE(@strSql, '''', '''''') + ''')'
    EXEC (@strSql)

    Like this way you can get the tabe count too..
    • Marked as answer by Kalman Toth Wednesday, March 14, 2012 5:43 AM
    Friday, March 2, 2012 7:38 PM
  • It may be provider settings problem as well.

    Are you able SELECT 1 FROM DUAL from this linked Oracle server?


    Serg

    • Marked as answer by Kalman Toth Wednesday, March 14, 2012 5:43 AM
    Friday, March 2, 2012 8:45 PM

All replies

    • Marked as answer by Kalman Toth Wednesday, March 14, 2012 5:43 AM
    Friday, March 2, 2012 6:43 PM
  • Hi Thanks for the reply, But when I try this I am getting this error message

    OLE DB provider "OraOLEDB.Oracle" for linked server "EDWRPT" returned message "Method is not supported by this provider.".

    Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 72

    The OLE DB provider "OraOLEDB.Oracle" for linked server "EDWRPT" reported an error. The provider does not support the necessary method.

    Msg 7311, Level 16, State 2, Procedure sp_tables_ex, Line 72

    Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "OraOLEDB.Oracle" for linked server "EDWRPT". The provider supports the interface, but returns a failure code when it is used.

    Friday, March 2, 2012 7:00 PM
  • Declare @strSql varchar(1000),
    		@TableName varchar(100),
    		@linkedServer varchar(100)
    		
    Set		@TableName='Your_TableName'
    Set     @linkedServer='Your_LinkedServer'
    Set @strSql='Select count(1) as TabExists FROM TAB WHERE TNAME='''+@TableName+''''
    SET @strSql = N'select ''Oracle'', ''DWH'',TabExists from OPENQUERY('+@linkedServer+', ''' + REPLACE(@strSql, '''', '''''') + ''')'
    EXEC (@strSql)

    Like this way you can get the tabe count too..
    • Marked as answer by Kalman Toth Wednesday, March 14, 2012 5:43 AM
    Friday, March 2, 2012 7:38 PM
  • It may be provider settings problem as well.

    Are you able SELECT 1 FROM DUAL from this linked Oracle server?


    Serg

    • Marked as answer by Kalman Toth Wednesday, March 14, 2012 5:43 AM
    Friday, March 2, 2012 8:45 PM