Changing Article Properties Without A New Snapshot

Jan 17, 2007

In our replication environment, the subscriber is initially set up with an snapshot of the publisher database. However, after that, the subscriber and publisher are different and we can never re-initialize from a snapshot again (we purge data on the publisher to reduce the database size but do not purge the same data on the subscriber; we do this by stubbing out the stored procedures on the subscriber that purge data on the publisher).

If an article is added or dropped from the publication, using snapshot and synchronize, just these changes are propagated to the publisher (without an entire new snapshot).

However, if an Article Property is changed (change SCALL to MCALL under Statement Delivery options for the UPDATE statement), the interface REQUIRES an entire new snapshot. Is there any way I can avoid the new Snapshot? It overwrites the subscriber database and this cannot happen!

Linda

View 5 Replies


ADVERTISEMENT

How To Add Tabls To Published Article Without Recreating Snapshot?

Jan 26, 2007

Hi experts:

We have 4 SQL SERVER 2000 servers linked via replication, and due to business changes we have to add one table to the published databases. And the existed database has more than 20g, recreating the snapshot and re-init replication is not allowed as the business cannot be stopped more than 1 hour. So my question is how to add tables to the published article without recreating the snapshot?

By that is not possilbe, can we publish one new article on the same database?



Thanks in advance!

Ron

View 1 Replies View Related

SQL Server 2008 :: Transactional Replication Snapshot - Does Not Generate Any Files For New Article

Oct 22, 2015

There is a SQL Server 2008 R2 SP3 Clustered Instance that has Transactional Replication. It is by no means a large replication setup in terms of data/article count. SQL Server was recently patched to SP3 and is current on Windows 2008 R2 Patches.

When I added a new article to replication (via 2014 SSMS GUI) it seems to add everything correctly (replication tables/procs show the new article as part of the publication).
The Publication is set to allow the snapshot to generate for just new articles (setting immediate_sync & allow_anonymous to false).

When the snapshot agent is run, it runs without error and claims to have generated a snapshot of 1 article. However the snapshot folder only contains a folder for the instance (that does have the modified time of the snapshot agent execution) and none of the regular bcp/schema files.

The tables never make it to the subscribers and replication continues on without error for the existing articles. No agents produce any errors and running the snapshot agent w/ verbose output provides no errors or insight into any possible issues.

I have tried:

- dropping/re-adding the article in question.

- Setting up a new Snapshot Folder

- Validated all the settings and configurations

I'm hesitant to reinitialize a subscriber since I am not confident a snapshot can be generated. Also wondering if this is related to the SP3 Upgrade, every few months new articles are added to the publication and this is the first time since the upgrade to SP3 that it has been done.

View 0 Replies View Related

Error When Changing Published Article At The Subscriber

Mar 9, 2006

Hi


Using merge replication - the stored procs are published articles.


When trying to change the article at the subscriber (sql express) I get the following errors:

Msg 21531, Level 16, State 1, Procedure sp_MSmerge_alterschemaonly, Line 45
The DDL statement cannot be performed at the Subscriber or Republisher.

Msg 21530, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 181
The DDL operation failed inside merge DDL replication manipulation.

Msg 3609, Level 16, State 2, Procedure usp_Anniversary_upd, Line 21
The transaction ended in the trigger. The batch has been aborted.



Does this mean it can only be changed at the publisher ?

Bruce

View 5 Replies View Related

Changing Properties In A DTS Package

Sep 26, 2000

Is it possible to change the database name defined in the destinatation table definition of the transformation object programmitacally?

We have different database names between dev and prod. The package was created against dev. I'm trying to copy the package to the prod server and programmatically change the database name where necessary.

View 4 Replies View Related

Error Changing Subscription Properties

Dec 1, 2005

Hi

View 6 Replies View Related

Dynamically Changing The Connection Properties

Apr 18, 2007

I want to transfer data from one server to another by using SSIS. i want the connection string to be dynamic and also according to the some other variable, the transforming data is changing.

Could you provide me the solution thet how i am able to change my connetction string dynamically and the other variable too.

i am using VS 2003 as front end and SQL server 2005 as a backhand.

Due to VS.NET 2003 i am able to create DTS packages but i have to migrate it and then anly i am able to use it in JOB in SQL server agent of SQL server 2005.

is that any code or any stored procedure from which i am able to migrate DTS packages to SSIS packages.

Thank you

View 4 Replies View Related

Changing MS SQL Server Express Table Properties

Jul 13, 2005

Hi,

I have a MS SQL Server Express database wich I upsized once from MS
Access. I can connect to it and get/insert data but I can't update the
field properties.

I've used Access 2003, Visual Studio .Net and SQL Server Web Data
Administrator, but I can't change the properties of existing tables. I
cán add new tables and add their properties afterwards. Since I have a
lot of tables it would be too much work too recreate them all. So how
can I edit my existing tables?

View 1 Replies View Related

Restore From Snapshot After Changing Recovery Model

Oct 18, 2007

Given the follwoing scenario: You create a snapshot of a database with full recovery model, change it's recovery model to simple, then perform several updates/modifications on the database, before you finally (due to an error) restore the database from the snapshot.

Will the log chain be broken or not? The resteore sets the recovery model back to full, but I'm still a bit "curious" about the transaction logs.

View 7 Replies View Related

SQL Server 2008 :: Changing Alt Snapshot Folder For Merge Replication?

Sep 22, 2015

I have a merge replication. Currently works fine. Publisher & Distributor are on the same server. I need to change the location of the alternate folder for the snapshot files.

I’ll probably just change it through the GUI, but would I use sp_changedistpublisher or sp_changemergepublication if I were scripting everything?

My real concern is the subscribers. Do I have to ‘tell’ the subscribers where the alt folder has been changed to? Do I just run sp_changemergepullsubscription on the subscribers?

View 1 Replies View Related

SQL 2012 :: Connection Properties Versus SSMS Server Properties

Mar 16, 2015

I have an ODBC connection string that is working fine with the following properties:

Database="XXXXXXX",Network="YYYYYY"; strangely no server is specified in the string, but it is specified in the ODBC Connection file.

I am trying to do a new server registration in SSMS for this database.However, I don't understand where the network spec is placed.

Under Registered server name I've tried:

YYYYYYXXXXX

When I browse the server for the database instance list, I receive "network path was not found".

I even tried:"XXXXXXX",Network="YYYYYY" for the registered server name.Same error message.

What am I doing wrong ?

View 1 Replies View Related

Problem When Applying A Snapshot When Tables Have Been Updated During Snapshot Generation

Jun 20, 2007

Hi

I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated.



Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name.



The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors.



I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name.



All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.

View 1 Replies View Related

Article

Jan 9, 2004

Tech firms defend moving U.S. jobs overseas (http://www.msnbc.msn.com/default.aspx?id=3899043&p1=0)

Any thoughts or comments?

View 14 Replies View Related

MS Kb Article 825019 And Beyond

Aug 8, 2006

I am experiencing the behaviour described in this kb article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;825019

but I am reliably informed that both the sql 2000 boxes which are linked have been patched to sp4.

Has anyone had any experience of this issue recurring post patching?

View 1 Replies View Related

How To Add New Article To Publication?

Dec 23, 2004

Hi,
I'm use MSSQL2000 SP3 with replication.
I want to know that Can i add new article to existing publication by
Enterprice Manager ?

someone told me that we can do that in older version but not now,right? :confused:

View 1 Replies View Related

How To Ask A Question...(MS KB Article)

Feb 2, 2006

MS has post a KB article on how to post a question on a online forum...

http://support.microsoft.com/kb/q555375

At first I though this was hilarious, but there is actually some good information in here that is applicable on this forum as well as anywhere else. Perhaps Brett could add this as a sticky?

Regards,

hmscott

View 2 Replies View Related

Add New Article To The Publisher

Jun 19, 2008

Dear All,
i'm in the transactional replication environment. we need to add one new table to the publisher. it is sql server 2005 environment. please explain me the steps

and in another table, i need to change the data type of a table.

please guide me

Arnav
Even you learn 1%, Learn it with 100% confidence.

View 15 Replies View Related

Article About Replication

Oct 31, 2007

hi
i'm new to replication. i'm looking for a full article that explains Replication good, can u take a link to that article ?
thanks

View 5 Replies View Related

Sp_dropsubscription, Article

Sep 26, 2005

Hi There

View 13 Replies View Related

Unpublishing An Article

Mar 8, 2006

Hi:

If I want to unpublish an article thru Enterprise Manager, it does not allow me to uncheck it at publication properties. It only allows me to uncheck articles if I delete the subscription first. Does anybody know how tp do this in Enterprise Manager without deleting the subscription?

Thanks



View 3 Replies View Related

Same Article In Different Publications

Sep 23, 2005

Hi!

View 1 Replies View Related

Add Article To Transactional Replication?

Oct 14, 2014

Here's what my script boils down to when adding a new view (admin.replication_test):

Code:
EXEC sp_addarticle
@publication = N'<publication name>'
, @article = N'replication_test'
, @source_owner = N'admin'
, @source_object = N'replication_test'
, @type = N'view schema only'
;

If I check the GUI, the article appears to be ticked in the publication.

Generate a new snapshot.

View not found in replicated database!

View 9 Replies View Related

Article On Advantages Of Sql Server Over VFP

Oct 9, 2007

hi guys,

im new to sql server...

i am currently studying sql server 2000

i know this subject has been asked before...

i would just like to ask if you know some related articles regarding advantages of using sqlserver over vfp dbf/dbc?

i am going to discuss it to my class.

thanks very much!

any help is very much appreciated!

Joel

View 4 Replies View Related

Adding New Article In Replication

Mar 27, 2008

how to add new article within the existing replication

View 1 Replies View Related

Article For Subscription That Does Not Exist ?!?

Jun 2, 2006

Hi There

I hope someone can help me asap.

I need to alter a column on a replicated table.

So i execute sp_dropsubscription and sp_droparticle for the table for all publication to that article in the database.

Usually i just alter the table and then execute sp_adddarticle and sp_addsubscription afterwards and everything is cool.

However in this case after i drop the subscriptions for the article, when i try alter the table it says it is being replicated.

I query sysobjects and see that the table has replinfo = 1 , this is snapshot replication, but i only have transactional replication ? I used to have snapshot but that was dropped long ago.

I then query sysarticles and i find the table, however the pubid (publication id) for the table is equal to the publication id of another databases publication ??? I also have found in sysarticles articles with a publication id's equal to publications that do not exist, for example articles will have pubid of 2 , but if i run sp_helppublication on all user databases there is no publciation with an id of 2 ?

Please help, even after i drop all subscriptions to an article it seems that sql server thinks the article belongs to another databases publciation or publications that no longer exist?

Maybe there is some sort of cleanup sp i can run or something but it seems to me sql server has gotten articles confused with old deleted publications that no longer exist.

Therefore after dropping all subscriptions i still cannot alter tables as sql server think it is still being published but it is not!

Thanx

View 4 Replies View Related

Article Row Filter - 2 Parameters

Nov 10, 2006

hello,

i need to filter an article based on a user-supplied datetime filter (the datetime parameter is specified by the subscriber just before replication). at the same time i need to filter again by user (different subscribers get different rows).

i already did the user-based filter using HOST_NAME( ). but the difficulty here (al least i think so) lies in passing 2 parameters to the filter. i cannot rely on using SUSER_SNAME to pass the user filter, because no one will want to create 500 user accounts. so i guess the only solution here is to pass both parameters using only HOST_NAME( ) and then write 2 splitting functions which uses HOST_NAME( ) as its parameter. am i right ?

publisher/distributor is sql server 2005, all subscribers use sql mobile.

TIA, kamil nowicki

View 10 Replies View Related

Is There Any Way To Just Reinitialize Only The Changed Article?

Oct 24, 2007

Hi,

I'm setting up Transaction Replication b/w SQL Server 2K and SQL Server 2K5.
I have published Tables, Views and SPs as articles.
When I try to modify the published Stored procedure, the changes are not replicated.

When I Reinitialize the Subscription and start the Snapshot agent, it is copying the changes

made. But all articles are reinitialized again, So it takes huge time to do this.

Is there any way to just reinitialize only the changed article?
Or Is there any work around for this problem?

Than

View 1 Replies View Related

Upgrading Packages Article

Nov 3, 2006

The chapter from my book that talks about upgrading packages from DTS to SSIS has been posted here : http://www.quepublishing.com/articles/article.asp?p=605035&rl=1

Didn't even know it until I happened to be doing a search on upgrading and the article popped up in the search...

How funny is that! :)

Hope it helps.

View 2 Replies View Related

