SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
johnnyrmtl
johnnyrmtl
Say Hey Kid
Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)

Group: General Forum Members
Points: 709 Visits: 429
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
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70960 Visits: 40924
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!
johnnyrmtl
johnnyrmtl
Say Hey Kid
Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)

Group: General Forum Members
Points: 709 Visits: 429
Thanks ... that's works Smile

I was trying to separate both queries.
johnnyrmtl
johnnyrmtl
Say Hey Kid
Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)

Group: General Forum Members
Points: 709 Visits: 429
I need more help with another query towards a linked server ... pardon my ignorance with the syntax Smile

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')
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70960 Visits: 40924
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!
johnnyrmtl
johnnyrmtl
Say Hey Kid
Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)

Group: General Forum Members
Points: 709 Visits: 429
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.
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70960 Visits: 40924
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!
johnnyrmtl
johnnyrmtl
Say Hey Kid
Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)Say Hey Kid (709 reputation)

Group: General Forum Members
Points: 709 Visits: 429
Thank you ! now the output is as I wanted.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search