January 8, 2009 at 4:53 pm
Hi Guys,
I have a query and which gives some results and I want to automate this query using sql job(to run everyday) and I want the results of the query to be placed in a txt file on the server.
The job should run every day and the result file should be different everytime the job executed.
Please help me to do this..
Thanks
January 8, 2009 at 5:53 pm
Check BOL for bcp(make use of queryout parameter).
MJ
January 8, 2009 at 8:17 pm
You can also put the select statement in a Transact-SQL job step, and add an output file on the Advanced settings for the step and it will write the data to the output file.
USE [msdb]
GO
/****** Object: Job [test] Script Date: 01/08/2009 22:16:05 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 01/08/2009 22:16:05 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'test',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [test] Script Date: 01/08/2009 22:16:06 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'test',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'select * from sys.objects',
@database_name=N'master',
@output_file_name=N'c:\test_outputfile.txt',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
January 8, 2009 at 10:24 pm
kiransuram19 (1/8/2009)
Hi Guys,I have a query and which gives some results and I want to automate this query using sql job(to run everyday) and I want the results of the query to be placed in a txt file on the server.
The job should run every day and the result file should be different everytime the job executed.
Please help me to do this..
Thanks
Use BCP command or Echo command to export result to file
January 9, 2009 at 2:46 pm
In the job you mentionted output file as c:\test_outputfile.txt..,and job will run successfully for the first time only ...after that job(2nd attempt) will fail with error.. file already exists ...
I want the job to create a file with new name everytime it exectes..
Thanks
January 9, 2009 at 3:12 pm
/* Add the following scripts into your job step*/
DECLARE
@id char(8),
@filename varchar(50),
@cmd varchar(2000)
SET @id=convert(char(8),getdate(),112)
SET @filename='My FILEPATH Myfilename_'+@id+'.txt'
SET @cmd= 'bcp "MY QUERY" queryout '+@filename+ '-T -c'
--print @cmd
EXEC xp_cmdshell @cmd
GO
January 9, 2009 at 5:33 pm
kiransuram19 (1/9/2009)
In the job you mentionted output file as c:\test_outputfile.txt..,and job will run successfully for the first time only ...after that job(2nd attempt) will fail with error.. file already exists ...
I want the job to create a file with new name everytime it exectes..
Thanks
The output filename is stored in msdb.dbo.sysjobsteps. You can add a first step to the job that updates the output filename for the second step by updating the second steps row in sysjobsteps and achieve that functionality.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
January 9, 2009 at 9:09 pm
Thankyou verymuch
January 9, 2009 at 9:52 pm
kiransuram19 (1/9/2009)
In the job you mentionted output file as c:\test_outputfile.txt..,and job will run successfully for the first time only ...after that job(2nd attempt) will fail with error.. file already exists ...
I want the job to create a file with new name everytime it exectes..
Thanks
U can create new file every time or u can append existing file..
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply