There are multiple situations that will require you to send the results of a query to a text file, in example I could mention a recent experience. The company I work for has shut down a distribution center and decided to hand over all the logistic operations to a third party broker, they wanted to get directly connected to our ERP system databases as well as query and push transactions to them, obiusly my immediate answer was "No way!" so I decided to came up wit the idea of send text files thru FTP with shipping details, that was what these guys needed... maybe is not the best approach but I could not afford to have them screwing up the data in our ERP system either, I currently work with Solarsoft's IVp 5.2 and I swear to God that it is a really compelx system and has a very hard to understand database structure.
So, if you face a situation that ends with you having to download data from your SQL server to a text file, this is the place to look in.
What Tools you’ll need.
* A command prompt window
* SQLCMD command line tool
About SQLCMD
Sqlcmd is a client tool which allows you to connect to a SQL Server Database Engine instance and run script against it. You might be familiar with SQL Server Management Studio (SSMS) to run queries, SSMS uses the .NET Sqlclient but SQLCMD will use an oledb provider. If you require furher information about SQLCMD utilty, don't hesitate to visit this link.
Here are some switches and examples of SQLCMD. I did not post an explanation of every single switch used in this command but I am pretty sure that you will be likely use the following ones:
-E Will establish a trusted connection to an instance of SQL Server, this one uses the windows integrated authentication, very usefull if you work have an Active Directory domain in your network and want to ease the task of securing connections to SQL Server
-S This switch indicates the server and instance name to connect, you usually do it in a "servername\instance" fashion
-d Indicates the database name to connect.
-Q Runs a query and then exits SQLCMD, if you do not want to exit just use a lower case q.
-o Redirects the query results to a text file, this switch is the one we are interested on.
These are some examples of the above switches and SQLCMD in action
* SQLCMD -E -S "sqlserver\instancename" -d "dbname" - will connect to a sql server instance using trust connection and use the "dbname" database
* SQLCMD -E -S "sqlserver\instancename" -Q "select * from customer" -d "dbname" - will connect to a SQL Server instance using trust connection, use the "dbname" database, execute a query and then exit.
Finally, this last example will do de trick you want, send the output to a text file, in this example we will run a query, then send it to a text file and exit the utility
* SQLCMD -E -S "sqlserver\instancename" -d "dbname" -Q "select * from customer" - o "c:\textf.txt"
Now you can embed this command into scripts or whatever you think will fit the best, enjoy your new finding and happy coding!
