Can I delete Transaction log on subscriber

Hi,
One of our subscriber server is full due to transaction
log too large. The replication was failed without no
space for the transaction log. My question is can I
delete the transation log? Or is there any way I can free
some space on that subscriber server? Please help me.
p.s. we do full backup on our publisher server every day.
Thanks,
While you can delete it I would urge you not to do this. By shutting down
SQL Server and deleting the actual log physical file is a bad idea, although
in some people have claimed to be able to do it, I have never been able to.
I hope you mean truncating it and perhaps shrinking the log file. This is a
better option.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Ping" <anonymous@.discussions.microsoft.com> wrote in message
news:228501c506fc$0ecd0690$a401280a@.phx.gbl...
> Hi,
> One of our subscriber server is full due to transaction
> log too large. The replication was failed without no
> space for the transaction log. My question is can I
> delete the transation log? Or is there any way I can free
> some space on that subscriber server? Please help me.
> p.s. we do full backup on our publisher server every day.
> Thanks,
|||Hi hilary,
Thanks for your reply.
Regarding truncating and shrinking, which one do you
recommend?
Tks
>--Original Message--
>While you can delete it I would urge you not to do this.
By shutting down
>SQL Server and deleting the actual log physical file is a
bad idea, although
>in some people have claimed to be able to do it, I have
never been able to.
>I hope you mean truncating it and perhaps shrinking the
log file. This is a
>better option.
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>"Ping" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:228501c506fc$0ecd0690$a401280a@.phx.gbl...
free[vbcol=seagreen]
day.
>
>.
>

Can i apply any conditon on Replication ,before publishing the data?

i want to use Replication in sql server 2000 at specified condtion ,mean if any condition is satisfied then the data is publish to subscriber database?Extracted from BOL:


Filtering Published Data

Filtering data during replication allows you to publish only the data or partitions of data that are needed at the Subscriber. You can filter data to create partitions that include only the columns and/or only the rows that you specify for replication.
With all types of replication, you can choose to copy and distribute complete tables, or data filtered horizontally or vertically with static filters. Merge replication (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\replsql.chm::/replintro_0odv.htm#sql:merge_replication) is especially strong in filtering options, and you can use dynamic filters to customize the filter based on a property of the Subscriber receiving the data.
Filtering data horizontally allows you to publish only the data that is needed, partition data to different sites, avoid conflicts (because Subscribers will be viewing and updating different subsets of data), and manage publications based on user needs or applications.
Additionally, you have the option of employing user-defined functions in your static and dynamic filters and leveraging the power of customized functions.|||Thanks Tomh,i got the idea what you are trying to say, this can be done from filtering horizontally or vertically option when we Select Yes, I will define data filters, enable anonymous subscriptions, or customize other properties.

i am explaining what i want to do,i want to apply condition let suppose:

if users.usersId between 10 and 20
begin

-- replication perform

end

and u said in ur comments we can also use or apply user defined function can u give me example of it and where should i call this UDF.

thanks in advance :)|||I've never had to use UDFs to filter, so I don't have an example to give you.

I would suggest you do two things:

1. Experiment ... set up a test replication and work thru simple horizontal (your userid example) and vertical filtering (column subset), then when you have that working, try incorporating functions into the filtering.

2. Search the microsoft.public.sqlserver groups for more information, as well as use GOOGLE to search for user written articles to help lead you to your final result.

You will notice that most of the answers on this forum come from members working thru real life situations that have been overcome by testing and finding the many different ways things don't work before finding the one that does.

Join us and be part of the solution!|||I think a horizontally filtered replication (either merge or transactional) would work just fine for you in this situation. It seems that it would be a bit simpler since it's not dynamic (or it does not appear to be from your description).

As tomh suggested, set up a test environment and practice with it. Replication can be tedious and very involved. Learn all you can, test variouse scenarios, document the test conditions and outcomes and develop your plans accordingly.

Regards,

hmscott|||if users.usersId between 10 and 20
begin

-- replication perform

end

if users.usersId between 10 and 20
begin

exec msdb..sp_start_job 'replicationJobName'
end|||Hy guys it works when i did sample example to test data for (merge replication), from row filter and dynamic row filter we can apply conditions (like where clause)

Thnx to all u guys ur guidance very valueable for me thnx to all once again

one thing which would i want to know is that like cfr want to say to write UDF in a specific condition ,i am unable to do like this Guys do u have any idea? where i should call UDF ?

Can Data Partitions be used with associative tables?

First of all, we are using SQL Server 2005 with a SQL Mobile subscriber and we are attempting to use Data Partitions on our current database

schema which contains associative tables for many-to-many relationships.

We have two tables, a User table

and an Audit table.A user can be

assigned more than one Audit.An Audit

can be assigned to more than one User.

So an AuditUser associative table exists.If data partitions are used based on User,

then any Audits that are assigned to one or more users should be copied to the

proper partition for each User (the msmerge_current_partition_mappings table

with the proper partition_id values).

In order to insert records with such a schema, the following

steps occur in order:

1. Insert

   new row into Audit table with new rowguid

2. Insert

   entry into AuditUser table associating the auditguid with every userguid that

   is assigned this audit.

Merge replication triggers are fired on insert of the Audit

row and another one for the insert of the AuditUser row.

When the Audit row is inserted, the replication trigger follows

the following logic:

1. Inserts

   a copy of that row into the msmerge_contents table.

2. Evaluates

   the row to determine which partition(s) this row should be copied to as

   well (msmerge_current_partition_mappings table).To do this, it checks to see if the

   AuditGuid is referenced in one or more AuditUser rows.Since we haven’t inserted the AuditUser

   row at this point, the trigger’s logic doesn’t find a partition to copy

   this row to.

When the AuditUser row is inserted, the replication trigger performs

the same logic as with the Audit row, it:

1. Inserts

   a copy of that row into the msmerge_contents table.

2. Evaluates

   the row to determine which partition(s) this row should be copied to as

   well (msmerge_current_partition_mappings table).Since the row meets the criteria for one

   or more partitions, it is copied to the msmerge_current_partition_mappings

   table for each partition that exists.

When replication occurs, we see only the AuditUser rows

copied down to our device, and not the corresponding Audit rows.Now that we understand the triggers, it is

plain to see why.If the AuditUser row

could be inserted first, then the trigger on the Audit row would copy that row

into the proper partitions and all would work well.However, the Audit row must be inserted

first, so that foreign key relationship constraints are preserved.

It seems that the Update trigger on the AuditUser row

actually walks the relationships and copies any related child rows to the

msmerge_current_partition_mappings table.

The answer is YES! I'm posting a followup as we've discovered what our problem turned out to be. In the end, it was a security issue - which was not straightforward at all!

First a quick recap:

When attempting to replicate data using merge replication and data

partitions with a schema that contains an associative table, only the

associative table row was being replicated. Given the following table

relationships:

[User] <-> [AuditUser] <-> [Audit]

It is necessary to insert the row into the Audit table first before

inserting the row into the AuditUser table (assuming that the [User] table is

somewhat fixed in this scenario). Because the

msmerge_current_partition_mappings table is maintained on INSERT triggers, when

the trigger fires for the Audit table, it does not determine that the row meets

the filter criteria, because that criteria is based on a User and since the

AuditUser row hasn’t been inserted yet, the Audit row is not seen as belonging

to user’s partition and thus is not copied to the

msmerge_current_partition_mappings table.

When the AuditUser row is inserted next, it easily passes the

filter test and is copied to the msmerge_current_partition_mappings table. To

compensate for the fact the Audit row was missed, this trigger rescans related

rows and attempts to copy any related rows that match this AuditUser row over

to the msmerge_current_partition_mappings table (provided those rows meet

filter criteria set on those tables). It does this by checking to see if those

rows exist in a View that was created based on the filters for that table. Such

a View for our Audit table is:

Replication View for our filtered Audit table:

create view dbo.[MSmerge_Audit_Audit_PARTITION_VIEW] as select

[Audit].[Guid], [Audit].[SystemGuid], [Audit].[StatusGuid], [Audit].[Active],

[AuditUser].partition_id from [dbo].[Audit] [Audit] ,

[dbo].[MSmerge_Audit_AuditUser_PARTITION_VIEW] [AuditUser] where ( (

[AuditUser].[AuditGuid] = [Audit].[Guid]

AND ([Audit].[StatusGuid] = '73fbcc34-260e-430f-bda6-fd6bdf944d85'

OR [Audit].[StatusGuid] = '0B7E27FA-712F-455D-A651-B3C6EC815EE75')

AND [Audit].[Active] = 1

AND [Audit].[SystemGuid] in (SELECT guid FROM [System] WHERE Active

= 1)) ) and ({fn ISPALUSER('E65FCB7D-BD91-480A-8D02-F8101DA974FE')} = 1 or

permissions(469576711) & 0x1b <> 0)

