3

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'').

4

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;
|improve this answer|||||
3

Check out OPENQUERY (BOL reference)

OPENQUERY is specifically designed for executing queries directly on the Linked Server.

The first parameter is the name of your Linked Server, whilst the second is the query that you want to execute against it.

I'm not entirely sure of the syntax of your query, but something like this should work.

SELECT ipo 
FROM 
OPENQUERY(mylinkedserver, 
             'SELECT ''Identity property OFF'' AS ipo 
              FROM testdb.dbo.Table_1
              where COLUMNPROPERTY( OBJECT_ID(''Table_1''),''column1'',''IsIdentity'')=0'
);
|improve this answer|||||
  • +1 for replies and effort. Your updated answer didn't work. If you include the from condition it will not work even directly in server. Answer from Aaron Bertrand works and it was easy. – IT researcher Jun 12 '14 at 5:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.