Problem
As a part of SQL Server production support and day to day tasks we usually come across situations where we get requests to run a T-SQL script against multiple servers. Sometimes the request is to not only to run the script, but save the output too. We recently received a request to run multiple scripts against every PROD and UAT server and then save the output as a csv file for each script from every server. We could have achieved the script execution using CMS server, but with that technology we were not able to save output for each query.
Solution
We have requests to run 4 different scripts against every PROD and UAT server, we have more than 40 PROD and UAT servers. One challenge was every server has a different user defined database name where we are supposed to run the scripts. Beyond this, we had to save the script output with a file format of script name, ServerName and environment.
Using SQLCMD to Run a Script Against Multiple Servers
Looking at the request to run and save output separately for each server using CMS server was not a complete solution, but if your goal is to only run the script against multiple servers you can refer to these tips: Tip1 and Tip2.
This problem can be solved a few different ways. Let’s walk through an option using SQL Server Management Studio in SQLCMD Mode then a second option with just SQLCMD.
Running a Script Against Several Servers Using SQLCMD Mode in SSMS
Open SQL Server Management Studio (SSMS) and connect to new query window. Then go to Query and select SQLCMD. This will connect SQL Query in SQLCMD mode.
Now your SSMS query will be in SQLCMD mode and from a single query window you can connect to multiple SQL Servers. Now you can run any SELECT, UPDATE or any other query from a single window as shown below:
In the SSMS Message window you can see message as shown below.
Connecting to SQLDBAExperts2016,1443... (4 row(s) affected) Disconnecting connection from SQLDBAExperts2016,1443... Connecting to SQLDBAExperts2017,1443... (4 row(s) affected) Disconnecting connection from SQLDBAExperts2017,1443...
This will help you to connect to multiple SQL Servers and run the query against them from a single query window. But the tricky part is how can you save the output of each query in separate csv file? If you have to do it, you will have to right click on each result set and save the output as a CSV file. You will have to repeat the same steps for every server. So in the end you will end up doing this several times if you have run this against every server which is time consuming and not a fruitful solution.
Running a Script Using SQLCMD from Command Prompt
If you don’t know about running SQLCMD from a command prompt, please refer to this SQLCMD Utility tip. It has multiple options to connect and run a query. As an example, we could open a command prompt and run the below query.
sqlcmd -S SQLDBAExperts2016,1443 -d SQLDBAExperts -i C:\scripts\Script1.sql -o C:\scripts\DMI\PROD_SQLDBAExperts2016__Script.csv -s "," –W
Here is a brief explanation of the code and the options:
- -S is used to specify the server against which you need to run the query.
- -d is used to specify the database against which you have to run the query.
- –i is used to specify the input script to run against the SQL Server.
- –o will save the query output in csv format.
- -s will define the column delimiter, in this case I have used a comma as column delimiter. You can also choose a “|” or “;”.
- -W will remove all the trailing spaces from column output.
Running a Script Against Several Servers Using SQLCMD from Command Prompt and Saving Output
Now that you know how to run the script against one server, we will see how we can run the script against multiple servers.
If you have the ServerName and DatabaseName, you can use an Excel spreadsheet to generate the scripts for multiple servers as shown in the below screenshot. You will have to change the column values that are highlighted with your ServerName, database name and script you will have to run.
You can use the concatenate function like I used to concatenate the code from column A to column I (like CONCATENATE(A2,B2,C2,D2,E2,F2,G2,H2,I2)). Column J will have the code you need to execute.
Once you have your code ready for execution, copy all the values from column J to a .txt file on the server from where you have connectivity to all the SQL Servers in your environment. Click here for a sample spreadsheet.
Once, you have the script ready, save the script to the same location where your script is saved – see the screenshot below.
Now your batch is ready. Simply double click on the batch file, it will open a command prompt and will execute the script for each server in the order in the batch file.
Go to the output folder and you will see an output file for every server (see the screenshot below).
Review the files to make sure everything looks good.
Issues with the CSV Output Generated from Command Prompt
After I exported all the data in the CSV files, we had an issue with the format of the CSV files. The subsequent ETL process was failing when importing some of the CSV files with an incorrect number of columns. After some analysis, we found that a few of the columns contained a comma which was the culprit.
We can see an example below for Employee table.
If you export this table using SQLCMD with a comma separated delimiter, the file in Notepad will look like the screen shot below.
And if you open the file in Excel, it will look like the image shown below. The format is incorrect and columns are staggered and overlapped, some rows have more columns than the header.
For this you will have to tweak the SQL script in such a way that it will have double quotes for each text-based column where you have the possibility of a comma in the column itself which will disrupt the csv file formatting.
You have to modify the SQL select statement using the Quotename function as shown below.
SET NOCOUNT ON SELECT [Emp id], QUOTENAME (Employee,'"') AS EMPLOYEE, QUOTENAME (Address,'"') AS Address, Phonenumber FROM Employee GO
When you repeat the process and open the output in Excel it should be visible in the correct format as shown below.
Another option to fix this issue is to use a different delimiter other than comma.
Next Steps
- Please reference the following tips:
