August 23, 2004 at 11:41 am
Calling All Experts:
I need to be able to write output from T-SQL code to a text file on the system. I know how to do this from a VB or Java script file using the scripting object but have not yet been able to do the same from T-SQL. I would like to write some T-SQL that I can use in SQL Server Agent as a scheduled job that would create or open a file and either write to or append to the same file.
Does anyone know if this is possible? I know I can set the job properties to output to a file but it puts a lot of characters and information in the file that I do not want. I also know that I could use DTS but was hoping to do this right from T-SQL code.
My idea is to create a cursor in the T-SQL code and loop through this cursor writing each fetch to the text file as a new line.
Any and all professional responses are appreciated.
Thanks in advance.
Thomas
August 23, 2004 at 1:16 pm
Try using the osql or isql command with the -o (that's a lower case o.) This allows you to execute SQL and place the output in the file specified by the -o option. I'm not sure if this will put those unwanted "special" characters in the output to but it's worth looking into.
August 23, 2004 at 4:02 pm
Another possibility is to use Data Transformation Services (DTS), which is part of SQL Server Enterprise Manager. Theses jobs can be scheduled from within DTS.
Michael
August 23, 2004 at 5:15 pm
And ... as I've just remembered, there's another way which is much more friendly. Edit the SQL Server Agent job. Edit the job step. Under "Advanced" there's the option to send the output to file.
August 23, 2004 at 11:11 pm
If instead of using a cursor to fetch into a text file, how about putting the result set to a global temp table and use xp_cmdshell to execute BCP to query from that temp table and send the output into a text file?
August 23, 2004 at 11:14 pm
Additionally, you can also use xp_cmdshell from within transact-sql if you want to use osql, as Scott earlier suggested, instead of bcp.
August 24, 2004 at 5:23 am
My favourite way is to use osql within a batch file.
You then call the batch file from a job and write the execution log to another text file.
I use this for overnight jobs. I can then get junior staff who do not know sql to check the text file that contains the execution logs to make sure everything ran OK.
Works realy well for my environment.
August 24, 2004 at 11:57 am
I don't remember the source of this code, i found some wehre and modified accroding to my requirments.
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fsObject int
DECLARE @tfObject int
DECLARE @fname VARCHAR(30)
<--Declare to hold your data,@var1, @var2,@var3>
EXEC @hr = sp_OACreate "Scripting.FileSystemObject", @fsObject OUTPUT
if @hr <> 0 EXEC sp_displayoaerrorinfo @fsObject, @hr
SET @fname = '<your file path>'
EXEC @hr = sp_OAMethod @fsObject, 'CreateTextFile' , @tfObject OUTPUT ,@fname
if @hr <> 0 EXEC sp_displayoaerrorinfo @fsObject, @hr
select @tfObject
DECLARE sn_cursor CURSOR FOR <Your SQL statement>
OPEN sn_cursor
FETCH FROM sn_cursor INTO <YOUR variables>
WHILE @@fetch_status = 0
BEGIN
EXEC @hr = sp_OAMethod @tfObject , 'WriteLine' , NULL, @var1
EXEC @hr = sp_OAMethod @tfObject , 'WriteLine' , NULL, ''
EXEC @hr = sp_OAMethod @tfObject , 'WriteLine' , NULL, @var2
EXEC @hr = sp_OAMethod @tfObject , 'WriteLine' , NULL, ''
EXEC @hr = sp_OAMethod @tfObject , 'WriteLine' , NULL, @var3
'-------------------------------------------------------------------------------------'
FETCH NEXT FROM sn_cursor INTO <YOUR variables>
END
CLOSE sn_cursor
DEALLOCATE sn_cursor
Hope it helps you.
August 31, 2004 at 3:48 pm
Exactly what do you want to output to the text file and what do you want the text file for?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply