Disable SQLCMD Syntax Check

Question

We sometimes write dataport scripts that rename tables or columns and have other complex logic. We also have SQL in IF statements referencing those old columns or tables. We have a standard to use IF statements to make our SQL Scripts multi run friendly.

However occasionally, even though the if statement evaluates to false, the code wrapped in the block errors out, I'm guessing because of a syntax check.

if 1 = 0
begin

   select * from mydatabase.dbo.mytable; -- doesn't execute, but still errors because doesn't exist

end

Another weird thing is it isn't always consistent, sometimes it works fine.

Does anybody know if a way I can disable these sort of checks on a script by script basis.

Thanks

NOTE

I know people are going to say they tried a similar situation and it doesn't error. At first for me it didn't error in SSMS but it errored in SQLCMD.

sqlcmd MyTestScript.sql -r 1 -U user -P password

Then I put SSMS in SQLCMD mode Query -> CMDMODE and it still didn't give the error. Then after rerunning a few times it started to error in both.

Answer 1

I think execute_sql built in stored procedure can work around this.

Answer 2

Run the following code in your dev environment

DROP Procedure dbo.#TestProc
GO
CREATE Procedure dbo.#TestProc
AS

IF 1=0
BEGIN
 SELECT 1 FROM XXXXX
END
ELSE
BEGIN
 SELECT * 
 FROM Information_Schema.Tables 
 WHERE Table_Name = 'XXXXX'
END

GO
EXEC #TestProc

It compiles. It executes. It does not error out during either compilation or execution.

I am using SQL 2005.

As you can see from the results, there is no table called XXXXX.

I think your conditions are actually being met and the code is executing.

---

Edt

I went further than what I typed:

The stored proc gets created and runs for each one of the following

SELECT * FROM XXXXX.XXXXX /* owner XXXXX */
SELECT * FROM XXXXX.XXXXX.XXXXX /* database XXXXX */

The code bombs out when I fully qualify the name

SELECT * FROM XXXXX.XXXXX.XXXXX.XXXXX /* linked server XXXXX */

Now it LOOKS for the linked server and does not find one and throws an error.

Msg 7202, Level 11, State 2, Procedure #TestProc, Line 6
Could not find server 'XXXXX' in sys.servers. 
Verify that the correct server name was specified. 
If necessary, execute the stored procedure sp_addlinkedserver 
to add the server to sys.servers.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '#TestProc'.

Answer 3

A blunt instrument, but you can ignore errors.

:On Error ignore in the script?

-b and -V to ignore "severity < V" (Not tried it). Could be useful to detect "no object" errors but throw your own above V severity when there is really an error.

See SQLCMD in MSDN

Answer 4

Try to use TRY-CATCH blocks, easy to use and elegant.

if 1 = 0
BEGIN TRY
select * from mydatabase.dbo.mytable;
END TRY
BEGIN CATCH
--error handling code
SELECT ERROR_MESSAGE()
END CATCH