SQL Syntax error (only when run as scheduled job)

Question

I have just created a scheduled job to run daily, but when the job runs via the scheduler I get the error "Incorrect syntax near 'Item'. [SQLSTATE 42000] (Error 102)".

This only happens when the job is run via the scheduler, and works fine when run directly.

DELETE FROM [NAVQueriesDB].[dbo].[New Items on Order] GO
  SET ANSI_WARNINGS OFF
  GO
 INSERT [NAVQueriesDB].[dbo].[New Items on Order] (
   [Updated Date], 
   [Location Code],
   [Order No_],
   [Item No_],
   [Item Description],
   [Variant Code],
   [Description 2],
   [Outstanding Qty_], 
   [Order Date],
   [Vendor_No],
   [Vendor_Name])

  SELECT  
    GETDATE() AS "Updated Date", 
    [Purch Line].[Location Code],
    [Purch Line].[Document No_],
    [Item].[No_],
    [Item].[Description],
    ISNULL([Purch Line].[Variant Code],'-') AS "Variant Code",
    ISNULL([Variant].[Description 2],'-') AS "Description 2", 
    CAST(SUM([Purch Line].[Outstanding Quantity]) as decimal(18,0)) AS "Outstanding Qty.",
    [Purch Line].[Order Date],
    [Purch Line].[Buy-from Vendor No_],
    [Vendor].[Name]
 FROM [Wings$Item] AS "Item"
  LEFT JOIN [Wings$Purchase Line] AS "Purch Line"
  ON [Item].[No_] = [Purch Line].[No_]

  LEFT JOIN [Wings$Item Variant] AS "Variant"
  ON [Item].[No_] = [Variant].[Item No_] AND [Purch Line].[Variant Code] = [Variant].[Code]

  LEFT JOIN [Wings$Vendor] AS "Vendor"
  ON [Purch Line].[Buy-from Vendor No_] = [Vendor].[No_]

  LEFT JOIN [Wings$Item Ledger Entry] AS "Item Ledger"
  ON [Item].[No_] = [Item Ledger].[Item No_]

  WHERE
    [Item].[No_] > '5618%'
    AND [Item].[No_] <> 'Z999999'
    AND [Item Ledger].[Item No_] IS NULL
 GROUP BY
  [Purch Line].[Location Code],
  [Purch Line].[Document No_], 
  [Item].[No_], 
  [Item].[Description],
  [Purch Line].[Variant Code], 
  [Variant].[Description 2],
  [Purch Line].[Outstanding Quantity], 
  [Purch Line].[Order Date],
  [Purch Line].[Buy-from Vendor No_], 
  [Vendor].[Name]
ORDER BY
  [Location Code],
  [Item].[No_],
  [Item].[Description],
  [Purch Line].[Variant Code]

Could it have something to do with changing the name of the table [Wings$Item] AS "Item" and then referring to it as "Item" the rest of the time in the join statements? Have you try changing your joins to using Wings$Item instead of "Item"? — sokeefe, Oct 31 '17 at 15:08
Do you need to `Use [NavQueriesDB]` before the `select`? The opening bits use fully-qualified table names but the select doesn't. — Hellion, Oct 31 '17 at 15:25
@Hellion I need to set that because I am running this query in one database to populate data in another SQL database. — Joe Cast, Oct 31 '17 at 16:00

score 1 · Accepted Answer · answered Oct 31 '17 at 15:17

It's a bad idea (IMO) to use double quotes for your identifiers. The standard is [] if it has to be quoted (which is also why I avoid spaces in my names so that I don't have to quote them).

SQL Agent defaults to QUOTED_IDENTIFIER being set to OFF. If you change the double quotes to brackets then this should work. Alternatively (but not recommended), you could include the first line of your script to be SET QUOTED_IDENTIFIER ON.

For example, GETDATE() AS "Updated Date"... GETDATE() AS [Updated Date] is better. GETDATE() AS updated_date is best.

Thanks @Tom. Appreciate the response! Setting the quoted identifier to on worked. Will take your advice going forward on using best practice with brackets! — Joe Cast, Oct 31 '17 at 16:47

SQL Syntax error (only when run as scheduled job)

1 Answers1