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?
I have an existing publication in sql 2012 with 2 articles, and then I add 2 more articles. After that when I generate a snapshot, will the snapshot be generated for 2 new articles only or for all 4 articles?
I remember adding 1 new articles to one existing publication with 150 articles and when I generated snapshot, it was generated only for 1 article. But I don't remember clearly.
Does it behave differently for small and large number of articles?
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
I have setup Transactional replication in SQL 2005 between two servers and have about 200 tables being replicated. The problem is that every time, I add or drop a table to replication, and start the Snapshot agent, it re-initializes every article and re-loads every article. This process takes 1 hour to complete and CPU usage goes to 100% during that time.
This behaviour seems very different from SQL 2000 where I would start Snapshot agent and only the relevant tables were added/dropped.
Has that functionality changed from 2000 to 2005? Am I not doing something right? Thanks, Amir
The subscriber was able to get the snapshot ok, then synchronise fine
However after adding an article to the publication and recreating the snapshot I get this error now at the subscriber
Microsoft.SqlServer.Replication.ComErrorException (0x80004005): A failure occurred when accessing 'usp_misc_dailyTasks_3.sch' due to an operating system error [2='The system cannot find the file specified.'] during Web synchronization. Ensure that the -InternetLogin user when using basic authentication and the user running the merge when using Windows integrated authentication has been granted access to the snapshot share.
at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Run()
The key properties when synchronising are as follows:
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
First of all; My Oracle publication works fine when I don't explicit specify the shema_option parameter for the articles I'm adding to the publication. The reason why I then want to explicit specify the parameter is as following.
I'm developing a replication solution to get data from our production server (Oracle) to our Data Warehouse (SQL Server). The SQL Server (and the Data Warehouse code) uses the SQL_Latin1_General_CP1_CI_AS collation. When I don't explicit specify the schema_option, the nvarchar columns of the replicated tables are created using the SQL_Latin1_General_CP1_CS_AS collation and this results in some comparison errors, when for instance a select statement is trying to compare two nvarchar strings using different collations.
I've tried to specify the schema_option parameter as "@schema_option = 0x80" (Replicates primary key constraints.) to avoid the use of the SQL_Latin1_General_CP1_CS_AS collation when creating the destination tables - I'm not sure it's enough? No matter what, I'm getting an error when I'm doing it (see below).
Message 2006-07-13 12:00:15.529 Applied script 'ITEMTRANSLATION_2.sch' 2006-07-13 12:00:15.544 Bulk copying data into table 'ITEMTRANSLATION' 2006-07-13 12:00:15.544 Agent message code 20037. The process could not bulk copy into table '"ITEMTRANSLATION"'. 2006-07-13 12:00:15.591 Category:NULL Source: Microsoft SQL Native Client Number: 208 Message: Invalid object name 'ITEMTRANSLATION'. 2006-07-13 12:00:15.591 Category:NULL Source: Number: 20253
The questions are now whether I actually have a schema_option alternative for Oracle Publishing? If so, what is the solution, and eventually how can I avoid the error stated above?
If I'm not able to avoid the article columns getting created with the "wrong" collation, is there then any other obviously solution to the problem?
I've created a merge replication a few days ago. it works correctly, but today when click synchronize publications, some of then encounter this error :
Data validation failed for one or more articles. When troubleshooting, check the output log files for any errors that may be preventing data from being synchronized properly. Note that when error compensation or delete tracking functionalities are disabled for an article, non-convergence can occur. (Source: MSSQL_REPL, Error number:
Hi, I'm setting up a heterogeneous transactional push replication with Sybase ASE 12.5.3 as subscriber. With management studio I try to create an procedure article with following properties
Copy extended properties : false Destination object name : pGS_RefuseRequest Destination object ownere : dbo Action if name is in use : keep existing object unchanged Replicate : Execution of the stored procedure Create schemas at Subscriber : false
When I save the article and then the publication I got following error message:
Can not add artice 'pGS_RefuseRequest'. Object was not found on server. Check if this object exists on the server. (Microsoft.SqlServer.Rmo)
That's realy strange because the wizard offered the procedure pGS_RefuseRequest in the list of possible articles.
Fortunatly I can create the article with following TSQL statement :
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.
Hi! I want to add a word to a value if the value already exists in that field. How to do this? Please help me. In detail, i have 'id', 'name' and 'info' three columns in one Data Table. When I inserted one value to id field, if the value already exists it should add a word to that value and it should get inserted. Please help me to do this? Thanks in advance!
I want to add a computed field to an existing SQL table: where Field1 is >0 and field2 is not null set newfield = 'Y' else set newfield = 'N'
I want to keep this existing table because I'm using it as the basis for an Access Report that is nearly complete.
BTW this table is the result of a DTS package that is comprised of several SQL executables so I need to be able to repeat this as part of a larger process.
Hi Friends, I have a existing table named as activity, and have the column like ID,Description. I want to add the Identity for the ID column using script only.. Have any ideas how to do in sql query analyser?
Hi! I want to add a word to a value if the value already exists in that field. How to do this? Please help me. In detail, i have 'id', 'name' and 'info' three columns in one Data Table. When I inserted one value to id field, if the value already exists it should add a word to that value and it should get inserted. Please help me to do this? Thanks in advance!
what is best and quicker way to add 500 columns to existing table having 145 columns already.
Is there any way to avoid manual work of adding columns one by one in design mode or using script.
I have a TXT file (comma delimited) that contains all those columns names as a first row,but I am not sure if i can use DTS package to create table design having such sourcre TXT file.
We currently have a 2 node Active/Passive 2 named instance SQL 2000 cluster. We will be chaning the configuration to Active/Active, basically moving 1 instance to the passive node (so we can take advantage of the resources on the passive node).
We would also like to add 3 nodes to the cluster making it a 5 node SQL cluster. What we are thinking of doing is basically making it an Active/Active/Active/Active with the 5th server being passive. The question I have is will I be able to add and install the 3 new nodes without having to redo the SQL cluster? Should I install the two new active nodes with the default SQL instace or do I have to install SQL with named instances? (actually I think the named instances is the way to go but then I wouldn't be posting here if I was sure about the answer).
I cannot figure out how to add a default constraint to an existing column. The syntax I'm using is :
ALTER TABLE table_name WITH NOCHECK ADD CONSTRAINT column_name DEFAULT (0)
This gives me a syntax error.
The column was originally added with a default constraint of 1 to a 2.6 million row table. I dropped the existing constraint and need to add the new default constraint of 0 for that column.
Sorry I'm pretty new to SQL so I don't know if this is a simple question. I have a table, and I am trying to add a column to the table and populate this column using what would be called an 'IF' function in Excel.
Basically 'column A' has numbers in it. I want SQL to look at 'column A' and if the first 5 digits of the number in 'column A' are 00001, then put 'description A' into new column 'column B'. If the first 5 digits of the number in 'column A' are 00002, then put 'description B' into 'column A' etc.
I want to add new primary key into existing table which already has a primary key. But,I do not want to remove the old primary key, since there are many records and the old primary key also have relationship with other table
When I am using this query:
alter table hem154 add indexNO uniqueidentifier default newid()
alter table hem154 add CONSTRAINT pk_hem154_indexNo PRIMARY KEY (PK_indexNO) go
Note: Hem154 ~ Table name indexNo ~ Column Name
I get this runtime error:
Msg 1779, Level 16, State 0, Line 1 Table 'hem154' already has a primary key defined on it. Msg 1750, Level 16, State 0, Line 1
I have a table. I want to add 2 date columns. One when we are inserting any record it will show and another whenever the record updated to record that.
I want to insert dummy data for the previous dates. How to insert those dummy dates in batch wise?
I was asked to add an additional column to an existing query. I'm using Microsoft Query with a MS SQL 2000 server, and don't have much knowledge of SQL in general. Here's the existing query:
SELECT A.COMPANYCODE, A.INVOICENUMBER, A.LINENUMBER, A.SONUMBER, A.CUSTOMERCODE, A.SHIPPERNUMBER, A.INVOICEDATE, A.ITEMCODE, A.QUANTITYINVOICED, A.UNITPRICE AS 'InvPrice', A.QUANTITYINVOICED * A.UNITPRICE AS 'ExtInvPrice', INVENTORY.UNITPRICE AS 'StdPrice', INVENTORY.STANDARDCOST, A.QUANTITYINVOICED * INVENTORY.STANDARDCOST AS 'ExtCost', (A.QUANTITYINVOICED * A.UNITPRICE) - (A.QUANTITYINVOICED * INVENTORY.STANDARDCOST) AS 'GM$', (INVENTORY.UNITPRICE - A.UNITPRICE) * -1 AS 'PriceListDiff' FROM ABW.DBO.SALESANALYSISHISTORY A, ABW.DBO.INVENTORY INVENTORY WHERE INVENTORY.COMPANYCODE = A.COMPANYCODE AND INVENTORY.ITEMCODE = A.ITEMCODE AND ((A.COMPANYCODE = 'csp') AND (A.QUANTITYINVOICED <>$ 0) AND (A.INVOICEDATE BETWEEN '03/1/08' AND '03/31/08')) ORDER BY A.INVOICEDATE, A.ITEMCODE
They want a column added to the current query where if A.Unitprice is greater than or equal to Inventory.UnitPrice then populate the column with A.QuantityInvoiced*A.UnitPrice. I posted on another forum, and the advice I got was to add this:
SELECT NewColumn = CASE WHEN A.Unitprice >= Inventory.UnitPrice THEN A.QuantityInvoiced * A.Unitprice ELSE 'null' END, FROM ABW.DBO.SALESANALYSISHISTORY A, ABW.DBO.INVENTORY INVENTORY
I'm not sure how to integrate it to the current query, so I've tried running it by itself, and I get the error: Didn't expect 'A' after the SELECT column list.
Any help would be greatly appreciated to modify the current query to display the new column.
Is it possible to add new data to an existing report. I already updated the SQL query, but the new data does not appear within the report. How can I modify the rows, columns and data fields???
I removed all constraints in order to load a bunch of data into a table, now I'm wondering if I can add an identity column to this table which does contain data or if I have to create a new table with the identity column and insert the data into that.
Is there an easy way to install reporting services on a machine that is already running sp1? When I initially isntalled Sql Server I did not install ssrs. Now, SP1 is installed and when I run the setup tool to install ssrs, it warns me regarding a "version change", presumablly because my media is not sp1, but RTM. Any ideas?
We have a process here that produces several dozen Excel Spreadsheets every day from SQL Server using the bcp utility. the problem is that we cannot include the column headers when producing the Output. We are therefore trying to find a method where we can append the column Headers to the Spreadsheets; is there a way of doing this?
