Date Base Replication

• Manoj Sahoo

  SSC Rookie

  Points: 47

  More actions

  January 25, 2012 at 2:39 am

  #267203

  Hi All,

  i am facing one big problem regarding Database replication. The Database Replication is that uploading the data from one local server to another remote server.

  here is the description

  I am accessing data from my own local server. after 15-20 days i have to upload data from local server to remote server and this procedure is a cycle.

  the sql statement i have written is

  Insert into OPENROWSET('MSDASQL',

  'DRIVER={SQL Server};SERVER=[SERVER NAME];UID=sa;PWD=[PASSWORD]',

  DATABASENAME.dbo.TABLENAME) select * FROM TABLENAME

  if i am writing this sql statement inside a procedure inside the

  Begin Transaction

  End

  block it throws me a error.

  the error is

  OLE DB provider "SQLNCLI10" for linked server "" returned message "No transaction is active.".

  Please Help Me .

  Any help is highly appriciated.

  Thank You

  MAnoj Sahoo

• umasingh

  Ten Centuries

  Points: 1182

  More actions

  January 25, 2012 at 2:47 am

  #1438144

  you need to change DTC configuration setting. please check this link for more details.

  http://sqlservermemories.blogspot.com/

• Brandie Tarvin

  SSC Guru

  Points: 173095

  More actions

  January 25, 2012 at 6:22 am

  #1438197

  It doesn't actually sound like you're using Database Replication (which is an actual feature). If I'm reading this right, you're just using a job to do a data grab. Correct?

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.