Possible bug

Hi People,
Does any body have any information how to report a possible SQL Server bug. I think I found the very interesting one
Thanks,
Dusko Vuksanovic
woox@.gmx.net
This is a good place to start. If it is a new one, I am sure myself or
another MVP will let Microsoft know.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Dusko Vuksanovic" <woox@.gmx.com> wrote in message
news:eYS8MCZrFHA.3216@.TK2MSFTNGP12.phx.gbl...
> Hi People,
> Does any body have any information how to report a possible SQL Server
> bug. I think I found the very interesting one
>
> Thanks,
> Dusko Vuksanovic
> woox@.gmx.net
>
|||Ok. Thanks Geoff.
Here is the brief description; I created a simple scalar UDF, and pointed it to a field in table as "Default Value". Amaizingly,
filled value in the table was always zero (0), instead of the integer value returned from UDF.
UDF is returning INT value, and field for filling the "Default Value" is INT too.
By the way, I think I'm having SQL2000 , SP3
Here is the sample TSQL (IntVal shoul always be 111 instead of 0; I cripled example to be as simple as possible):
--UDF:
CREATE FUNCTION dbo.Function1
()
RETURNS INT
AS
BEGIN
RETURN 111
END
--TABLE
/****** Object: Table [dbo].[TktTrack] Script Date: 8/30/2005 11:41:54 AM ******/
CREATE TABLE [dbo].[TktTrack] (
[IntVal] [int] NOT NULL ,
[ANum] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TktTrack] WITH NOCHECK ADD
CONSTRAINT [DF_TktTrack_IntVal] DEFAULT ([dbo].[Function1]()) FOR [IntVal]
GO
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message news:OLVcuDZrFHA.3596@.TK2MSFTNGP15.phx.gbl...
> This is a good place to start. If it is a new one, I am sure myself or
> another MVP will let Microsoft know.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Dusko Vuksanovic" <woox@.gmx.com> wrote in message
> news:eYS8MCZrFHA.3216@.TK2MSFTNGP12.phx.gbl...
>
|||On Tue, 30 Aug 2005 11:45:53 -0700, Dusko Vuksanovic wrote:

>Ok. Thanks Geoff.
>Here is the brief description; I created a simple scalar UDF, and pointed it to a field in table as "Default Value". Amaizingly,
>filled value in the table was always zero (0), instead of the integer value returned from UDF.
>UDF is returning INT value, and field for filling the "Default Value" is INT too.
>By the way, I think I'm having SQL2000 , SP3
Hi Dusko,
I couldn't reproduce it on SP4:
CREATE FUNCTION dbo.Function1
()
RETURNS INT
AS
BEGIN
RETURN 111
END
go
--TABLE
/****** Object: Table [dbo].[TktTrack] Script Date: 8/30/2005
11:41:54 AM ******/
CREATE TABLE [dbo].[TktTrack] (
[IntVal] [int] NOT NULL ,
[ANum] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TktTrack] WITH NOCHECK ADD
CONSTRAINT [DF_TktTrack_IntVal] DEFAULT ([dbo].[Function1]()) FOR
[IntVal]
GO
insert TktTrack (IntVal, ANum) values(default, 1)
go
select * from TktTrack
go
drop table TktTrack
go
drop function dbo.Function1
go
select @.@.version
go
IntVal ANum
-- --
111 1
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||You are right, Geoff. It works. However, if I manually insert the record in SQL Manager, I must refresh it to see the value (this is
kind of "buggy" behaviour, because it will show 0 at the first place).
Thanks,
Dusko
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:jbc9h1pc4jdc13j7hr9g1t15mmjt7cc7fk@.4ax.com...
> On Tue, 30 Aug 2005 11:45:53 -0700, Dusko Vuksanovic wrote:
>
> Hi Dusko,
> I couldn't reproduce it on SP4:
> CREATE FUNCTION dbo.Function1
> ()
> RETURNS INT
> AS
> BEGIN
> RETURN 111
> END
> go
> --TABLE
> /****** Object: Table [dbo].[TktTrack] Script Date: 8/30/2005
> 11:41:54 AM ******/
> CREATE TABLE [dbo].[TktTrack] (
> [IntVal] [int] NOT NULL ,
> [ANum] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[TktTrack] WITH NOCHECK ADD
> CONSTRAINT [DF_TktTrack_IntVal] DEFAULT ([dbo].[Function1]()) FOR
> [IntVal]
> GO
> insert TktTrack (IntVal, ANum) values(default, 1)
> go
> select * from TktTrack
> go
> drop table TktTrack
> go
> drop function dbo.Function1
> go
> select @.@.version
> go
> IntVal ANum
> -- --
> 111 1
>
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
> May 3 2005 23:18:38
> Copyright (c) 1988-2003 Microsoft Corporation
> Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
>
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||Wasn't me. Thank Hugo.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Dusko Vuksanovic" <woox@.gmx.com> wrote in message
news:%23ASqlqarFHA.3604@.tk2msftngp13.phx.gbl...
> You are right, Geoff. It works. However, if I manually insert the record
> in SQL Manager, I must refresh it to see the value (this is
> kind of "buggy" behaviour, because it will show 0 at the first place).
> Thanks,
> Dusko
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:jbc9h1pc4jdc13j7hr9g1t15mmjt7cc7fk@.4ax.com...
>
|||On Tue, 30 Aug 2005 14:34:45 -0700, Dusko Vuksanovic wrote:

>You are right, Geoff. It works. However, if I manually insert the record in SQL Manager, I must refresh it to see the value (this is
>kind of "buggy" behaviour, because it will show 0 at the first place).
Hi Dusko,
Enterprise Manager *IS* buggy when you use it to change data. Don't.
In this case, the most likely explanation is that this is a result of
not having a primary key on the table.
<speculation>
When you insert data in a table through EM, EM will check if any data
gets changed automaGically, so it reads back the row you just inserted.
If the table has a kkey (as all good tables should), EM uses just the
key values to find the row just inserted. But if there's no key, EM has
no choice but to match on all columns.
Of course, since one of the columns gets populated with a value
different from what you entered in EM (that's what the default is for,
of course), EM can't find the row. Only after refreshing the table will
the row show up, as EM will read the complete table when you refresh.
</speculation>
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)