This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a kind of utility article which you can use to export your records or data from SQL Server Database to Windows Text File.
Let me share, where I am using this. I am monitoring the performance of a few critical stored procedures by logging in line important messages.
Later, I am saving this details into a text file and sending that text file as an email attachment. We can also directly send table data as an email body, but we require into files for other tools also.
You can use xp_cmdshell and BCP command to export your data from SQL Server to Text File.
If you are getting an error like below, enable xp_cmdshell. You can use below reference article to enable xp_cmdshell.
|
1 2 3 |
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online. |
SQL Server: Script to Enable and Disable XP_CMDSHELL Configuration
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE tbl_Students ( Rno INT PRIMARY KEY ,STudName VARCHAR(20) ,ClassName CHAR(1) ) GO INSERT INTO tbl_Students VALUES (1,'Anvesh','A'),(2,'Neevan','B'),(3,'Toby','C') ,(5,'Roy','A'),(4,'Jenny','B'),(6,'Kaviy','C') ,(7,'Martin','A'),(8,'Laxmi','B'),(9,'Nion','C') GO |
Execute xp_cmdshell to export data in text file:
|
1 |
EXEC xp_cmdshell 'bcp "SELECT * FROM dbo.tbl_Students" queryout "C:\Test\StudentData.txt" -T -c -t,' |
Check the text file:
