Incorrect Syntax

1

exec usp_ConvertQuery2HTMLTable 'SELECT Orgs.LookupCode, StateProvinces.Name, Orgs.Zip, Users.FirstName, Users.LastName, Orgs.Address1, Orgs.Name, Orgs.City, Orgs.CreateDT, Orgs.LastEditDT
FROM Orgs INNER JOIN StateProvinces ON Orgs.StateProvinceId = StateProvinces.Id) INNER JOIN Users ON Orgs.CreateByUserId = Users.Id
WHERE StateProvinces.Name="unknown"
ORDER BY Orgs.CreateDT'

Incorrect syntax near the keyword 'FROM'.
Any ideas? Should be an easy one.

2

:eyes:

SELECT Orgs.LookupCode, 
                StateProvinces.Name, 
                Orgs.Zip, 
                 Users.FirstName, 
                 Users.LastName, 
                 Orgs.Address1, 
                 Orgs.Name, 
                 Orgs.City, 
                 Orgs.CreateDT, 
                  Orgs.LastEditDT
       FROM Orgs INNER JOIN StateProvinces 
            ON Orgs.StateProvinceId = StateProvinces.Id)     <---------
         JOIN Users ON Orgs.CreateByUserId = Users.Id
     WHERE StateProvinces.Name="unknown"
     ORDER BY Orgs.CreateDT
1 Like
3

That error must be coming from the proc itself, the literal value is find after double single quotes and a not a double quotes.

4

Nice catch. But now I get "Invalid syntax near the keyword FROM"

5

exec usp_ConvertQuery2HTMLTable 'SELECT Orgs.LookupCode, StateProvinces.Name, Orgs.Zip, Users.FirstName, Users.LastName, Orgs.Address1, Orgs.Name, Orgs.City, Orgs.CreateDT, Orgs.LastEditDT
FROM Orgs INNER JOIN StateProvinces ON Orgs.StateProvinceId = StateProvinces.Id INNER JOIN Users ON Orgs.CreateByUserId = Users.Id
WHERE StateProvinces.Name = "unknown"
ORDER BY Orgs.CreateDT'

Even with double quotes I get Incorrect syntax near the keyword 'FROM'.

6

did this call ever work? when did you create this sproc? I think this has more to do with this non SQL related function

7

If you just parse the code and don't exec it, do you still get the error?

8

Yep it parses error free. Commands completed successfully.

9

@ScottPletcher I'm still getting Invalid syntax near keyword From. Any ideas?

exec usp_ConvertQuery2HTMLTable 'SELECT Orgs.LookupCode, StateProvinces.Name, Orgs.Zip, Users.FirstName, Users.LastName, Orgs.Address1, Orgs.Name, Orgs.City, Orgs.CreateDT, Orgs.LastEditDT
FROM Orgs INNER JOIN StateProvinces ON Orgs.StateProvinceId = StateProvinces.Id INNER JOIN Users ON Orgs.CreateByUserId = Users.Id
WHERE StateProvinces.Name = "unknown"
ORDER BY Orgs.CreateDT'

10

decompose that usp_ConvertQuery2HTMLTable proc and look inside of it.
add the following to it and see what it produces from within ssms

PRINT @DynTSQL
EXEC (@DynTSQL)
1 Like
11

Well, that's interesting.
In the sp code I added this
Set @SQLQuery = 'SELECT FirstName,LastName, TMSVersion, FreightBillRecdDate, SUM (CountOfBills)
FROM [dbo].[ProductionResultsCurrent]
GROUP BY FirstName, LastName, TMSVersion, FreightBillRecdDate
ORDER BY FirstName;'

Ran it and the PRINT stmt gives this: Look at the SELECT its missing the columns I'm selecting

SELECT (SELECT FROM [dbo].[ProductionResultsCurrent]
GROUP BY FirstName, LastName, TMSVersion, FreightBillRecdDate
ORDER BY FirstName; FOR XML RAW ('TR'), ELEMENTS, TYPE) AS 'TBODY' FOR XML PATH (''), ROOT ('TABLE')

12

Look at the insides of usp_ConvertQuery2HTMLTable and see why. Make sure you cross you Ts and dot your Is

You cant just plug in any sql code without fully vetting it and doing a personal code review and expect it to work.

Why do you think there is a ; before the FOR Xml?

Once you change that proc, you own it