.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Imran Ghani
Post New Web Links

transactional replication - no initial snapshot generated

Posted By:      Posted Date: September 28, 2010    Points: 0   Category :Sql Server
 

When I try to set up a transactional replication, setting it up works fine, but then I get an error from the snapshot assistant: the execution stops at the table sysdiagrams with the error message that the filed „definition“ uses the type varbinary(max).

There was no specific error code, i.e. the error code displayed was '0'

If I try to set up a snapshot replication the sysdiagrams table is not included and it runs through just fine.

 



View Complete Post


More Related Resource Links

Cannot get Transactional Replication Snapshot to send

  

I have a database called reference that exists on server A and server B.  Both server A and B are publishers and both are their own distributors.  Certain articles are replicated (push) from the reference database on server A to server B and certain articles are replicated (push) from the reference database on server B to server A.  I recently had to restore the reference database on server B with a backup from server A (here is where my problems started).  I deleted the existing server A subscription from the server B publication and then deleted the server B publication, after that I rebuilt the server B publication and added the server A as a subscriber.  When I generated a snapshot of the server B publication the subscription will not recognize the snapshot and continues to say that the snapshot has not been fully generated (yes I verified that the snapshot was fully generated) at this point I even tried to reinitialize the subscription with a new snapshot and I netted the same result.

I then deleted the server B subscription to the server A publication and then deleted the server A publication and then took a backup of server A reference database restored it on server B and ran through the same steps in paragraph 1 with the same result.

I have tried, sp_replrestart, sp_replflush, sp_removedbreplication 'reference', 'tran' an

Transactional Replication - Creating Snapshot after adding a new table

  

Hello,

 

Sorry for my English..

 

Could you tell me if it is possible not to create entire snapshot after adding a new table? Our database is very big and it takes a lot of time to make snapshot.

 

Also could someone tell me exactly how works blocking of tables while creating snapshot?

I've read in BOL that if Snapshot Replication is used then all tables are blocked while creating snapshot for replication. And if Transactional Replication is used, snapshot is created in parallel mode and tables are not blocked and users can work with them. I understand that in second case each table is blocked while it is processing and after processing it is unblocked and next table is blocked. Is it so?

 

Also I am interested how actually tables are blocked? For SELECT/UPDATE/INSERT or how? Where can I read information about it?

 

Thanks,

V

When doing Snapshot Replication and Transactional Replication, are the Foreign Keys and Primary Keys

  
When doing Snapshot Replication and Transactional Replication, are the Foreign Keys and Primary Keys replicated?
lcerni

Locking published tables while generating the snapshot infinite (Transactional replication)

  

I set up a transactional replication, publisher 2005 SP2 to a subscriber SQL 2008r2. The database is about 90 GB. When I want to create a Snapshot, the snapshot agent is running to 96% and then give a error. I have set up some logging, here is the tail of it:

------------------------

2011-05-16 20:16:15.32 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$XBRLAutorisatieRegister' during concurrent snapshot into the distribution database.
2011-05-16 20:16:15.32 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$XBRLBericht' during concurrent snapshot into the distribution database.
2011-05-16 20:16:15.33 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$XBRLBerichttype' during concurrent snapshot into the distribution database.
2011-05-16 20:16:15.33 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$XBRLFault' during concurrent snapshot into the distribution database.
2011-05-16 20:16:15.33 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$Ziekmeldingsregistratie' during concurrent snapshot into the distribution database.
2011-05-16 20:16:15.33 [96%] Inserted stored proc to disable constraint/triggers article 'WGF B.V.$Ziekte Bijzonderheden' during concurrent snapshot into the distribution database

Transactional replication: Updates to one of my columns (sometimes) does not get replicated from the

  
hi, This is my scenario: I have a varchar(max) column on a table that is replicated to other servers. I have a stored procedure that does and update to this column and other columns on the table. This is done inside a transaction that also includes other updates. When I call the stored procedure, this one varchar(max) column's value is not replicated. When I just run the single update statement on its own, the value is replicated. Anyone know something about this? Thanks!    

transactional replication and sp_MSupd tables

  
Hi thre, My target server keeps running sp_MSupd sp for different values in profiler and synch status shows "delivering replication commands" I keep waiting but this never ends. and ultimately we run a snapshot and it can't be applied. So recreate the whole replication again. It runs fine for a while but again, the sp_MSupd sp starts running on the target server and the replication goes down again. I don't know how to fix this. any advices?   Regards

Merge Replication: How to give read access on snapshot share to a sql account

  
Hello All, I want to give read access to an sql account(not windows account). Can it be given or not. Someone please tell. Thanks saandii777

transactional replication falling behind. millions of transactions in sp_brosereplcmds

  
Hi, Hi All, we have transactional replication set up with all the agents running at source database. The synchronization status is stuck at "delivering replication transactions". The replication latency in replication monitor says excellent. when I check sp_browsereplcmds. it ever ends. It has over more than millions of rows of output. We were having trouble with this replciation recently because at the source db. they run a large batch. So we decided to schedule snapshot agent to run exactly after that batch but the snapshot doesn't seem to helping out with the latency. what should be done to resolve the issue. Please help. thanks.

