Overblog Follow this blog
Edit post Administration Create my blog
Balavardhan Reddy Narani

Save your SELECT query results in XML File or Text File.

August 6 2009 , Written by Balavardhan Reddy Published on #SQL Server2005


To save the result of your SELECT query to XML or a text file. Well use xp_cmdshell.  xp_cmdshell is an extended stored procedure kept in the master database. It issues OS commands directly to the Windows command shell. You need to be a member of the sys_admin group to use this command or have the xp_sqlagent_proxy_account.

To Save your SELECT query results to XML File or a text file, use this query :

--For XML

EXEC master..xp_cmdshell'bcp "SELECT TOP 5 * FROM cscs.dbo.employee FOR XML PATH (''e''), ROOT(''emp'')" queryout "c:\text.xml" -c -T -x'


-- For Text

EXEC master..xp_cmdshell'bcp "SELECT TOP 5 * FROM cscs.dbo.employee" queryout "c:\text.txt" -c -T -x'

Note: By default, xp_cmdshell is disabled in SQL 2005 for security reasons. To enable it, use the Surface Area Configuration tool or sp_configure. To enable xp_cmdshell using sp_configure, use this query :

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

--

Share this post

Repost 0

Comment on this post