Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Syntax request needed. Merge an 'openquery' select towards linkedserver Expand / Collapse
Author
Message
Posted Wednesday, November 28, 2012 12:15 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:30 PM
Points: 75, Visits: 350
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
Posted Wednesday, November 28, 2012 1:46 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 12,901, Visits: 32,131
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
Posted Wednesday, November 28, 2012 1:53 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:30 PM
Points: 75, Visits: 350
Thanks ... that's works :)

I was trying to separate both queries.
Post #1390115
Posted Friday, November 30, 2012 12:03 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:30 PM
Points: 75, Visits: 350
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
Posted Friday, November 30, 2012 12:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 12,901, Visits: 32,131
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
Posted Friday, November 30, 2012 1:00 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:30 PM
Points: 75, Visits: 350
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
Posted Friday, November 30, 2012 2:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 12,901, Visits: 32,131
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
Posted Monday, December 3, 2012 7:03 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 12:30 PM
Points: 75, Visits: 350
Thank you ! now the output is as I wanted.

Post #1391922
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse