How to programatically check SQL syntax?

Guest · Jun 1, 2004

Hi

I'd appreciate any help on providing a direction on how to programatically check SQL syntax against scripts collected from a database object (e.g., Table, View, Stored Procedure, Function, Trigger, Alert or Job) or written by a user

I have tried the following but it doesn't work due to the nature of SQL DMO batch processing

SQLDMO.SQLServer objServer = new SQLDMO.SQLServer();
objServer.Connect(ServerName, UserName, Password);

SQLDMO.Database objDatabase=(SQLDMO.Database)objServer.Databases.ItemByID(DBID)

// Script is a parameter containing Script from a View or Stored Procedure or Function or Table ...see belo
/
SET QUOTED_IDENTIFIER OFF
G
SET ANSI_NULLS OFF
G

CREATE VIEW VIEWNAME A

SELEC
SY50.DATE1
SY50.TIME1
SY50.USERID
SY14.USERNAME
SY50.SECDESC
SY50.INQYTYP
FRO
DATABASENAME..SY05000 SY50 (NOLOCK
INNER JOIN SY01400 SY14 (NOLOCK) ON (SY14.USERID = SY50.USERID AND SY14.RELID=0
WHER
DATEDIFF (DAY, DATE1, GETDATE()) <= 14 AN
( INQYTYPE = 2 O
INQYTYPE = 4 O
INQYTYPE = 7 O
INQYTYPE = 8 O
INQYTYPE = 9 O
INQYTYPE = 18 O
INQYTYPE = 19 O
INQYTYPE = 20 OR
INQYTYPE = 15

G
SET QUOTED_IDENTIFIER OFF
G
SET ANSI_NULLS ON
G
*

string strCmd= @"SET PARSEONLY ON\r\nGO\r\n" + Script + @"\r\nSET PARSEONLY OFF\r\nGO\r\n"
SQLDMO.QueryResults qrs=objDatabase.ExecuteWithResultsAndMessages(strCmd, strCmd.Length,out strMsg)

I got the following run-time error

Additional information: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '\'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 4: Incorrect syntax near 'GO'
[Microsoft][ODBC SQL Server Driver][SQL Server]'CREATE VIEW' must be the first statement in a query batch
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 31: Incorrect syntax near 'GO'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 33: Incorrect syntax near 'GO'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 35: Incorrect syntax near 'GO'

Thanks!

Nick Holmes · Jun 1, 2004

Take the "GO"s out.

Nick Holmes.

Tim said:
Hi!

I'd appreciate any help on providing a direction on how to programatically

check SQL syntax against scripts collected from a database object (e.g.,
Table, View, Stored Procedure, Function, Trigger, Alert or Job) or written
by a user.

I have tried the following but it doesn't work due to the nature of SQL DMO batch processing.

SQLDMO.SQLServer objServer = new SQLDMO.SQLServer();
objServer.Connect(ServerName, UserName, Password);

SQLDMO.Database objDatabase=(SQLDMO.Database)objServer.Databases.ItemByID(DBID);

// Script is a parameter containing Script from a View or Stored Procedure

or Function or Table ...see below

/*
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE VIEW VIEWNAME AS

SELECT
SY50.DATE1,
SY50.TIME1,
SY50.USERID,
SY14.USERNAME,
SY50.SECDESC,
SY50.INQYTYPE
FROM
DATABASENAME..SY05000 SY50 (NOLOCK)
INNER JOIN SY01400 SY14 (NOLOCK) ON (SY14.USERID = SY50.USERID AND SY14.RELID=0)
WHERE
DATEDIFF (DAY, DATE1, GETDATE()) <= 14 AND
( INQYTYPE = 2 OR
INQYTYPE = 4 OR
INQYTYPE = 7 OR
INQYTYPE = 8 OR
INQYTYPE = 9 OR
INQYTYPE = 18 OR
INQYTYPE = 19 OR
INQYTYPE = 20 OR
INQYTYPE = 15
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
*/

string strCmd= @"SET PARSEONLY ON\r\nGO\r\n" + Script + @"\r\nSET PARSEONLY OFF\r\nGO\r\n";
SQLDMO.QueryResults qrs=objDatabase.ExecuteWithResultsAndMessages(strCmd, strCmd.Length,out strMsg);

I got the following run-time error.

Additional information: [Microsoft][ODBC SQL Server Driver][SQL

Server]Line 1: Incorrect syntax near '\'.

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 4: Incorrect syntax near 'GO'.
[Microsoft][ODBC SQL Server Driver][SQL Server]'CREATE VIEW' must be the

first statement in a query batch.

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 31: Incorrect syntax near 'GO'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 33: Incorrect syntax near 'GO'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 35: Incorrect syntax near 'GO'.

Thanks!

Jerry Pisk · Jun 2, 2004

When you use @"" string literal you can't escape characters with \, so \r
will be a slash character followed by r, not a CR character. Either remove
the @ to make those regular, escapable character literals or remove the new
lines, it's just whitespace.

Jerry