locked
Run “USE” with linked server alternatives RRS feed

  • Question

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

    Below is the actual query.

    USe testdb
    SELECT COLUMNPROPERTY( OBJECT_ID('Table_1'),'column1','IsIdentity')



    It returns 1 as column1 is identity column 
    To run it to a linked server I used
    Use mylinkedserver.testdb
    SELECT COLUMNPROPERTY(OBJECT_ID('Table_1'),'column1','IsIdentity') 



    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.

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


    So how to run the above query?(I think i cannot make use of "USE" with linked server.So what is the alternative 
    Wednesday, June 11, 2014 2:36 PM

Answers

  • One way to accomplish what you want to do is by using dynamic SQL.  For example:

    execute [mylinkedserver].master.dbo.sp_executesql N'USe testdb SELECT COLUMNPROPERTY( OBJECT_ID(''Table_1''),''column1'',''IsIdentity'')'

    • Proposed as answer by Fanny Liu Friday, June 13, 2014 9:49 AM
    • Marked as answer by Fanny Liu Friday, June 20, 2014 8:54 AM
    Wednesday, June 11, 2014 7:54 PM