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

Push or Pull for Replication-Based failover

I am trying to devise a setup where I can use Replication to maintain a
failover server in case of a critical failure on the primary DB server. I
plan on using transactional replication and immeadiate updating for minimal
latency, but I have a couple of questions about the right setup:
1. Push or pull? Push would allow for 2 phase commit, which seems
desirable. On the other hand, when system 1 fails, don't you need to "break
the link" between the subscriber and publisher (i.e. run sp_dropsubscription)
in order to ensure proper operation?
2. We have some identity columns. I am planning on marking them as NOT FOR
REPLICATION and then in a failover scenario, run DBCC CHECKIDENT('my
table',RESEED,<looked up max from table>) to reseed the identity on the
secondary. Is this an advisable strategy?
I have been looking at other solutions in this area which involve modifying
some of the generated procs and I would like to avoid that so that we can use
as much out of the box as possible (trying not to pretend I can engineer
better than MS ).
Thanks,
Tom
Push or pull doesn't really relate to 2PC - the setting really controls who
initiates the distributor->subscriber traffic. If the subscriber fails, the
2PC won't matter because it is in the direction subscriber->distributor. The
other way round and you'll have to remove the subscription.
For the identities, the easiest solution is to set up immediateupdating with
queued updating failover and have a large seed set up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Could you clarify a bit? does subscriber->distributor imply pull whereas
distributor ->subscriber implies push? I am trying to understand when I
would have to remove the subscription.
Thanks,
Tom
"Paul Ibison" wrote:

> Push or pull doesn't really relate to 2PC - the setting really controls who
> initiates the distributor->subscriber traffic. If the subscriber fails, the
> 2PC won't matter because it is in the direction subscriber->distributor. The
> other way round and you'll have to remove the subscription.
> For the identities, the easiest solution is to set up immediateupdating with
> queued updating failover and have a large seed set up.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||The directions I indicated refer to data flow, eg subscriber->distributor in
your case is 2PC, whereas distributor ->subscriber is the PUSH/PULL running
of the distribution agent. I think you're mixing PUSH/PULL with the flow of
data, while the 2 are not really related in that way - it's more which
computer is going to do the synchronization work.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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

Publisher SP1 Subscriber SP2 ?

I have a clustering between Server A and Server B. There is a clustered SQL Server installed.

Server A is a primary node(oltp production server) and Server B is a secondary node.

i've set up transactional replication betw serer A and B. Server A is Publisher and Server B is subscriber. B also has a distribution database. I use serve B for all the reporting purposes.

Currently both servers have sp1.

Now i want to apply sp2 to the server B first since i use it for reporting purposes (just to make sure there are no issues and then after some days i want to apply sp2 on server A which is my prodction oltp server)

So when i apply sp2 on server B , my publisher will have SP1 and subscriber (& distributor) will have sp2. Will this cause any issues with replication?

Any help would be appriciated.

Thanks

No, the correct order it to update the distributor first, then the publisher and finally the subscibers. It sounds like you are doing things correctly.

I am a little confused by your clustering topology - I take it you have a multi-instance (active active) topology?|||
Thanks so much for the reply.

Actually i have active passive clustering. Server A is primary and Server B is secondary. If something goes wrong with A, it fails over to B but not a vice-versa. Server A is my production oltp server.

On server B i also installed a named instance (standalone instance not the clustered instance) which i am using for all the reporting purposes.

For replication, I am replicating server A database articles to the named instance on Server B. And this named instance is also acting as its distributor.

So

Server A (Default Instance & Clustered):- Publisher

Server B\Instance Name (standalone instance): Subscriber as well as the distributor

So if I apply SP2 on the named instance of Server B, i will be upgrading distributor as well as Subscriber (both at the same time.) And after some days i am planning to apply SP2 on the cluster which will upgrade my production oltp in the cluster.

I hope this will clear your doubts.

Please let me know if there are any issues with what i am doing as i explained above.

Thanks

|||I am unaware with any issues surrounding this. It should work.|||Thank you so much.

Publisher SP1 Subscriber SP2 ?

I have a clustering between Server A and Server B. There is a clustered SQL Server installed.

Server A is a primary node(oltp production server) and Server B is a secondary node.

i've set up transactional replication betw serer A and B. Server A is Publisher and Server B is subscriber. B also has a distribution database. I use serve B for all the reporting purposes.

Currently both servers have sp1.

Now i want to apply sp2 to the server B first since i use it for reporting purposes (just to make sure there are no issues and then after some days i want to apply sp2 on server A which is my prodction oltp server)

So when i apply sp2 on server B , my publisher will have SP1 and subscriber (& distributor) will have sp2. Will this cause any issues with replication?

Any help would be appriciated.

Thanks

No, the correct order it to update the distributor first, then the publisher and finally the subscibers. It sounds like you are doing things correctly.

I am a little confused by your clustering topology - I take it you have a multi-instance (active active) topology?|||
Thanks so much for the reply.

Actually i have active passive clustering. Server A is primary and Server B is secondary. If something goes wrong with A, it fails over to B but not a vice-versa. Server A is my production oltp server.

On server B i also installed a named instance (standalone instance not the clustered instance) which i am using for all the reporting purposes.

For replication, I am replicating server A database articles to the named instance on Server B. And this named instance is also acting as its distributor.

So

Server A (Default Instance & Clustered):- Publisher

Server B\Instance Name (standalone instance): Subscriber as well as the distributor

So if I apply SP2 on the named instance of Server B, i will be upgrading distributor as well as Subscriber (both at the same time.) And after some days i am planning to apply SP2 on the cluster which will upgrade my production oltp in the cluster.

I hope this will clear your doubts.

Please let me know if there are any issues with what i am doing as i explained above.

Thanks

|||I am unaware with any issues surrounding this. It should work.|||Thank you so much.