Looping the query through multiple linked servers

1

This is the query I have.I am trying to figure out how do I query this against multiple linked servers.I have 7 linked servers.

select count() from (select col1,col2,count()NoOfDiscounts
from tblname a
join tblname2 b
on a.columnID=b.columnID
where col1>0
and col2 is not null
group by col1,col2
having count(*)>1)a

Thanks in advance

2

Do you know about four part naming? (server_name.[database_name].[schema_name].object_name)

3

Yes.But don't want to create 7 different queries for 7 servers.I would like to see results like server1 ,count. server2 count and so on.Thanks

4

I need help in this query,how do I concatenate col2 is not null in this dynamic query.If I removed this part query runs fine,but if i put this part,gives me an error 'near the keyword 'is'

set @sql='SELECT * from OPENQUERY('+@servername+',
''select count() from (select col1,col2,count()NoOfDiscounts
from tblname a
join tblname2 b
on a.columnID=b.columnID
where col1>0
and col2 is not null
group by col1,col2
having count(*)>1)a'

EXEC sp_executesql @SQL

Thanks a lot.

5

you missed this at the end of your query

')'

should be

having count(*)>1)a'')'
1 Like