Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Execute SQL Scripts on Multiple SQL Servers and Save Output as CSV Files


By:   |   Last Updated: 2019-05-30   |   Comments (3)   |   Related Tips: More > T-SQL

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.

Connecting SQLCMD using SSMS

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:

Running code in SQLCMD mode in SSMS

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

Connecting SQL CMD from Command Prompt

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.

How to run the Query against Multiple servers and save output

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.

sqlcmd command examples

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.

SQL Cmd batch execution.

Go to the output folder and you will see an output file for every server (see the screenshot below).

sqlcmd output file from every server

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.

Query data in SSMS

If you export this table using SQLCMD with a comma separated delimiter, the file in Notepad will look like the screen shot below.

Export this table using a SQLCMD comma separated output file

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.

Incorrectly formatted Excel file

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.

Properly formatted Excel output

Another option to fix this issue is to use a different delimiter other than comma.

Next Steps


Last Updated: 2019-05-30


get scripts

next tip button



About the author
MSSQLTips author Atul Gaikwad Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, June 03, 2019 - 9:58:42 AM - Aslam Back To Top

This is a very useful tip.thanks for sharing


Friday, May 31, 2019 - 12:46:11 AM - Jan Back To Top

OMG. This is genius. I can't wait to out these tip to good use. Thx.


Thursday, May 30, 2019 - 7:46:01 PM - ken j ambrose Back To Top

SQLCMD is good!  But I prefer Powershell for multi server work and exporting data- it's a real programming language for logic, error hanling, etc.  Big learning curve though to get truly competent with it.

For example with powershell, I created a trivial module to fix quoted strings.  Can reference it everywhere now without duplicating the code.


Learn more about SQL Server tools