Post reply

Run “USE” with linked server alternatives

  • June 11, 2014 at 8:23 am

    #286415

    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

  • June 11, 2014 at 10:27 am

    #1720822

    No you can't use "USE" to connect to a linked server. That would be cool if you could, but USE only allows you to change database context on the server.

    I think you could include the use in your openquery query like this:

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

    I don't know for sure if that would work.

    Another option is to create a stored procedure or function on the linked server that you call to get the information you need.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply