November 1, 2012 at 12:49 pm
Does anyone know if it is possible to call SQL Server's Statement parser (syntax checking) via T-SQL?
I'm looking to see if it's possible to validate a field that contains SQL code programmatically.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 1, 2012 at 1:51 pm
I've played with this a couple of times, Jason; i've used it to chop a SQL statement into elements, so i could play with building my own formatter, and of course basic error checking without talking to a server.
here's a vb.net example.
'adding reference Imports Microsoft.SqlServer.Management.Sdk.Sfc
Private Sub btnSQLParse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSQLParse.Click
Dim sql As String
Dim script As Microsoft.SqlServer.SqlParser.SqlCodeDom.SqlScript
sql = " SELECT * FROM FROM sys.objects"
'a shared public function, no need to construct an object, so no object.parse method is used.
script = Microsoft.SqlServer.SqlParser.Parser.Parser.Parse(sql)
For Each s As Microsoft.SqlServer.SqlParser.Parser.Error In script.Errors
Debug.Print(s.Message)
Next
End Sub
and the errors i got from this malformed statement, without needing a database/connection or anything else:
Incorrect syntax near 'FROM'.
Incorrect syntax near '.'.
Lowell
November 1, 2012 at 1:54 pm
Lowell (11/1/2012)
I've played with this a couple of times, Jason; i've used it to chop a SQL statement into elements, so i could play with building my own formatter, and of course basic error checking without talking to a server.here's a vb.net example.
'adding reference Imports Microsoft.SqlServer.Management.Sdk.Sfc
Private Sub btnSQLParse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSQLParse.Click
Dim sql As String
Dim script As Microsoft.SqlServer.SqlParser.SqlCodeDom.SqlScript
sql = " SELECT * FROM FROM sys.objects"
'a shared public function, no need to construct an object, so no object.parse method is used.
script = Microsoft.SqlServer.SqlParser.Parser.Parser.Parse(sql)
For Each s As Microsoft.SqlServer.SqlParser.Parser.Error In script.Errors
Debug.Print(s.Message)
Next
End Sub
and the errors i got from this malformed statement, without needing a database/connection or anything else:
Incorrect syntax near 'FROM'.
Incorrect syntax near '.'.
That's pretty darned close. :w00t:
I guess I could wrap this up and use CLR, although I'm not sure if I'll be able to do this in client DB's which is why I was looking for a straight SQL approach.
Also, your output leads me to believe that it has issues with schema? Why the error on the '.' ?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 1, 2012 at 2:09 pm
SELECT * FROM FROM sys.objects
, yeah the parse assumes the second FROM is a table name, and so the alias 'sys.tables' is an error.
a longer, better error message comes from this:
Debug.Print(IIf(s.IsWarning, "Warning: ", "Error:") & "Line Number: " & s.Start.LineNumber.ToString & " ColumnNumber:" & s.Start.ColumnNumber & " Error:" & s.Message)
Error:Line Number: 1 ColumnNumber:16 Error:Incorrect syntax near 'FROM'.
Error:Line Number: 1 ColumnNumber:24 Error:Incorrect syntax near '.'.
Lowell
November 1, 2012 at 4:11 pm
Ha Ha...I need to sloooooow down. I totally missed the two FROM's
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 1, 2012 at 7:07 pm
A pure SQL solution, using Lowell's example might be like this:
DECLARE @SQL VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
PRINT ERROR_NUMBER()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
PRINT ERROR_PROCEDURE()
PRINT ERROR_LINE()
PRINT ERROR_MESSAGE()
END CATCH
But:
1. I would only use it for SELECTs (not INSERT, UPDATE or DELETE) for obvious reasons.
2. Long running SELECTs will be problematic.
3. Results from the SELECT (if it runs) are of course returned but its probably they can be ignored.
In the CATCH, the nice feature is that you can return the exact syntax failure message to the client.
I find this to be an interesting question so I will look around further to see if SQL has a callable syntax parser. My initial search yielded nothing.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 1, 2012 at 7:31 pm
Still haven't found the definitive answer but I did think of this approach.
It might work a little better and could conceivably be used with INSERTs, UPDATEs and DELETEs!
DECLARE @SQL VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'
BEGIN TRY
EXEC ('CREATE PROCEDURE DUMMY WITH RECOMPILE AS BEGIN; ' + @SQL + ';END');
DROP PROCEDURE DUMMY;
END TRY
BEGIN CATCH
PRINT ERROR_NUMBER();
PRINT ERROR_SEVERITY();
PRINT ERROR_STATE();
PRINT ERROR_PROCEDURE();
PRINT ERROR_LINE();
PRINT ERROR_MESSAGE();
END CATCH;
Basically you're using your SQL to create a stored procedure and only the CREATE of the stored procedure is executed, which syntax checks your SQL. The CATCH executes if the dynamic SQL won't compile. It should probably be executed within the database context and the logged in user would need to have permissions granted to create a SP.
While it would not catch issues where a referenced table is the wrong name or something due to deferred name resolution when you create the SP, it would catch cases where a column name is wrong and the table already exists.
Of course, you may want to use a more definitive name for the SP (instead of DUMMY). Something with a timestamp perhaps (but then the DROP would also need to be dynamic SQL).
Edit: The RECOMPILE causes SQL Server to not cache an execution plan for the SP. I was hoping there might be a way to turn off deferred name resolution on CREATE but apparently it was not to be.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 1, 2012 at 8:17 pm
Good idea dwain, but I do not want to/ can not execute the statements.
I need only to check the syntax.
Also, the syntax can be any valid sql. So if the input was a "create procedure" statement, yours would fail would fail.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 1, 2012 at 8:58 pm
Jason Selburg (11/1/2012)
Good idea dwain, but I do not want to/ can not execute the statements.I need only to check the syntax.
Also, the syntax can be any valid sql. So if the input was a "create procedure" statement, yours would fail would fail.
Maybe load the query into a variable, SET SHOWPLAN_XML ON, and then EXEC the variable dumping the result set to yet another variable???
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2012 at 9:03 pm
Jeff Moden (11/1/2012)
Jason Selburg (11/1/2012)
Good idea dwain, but I do not want to/ can not execute the statements.I need only to check the syntax.
Also, the syntax can be any valid sql. So if the input was a "create procedure" statement, yours would fail would fail.
Maybe load the query into a variable, SET SHOWPLAN_XML ON, and then EXEC the variable dumping the result set to yet another variable???
I tried something like that but it seems to mask the error.
SET SHOWPLAN_XML ON
GO
DECLARE @SQL VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
PRINT ERROR_NUMBER()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
PRINT ERROR_PROCEDURE()
PRINT ERROR_LINE()
PRINT ERROR_MESSAGE()
END CATCH
GO
SET SHOWPLAN_XML OFF
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 2, 2012 at 6:57 am
Jeff Moden (11/1/2012)
Jason Selburg (11/1/2012)
Good idea dwain, but I do not want to/ can not execute the statements.I need only to check the syntax.
Also, the syntax can be any valid sql. So if the input was a "create procedure" statement, yours would fail would fail.
Maybe load the query into a variable, SET SHOWPLAN_XML ON, and then EXEC the variable dumping the result set to yet another variable???
Respectfully, EXEC is not an option, even if it were wrapped in a transaction and rolled back. I was hoping there was an undocumented feature or cleaver work around. :crying:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 2, 2012 at 7:18 am
Not even with SET NOEXEC ON?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2012 at 7:46 am
GilaMonster (11/2/2012)
Not even with SET NOEXEC ON?
That might be it.
So how would I (or is it possible) to do this inside a procedure and capture the output.
In other words, I have a table with supposedly valid SQL inside and I need to valid each instnace.
CREATE TABLE dbo.testData
(nDex int IDENTITY
,sqlText nvarchar(MAX)
,isValid BIT DEFAULT(0))
GO
-------------------------------------------
INSERT dbo.testData (sqlText)
VALUES
('SELECT * FROM sys.databases')
,('SELECT * FROM FROM sys.databases')
,('CREATE TABLE dbo.test (a int, b int)
GO
EXEC dbo.someProc @p1=''xyz''')
,('Gobly gook')
GO
-------------------------------------------
I've tried ...
CREATE PROCEDURE dbo.isValid
(@nDex INT)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = sqlText FROM dbo.testData WHERE nDex = @nDex
BEGIN TRY
SET NOEXEC ON;
PRINT @sql
EXEC (@sql)
SET NOEXEC OFF;
UPDATE dbo.testData SET isValid = 1 WHERE nDex = @nDex
END TRY
BEGIN CATCH
UPDATE dbo.testData SET isValid = 0 WHERE nDex = @nDex
END CATCH
END
GO
Thoughts?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 2, 2012 at 8:40 am
Try this:
DECLARE @SQL VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'
SET @SQL = 'SET NOEXEC ON;
' + @SQL + '
SET NOEXEC OFF;'
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
PRINT 'Error here: ' + ERROR_MESSAGE()
END CATCH
SET NOEXEC OFF;
November 2, 2012 at 9:11 am
Eugene Elutin (11/2/2012)
Try this:
DECLARE @SQL VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'
SET @SQL = 'SET NOEXEC ON;
' + @SQL + '
SET NOEXEC OFF;'
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
PRINT 'Error here: ' + ERROR_MESSAGE()
END CATCH
SET NOEXEC OFF;
Almost there!!!!!
It seems multi-line statements/batches seem to have problems and are returned as invalid. :hehe:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply