Hi,
I would like to know the procedure to be followed to add a table to the existing publication. The subcriber doesn't have the object yet. I have data in the source table and I want to add this table in a existing publication for transactional replication. Any advise is appreciated.
Thanks,
Ravi
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 re-created a publication that was having problems and it gives this error when I start the snapshot agent from SQL Server Management Studio: I am stuck on how to resolve - any ideas?
"The publisher's identity range allocation entry could not be found in MSmerge_identity_range table. Transaction count after EXECUTE indicates that a commit or ROLLBACK TRANSACTION statement is missing. Previous Count = 1, current count = 2."
select * from MSmerge_identity_range returns 19 entries but I don't know how to fix.
I have tried deleting and re-creating but always get this error.
My other publications and subscriptions are working fine and I was able to create a new test publication that worked but can not get this one to work that worked fine up until today.
Any ideas?
We have a publication from database a to database b. Database a containts table1 to be published to databasae b. Database b contains a publication of table1 to database c. When we go to rerun the publication from database a it errors saying cannot drop table1 since it is part of database b's publication. how do you do this.
Thanks in Advance,
Phillip M. Triocli
I have a database with two publications, one for SQL CE Clients with filters that send each client its data and keeps it database with "low weight" filtering data not older than a week, and the other publications for SQL Servers.
The publication for SQL CE Clients works excelent, the other one has this problem:
In the publisher i can insert data that should be replicated to subscribers, but subscribers don't receive it. And if I try to insert both in the publisher and the subscriber, the subscriber throws a Primari Key exception.
In the other way, if i insert the data in the subscriber, when it synchronizes, the publisher pulls this data to the table and keeps it, but deletes the subscriber table due to a "conflict". And it never sends the data back to the subscriber, keeping the subscriber's table always clear.
Any Idea?
Thank you.
Omar.Rojas
Can i alter a table included in a Publication?
I am having problems with that.
Hi,
I have created a publication for sql server 2005 Mobile Edition subscriber,
I need to add another Publication/merge replication on the same tables,
Scenario:
-Server A and mobile devices (publication for sql server 2005 Mobile Edition subscriber)
-The same Server A send data "1 way direction" to Server B by merge replication publication.
NB: My Tables are updatble in both publications.
Any idea?
Thanks,
Tarek Ghazali
SQL Server MVP
Hello,
We are new to replication and are testing it in our development environment. We have a peer-to-peer transactional publication on our three servers. The single table in the original publication replicated fine to the two subscribing servers. We next added a new table (article) to the publication. Adding it to the original publication worked fine but the table did not replicate to the other servers. (We previously had changed the schema of the original table and the schema changes replicated properly.) We attempted to recreate the snapsnot using the "View Snapshot Agent Status" option. Clicking the Start button resulted in the display of this message: "[0%] A snapshot was not generated because no subscriptions needed initialization."
This seems odd because a new table was added to the publication and Microsoft help states that the snapshot must be rebuilt. I have read other topics that refer to a @immediate_sync property that must be set to zero. I'm not sure if this is our problem or even how to set this value. Meanwhile, the other servers, as viewed through the Replication Monitor, are complaining that their snapshots do not match the publication snapshot.
Can someone point me in the right direction?
Thanks,
BCB
Hello Everybody, If any one have solution then please help me.
Thanks in Advance.
I do the following steps:
I have created two publications on my SQL SERVER for merge replication
Publication A €“ which returns all rows from the Table1
Publication B €“ which returns all rows from the Table1 where the field MANAGER =€™ABC€™
I have two clients who have MSDE
Client 1 is subscribed to Publication A and Client 2 is subscribed to Publication B
All works fine till now and I am able to make transfers from the two clients and get all the changed data
However, now If I change the filter rules for Publication B and set that it should return all rows from the Table1 where the field MANAGER = €˜DEF€™ , SQL Server tells me that I have to reinitialize all snapshots for all subscriptions. If I don€™t do this it doesn€™t allow me to make a transfer from my Client 1.
As you can see in my example I have not made any changes to Publication A to which Client 1 is subscribed. So this seems to be illogical.
In our case it would not be possible for us to reinitialize the subscriptions for all Publications when the rules for only one Publication are changed because we may have a lot of clients connected to our Server and if we reinitialize the subscription then all the data is sent again.
If anybody know that how to restrict the other publications re-initialization then please tell me how we can do.
Hi, everyone,
I am new in SQL server 2005. I had setup SQL server 2005 P2P replication. Somehow it did not work one of two way replication. I tried to delete the publication. However I could not do it. have the same problem. When I tried to delete the publication, I got the publication " " does not exist.[SQL server error: 20026]. I tried to use sp_droppublication, it gave me error "the database is not enabled for publication". Nevertheless, I can see the publication in MS SQL Management Studio and Publication monitor with OK status. I could not find the distribution database either.
Could you anyone has ideas to delete this publication? I am sorry I am not a programmer. Please give me more detail explanation if you can. Thanks.
I do not understand why I have this issue.
SQL 2012 SP2, Transactional Replication.
I have added a new table to a database (existing publication) using T-SQL, I then opened up publisher properties, and ticked the new table/article so that it would be added to the subscriber. It did not show up.
I did not use a snapshot to initialize the subscription.
Immediate Synch is 0.
allow_anonymous is 0.
I mark the subscription to be reinitialized. When I start the snapshot agent I get '0% A snapshot was not generated because no subscriptions needed initialization'.
What could I be doing wrong, or missed out? Do I need to drop and recreate the subscription to get the article to show up?
I have a table that is in a one way transactional publication. I need to create a full-text catalog on this table and have that catalog exist on the subscriber as well. I understand simply creating a FTC for an object in a publication will not cause that FTC to be replicated to the subscribers. I have scripted out the command and tried to use sp_addscriptexec to push it to the subscriber. When I do this, I get the following error:
Last 183 characters in 'sqlcmd' output buffer: Changed database context to 'database'.
Msg 574, Level 16, State 1, Server SQLSERVER, Line 2
CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
Also, when this script is run directly from the subscriber, it works fine (but since the subscriber is our production machine, I'd prefer to not have to do it that way). I believe I can reinitialize the publication and have the FTC pushed to the subscriber, but this is not ideal either, since some of our databases can be very large and take a long time to initialize.
Is there a better way to accomplishing this other then connecting directly to the subscriber and running the script or by reinitializing the publication? Thanks for your help!
-mike
I have created a table as below mentioned. Then I want to alter the ID column as identity(1,1) without dropping the table as well as losing the data.
create table dbo.IdentityTest
(
id int not null,
descript varchar(255) null,
T_date datetime not null
)
Hi all, please help. I m trying to create an "empty" table from existing table for the audit trigger purpose.
For now, i am trying to create an empty audit table for every table in a database named "pubs", and it's seem won't work.
Please advise.. Thanks in advance.
Here is my code:
Code Snippet
USE pubs
DECLARE @TABLE_NAME sysname
DECLARE @AUDIT_TABLE VARCHAR(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME NOT LIKE 'audit%'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME = 'sales'
WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_NAME = 'sales'
SELECT @AUDIT_TABLE = 'Audit'+''@TABLE_NAME''
SELECT * INTO @AUDIT_TABLE
FROM @TABLE_NAME
TRUNCATE TABLE @AUDIT_TABLE
ALTER TABLE @AUDIT_TABLE ADD UserAction Char(10),AuditStartTime Char(50),AuditUser Char(50)
SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_NAME> @TABLE_NAME
AND TABLE_TYPE= 'BASE TABLE'
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit'
AND TABLE_NAME NOT LIKE 'audit%'
END
Thanks. ..
i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.
any ideas on how i could do this easily?
thanks
Hello,
I'd like to create a temporary table with the same schema as an exiting table. How can I do this without hard coding the column definitions into the temporary table definition?
I'd like to do something like:
CREATE TABLE #tempTable LIKE anotherTable
..instead of...
CREATE TABLE #tempTable (id INT PRIMARY KEY, created DATETIME NULL etc...
I'm sure there must be a simple way to do this!
Many thanks,
Ben S
I am using the DTS wizard and having problems importing excel into an existing table.
Problem is that various column in excel are defined as double in the wizard but in my db table it is defined as an integer.
How do I get around this issue so the data types in excel can match up accordingly to my defined data type in my db table?
The wizard does a bad job of guessing the correct data type.
I have heard of using a staging table to import from excel and using that as my source to import into my existing table.
I want to move an existing table from another.
Example
B.mdf dbo.xx to C.mdf dbo.xx
B.mdf dbo.xx has records - I created a script and install the script for C.mdf dbo.xx
C.mdf dbo.xx is empty -
All I need now is to populate the records from B.mdf dbo.xx to the empty C.mdf dbo.xx
I was telling I could do a cut and paste to copy the dbo.xx
Thank you in advance.
~ Peaches ~
Hi,
How would I select from TABLE A and add this data to TABLE B which has existing data that I want to keep. I would like to keep adding onto the old data. When I do an "Insert Into", it keeps on deleting my old data.
Thanks,
I have created two tables in phpMyAdmin 3.5.8.1. comments which stores comments users post on a website and registration which stores a users registration details (username, password, e-mail etc, etc).
I want to add the username field from the registration table as a foreign key to the comments table. How do I do this in phpMyAdmin?
Hi everyone!
I just started with SQL and SQL Server 2005. I cantfind in my books how to add a column to an existing table. It should be a primary key, auto_increment column.
Hope someone can help, many thanks in advance
Greetings from Vienna Austria
landau
i have table fff .it has two fields one is fno int , another is fnamevarchar(20)ffffno fname-------- -----------100 suresh102 rameshhere there is no not null constraint and identity column theni am add primary key constraint fno column pls help me
View 1 Replies View RelatedHi all,I have an existing table(Several actually) that I want to add a columnor two or three. I need to have, I think, at least two columns. Onefor Created date and believe this is a no brainer in that I just addthe column and set the default to getdate(). Two, for tracking amodified date of the row of data. I want to keep it simple. What do Ido to track the modify date of a row in SQL Server 2005? Anytime anydata changes on a row update the update column? Is a trigger the onlyway to accomplish this task?Another column could be used to track who changed it. Is there a neatway to determine username, machine name, MAC address or any othermethod? I pretty wide open on this one as I am not sure what I want touse here.TIA!!!
View 16 Replies View RelatedI trashed SQL Server2000 when I added the WINXP SP2, and had to reinstallit. When I did, my database(DEV_DATA) remained intact, but when I go inthru Enterprise Manager, it is not located in the system tree, and istherefore inaccessible. Can anyone suggest how I could get this databaseincluded.
View 4 Replies View Related
How do you alter the table to use the new partition (I know ALTER TABLE is in there but BOL doesn't give a valid example with the move option)? I can create the partition but I want to apply it to an existing table with no partition?
Thanks
I need sql script that can generate 'Create Table' definition including primary and foreign constraints for the table already existing in SQL server 2000.
Please do not refer me to the utilities or commercial software, I need only the sql script.
thanks
Hi Forum,
I am currently implementing RDA Pulls and Pushes. Both worked fine for me, except when I try to pull a certain table twice. I read that in order to pull the table a second time I must drop it on the client.
My original approach was to use a select statement with a where criterium in the pull statement (e.g. SELECT * from tblPhonebook where Pulled = 0), then set Pulled to 1 and pull again later.
My understanding of RDA was that I use the where criterium to filter the data at the server side and simply append that data at the client side.
Can I simply append data in some way or do I really have to drop the table on the client side every time I pull?
Regards
Tobias
Hi
I'm trying to Create a new Table from existing table in Q/Analyzer. I figured it would be something like this:
CREATE TABLE newTable AS(SELECT * FROM OldTable);
but i keep getting
Server: Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'AS'.
also.. is there another method of doing this, something like
INSERT INTO newTable(SELECT * FROM OldTable);
and it creates the table ( newTable ) for u if it doenst already exist??
Cheers!!!
im using sql2000
Hi,
I m new to this forum with a Query
Create table <table name1 > as select * from <table name2>
this works for oracle. do anybody knows its alternative in sqlserver 2000
thanx. :)
hi,
i have a table with data in the following format (dont ask why it was done this way, its not something i can change)
so my table looks like this:
point :timestamp :value
_________________________
var1 11:00 5
var2 11:00 2.3
var3 11:00 21
var1 12:00 5.2
var2 12:00 2.4
var3 12:00 18
and so on.
i would like to have my data rearranged to look like the following:
timestamp :var1 value :var2 value :var3 value
________________________________________________
11:00 x y z
12:00 a b c
....
i am working with my database in a c sharp application. so if anyone does come up with a solution for this maybe keep that in mind so that i can discuss how to impliment it in the code.
thanks guys.
Hi All I want to create new table which is going to be a copy of one existing table.
I need to copy the existing data into the new table too. How do I do that?
Hai allI am new to SLQ server. Can anyone tell me how to create a table thatshould look like a existing table fields....thanx
View 2 Replies View RelatedI went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
View 1 Replies View Related