Puzzling Primary Key Problem

Hi,

I wonder if anybody encountered before with tables which has records with some of the primary keys as null values?

Currently I've found a number of records which isn't suppose to be inserted in the first place from a table.

My table size is over a few millions and currently using SQL Server Standard version

Seems like the primary key got corrupted or something...

Anybody encountered this before??

-deb-If the primary key of the table is autogenerated then you can use

DBCC CHECKIDENT

for Checking the current identity value for the specified table and, if needed, corrects the identity value.|||Thanks but I don't think that's the case because it happens to a non identity column.
By Default even before inserting it SQL Server should have prompted a Primary Key Violation Error but in this case it doesn't and proceed to insert the value into the column as null.|||Can you execute a DBCC Checktable statement on the table and verify the results.|||Will try and see and let you know as the db is at customer's site.
Thanks
:)|||do the users provide their own values for example could they press the spacebar or does your front end add characters using a ascii function like char(xx)

here is what [BOL] has to say on the matter

"When a PRIMARY KEY constraint is added to an existing column or columns in the table, Microsoft SQL Server 2000 checks the existing data in the columns to ensure that the existing data follows the rules for primary keys:

No null values
No duplicate values

If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.
"

so i tried this

create table nulltesttable
(
nullcolumn varchar(10) not null
)

and added three rows

insert nulltesttable values (char(9))
insert nulltesttable values (char(13))
insert nulltesttable values (char(32))

then i applied the pk with nocheck and it applied as long as i didnt duplicate the ascii chars in the columns

i cant say what your issue is but if you try this query it could at least lead you to eliminate this as a problem

select Char(nullcolumn) as 'Null column'
from nulltesttable

there are a very limited ascii chars that are invisible so if you have a large # of rows that appear to be null then this is probably not your issue either way thanks for the exercise...|||Thanks... actually that was what I suspect also due to the reason the values are from barcode and sometimes the reader can return some rubbish that is not visible. There are a few records which this rubbish data.

But still I need to look for other possibilities. For my case if I were to select out using a select statement

select * from table
where column is null

It'll return records. :)

That's the very funny thing*sigh*

Originally posted by Ruprect
do the users provide their own values for example could they press the spacebar or does your front end add characters using a ascii function like char(xx)

here is what [BOL] has to say on the matter

"When a PRIMARY KEY constraint is added to an existing column or columns in the table, Microsoft SQL Server 2000 checks the existing data in the columns to ensure that the existing data follows the rules for primary keys:

No null values
No duplicate values

If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.
"

so i tried this

create table nulltesttable
(
nullcolumn varchar(10) not null
)

and added three rows

insert nulltesttable values (char(9))
insert nulltesttable values (char(13))
insert nulltesttable values (char(32))

then i applied the pk with nocheck and it applied as long as i didnt duplicate the ascii chars in the columns

i cant say what your issue is but if you try this query it could at least lead you to eliminate this as a problem

select Char(nullcolumn) as 'Null column'
from nulltesttable

there are a very limited ascii chars that are invisible so if you have a large # of rows that appear to be null then this is probably not your issue either way thanks for the exercise...sql

PULL huge table from SQL Server Problem

Hey Guys

I have meet the same problem, too.

I create a table with 1,380,000 rows data,

the db real size about 114 MB.

The primary key size is nchar(6).

When I use RDA pull, I found that the primary

key in the PDA disappear. So, It took a long time

to get query response.

But when I delete some rows to 680,000 rows of data.

After I pull, The primary key can pull from the SQL Server.

PS: I didn't change any code. Just delete some rows.

Is that SQL-Mobile's bug?

PS: 1.Database and Temp Database limitation both are 384MB

2.If I use query analyzer to add primary key it works! so strange!!

3.Pull process return "S_OK".

4.After Pull process finished, the db connection still alive. It seems not like

time out problem.

5.Local Connection String:"Data Source='%s\\%s';SSCEatabase Password='%s';SSCE:Encrypt Database='true';SSCE:Max Database Size=384;SSCE:Temp File Max size=384;SSCE:Temp File Directory=%s"

We do have tests that do PULL of 200 MB data and almost 10 Lakh rows. Is that possible for you to give the schema and few more details of your environment so that we can try reproducing and find the root cause.

Thanks,

Laxmi

Public key Token = b77a5c561934e089

Hi Ppl,

I have installed my program and SQL server 2K5 on the server.

But when I try to run it (from he client machine)I get the following error :

It works fine on the SERVER

The appication attempted to perform an operation not allowed by the security policy.

The operation required the security Exception. To grant this application the required permission

please contact your systems Administrator or use the Microsoft .NET security policy administration tool.

If you click Bla,bla,bla

Request for the permision of type System.Data.SqlClient.SqlClient

Permission, System.Data Version - 1.0.5000.0. Cultre = neutral.

Public key Token = b77a5c561934e089

did you ever solve this as I now have the same issue|||

I've done some checking and this seems to be related to the fact that you're apparently running your application from a network share. When you run a managed application from the network, you need to either trust the network or trust the assembly by adjusting the security policy on the local computer. Check out the Code Access Security information for more details.

The alternative to adjusting the security policy is to install the application onto the local computer via either ClickOnce or MSI. More details on the these tools can be found at:

For .NET 1.1 see

Smart Client Architecture and Design Guide

Chapter 7 — Deploying and Updating Smart Client Applications

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scag-ch07.asp

For .NET 2.0 see

ClickOnce

http://msdn.microsoft.com/clickonce

Regards,

Mike Wachal
SQL Express team