Sending a request to the specified MS SQL Server databases of all the specified servers using available tools
Write this article I was prompted by a note respected jobgemws " Sending a request to all databases of all these servers on the example of MS SQL Server and C #. NET "
I will tell you how a similar task can be solved with the standard MSSQLSERVER tools, namely SSMS (or, for management extremes - sqlcmd), quickly, without programming, with the help of a tiny life hack.
So, in our organization there are several dozens of different types of MSSQLSERVER, different editions. Express prevails, but this is not important. The infrastructure is old, entirely inherited, and “piecewise-continuous”.
')
The task: “run the same query / package on several servers in several databases”, we have, as they say, “rarely happens, but often it happens”.
And the fact that this “rarely happens” does not allow to invent a worthy justification for purchasing or writing full-fledged software for centralized administration of the entire zoo, and what “often happens” happens sometimes requires an instant decision in the style of “5 seconds before the explosion”.
But all this is a lyrical digression, a disclaimer and all that.
What do we need to run the script on multiple servers, in many different databases? In addition to the script itself, of course, which lies with us, well, at least on the desktop: "C: \ Users \ usr_root \ Desktop \ check_version.sql".
The script can be absolutely anything, generated, or written by hand, so I don’t give it here. However, you need to keep in mind that Use [database] should be removed from it.
So, we will need:
1. Privileges allowing to connect to the server and execute this script. Who said sysadmin?
2. The list of servers with databases in which you are going to run the script
The list is, of course, better stored in a table somewhere on the server, but I usually get around with the table designer.
Well, of course, it goes without saying that it is better that the connection to the servers would be carried out in Windows mode, which will make it possible for the privileged users not to show passwords to random eyes.
3. SSMS, at the initial stage, to create a script to run on other servers.
4. SSMS or sqlcmd to execute it.
Let's get started
1. Run the following script on any server instance:
2. Click on the result of the execution
3. In the window that opens, do Ctrl + A, Ctrl + C.
4. Open the new request window, make Ctrl + V.
5. In the Query SSMS menu item, select “SQLCMD mode”. The script will immediately turn into cheerful gray tones, which means that the sqlcmd commands are recognized:
6. Voila! You can run. The script will be executed alternately on several servers, without registration and sms ... uh ... sorry, without C #, PowerShell and other wonders, which usually have neither time, nor ... no, well, there is a desire. Just laziness.
Additionally, I want to say that if the script selects some data, generating (possibly several) resulting data sets, then they, as usual, will appear in the “basement for data” SSMS query window, from where you can get something like ssmsbooster which, for some time, unfortunately, has become severely paid.
Well, or just change the display mode of the result to “Result to Text” or “Result to File” and then parse the resulting text with delimiters with something like Excel.
However, you can use the command: out <filename>, which can also be written into the script, along with: connect and: r
That is, in fact, all the magic.
The disadvantages of the method include the fact that scripts are executed on servers sequentially, but, since this life hack is rather related to the category of improvised means — this is hardly important.
Well, links on the topic:
→ Edit SQLCMD Scripts with Query Editor
→ An SQLCMD Mode in SSMS
→ Using the SSMS Query Editor in SQLCMD Mode
I will tell you how a similar task can be solved with the standard MSSQLSERVER tools, namely SSMS (or, for management extremes - sqlcmd), quickly, without programming, with the help of a tiny life hack.
So, in our organization there are several dozens of different types of MSSQLSERVER, different editions. Express prevails, but this is not important. The infrastructure is old, entirely inherited, and “piecewise-continuous”.
')
The task: “run the same query / package on several servers in several databases”, we have, as they say, “rarely happens, but often it happens”.
And the fact that this “rarely happens” does not allow to invent a worthy justification for purchasing or writing full-fledged software for centralized administration of the entire zoo, and what “often happens” happens sometimes requires an instant decision in the style of “5 seconds before the explosion”.
But all this is a lyrical digression, a disclaimer and all that.
What do we need to run the script on multiple servers, in many different databases? In addition to the script itself, of course, which lies with us, well, at least on the desktop: "C: \ Users \ usr_root \ Desktop \ check_version.sql".
The script can be absolutely anything, generated, or written by hand, so I don’t give it here. However, you need to keep in mind that Use [database] should be removed from it.
So, we will need:
1. Privileges allowing to connect to the server and execute this script. Who said sysadmin?
2. The list of servers with databases in which you are going to run the script
The list is, of course, better stored in a table somewhere on the server, but I usually get around with the table designer.
Well, of course, it goes without saying that it is better that the connection to the servers would be carried out in Windows mode, which will make it possible for the privileged users not to show passwords to random eyes.
3. SSMS, at the initial stage, to create a script to run on other servers.
4. SSMS or sqlcmd to execute it.
Let's get started
1. Run the following script on any server instance:
Select N' :connect ' + t.[server] + isNull(N' -U ' + t.[user] + N' -P ' + t.[pwd],N'') + N' Use ' + t.base + N' GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO ' From (Values (N'dbt0300',N'5.144.18.12', N'sa', N'pwd@',''), (N'dbt1100',N'S01100s0', NULL, NULL,''), (N'dbt1200',N'S01200s11', NULL, NULL,'-'), (N'dbt1300',N'S01300', NULL, NULL,''), (N'dbt1800',N'S01800', NULL, NULL,''), (N'dbt2300',N'S02300', NULL, NULL,''), (N'dbt2800',N'S02800', NULL, NULL,''), (N'dbt2900_1',N'S02900-backup', NULL, NULL,''), (N'dbt3000',N'S03000-2', NULL, NULL,''), (N'dbt3400',N'S03400_tds', NULL, NULL,''), (N'dbt3600',N'S03600_s1', NULL, NULL,''), (N'dbt3700_1',N'S03700-s1', NULL, NULL,''), (N'dbt3900',N'S03900s1', NULL, NULL,''), (N'dbt4100',N's04100s2', NULL, NULL,''), (N'dbt4900',N'S04900s0', NULL, NULL,''), (N'dbt5600',N'S05600v', NULL, NULL,''), (N'dbt5700_release',N'S05700v', NULL, NULL,''), (N'dbt6100',N'S06100s1', NULL, NULL,''), (N'dbt6200',N'S06200-1', NULL, NULL,''), (N'dbt6500',N'S06500', NULL, NULL,''), (N'dbt6501',N'S06500-2', NULL, NULL,''), (N'dbt6600',N'S06600-1', NULL, NULL,''), (N'dbt7400',N'S07400-01', NULL, NULL,''), (N'dbt6700',N'S06700-bd01', NULL, NULL,''), (N'dbt7000_0',N'S07000-01', NULL, NULL,''), (N'dbt7200',N'S07200-02', NULL, NULL,''), (N'dbt7700',N'S07710-01', NULL, NULL,''), (N'dbt8600',N'S08600-s1', NULL, NULL,''), (N'dbt8700',N'8.12.36.11',N'sa', N'sapwd' ,''), (N'dbt8900',N'S08950s3', NULL, NULL,'- '), (N'dbt5000_zzz',N'S09700s1', NULL, NULL,' ')) t(base, [server], [user], [pwd], [name]) For xml path(N''), type 2. Click on the result of the execution
3. In the window that opens, do Ctrl + A, Ctrl + C.
4. Open the new request window, make Ctrl + V.
5. In the Query SSMS menu item, select “SQLCMD mode”. The script will immediately turn into cheerful gray tones, which means that the sqlcmd commands are recognized:
6. Voila! You can run. The script will be executed alternately on several servers, without registration and sms ... uh ... sorry, without C #, PowerShell and other wonders, which usually have neither time, nor ... no, well, there is a desire. Just laziness.
What script was the result of (+)
:connect 5.144.18.12 -U sa -P pwd@ Use dbt0300 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S01100s0 Use dbt1100 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S01200s11 Use dbt1200 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S01300 Use dbt1300 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S01800 Use dbt1800 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S02300 Use dbt2300 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S02800 Use dbt2800 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S02900-backup Use dbt2900_1 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S03000-2 Use dbt3000 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S03400_tds Use dbt3400 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S03600_s1 Use dbt3600 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S03700-s1 Use dbt3700_1 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S03900s1 Use dbt3900 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect s04100s2 Use dbt4100 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S04900s0 Use dbt4900 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S05600v Use dbt5600 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S05700v Use dbt5700_release GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S06100s1 Use dbt6100 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S06200-1 Use dbt6200 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S06500 Use dbt6500 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S06500-2 Use dbt6501 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S06600-1 Use dbt6600 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S07400-01 Use dbt7400 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S06700-bd01 Use dbt6700 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S07000-01 Use dbt7000_0 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S07200-02 Use dbt7200 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S07710-01 Use dbt7700 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S08600-s1 Use dbt8600 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect 8.12.36.11 -U sa -P sapwd Use dbt8700 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S08950s3 Use dbt8900 GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO :connect S09700s1 Use dbt5000_zzz GO :r "C:\Users\usr_root\Desktop\check_version.sql" GO Additionally, I want to say that if the script selects some data, generating (possibly several) resulting data sets, then they, as usual, will appear in the “basement for data” SSMS query window, from where you can get something like ssmsbooster which, for some time, unfortunately, has become severely paid.
Well, or just change the display mode of the result to “Result to Text” or “Result to File” and then parse the resulting text with delimiters with something like Excel.
However, you can use the command: out <filename>, which can also be written into the script, along with: connect and: r
That is, in fact, all the magic.
The disadvantages of the method include the fact that scripts are executed on servers sequentially, but, since this life hack is rather related to the category of improvised means — this is hardly important.
Well, links on the topic:
→ Edit SQLCMD Scripts with Query Editor
→ An SQLCMD Mode in SSMS
→ Using the SSMS Query Editor in SQLCMD Mode
Source: https://habr.com/ru/post/345344/
All Articles