BUG: SET FMTONLY not working on SQL Server 2000

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=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, MCSD" <bulent@.e-kampus.com> wrote in message
news:DDE055D6-D61D-49AF-B6F9-7615EA12E9A6@.microsoft.com...
> 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
>|||Bulent Biyikoglu, MCSD wrote:
> 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
/*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
=============sql