One of the ways I have worked around this is by using bcp with xp_cmdshell. It's fast, easy and I can integrate it right into my code.
The first thing you have to do is make sure xp_cmdshell is enabled. You can do this in one of two ways.
1. You can use sp_configure and execute the following script.
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
2. You can use the Surface Area Configuration Tool.
Select Surface Area Configuration for Features and check the Enable xp_cmdshell checkbox.
Here is a sample command that will export the results of the sysfiles table to a comma delimited file called bcptest.txt. Just copy and paste this into a query window and execute the query.
EXEC xp_cmdshell 'bcp "SELECT * FROM sysfiles" queryout "C:\bcptest.txt" -T -c -t,'
The parameters that were used are:
. The queryout option allows you to specify a query to export. This could be as simple as the query we have are as complicated as you want. You can also create a view and select the data from a view.
. The file name where the results will be stored is placed after the queryout option.
. The -T parameter specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. You could use -P (password) and -U (user) if you want to use a SQL Server login.
. The -c specifies the character dataype will be used for each field.
. The -t parameter allows you to specify a field delimiter. The character after -t will be used to separate the data fields. If -t is removed, tab will be used as the default delimiter.