When looking at the how this view is constructed, we noticed that

aside from the defined filter information, an ‘and’ clause is tacked on that

restricts the rows returned based upon the user’s inclusion in the Publication

Access List.

Our production and test environment is set up in the following

manner:

IIS on machine 1

SQL Server 2005 on machine 2

IIS is set to use a domain account called WebReplication that has

access to the Publication (is in the Publication Access List) and has access

the shared UNC repldata directory. However, our website users run under

different domain account, WebUser, which we never granted access to the

Publication. Since the WebUser account wouldn’t be replicating, it didn’t seem

necessary to give that user access to the publication. But without access to

the publication, the WebUser doesn’t have rights to see the data in the View

generated above. So, the trigger determines that there are no child rows

(Audit) that meet the filter criteria, and thus doesn’t copy the related child

rows to the msmerge_current_partition_mappings table.

The result is exactly what we’ve seen: only the AuditUser rows are

replicated and not the Audit rows that relate to them! By adding the WebUser

account to the Publication Access List, and inserting rows into Audit and

AuditUser again, both sets of rows, Audit and AuditUser, are now replicated

since the Audit rows are copied to the msmerge_current_partition_mappings

table!

It would seem to me that granting every user access to a

publication that might alter a table that is to be replicated in that

publication is a bit cumbersome, especially since they already have rights to

the table. It’d be like assigning them rights twice – once to the table and

again to the publication that contains that table. Why not have the view ignore

the checking to see if the user has rights to publication altogether? If the

user has rights to modify the table, then they should implicitly have the right

to have their modification replicated!

I hope this helps someone. It cost us 3 days of digging through replication to find it. And only then, it was because we just happened to look at how these views were created.

-Santino Lamberti

|||

Why are you granting the users permissions to the tables at the publisher? And what permissions are you giving them, dbo access? You should not have to grant permissions to the users for each table, just add them to the PAL. The only reason you would have to grant them access to the underlying tables is if the tables are not owned by "dbo". In this case, the permission chain would break since the PAL users will call the replication views, which are owned by dbo. Other reasons you would grant them access is if they're accessing the tables outside of replication process. Depending on what access you gave them, you may have short-circuited some of the replication security checks, which then expects you to have granted permissions to all your tables properly. Otherwise if user wasn't in PAL group, and didn't have permissions on underlying tables, then the sync would have failed with appropriate security error message.

Let me know if there's any other concerns.

Can Data Partitions be used with associative tables?

First of all, we are using SQL Server 2005 with a SQL Mobile subscriber and we are attempting to use Data Partitions on our current database

schema which contains associative tables for many-to-many relationships.

We have two tables, a User table

and an Audit table.A user can be

assigned more than one Audit.An Audit

can be assigned to more than one User.

So an AuditUser associative table exists.If data partitions are used based on User,

then any Audits that are assigned to one or more users should be copied to the

proper partition for each User (the msmerge_current_partition_mappings table

with the proper partition_id values).

In order to insert records with such a schema, the following

steps occur in order:

1. Insert

   new row into Audit table with new rowguid

2. Insert

   entry into AuditUser table associating the auditguid with every userguid that

   is assigned this audit.

Merge replication triggers are fired on insert of the Audit

row and another one for the insert of the AuditUser row.

When the Audit row is inserted, the replication trigger follows

the following logic:

1. Inserts

   a copy of that row into the msmerge_contents table.

2. Evaluates

   the row to determine which partition(s) this row should be copied to as

   well (msmerge_current_partition_mappings table).To do this, it checks to see if the

   AuditGuid is referenced in one or more AuditUser rows.Since we haven’t inserted the AuditUser

   row at this point, the trigger’s logic doesn’t find a partition to copy

   this row to.

When the AuditUser row is inserted, the replication trigger performs

the same logic as with the Audit row, it:

1. Inserts

   a copy of that row into the msmerge_contents table.

2. Evaluates

   the row to determine which partition(s) this row should be copied to as

   well (msmerge_current_partition_mappings table).Since the row meets the criteria for one

   or more partitions, it is copied to the msmerge_current_partition_mappings

   table for each partition that exists.

When replication occurs, we see only the AuditUser rows

