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

More Control over Data Driven Subscription email content

I really like the data driven subscription functionality but I'm having
trouble with the nitty gritty of customizing subsctiption email content.
Here's my example - we send out a report each week to those who have not
booked time against our project management system. I want to include a
hyperlink in the comments section of the email that takes the recipient to
their timesheet. I thought this would be simple because RS has the ability
to include a hyperlink to the report itself.
In addition, I can't seem to access any reportserver global values other
than the report execution time and report name (these are included in the
MSDN walk-throughs as well). Does anyone know what parameters, etc. are
available to be parsed into a subscription email?
Has anyone had luck adding formatting like carriage returns, etc. to the
email comments section? I've tried ascii values but no luck.Hi John,
An option for this problem would be to create a report that is parameterised
per person. This report could contain some text to explain that time needs to
be captured and you could add an action on a text box using the current
person name (or id) that would link to the timesheet system.
Your subscription could then mail out rendered versions of these reports in
HTML format which would be embedded in the body of the email. From a users
point of view, it would do the trick.
To the best of my knowledge, the only two variables available via the
subscription engine are the two listed below. Maybe an option for SP3 is to
include column values from the subscription table as variables?
Lastly, to do fancy formatting in the comments section you need to embed
HTML tags. So, your new line would need the <BR> or <P> tag. (of course, this
is also an option to include a link in the mail that would link to the login
page of your timesheet system...)
"john" wrote:
> I really like the data driven subscription functionality but I'm having
> trouble with the nitty gritty of customizing subsctiption email content.
> Here's my example - we send out a report each week to those who have not
> booked time against our project management system. I want to include a
> hyperlink in the comments section of the email that takes the recipient to
> their timesheet. I thought this would be simple because RS has the ability
> to include a hyperlink to the report itself.
> In addition, I can't seem to access any reportserver global values other
> than the report execution time and report name (these are included in the
> MSDN walk-throughs as well). Does anyone know what parameters, etc. are
> available to be parsed into a subscription email?
> Has anyone had luck adding formatting like carriage returns, etc. to the
> email comments section? I've tried ascii values but no luck.

Monitoring Merge Push Subscription from Subscriber

Hi all!

Is there an option to monitor current state of Merge Push Subscription from the Subscriber, without connecting to the Publisher Server? I have examined many SPs and system tables at Subscriber, but didn't find any reliable method...

What we do is record the current UTC time every time the sync job completes successfully. Then another job which sends out an alert if the last sync time is too far out of date. However another thing you can do is look at MSMerge_genhistory. There is a genstatus in there 1 or 0 for delivered or not delivered. If the last genstatus is still zero and the coldate is X number of hours out of date then you can send out some alerts.

Martin

|||

Thanks, Martin!

I also discovered, that table sysmergesubscriptions at Subscriber contains some information about when the last synchronization occured and it's message.

MOM Subscription errors

I have a few MOM reports that give the following error when I try to
set up a new subscription. I have previously been able to subscribe to
the reports, but now we get this error.
"There is an error in XML document (1,50623)"
I have seen couple of other posts on this, but never an answer or
solution. I can open and run the report in Visual Studio, but even if I
republish them to another folder they still fail. I can see the error
in the logs telling me I have an illegal character, but other than
validating the error, the log does not provide useful info.
THis is happening on more than one MOM report, but only on MOM reports.
Anyone know what is happening'?Had to contact MS. Of course there is a bug that requires a HOT FIX
that needs to be run on the MOM server. Turns out somehow there are
non-printable characters being populated in the data the reports are
using for paramaters to the reports.
The RS log files shows that there is an illegal character but does not
tell you where it is. I finally stumbled upon the illegal character
when looking at the drop down list boxes on the reports. They are of
course non-printable characters so you will not see them in the results
of a SQL select statement.
Here is the article that outlines the problem.
http://support.microsoft.com/?id=915785#XSLTH3120121123120121120120
Article ID : 915785