SQL Server: BCP automation using job

Article
1/17/2024

https://msdnshared.blob.core.windows.net/media/2016/08/7827.NinjaAwardTinyBronze.png Award in March 2019

Introduction

Many times customer requirement to send table output in specific folder.This data used for presentation or send other vendor.This is possible by SSIS and BCP.

BCP help

Example of BCP:

BCP.exe tst.dbo.testrel out tst.bcp -c -t -T -S .

Main code

USE [msdb]
GO
 
/****** Object:  Job [bcp1]    Script Date: 2/26/2016 12:59:54 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** ******/
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'dbaadmin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO  QuitWithRollback
/****** Object:  Step [step1]    Script Date: 2/26/2016 12:59:55 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step1', 
        @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'DECLARE @filename NVARCHAR(4000) =''C:\tmp\excelexport''+Convert(varchar(8),getdate(),112)+''.csv''
DECLARE @cmd1 NVARCHAR(4000) 
set @cmd1 = ''BCP [ADB].[dbo].[monitoring]  OUT  ''+ @filename + '' -c -T -t, -S''+ @@servername
exec master..xp_cmdshell @cmd1
  ', 
        @database_name=N'master', 
        @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

References

Forum: Automation

Share via