How to modify my existing T-SQL query to work for a linked server?

All we need is an easy explanation of the problem, so here it is.

I am using SQL Server 2014 and I need to modify my existing T-SQL query so that it works for a linked server.

My existing codes are as follows:

USE [MyDatabase]

SELECT name 
FROM sysobjects
WHERE id IN ( SELECT id 
              FROM syscolumns 
              WHERE name like '%SPA%' )

I usually connect to the linked server by using the following:

Use [MyDatabase]

Select * from [xxx.xx.0.20].[LinkedDatabaseName].dbo.[TableName]

I have tried the answers given here (but it’s still not working): Unable to query SQL server metadata through linked server

Here’s my try (even though I don’t quite understand why I need to put that dot (.) in sysobjects compared to my existing codes shown above):

   USE [MyDatabase]
    
    SELECT name 
    FROM [xxx.xx.0.20].[LinkedDatabaseName].sys.objects
    WHERE id IN ( SELECT id 
                  FROM syscolumns 
                  WHERE name like '%SPA%' )

I get the following error message: "Invalid column name ‘id’."

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You’d need to make all references in your query point to the table on the other side of the linked server.

i.e.:

USE [MyDatabase]
    
SELECT name 
FROM [xxx.xx.0.20].[LinkedDatabaseName].sys.objects o
WHERE o.object_id IN ( SELECT c.object_id 
              FROM [xxx.xx.0.20].[LinkedDatabaseName].sys.columns c
              WHERE c.[name] like '%SPA%' );

Your current version of the query is looking at sys.objects from the remote database, and syscolumns from the local database in the current context.

You should avoid using syscolumns since that is a deprecated object. Instead, use sys.columns. The id column you’re looking for is actually object_id, or for the column id, it is column_id.

Since you are referencing a database directly via four-part naming through a linked server, the USE [MyDatabase] in the first line is irrelevant.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , ,

Leave a Reply