Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
amgrace
Starting Member
30 Posts |
 Posted - 2004-07-20 : 05:08:28
|
| Hello,Can anyone please tell me how to specify in my SQL query (as part of the script) that the output should be saved as a file in a particular directory.i.e (run the following query and save the output as a txt.file in this directory)Thanks very much in advancePLEASE HELP.........AM GETTING DESPERATE. |
|
|
amgrace
Starting Member
30 Posts |
Posted - 2004-07-20 : 05:12:26
|
| something like (not the right syntax, but just to give you an idea of what I am trying to achieve:Select * from mytable [and save it in c:\sql_files] |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-20 : 10:57:39
|
| If you are doing this in Query Analyser then press Control-Shift-FOr use Tools : Options : Results (which lets you, additionally, choose output format and so on. [Best to set max. column width to 8000 whilst you are there)Alternatively you could use DTS to "Export" to a file. This would be useful for one-time and scheduled output. For the later you could also use BCP (Bulk Copy Program) which is a command-prompt based application.If you want to do this from something other than Query Analyser please tell us what it is, and whether this is a one-off or something that needs to be scheduled.Kristen |
|
|
|
amgrace
Starting Member
30 Posts |
Posted - 2004-07-20 : 11:48:52
|
| Thanks very much for replying. Unfortunately, i am not doing it in Query Analyser.It needs to be written as part of the SQL script (it's a stored prodedure that displays output when ran). So, I want this output to be saved in a file in a particular directory (like a log file)...instead of displaying it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-20 : 12:27:36
|
| Ah, so a Stored Procedure is called from somewhere, and it should output something to a text file?You could do this with xp_cmdshell - don't know how (in)effecient it is, and xp_cmdshell is "locked down" on many systems.DECLARE @strSQL varchar(8000)SELECT @strSQL = 'ECHO ' + @SomeText + ' >>C:\SomePath\MyFile.TXT'EXEC xp_cmdshell(@strSQL)but my preference would be to have the Stored Procedure "log" the information to a database table, and then export that table periodically, or provide some enquiry tool for the users to view it.If that doesn't solve the problem it might help to better understand the type of logging information you are storing, and how access to it is needed (by users? or other processes?)Kristen |
|
|
|
|
|
|
|
|
PLS HELP! How do I specify a txtoutput file in SQL