query for all instance

Question

query for all instance

tony28

SSCertifiable

Points: 7112
More actions
September 29, 2017 at 1:19 am

#333123

Hello,
is possible do some query for all instances?
any different way than linked server or openrowset? Because linked server not possible here, and openrowset is blocked and I cannot allow.
Maybe with powershell ? or something else ?
thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

When you say query, what is your goal? To run the same query on multiple instances, which will return many resultsets, or are you aiming to run one query/process that returns a single resultset that combines the results from many instances?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Powershell will work, as will a SQLCMD script. You can also run a single query against a group of registered servers. For regular tasks, consider SSIS. Thom is right - we really need to understand what you're trying to do.

John

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

How about cmdshell?
😎
wmic SERVICE LIST FULL|find /i "sqlservr.exe"

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Eirikur Eiriksson - Friday, September 29, 2017 2:50 AM
How about cmdshell?
😎
wmic SERVICE LIST FULL|find /i "sqlservr.exe"

Eirikur, I think he's looking to run queries against known instances, not discover unknown instances.

John

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 5

John Mitchell-245523 - Friday, September 29, 2017 2:59 AM
Eirikur Eiriksson - Friday, September 29, 2017 2:50 AM
How about cmdshell?
😎
wmic SERVICE LIST FULL|find /i "sqlservr.exe"
Eirikur, I think he's looking to run queries against known instances, not discover unknown instances.
John

Understood John😉
😎
The last part of the output is the instance name, it's straight forward to grab and use for connecting to each instance.

Thom A SSC Guru Points: 99133 More actions · Answer 6

Here's a very quick "introduction" that I wrote up while waiting for the coach. This might give you some idea on what you can do with powershell:
[CmdletBinding()] Param( [Parameter(Mandatory=$True, ValueFromPipeline=$True, HelpMessage="-InstanceNames Comma Delimited list of SQL instance to Query.")] [string]$InstanceNames, [Parameter(Mandatory=$True, ValueFromPipeline=$True, HelpMessage="-Query Query to run on each Instance.")] [string]$Query, [Parameter(Mandatory=$True, ValueFromPipeline=$True, HelpMessage="-OutFile FilePath to save output to.")] [string]$OutFile )

Clear-Content $OutFile $InstanceNames.Split(",") | ForEach-Object { Write-Host "Running Query on $_" Invoke-Sqlcmd -Query "Select @@ServerName AS ServerName;" -ServerInstance "$_" | Out-File -filePath "$OutFile" -Append Invoke-Sqlcmd -Query "$Query" -ServerInstance "$_" | Out-File -filePath "$OutFile" -Append } Write-Host "Queries complete."
The script requires 3 parameters, -InstanceNames, -Query and -OutFile. The first is a list of the instances you want to query, the second, the query you want to run those instances, and finally, the location you want to save the output.

So, for example, to run the script (assuming it is called MultiInstanceQuery.ps1 and you are in the directory it is stored): .\MultInstanceQuery.ps1 -InstanceNames "SQL2012Instance,SQL2016Instance,SQL2017DevInstance" -Query "Select [name] AS DatabaseName FROM sys.databases;" -OutFile "C:\TEMP\MultiInstanceOut.txt"
Note that this is very barebones. It doesn't take authentication details, for example, it uses your current credentials. This is just an example of what you could do, or, a stepping stone for your own script.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

tony28 SSCertifiable Points: 7112 More actions · Answer 7

Thanks guys,

I will check.
Goal is that I would like to have select of all jobs on these instances with some query(but this is litlte big and will be funny give him to powershel 😀 )
I think that doesnt matter if will be like every table or together, because than I will summarize, but I dont want click to every instance new query and copy there and etc.. I would like to only execute and check results. This will safe my time you know

I did with powershell few times and also with sqlcmd so it will be also funny, but good for learning something new. 🙂

Thom A SSC Guru Points: 99133 More actions · Answer 8

tony28 - Friday, September 29, 2017 4:00 AM
Thanks guys,
I will check.
Goal is that I would like to have select of all jobs on these instances with some query(but this is litlte big and will be funny give him to powershel 😀 )
I think that doesnt matter if will be like every table or together, because than I will summarize, but I dont want click to every instance new query and copy there and etc.. I would like to only execute and check results. This will safe my time you know
I did with powershell few times and also with sqlcmd so it will be also funny, but good for learning something new. 🙂

This sounds more like you want to run a query on multiple registered servers, and then copy of the results sets into something else (i.e. Excel). Have a look here: https://docs.microsoft.com/en-us/sql/ssms/register-servers/execute-statements-against-multiple-servers-simultaneously

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk