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.
You’d need to make all references in your query point to the table on the other side of the linked server.
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
id column you’re looking for is actually
object_id, or for the column id, it is
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 🙂