more problems with unique sequences

I have another different schema with the same data, but this one its like this

Code Snippet

CREATE Table events (
id INT not null,
PxMiss Real Not Null,
PyMiss Real Not Null,
filenames Varchar(50));

ALTER TABLE events
ADD CONSTRAINT pk_particle PRIMARY KEY (id,filenames);

GO

CREATE Table Muon (
idap INT Not Null,
id INT Not Null,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

CREATE Table Electron(
idap INT Not Null,
id INT NOT NULL,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

CREATE Table Jet (
idap INT Not Null,
id INT NOT NULL,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

Create View lepton AS
select * from Muon
Union all
select * from Electron;
GO

Create View particle AS
select * from lepton
Union all
select * from Jet;
GO

I need that every particle had a different idap, but all the date is filled in muon, electron and jet. and then is joined in a view called particle.

The way that you are going about this is likely to cause some problems.

For example, while it is possible to create a VIEW that would provide a unique idap for for each Particle, since it is a view and will be re-constituted at every execution, there is no certainity that the idap will be the same at each execution. (In my opinion -that is a big issue. -but maybe not for your situation...)

It seems more stable if you were to create a Particle idap that was the combination of each constituent Identifier + idap. Something like this for example:

CREATE VIEW Particle
AS

SELECT
'M' + cast( idap AS varchar(12)),
id,
eventid,
Px,
Py
Pz
Kf,
Ee
FROM Muon

UNION ALL

SELECT
'E' + cast( idap AS varchar(12),
id,
eventid,
Px,
Py,
Pz,
Kf,
Ee
FROM Electron

UNION ALL

SELECT
'J' + cast( idap AS varchar(12),
id,
eventid,
Px,
Py,
Pz,
Kf,
Ee
FROM Jet
GO

With this example, each idap will be unique, and also give you a clue about the constitutent component.

more info

If I just do a regular Subscription, allowing a new table
to be created a regular way, with the same schema as the
Publisher and creating its own procs and everything, the
snapshot looks alot different. When I do all my custom
stuff, the snapshot data file gets created with spaces in
the words. So "bla" becomes "b l a". When I don't do my
custom stuff, "bla" stays "bla". Don't know if this is
causing my prob, but thought it would be worth mentioning.

>--Original Message--
>sql2k sp3
>Howdy kids. Im using Custom Sync Objects for Replication.
>The Subscriber has a different schema than the Publisher.
>(more columns) So I use sp_addarticle to create the
>article, @.creation_script to create the table,
and "before
>applying the snapshot, apply this script" to create the
>insert Stored Proc. The snapshot runs. The table and proc
>get created correctly. (In the format of the Subscriber.)
>However, I still get an error:
>The process could not bulk copy into table '"transdtl"'.
>Unexpected EOF encountered in BCP data-file
>(Source: NECDEVSQL1 (ODBC); Error number: S1000)
>Below are the scripts neccessary to duplicate my
>environment.
>
>--sync view
>create view SyncTransDTL
>as select
> TransDtlKey ,
> CustomerKey ,
> SerialNbr ,
> TranCode ,
> TransDate ,
> TransDateShort = Convert(varchar(10), TransDate, 101),
> TransDateMonth = Month(TransDate),
> TransDateYear = Year(TransDate),
> TransAmt ,
> RefNbr ,
> MerchName ,
> City ,
> State ,
> RejectReason ,
> PostDate ,
> PostDateShort = Convert(varchar(10), PostDate, 101),
> PostDateMonth = Month(PostDate),
> PostDateYear = Year(PostDate),
> CreateDate ,
> MerchSIC
>from dbo.transdtl
>--article
>sp_addarticle @.publication = 'transdtl'
> , @.article = 'transdtl'
> , @.source_table = 'transdtl'
> , @.destination_table = 'transdtl'
> , @.type = 'logbased manualview'
> , @.sync_object = 'SyncTransDTL'
> , @.Creation_Script = '\\necdevsql2
>\d$\Replication\CreateTables.txt'
>,@.schema_option = 0x00
>,@.status = 8
>,@.ins_cmd = 'CALL sp_MSins_TransDTL'
>,@.del_cmd = 'CALL sp_MSdel_TransDTL'
>,@.upd_cmd = 'MCALL sp_MSupd_TransDTL'
>
>--create table script
>CREATE TABLE [dbo].[TransDtl] (
> [TransDtlKey] [int] NOT NULL ,
> [CustomerKey] [int] NULL ,
> [SerialNbr] [char] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranCode] [char] (4) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDate] [smalldatetime] NULL ,
> [TransDateShort] [char] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDateMonth] [tinyint] NULL ,
> [TransDateYear] [smallint] NULL ,
> [TransAmt] [money] NULL ,
> [RefNbr] [char] (23) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [MerchName] [varchar] (25) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [varchar] (15) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [State] [varchar] (3) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [RejectReason] [varchar] (15) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDate] [datetime] NULL ,
> [PostDateShort] [char] (10) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDateMonth] [tinyint] NULL ,
> [PostDateYear] [smallint] NULL ,
> [CreateDate] [datetime] NULL ,
> [MerchSIC] [char] (4) COLLATE
>SQL_Latin1_General_CP1_CI_AS NULL
>) ON [PRIMARY]
>GO
> CREATE CLUSTERED INDEX [IX_TransDtl_TransDate] ON
[dbo].
>[TransDtl]([TransDate]) WITH FILLFACTOR = 100 ON
[PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl_PostDate] ON [dbo].[TransDtl]
>([PostDate]) WITH FILLFACTOR = 100 ON [PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl_CustomerKey] ON [dbo].
>[TransDtl]([CustomerKey]) WITH FILLFACTOR = 100 ON
>[PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl_RefNo] ON [dbo].[TransDtl]
>([RefNbr]) WITH FILLFACTOR = 100 ON [PRIMARY]
>GO
> CREATE INDEX [IX_TransDtl_SerialNbr] ON [dbo].[TransDtl]
>([SerialNbr]) WITH FILLFACTOR = 100 ON [PRIMARY]
>GO
>/****** The index created by the following statement is
>for internal use only. ******/
>/****** It is not a real index but exists as statistics
>only. ******/
>if (@.@.microsoftversion > 0x07000000 )
>EXEC ('CREATE STATISTICS [Statistic_MerchSIC] ON [dbo].
>[TransDtl] ([MerchSIC]) ')
>GO
>
>--insert proc script
>create procedure sp_msIns_TransDtl
> @.TransDtlKey int ,
> @.CustomerKey int ,
> @.SerialNbr char (10) ,
> @.TranCode char (4) ,
> @.TransDate smalldatetime ,
> @.TransDateShort char (10) ,
> @.TransDateMonth tinyint ,
> @.TransDateYear smallint ,
> @.TransAmt money ,
> @.RefNbr char (23) ,
> @.MerchName varchar (25) ,
> @.City varchar (15) ,
> @.State varchar (3) ,
> @.RejectReason varchar (15) ,
> @.PostDate datetime ,
> @.PostDateShort char (10) ,
> @.PostDateMonth tinyint ,
> @.PostDateYear smallint ,
> @.CreateDate datetime ,
> @.MerchSIC char (4)
>as
>insert into TransDTL
>(
> TransDtlKey ,
> CustomerKey ,
> SerialNbr ,
> TranCode ,
> TransDate ,
> TransDateShort ,
> TransDateMonth ,
> TransDateYear ,
> TransAmt ,
> RefNbr ,
> MerchName ,
> City ,
> State ,
> RejectReason ,
> PostDate ,
> PostDateShort ,
> PostDateMonth ,
> PostDateYear ,
> CreateDate ,
> MerchSIC
>)
>values
>(
> @.TransDtlKey ,
> @.CustomerKey ,
> @.SerialNbr ,
> @.TranCode ,
> @.TransDate ,
> @.TransDateShort ,
> @.TransDateMonth ,
> @.TransDateYear ,
> @.TransAmt ,
> @.RefNbr ,
> @.MerchName ,
> @.City ,
> @.State ,
> @.RejectReason ,
> @.PostDate ,
> @.PostDateShort ,
> @.PostDateMonth ,
> @.PostDateYear ,
> @.CreateDate ,
> @.MerchSIC
>)
>.
>
Chris,
I used your script and after running sp_refreshsubscriptions everything
worked ok. Spaces get created between letters if I use native format, but
just to check if this was a problem, I tried once with native and once with
character format and both worked fine. I added the stored procedure to the
subscriber by hand, but apart from that it was all the same. Can you try
without the stored proc and use native format, just to check that the bcp is
the same style as the one you are used to? If this still errors, please can
you export some data to a csv file and post it up, just so I am using more
or less the same data.
TIA,
Paul Ibison
|||Paul as usual I appreciate your insights. Character format
wont work as then the option "before applying the
snapshot, exec this script" is not available. Which made
me realize, I also didnt post the schema for the Publisher:
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TranCode] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TransDate] [smalldatetime] NOT NULL ,
[TransAmt] [money] NOT NULL ,
[RefNbr] [char] (23) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MerchName] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[City] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectReason] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL ,
[MerchSIC] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TransDtl] WITH NOCHECK ADD
CONSTRAINT [PK_TransDtl] PRIMARY KEY CLUSTERED
(
[TransDtlKey]
) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
ALTER TABLE [dbo].[TransDtl] ADD
CONSTRAINT [DF_TransDtl_CreateDate] DEFAULT
(getdate()) FOR [CreateDate]
GO
CREATE INDEX [IX_TransDtl_PostDate] ON [dbo].[TransDtl]
([PostDate]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO
CREATE INDEX [IX_TransDtl_SerialNbr] ON [dbo].[TransDtl]
([SerialNbr]) WITH FILLFACTOR = 85 ON [PRIMARY]
GO
CREATE INDEX [IX_TransDtl_TransDate_TranCode] ON [dbo].
[TransDtl]([TransDate], [TranCode]) WITH FILLFACTOR = 95
ON [PRIMARY]
GO

>--Original Message--
>Chris,
>I used your script and after running
sp_refreshsubscriptions everything
>worked ok. Spaces get created between letters if I use
native format, but
>just to check if this was a problem, I tried once with
native and once with
>character format and both worked fine. I added the stored
procedure to the
>subscriber by hand, but apart from that it was all the
same. Can you try
>without the stored proc and use native format, just to
check that the bcp is
>the same style as the one you are used to? If this still
errors, please can
>you export some data to a csv file and post it up, just
so I am using more
>or less the same data.
>TIA,
>Paul Ibison
>
>.
>
|||Paul attached is the data Im using. Ive also attached the snapshot file
which I noticed only includes one of the two rows of data in the table.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%239dkpX%23gEHA.592@.TK2MSFTNGP11.phx.gbl...
> Chris,
> I used your script and after running sp_refreshsubscriptions everything
> worked ok. Spaces get created between letters if I use native format, but
> just to check if this was a problem, I tried once with native and once
with
> character format and both worked fine. I added the stored procedure to the
> subscriber by hand, but apart from that it was all the same. Can you try
> without the stored proc and use native format, just to check that the bcp
is
> the same style as the one you are used to? If this still errors, please
can
> you export some data to a csv file and post it up, just so I am using more
> or less the same data.
> TIA,
> Paul Ibison
>
begin 666 Transdtl.CSV
M,2PQ+#$@.(" @.(" @.(" L=&5S="PR,# T+3 Q+3 Q(# P.C P.C P+#$N,# P
M,"QT97-T(" @.(" @.(" @.(" @.(" @.(" @.("QT97-T+'1E<W0L=&5S+'1E<W0L
M,C P-"TP,2TP,2 P,#HP,#HP,"XP,# L,C P-"TP,2TP,2 P,#HP,#HP,"XP
M,# L=&5S= T*,BPQ+#$@.(" @.(" @.(" L8FQA("PR,# T+3 Q+3 Q(# P.C P
M.C P+#$N,# P,"QT97-T(" @.(" @.(" @.(" @.(" @.(" @.("QT97-T+'1E<W0L
M=&5S+'1E<W0L,C P-"TP,2TP,2 P,#HP,#HP,"XP,# L,C P-"TP,2TP,2 P
2,#HP,#HP,"XP,# L=&5S= T*
`
end
begin 666 transdtl_0.bcp
M`0````0!````,0`@.`" `( `@.`" `( `@.`" `( !T`&4`<P!T`&&4```4`# `
M,0`O`# `,0`O`#(`, `P`#0`! $````$U <````````0)P``= !E`',`= `@.
M`" `( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.``@.`= !E`',`
M= `(`'0`90!S`'0`!@.!T`&4`<P`(`'0`90!S`'0`890````````4 `# `,0`O
M`# `,0`O`#(`, `P`#0`! $````$U <``&&4````````" !T`&4`<P!T``(`
M```$`0```#$`( `@.`" `( `@.`" `( `@.`" `8@.!L`&$`( !AE ``% `P`#$`
M+P`P`#$`+P`R`# `, `T``0!````!-0'````````$"<``'0`90!S`'0`( `@.
M`" `( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `(`'0`90!S`'0`
M" !T`&4`<P!T``8`= !E`',`" !T`&4`<P!T`&&4````````% `P`#$`+P`P
I`#$`+P`R`# `, `T``0!````!-0'``!AE ````````@.`= !E`',`= ``
`
end

Monitor show distribution agent that was removed.

While setting up transaction replication from 7 to 2000 I generate a push
subscription that failed to copy the initial schema over. I deleted the
subscription that had caused the problem and started another, that finished
okay and is replicating now. However in the Replication
Monitor-Publishers-servername-publicationname I have the failed agent from
the initial subscription and can not remove it. The same thing applies to
the Replication Monitor-Agents-Distribution Agents. It just won't go away.
SQL7 is the publisher and distributor as well as pushing a subscription to
the SQL2000 box.
Ant help would be appreciated.
Hi Charles,
From your descriptions, I understood that you would like to delete failed
agent but failed to do so. Have I understood you? If there is anything I
misunderstood, please feel free to let me know
Based on my knowledge, when you try to delete them, what's the error
message that shows you are denied to do so? More detailed information will
get us closer to the resolutions.
Secondly, I would like to strongly recommand you the following document,
which show you how to remove a replication manually
How to manually remove a replication in SQL Server 2000
http://support.microsoft.com/?id=324401
Hope this helps, if you have any questions or concerns, don't hesitate to
let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Mingqing,
I can't delete the failed subscription because it does not appear in the
list when I look in the list of subscriptions, the only place that it
appears is in the Monitor. There no way to delete it as far as I know other
than from with in the Publication Properties-Subscriptions and it does not
appear there. The current work subscription is there.
Charlie
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:uLj4km2TEHA.2436@.cpmsftngxa10.phx.gbl...
> Hi Charles,
> From your descriptions, I understood that you would like to delete failed
> agent but failed to do so. Have I understood you? If there is anything I
> misunderstood, please feel free to let me know
> Based on my knowledge, when you try to delete them, what's the error
> message that shows you are denied to do so? More detailed information will
> get us closer to the resolutions.
> Secondly, I would like to strongly recommand you the following document,
> which show you how to remove a replication manually
> How to manually remove a replication in SQL Server 2000
> http://support.microsoft.com/?id=324401
> Hope this helps, if you have any questions or concerns, don't hesitate to
> let me know. We are here to be of assistance!
> Sincerely yours,
> Mingqing Cheng
> Microsoft Online Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
|||Hi Charlie,
Thanks for your prompt updates!
Based on my knowledge, the list of Agents being displayed in Replication
Monitor are stored in the Tempdb database in the MSreplication_agent_status
table. You could removing a "left over" agent from this table will delete
it from Replication Monitor listing. You can delete any Agent from this
table without causing problems to Replication.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Mingqing,
Thanks for your reply. But it looks like it cleared it's self up over the
weekend.
Charles Deaton
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:W%2345a5fUEHA.432@.cpmsftngxa10.phx.gbl...
> Hi Charlie,
> Thanks for your prompt updates!
> Based on my knowledge, the list of Agents being displayed in Replication
> Monitor are stored in the Tempdb database in the
MSreplication_agent_status
> table. You could removing a "left over" agent from this table will delete
> it from Replication Monitor listing. You can delete any Agent from this
> table without causing problems to Replication.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Microsoft Developer Community Support
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>