انتقال داده ها میان Stepهای SQL Server Agent Job

گاهی اوقات، ممکن است یک الزام تجاری وجود داشته باشد که در یک SQL Server Agent Job، بخواهیم یک مقدار پارامتر یا چندین مقدار را از Step 1 به Step 2 منتقل کنیم. به عنوان مثال، در Step 1 یک Job، من یک رکورد را به جدولی با یک ستون  identity وارد می کنم و من می‌خواهم مقدار identitty که به تازگی درج شده است، یعنی ()scope_identity را به Step بعدی آن Job منتقل کنم.

انواع مختلفی از Stepهای SQL Server Agent Job وجود دارد، مانند ActiveX Script، PowerShell script، پکیج های SSIS، اسکریپت T-SQL و غیره، از بین این Job Stepها، احتمالاً سخت ترین آنها نوع T-SQL Script است. T-SQL معمولاً راه آسانی برای نوشتن اطلاعات خارج از محدوده برای Step های بعدی ندارد، در حالی که ActiveX Script، PowerShell وپکیج SSIS می توانند به راحتی یک فایل خارجی یا حتی دسترسی به متغیرهای محیط سیستم عامل برای اهداف تبادل داده ایجاد کنند. .

بنابراین چه راه حلی برای رسیدگی به این مشکل تبادل داده برای استپ های جاب از نوع T-SQL داریم؟

در واقع، یک راه معمول برای انجام این کار وجود دارد، یعنی ایجاد یک جدول دائمی و سپس استفاده از این جدول به عنوان “رابط” یا “پیام‌رسان” برای تبادل داده بین استپ های جاب. این رویکرد اشکالی ندارد، با این حال، ممکن است راه‌های دیگری نیز وجود داشته باشد که انعطاف‌پذیرتر و قوی‌تر با کار کمتر یا همسویی بهتر با الزامات تجاری باشد.

ما این راه حل های بالقوه را به جز استفاده از جدول دائمی بررسی خواهیم کرد.

ما فرض می کنیم که می خواهیم دو پارامتر را ارسال کنیم، یکی پارامتر عدد صحیح، parmInt@ و دیگری یک پارامتر رشته، parmStr@.

برای اهداف آزمایشی، فرض می کنیم که یک Job با دو Step داریم و مقادیر را از Step 1 به Step 2 منتقل می کنیم.

روش 1: انتقال داده ها  از طریق یک Extended Proeprty بین استپهای جاب Agent SQL Server 

یک Etended Proeprty مکان خوبی برای ذخیره مقادیر موقتی است زیرا هدف اولیه آن برای “مستندسازی” اشیاء پایگاه داده است.

برای ساده‌تر کردن کارها، Etended Proeprtyها را به TempDB اضافه می‌کنیم تا داده‌ها را بین استپ ها ارسال کنیم.

بنابراین، در Step 1 جاب، این اسکریپت را داریم:

if exists (select * from tempdb.sys.extended_properties where class=0 and name=’parmInt’)

   ‘exec tempdb.sys.sp_dropextendedproperty @name=’parmInt

if exists (select * from tempdb.sys.extended_properties where class=0 and name=’parmStr’)

   ‘exec tempdb.sys.sp_dropextendedproperty @name=’parmStr

EXEC tempdb.sys.sp_addextendedproperty  

@ ,’name = N’parmStr@  

value = ‘HelloWorld’; —  assuming ‘HelloWorld’ is what we want to pass

EXEC tempdb.sys.sp_addextendedproperty  

@ ,’name = N’parmInt@  

value = ‘123’; — assuming ‘123’ is what we want to pass

در استپ 2 job، می توانیم از اسکریپت زیر برای بازیابی مقادیر استفاده کنیم:

declare @parmInt int, @parmStr varchar(128) 

select @parmInt = cast([value] as int) from tempdb.sys.fn_listextendedproperty(‘parmInt’, null, null, null, null, null, null) 

select @parmStr = cast([value] as varchar(128)) from tempdb.sys.fn_listextendedproperty(‘parmStr’, null, null, null, null, null, null)

*/

 other regular business processing with @parmInt and @parmStr

 you can even drop these Eps after the work is done using sp_dropextendedproperty

/*

روش 2: ارسال مقادیر از طریق Error Log بین استپهای جاب Agent SQL Server

در این روش مقدار پارامتر را در Error log می نویسیم و سپس در استپ بعدی جاب از Log error SQL Server آنرا می خوانیم.

بنابراین در استپ 1، با استفاده از raiserror..with Log  در لاگ می نویسیم. “with log” پیام را در error Log SQL Server خواهد نوشت.

برای عبارت raiserror، اگر level severity را 0 (یا 10) قرار دهیم، step job شکست نمی خورد و در چنین مواردی، raiserror خودش فقط برای نوشتن اطلاعاتی در error log است.

بنابراین، در استپ 1، کد زیر را داریم:

*/

other business logic block

now we want to pass values to next step

/*

‘declare @parmInt int = 1234, @parmStr varchar(128) = ‘hello world

raiserror (‘@parmInt = %d;; @parmStr = %s’, 10, 1, @parmInt, @parmStr) with log

و در error Log SQL Server ما مشاهده میکنیم:

در استپ بعد می توانیم کد زیر را برای بازیابی داده ها بر اساس فرمت داده از استپ 1 اجرا کنیم:

;set nocount on

declare @t table (LogDate datetime, ProcessInfo varchar(100), [text] varchar(300))

 insert into @t (LogDate, ProcessInfo, [Text])

‘exec master.sys.sp_readerrorlog 0, 1, ‘parmInt 

;declare @parmInt int, @parmStr varchar(128) 

select top 1 @parmInt = cast(substring([text], len(‘@parmInt = ‘)+1, charindex(‘;;’, [text])-len(‘@parmInt = ‘)-1) as int), @parmStr = substring([text], charindex(‘@parmstr = ‘, [text])+len(‘@parmStr = ‘)+1, 128)

from @t

order by LogDate desc; — find the most recent data

raiserror(‘@parmInt = %d’, 0, 1, @parmInt) with nowait; — for debug purpose

raiserror(‘@parmStr = %s’, 0, 1, @parmStr) with nowait; — for debug purpose

*/

 the result is exactly as expected@parmInt = 1234@parmStr = hello world

