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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » General » Syntax request needed. Merge an 'openquery'...

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

Rate Topic

Display Mode

Topic Options

Author

Message

johnnyrmtl

Posted Wednesday, November 28, 2012 12:15 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:02 AM
Points: 70, Visits: 325

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

Post #1390052

Lowell

Posted Wednesday, November 28, 2012 1:46 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 11,638, Visits: 27,712

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1390109

johnnyrmtl

Posted Wednesday, November 28, 2012 1:53 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:02 AM
Points: 70, Visits: 325

Thanks ... that's works :)

I was trying to separate both queries.

Post #1390115

johnnyrmtl

Posted Friday, November 30, 2012 12:03 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:02 AM
Points: 70, Visits: 325

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

Post #1391488

Lowell

Posted Friday, November 30, 2012 12:55 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 11,638, Visits: 27,712

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1391522

johnnyrmtl

Posted Friday, November 30, 2012 1:00 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:02 AM
Points: 70, Visits: 325

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

abc master 1 NULL 0x01
abc tempdb 2 NULL 0x01
abc model 3 NULL 0x01
abc msdb 4 NULL 0x01

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.

Post #1391527

Lowell

Posted Friday, November 30, 2012 2:49 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 11,638, Visits: 27,712

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1391564

johnnyrmtl

Posted Monday, December 03, 2012 7:03 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:02 AM
Points: 70, Visits: 325

Thank you ! now the output is as I wanted.

Post #1391922

« Prev Topic | Next Topic »

Permissions