Replication to the Non Default File Group

Hi,

I have a snapshot replication set up on a SQL 2K Server publishing to other SQL 2K servers. The publisher database has mutliple file groups - which is structured the same as the subscriber databases. The snapshot has been set to delete and recreate the tables on the subscribers - this is a performance decision as the tables are large and this is the better approach. The tables reside in their own file groups (but not the default). I want the replication agents to recreate these tables in the original file groups and not the default.

Is there a method within Replication to specify which file group to recreate the table in so it doesn't fill up the default file group?

Thanks in advance,

Pete

Hi Pete,

The tables on the subscriber will only be created on the default filegroup. The options are either to not drop tables and use delete instead or modify the default filegroup of the destination databases.

You can create a job that does the following:

1. sets the default filegroup of the subscriber database to secondary filegroup.

2. Agent starts and does the job.

3. Once agent job has finished and call a new job that will set the filegroup backup to primary.

In SQL 2005 you have the option to specify the scriptts that you want to execute before and after the snapshot is applied.

Jag

|||

There is a third option. After the snapshot has been created, edit the script files on the distributor which hold the CREATE TABLE commands and correct them to use the correct file groups.

You can also setup a T/SQL script which can be run after the snapshot has been delivered. Write a script which rebuilds the clustered indexes of the tables on the correct file group. By rebuilding the clustered index and moving it to another file group you will move the tables to that file group as well.

Replication through firewall

Hi,
Can anyone tell me whether replication can help me solve a problem
when my publisher has a very tight firewall? Specifically, they
absolutely will not enable ports, MS Proxy Server, or VPN. They are
willing to periodically forward a file through FTP, but there is no
way they will allow logins to their SQL Server. The total data set we
want to replicate is too large for snapshot replication, so I'm
thinking of using transactional replication. Could I set the protected
server up as a publisher with a local distributor but no
subscribers... I'm thinking I could at least capture the
logs/transactions from the distributor to a data file that they would
then ftp to me on their regular schedule. Is there a better way? I'm
hoping to use as much of the replication technology as reuse resources
rather than build from scratch.
Thanks for any advice,
Melissa
What about a Pull Subscription where they pull the data from you? I could be
reading your post wrong but I think that would work.
"Melissa" <shoesmith@.cox.net> wrote in message
news:8e0521f3.0404230709.515a5447@.posting.google.c om...
> Hi,
> Can anyone tell me whether replication can help me solve a problem
> when my publisher has a very tight firewall? Specifically, they
> absolutely will not enable ports, MS Proxy Server, or VPN. They are
> willing to periodically forward a file through FTP, but there is no
> way they will allow logins to their SQL Server. The total data set we
> want to replicate is too large for snapshot replication, so I'm
> thinking of using transactional replication. Could I set the protected
> server up as a publisher with a local distributor but no
> subscribers... I'm thinking I could at least capture the
> logs/transactions from the distributor to a data file that they would
> then ftp to me on their regular schedule. Is there a better way? I'm
> hoping to use as much of the replication technology as reuse resources
> rather than build from scratch.
> Thanks for any advice,
> Melissa
|||you can configure SQL Server to listen on another port other than 1433 by
modifying the ServerPortNumber. If your firewall admins won't allow this
there is little you can do.
"Melissa" <shoesmith@.cox.net> wrote in message
news:8e0521f3.0404230709.515a5447@.posting.google.c om...
> Hi,
> Can anyone tell me whether replication can help me solve a problem
> when my publisher has a very tight firewall? Specifically, they
> absolutely will not enable ports, MS Proxy Server, or VPN. They are
> willing to periodically forward a file through FTP, but there is no
> way they will allow logins to their SQL Server. The total data set we
> want to replicate is too large for snapshot replication, so I'm
> thinking of using transactional replication. Could I set the protected
> server up as a publisher with a local distributor but no
> subscribers... I'm thinking I could at least capture the
> logs/transactions from the distributor to a data file that they would
> then ftp to me on their regular schedule. Is there a better way? I'm
> hoping to use as much of the replication technology as reuse resources
> rather than build from scratch.
> Thanks for any advice,
> Melissa
|||Hi,
The administrator is not willing to open any port including 1433. They
are only willing to send data out periodically over ftp.
Answering the other reply, since they produce the data, I'm not sure
how a pull subscription from them would help solve the problem of
getting the data to me.
The fundamental problem is that I can't log into that SQL Server
database from outside their firewall. I'm trying to move transactions
and their application to a subscriber to outside the database. If I
set up a publisher and distributor inside that firewall, they would
produce some sort of log or dataset of the transactions. If so, where
is that? Could I have a script access that transaction history and
write that out to a file? I could then script sending it over ftp as
the administrator is willing to do. When I get it to my side, could I
insert it into a distributor-like database and subscribe from that? Or
maybe just apply the data directly from the datafile to the
"subscriber"? Is any or all of this possible?
Thanks,
Melissa
|||you can export the msrepl_commands table to the subscriber via some process,
perhaps ado.
Make sure you don't store your parameters for your publication in binary
format. To do this right click on your publication, go to the articles tab,
highlight each article, click on the three dots to the right of each
article, and then commands section uncheck the send parameters in binary
format.
Then make sure you set your distribution cleanup agent to run once a day.
You can convert the msrepl_commands table to meaningful information by doing
this
select convert(nchar(300),command ) from msrepl_commands
"Melissa" <shoesmith@.cox.net> wrote in message
news:8e0521f3.0404260551.4ce1ddbb@.posting.google.c om...
> Hi,
> The administrator is not willing to open any port including 1433. They
> are only willing to send data out periodically over ftp.
> Answering the other reply, since they produce the data, I'm not sure
> how a pull subscription from them would help solve the problem of
> getting the data to me.
> The fundamental problem is that I can't log into that SQL Server
> database from outside their firewall. I'm trying to move transactions
> and their application to a subscriber to outside the database. If I
> set up a publisher and distributor inside that firewall, they would
> produce some sort of log or dataset of the transactions. If so, where
> is that? Could I have a script access that transaction history and
> write that out to a file? I could then script sending it over ftp as
> the administrator is willing to do. When I get it to my side, could I
> insert it into a distributor-like database and subscribe from that? Or
> maybe just apply the data directly from the datafile to the
> "subscriber"? Is any or all of this possible?
> Thanks,
> Melissa
sql

Replication Table Permissions

Is there a way to replicate the table permissions from publisher to
subscriber? I noticed that when replication takes place, the permissions
that were set up on tables on the subscriber are wiped out. I need the permissions to be send to the subscriber automatically.I believe that is not part of the snapshot. However, you can script it and make it the post snapshot script.|||Can yoy give me the script or recommend a side with the script because that exactly what i am looking for and can't figure it out as is not discussed on microsoft help|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replmon_9pwh.asp for reference.|||Satya, i don't see anything to do with permissions on the website you send me|||As per the BOL :
How to script replication (Enterprise Manager)
At the Publisher, open SQL Server Enterprise Manager, expand a server group, right-click the Replication folder, and then click Generate SQL Script.

Select the replication component to script (Distributor properties, publications and push subscriptions, or pull subscriptions) and whether you want the script to enable or create the components or disable or drop the components. .

HTH|||I am sorry about this Satya, but i can see only the logins granted for my publicatiom on my Publisher/Distributor, my question is how do you take those permissions automatically to the subsciber?

Replication subscription validation in Microsoft SQL Server 2000

Hi,
I have set up a pull transactional replication using 2 machines: one
Publisher and the other Distributor-Subscriber.
Both are running Microsoft SQL Server 2000 SP3. They have different OSes
though: the Publisher has Microsoft Windows Server 2003 SP1 but the
Distributor-Subscriber has Microsoft Windows 2000 Advanced Server SP4.
I have one publication of all objects in a database and one subscriber to
all articles in that publication.
Will the successful result of sp_publication_validation be the sufficient
evidence to make an assumption that the Publication and Subscription
databases are identical if we don't use any text or binary data types? Does
it always need to be a check with checksums?
--
Many thanks,
OskarOskar,
sp_publication_validation is only a basic comparison. Binary checksums are
needed to be certain, or in some cases I use SQL Data Compare. This can be
useful if you want to manually synchronize the data then reinitialize with
nosync ie you can't afford to reinitialize.
If you are using merge replication, there are some circumstances when rows
won't get replicated (basically those cases where triggers don't fire) and
these cases will be caught by your simple rowcount. Using more arcane
settings like -EXCHANGETYPE can result in the same rowcount but different
data, and while the binary checksum would catch this behaviour the rowcount
might miss it.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Replication Subscription Error

Hi,
I tried setting a server up as the publisher, distributor and subscriber.
When I tried to create either a push or pull subscription I got the following
message:
Error 14053: Cannot load the DLL replincrementlsn extended procedure, or one
of the DLLs it references. Reason: 126(error not found).
The subscription could not be updated at this time.
The subscription could not be created.
SQL Server Enterprise Manager could not create a pull subscription to
publication XXXX
Error 14053: Cannot load the DLL replincrementlsn extended procedure, or one
of the DLLs it references. Reason: 126(error not found).
The subscription could not be updated at this time.
The subscription could not be created.
Has anyone seen this before?
Server running SQL 2000 on Windows 2000 Server
Thanks
Something is not installed correctly. Reapply your latest service pack.
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
"DavidW" <DavidW@.discussions.microsoft.com> wrote in message
news:895B9996-9D97-4CAA-84F2-FF314F842E6F@.microsoft.com...
> Hi,
> I tried setting a server up as the publisher, distributor and subscriber.
> When I tried to create either a push or pull subscription I got the
> following
> message:
> Error 14053: Cannot load the DLL replincrementlsn extended procedure, or
> one
> of the DLLs it references. Reason: 126(error not found).
> The subscription could not be updated at this time.
> The subscription could not be created.
> SQL Server Enterprise Manager could not create a pull subscription to
> publication XXXX
> Error 14053: Cannot load the DLL replincrementlsn extended procedure, or
> one
> of the DLLs it references. Reason: 126(error not found).
> The subscription could not be updated at this time.
> The subscription could not be created.
> Has anyone seen this before?
> Server running SQL 2000 on Windows 2000 Server
> Thanks
>
|||Thank you Hilary, that has resolved the problem!
"Hilary Cotter" wrote:

> Something is not installed correctly. Reapply your latest service pack.
> --
> 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
> "DavidW" <DavidW@.discussions.microsoft.com> wrote in message
> news:895B9996-9D97-4CAA-84F2-FF314F842E6F@.microsoft.com...
>
>

Replication sometimes does not delete all records

Hi all,
I have a following problem with my replication.
I have 3 machines - 1 is publisher and its own distributor, and two are
subscribers. The replication topology model is "central publisher".
The problem concerns the merge replication with filtering (filter is
"2=1"). This replication should take any records inserted into
replicated tables on subscribers and copy them to main server (typical
merge process for new entries). Received rows are checked against the
filter condition and are immediately deleted on subscribers because the
"2=1" condition is impossible.
This is working fine, but sometimes it happens that some rows are not
deleted on subscribers. They are copied properly, but sometimes you can
see the situation when there was 15371 inserts and 15270 deletes. They
are not taken into consideration during the next replication (they are
just left, cluttering the tables on subscribers).
All machines working on sql server 2k with sp2.
Did someone encountered such behaviour? I'm new to replication and I
don't know what can I do to analyse what's going on.
Regards
Xavier
Does anything show up in the conflict viewer?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||> Does anything show up in the conflict viewer?
No, there are no conflicts shown. There are no errors either. Everything
goes smooth (with one exception).
Regards
|||I'd run a validation to verify everything is working fine.
The record being uploaded/downloaded is likely a metadata record for the system tables.
I am perplexed by your filter. If you are filtering updates coming from your subscriber you are also filtering going to your publisher which begs the question - why are you replicating this table?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||> I am perplexed by your filter. If you are filtering updates coming from your subscriber you are also filtering going to your publisher which begs the question - why are you replicating this table?
the task i want to achieve is to move the data from distributed machine
to one central server. is there something i missed?
sql

replication sequence

I have 2 tables which tab B depends on the record on tab A. In publisher, records are generated to tabA before some audit information record to tabB. In subscriber, there is replication error that the trigger in tab B couldn't find the corresponding record in tab A and cause the transactional replication error. And I found the comand_id within the same xact_segno of record in tab B is smaller than that of record in tab A. Does it mean the data in tab B replicate before tab A?

Also, how to know the replication sequence to the subscriber?

Thanks in advance!

You may want to check your trigger to see which table is committing first. Transactional replication will replicate the commands/transaction in the order they were applied.

replication scenario

Hi there,
I have two servers setup at two different locations, one a publisher(server
A) the other subscriber(server B). I'm using Merge replication and SQL server
2000.
We have teams working in remote areas gathering data in laptops that have
been setup as subscribers to server A.
How can i get the laptops to synchronise with server B incase server A fails?
Samman,
have a look in BOL for "merge replication, alternate synchronization
partners".
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication sample program

Hello i am new to SQL Replication.

I have a application in which Publisher node insert/update/delete in a SQL server data base on it's own node.

I have 3-4 different nodes which has SQL servers.

My reqirement is that when ever i insert/update/delete on the sql server in the publisher node, all the subscriber should insert/update/delete the same data in their respective data base.

so in short, all the publishers and subscribers should have same set of data. note that the subscribers are not doing any additional changes in their databases.

Also , at some point of time (when there is a fail over in the subscriber database), while restarting the subscriber database, i need to first synchronize all the data which were lost during down time with the publisher database and then onwards continue replication.
I will term this as startup synchronization step.

Can i use the SQL Transactional replication for this purpose? DO i need some other mechanism for startup synchronization step.

For example in the publisher i run this query,
Insert into customer(customerid,name,itemname) Values(20,'faith','speaker')

Once this query is executed on the publisher, i want to see that all the customer tables on the various subscribers node should have this record (20,'faith','speaker').

Is Replication is good approach or any thing else is suggested.

Can any body point to some sample tutorial or sample script to perform this kind of operation using Replication

Thanks a lot in adavance

Yes. Transactional replication is good enough to implement your scenario.

Whenever your data change happened on the publisher, subscriber will get the same change immediately (just default setting , continuous mode for both log reader and distribution agents). As for your "startup synchronization step", you can just resynchronize subscription, publisher present data will wrap up and load to subscriber through snapshot and distribution agent.

Here is transactional replication introduction in SQL Books On Line. It will also point you to implementation sample through UI and/or scripts.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/3ca82fb9-81e6-4c3c-94b3-b15f852b18bd.htm

Thanks

|||

Hello, Thanks for the info, it was really useful.

I have an extra complexity involved in my application. That is I have more than one set of publishers which are redundant to overcome the fail over situation.

When a particular publisher goes down , all the subscribers which were connected to the this failed publisher node, should now switch to a new publisher(before starting the replication , this subscriber should synchronise it self with the new publisher).

all the publishers independently updating there databases from a redundant lagacy application. that means i don't need to implement redundancy of publishers using the Replication.

I just should be able to synchronise the subscriber with the new publisher, assuming that the new publisher has the latest data (It is same as the .

There on, i should replicate the data from the new publisher to the subscriber.

Please proveide some details on how to perform this mixed approach.

Thanks in advance for your valuable feed back.

Replication rows getting deleted at publisher

I am using merge replication between two servers.
When i insert data into a perticular table ..the data gets deleted after some time.
Anyone faced this problem?In your publisher table, how many rowguid id in each table?|||The each table has one rowguid...

Replication question - unable to modify table

I have disabled publisher, distributor and subscriber; no publications exist
on publisher. However, when I try to alter a table on former publisher I get
this error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the
table 'dbo.MAIN' because it is being used for replication.
Please help, thank you in advance for your time!
Leon Shargorodsky
Run stored proc sp_removedbreplication and see if that fixes your problem.
Mike
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> I have disabled publisher, distributor and subscriber; no publications
exist
> on publisher. However, when I try to alter a table on former publisher I
get
> this error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the
> table 'dbo.MAIN' because it is being used for replication.
> Please help, thank you in advance for your time!
> Leon Shargorodsky
|||No, I'm still getting the same error: "table is being used for replication"
"Mike Wengler" wrote:

> Run stored proc sp_removedbreplication and see if that fixes your problem.
> Mike
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> exist
> get
>
>
|||NOT RECOMMENDED Under normal circumstances only after trying ALL other
solutions . . but I have had this problem resolved in the past by reseting
the value in replinfo column in sysobjects to 0.
HTH
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:8460093D-8C06-43AD-964B-06592F231ADE@.microsoft.com...
> No, I'm still getting the same error: "table is being used for
replication"[vbcol=seagreen]
> "Mike Wengler" wrote:
problem.[vbcol=seagreen]
in[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]
|||THANK YOU SO MUCH! It worked!!!
"Olu Adedeji" wrote:

> NOT RECOMMENDED Under normal circumstances only after trying ALL other
> solutions . . but I have had this problem resolved in the past by reseting
> the value in replinfo column in sysobjects to 0.
> HTH
>
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:8460093D-8C06-43AD-964B-06592F231ADE@.microsoft.com...
> replication"
> problem.
> in
> I
> the
>
>

Replication question - unable to modify table

I have disabled publisher, distributor and subscriber; no publications exist
on publisher. However, when I try to alter a table on former publisher I get
this error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the
table 'dbo.MAIN' because it is being used for replication.
Please help, thank you in advance for your time!
Leon ShargorodskyRun stored proc sp_removedbreplication and see if that fixes your problem.
Mike
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> I have disabled publisher, distributor and subscriber; no publications
exist
> on publisher. However, when I try to alter a table on former publisher I
get
> this error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the
> table 'dbo.MAIN' because it is being used for replication.
> Please help, thank you in advance for your time!
> Leon Shargorodsky|||No, I'm still getting the same error: "table is being used for replication"
"Mike Wengler" wrote:
> Run stored proc sp_removedbreplication and see if that fixes your problem.
> Mike
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> > I have disabled publisher, distributor and subscriber; no publications
> exist
> > on publisher. However, when I try to alter a table on former publisher I
> get
> > this error:
> >
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the
> > table 'dbo.MAIN' because it is being used for replication.
> >
> > Please help, thank you in advance for your time!
> >
> > Leon Shargorodsky
>
>|||NOT RECOMMENDED Under normal circumstances only after trying ALL other
solutions . . but I have had this problem resolved in the past by reseting
the value in replinfo column in sysobjects to 0.
HTH
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:8460093D-8C06-43AD-964B-06592F231ADE@.microsoft.com...
> No, I'm still getting the same error: "table is being used for
replication"
> "Mike Wengler" wrote:
> > Run stored proc sp_removedbreplication and see if that fixes your
problem.
> >
> > Mike
> >
> > "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote
in
> > message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> > > I have disabled publisher, distributor and subscriber; no publications
> > exist
> > > on publisher. However, when I try to alter a table on former publisher
I
> > get
> > > this error:
> > >
> > > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop
the
> > > table 'dbo.MAIN' because it is being used for replication.
> > >
> > > Please help, thank you in advance for your time!
> > >
> > > Leon Shargorodsky
> >
> >
> >|||THANK YOU SO MUCH! It worked!!!
"Olu Adedeji" wrote:
> NOT RECOMMENDED Under normal circumstances only after trying ALL other
> solutions . . but I have had this problem resolved in the past by reseting
> the value in replinfo column in sysobjects to 0.
> HTH
>
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:8460093D-8C06-43AD-964B-06592F231ADE@.microsoft.com...
> > No, I'm still getting the same error: "table is being used for
> replication"
> >
> > "Mike Wengler" wrote:
> >
> > > Run stored proc sp_removedbreplication and see if that fixes your
> problem.
> > >
> > > Mike
> > >
> > > "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote
> in
> > > message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> > > > I have disabled publisher, distributor and subscriber; no publications
> > > exist
> > > > on publisher. However, when I try to alter a table on former publisher
> I
> > > get
> > > > this error:
> > > >
> > > > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop
> the
> > > > table 'dbo.MAIN' because it is being used for replication.
> > > >
> > > > Please help, thank you in advance for your time!
> > > >
> > > > Leon Shargorodsky
> > >
> > >
> > >
>
>

Replication question - unable to modify table

I have disabled publisher, distributor and subscriber; no publications exist
on publisher. However, when I try to alter a table on former publisher I get
this error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Canno
t drop the
table 'dbo.MAIN' because it is being used for replication.
Please help, thank you in advance for your time!
Leon ShargorodskyRun stored proc sp_removedbreplication and see if that fixes your problem.
Mike
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> I have disabled publisher, distributor and subscriber; no publications
exist
> on publisher. However, when I try to alter a table on former publisher I
get
> this error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Can
not drop the
> table 'dbo.MAIN' because it is being used for replication.
> Please help, thank you in advance for your time!
> Leon Shargorodsky|||No, I'm still getting the same error: "table is being used for replication"
"Mike Wengler" wrote:

> Run stored proc sp_removedbreplication and see if that fixes your problem.
> Mike
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:37DFDC3E-7435-456E-A137-D94EDFED5915@.microsoft.com...
> exist
> get
>
>|||NOT RECOMMENDED Under normal circumstances only after trying ALL other
solutions . . but I have had this problem resolved in the past by reseting
the value in replinfo column in sysobjects to 0.
HTH
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:8460093D-8C06-43AD-964B-06592F231ADE@.microsoft.com...
> No, I'm still getting the same error: "table is being used for
replication"[vbcol=seagreen]
> "Mike Wengler" wrote:
>
problem.[vbcol=seagreen]
in[vbcol=seagreen]
I[vbcol=seagreen]
the[vbcol=seagreen]|||THANK YOU SO MUCH! It worked!!!
"Olu Adedeji" wrote:

> NOT RECOMMENDED Under normal circumstances only after trying ALL other
> solutions . . but I have had this problem resolved in the past by reseting
> the value in replinfo column in sysobjects to 0.
> HTH
>
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
> message news:8460093D-8C06-43AD-964B-06592F231ADE@.microsoft.com...
> replication"
> problem.
> in
> I
> the
>
>

replication question

I have a transactional replication set up Between servers a as publisher and b as subscriber with tables aaa and bbb.
If i make a change on table aaa on the subscriber side should it over written by the value of the publication table value.

means

i have table aaa

id name date

1 bbbb 7/7/77

I have changed name as cccc on subscriber side, should it be over written to bbbb which is the value on publisher table.

Thanks.Transactional replication is a "one way street", from the publisher to the subscriber(s). You can enable RPC calls to funnel changes from the subscriber back to the publisher, but these aren't enabled by default. They are also a positive pain in the patoot to maintain!

Methinks thou desires Merge replication, bubba!

-PatP|||Thanks and I appreciate your response But, I was not asking whether the changes in the suscriber effects at publisher or not?If i made a change at subscriber like changing name from bbbb to cccc.So now the name at publisher database is bbbb and at subscriber is cccc.
Does the changed name at subscriber database(cccc) will be overwritten as bbbb which the name at publisher database. or it will be cccc at subscriber and bbbb at publisher.If it wont over write that name at subscriber how can we maintain same name at both the ends.
Thanks.|||Nothing will automagically force an update at the subscriber, but the next update to that row at the publisher will either a) update the name value at the subscriber, b) ignore the name value at the subscriber, or c) cause the replication distribution agent to fail. Which of the three will be determined by a combination of subscription and distributor settings.

-PatP

Replication Question

Hi all,
I am seting up a Publisher in SQL 2000.
When I get to the screen to specify Articles after selecting a transactional replication, most of my tables are unselectable. In fact out of 25 tables only one is selectable. The rest have a key with an x next to them. Why would that be?Hi again,
I went ahead and started a replication as merge replication. But now when I created a Pull Subscriber, the job failed with the following:

Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script '\\NJRARSVR00E9\d$\sqldata\system\MSSQL$P001\ReplD ata\unc\NJRARSVR00E9$P001_PTR_PTR Merge\20040106110922\tblUser_1.sch' could not be propagated to the subscriber.

What does this mean?|||Do you have a primary key for each of those 25 tables?|||I do not. I have a primary key on some, but not all.|||You can't do transactional replication on tables tat do not have primary keys.

Try adding primary keys into your table again before doing it.

Replication question

Hi,
SQL 2000
I disabled the transaction replication from publisher server and everything
cleaned up from publisher, but I still can see agents under subscription on
subscriber server. How can I reomove the agent from subscriber server ?
Thanks.
PatIs that a pull subscription? You should be able to delete the pull agent.
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Patrick" <patriarck@.gmail.com> wrote in message
news:%2392B8lCIFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi,
> SQL 2000
> I disabled the transaction replication from publisher server and
everything
> cleaned up from publisher, but I still can see agents under subscription
on
> subscriber server. How can I reomove the agent from subscriber server ?
> Thanks.
> Pat
>|||No, it was a push !
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:e$T9N9CIFHA.588@.TK2MSFTNGP15.phx.gbl...
> Is that a pull subscription? You should be able to delete the pull agent.
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:%2392B8lCIFHA.1476@.TK2MSFTNGP09.phx.gbl...
> everything
> on
>

replication question

Hi guys,
I have a question, not sure if it's do-able.
I have a transaction replication setup on Server A (Publisher) and Server B
is my Subscriber) I have over 100 tables in this publication.
I want to change order of columns to just one table in Publisher. is
there a way that I can do it without dropping publication?
let said my table have column A , column B, column C
I want to change order to Column B, Column C , Column A
nope. You would have to drop replication, script out your tables, export
your database, drop the tables, recreate them with the correct column order
and then reimport the data.
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
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:O%23hiljsuFHA.3588@.tk2msftngp13.phx.gbl...
> Hi guys,
> I have a question, not sure if it's do-able.
> I have a transaction replication setup on Server A (Publisher) and Server
B
> is my Subscriber) I have over 100 tables in this publication.
> I want to change order of columns to just one table in Publisher. is
> there a way that I can do it without dropping publication?
>
> let said my table have column A , column B, column C
> I want to change order to Column B, Column C , Column A
>

Replication question

Dear Sir/Madam,
I am replicating 5 tables from Server A to Server B. On my first setting, I
included those 5 tables on a single publisher. After that, I recognize when a
table schema changes, I needed to drop and re-create the publishication,
means all 5 tables needed to re-synchronize again, is that true? Will it be
better to create 5 different publishication with each for one table only, so
during recreate publishication, only one table needed to synchronize, more
flexible? What the disadvantage for that?
Another question is, during recreate of one publishication, what is the
sequence of action? Is that stop log reader first, then recreate the one of
the publishication, generate snapshot, distribute the snapshot and finially
restart the log reader? Would this action sequence can make sure the
synchonize of those 5 tables?
Waiting to hearing from you soon. Many thanks!
Yours sincerely,
Henry
I would use sp_repladdcolumn or sp_repldropcolumn for this. SQL 20005 has
better options to replication schema changes seemelessly, otherwise if you
have a lot of changes to our schema you might want to think about log
shipping.
Splitting each article out into a seperate publication is a good idea when
sp_repladdcolumn and sp_repldropcolumn can't do the changes you are
looking for - for instance modifiying pk's. It is also a good idea when
your tables are large as if your snapshot fails on one table you will have
to replicate all tables again.
In general you shoudl group your tables into their own publications by DRI.
The way replication works is that the publication tables are locked and
then the log reader agent starts. Users can't make changes to tables until
the data is bcp'd out. Then when the data is bcp'd out the lock is
released and users changes are written to the tlog (as always) and the log
reader will read the transactions in the log and build commands to
replicate these transactions to the subscriber.
This way all the transactions are replicated and nothing is missed.
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

Replication Problems from Subscriber to Publisher.

Hi,
I am experiencing some difficulties with DataBase Replication. I have
two SQL 2000 servers running on different machines (The server is
running on Windows 2000 Server, and the other on Window 2000
Professional).
I have created a database with one table. Have then Published this
database, and setup a Subscription. (The Publication is Transactional,
using Immediate updating.)
If I amend/insert/delete any rows/columns at the Publisher, the
changes are immediately propagated to the Subscriber. The problem I'm
having is when the Subscriber makes any changes to the Table, SQL
generates the following error:
Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]Could
not execute procedure 'sp_MSsync_upd_<tablename>_1' on remote server
'SQLOLEDB'.
I have used the Profiler, and determined that the table change
executes the trigger 'trg_MSsync_upd_<tablename>'. This trigger is
then failing when it try's to execute the following statement:
exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=s a;PWD=;').[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
...
And since I didn't want to re-code the triggers automatically created
by SQL, I'm trying to figure out why the execution of the Remote
Stored
Procedure doesn't work.
Any help is much appreciated.
Thanks in Advance.
Rick 8-)
Two things to check. First, make sure the distributor and/or the publisher
servers have allowed remote proc execution through sp_configure.
Next, make sure your Win 2K Pro installation is running the SQL Server
service accounts under an id that has access to the distributor/publisher or
has the correct sign-in information.
Sincerely,
Anthony Thomas
"Rick Knight" wrote:

> Hi,
> I am experiencing some difficulties with DataBase Replication. I have
> two SQL 2000 servers running on different machines (The server is
> running on Windows 2000 Server, and the other on Window 2000
> Professional).
> I have created a database with one table. Have then Published this
> database, and setup a Subscription. (The Publication is Transactional,
> using Immediate updating.)
> If I amend/insert/delete any rows/columns at the Publisher, the
> changes are immediately propagated to the Subscriber. The problem I'm
> having is when the Subscriber makes any changes to the Table, SQL
> generates the following error:
> Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]Could
> not execute procedure 'sp_MSsync_upd_<tablename>_1' on remote server
> 'SQLOLEDB'.
> I have used the Profiler, and determined that the table change
> executes the trigger 'trg_MSsync_upd_<tablename>'. This trigger is
> then failing when it try's to execute the following statement:
> exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLServer;UID=s a;PWD=;').[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
> ...
> And since I didn't want to re-code the triggers automatically created
> by SQL, I'm trying to figure out why the execution of the Remote
> Stored
> Procedure doesn't work.
> Any help is much appreciated.
> Thanks in Advance.
> Rick 8-)
>
|||Thanks for the feedback Anthony, But I have check both of these, and
still no luck. Now however the error message is different.
Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB
error trace [OLE/DB Provider 'SQLOLEDB' IUnknow QueryInterface
returned 0x80070005: Access denied.
BTW: Replication from the Publisher to the Subscriber still works okay
though.
Have you any other suggestions?
Thanks in Advance
Rick
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message news:<4DCC547D-F875-4F14-8B65-F02CD34B8088@.microsoft.com>...[vbcol=seagreen]
> Two things to check. First, make sure the distributor and/or the publisher
> servers have allowed remote proc execution through sp_configure.
> Next, make sure your Win 2K Pro installation is running the SQL Server
> service accounts under an id that has access to the distributor/publisher or
> has the correct sign-in information.
> Sincerely,
>
> Anthony Thomas
>
> "Rick Knight" wrote:
|||That error message means you've granted the publisher/distributer SQL Agent
account access to the subscriber machine but that the reverse is not true.
In order to do a Pull, the subscriber must have login rights to the
distributer/publisher server.
Sincerely,
Anthony Thomas
"Rick Knight" wrote:

> Thanks for the feedback Anthony, But I have check both of these, and
> still no luck. Now however the error message is different.
> Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB
> error trace [OLE/DB Provider 'SQLOLEDB' IUnknow QueryInterface
> returned 0x80070005: Access denied.
> BTW: Replication from the Publisher to the Subscriber still works okay
> though.
> Have you any other suggestions?
> Thanks in Advance
> Rick
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message news:<4DCC547D-F875-4F14-8B65-F02CD34B8088@.microsoft.com>...
>
sql

Replication Problems from Subscriber to Publisher.

Hi,
I am experiencing some difficulties with DataBase Replication. I have
two SQL 2000 servers running on different machines (The server is
running on Windows 2000 Server, and the other on Window 2000
Professional).
I have created a database with one table. Have then Published this
database, and setup a Subscription. (The Publication is Transactional,
using Immediate updating.)
If I amend/insert/delete any rows/columns at the Publisher, the
changes are immediately propagated to the Subscriber. The problem I'm
having is when the Subscriber makes any changes to the Table, SQL
generates the following error:
Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]
Could
not execute procedure 'sp_MSsync_upd_<tablename>_1' on remote server
'SQLOLEDB'.
I have used the Profiler, and determined that the table change
executes the trigger 'trg_MSsync_upd_<tablename>'. This trigger is
then failing when it try's to execute the following statement:
exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLSe
rver;UID=sa;PWD=;').
[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
...
And since I didn't want to re-code the triggers automatically created
by SQL, I'm trying to figure out why the execution of the Remote
Stored
Procedure doesn't work.
Any help is much appreciated.
Thanks in Advance.
Rick 8-)Two things to check. First, make sure the distributor and/or the publisher
servers have allowed remote proc execution through sp_configure.
Next, make sure your Win 2K Pro installation is running the SQL Server
service accounts under an id that has access to the distributor/publisher or
has the correct sign-in information.
Sincerely,
Anthony Thomas
"Rick Knight" wrote:

> Hi,
> I am experiencing some difficulties with DataBase Replication. I have
> two SQL 2000 servers running on different machines (The server is
> running on Windows 2000 Server, and the other on Window 2000
> Professional).
> I have created a database with one table. Have then Published this
> database, and setup a Subscription. (The Publication is Transactional,
> using Immediate updating.)
> If I amend/insert/delete any rows/columns at the Publisher, the
> changes are immediately propagated to the Subscriber. The problem I'm
> having is when the Subscriber makes any changes to the Table, SQL
> generates the following error:
> Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Serve
r]Could
> not execute procedure 'sp_MSsync_upd_<tablename>_1' on remote server
> 'SQLOLEDB'.
> I have used the Profiler, and determined that the table change
> executes the trigger 'trg_MSsync_upd_<tablename>'. This trigger is
> then failing when it try's to execute the following statement:
> exec @.retcode = OpenDataSource('SQLOLEDB',N'SERVER=SQLSe
rver;UID=sa;PWD=;'
).[<databasename>].[dbo].[sp_MSsync_del_<tablename>_1]'
> ...
> And since I didn't want to re-code the triggers automatically created
> by SQL, I'm trying to figure out why the execution of the Remote
> Stored
> Procedure doesn't work.
> Any help is much appreciated.
> Thanks in Advance.
> Rick 8-)
>|||Thanks for the feedback Anthony, But I have check both of these, and
still no luck. Now however the error message is different.
Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Server]
OLE DB
error trace [OLE/DB Provider 'SQLOLEDB' IUnknow QueryInterface
returned 0x80070005: Access denied.
BTW: Replication from the Publisher to the Subscriber still works okay
though.
Have you any other suggestions?
Thanks in Advance
Rick
"AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message news:<4DCC547D-F8
75-4F14-8B65-F02CD34B8088@.microsoft.com>...[vbcol=seagreen]
> Two things to check. First, make sure the distributor and/or the publishe
r
> servers have allowed remote proc execution through sp_configure.
> Next, make sure your Win 2K Pro installation is running the SQL Server
> service accounts under an id that has access to the distributor/publisher
or
> has the correct sign-in information.
> Sincerely,
>
> Anthony Thomas
>
> "Rick Knight" wrote:
>|||That error message means you've granted the publisher/distributer SQL Agent
account access to the subscriber machine but that the reverse is not true.
In order to do a Pull, the subscriber must have login rights to the
distributer/publisher server.
Sincerely,
Anthony Thomas
"Rick Knight" wrote:

> Thanks for the feedback Anthony, But I have check both of these, and
> still no luck. Now however the error message is different.
> Database Error: '[Microsoft][ODBC SQL Server Driver][SQL Serve
r]OLE DB
> error trace [OLE/DB Provider 'SQLOLEDB' IUnknow QueryInterface
> returned 0x80070005: Access denied.
> BTW: Replication from the Publisher to the Subscriber still works okay
> though.
> Have you any other suggestions?
> Thanks in Advance
> Rick
>
> "AnthonyThomas" <AnthonyThomas@.discussions.microsoft.com> wrote in message
news:<4DCC547D-F875-4F14-8B65-F02CD34B8088@.microsoft.com>...
>