时间戳

首页 > 解决方案 > Azure SQL 数据库中的 SQL 作业中的语法错误

sql - Azure SQL 数据库中的 SQL 作业中的语法错误

问题描述

我在 Azure Elastic 作业代理中插入了一个作业步骤。这是我的代码：

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'

我收到以下错误：

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 ''

我的代码在不应该的地方结束。请帮忙。

标签： sqlsql-serverazureazure-sql-databasejobs

解决方案

之后你分配了@command，你可以写：打印@command然后你可以查看打印的脚本，复制它并粘贴到查询窗口中。然后你会明白你的错误。

推荐阅读