Syntax request needed. Merge an 'openquery' select towards linkedserver
-
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
-
why not the two values in the same query? just an oversight?
select * from openquery([linkedserver here],'selectname,@@versionfrom sys.serverswhere 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! -
Thanks ... that's works
I was trying to separate both queries.
-
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')
-
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],'SELECTmyAlias.name,myAlias.VersionInfo,DB.*,CONVERT(VARCHAR(25), DB.name) AS dbNamefrom sys.databases DBCROSS JOIN (selectname,@@version As VersionInfofrom sys.serverswhere 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! -
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.
-
you just need to add an alias, sorry:
select * from openquery([abc],'SELECTmyAlias.ServerName,DB.*,CONVERT(VARCHAR(25), DB.name) AS dbNamefrom sys.databases DBCROSS JOIN (selectname As ServerNamefrom sys.serverswhere 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! -
Thank you ! now the output is as I wanted.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply