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 Merge snapshot fails with Data is Null message.

Hi,

I'm converting a replication script from SQL 2000 to SQL 2005.

I am getting an error with push merge with no way to figure out what is wrong.

I've configured replication on a single XP server in SQL 2005 RTM version.

I have a push merge set up between A and B and between B and C. All 3 databases are 9.0 compatibility.

The snapshot and merge jobs for the A to B run fine with no errors, and merge replicates ok.

The snapshot for B to C fails with this message:
Message
2006-03-09 17:30:35.94
2006-03-09 17:30:35.94 -BcpBatchSize 100000
2006-03-09 17:30:35.94 -HistoryVerboseLevel 2
2006-03-09 17:30:35.94 -LoginTimeout 15
2006-03-09 17:30:35.94 -QueryTimeout 1800
2006-03-09 17:30:35.94
2006-03-09 17:30:35.95 Connecting to Publisher 'MyInstance'
2006-03-09 17:30:35.97 Publisher database compatibility level is set to 90.
2006-03-09 17:30:35.97 Retrieving publication and article information from the publisher database 'MyInstance.MyDB'
2006-03-09 17:30:36.22 [0%] The replication agent had encountered an exception.
2006-03-09 17:30:36.22 Source: Replication
2006-03-09 17:30:36.22 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2006-03-09 17:30:36.22 Exception Message: Data is Null. This method or property cannot be called on Null values.
2006-03-09 17:30:36.22 Message Code: 52006
2006-03-09 17:30:36.22

Love that exception message: "Data is Null" - very helpful to someone who is clairvoyant perhaps.
I checked the snapshot bcp files. The tables being merged all have data.

A sample add article command is:

exec sp_addmergearticle @.publication = N'MyMerge', @.article = N'Phone', @.processing_order = 4, @.source_owner = N'dbo', @.source_object = N'Phone', @.type = N'table', @.description = null, @.column_tracking = N'true', @.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option = 0x000000004C42CDDF, @.article_resolver = null, @.subset_filterclause = null, @.vertical_partition = N'false', @.destination_owner = N'dbo', @.verify_resolver_signature = 0, @.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true', @.check_permissions = 0, @.identityrangemanagementoption = N'none' ,@.force_invalidate_snapshot = 1,@.force_reinit_subscription = 1

If you have any ideas on how to fix this, I'd be most grateful. As it is after 6pm I probably won't read this again until morning. Thanks for any suggestions.

Can you get the full stack trace from MSrepl_errors in the distribution database and post it here please? Thanks.

-Raymond

|||

Here you go:

Message: Data is Null. This method or property cannot be called on Null values.
Command Text: sp_MSestimatemergesnapshotworkload
Parameters: @.publication = StandardMerge Stack:
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate)
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.InitializeProgressTracker()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run()

Source: System.Data Target Site: Int32 get_Int32()
Message: Data is Null. This method or property cannot be called on Null values. Stack:
at System.Data.SqlClient.SqlBuffer.get_Int32()
at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
at Microsoft.SqlServer.Replication.Snapshot.ProgressTracker..ctor(SqlDataReader dataReader, SnapshotGenerationAgent snapshotGenerationAgent)
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.CreateProgressTracker(SqlDataReader dataReader)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteWithOptionalResults(CommandSetupDelegate commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32 queryTimeout, CommandBehavior commandBehavior)

|||

This looks like a bug, can you execute sp_MSestimatemergesnapshotworkload @.publication = 'StandardMerge' at the publisher database and post any rows with a null 'taskid' or 'taskload' value here. Thanks.

-Raymond

|||

Here they are. I've renamed the tables from their real names.

name taskid taskload Table 1 4 NULL Table 2 4 NULL Table 3 4 NULL Table 4 4 NULL Table 5 4 NULL Table 6 4 NULL Table 7 4 NULL Table 8 4 NULL Table 9 4 NULL Table 10 4 NULL Table 11 4 NULL Table 12 4 NULL Table 13 4 NULL

|||

By the way, for the same task id (4), for the A to B merge, these are the values (in case this is useful for comparison).

name taskid taskload Table 1 4 28 Table 2 4 41 Table 3 4 9 Table 4 4 2 Table 5 4 10 Table 6 4 20 Table 7 4 2 Table 8 4 5 Table 9 4 20 Table 10 4 37 Table 11 4 10 Table 12 4 27 Table 13 4 64

|||

Can you try the following query at the publisher database and see if it returns any rows?

select rowcnt from sysindexes where id = object_id(N'Table1') and indid in (0,1)

If the above query doesn't return any rows then this is basically the cause of the problem and it would be great if you can retry the query without the "and indid in (0,1)" part and report back whether there are any rows returned in that case. And if the publisher connection of the snapshot agent is not db_owner\sysadmin, you may want to temporarily change that and see if the problem goes away.

-Raymond

|||

The result from the original query is rowcnt = 195.

|||Also, this query and the prior queries were run using the login of the original script.|||

More info - in case it is useful

