Solved

How to Validate SQL Syntax Before Execution

Posted on 2004-09-25
18
1,409 Views
Last Modified: 2008-01-09
I have a table of data that contains many different "where clauses".  I run those "where clauses" in a DTS package each night.  The problem is that for some reason a few of the "where clauses" are corrupt and causing my step in the DTS to fail when it tries to execute that statement after it has been dynamically built.  My question is "how do I validate the SQL syntax in the statement before the execution.  I would like to pass a string (sql statement) to some object and get back some type of "valid/not valid" check before I actually execute each statement.  I found something that looks close, but I am not using it properly.
=====
objDatabase.ExecuteImmediate(Script, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_ParseOnly, Script.Length)
=====

For testing purpose lets just say I should be able to send it one of two strings:
  SELECT TOP 100 FROM tblUsers where LName = ('anderson')
    (valid)
  SELECT TOP 100 FROM tblUsers where LName = (anderson)
    (not valid)

Any help would be greatly appreciated.

Thank you,
  Chad
0
Comment
Question by:a_d_m_i_n
  • 5
  • 5
  • 4
  • +1
18 Comments
 
LVL 7

Expert Comment

by:ala_frosty
ID: 12153851
Say, why don't you just post the sp's that are building the 'corrupt' SQL Strings and we'll see about fixing them. It might be easier. I'm not familiar with any 'user' accessible parser under SQL. You're probably trying to do an implicit conversion and it's not liking it. When you post your code that's building the queries, be sure to give us the table schema.
0
 

Author Comment

by:a_d_m_i_n
ID: 12155102
well....its not that easy.... we have web pages that post the where clauses based on options the user choices.  When I see the bad "where clauses" I easily recognize they are bad and why they are bad.  The problem is that there currently are 21,000 users and they have all put in some criteria.  Some of those WHERE clauses got posted to the DB bad and when the DTS comes across one of those bad where clauses it chokes.  I would rather just be able to cofirm the SQL before I execute it and only execute it if the SQL is valid.  If the SQL that is not valid I will simply log it to a file and investigate why it is happening.

Thanks for your help.

-Chad
0
 
LVL 7

Expert Comment

by:ala_frosty
ID: 12156601
I can see you have some work ahead of you. :-)
0
 
LVL 15

Expert Comment

by:justinbillig
ID: 12162012
you going to have to fix the web code to make the SQL queries work.
0
 

Author Comment

by:a_d_m_i_n
ID: 12162908
The problem is the "web code" is extremely complex because the WHERE clause comes from many different options the user selects. All I'm basically trying to accomplish is the same thing the "check mark" does in Query Analyzer.  I would think MS just has that speed button make some type of stored function and assigns a button to that action.  There must be some type of SQL Server call I can do.

In the mean time, I'm just going to add some "on error resume next" / "on error skip record and report to file" type action, but I would rather not turn on the "on error resume next" because then the ActiveX script in my DTS could skip over something else that I don't want it to.

The best solution would be to figure out what function call MS is doing in Query Analyzer or find some other function that can validate SQL syntax with out executing it.

Of course once I figure out which set of logic is causing these bad "where clauses" we will fix them as find them, but it's way to hard to just dig into all the scripts (over 100 different scripts) that generate the "where clauses" for users on the site.  I'm sure it's only one or two scripts that are causing this, but it's a needle in a hay stack search to find.  It would be so much simpler to just find the bad records and find the common where clause.

Thanks,
  Chad
0
 
LVL 34

Expert Comment

by:arbert
ID: 12163101
Well, it's not totally foolproof, but this might work for you:

SET FMTONLY ON
go
your query here.....
SET FMTONLY OFF


It doesn't actually execute the query, but it does parse it....There use to be a DLL floating around called SQLPARSE.DLL.  I can't really find it or see where it's supported any more though.

Brett
0
 
LVL 15

Expert Comment

by:justinbillig
ID: 12180714
what you could do is run some scenarios on your webpage and instead of submitting the sql to the database, write the SQL to a file, run those queries then find out what is causing the errors ... the only real ( and best ) way to do it is to fix the web code
0
 
LVL 15

Accepted Solution

by:
justinbillig earned 100 total points
ID: 12180767
you might be able to run it in a stored procedure


create procedure uspRunQuery
(
          strQuery                varchar( 1000 )
)
AS
set nocount on

-- Run the query
EXECUTE ( strQuery )

-- If that query caused an error just return the string Error
if @@ERROR <> 0 THEN SELECT "ERROR"


You could then modify your ASP cod e to email you when the recordset / whatever returns  the string "ERROR" with any pertanant information ( Date, Time, SQL String, User, Page, Function) and then fix the problem. So each time you get an error you know exactly where to look
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:a_d_m_i_n
ID: 12180846
Thanks - I will try that later today. I was wondering how I was going to run that logic in my ActiveX Script.  I think your Stored Procedure method may work.  I will try it after my afternoon meeting.

-Chad
0
 
LVL 34

Expert Comment

by:arbert
ID: 12181025
So what exactly are you looking for--I thought you wanted to validate the SQL without running it?
0
 
LVL 15

Expert Comment

by:justinbillig
ID: 12181247
the SP i wrote will run the query, but it will tell you if you have an error, then he can take the appropriate measures
0
 
LVL 34

Expert Comment

by:arbert
ID: 12181963
"the SP i wrote will run the query, but it will tell you if you have an error, then he can take the appropriate measures "

I realize that--that's why I asked the question.  The original question asked how to validate SQL  BEFORE execution....
0
 
LVL 15

Expert Comment

by:justinbillig
ID: 12182797
yes i realize that but there really isnt a way, that will stop the query from interuppting DTS
0
 
LVL 34

Expert Comment

by:arbert
ID: 12183120
Sure there is

set FMTONLY ON
0
 

Author Comment

by:a_d_m_i_n
ID: 12453411
Sorry I took so long... I had a new project jump up and had to drop this issue and finish the new thing.  I was able to create a SP with the code above and basicly find out if there was a error or not in the statement.  Thank you everyone for your help.

-Chad
0
 
LVL 34

Expert Comment

by:arbert
ID: 12457841
For anyone browsing in the future, the accepted answer didn't answer the question on how to validate BEFORE execution...
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now