Transactional Publication With Article Using DATE Datatype Causes Snapshot Agent to Fail with Error

  
So I am using SQL Server 2008 Enterprise Edition (64Bit) and have run into a strange problem that is baffling me. I have two tables that I need to replicate.  They are identical in specification except the name (one is for monthly stats and one daily stats) CREATE TABLE [dbo].[statsDailyLite]( [EntryDate] [date] NOT NULL, [SetID] [int] NOT NULL, [ProductID] [smallint] NOT NULL, [Hooks] [int] NOT NULL, [AdViews] [int] NOT NULL, [Clicks] [int] NOT NULL, [UAdViews] [int] NOT NULL, CONSTRAINT [PK_statsDailyLite] PRIMARY KEY CLUSTERED ( [EntryDate] ASC, [SetID] ASC, [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ) GO CREATE TABLE [dbo].[statsMonthlyLite]( [EntryMonth] [date] NOT NULL, [SetID] [int] NOT NULL, [ProductID] [smallint] NOT NULL, [Hooks] [int] NOT NULL, [AdViews] [int] NOT NULL, [Clicks] [int] NOT NULL, [UAdViews] [int] NOT NULL, CONSTRAINT [PK_statsMonthlyLite] PRIMARY KEY CLUSTERED ( [EntryMonth] ASC, [SetID] ASC, [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ) GO They both conatin a thousand or so test rows of data, and they are both in a transactional publication going to another SQL 2008 box. Now when the tables are empty

getting an e-mail notification when a transactional replication stucks

  
Hi all, I've SQL Server 2005 and a transactional replication scenario with one puplisher and 11 subscribers. Sometimes one of the subscriptions fails and a manual operation is required. Is there a way to get an e-mail notification whenever the replica fails or stucks or simply degrade its performances?

How to let a non admin business user run a replication snapshot job?

  
According to the msdn library, I would have to give the non admin user (User1) SQLAgentUserRole privileges. But it also says that it has to be a local job and not a multi server job. Is the snapshot agent job a local or multi server job? Considering the snapshot agent is a local job and i make USER1 the owner of that job. He should be able to run the job rite? I need to give USER1 the ability to run sp_start_job to start the snapshot agent ONLY! (and not any other jobs). So what are the steps to do this? I also read that the only way to achieve this is by creating a proxy account? Is there an article somewhere that best describes this?  

Merge Replication, Push Subscription : The snapshot takes centuries to apply

  
Well, not centuries, except that the users are storming the gate. I'm trying to find how to get the snapshot moved to the subscriber and applied in a reasonable time. Last time was successful, but took 2 1/2 days to build the subscriber database from the snapshot. Hillary responded: Something is very wrong here. You should be able to generate your snapshot, copy it manually over to the subscriber - using the altsnapshotfolder parameter and then apply it there. So now I have my snapshot, a folder with lots of .cft .bcp .dri .prc .sch  and .trg files. Getting this to the subscriber computer shouldn't take long. Once I get it there, how do I use it to get the subscriber set up?  You can't be successful at this unless you're at least 1/2 a bubble off level.

Issue with transactional replication on SQL 2005 SP3 using row filters with bit columns

  
We recently upgraded from SQL 2000 to SQL 2005 SP3 and I am noticing something that I think is a bug with replication from a publication that has an article in it with row filters.  It seems to be that if the row filter has columns that are bit datatype and that column is updated on the publisher to cause the row filter to exclude it, it is not removed from the subscriber.  If you update a column that is part of the row filter that is not a bit datatype it works as expected.  I can reproduce this in a test environment with a small base table.  In production this is resulting in an occasional 20598 error "The row was not found at the Subscriber when applying the replicated command". Is anyone aware of a hotfix that addresses this? I can think of a few work arounds but the table is rather large and I really would like to avoid having to pull replication, re-snap or changing datatypes on the base table etc. Any suggestions are greatly appreciated.  Thanks!

A query in Snapshot replication ---- Please help !!!!!

  
Hi,     I have a SQL Database with different roles and users,depends upon their roles,permissions,they can see some table,views and etc.     Now,I am going to replicate (Snapshot) the same Database into different server.    My query is, in the source database incase if they do some changes in the roles,permissions,adding roles/updation of users happens, the same will be replicated in the  destination databsase as well? One of my friend told that ,snapshot replication just deals with records inside the Database and does not deal with users/roles/permissions...Is that so?

set reinitialization period on Transactional replication

  
How do you set the parameters on Standard Transactional Replication to Reinitialize after a certain period of time? Thank you.

which is best Snapshot Replication or Transaction Replication for my requirement?

  
Hi,    We have a main transaction database where all the live data is there.Now, to run a reporting application,we need a  different database, but the data is as EXACTLY as the live database.So,probaly every noght aroung 1 0'clock,we want to sync these two databases.   In this scenario,which is better, Snapshot replication or Transaction Replication? what are the pros and cons of using them? As I am new to this things,ur answers would be very much helpful..

Merge Replication Compressed Snapshot

  
Hi, How do I go about compressing my snapshot files? I am unable to find a tutorial explaining how to do it.
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend