Incorrect Syntax

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.

: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

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

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

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

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

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

Yep it parses error free. Commands completed successfully.

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

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

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

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