Toad World® Forums

Veryfi SQL issue


#1

Hi,
‘funny’ issue: I checked [verified sql] script
script [btw: generated by toad ] consist of 2 parts:
part 1. check for existence of table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N’[dbo].[DZIAL]’) AND type in (N’U’))
BEGIN
DROP TABLE [PHOBOS].[dbo].[DZIAL];
END
GO

table Dzial is referenced by foreign key
part 2. Creating table [again ;)]
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N’[dbo].[DZIAL]’) AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[DZIAL] (
[DZIAL_ID] int IDENTITY( 1 , 1 ) NOT NULL,
[DZI_DZIAL_NADRZ] int NULL …

Now: When cursor is ‘in’ [I do not mark/select any part of script]
first part of script, I get error. When ‘in’ second - everything is OK.

It looks like you check only [without any warning] part of script. However, Imho
first part is ALSO correct!


Butter

int_1.jpeg
int_1.jpeg


#2

Hello Marcin,

Thank you for report

As I see from attachment, when you run Verify SQL for the first time, it is for
the first part.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N’[dbo].[DZIAL]’) AND type in (N’U’))
BEGIN
DROP TABLE [PHOBOS].[dbo].[DZIAL];
END
GO

And you see SQL ERROR message that table could not be dropped due this table is
referenced by a FOREIGN KEY constraint. This is correct behavior. Script is
fine, but table could not be dropped.

When you run Verify SQL for the second time, it is for the second part.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N’[dbo].[DZIAL]’) AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[DZIAL] (
[DZIAL_ID] int IDENTITY( 1 , 1 ) NOT NULL,
[DZI_DZIAL_NADRZ] int NULL …
This part of script is correct and executes successfully

Thanks,

Anastasia
int_1.jpeg


#3

Hello Marcin,

Thank you for report

As I see from attachment, when you run Verify SQL for the first time, it is for
the first part.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N’[dbo].[DZIAL]’) AND type in (N’U’))
BEGIN
DROP TABLE [PHOBOS].[dbo].[DZIAL];
END
GO

And you see SQL ERROR message that table could not be dropped due this table is
referenced by a FOREIGN KEY constraint. This is correct behavior. Script is
fine, but table could not be dropped.

When you run Verify SQL for the second time, it is for the second part.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N’[dbo].[DZIAL]’) AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[DZIAL] (
[DZIAL_ID] int IDENTITY( 1 , 1 ) NOT NULL,
[DZI_DZIAL_NADRZ] int NULL …
This part of script is correct and executes successfully

Thanks,

Anastasia
image001.jpeg


#4

Hello Marcin,

Thank you for report

As I see from attachment, when you run Verify SQL for the first time, it is for
the first part.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N’[dbo].[DZIAL]’) AND type in (N’U’))
BEGIN
DROP TABLE [PHOBOS].[dbo].[DZIAL];
END
GO

And you see SQL ERROR message that table could not be dropped due this table is
referenced by a FOREIGN KEY constraint. This is correct behavior. Script is
fine, but table could not be dropped.

When you run Verify SQL for the second time, it is for the second part.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N’[dbo].[DZIAL]’) AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[DZIAL] (
[DZIAL_ID] int IDENTITY( 1 , 1 ) NOT NULL,
[DZI_DZIAL_NADRZ] int NULL …
This part of script is correct and executes successfully

Thanks,

Anastasia
image001.jpeg


#5

Hello Marcin,

Thank you for report

As I see from attachment, when you run Verify SQL for the first time, it is for
the first part.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N’[dbo].[DZIAL]’) AND type in (N’U’))

BEGIN

DROP TABLE [PHOBOS].[dbo].[DZIAL];

END

GO

And you see SQL ERROR message that table could not be dropped due this table is
referenced by a FOREIGN KEY constraint. This is correct behavior. Script is
fine, but table could not be dropped.

I understand this. Issue is, that I expect WHOLE script will be validated as one
and result will not depend on cursor position!

Butter