Post reply

Syntax request needed. Merge an 'openquery' select towards linkedserver

  • November 28, 2012 at 12:15 pm

    #98376

    hello all,

    How can I merge these two queries to produce one row?

    i.e

    TESTSERVER | VERSION

    select * from openquery([linkedserver here],'select name from sys.servers where server_id =0')

    select * from openquery([linkedserver here],'SELECT @@version AS [VERSION]')

    thanks in advance

  • November 28, 2012 at 1:46 pm

    #1563434

    why not the two values in the same query? just an oversight?

    select * from openquery([linkedserver here],

    'select

    name,

    @@version

    from sys.servers

    where server_id =0')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 28, 2012 at 1:53 pm

    #1563440

    Thanks ... that's works 🙂

    I was trying to separate both queries.

  • November 30, 2012 at 12:03 pm

    #1564515

    I need more help with another query towards a linked server ... pardon my ignorance with the syntax 🙂

    I'm trying to merge these two queries to get the linkedserver along with all databases from it's instance.

    select * from openquery([abc],'SELECT *, CONVERT(VARCHAR(25), DB.name) AS dbName from sys.databases DB')

    and

    select * from openquery([abc],'select name, @@version from sys.servers where server_id =0')

  • November 30, 2012 at 12:55 pm

    #1564546

    in that case, assuming you wanted the value as columns again like your first query, it would go like this...notice i did a simple cross join, on a table we know has just one row.

    select * from openquery([abc],'SELECT

    myAlias.name,

    myAlias.VersionInfo,

    DB.*,

    CONVERT(VARCHAR(25), DB.name) AS dbName

    from sys.databases DB

    CROSS JOIN (select

    name,

    @@version As VersionInfo

    from sys.servers

    where server_id =0) myAlias')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 30, 2012 at 1:00 pm

    #1564551

    Actually i don't need @@version. need something like this

    abc master1NULL0x01

    abc tempdb2NULL0x01

    abc model3NULL0x01

    abc msdb4NULL0x01

    select * from openquery([abc],'SELECT *, CONVERT(VARCHAR(25), DB.name) AS dbName from sys.databases DB')

    and

    select * from openquery([abc],'select name from sys.servers where server_id =0')

    getting this error

    Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "name" is a duplicate.

  • November 30, 2012 at 2:49 pm

    #1564582

    you just need to add an alias, sorry:

    select * from openquery([abc],'SELECT

    myAlias.ServerName,

    DB.*,

    CONVERT(VARCHAR(25), DB.name) AS dbName

    from sys.databases DB

    CROSS JOIN (select

    name As ServerName

    from sys.servers

    where server_id =0) myAlias')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 3, 2012 at 7:03 am

    #1564862

    Thank you ! now the output is as I wanted.

Viewing 8 posts - 1 through 7 (of 7 total)

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