Social media is evolving at a rapid pace and every day I keep on getting question from different methods. Here is the latest question which I received on my Facebook page. The question was how to export the data of query into CSV using SQLCMD.
This is indeed very easy process and very simple command to export any query data. For example we will use AdventureWorks2012 database. Here is the query we will be using for our demonstration.
USE AdventureWorks2012
GO
SELECT TOP 10 sp.BusinessEntityID,
sp.TerritoryID, sp.SalesQuota,
sp.Bonus, sp.CommissionPct
FROM Sales.SalesPerson sp
GO
The above query will return following result set.
Now we can export above data to CSV using SQLCMD using following command.
SQLCMD -S . -d AdventureWorks2012 -Q “SELECT TOP 10 sp.BusinessEntityID, sp.TerritoryID, sp.SalesQuota, sp.Bonus, sp.CommissionPct FROM Sales.SalesPerson sp” -s “,” -o “e:\result.csv”
Generically you can use the following syntax:
SQLCMD -S YourSQLServer -d YourDatabase -U YourUserName -P YourPassword -Q “Your Query” -s “,” -o “C:\Yourfilename.csv”
Now you can go to your file location and open the file and you will see that new csv file created there. When you open the csv file you will notice the results of the query.
Reference: Pinal Dave (http://blog.sqlauthority.com)
SQLCMD can be a very powerful tool in a DBA’s toolkit. Thanks for raising it’s profile.
Gives following output,
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
I have same error.
nice post
cool … !
I am trying to execute below command from SQLCMD and it will gives error like:
Sqlcmd: Error: Internal error at ReadText (Reason: No mapping for the Unicode character exists in the target multi-byte code page).
Command:
SQLCMD -S ServerName -d Database -U YourUserName -P YourPassword -i “C:\sqlfile.sql”
Operating System: window server 2008 R2
this command is successfully executed before formating this machine
please kindly give any suggestions
Thanks
unexpected agrument error occur
unexpected agrument error occur