I found a bug in Sql Server 2000！！！

Yesterday I created a table in Sql Server 2000,using this script：
CREATE TABLE [dbo].[User](
[Id] [int] NOT NULL,
[UserName] [nvarchar](20) NOT NULL,
[TrueName] [nvarchar](20) NOT NULL,
[Password] [nvarchar](60) NOT NULL,
[Department] [int] NOT NULL,
[Mobile] [nvarchar](20) NULL,
[Telephone] [nvarchar](20) NULL,
[Remark] [nvarchar](200) NULL,
[Enabled] [bit] NOT NULL,
[Available] [bit] NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]
There was only one record in it:
INSERT INTO [User] VALUES(1,N'SUP',N'Jim',N'213123',1,NULL,NULL,NULL,1,1)
After I executed this script：UPDATE [User] SET UserName=N'f',TrueName=N'gg',[Password]=N'dfsdfsdf',Mobile=NULL,Telephone=NULL,Remark=NULL,
Enabled=1 WHERE [Id]=1 AND Available=1
The value of 'Available' field changed to Zero.That was not supposed to happen.
I'm pretty sure it is a bug, because when I did the same thing in Sql Server 2005, everything was correct.
Anybody can tell me if this is a known bug? I searched google,but couldn't find any answer.
Thanks!
"The value of 'Available' field changed to Zero"
Nope. Does not happened when i try it on my SQL Server 2000. It is still 1.
|||Thank you for trying it. But it went wrong on my machine. I also tried

in several other machines(all had service pack4 installed). They all

changed the 'Available' field.

Weirdly enough, when I changed the table structure, for example ,

changing the nvarchar field to nchar or cutting away the 'Enabled'

field, the bug disappeared.

Can anybody else try it? Please|||

Hi Okay,

No issues running on a 2k sp4 machine here.

Cheers

Rob

|||

Robert Varga wrote:

Hi Okay,

No issues running on a 2k sp4 machine here.

Cheers

Rob

Not win2000 sp4. I meant Sql Server 2000 sp4. The system is XP sp2.|||

Yes, that's what I meant: 2k = 2000, sp4

|||Mine is Personal Edition.I tried on Developer Edition just now, it didn't happen. Maybe that's the reason.

I don't remember how it was...

Using this system function (::fn_dblog(null,null)) you find out how many transactions are been confirmed in your .LDF. Although by means of another system function you can see further information, i mean statistical ones.

Any help would be very appreciated.

Hi,

how about this one here:

DBCC LOGINFO(db_id)

Select * from fn_dblog(null,null)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

I don't remember how it was...

Using this system function (::fn_log(null,null)) you can find out how many transactions have been confirmed in your .LDF. Although by means of another system function you can see further information, such as statitical.

Any help would be very appreciated.

Thanks

There isn't a function by that name. There's a similarly named undocumented and unsupported function (that I'm not going to discuss) for use during troubleshooting by Product Support and the product group.

What exactly are you trying to find out?

I can't get a SQL QUery to accept a null for a parameter

I have straight forward Insert Query, which takes values mainly from text boxes, however I am having trouble when the value is null.

eg
cmdP0.Parameters["@.EmpID"].Value=txtEmpNo.Text;

When I run the Query I get...
Message="Parameterized Query '(@.ID int,@.EmpID int,@.Photo nvarchar(260),@.DoB smalldatetime,@.Med' expects parameter @.EmpID, which was not supplied."

I also get this when the parameter can be a string.
I have set the parameter properties so that SourceColumnNullMaping to true (it was set as false - so hoped this might fix it!)

No luck.

Have you tried to set DBNull.Value as value of the parameter, if the text is returned as null?|||

That worked for the strings it took a bit of effort forn integers.

Thanks to someone eles code, which I don't quite understand...

string inputGap = txtGap.Text;
Int32? searchGap = null;
if(!string.IsNullOrEmpty(inputGap)) {
Int32 Gap;
if(Int32.TryParse(inputGap , out Gap))
searchGap = Gap;
}
if(searchGap!=null) {
cmdP0.Parameters["@.MedicalGap"].Value=searchGap;
} else {
cmdP0.Parameters["@.MedicalGap"].Value=DBNull.Value;}

I don't know what Int32? searchGap = null; does

|||

Int32 is a struct (a value type). That means it can't be null. .NET 2.0 adds so called Nullable Types. That means you can set an Int32 null, by using a special syntax. Int32? (with the question mark) means that this is a nullable. It means that this variable can be set to null.

This piece of code is to complex. You can make it a lot easier by doing this:

int value = 0;
if (int.TryParse(txtGap.Text, out value))
cmdP0.Parameters["@.MedicalGap"].Value = value;
else
cmdP0.Parameters["@.MedicalGap"].Value = DBNull.Value;

|||

Thank you

A nice clear explanation and some nice code

Much appreciated, this has been driving me nuts!

I am also getting: Login failed for user (null). Reason: Not associated with a trusted SQL

This summary is not available. Please click here to view the post.