HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Run "USE" with linked server alternatives

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
withalternativesserveruselinkedrun

Problem

I am using SQL server linked servers and find some difficulties while running queries.

Below is the actual query.

USe testdb
SELECT 'Identitiy property OFF' where COLUMNPROPERTY( OBJECT_ID('Table_1'),'column1','IsIdentity')=0


To run it to a linked server I used

Use mylinkedserver.testdb
SELECT 'Identitiy property OFF' where COLUMNPROPERTY( OBJECT_ID('Table_1'),'column1','IsIdentity')=0


But when I run this I get error as shown below
Database 'mylinkedserver' does not exist. Make sure that the name is entered correctly.

Where as I can query the table by using select * from pc91sql.testdb.dbo.Table_1 successfully.

So how to run the above query?(I think i cannot make use of "USE" with linked server.So what is the alternative

EDIT:

These things i have tried after getting nice replies.
Then i used OPENQUERY option

SELECT * FROM OPENQUERY(pc91sql,'SELECT COLUMNPROPERTY( OBJECT_ID(''testdb.dbo.Table_1''),''column1'',''IsIdentity'')');


It returned NULL. But it should have returned 1 as column1 is a identity column.

Then I checked again running my original query directly in the linked server without use command and NULL is returned . If I run same query after I mention use testdb or in SSMS after choosing database from list then it returns 1 .So it means OBJECT_ID is not able to use database name along with table name(i.e like ''testdb.dbo.Table_1'').

Solution

You can use EXEC to run dynamic SQL in a different database or different server. Also please try to use the catalog views directly instead of all of these messy metadata functions.

DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF'' 
      FROM sys.columns AS c
      INNER JOIN sys.tables AS t
      ON c.[object_id] = t.[object_id]
      WHERE c.is_identity = 0
      AND t.name = N''Table_1''
      AND c.name = N''column1'';';

EXEC mylinkedserver.testdb.sys.sp_executesql @sql;


Even better, parameterize it:

DECLARE @t SYSNAME = N'Table_1', @c SYSNAME = N'column1';

DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF'' 
      FROM sys.columns AS c
      INNER JOIN sys.tables AS t
      ON c.[object_id] = t.[object_id]
      WHERE c.is_identity = 0
      AND t.name = @t
      AND c.name = @c;';

EXEC mylinkedserver.testdb.sys.sp_executesql 
  @stmt = @sql,
  @params = N'@t SYSNAME, @c SYSNAME', 
  @t = @t, 
  @c = @c;

Code Snippets

DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF'' 
      FROM sys.columns AS c
      INNER JOIN sys.tables AS t
      ON c.[object_id] = t.[object_id]
      WHERE c.is_identity = 0
      AND t.name = N''Table_1''
      AND c.name = N''column1'';';

EXEC mylinkedserver.testdb.sys.sp_executesql @sql;
DECLARE @t SYSNAME = N'Table_1', @c SYSNAME = N'column1';

DECLARE @sql NVARCHAR(MAX) = N'SELECT ''Identity property OFF'' 
      FROM sys.columns AS c
      INNER JOIN sys.tables AS t
      ON c.[object_id] = t.[object_id]
      WHERE c.is_identity = 0
      AND t.name = @t
      AND c.name = @c;';

EXEC mylinkedserver.testdb.sys.sp_executesql 
  @stmt = @sql,
  @params = N'@t SYSNAME, @c SYSNAME', 
  @t = @t, 
  @c = @c;

Context

StackExchange Database Administrators Q#67935, answer score: 4

Revisions (0)

No revisions yet.