copied down to our device, and not the corresponding Audit rows.Now that we understand the triggers, it is

plain to see why.If the AuditUser row

could be inserted first, then the trigger on the Audit row would copy that row

into the proper partitions and all would work well.However, the Audit row must be inserted

first, so that foreign key relationship constraints are preserved.

It seems that the Update trigger on the AuditUser row

actually walks the relationships and copies any related child rows to the

msmerge_current_partition_mappings table.

The answer is YES! I'm posting a followup as we've discovered what our problem turned out to be. In the end, it was a security issue - which was not straightforward at all!

First a quick recap:

When attempting to replicate data using merge replication and data

partitions with a schema that contains an associative table, only the

associative table row was being replicated. Given the following table

relationships:

[User] <-> [AuditUser] <-> [Audit]

It is necessary to insert the row into the Audit table first before

inserting the row into the AuditUser table (assuming that the [User] table is

somewhat fixed in this scenario). Because the

msmerge_current_partition_mappings table is maintained on INSERT triggers, when

the trigger fires for the Audit table, it does not determine that the row meets

the filter criteria, because that criteria is based on a User and since the

AuditUser row hasn’t been inserted yet, the Audit row is not seen as belonging

to user’s partition and thus is not copied to the

msmerge_current_partition_mappings table.

When the AuditUser row is inserted next, it easily passes the

filter test and is copied to the msmerge_current_partition_mappings table. To

compensate for the fact the Audit row was missed, this trigger rescans related

rows and attempts to copy any related rows that match this AuditUser row over

to the msmerge_current_partition_mappings table (provided those rows meet

filter criteria set on those tables). It does this by checking to see if those

rows exist in a View that was created based on the filters for that table. Such

a View for our Audit table is:

Replication View for our filtered Audit table:

create view dbo.[MSmerge_Audit_Audit_PARTITION_VIEW] as select

[Audit].[Guid], [Audit].[SystemGuid], [Audit].[StatusGuid], [Audit].[Active],

[AuditUser].partition_id from [dbo].[Audit] [Audit] ,

[dbo].[MSmerge_Audit_AuditUser_PARTITION_VIEW] [AuditUser] where ( (

[AuditUser].[AuditGuid] = [Audit].[Guid]

AND ([Audit].[StatusGuid] = '73fbcc34-260e-430f-bda6-fd6bdf944d85'

OR [Audit].[StatusGuid] = '0B7E27FA-712F-455D-A651-B3C6EC815EE75')

AND [Audit].[Active] = 1

AND [Audit].[SystemGuid] in (SELECT guid FROM [System] WHERE Active

= 1)) ) and ({fn ISPALUSER('E65FCB7D-BD91-480A-8D02-F8101DA974FE')} = 1 or

permissions(469576711) & 0x1b <> 0)

When looking at the how this view is constructed, we noticed that

aside from the defined filter information, an ‘and’ clause is tacked on that

restricts the rows returned based upon the user’s inclusion in the Publication

Access List.

Our production and test environment is set up in the following

manner:

IIS on machine 1

SQL Server 2005 on machine 2

IIS is set to use a domain account called WebReplication that has

access to the Publication (is in the Publication Access List) and has access

the shared UNC repldata directory. However, our website users run under

different domain account, WebUser, which we never granted access to the

Publication. Since the WebUser account wouldn’t be replicating, it didn’t seem

necessary to give that user access to the publication. But without access to

the publication, the WebUser doesn’t have rights to see the data in the View

generated above. So, the trigger determines that there are no child rows

(Audit) that meet the filter criteria, and thus doesn’t copy the related child

rows to the msmerge_current_partition_mappings table.

The result is exactly what we’ve seen: only the AuditUser rows are

replicated and not the Audit rows that relate to them! By adding the WebUser

account to the Publication Access List, and inserting rows into Audit and

AuditUser again, both sets of rows, Audit and AuditUser, are now replicated

since the Audit rows are copied to the msmerge_current_partition_mappings

table!

It would seem to me that granting every user access to a

publication that might alter a table that is to be replicated in that

publication is a bit cumbersome, especially since they already have rights to

the table. It’d be like assigning them rights twice – once to the table and

again to the publication that contains that table. Why not have the view ignore

the checking to see if the user has rights to publication altogether? If the

user has rights to modify the table, then they should implicitly have the right

to have their modification replicated!

I hope this helps someone. It cost us 3 days of digging through replication to find it. And only then, it was because we just happened to look at how these views were created.

-Santino Lamberti

|||

Why are you granting the users permissions to the tables at the publisher? And what permissions are you giving them, dbo access? You should not have to grant permissions to the users for each table, just add them to the PAL. The only reason you would have to grant them access to the underlying tables is if the tables are not owned by "dbo". In this case, the permission chain would break since the PAL users will call the replication views, which are owned by dbo. Other reasons you would grant them access is if they're accessing the tables outside of replication process. Depending on what access you gave them, you may have short-circuited some of the replication security checks, which then expects you to have granted permissions to all your tables properly. Otherwise if user wasn't in PAL group, and didn't have permissions on underlying tables, then the sync would have failed with appropriate security error message.

Let me know if there's any other concerns.

Can columns be aliased in Transactional Replication?

I have finally got all the bugs worked out of my TransRep publisher ->
Distributor/Subscriber set up when I got hit with a new question that I can't
answer. I've been asked to alias a few of the columns so that in the
replicated DB they show up with a different header than the columns in the
publisher DB.
IE, Server 1 has column name that says Enroller_ID. Boss wants Server 2 to
say User_ID so that the people reading the data won't get confused. Data in
the column will remain the same and has already been filtered on a row
(horizontal) level. I've also filtered on columns, only sending what is
necessary so the users don't see private information.
But no where I look can I find an "alias" box. BOL mentioned something
about using DTS in replication, but I can't find how to link the two. I know
how to change a column name in DTS (I've become quite the expert in using the
Import / Export Wizard to create packages). But, again, I can't figure out
how to link the two or if this is even necessary.
Help is appreciated. Thank you all in advance. Even if you could tell me
what references to search for in BOL would be a great help.
But doesn't replicating a vew (indexed or otherwise) require the base tables
be published with it?
The replication is getting sent to a customer's server, these people have
sysadmin rights on their machines, and if we send over both the table and the
view, they're probably not even going to look at the view.
"Paul Ibison" wrote:

> I wouldn't use transformable publications for this -
> there are easier ways with less overhead. You could
> replicate an indexed view, or alternatively use an
> @.creation_script.
> HTH,
> Paul Ibison, SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||No - if you use an indexed view, it will appear in a
separate section when you are selecting the articles, and
it doesn't require the underlying table(s). What you are
saying applies to standard views, which of course is
another posibility - you could replicate the table to
another named table and have a view on the subscriber
with the same name as the original table. This would
avoid the overhead on the publisher of maintaining the
indexed view behind the scenes.
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||you can modify the replication stored procedures to reflect the new names on
the subscriber.
To do this you will also need to use a pre-creation script containing the
schema of these tables with the new names.
Then you will need to use the delete existing data in the table option in
the snapshot tab of your table article properties dialog box.
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
"Catadmin" <Catadmin@.discussions.microsoft.com> wrote in message
news:D16A826B-586C-4FD6-AFFE-A22AE222A06F@.microsoft.com...
> I have finally got all the bugs worked out of my TransRep publisher ->
> Distributor/Subscriber set up when I got hit with a new question that I
can't
> answer. I've been asked to alias a few of the columns so that in the
> replicated DB they show up with a different header than the columns in the
> publisher DB.
> IE, Server 1 has column name that says Enroller_ID. Boss wants Server 2
to
> say User_ID so that the people reading the data won't get confused. Data
in
> the column will remain the same and has already been filtered on a row
> (horizontal) level. I've also filtered on columns, only sending what is
> necessary so the users don't see private information.
> But no where I look can I find an "alias" box. BOL mentioned something
> about using DTS in replication, but I can't find how to link the two. I
know
> how to change a column name in DTS (I've become quite the expert in using
the
> Import / Export Wizard to create packages). But, again, I can't figure
out
> how to link the two or if this is even necessary.
> Help is appreciated. Thank you all in advance. Even if you could tell me
> what references to search for in BOL would be a great help.
>
|||Well, the system is not letting me say both of you answered my question.
However, you both did (with different ideas) and I greatly appreciate all
your assistance. You just made my life a LOT easier. @.=)
Thank you very much for your time.

can avoid this rollback ??

Hi,
We're testing Transactional replication with updatable subscriber. We have
kept a machine @.branch office which is the publisher and the subscriber is at
the head office. Publisher is win 2003 server with win 2000 sp4 and
subscriber is a cluster with win 2003 and sql 2000 sp3. For small commands
replication is quick but for some specific processes invloving many
transactions, the replication backlog is huge.
We used profiler and found that there is a table whci is deleted completely
during the process and populated again with current data. The table has
almost 80 thousand rows. The profiler shows that sp_MSdel is run for every
row and then sp_MSins is run to insert data. this process takes lot of time
and creates backlog.
After the table got repopulated at subscriber and replication procs started
inserting data in another table, the publisher machine got switched off
accidently by a person at branch. When we started the machine next time, the
second tables at subscriber was empty and the profiler showed that again the
sp_MSdel was being executed at subscriber.
Does this mean that , during the process, if ne of the participating machine
is not available, the whole process will be restarted?
Why it strts with the table it had already replicated?can I stop this?
Ne suggestions?
regards,
have a look at the transaction on the publisher which caused this process.
It is probably a large update statement which is not being done as an update
in place on the subscriber, but rather being decomposed into a series of
delete and then update statements. See if you can identify if this action
was initiated by a stored procedure on the publisher and if so consider
replicating the execution of this stored procedure. You might also want to
look at using the trace flag mentioned in the following kb article which
will force an update in place.
http://support.microsoft.com/kb/302341/EN-US/
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
"k_s" <ks@.discussions.microsoft.com> wrote in message
news:40CEB69C-0E43-4A93-A89D-60A9BBFD08DE@.microsoft.com...
> Hi,
> We're testing Transactional replication with updatable subscriber. We have
> kept a machine @.branch office which is the publisher and the subscriber is
> at
> the head office. Publisher is win 2003 server with win 2000 sp4 and
> subscriber is a cluster with win 2003 and sql 2000 sp3. For small commands
> replication is quick but for some specific processes invloving many
> transactions, the replication backlog is huge.
> We used profiler and found that there is a table whci is deleted
> completely
> during the process and populated again with current data. The table has
> almost 80 thousand rows. The profiler shows that sp_MSdel is run for every
> row and then sp_MSins is run to insert data. this process takes lot of
> time
> and creates backlog.
> After the table got repopulated at subscriber and replication procs
> started
> inserting data in another table, the publisher machine got switched off
> accidently by a person at branch. When we started the machine next time,
> the
> second tables at subscriber was empty and the profiler showed that again
> the
> sp_MSdel was being executed at subscriber.
> Does this mean that , during the process, if ne of the participating
> machine
> is not available, the whole process will be restarted?
> Why it strts with the table it had already replicated?can I stop this?
> Ne suggestions?
> regards,
|||Thanks for the prompt reply Hilary.
The process is actually deleting a table completely and then inserting rows
into it. This is done by a stored procedure.
Could u give me ne link to get more idea about the 'replicatin execution of
sp'.
We told the application maintenance guys this issue, anmd asked them to
change the logic to update instead of competely emptying the table.
"Hilary Cotter" wrote:

