replication error

we are replicating from SQL 2000 to Oracle via linked server. and are
getting the following error "The process encountered invalid column
data in bcp file", the bcp file is too big to investigate it, any one
has any insight of this?
Did this ever work? By default SQL Server maps the SQL Server datatypes to
the Oracle datatypes, so it should be able to construct the table on Oracle
and bcp the data there. You might want to try it with a null filter to see
if the problem exists with the schema or 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
"shunyen" <kate.s.chiang@.healthnet.com> wrote in message
news:1175209502.943214.313370@.d57g2000hsg.googlegr oups.com...
> we are replicating from SQL 2000 to Oracle via linked server. and are
> getting the following error "The process encountered invalid column
> data in bcp file", the bcp file is too big to investigate it, any one
> has any insight of this?
>
|||It worked before, the table got rebuilt and we have to recreate the
replication and this shows up. what do you meen to use a null
filter?
On Mar 30, 5:56 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Did this ever work? By default SQL Server maps the SQL Server datatypes to
> the Oracle datatypes, so it should be able to construct the table on Oracle
> and bcp the data there. You might want to try it with a null filter to see
> if the problem exists with the schema or 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 FTShttp://www.indexserverfaq.com
> "shunyen" <kate.s.chi...@.healthnet.com> wrote in message
> news:1175209502.943214.313370@.d57g2000hsg.googlegr oups.com...
>
>
> - Show quoted text -
|||A filter that looks like this
where 1=2
It always evaluates to false.
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
"shunyen" <kate.s.chiang@.healthnet.com> wrote in message
news:1175268021.364120.267330@.p77g2000hsh.googlegr oups.com...
> It worked before, the table got rebuilt and we have to recreate the
> replication and this shows up. what do you meen to use a null
> filter?
>
> On Mar 30, 5:56 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>

Replication Conflict Auto Notification (How)

Hi

I'm Running SQL 2k - Mainly Merge Replication.

Can Someone point me in the right direction on how best to Alert myself/Operator via email when a conflict occurs.

I use xp_sendmail regularly so can deal with that part

I've tried looking for a solution but with no success.

Having to keep opening the Conflict Viewer to check is becomming a Bit Tiresome.

The Only way I can think of is running a batch Job to look at the rowcount of conflict tables (Possibly using a cursor and dynamically built Select Count(*) statements from sysobjects to get conflict table names).

or

If Conflicts are flagged in the system Replication tables query them but I don't know which table if any exist.

Theres gotta be an easier way - I must be missing something

Any Help Greatly appreciated

thx

GWCheck Lumigent (http://www.lumigent.com) website for latest tool on notification and its free.|||Cool

thx again Satya

GW

Replication Configuration?

Our website and sql database are hosted away from our offices and is updated via the web by customers and by us in our offices. However due to our slow internet connection we would like to bring a replication server into our office and have the two databases talk to each other when a change occurs.(Customers updating the public database and us update a local one).

I have seen sites mention circular replication is troublesome and may not work, is this the case or is it just a matter of correct configuration?

Any advice, tips etc would be appreciated.

Thanks

John

I guess u need a Transactional replication configured between the two server.

Two scenario u can have

1. Central publishers and many(as many as u wish) read only subscriber

2. Other case in which subscribers also get updated Immediate updating subscribers.

I guess u have a scenario where in both the server will get updated. u can go for transactional replication with immediate updating subscribers.

I see no issues in this and ofcourse correct configuration is must for anything u do with SQL Server.

|||

Also i have one more quick question..how frequent is update on the both the server?

if both server are frequently updated u should consider using merge replication instead of transactional replication with immediate updating subscription as immediate updating subscription is useful for only occasional changes on the subscribers.

|||

I posted this on a different thread, but I think it applys to you as well.

Here is a direct quote from BOL http://msdn2.microsoft.com/en-us/library/ms152570.aspx

The following types of replication and replication options allow you to make changes at a Subscriber and have those changes flow to the Publisher:

Type of replication

Use when…

Merge replication

There are a large number of Subscribers.

Data is replicated to mobile users.

Replicated data is frequently updated at the Subscriber.

Data filtering is needed so that Subscribers receive different partitions of data.

Replication advice

Hello,
I have 30 remote WinXP sites connected to a Win 2K3 server via VPN.
Each remote site runs MSDE. Basically, they are automated data aquisition
machines recording into a DB about 200K of data every 15 minutes.
The data at each remote site needs to be replicated into its own DB on the
Win 2K3 server on a regular basis, hopefully within 1 or 2 hours of being
first recorded..
What form of replication would be recommended for this scenario?
Thank you
Unfortunately MSDE cannot be a transactional publisher which is the best fit
for what you are trying to do. What you will have to do is use merge
replication with the with the exchangetype being upload only.
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
"MikeH" <MikeH@.community.nospam> wrote in message
news:1AD6F4E7-B7B0-412E-BA9F-D05B7BE6168F@.microsoft.com...
> Hello,
> I have 30 remote WinXP sites connected to a Win 2K3 server via VPN.
> Each remote site runs MSDE. Basically, they are automated data aquisition
> machines recording into a DB about 200K of data every 15 minutes.
> The data at each remote site needs to be replicated into its own DB on the
> Win 2K3 server on a regular basis, hopefully within 1 or 2 hours of being
> first recorded..
> What form of replication would be recommended for this scenario?
> Thank you

Replication across the internet

Hi,
I want to replicate my data with a sql server in internet(via dial-up
connection).
Do I necessarily need to use FTP for transferring the initial snapshots or
it is an alternate solution?
I saw this in article number '321822' in Microsoft's site, the second
example in this article mentions:
Example 2: The Subscriber Does Not Have the Schema and Data, and the Initial
Subscription from the Publisher Is Required.
This example requires you to configure the FTP service at the Publisher, and
then configure the Subscriber to download the initial snapshot from the FTP
site. For more information, see the following topics in SQL Server Books
Online...
Thanks in advance,
Amin
you don't need to. Somehow you need to get the schema and data to the
subscriber. You can't use a UNC, so you have to use FTP, or get them there
via mail, courier or some other system.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Amin Sobati" <amins@.morva.net> wrote in message
news:OddhXyBXEHA.3640@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I want to replicate my data with a sql server in internet(via dial-up
> connection).
> Do I necessarily need to use FTP for transferring the initial snapshots or
> it is an alternate solution?
> I saw this in article number '321822' in Microsoft's site, the second
> example in this article mentions:
> --
> Example 2: The Subscriber Does Not Have the Schema and Data, and the
Initial
> Subscription from the Publisher Is Required.
> This example requires you to configure the FTP service at the Publisher,
and
> then configure the Subscriber to download the initial snapshot from the
FTP
> site. For more information, see the following topics in SQL Server Books
> Online...
> --
> Thanks in advance,
> Amin
>
|||Hilary,
If I'm creating a push subscription, and my publisher is distributor too,
then I suppose the snapshot on my local hard drive can be used by merge
agent, is it correct?
Thanks,
Amin
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OQ#8NYLXEHA.2844@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> you don't need to. Somehow you need to get the schema and data to the
> subscriber. You can't use a UNC, so you have to use FTP, or get them there
> via mail, courier or some other system.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:OddhXyBXEHA.3640@.TK2MSFTNGP11.phx.gbl...
or
> Initial
> and
> FTP
>
|||The distributor doesn't really factor into merge replication other than
being a repository where historical information is held.
You should be using a PULL subscription when you are replicating over the
internet. If you open the NetBIOS (IIRC) necessary for a push subscription
you are exposing your self to hackers.
"Amin Sobati" <amins@.morva.net> wrote in message
news:O00$21NXEHA.3512@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Hilary,
> If I'm creating a push subscription, and my publisher is distributor too,
> then I suppose the snapshot on my local hard drive can be used by merge
> agent, is it correct?
> Thanks,
> Amin
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OQ#8NYLXEHA.2844@.TK2MSFTNGP11.phx.gbl...
there[vbcol=seagreen]
snapshots[vbcol=seagreen]
> or
Publisher,[vbcol=seagreen]
the[vbcol=seagreen]
Books
>
|||Hilary,
I'm only the owner of the database at subscriber, and there's some errors
while trying to create pull subscription. Our sql server host(subscriber)
has added my publisher name(computer name) as a remote server and mapped all
of our logins(local) to the login which was created at their sql server. But
still there're some error indicating the loss of permissions.
What's the best solution?
Thanks,
Amin
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:#7ZT5bQXEHA.2364@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> The distributor doesn't really factor into merge replication other than
> being a repository where historical information is held.
> You should be using a PULL subscription when you are replicating over the
> internet. If you open the NetBIOS (IIRC) necessary for a push subscription
> you are exposing your self to hackers.
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:O00$21NXEHA.3512@.TK2MSFTNGP12.phx.gbl...
too,[vbcol=seagreen]
> there
dial-up[vbcol=seagreen]
> snapshots
second
> Publisher,
> the
> Books
>
|||Hilary,
I'm only the owner of the database at subscriber, and there's some errors
while trying to create pull subscription. Our sql server host(subscriber)
has added my publisher name(computer name) as a remote server and mapped all
of our logins(local) to the login which was created at their sql server. But
still there're some error indicating the loss of permissions.
What's the best solution?
Thanks,
Amin
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:#7ZT5bQXEHA.2364@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> The distributor doesn't really factor into merge replication other than
> being a repository where historical information is held.
> You should be using a PULL subscription when you are replicating over the
> internet. If you open the NetBIOS (IIRC) necessary for a push subscription
> you are exposing your self to hackers.
> "Amin Sobati" <amins@.morva.net> wrote in message
> news:O00$21NXEHA.3512@.TK2MSFTNGP12.phx.gbl...
too,[vbcol=seagreen]
> there
dial-up[vbcol=seagreen]
> snapshots
second
> Publisher,
> the
> Books
>

Replication

hello
i have to merge data to sql server via gprs in my application developed in
vb 6.0.
can any one help me how can i send data to sl server via gprs
Thx a lothave a look at
http://msdn.microsoft.com/library/d...ication_709.asp
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Nazeedah" <Nazeedah@.discussions.microsoft.com> wrote in message
news:8726F83D-523F-477D-A2DD-1710204FAA05@.microsoft.com...
> hello
> i have to merge data to sql server via gprs in my application developed in
> vb 6.0.
> can any one help me how can i send data to sl server via gprs
> Thx a lot
>|||Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Nazeedah" <Nazeedah@.discussions.microsoft.com> wrote in message
news:8726F83D-523F-477D-A2DD-1710204FAA05@.microsoft.com...
> hello
> i have to merge data to sql server via gprs in my application developed in
> vb 6.0.
> can any one help me how can i send data to sl server via gprs
> Thx a lot
>

Replication

hello
i have to merge data to sql server via gprs in my application developed in
vb 6.0.
can any one help me how can i send data to sl server via gprs
Thx a lotNaz,
If you had a local instance of MSDE/SQL Server running on the local
machine/device able to connect via GPRS, you could, feasibly do a replicatio
n
or DTS over the connection to your other server/device.
I think to help though, we need more information on what devices,
connections, etc that you have.
James.
"Nazeedah" wrote:

> hello
> i have to merge data to sql server via gprs in my application developed in
> vb 6.0.
> can any one help me how can i send data to sl server via gprs
> Thx a lot
>|||Hello
Thx for your reply and concern
Well the application developed is on VB 6.0 and i have to merge the data to
sql server 2000.
The device used is a tablet PC
Please help me of how shouls i process
Regards
Nazeedah
"James Vickers" wrote:
[vbcol=seagreen]
> Naz,
> If you had a local instance of MSDE/SQL Server running on the local
> machine/device able to connect via GPRS, you could, feasibly do a replicat
ion
> or DTS over the connection to your other server/device.
> I think to help though, we need more information on what devices,
> connections, etc that you have.
> James.
> "Nazeedah" wrote:
>

Replicating User Defined Data Types

Hey guys,
Thanks so much for this great newsgroup btw, helped me hugely through doing
replication via handheld units etc.
I'm running into a bit of a problem between server 2 server replication.
Both databases have user defined data types. All the tables using them are
fine, but once the snapshot is applied to the subscriber, and the replication
is hooked up to the publisher, all the data types are converted to their base
types (char 10 etc)
Is there any way to stop this? The subscriber needs to keep the user defined
data types.
I noticed in the publication wizard there is an option that says "Keep the
existing table unchanged" but the default value for this is "DROP the
existing table and re-create it" this is all under the Snapshot tab of the
article properties.
If I set it to "Keep the existing table unchanged" will that fix my problems
or is that not the proper fix for this issue?
Thanks and take care!
Simon,
on the article propertuies at the bottom there is the option to change user
defined datatypes to base types and by default this is enabled. Also, you
can add the user defined types themselves to a prescript.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replicating twice

I have a question that google seems eerily quite on. I'm running SQL
2005 and am replicating (merge via the web) from an intermediate box
which is also running 05. The intermediate box is replicating in some
fashion (unsure since it isn't under my control) from a SQL 2K box.
My issue is that when the job executes it says "Merge completed with no
data changes processed." even though the intermediate box has updated
data on it. Is this common? Is the answer so obvious that everyone is
laughing at me now?
Thanks
Jeremiah
It should work. How is your global priority configured? You might want to
check the conflict viewer to see if these conflicts are logged.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"JC" <jbzcooper@.gmail.com> wrote in message
news:1141343267.632060.296050@.u72g2000cwu.googlegr oups.com...
>I have a question that google seems eerily quite on. I'm running SQL
> 2005 and am replicating (merge via the web) from an intermediate box
> which is also running 05. The intermediate box is replicating in some
> fashion (unsure since it isn't under my control) from a SQL 2K box.
> My issue is that when the job executes it says "Merge completed with no
> data changes processed." even though the intermediate box has updated
> data on it. Is this common? Is the answer so obvious that everyone is
> laughing at me now?
> Thanks
> Jeremiah
>
|||Jeremiah,
If I understand correctly, your SQL 2005 is subscribing from an intermediate
box, which subscribes from a SQL 2K server? Please note that for merge
replication, the publisher database version has be to newer than the
subscriber version, so basically SQL Server 2005 cannot be a subscriber of a
SQL 2000 publisher.
Yi Chen
"JC" <jbzcooper@.gmail.com> wrote in message
news:1141343267.632060.296050@.u72g2000cwu.googlegr oups.com...
>I have a question that google seems eerily quite on. I'm running SQL
> 2005 and am replicating (merge via the web) from an intermediate box
> which is also running 05. The intermediate box is replicating in some
> fashion (unsure since it isn't under my control) from a SQL 2K box.
> My issue is that when the job executes it says "Merge completed with no
> data changes processed." even though the intermediate box has updated
> data on it. Is this common? Is the answer so obvious that everyone is
> laughing at me now?
> Thanks
> Jeremiah
>
|||Are there any filters on the publisher that make the data not qualify for
the subscriber?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"JC" <jbzcooper@.gmail.com> wrote in message
news:1141343267.632060.296050@.u72g2000cwu.googlegr oups.com...
> I have a question that google seems eerily quite on. I'm running SQL
> 2005 and am replicating (merge via the web) from an intermediate box
> which is also running 05. The intermediate box is replicating in some
> fashion (unsure since it isn't under my control) from a SQL 2K box.
> My issue is that when the job executes it says "Merge completed with no
> data changes processed." even though the intermediate box has updated
> data on it. Is this common? Is the answer so obvious that everyone is
> laughing at me now?
> Thanks
> Jeremiah
>

Replicating over VPN to non trusted workgroup

I have a situation where a client connects to our network via VPN has set up
a SQL Server on a small workgroup that wishes to subscribe (via
transactional replication) to publications on my SQL Publisher.
My stumbling block is permissions to the \\myserver\repldata folder on our
domain. The client machine being a non-trusted domain (in fact not even a
domain) means I can't set up read and list files & folders permissions.
My plan is that I set up the pull subscriptions, create the snapshots and
then copy the entire \\myserver\repldata folder to the client machine and
point the pull subscriptions there.
Is this the best solution in the circumstances? After the initial snapshot,
does transactional replication ever need to access \\myserver\repladat
again?
Cheers!
Simon
Simon,
you can use the alternative snapshot location option - I do it this way for
large snapshots which I zip up before sending over.
If FTP is available this is probably an easier option though to set up the
publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Many thanks.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OzVonAjYHHA.208@.TK2MSFTNGP05.phx.gbl...
> Simon,
> you can use the alternative snapshot location option - I do it this way
> for large snapshots which I zip up before sending over.
> If FTP is available this is probably an easier option though to set up the
> publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||Try to use pass-through authentication. This is where you have two identical
local machine accounts with the same name and the same password.
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
"Simon" <simon.gladwell@.gmailNotThisBit.com> wrote in message
news:%23W9Y9siYHHA.448@.TK2MSFTNGP04.phx.gbl...
>I have a situation where a client connects to our network via VPN has set
>up a SQL Server on a small workgroup that wishes to subscribe (via
>transactional replication) to publications on my SQL Publisher.
> My stumbling block is permissions to the \\myserver\repldata folder on our
> domain. The client machine being a non-trusted domain (in fact not even a
> domain) means I can't set up read and list files & folders permissions.
> My plan is that I set up the pull subscriptions, create the snapshots and
> then copy the entire \\myserver\repldata folder to the client machine and
> point the pull subscriptions there.
> Is this the best solution in the circumstances? After the initial
> snapshot, does transactional replication ever need to access
> \\myserver\repladat again?
> Cheers!
> Simon
>
|||That works when I log on that account and browse to the folder. Should I
then run SQL Server and Agent under that local account?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OiI0V2jYHHA.4308@.TK2MSFTNGP05.phx.gbl...
> Try to use pass-through authentication. This is where you have two
> identical local machine accounts with the same name and the same password.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
|||Yes!
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
"Simon" <simon.gladwell@.gmailNotThisBit.com> wrote in message
news:eQmS6QlYHHA.4692@.TK2MSFTNGP04.phx.gbl...
> That works when I log on that account and browse to the folder. Should I
> then run SQL Server and Agent under that local account?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OiI0V2jYHHA.4308@.TK2MSFTNGP05.phx.gbl...
>

Replicating Multiple Tables

I can't seem to find the answer to this in the documentation.

I have several tables that I want to replicate via transactional replication. These tables do have some foreign keys between them.

What are the advantages/disadvantages of "one table per publication" versus "one publication with all of these tables listed as articles"?

If I do not define these in the same publication, will I get into trouble with the order that data is loaded to satisfy the foreign key constraints?

THe big disadvantage is management overhead of multiple publications. But for your scenario, you can mark FKs as not for replication at the subscriber if you want. However with tran, it will maintain and replicate in the exact order it was applied at the publisher.