BUG: SET FMTONLY not working on SQL Server 2000

G

Guest

Hello

I have reported this bug previosly, but Microsoft has so far not responded yet, so I am reporting it again. Basicly, SET FMTONLY is NOT working correctly with SQL Server 2000. Because of this bug I still have to use SQL Server 7.0 for my campuswide student information system application, although I really would like to upgrade to SQL Server 2000 :-

Here's the script to reproduce the bug

-- create the test tables, and populate the
Create Table dbo.USERS_TES
([USER_ID] int PRIMARY KEY not null
[NAME] varchar(20) null
[SURNAME] varchar(20) null
G

Set NoCount O

Insert Into dbo.USERS_TEST Values (1,'Bulent','Biyikoglu'
Insert Into dbo.USERS_TEST Values (2,'Jane','Doe'
G

Create Table dbo.USER_DETAILS_TES
([USER_DETAIL_ID] int PRIMARY KEY not null
[USER_ID] int NOT NULL FOREIGN KEY REFERENCES dbo.USERS_TEST([USER_ID])
FATHERS_NAME varchar(20) null
MOTHERS_NAME varchar(20) null
PLACE_OF_BIRTH varchar(40) null
CITY varchar(20) null
TOWN varchar(20) null
COUNTRY varchar(20) null
G

Set NoCount O

Insert Into dbo.USER_DETAILS_TEST Values (1,1,'Father1','Mother1','Polatli','Palo Alto','Stanford','Turkey'
Insert Into dbo.USER_DETAILS_TEST Values (2,2,'Father2','Mother2','Orange County','Los Angeles','Berkeley','USA'
G

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
G

-- create the stored procedure that accesses the test table
/****** Object: Stored Procedure dbo.GetUserDetailsTest Script Date: 27.11.2002 00:57:27 ******
CREATE PROCEDURE [GetUserDetailsTest
(@UserId int = Null
@UserDetailId int = Null
@ExpansionType tinyint = 0

A

Set NoCount O

If @ExpansionType =
Begi

If @UserDetailId Is Not Nul
Begi
Select
ud.USER_DETAIL_ID
ud.USER_ID
ud.FATHERS_NAME
ud.MOTHERS_NAME
ud.PLACE_OF_BIRTH
ud.CITY
ud.TOWN
ud.COUNTR
From dbo.USER_DETAILS_TEST u
Where ud.USER_DETAIL_ID = @UserDetailI
Return
En

If @UserId Is Not Nul
Begi
Select
ud.USER_DETAIL_ID
ud.USER_ID
ud.FATHERS_NAME
ud.MOTHERS_NAME
ud.PLACE_OF_BIRTH
ud.CITY
ud.TOWN
ud.COUNTR
From dbo.USER_DETAILS_TEST u
Where ud.USER_ID = @UserI
Return
En

En

If @ExpansionType =
Begi

If @UserId Is Not Nul
Begi
Select
u.USER_ID
u.[NAME]
u.[SURNAME]
ud.USER_DETAIL_ID
ud.FATHERS_NAME
ud.MOTHERS_NAME
ud.PLACE_OF_BIRTH
ud.CITY
ud.TOWN
ud.COUNTR
From dbo.USER_DETAILS_TEST u
Inner Join dbo.USERS_TEST u On ud.USER_ID = u.USER_I
Where u.USER_ID = @UserI
Return
En

En

G

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
G

-- use SET FMTONLY to return metadata, HERE's WHERE THE BUG OCCURS
-- the procedure is called as if @ExpansionType parameter is set to
set fmtonly on exec GetUserDetailsTest @UserId=1,@ExpansionType=1 set fmtonly of

exec GetUserDetailsTest @UserId=1,@ExpansionType=
 
T

Tibor Karaszi

Ignore this if you are posting from the MSDN managed groups:

This is not a formal bug reporting forum. This is a peer to peer forum,. To
report a bug formally, open a case with MS. You will not be charged if it is
a bug.

--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver


Bulent Biyikoglu said:
Hello,

I have reported this bug previosly, but Microsoft has so far not responded
Click to expand...
yet, so I am reporting it again. Basicly, SET FMTONLY is NOT working
correctly with SQL Server 2000. Because of this bug I still have to use SQL
Server 7.0 for my campuswide student information system application,
although I really would like to upgrade to SQL Server 2000 :-(
Here's the script to reproduce the bug:

-- create the test tables, and populate them
Create Table dbo.USERS_TEST
([USER_ID] int PRIMARY KEY not null,
[NAME] varchar(20) null,
[SURNAME] varchar(20) null)
GO

Set NoCount On

Insert Into dbo.USERS_TEST Values (1,'Bulent','Biyikoglu')
Insert Into dbo.USERS_TEST Values (2,'Jane','Doe')
GO

Create Table dbo.USER_DETAILS_TEST
([USER_DETAIL_ID] int PRIMARY KEY not null,
[USER_ID] int NOT NULL FOREIGN KEY REFERENCES dbo.USERS_TEST([USER_ID]),
FATHERS_NAME varchar(20) null,
MOTHERS_NAME varchar(20) null,
PLACE_OF_BIRTH varchar(40) null,
CITY varchar(20) null,
TOWN varchar(20) null,
COUNTRY varchar(20) null)
GO

Set NoCount On

Insert Into dbo.USER_DETAILS_TEST Values
Click to expand...
(1,1,'Father1','Mother1','Polatli','Palo Alto','Stanford','Turkey')
Insert Into dbo.USER_DETAILS_TEST Values (2,2,'Father2','Mother2','Orange
Click to expand...
County','Los Angeles','Berkeley','USA')
GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

-- create the stored procedure that accesses the test tables
/****** Object: Stored Procedure dbo.GetUserDetailsTest Script Date: 27.11.2002 00:57:27 ******/
CREATE PROCEDURE [GetUserDetailsTest]
(@UserId int = Null,
@UserDetailId int = Null,
@ExpansionType tinyint = 0)

As

Set NoCount On

If @ExpansionType = 0
Begin

If @UserDetailId Is Not Null
Begin
Select
ud.USER_DETAIL_ID,
ud.USER_ID,
ud.FATHERS_NAME,
ud.MOTHERS_NAME,
ud.PLACE_OF_BIRTH,
ud.CITY,
ud.TOWN,
ud.COUNTRY
From dbo.USER_DETAILS_TEST ud
Where ud.USER_DETAIL_ID = @UserDetailId
Return 0
End

If @UserId Is Not Null
Begin
Select
ud.USER_DETAIL_ID,
ud.USER_ID,
ud.FATHERS_NAME,
ud.MOTHERS_NAME,
ud.PLACE_OF_BIRTH,
ud.CITY,
ud.TOWN,
ud.COUNTRY
From dbo.USER_DETAILS_TEST ud
Where ud.USER_ID = @UserId
Return 0
End

End

If @ExpansionType = 1
Begin

If @UserId Is Not Null
Begin
Select
u.USER_ID,
u.[NAME],
u.[SURNAME],
ud.USER_DETAIL_ID,
ud.FATHERS_NAME,
ud.MOTHERS_NAME,
ud.PLACE_OF_BIRTH,
ud.CITY,
ud.TOWN,
ud.COUNTRY
From dbo.USER_DETAILS_TEST ud
Inner Join dbo.USERS_TEST u On ud.USER_ID = u.USER_ID
Where u.USER_ID = @UserId
Return 0
End

End

GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

-- use SET FMTONLY to return metadata, HERE's WHERE THE BUG OCCURS:
-- the procedure is called as if @ExpansionType parameter is set to 0
set fmtonly on exec GetUserDetailsTest @UserId=1,@ExpansionType=1 set fmtonly off

exec GetUserDetailsTest @UserId=1,@ExpansionType=1
Click to expand...
 
M

Mark Broadbent

Hello,

I have reported this bug previosly, but Microsoft has so far not
responded yet, so I am reporting it again. Basicly, SET FMTONLY is
NOT working correctly with SQL Server 2000. Because of this bug I
still have to use SQL Server 7.0 for my campuswide student
information system application, although I really would like to
upgrade to SQL Server 2000 :-(

Here's the script to reproduce the bug:

-- create the test tables, and populate them
Create Table dbo.USERS_TEST
([USER_ID] int PRIMARY KEY not null,
[NAME] varchar(20) null,
[SURNAME] varchar(20) null)
GO

Set NoCount On

Insert Into dbo.USERS_TEST Values (1,'Bulent','Biyikoglu')
Insert Into dbo.USERS_TEST Values (2,'Jane','Doe')
GO

Create Table dbo.USER_DETAILS_TEST
([USER_DETAIL_ID] int PRIMARY KEY not null,
[USER_ID] int NOT NULL FOREIGN KEY REFERENCES
dbo.USERS_TEST([USER_ID]), FATHERS_NAME varchar(20) null,
MOTHERS_NAME varchar(20) null,
PLACE_OF_BIRTH varchar(40) null,
CITY varchar(20) null,
TOWN varchar(20) null,
COUNTRY varchar(20) null)
GO

Set NoCount On

Insert Into dbo.USER_DETAILS_TEST Values
(1,1,'Father1','Mother1','Polatli','Palo Alto','Stanford','Turkey')
Insert Into dbo.USER_DETAILS_TEST Values
(2,2,'Father2','Mother2','Orange County','Los
Angeles','Berkeley','USA') GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

-- create the stored procedure that accesses the test tables
/****** Object: Stored Procedure dbo.GetUserDetailsTest Script
Date: 27.11.2002 00:57:27 ******/ CREATE PROCEDURE
[GetUserDetailsTest] (@UserId int = Null,
@UserDetailId int = Null,
@ExpansionType tinyint = 0)

As

Set NoCount On

If @ExpansionType = 0
Begin

If @UserDetailId Is Not Null
Begin
Select
ud.USER_DETAIL_ID,
ud.USER_ID,
ud.FATHERS_NAME,
ud.MOTHERS_NAME,
ud.PLACE_OF_BIRTH,
ud.CITY,
ud.TOWN,
ud.COUNTRY
From dbo.USER_DETAILS_TEST ud
Where ud.USER_DETAIL_ID = @UserDetailId
Return 0
End

If @UserId Is Not Null
Begin
Select
ud.USER_DETAIL_ID,
ud.USER_ID,
ud.FATHERS_NAME,
ud.MOTHERS_NAME,
ud.PLACE_OF_BIRTH,
ud.CITY,
ud.TOWN,
ud.COUNTRY
From dbo.USER_DETAILS_TEST ud
Where ud.USER_ID = @UserId
Return 0
End

End

If @ExpansionType = 1
Begin

If @UserId Is Not Null
Begin
Select
u.USER_ID,
u.[NAME],
u.[SURNAME],
ud.USER_DETAIL_ID,
ud.FATHERS_NAME,
ud.MOTHERS_NAME,
ud.PLACE_OF_BIRTH,
ud.CITY,
ud.TOWN,
ud.COUNTRY
From dbo.USER_DETAILS_TEST ud
Inner Join dbo.USERS_TEST u On ud.USER_ID = u.USER_ID
Where u.USER_ID = @UserId
Return 0
End

End

GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

-- use SET FMTONLY to return metadata, HERE's WHERE THE BUG OCCURS:
-- the procedure is called as if @ExpansionType parameter is set to 0
set fmtonly on exec GetUserDetailsTest @UserId=1,@ExpansionType=1 set
fmtonly off

exec GetUserDetailsTest @UserId=1,@ExpansionType=1
Click to expand...


/*Hi Bulent, while I am certainly not going to dispute that the
behaviour using this command in SQL 7 does do what you expect (since I
cant test it against 7 at the mo), I think this behaviour in 2000 is by
design.

As you know the SET FMTONLY ON setting is telling SQL to return only
the metadata of a result set. It appears that all statements that do
not return a result set and metadata are processed only but then
ignored (if that makes sense! since you have effectively told SQL that
you are only interested in the metadata) INCLUDING logic statements.

See the following*/


USE Northwind
SET NOCOUNT OFF
DECLARE @myInt TINYINT
SET @myInt=1
--SET FMTONLY ON
PRINT 'Display this text from print. You will see it does not print'
SELECT 'Display this text from select. You will see it is processed BUT
the result set contains no metadata'
SELECT @myInt=2 --You will see below (from print) that this select is
processed
SET FMTONLY OFF PRINT @myInt SET FMTONLY ON
SET @myInt=3 --You will see below (from print) that this set is
processed
SET FMTONLY OFF PRINT @myInt SET FMTONLY ON
select * from northwind..orders
select * from northwind..[order Details]
IF @myInt=1 --is skipped
select * from northwind..orders --meta is returned
ELSE --is skipped
select * from northwind..[order Details] --meta is returned
ENDIF
EXEC SP_HELP --Stored procedure with multiple Selects including
variable assignments all metadata selects are returned
SELECT * FROM Northwind..Invoices --This is a view, metadata is returned
SET FMTONLY OFF


--

Br,
Mark Broadbent
mcdba , mcse+i
=============
 
Top