> have a look at the transaction on the publisher which caused this process.
> It is probably a large update statement which is not being done as an update
> in place on the subscriber, but rather being decomposed into a series of
> delete and then update statements. See if you can identify if this action
> was initiated by a stored procedure on the publisher and if so consider
> replicating the execution of this stored procedure. You might also want to
> look at using the trace flag mentioned in the following kb article which
> will force an update in place.
> http://support.microsoft.com/kb/302341/EN-US/
> --
> 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
> "k_s" <ks@.discussions.microsoft.com> wrote in message
> news:40CEB69C-0E43-4A93-A89D-60A9BBFD08DE@.microsoft.com...
>
>
|||Is it possible that you are doing a cascading update of the entire table or
a large portion of it?
Regarding replicating the execution of stored procedures have a look at
Publishing Stored Procedure Execution in BOL.
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
"k_s" <ks@.discussions.microsoft.com> wrote in message
news:35203F89-5688-497D-B46B-F8D5B85728A1@.microsoft.com...[vbcol=seagreen]
> Thanks for the prompt reply Hilary.
> The process is actually deleting a table completely and then inserting
> rows
> into it. This is done by a stored procedure.
> Could u give me ne link to get more idea about the 'replicatin execution
> of
> sp'.
> We told the application maintenance guys this issue, anmd asked them to
> change the logic to update instead of competely emptying the table.
> "Hilary Cotter" wrote:
|||Couldn't get u. Presently it's not update. It's a history table which
maintens one day history. So everyday, it is cleared and working table's data
is shifted ti it
I saw and tried the sp execution , works well. But in my case it won't fit,
as I have my procedure calling another procedure and BOL says that won't b
supported.
: (
"Hilary Cotter" wrote:

> Is it possible that you are doing a cascading update of the entire table or
> a large portion of it?
> Regarding replicating the execution of stored procedures have a look at
> Publishing Stored Procedure Execution in BOL.
> --
> 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
> "k_s" <ks@.discussions.microsoft.com> wrote in message
> news:35203F89-5688-497D-B46B-F8D5B85728A1@.microsoft.com...
>
>

can a Subscriber be a Publisher?

sql2k sp3a
The configuration is currently.
Box1-- Publisher/ Distributor.
Several Subscribers all with Pull Subscriptions.
One of these Pull Subscriptions that my boss wants me to start Replicating
from. Can this type of deal cause problems?
TIA, ChrisR
Have a look at "republisher replication model" in SQL Server books online
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"ChrisR" <noemail@.bla.com> wrote in message
news:eDAEYipSFHA.3332@.TK2MSFTNGP15.phx.gbl...
> sql2k sp3a
> The configuration is currently.
> Box1-- Publisher/ Distributor.
> Several Subscribers all with Pull Subscriptions.
>
> One of these Pull Subscriptions that my boss wants me to start Replicating
> from. Can this type of deal cause problems?
> TIA, ChrisR
>
|||Makes sense. But what will happen if I sp_replAddColumn on the Publisher. I
know that column will make it on the RePublisher, but will it arrive on the
RePublisher's Subscribers?
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:OJI0ZiqSFHA.616@.TK2MSFTNGP12.phx.gbl...
> Have a look at "republisher replication model" in SQL Server books online
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eDAEYipSFHA.3332@.TK2MSFTNGP15.phx.gbl...
>
|||No idea...I'd have to test it against Pubs, Pubs1 and Pubs2 on my box (or
you can test it on yours...)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"ChrisR" <noemail@.bla.com> wrote in message
news:uu7xAwqSFHA.2648@.TK2MSFTNGP10.phx.gbl...
> Makes sense. But what will happen if I sp_replAddColumn on the Publisher.
> I know that column will make it on the RePublisher, but will it arrive on
> the RePublisher's Subscribers?
>
> "Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
> news:OJI0ZiqSFHA.616@.TK2MSFTNGP12.phx.gbl...
>

Can a subscriber (anonymous...) *change* an article?

Hello,
In a project I work on, there's a server and many clients connected to
it. They go offline sometimes and before they do that they request
several items from the server which are then "downloaded" to the client
computer - they basically consist of several rows in related tables
(plus several full lookup tables). Then after they get back online, the
data is being synchronized. The problem is that the current code is
static and since we're implementing some changes we thought of changing
this too.
So my question is - can a subscriber dynamically set the query it needs?
Thanks in advance,
S. Neumann
While there are dynamic filters in merge replication, I wouldn't necessarily
suggest going down that route before finding out a bit more - it may be that
static filters based on some sort of flag would be more appropriate. Please
can you post up an example table schema and tell us in what way the data
required changes from one synchronization to another.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi,
Thanks for your quick reply!
The very basics of the db are:
Files table: ID, Name, Type
Documents table: ID, FileID, Name, DocPath
Type lookup table: ID, Name
(so there are many documents in each file). There are more tables in
such relations to the files table but two tables are enough for the
example.
Before the user goes offline, he asks to "download" several files he's
been working on to his laptop and what the program does is copy the
lookup tables to a local database (currently jet), copy the data about
the files the user requested, and the actual documents (referred to
with DocPath) as well.
When the user gets back online, changes he's made (eg. update Name
field in documents or files tables, delete a row, or any change to the
actual documents) are updated on the server.
Today we do this by using a saved log of all the changes made locally
(table name, row ID and what the change was - insert/update/delete).
The main problem we have with the way we do it now, is that since the
relations between several tables are going to change to many-to-many,
it'll be hard to keep track of which rows to actually copy to the
client and updating back to the server may also get complicated. For
instance, if the new schema will be like this:
Files table: ID, Name, Type
Documents table: ID, Name, DocPath
File-Document junction table: FileID, DocID
Without cascading delete, it'll be a pain to update the junction table.
If I locally delete a document that is connected to two files of which
I have only one downloaded, the other row in the file-document junction
table (for the file I didn't download) won't be deleted.
I wouldn't want the clients to have the whole database because it's
huge and they really only need the data for the files they work on when
offline.
So can replication help me with this?
Thanks in advance,
S. Neumann
Paul Ibison wrote:

> While there are dynamic filters in merge replication, I wouldn't
> necessarily suggest going down that route before finding out a bit
> more - it may be that static filters based on some sort of flag would
> be more appropriate. Please can you post up an example table schema
> and tell us in what way the data required changes from one
> synchronization to another. Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||Hi, The very basics of the db are:
Files table: ID, Name, Type
Documents table: ID, FileID, Name, DocPath
Type lookup table: ID, Name
(so there are many documents in each file). There are more tables in
such relations to the files table but two tables are enough for the
example.
Before the user goes offline, he asks to "download" several files he's
been working on to his laptop and what the program does is copy the
lookup tables to a local database (currently jet), copy the data about
the files the user requested, and the actual documents (referred to
with DocPath) as well.
When the user gets back online, changes he's made (eg. update Name
field in documents or files tables, delete a row, or any change to the
actual documents) are updated on the server.
Today we do this by using a saved log of all the changes made locally
(table name, row ID and what the change was - insert/update/delete).
The main problem we have with the way we do it now, is that since the
relations between several tables are going to change to many-to-many,
it'll be hard to keep track of which rows to actually copy to the
client and updating back to the server may also get complicated. For
instance, if the new schema will be like this:
Files table: ID, Name, Type
Documents table: ID, Name, DocPath
File-Document junction table: FileID, DocID
Without cascading delete, it'll be a pain to update the junction table.
If I locally delete a document that is connected to two files of which
I have only one downloaded, the other row in the file-document junction
table (for the file I didn't download) won't be deleted.
I wouldn't want the clients to have the whole database because it's
huge and they really only need the data for the files they work on when
offline.
So can replication help me with this?
Thanks in advance,
S. Neumann
Paul Ibison wrote:

> While there are dynamic filters in merge replication, I wouldn't
> necessarily suggest going down that route before finding out a bit
> more - it may be that static filters based on some sort of flag would
> be more appropriate. Please can you post up an example table schema
> and tell us in what way the data required changes from one
> synchronization to another. Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
|||Changing the initializing data retrospectively isn't possible directly
unless you have a separate publication for each subscriber, change the
filter clause and then reinitialize. However, using static filters, it is
possible to reallocate records at the highest level, and the effect will
propagate downwards by virtue of the join filters. This will allow you to
simulate changing the filtering dynamically. Practically speaking, there
would be have to be another table which relates files to subscriber., and
table joins would relate the other tables, while the static filter would
apply to the "File_Subscriber" table only. Checking out another file by a
subscriber would be an insert into the "File_Subscriber" table. Removing a
record from this table will propagate a series of deletes to the subscriber
for the Files and Documents tables of related records. I wouldn't recommend
using cascade deletes in the manner you suggested, as the result could be
unexpected for the reason you stated. Instead, I'd use a bit flag to
designate each record as 'current' and change that accordingly - either in
the stored procedure that initiates the change or in a trigger.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Can a Sql CE 2.0 application be a subscriber of a Sql Server 2005 publication ?

We actuallly have en error when we try to synchronize a ppc 2003 device
running an application built with CF 1.0 and Sql Ce 2.0 and a Sql 2005
server.
The errror is this one :
28035
SSCE_M_FAILUREQUERYINGHEADER
Internal error: HTTP header information is either corrupted or missing in
the transport message. It could be a network transmission error or an IIS
problem.
The queried dll is the sqlcesa30.dll which works correctly with Sql Mobile
applications on same hardware...
We also check in the publication properties that the minimum client version
is Sql 2000 and not sql 2005.
Thanks,
Steve
Steve,
Yes, a SQL CE 2.0 application can be a subscriber of a SQL Server 2005
publication.
It will need to go through ssceSA20.dll instead of sqlcesa30.dll. You will
need to install SQL CE 2.0 Server Tools.
The dev team has put together a detailed page with steps and upgrade paths
for connectivity tools setup:
Connectivity Tools for SQL Server Mobile, SQL Server 2000, and SQL Server
2005 http://www.microsoft.com/sql/edition...ity-tools.mspx
Thank you!
Syed N. Yousuf
Microsoft Developer Support Professional
This posting is provided "AS IS" with no warranties, and confers no rights.