How to use SQLCMD commands in the SSMS query editor December 13, 2016 by Marko Zivkovic SQLCMD Mode allows creating, testing, executing SQLCMD commands or scripts in SQL Server Management Studio directly in the query editor. Background: I'm using SQLCMD tool to extract data from table and output it to.csv file Everything working fine the only problem is 1. How to append double quotes to text column in SQLCMD output file.
The sqlcmd utility is a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks. To use sqlcmd interactively, or to build script files to be run using sqlcmd, users must understand Transact-SQL. The sqlcmd utility is typically used in the following ways:
Users enter Transact-SQL statements in a manner similar to working at the command prompt. The results are displayed at the command prompt. To open a Command Prompt window, enter 'cmd' in the Windows search box and click Command Prompt to open. At the command prompt, type sqlcmd followed by a list of options that you want. For a complete list of the options that are supported by sqlcmd, see sqlcmd Utility.
Users submit a sqlcmd job either by specifying a single Transact-SQL statement to execute, or by pointing the utility to a text file that contains Transact-SQL statements to execute. The output is usually directed to a text file, but can also be displayed at the command prompt.
SQLCMD mode in SQL Server Management Studio Query Editor.
SQL Server Management Objects (SMO)
SQL Server Agent CmdExec jobs.
Typically used sqlcmd options
Server option (-S) identifies the instance of Microsoft SQL Server to which sqlcmd connects.
Authentication options (-E, -U, and -P) specify the credentials that sqlcmd uses to connect to the instance of SQL Server. NOTE: The option -E is the default and does not need to be specified.
Input options (-Q, -q, and -i) identify the location of the input to sqlcmd.
The output option (-o) specifies the file in which sqlcmd is to put its output.
Connect to the sqlcmd utility
Connecting to a default instance by using Windows Authentication to interactively run Transact-SQL statements:
NOTE: In the previous example, -E is not specified because it is the default and sqlcmd connects to the default instance by using Windows Authentication.
Connecting to a named instance by using Windows Authentication to interactively run Transact-SQL statements:
or
Connecting to a named instance by using Windows Authentication and specifying input and output files:
Connecting to the default instance on the local computer by using Windows Authentication, executing a query, and having sqlcmd remain running after the query has finished running:
Connecting to the default instance on the local computer by using Windows Authentication, executing a query, directing the output to a file, and having sqlcmd exit after the query has finished running:
Connecting to a named instance using SQL Server Authentication to interactively run Transact-SQL statements, with sqlcmd prompting for a password:
HINT!! To see a list of the options that are supported by the sqlcmd utility run:
sqlcmd -?.
Run Transact-SQL statements interactively by using sqlcmd
You can use the sqlcmd utility interactively to execute Transact-SQL statements in a Command Prompt window. To interactively execute Transact-SQL statements by using sqlcmd, run the utility without using the -Q, -q, -Z, or -i options to specify any input files or queries. For example:
sqlcmd -S <ComputerName><InstanceName>
When the command is executed without input files or queries, sqlcmd connects to the specified instance of SQL Server and then displays a new line with a 1> followed by a blinking underscore that is named the sqlcmd prompt. The 1 signifies that this is the first line of a Transact-SQL statement, and the sqlcmd prompt is the point at which the Transact-SQL statement will start when you type it in.
At the sqlcmd prompt, you can type both Transact-SQL statements and sqlcmd commands, such as GO and EXIT. Each Transact-SQL statement is put in a buffer called the statement cache. These statements are sent to SQL Server after you type the GO command and press ENTER. To exit sqlcmd, type EXIT or QUIT at the start of a new line.
To clear the statement cache, type :RESET. Typing ^C causes sqlcmd to exit. ^C can also be used to stop the execution of the statement cache after a GO command has been issued.
Transact-SQL statements that are entered in an interactive session can edited by entering the :ED command and the sqlcmd prompt. The editor will open and, after editing the Transact-SQL statement and closing the editor, the revised Transact-SQL statement will appear in the command window. Enter GO to run therevised Transact-SQL statement.
Quoted strings
Characters that are enclosed in quotation marks are used without any additional preprocessing, except that quotations marks can be inserted into a string by entering two consecutive quotation marks. SQL Server treats this character sequence as one quotation mark. (However, the translation occurs in the server.) Scripting variables will not be expanded when they appear within a string.
Sqlcmd To Output File Append Excel
For example:
sqlcmd
PRINT 'Length: 5' 7';
GO
Here is the result set.
Length: 5' 7'
Strings that span multiple lines
sqlcmd supports scripts that have strings that span multiple lines. For example, the following SELECT statement spans multiple lines but is a single string executed when you press the ENTER key after typing GO.
SELECT First line
FROM Second line
WHERE Third line;
GO
Interactive sqlcmd example
This is an example of what you see when you run sqlcmd interactively.
When you open a Command Prompt window, there is one line similar to:
C:> _
This means the folder C: is the current folder, and if you specify a file name, Windows will look for the file in that folder.
Type sqlcmd to connect to the default instance of SQL Server on the local computer, and the contents of the Command Prompt window will be:
C:>sqlcmd
1> _
This means you have connected to the instance of SQL Server and sqlcmd is now ready to accept Transact-SQL statements and sqlcmd commands. The flashing underscore after the 1> is the sqlcmd prompt that marks the location at which the statements and commands you type will be displayed. Now, type USE AdventureWorks2012 and press ENTER, and then type GO and press ENTER. The contents of the Command Prompt window will be:
sqlcmd
USE AdventureWorks2012;
GO
Here is the result set.
Changed database context to 'AdventureWorks2012'.
1> _
Pressing ENTER after entering USE AdventureWorks2012 signaled sqlcmd to start a new line. Pressing ENTER, after you type GO, signaled sqlcmd to send the USE AdventureWorks2012 statement to the instance of SQL Server. sqlcmd then returned a message to indicate that the USE statement completed successfully and displayed a new 1> prompt as a signal to enter a new statement or command.
The following example shows what the Command Prompt window contains if you type a SELECT statement, a GO to execute the SELECT, and an EXIT to exit sqlcmd:
sqlcmd
USE AdventureWorks2012;
GO
SELECT TOP (3) BusinessEntityID, FirstName, LastName
FROM Person.Person;
GO
Here is the result set.
BusinessEntityID FirstName LastName
----------- -------------------------------- -----------
1 Syed Abbas
2 Catherine Abel
3 Kim Abercrombie
(3 rows affected)
1> EXIT
C:>
The lines after line 3> GO are the output of a SELECT statement. After you generate output, sqlcmd resets the sqlcmd prompt and displays 1>. After entering EXIT at line 1>, the Command Prompt window displays the same line it did when you first opened it. This indicates that sqlcmd has exited its session. You can now close the Command Prompt window by typing another EXIT command.
Running Transact-SQL script files using sqlcmd
You can use sqlcmd to execute database script files. Script files are text files that contain a mix of Transact-SQL statements, sqlcmd commands, and scripting variables. For more information about how to script variables, see Use sqlcmd with Scripting Variables. sqlcmd works with the statements, commands, and scripting variables in a script file in a manner similar to how it works with statements and commands that are entered interactively. The main difference is that sqlcmd reads through the input file without pause instead of waiting for a user to enter the statements, commands, and scripting variables.
There are different ways to create database script files:
You can interactively build and debug a set of Transact-SQL statements in SQL Server Management Studio, and then save the contents of the Query window as a script file.
You can create a text file that contains Transact-SQL statements by using a text editor, such as Notepad.
Examples
A. Running a script by using sqlcmd
Start Notepad, and type the following Transact-SQL statements:
USE AdventureWorks2012;
GO
SELECT TOP (3) BusinessEntityID, FirstName, LastName
FROM Person.Person;
GO
Create a folder named MyFolder and then save the script as the file MyScript.sql in the folder C:MyFolder. Enter the following at the command prompt to run the script and put the output in MyOutput.txt in MyFolder:
sqlcmd -i C:MyFolderMyScript.sql -o C:MyFolderMyOutput.txt
When you view the contents of MyOutput.txt in Notepad, you will see the following:
Changed database context to 'AdventureWorks2012'.
BusinessEntityID FirstName LastName
---------------- ----------- -----------
1 Syed Abbas
2 Catherine Abel
3 Kim Abercrombie
(3 rows affected)
B. Using sqlcmd with a dedicated administrative connection
In the following example, sqlcmd is used to connect to a server that has a blocking problem by using the dedicated administrator connection (DAC).
C:>sqlcmd -S ServerName -A
1> SELECT blocked FROM sys.dm_exec_requests WHERE blocked <> 0;
2> GO
Here is the result set.
spid blocked
------ -------
62 64
(1 rows affected)
Use sqlcmd to end the blocking process.
1> KILL 64;
2> GO
C. Using sqlcmd to execute a stored procedure
The following example shows how to execute a stored procedure by using sqlcmd. Create the following stored procedure.
USE AdventureWorks2012;
IF OBJECT_ID ( ' dbo.ContactEmailAddress, 'P' ) IS NOT NULL
DROP PROCEDURE dbo.ContactEmailAddress;
GO
CREATE PROCEDURE dbo.ContactEmailAddress
(
@FirstName nvarchar(50)
,@LastName nvarchar(50)
)
AS
SET NOCOUNT ON
SELECT EmailAddress
FROM Person.Person
WHERE FirstName = @FirstName
AND LastName = @LastName;
SET NOCOUNT OFF
At the sqlcmd prompt, enter the following:
C:sqlcmd
1> :Setvar FirstName Gustavo
1> :Setvar LastName Achong
1> EXEC dbo.ContactEmailAddress $(Gustavo),$(Achong)
2> GO
EmailAddress
-----------------------------
gustavo0@adventure-works.com
D. Using sqlcmd for database maintenance
The following example shows how to use sqlcmd for a database maintenance task. Create C:BackupTemplate.sql with the following code.
USE master;
BACKUP DATABASE [$(db)] TO DISK='$(bakfile)';
At the sqlcmd prompt, enter the following:
C: >sqlcmd
1> :connect <server>
Sqlcmd: Successfully connected to server <server>.
1> :setvar db msdb
1> :setvar bakfile c:msdb.bak
1> :r c:BackupTemplate.sql
2> GO
Changed database context to 'master'.
Processed 688 pages for database 'msdb', file 'MSDBData' on file 2.
Processed 5 pages for database 'msdb', file 'MSDBLog' on file 2.
BACKUP DATABASE successfully processed 693 pages in 0.725 seconds (7.830 MB/sec)
E. Using sqlcmd to execute code on multiple instances
The following code in a file shows a script that connects to two instances. Notice the GO before the connection to the second instance.
:CONNECT <server>,<instance1>
EXEC dbo.SomeProcedure
GO
:CONNECT <server>,<instance2>
EXEC dbo.SomeProcedure
GO
E. Returning XML output
The following example shows how XML output is returned unformatted, in a continuous stream.
C:>sqlcmd -d AdventureWorks2012
1> :XML ON
1> SELECT TOP 3 FirstName + ' ' + LastName + ', '
2> FROM Person.Person
3> GO
Syed Abbas, Catherine Abel, Kim Abercrombie,
F. Using sqlcmd in a Windows script file
A sqlcmdcommand such as sqlcmd -i C:InputFile.txt -o C:OutputFile.txt, can be executed in a .bat file together with VBScript. In this case, do not use interactive options. sqlcmd must be installed on the computer that is executing the .bat file.
First, create the following four files:
C:badscript.sql
C:goodscript.sql
C:returnvalue.sql
C:windowsscript.bat
Then, at the command prompt, run C:windowsscript.bat:
C:>windowsscript.bat
Running badscript.sql
An error occurred
Running goodscript.sql
Running returnvalue.sql
SQLCMD returned 100 to the command shell
G. Using sqlcmd to set encryption on Windows Azure SQL Database
A sqlcmdcan be executed on a connection to SQL Database data on to specify encryption and certificate trust. Two sqlcmd``options are available:
The -N switch is used by the client to request an encrypted connection. This option is equivalent to the ADO.net option
ENCRYPT = true.The -C switch is used by the client to configure it to implicitly the trust server certificate and not validate it. This option is equivalent to the ADO.net option
TRUSTSERVERCERTIFICATE = true.
The SQL Database service does not support all the SET options available on a SQL Server instance. The following options throw an error when the corresponding SET option is set to ON or OFF:
SET ANSI_DEFAULTS
SET ANSI_NULLS
SET REMOTE_PROC_TRANSACTIONS
SET ANSI_NULL_DEFAULT
The following SET options do not throw exceptions but cannot be used. They are deprecated:
SET CONCAT_NULL_YIELDS_NULL
SET ANSI_PADDING
SET QUERY_GOVERNOR_COST_LIMIT
Syntax
The following examples refer to cases where SQL Server Native Client Provider settings include: ForceProtocolEncryption = False, Trust Server Certificate = No
Connect using Windows credentials and encrypt communication:
Connect using Windows credentials and trust server certificate:
Connect using Windows credentials, encrypt communication and trust server certificate:
The following examples refer to cases where SQL Server Native Client Provider settings include: ForceProtocolEncryption = True, TrustServerCertificate = Yes.
Connect using Windows credentials, encrypt communication and trust server certificate:
Connect using Windows credentials, encrypt communication and trust server certificate:
Connect using Windows credentials, encrypt communication and trust server certificate:
Connect using Windows credentials, encrypt communication and trust server certificate:
If the provider specifies ForceProtocolEncryption = True then encryption is enabled even if Encrypt=No in the connection string.
More about sqlcmd
sqlcmd Utility
Use sqlcmd with Scripting Variables
Edit SQLCMD Scripts with Query Editor
Manage Job Steps
Create a CmdExec Job Step
I can quite easily dump data into a text file such as:
However, I have looked at the help files for SQLCMD but have not seen an option specifically for CSV.
Is there a way to dump data from a table into a CSV text file using SQLCMD?
11 Answers
You can run something like this:
-h-1removes column name headers from the result-s','sets the column seperator to ,-w 700sets the row width to 700 chars (this will need to be as wide as the longest row or it will wrap to the next line)
The last line contains CSV-specific options.
-Wremove trailing spaces from each individual field-s','sets the column seperator to the comma (,)-w 999sets the row width to 999 chars
scottm's answer is very close to what I use, but I find the -W to be a really nice addition: I needn't trim whitespace when I consume the CSV elsewhere.
Also see the MSDN sqlcmd reference. It puts the /? option's output to shame.
Is this not bcp was meant for?
Run this from your command line to check the syntax.
For example:
Please, note that bcp can not output column headers.
See: bcp Utility docs page.
Example from the above page:
kenorbWith PowerShell you can solve the problem neatly by piping Invoke-Sqlcmd into Export-Csv.
SQL Server 2016 includes the SqlServer module, which contains the Invoke-Sqlcmd cmdlet, which you'll have even if you just install SSMS 2016. Prior to that, SQL Server 2012 included the old SQLPS module, which would change the current directory to SQLSERVER: when the module was first used (among other bugs) so for it, you'll need to change the #Requires line above to:
To adapt the example for SQL Server 2008 and 2008 R2, remove the #Requires line entirely and use the sqlps.exe utility instead of the standard PowerShell host.
Invoke-Sqlcmd is the PowerShell equivalent of sqlcmd.exe. Instead of text it outputs System.Data.DataRow objects.
The -Query parameter works like the -Q parameter of sqlcmd.exe. Pass it a SQL query that describes the data you want to export.
The -Database parameter works like the -d parameter of sqlcmd.exe. Pass it the name of the database that contains the data to be exported.
The -Server parameter works like the -S parameter of sqlcmd.exe. Pass it the name of the server that contains the data to be exported.
Export-CSV is a PowerShell cmdlet that serializes generic objects to CSV. It ships with PowerShell.
The -NoTypeInformation parameter suppresses extra output that is not part of the CSV format. By default the cmdlet writes a header with type information. It lets you know the type of the object when you deserialize it later with Import-Csv, but it confuses tools that expect standard CSV.
The -Path parameter works like the -o parameter of sqlcmd.exe. A full path for this value is safest if you are stuck using the old SQLPS module.
The -Encoding parameter works like the -f or -u parameters of sqlcmd.exe. By default Export-Csv outputs only ASCII characters and replaces all others with question marks. Use UTF8 instead to preserve all characters and stay compatible with most other tools.
The main advantage of this solution over sqlcmd.exe or bcp.exe is that you don't have to hack the command to output valid CSV. The Export-Csv cmdlet handles it all for you.
The main disadvantage is that Invoke-Sqlcmd reads the whole result set before passing it along the pipeline. Make sure you have enough memory for the whole result set you want to export.
It may not work smoothly for billions of rows. If that's a problem, you could try the other tools, or roll your own efficient version of Invoke-Sqlcmd using System.Data.SqlClient.SqlDataReader class.
A note for anyone looking to do this but also have the column headers, this is the solution that I used an a batch file:
This outputs the initial results (including the ----,---- separators between the headers and data) into a temp file, then removes that line by filtering it out through findstr. Note that it's not perfect since it's filtering out -,-—it won't work if there's only one column in the output, and it will also filter out legitimate lines that contain that string.
This answer builds on the solution from @iain-elder, which works well except for the large database case (as pointed out in his solution). The entire table needs to fit in your system's memory, and for me this was not an option. I suspect the best solution would use the System.Data.SqlClient.SqlDataReader and a custom CSV serializer (see here for an example) or another language with an MS SQL driver and CSV serialization. In the spirit of the original question which was probably looking for a no dependency solution, the PowerShell code below worked for me. It is very slow and inefficient especially in instantiating the $data array and calling Export-Csv in append mode for every $chunk_size lines.
Usually sqlcmd comes with bcp utility (as part of mssql-tools) which exports into CSV by default.
Usage:
For example:
To dump all tables into corresponding CSV files, here is the Bash script:
kenorbkenorbAn answer above almost solved it for me but it does not correctly create a parsed CSV.
Here's my version:
Someone saying that sqlcmd is outdated in favor of some PowerShell alternative is forgetting that sqlcmd isn't just for Windows. I'm on Linux (and when on Windows I avoid PS anyway).
Having said all that, I do find bcp easier.
Hack-RSince following 2 reasons, you should run my solution in CMD:
- There may be double quotes in the query
Login username & password is sometimes necessary to query a remote SQL Server instance
You can do it in a hackish way. Careful using the sqlcmd hack. If the data has double quotes or commas you will run into trouble.
You can use a simple script to do it properly:
Source: Writing SQL output to CSV with VBScript.
kenorbprotected by WillSep 17 '10 at 13:15
Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?