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