/*

روش 3: ارسال مقدار از طریق ویژگی Job Description بین استپ های جاب Agent SQL Server

ما می دانیم که هر Job Agent SQL Server دارای یک بخش توضیحات است که در زیر مشخص شده است.

ما در واقع می توانیم از این بخش برای انتقال داده ها استفاده کنیم. تنها چیزی که باید بدانیم این است که بسیاری از جاب ها ممکن است قبلاً از این بخش استفاده کنند، بنابراین باید داده ها را به اطلاعات موجود اضافه کنیم و بعد از بازیابی مقادیر پارامتر، آنها را پاکسازی کنیم.

در استپ 1، ما کد زیر را داریم که در آن، از توکن جاب ” (ESCAPE_SQUOTE(JOBNAME))$” استفاده می کنیم.

*/

The regular business codes

 the following is to put parameter values into job description section

/*

 declare @parmInt int, @parmStr varchar(128)

— assume we need to pass the following data to next step

;’select @parmInt = 123, @parmStr = ‘hello world

;declare @desc nvarchar(512)

;declare @crlf char(2) = char(0x0d) + char(0x0a)

select @desc = description from msdb.dbo.sysjobs where name = ‘$(ESCAPE_SQUOTE(JOBNAME))‘

;print @desc

 append the info —

set @desc = @desc + @crlf + ‘@parmInt = ‘ + cast(@parmInt as varchar(20)) + ‘;’ + @crlf

set @desc = @desc + ‘@parmStr = ‘ + @parmStr + ‘;’ + @crlf

 — update the job description with newly appended data

exec msdb.dbo.sp_update_job @job_name = ‘$(ESCAPE_SQUOTE(JOBNAME))‘, @description = @desc

اگر شرح جاب اولیه ما مانند زیر باشد:

پس از اجرای استپ 1 جاب، بخش توضیحات اصلاح شده را به صورت زیر مشاهده خواهیم کرد:

در استپ 2، می توانیم اطلاعات ضمیمه شده را با کد زیر بازیابی کنیم:

;declare @parmInt int, @parmStr varchar(128)

;declare @desc nvarchar(512)

 select @desc = description from msdb.dbo.sysjobs where name = ‘$(ESCAPE_SQUOTE(JOBNAME))’

select @parmInt=cast(substring(@desc, charindex(‘@parmInt’, @desc)+len(‘@parmInt = ‘)+1, charindex(‘;’, @desc, charindex(‘@parmInt’, @desc))-charindex(‘@parmInt’, @desc) – len(‘@parmInt = ‘)-1 ) as int), @parmStr = substring(@desc, charindex(‘@parmStr’, @desc)+len(‘@parmStr = ‘)+1, charindex(‘;’, @desc, charindex(‘@parmStr’, @desc))-charindex(‘@parmStr’, @desc) – len(‘@parmStr = ‘)-1 ) 

raiserror(‘@parmInt = %d’, 0, 1, @parmInt) with nowait; — for debug purpose

raiserror(‘@parmStr = %s’, 0, 1, @parmStr) with nowait; — for debug purpose

*/

  the result is exactly as expected

 parmInt = 123@

parmStr = hello world@

/*

روش 4: انتقال مقدار از طریق فایل خارجی بین استپ های جاب Agnet SQL Server

در این روش از دو فرآیند استفاده خواهیم کرد، یکی استفاده از sqlcmd.exe و گزینه -o آن برای خروجی و تولید فایل در استپ 1 جاب و دیگری استفاده از bulk insert برای وارد کردن فایل تولید شده در استپ 2.

برای اثبات مفهوم، می‌توانیم دستور sqlcmd زیر را در استپ 1 جاب اجرا کنیم، که نوع آن باید Operating System (CmdExec) باشد.

sqlcmd.exe -S localhost\sql2022 -d master -Q “set nocount on; select  parmInt=1234, parmStr=’hello world'” -o c:\temp\dataExch.txt -s 

‘|’

نتیجه تولید یک فایل csv از نوع c:\temp\dataExch.txt است و اگر این فایل متنی را در Notepad باز کنیم، این را خواهیم دید:

می بینیم که فایل از سه خط تشکیل شده است که خط اول برای نام متغیر، خط دوم جداکننده (delimiter) و خط سوم حاوی مقادیر است. ما از ‘|’ (نوار عمودی) به عنوان جداکننده فیلد استفاده می کنیم.

;if object_id(‘tempdb..#t’, ‘U’) is not null   drop table #t

create table #t (col_1 int, col_2 varchar(128))

 set nocount on

BULK INSERT #t

FROM ‘c:\temp\dataExch.txt’    WITH    (      FIELDTERMINATOR =’|’,    ROWTERMINATOR =’\n’ ,  FIRSTROW =3 — so we omit the column header line and the separator line )  

 declare @parmInt int, @parmStr varchar(128) — we will populate these two params

;select top 1 @parmInt = col_1, @parmStr = col_2 from #t

raiserror(‘@parmInt = %d’, 0, 1, @parmInt) with nowait; — for debug purpose

raiserror(‘@parmStr = %s’, 0, 1, @parmStr) with nowait; — for debug purpose

*/

 the result is exactly as expected

     parmInt = 1234@

parmStr = hello world@

/*