Msdn Article On Getting @Identity Question

Sep 26, 2007

the msdn article explains how to input new data to a databse and return the primary key of the inputted row. It works perfectly. However, it explains how to insert a value, but there is a problem. It seems to set the value to be submitted in the VB code, instead of having the value be the user's input in the textbox. Look for the following line in the code below: newRow("ClientFileNumber") = "ClientFileNumber" It inputs "clientfilenumber" instead of what is actually inputted in the texbox. Does anyone know how to bypass this, to not have the words "clientfilenumber" inputted?Dim sqlconn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Client.mdf;Integrated Security=True;User Instance=True")
 Dim catDA As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Orders", sqlconn)
 catDA.InsertCommand = New SqlCommand("JobInsert", sqlconn)
catDA.InsertCommand.CommandType = CommandType.StoredProcedure
 catDA.InsertCommand.Parameters.Add("@ClientFileNumber", SqlDbType.VarChar, 50, "ClientFileNumber")Dim NewJobNumber As SqlParameter = catDA.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "OrdersID")
NewJobNumber.Direction = ParameterDirection.Output
 
sqlconn.Open()
 Dim catDS As DataSet = New DataSetcatDA.Fill(catDS, "Orders")
 
Dim newRow As DataRow = catDS.Tables("Orders").NewRow()
newRow("ClientFileNumber") = ""catDS.Tables("Orders").Rows.Add(newRow)
 catDA.Update(catDS, "Orders")
 
sqlconn.Close()
 
Response.Redirect("control_page_copy.aspx?NewJobNumber=" + NewJobNumber.Value.ToString())

View 1 Replies View Related

News / Article Application Storage

Jun 2, 2006

Hi,
I'm building a straightforward-ish news site. Currently, the story details (e.g. headline, description, author, date) are stored in a SQL DB. The news is categorised and the category details are also stored in an SQL table.
However - what is the best way to store the actual news article?
At the moment, when the user enters a story, they input the details and article text. The details are saved in the DB and the text is saved as an XML file with filename corresponding to the article DB primary key. When the story is "read" the app pulls the details from the DB and loads up the appropriate XML file from disk. 
For various reasons, I need to keep the article stored as XML, either in a file or in the DB. The DB provides for faster sorting and retrieval, and I don't want to store large amounts of data (i.e. the article itself) in the DB if avoidable.
I guess there are a few ways to do it -
1. Store details in DB and article as XML file.2. Store details and article in same DB table3. Store details in one DB table and articles in another
I would imagine that 3 would be the best, but would there be a performance hit? What is the maximum size of field (i.e. article size) I can have in a table?
Cheers
Graham Wilson.

View 3 Replies View Related

Replication - How To Add Article From Enterpise Manager

Feb 3, 2001

Hi,

It would be very nice if some people out there, using merge replication can tell me their strategy to add Article through Enterprise Manager.

I have Merge Replication, I have new table which has to be replicated. I counld not find options in Wizard to add New article for replication.

Any Help will be appreciated.

Thanking you in anticipation

Cheers
Hemant

View 1 Replies View Related

Added Column To Article But DTS Isn't Recognizing It

Dec 9, 2002

We're replicating columns in a table from 7.0 server to SQL2k server. Once we've got the data on SQL2k, we were able to take advantage of DTS that was not available in version 7.0.

I recenlty added a column to the article and changed the DTS accordingly (transformation columns). However, I'm getting this error ever though I added the new column in the DTS.

-------
The number of columns in the bcp file does not match what is defined in the DTS package. Regenerate the package.

-----

I've recreated the package with no luck. This is becoming urgent as it's supposed to go into production soon.

Please help!!

Thanks,

Colleen

View 2 Replies View Related

Adding Article To The Existing Replication

Feb 11, 2005

Hi All,

I am having a server where replication is set up between 2 differnt databases. It is currently running. I want to add a couple of tables to the replication. I tried using sp_addArticle, but after executing it, in the properties of the publication it shows the new tables, but at the database level the tables are missing.

I tried with sp_addsubscription but I am getting strange error:

Server: Msg 14100, Level 16, State 1, Procedure sp_addsubscription, Line 240
Specify all articles when subscribing to a publication using concurrent snapshot processing.


What can I do to publish the tables into the target database?

Thanks in advance.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved