0

I am inserting a job step in Azure Elastic job agent. This is my code:

EXEC jobs.sp_add_jobstep @job_name='Sample T-SQL',
@command=N' declare @columns varchar(max)
select @columns = stuff(( select '],[' + [$Name]
                    from dbo.groups
                    order by '],[' + convert(varchar(max), [$_ClosingBalance]) desc
                    for xml path('')), 1, 2, '' + ']'
INSERT INTO dbo.Trial1  
(   [Bank Accounts],[Bank OD A/c],[Branch / Divisions],[Capital Account],[Cash-in-Hand],[Current Assets],[Current Liabilities],[Deposits (Asset)]
      ,[Direct Expenses],[Direct Incomes],[Duties & Taxes],[Fixed Assets],[Duties & Taxes1],[Duties & Taxes2],[Indirect Expenses],[Indirect Incomes],[Investments]
      ,[Loans & Advances (Asset)],[Loans (Liability)],[Misc# Expenses (ASSET)],[Provisions],[Purchase Accounts],[Reserves & Surplus],[Sales Accounts],[Secured Loans]
      ,[Stock-in-Hand],[Stock Transfer Outward],[Sundry Creditors],[Sundry Debtors],[Suspense A/c],[Duties & Taxes3]
      ,[Unsecured Loans]
)  
Select * from (
    Select [$_ClosingBalance], RowN = Row_Number() over (order by @columns) from dbo.Groups
    ) a
    pivot (max([$_ClosingBalance]) for RowN in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32])) p

 

INSERT INTO dbo.Date ([Date]) VALUES (GETDATE())
INSERT INTO dbo.Final
SELECT * 
FROM  dbo.Trial1
INNER JOIN dbo.Date
ON dbo.Trial1.IdCol = dbo.Date.IDCol1'',
@credential_name='JobRun',
@target_group_name='DatabaseGroup5'

I am receiving the following error :

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ']'.
Msg 137, Level 15, State 2, Line 15
Must declare the scalar variable "@columns".
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near ''
  • 2
    I believe your problem is related to the single quotes you're using inside the string for @command. Check this How to include a single quote in a SQL query. – Ronaldo Aug 31 at 15:42
  • 2
    Forget the EXEC and just do DECLARE @command nvarchar(max) = N' declare and SSMS will show you where the problems are. Keep doubling-up single quotes until you get them all. – Aaron Bertrand Aug 31 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.