The parms passed for the merge publications:

For the AtoB merge (which works)
exec sp_addmergepublication @.publication = N'AtoB', @.description = N'Corporate Merge Replication', @.retention = 14, @.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'false', @.publication_compatibility_level = '90RTM', @.enabled_for_internet = N'false', @.conflict_logging = N'publisher', @.snapshot_in_defaultfolder = N'true', @.pre_snapshot_script = @.PreScript, @.post_snapshot_script = @.PostScript, @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.conflict_retention = 14, @.keep_partition_changes = N'false', @.allow_subscription_copy = N'false', @.allow_synctoalternate = N'false', @.add_to_active_directory = N'false', @.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0

exec sp_addpublication_snapshot @.publication = N'AtoB',@.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 1, @.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 500, @.active_end_time_of_day = 235959, @.snapshot_job_name = @.MyName

exec sp_addmergesubscription @.publication = N'AtoB', @.subscriber = @.MyInstance, @.subscriber_db = N'SiteMaster', @.subscription_type = N'push', @.subscriber_type = N'global', @.subscription_priority = 75.0, @.sync_type = N'automatic', @.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false', @.offloadagent = 0, @.use_interactive_resolver = N'false', @.merge_job_name = @.Myname

For the BtoC merge (which doesn't)
exec sp_addmergepublication @.publication = N'BtoC', @.description = N'Facility Master Merge Replication', @.retention = 14, @.sync_mode = N'native', @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous = N'false',@.publication_compatibility_level = '90RTM', @.enabled_for_internet = N'false', @.conflict_logging = N'publisher', @.snapshot_in_defaultfolder = N'true', @.pre_snapshot_script = @.PreScript, @.post_snapshot_script = @.PostScript, @.compress_snapshot = N'false', @.ftp_port = 21, @.ftp_login = N'anonymous', @.conflict_retention = 14, @.keep_partition_changes = N'false', @.allow_subscription_copy = N'false', @.allow_synctoalternate = N'false', @.add_to_active_directory = N'false', @.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0

exec sp_addpublication_snapshot @.publication = N'BtoC',@.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 1, @.frequency_subday_interval = 5, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 500, @.active_end_time_of_day = 235959, @.snapshot_job_name = @.Myname

exec sp_addmergesubscription @.publication = N'BtoC', @.subscriber = @.MyInstance, @.subscriber_db = @.MyFacility, @.subscription_type = N'push', @.subscriber_type = N'local', @.subscription_priority = 0.0, @.sync_type = N'automatic', @.frequency_type = 4, @.frequency_interval = 1, @.frequency_relative_interval = 1, @.frequency_recurrence_factor = 0, @.frequency_subday = 8, @.frequency_subday_interval = 1, @.active_start_date = 0, @.active_end_date = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.enabled_for_syncmgr = N'false', @.offloadagent = 0, @.use_interactive_resolver = N'false', @.merge_job_name = @.MyName

|||

This looks really weird, can you run sp_helpmergearticle @.publication =N'BtoC' at B and report back whether the source_object and source_owner columns are NULLs? Thanks much.

-Raymond

|||

Raymond,

I get nothing back from that at all. When I run it on the AtoB pub,run at A, the result looks normal. But nothing at all comes back for BtoC publication, run at B.

|||

This is interesting:

When I run EXEC dbo.sp_helpmergepublication at A, I get priority = 100.

When I run the same thing at B, I also get priority = 100.

You can see in my sp_addmergesubscription for A, I had set priority to 75.0 and that for B I'd set it to 0.0 so SQL is ignoring those parms I guess.

|||

Ok, looks like your publication\article metadata is very messed up at B. To confirm my suspicion, can you try the following query at B:

select name, pubid, objid, object_name(objid) from dbo.sysmergeextendedarticlesview

I am interested to know if any values in the last column are null or not. If there are any null values, then your article meta-data is seriously corrupted. You can, of course, recover from this particular problem by recreating the publication at B but given the kind of problems you have experienced in the past, I strongly suspect some other background operations at B is what is causing the problems in the first place. Here are a couple of possibilities that I can think of:

1) Someone tried to renamed the published table at B

2) Someone tried to drop and recreate the published table at B and use sp_MSunmarkreplino to allow the operation to occur.

3) The merge agent has a problem updating the object ids of published tables at B when a snapshot is delivered from A to B.

4) You have included sysmergearticles and\or sysmergepublications as part of the publication from A to B.

HTH

-Raymond

|||

I ran it (ordered by 2,1) and all the articles for B publication have NULL in the last column.

As for the possibilities, nobody but me has done anything to this data.

1) haven't done

2) I ran that once a week ago when I was first putting this stuff together and trying to clean stuff up with another problem I have with transactional replication getting a 3724 error ("Cannot drop the table 'dbo.Mytablename' because it is being used for replication.) Would that still be messing me up after many builds since then?

3) At the moment, I believe everything is running under the SQL Agent Svc login.

4) Those are not in my 13 explicitly named sp_addmergearticle articles.

Also, per priority difference mentioned earlier. I ran sp_helpmergesubscription and the priorities were as I expected. So why are the sp_helpmergepublication priorities 100?

Pulling data from 2 tables, 1 with possibly multiple records

I'm trying to create a view which pulls data from the following 2 tables:
CREATE TABLE PC (
PCName varchar(50) NOT NULL,
Make varchar(50),
Model varchar(50),
SerialNumber varchar(50)
PRIMARY KEY
(PCName)
)
INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST1', 'Dell',
'OptiPlex GX1', '12345')
INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST2', 'Dell',
'PowerEdge 6450', '23456')
CREATE TABLE CPU (
PCName varchar(50) NOT NULL REFERENCES PC(PCName),
Row int NOT NULL,
Type varchar(50),
Speed int
PRIMARY KEY
(PCName, Row)
)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST1', 1, 'Pentium
III', 1000)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 1, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 2, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 3, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 4, 'Pentium III
Xeon', 700)
The CPU table holds an entry for each CPU in the PC. Now I want the view to
retrieve 1 row per PC and look like this...
PCName Make Model SerialNumber Type
Speed NumberOfCPUs
========================================
==============================
TEST1 Dell OptiPlex GX1 12345 Pentium III
1000 1
TEST2 Dell PowerEdge 6450 23456 Pentium III
Xeon 1200 4
... where NumberOfCPUs is the max(Row) for each particular PC.
Any ideas? Unfortunately we are stuck with the table structure as is (from
a 3rd party).
ThanksJim
Thanks for posting DDL
See , if this helps you
SELECT PC.PCName,Make,Model,SerialNumber,
Type, Speed,NumberOfCPUs FROM PC JOIN
(
SELECT MAX(Row)NumberOfCPUs,PCName,Type,Speed FROM CPU
GROUP BY PCName,Type,Speed
) AS Der ON PC.PCName=Der.PCName
"Jim Coyne" <REcoyneMO_jimVE@.hoMEtmail.com> wrote in message
news:usUSwQlvFHA.2072@.TK2MSFTNGP14.phx.gbl...
> I'm trying to create a view which pulls data from the following 2 tables:
> CREATE TABLE PC (
> PCName varchar(50) NOT NULL,
> Make varchar(50),
> Model varchar(50),
> SerialNumber varchar(50)
> PRIMARY KEY
> (PCName)
> )
> INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST1', 'Dell',
> 'OptiPlex GX1', '12345')
> INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST2', 'Dell',
> 'PowerEdge 6450', '23456')
> CREATE TABLE CPU (
> PCName varchar(50) NOT NULL REFERENCES PC(PCName),
> Row int NOT NULL,
> Type varchar(50),
> Speed int
> PRIMARY KEY
> (PCName, Row)
> )
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST1', 1, 'Pentium
> III', 1000)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 1, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 2, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 3, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 4, 'Pentium
> III Xeon', 700)
>
> The CPU table holds an entry for each CPU in the PC. Now I want the view
> to retrieve 1 row per PC and look like this...
> PCName Make Model SerialNumber Type Speed
> NumberOfCPUs
> ========================================
==============================
> TEST1 Dell OptiPlex GX1 12345 Pentium
> III 1000 1
> TEST2 Dell PowerEdge 6450 23456 Pentium III
> Xeon 1200 4
>
> ... where NumberOfCPUs is the max(Row) for each particular PC.
> Any ideas? Unfortunately we are stuck with the table structure as is
> (from a 3rd party).
> Thanks
>|||Yes, that certainly does. Thank you very much.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uMIH0gmvFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Jim
> Thanks for posting DDL
> See , if this helps you
>
> SELECT PC.PCName,Make,Model,SerialNumber,
> Type, Speed,NumberOfCPUs FROM PC JOIN
> (
> SELECT MAX(Row)NumberOfCPUs,PCName,Type,Speed FROM CPU
> GROUP BY PCName,Type,Speed
> ) AS Der ON PC.PCName=Der.PCName
>
> "Jim Coyne" <REcoyneMO_jimVE@.hoMEtmail.com> wrote in message
> news:usUSwQlvFHA.2072@.TK2MSFTNGP14.phx.gbl...
>

Pull Subscription

I'm trying to pull new subscription from another server, but for some reason
my attemp fails. The error I get is Error 15004: Name cannot be NULL. Can
someone tell me how can I resolve this issue.
Thanks
what do you get when you issue a select @.@.servername on the publisher and
subscriber?
If you get (NULL) follow the instructions here for more information.
http://support.microsoft.com/defaul..kb;en-us;302223
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BigRome" <BigRome@.discussions.microsoft.com> wrote in message
news:401552C3-6C3D-482F-94A8-CD57E8B164C3@.microsoft.com...
> I'm trying to pull new subscription from another server, but for some
reason
> my attemp fails. The error I get is Error 15004: Name cannot be NULL. Can
> someone tell me how can I resolve this issue.
> Thanks