I have merge replication between sql 2005 SP1, and when I disable distribution from my publisher, my tables with identity columns change the property "Not for replication" to "NO", does somebody knows if this is a known problem ?
Other information : the compatibilty level for database is 2000.
Hi,
I'm using merge replication to maintain a backup copy of my main (publisher)MSDE database. A push subscription periodically (1 per minute) updates the backup DB.
It's intended that if the main db goes down then the backup (subscription) db can be configured as a publisher. This must all be performed via scripting.
The initial configuration of the main publisher and subscription is controlled via scripting, which works fine.
The problems occur when I try to configure the subsciber to become a publisher. A script is executed on the subscriber but fails at the point when it's configuring the publisher detail. The error is something like "unable to configure a publication for a database setup as an anonymous subscription".
I'm guessing that there are subscritpion artifacts added to the database which need to be removed before it can be configured as a new publisher.
Please help,
Jez W
I have accidentally registered an existing database as a distribution database, which made it a system database.
the data itself is safe and sound, but I want to undo the whole thing.
How can I do this?
I was wondering if there is an easy way to get a database schema out of a MS SQL 7 database indicating specs on each field (datatype,length,table it resides in, whether it allows nulls, default values, primary keys, foreign keys, etc.)
View 2 Replies View Relatedhi,
the distribution agent says that the process is running and waiting for response from the server .
i have replication set up for different locations.
only one location bothers me.
it runs for hours and it will not get complete for quite a few days.
once it is done i am missing the data for few days.
can you please tell to me how to make the distribution agent run faster with out much delays.
thank you,
Can anyone shed any light on why this table exists in both the publication DB AND the distribution DB. The reference material just says:
The MSrepl_identity_range table provides identity range management support. This table is stored in the publication, distribution and subscription databases
However,doesn't shed any light on WHY it is in each database.
For merge replication which one should I be looking at to determine the next seed value?
Assuming I am looking at the appropriate records in the distribution db (where publication_db = my publication db) should the values be identical to the values in the MSrepl_identity_range table in the publication db?
I have transactional replication on SQL2000 with 60 subscribers.
For disaster recovery procedure i need to establish new replication within 15 minutes on another server with same subscribers.
Can i just confogure another DB as publisher and connect exist subscribers to new publisher ??
ThanX.
Hi all,
I'm a rather newbie, not only to this forum but also to sql server having a question to the following issue:
Is it possible to drop/remove/delete the "orphan" of a merge subscription on one instance of sql server without having the (former) distributor/publisher (on other instance) available?
The background is: I had a small replication infrastructure with two instances (on two machines), one the publisher and distributor, the other the subscriber. Now it happened that the publisher/distributor machine was completely set up new without having the replication dropped in advance, what remained on the subscriber is now a database with all the merge/replication tables and the guid columns in the user tables.
Moreover this, an entry remained in the subscription saying that a subscription with the former publisher exists...
Can I remove these "orphans" without having to setup the instance again?
Thank you,
Andy
How to speedup data from subscriber to Publisher on Merge replication?
Is there any we can speed up data from Subscriber to Publisher (uploading).
Subscriber downloading data from publisher running more than 20 hours after drop and recreate subscriber.
Could you please post reply.
Regards
Ponnu
From the SSMS GUI it is possible to re-initialize one or all subscriptions to a merge publication. This is done at the publisher.
How can I achieve these operations programmatically?
In particular, how do I initialize a single merge subscription from the publisher?
I have looked at the documentation for sp_reinitmergepullsubscription but it says that this proc must be run at the subscriber - which isn't much use when subscribers are disconnected for the majority of the time
I have a large number of merge subscribers and want to reinitialize all except one
aero1
For study effect, can I configure merge replication using just one SQL Server Instance?
View 3 Replies View RelatedWe have SQL Server 2000 with merge replication at a Publisher and subscriber.
We have some records getting deleted at Publisher and Subscriber and no conflicts are logged.
We have tried the compensate_for_errors setting and this has had no effect.
This is causing serious data corruption and has now become an URGENT issue. Out tech team are almost out of ideas.
Has anyone experienced this or have any ideas as to what to check next?
How to check if web merge sync is working between a subscriber and publisher thru HTTPS ? SQL port 1433 at subscriber is blocked so no direct connection to subscriber.
View 2 Replies View RelatedWhere can i find more information on the table definitions? I am trying to understand the definition of User.AuthType & User.UserType. There should be some sort of documentation somewhere.
When I add a user to a report or folder it will assign it to UserType = 1, AuthType = 3. I have no idea what that means.
Any ideas?
I am currently investigating the best way to handle partition swapping where the partition scheme is built on several file groups. In order to swap partitions, both partitions need to be on the same file group. In addition, the file groups and partitions are automatically updated once a month meaning that there is a scheduled job that adds a new file group, file group files, and updates the related partition objects.If I am forced to create a new non-partition table on the same file group as the partition of the target table, what would be the best way to create the target table without hard coding the full table definition and clustered index? I know that I can query the systables and syscolumns views to reconstruct a basic SQL statement to build the table definition and views Is there a SQL Server function that I can use to just give me the object definitions? Unfortunately, the OBJECT_ DEFINITION function doesn't apply to tables or indexes. URL....
View 4 Replies View RelatedHi,
Is there a way of uploading column definitions from the Flat File Connection Manager into a SQL Server table definition. Since I have over a dozen data sources to process each with around 200 columns and of course like many BI techies I have little immediate influence over the structure of these flat files. I just know that these data sources are business critical.
Judging by looking at similar threads I can't be the only one who would greatly benefit from being able to upload column definitions from the Flat File Connection Manager into a SQL Server table definition as opposed to doing this manually.
Thanks in advance,
Kieran.
Kinda new to SQL, using SQL Server 2005.I have some foreign keys in a couple of tables. I need to drop thesetables, but can't since I'll get the error:Msg 3726,Level 16, State 1, Line 1Could not drop object 'Client' because it is referenced by a FOREIGNKEY constraint.I'm not sure how to disable or get rid of these foreign keys so that Ican drop my tables. I tried:ALTER TABLE Client NOCHECK CONSTRAINT ALLAlter Table Client Drop Column Foreign Key Boss_ID;I went into the Object Explorer and deleted the FK lines from eachtable, but still the same error.What am I doing wrong?Thanks for your help.
View 1 Replies View RelatedOn SQL Server 2005 SP2 for Publisher and Distributor on the same instance, my old snapshots are not being cleaned up.
The following error is in the agent history:
Executed as user: DomainMyUser. Could not remove directory '\vmsql01ReplDatauncPublication_TRANSACTIONAL20070702104416'. Check the security context of xp_cmdshell and close other processes that may be accessing the directory. [SQLSTATE 42000] (Error 20015). The step failed.
xp_cmdshell is enabled and I can run commands like :
exec master.dbo.xp_cmdshell ' md c:TestFolder'
The permissions to the snapshot share and file system are that DomainMyUser has full control.
I have logged into the machine as this user and can remove snapshots so it does not seem to be a permission issue.
On other machines I do not get any errors but the snapshot folder still is not cleaned up.
Any suggestion as to what the problem could be?
Thanks,
Amy
Hi
Created a table User with the fields of Uname varchar(30) and pwd varchar(30) in SQL server 2005.
I need to create a publication and Merge subscription with the below structural changes
User table with the fields of Uname varchar(25) and pwd carchar(30).
The publication table having the Uname varchar(30) but we change this to the subscription table as Uname varchar(25).
Is it possible? If you, please give the details.
Thanks.
I want to add some new tables to the existing pub.We are using transactional replication.
As per my knowledge,by using below option we can do it.
exec sp_changepublication
Using SQL Server 2005. Replication working fine except the distribution table is continuely growing. Started to configure maintenance job (catagory: REPL-Distribution Cleanup) Any suggested steps that will not break the replication. Is there a SP available that will address my problem.
Thanks,
Question re Merge rep (pull) and processing order. We have a group of changes associated with an app upgrade, the scripts run fine on the publisher. Part of the change includes creation of a new table , followed by altering a view to use new table.Following the change at the publisher, when the sync is kicked off from the subscriber, it fails - the alter of the view throws an'invalid object' error with regard to the new table. Seems as if the view alter is attempted before the dependant table has been created.
I have tried to amend the processing order of the view using sp_changearticle, which executes (quickly) with a 0 return code.But it is to no avail , the error still occurs. is it possible to change the processing order for a view article , which will be applied to schema changes ? Have
I have a wired situation..!I set up transactional replication on one of my development server (SQL2000 Dev Edition with sp4).It is running fine without any issues and all of a sudden, i noticed inmy repication monitor tab under Publisher where I usually see thepublication is empty now.I do see the snapshot agent, log reader and distribution agent under myagents inside the replication Monitor. But its usefull to see all 3agents in one window under publisher before. What happend? Is there anyway to get that inside that monitor? Has someone encountered thissitation before? Please advise....After that I tried to create a new set of replication on differentdatabase on the same server and i dont see those either underReplication Monitor - Publishers....All it says is (No Items)....I would appreciate any help to correct this issue... Thanks in advance..
View 2 Replies View RelatedHi
I am new to Sql server and had just finished the MErge Replication setup on one of the PRoduction server. Today I got the request to change one of the Datatype of one the Published Article. Please help as what are the correct step to make it happen. Is there any production downtime required and if yes , then how much.
Thanks in Advance
Sanjay
I'm using SQL Server 2005 version 9.0.3054.
In the distribution.dbo.MSsubscriptions table, the field subscriber_db has a value of "virtual".
This is only occurring for one of my transactional publications.
There are 2 rows with "virtual" and they have subscriber_id's of -1 and -2.
There is a third and last row for that transactional publication that points to the correct db.
What does "virtual" mean and why does it appear?
More info:
I have 3 transactional subscriptions. The first two were created via SMO. The third one, with the "virtual" subscriber_id, was originally created manually via SSMS. I then generated scripts from SSMS in order to recreate it again later. Those stands out from the other two with differences in these columns:
snapshot_seqno_flag
value is 0 rather than 1;
independent_agent
value is 1 rather than 0;
article_id
value is 3 for all three rows listed for the subscription in that table;
status
values for the three rows are 1,2,1 respectively. Whereas my other subscriptions have status 2.
agent_id
values for the three rows are each different (10,11,12) whereas the agent_id for each of the rows in the other two publications is consistently the same.
publisher_seqno
ss_cplt_seqno
are displayed as equal for each row whereas the values for those two fields for rows for the other 2 publications are not equal; The value formats are different too. Example:
0x000013340000125200C1 0x000013340000125200C1
0x000013340000125200C7 0x000013340000125200C7
0x000013340000127D0012 0x000013340000127D0012
instead of format like the following for the other 2 publications:
0x00002CB2000026370001
0x00002CAF0000022B000100000002
0x00002CB2000026370001
0x00002CAF0000022B000100000002
0x00002CB2000026370001
0x00002CAF0000022B000100000002
0x00002CB2000026370001
0x00002CAF0000022B000100000002
No other columns appear unusual.
Paul
I am going to start a database and need to know the difference betweenData Modeling, Schema and Database design?I always thought of Data Modeling and schema as defining relationshipsand primary and secondary key?What is mean when someone designs an E-R diagram and a Data FlowDiagram??
View 2 Replies View RelatedI am setting up columns in a data table. Where can I find the definitions and uses for all the items on the DATA TYPE drop down list such as ntext and nchar? The data type list is also found under column properties general section.
View 9 Replies View RelatedHi all,
After installing SP2 for SQL2005, My boss has found new reports when looking for database information. Specifically, the new Reports option when right clicking on a database.
To better streamline our dbs he found a pair of Index reports that he does not know what to do with.
Can anyone help us understand the time length for the numbers shown under #User Seeks, #User Scans, #User Updates, Last User Seek Time, Last User Scan Time, etc..?
We are unsure if these are daily, or from the last backup, or for uptime since last restart, or collected from the inception of the database.
Any help would be good and very much appreciated as I am VERY new to this and would like to show that I can find information when asked.
I use SQLServer 2008 R2 Standard Edition.I know there are some triggers in my database that someone else has created, but I can't find their names and also I want to know the definitions of the triggers, if I want to alter or recreate them.
View 2 Replies View RelatedHi,
Does anybody know where to find out the definition of all the SQL error code
For example, What does error code 4002 means ? What does error code 3146 means ? Thanks.
Other than the error log is there an easy way to find the sort order and
chracter set of an installed SQL Server. Also after finding the numbers
is there a good reference to tell you what these numbers mean.
Thanks
Hi All,
In SQL Server 7, we had the option of opening a previously saved trace template and running it inorder to run a previously saved trace defintion repeatedly.
File > Open > Trace Definition
and choosing the Trace Name. Clicking OK would run the trace.
How do we do the same in SQL Server 2000?
File > Open > Trace Template
opens a Trace template but has only a 'Save' button and I'm therefore unable to run it.
How is it possible to run a previously defined trace repeatedly in SQL server 2000?
Thanks in advance,
Praveena
HiThis is a question of "what does it cost me".Lets say I have an integer value which would fit into a smallint fieldbut the field is actually defined as int or even larger as bigint.What would that "cost" me ? How would definitions larger than I need forthe values in the field affect me ?Its obvious that the volume of the database would grow but with the sizeof resources etc that we have nowadays disc space isn't a problem likeit used to be and i/o is much faster and many people would tell me "whocares" , or IS it a problem ?How does it affect performance of data retrieves ? Searches ? Updatesand inserts ? How would it affect all db access if tables are pointingat each other with foreign keys ?Thanks !David Greenberg
View 3 Replies View Related