| Home |
| Articles |
| Backup |
| Books |
| Certification |
| FAQ |
| Products |
| Replication |
| Scripts |
| Seminars |
| Training |
| TSQL |
|
| MSDN Fourms |
| Philippine SSUG |
| Fort Worth SSUG |
| Oklahoma City SSDG |
|
| Resume |
|
| MHS Enterprises |
| BlowFrog Software |
| FilAm Software |
| AcrylicAcetate.com |
| Bargain Humidors |
| Western Humidor |
|
|
How can I read/write to a flat
file from inside a SQL Server TSQL script/stored-procedure/trigger?
SQL Server doesn't have a handy SPOOL command like Oracle does for writing to files, but
there are a number of ways of doing what you want.
- For reading, use xp_cmdshell and the ECHO command. Use the > or >> redirection
symbols to either create or append to a file.
xp_cmdshell "@ECHO test message >> C:\file.fil"
- Put the information you want into a table (note this can't be an ordinary temporary
table, but it can be a global temporary table) and then bcp it out to a file via
xp_cmdshell.
xp_cmdshell "bcp <dbname>..<tablename> out c:\file.fil
-Usa -P<password> -c"
- BCP or BULK INSERT (SQL 7 only) can also be used to read in a flat file into a table,
from where it can be processed.
- Write your own extended stored procedure. As this is a C program it can use standard
file access commands to achieve whatever you want.
- Run the select through ISQL via xp_cmdshell and use the -o parameter to output the
results to a file. This example uses the -E parameter to avoild hard-coding a userid.
declare @str varchar(255)
select @str = 'isql -Q"select * from <tablename>" -E
-oc:\file.fil'
exec master..xp_cmdshell @str
- There is a free XP - xp_query_to_file - at www.dbmaint.com
|
