Hi, i restored a backup from a database thas has replication configured.
When i restored it the system tables that the merge replication creates
are restored too. I was investigating on internet and I found that i can
delete it using this query:
sp_configure 'allow updates', 1
go
reconfigure with override
go
DROP TABLE aonflict_SiacDataEEC_security_info
...
sp_configure 'allow updates', 0
go
reconfigure with override
go
Do somebody know if i use this queries to delete this tables i can
damage the database or is correct to use it.
Thanks a lot for your help.
*** Sent via Developersdex http://www.developersdex.com ***
On Analysis Services 2005 the members of the Administrators local group are also members of the fixed server role, therefore they have full control over Analysis Services databases.
I think this can be a problem becouse many system administrators don't need full control over AS. Does someone kwon how can I remove those high privileges to the local administrator?
We had transactional replication set up by a consultant with remote distributer, push subscription to the publisher (main production database). However, the subscriber machine had fatal crash and is unrecoverable. Now, replication jobs have been failing on distributor and we immediately need to disable and remove replication as it has started causing problems with transaction log backups on production database.
How can I completely remove this whole replication from publisher and distributor?
I have a db (with replication enabled) which was marked as suspect. As per the logs it is the Hardware crash that caused this db to become suspect. Tried with sp_resetstatus to recover the db. But it is still in suspect mode. Changed the db into emercengy mode to recover the data thru' bcp which was succesful. Since there is no other way to recover the db, now I want to drop the db and create / restore the db from backup. I am unable to drop the db becasue it is having a publication. I am also unable to drop publication becasue now the db is in emergency mode. Kindly help and inform how I can drop the db and create it again from the backup without reinstalling SQL Server again.
Does anyone know how to remove 'Replication Triggers'?
I have a database created by export of data object. This process does not export any system tables to do with replication but there still seems to be something in the database which references them. There is no poblem if I backup and restore the dataabse.
Several years ago I set up Merge Replication on a SQL 2000 database and an MSDE Client on a Laptop. The Laptop frequently travelled out of the country and would "synch" up when it came back in.
Well the Laptop received some Viruses that we cannot get to remove so putting it on the network is not possible. Also, the owner of the laptop received a new one. He wants the database set up on the new laptop but it is not a priority at the moment and we plan to wait till the "Yemen" project is finished.
In the mean time, I want to remove the old laptop out of the Merge Replication scenario. We are not concerned about losing the data in the database on the laptop.
Does anyone know the steps I need to perform to "unreplicate" my database without being able to connect them?
SQL Server 2005 Standard replicating to an identical server.
My issue:
When we go into the database to remove a large number of rows, it says we are unable to delete since the database is in replication. Is there a way to pause, or stop replication temporarily? I stopped the log reader but it still gave me the same error.
The only way I have found around this is to delete the publication and re-create it when I'm ready. Now though, I'm unable to delete the publication. SQL Server Management Studio will freeze if I either try to delete something from the UI or command line. This happens with objects in SQL Server as well as rows and such. It will just freeze and I have to kill the process.
I'm trying to write a create database script for a test database and I need to check if the database exists before dropping and recreating it. The problem I'm having is that the test database is also merge replicating so if the db exists I need to remove the replication and then drop the database. I'm running the script using osql from a NAnt build script. The script goes something like this:
USE master GO if exists (select * from sysdatabases where name = N'@INITIAL_CATALOG@') BEGIN USE [N'@INITIAL_CATALOG@'] EXEC sp_dropmergepublication @publication = N'@PUBLICATION@' USE master EXEC sp_replicationdboption @dbname = N'@INITIAL_CATALOG@', @optname = N'merge publish', @value = N'false' DROP DATABASE @INITIAL_CATALOG@ END GO CREATE DATABASE ...
Where the NAnt script replaces the @PARAMETER@ fields before running the script. Running the script using osql when the database doesn't exists appears to fail on parsing the line "USE [N'@INITIAL_CATALOG@']" with the error "Could not locate entry in sysdatabases for database '[DatabaseName]' ..." and does not create the database. If I run the same script in SQL Management Studio I get the same error but it does create the database.
The script should only execute the USE [N'@INITIAL_CATALOG@'] statement if the database exists, but the parser isn't able to see this and returns an error. If anyone can see an error in this script syntax, or see a different way of doing the same function without getting errors I would greatly appreciate it.
Hi,I have a few things on my databases which seem to be neither true systemobjects or user objects - notably a table called 'dtproperties' (createdby Enterprise manager as I understand, relating to relationship graphingor something) and some stored procs begining with "dt_" (some kind ofsource control stuff, possible visual studio related). These show up whenI use"exec sp_help 'databaseName'"but not in Ent. Mgr. or in Query Analyzer's object browser, and also notin a third party tool I use called AdeptSQL. I am wondering how thosetools know to differentiate between these types of quasi-system objects,and my real user data. (This is for the purpose of a customized schemagenerator I am writing). I'd prefer to determine this info with systemstored procs (ie sp_help, sp_helptex, sp_...etc) but will dip into thesystem tables if needed.Thanks,Dave
Hi guys does anyone know of a quick solution to remove the replication monitor icon within Enterprise Manager please I have tried all sorts but it is still appearing and starting to irritate me as I don't use replication on this server. cheers
I'm trying to remove replication from my database so that I can run some update scripts on it but SQL Enterprise Manager just keeps hanging on me. I've searched the web an no-one seems to have had this problem.
Does anyone know of this problem or have they experienced it? If so, can you help?
Am I doing something wrong trying to remove the replication (deleting the publication)?
I have found a bunch of duplicate records in our housing database that ideally I need to delete.There are two tables that I need to remove data from ih_cml_log_entry and ih_cml_log_notes. There is no unique identifier between the tables for a log entry. So I have had to join on the person_ref, log_seq and the date/time of entry.How do I go about deleting the data - I've used the script below to identify what I need to delete -
SELECT * FROM ( select cml.person_ref, cml.open_date + open_time as 'datetime',cml.open_user,cml.log_type ,ROW_NUMBER() OVER (PARTITION BY cml.person_ref, cml.open_date + cml.open_time,cml.open_user,cml.log_type ORDER BY (SELECT 0)) AS RowNo ,n.note FROM ih_cml_log_entry cml
It seems that there should be a solution for my situation, but for the life of me I can't seem to figure it out.
I need to compare two "like" tables, containing similar data. Tbl 1 is "BOOKED" (which is a snapshot of inventory) and tbl 2 is "CURRENT" (the live - working inventory table). If I write my query as follows the the subsequent result is "duplicate" data.
Code Block SELECT booked.item, booked.bin, booked.quantity, current.bin, current.quantity FROM BOOKED LEFT JOIN CURRENT ON booked.item = current.item
No matter what type of join I use, there is duplicate data displayed for each table. For example, if there are more bins in the BOOKED table that contain a certain product then the CURRENT table will repeat data and vica versa.
As follows:
Item Bin Quantity Bin Quantity
12345 A01 500 A01 7680
12345 B01 6 A01 7680
12345 C01 20 A01 7680
54321 G10 1032 E15 1163
54321 G10 1032 F20 523
54321 G10 1032 H30 750
98765 Z20 7000 Z20 8500
98765 Y15 2500 Y15 3000
98765 X10 1200 Y15 3000
What I would like to do is display Bin and Quantity only once and the repeating values as NULL or [BLANK]. Or, to display all of the bins from both tables and only the quantities from each table in relation to the bin found in that table, returning a "0" if no quantity exists.
This is what I'm after:
Item Bin Quantity Bin Quantity
12345 A01 500 A01 7680
12345 B01 6 B01 0
12345 C01 20 C01 0
54321 G10 1032 E15 1163
54321 F20 0 F20 523
54321 H30 0 H30 750
98765 Z20 7000 Z20 8500
98765 Y15 2500 Y15 3000
98765 X10 1200 X10 0
Is this possible? If so, how?
I also might add that it is ok for each table to contain multiple entries for any given item. This is basically being requested as an inventory variance report - inventory before physical count and immediatly after physical count - and will only be run once a year.
----------------------------------------------- Just thinking out loud here: What if I created three subqueries, the first containing only BOOKED information, the second containing only CURRENT information and the third being a UNION of both tables? Something like this:
Code Block SELECT q3.bin, q1.item, ISNULL(q1.quantity, 0) as QTY_BEFORE, ISNULL(q2.quantity, 0) as QTY_AFTER
FROM
(select item, bin, quantity from BOOKED)q1 Left Join
(select item, bin, quantity from CURRENT)q2 on q1.item = q2.item Left Join
(select bin, item from BOOKED UNION CURRENT)q3 on q1.item = q3.item
Order By q1.item
I don't know if I wrote the UNION statement correctly, but I will have to try this when I get back to work...
MS documentation is short when it comes to explanation of Quiescing a system for peer to peer replication. It seems that for any change the whole p2p machine has to stop ? This would cripple our development/releases... Is it really the case ? Can all p2p nodes be quiesced except one, the one when I would also make changes ? i.e. N1 <> N2 <> N3 I would stop inserts/updates/deletes for N1 & N3 and keep inserting/updating/deleting for N2 as I'm making schema change on N2 (additional table, additional field etc). Would it work ? Also, would I need to stop selects for N1 & N2 ?
When adding/removing tables in SSMS 2016 CTP2, I keep tripping into:
Index was outside the bounds of the Array
When editing a SQL Server 2014 Database.
If I use the SSMS for SQL 2014, the error is not happening. Possibly a reintroduced bug. Tried to use the Connect site to submit, but it says I am not enabled to use this area.
Summary: Started replication April 1 of 4M xact / day publishing system to subscribing system.
Performance was good. Latency was ~ 5-7 seconds.
May 10 we noticed that the DB was behind (latency was 12 hours).
All performance counters seem good with the exception of the disk.
. Performance spikes are 8 minutes apart and last from 30 - 60 seconds.
. During this period, Disk % Busy (1 - Disk % Idle) is 100%
The publisher DB publishes about 50-52 xacts/sec.
Rate of distribution (distribution DB to Subscriber DB) is ~ 47 xacts / second, so latency is increasing (currently at 33 hours). Previously my Subscriber system's "capacity" was 150 xacts / sec.
I know this because several weeks ago, the network went down, we were 24 hours behind.
When the network came back up the replication subscriber system was able to catchup at around 150 xacts / sec, or 3X the production system rate.
What has changed between then and now? Not much. We did install Tivoli Service Manager (IBM's backup system) a couple of weeks ago. It seems to run fine on a nightly basis, but I don't see any periodic heavy Disk I/O from that. Just to be sure, I've had them shut the TSM services down just to be sure.
We've also eliminated all extraneous processes other than those I need for performance monitoring (there was a RTVscan, virus scan process).
I've eliminated Autogrowth's as an issue as I've bumped the growth so that they are very infrequent (several days at this point. When we resolve the problem, I'll dial this down to something more reasonable.
My disk configuration is not ideal I realize (single Raid-5 disk with 3 partitions), however, this has not changed in the 6 weeks.
Thanks for any help on this!
Jack Griffith
Configuration:
Subscribing System:
SQL Server: 2000, SP4 - 8.0.2039
CPU - 2.8GHZ Xeon, Quad Dual-core
Memory - 3.5GB RAM
Disk: 3 partitions on a single RAID-5 disk with 1118 GB of space:
C: 39GB System and Programs
D: 97GB Log space
E: 982 GB Data space
Replication configuration:
- nosynch, continuous Transactional Replication - Distribution db is on Subscription system - distribution - Publication of approx. 50 transactions / second
Subscriber DB configuration: DB size: 64458 MB Logging: Simple (at this point)
All of a sudden none of our merge replications are working. In fact you can't even insert, update or delete and data from the tables in the merge publication. When trying that, we get an error stating:
Msg 550, Level 16, State 1, Procedure MSmerge_ins_E3F43EF8B259476099BBB194A2E1708C, Line 42 The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint. The statement has been terminated.
Currently, the only solution I've found is to delete the publication and recreate it. I'm trying to figure out why this happened. It happened on a development server that to my knowledge, hasn't been changed in a week or so outside of changing the server's IP address. Would that cause such an error to occur?
I want to know where I can find Object permission detailes, For example I have table1 and user "abc", I have given permission to user "abc" for table1 to "SELECT" "INSERT", "UPDATE", I want to know where this detail is stored in system table. I was looking at "SYSUSERS","SYSLOGINS", "SYSPERMISSIONS", "SYSMEMBERS", "SYSOBJECTS" I couldn't find anything, If some one can post your answers that will be great.
I have succesffuly BCP out system tables(sysdatabases, sysdevices, syslogins, and sysusages). Now I want to BCP those same tables IN to create devices, databases and so on using the BCP utility. I run the same command used to BCP out and only changes the OUT to IN but it did not work. Can somebody whose done this before tell me how to do it.
Where can I find a discussion of how to interpret the keys1 and keys2 columns of the sysindexes table? These columns are binary(255) and are supposed to contain a description of the columns to which the index applies. I also need to know how to interpret the status column of the sysconstraints column.
I've one Sybase DBA asking this question, Is this really true, Pls help me.
Due to architectural changes in SQL Server 7.0, you cannot dump the transaction log if a database's system tables are inaccessible (due to media failure, for instance)in 7.0 and later versions. Microsoft recommends that separate devices be used for system tables and user tables to allow a final log backup to take place in the event the data device is inaccessible. The additional exposure incurred by not doing this is possible loss of data for the interval between the last transaction dump and the point of failure.
I'm doing an impact analysis to see what views / stored procedures etc are impacted by a change to columns in a table. I'm using the system tables to identify these changes, and it is something that I'll have to repeat across all our databases in case the tables are being accessed from elsewhere.
I'd like to make sure I'm doing it the right way so could someone point me in the direction of a good article or code for doing this.
I dont want to see these at all and cannot find where tospecify a view or modification of a view. can someone throw me abone here and tell me where to make the adjustments if I can make themat allMichael
I have created a linked server XYZ that is linked to server ABC. I am tying to view the tables via XYZ but I'm unable to do so. I can only see the system tables. When I run a select statement, I get the correct results. That means I have the access to the tables, yet why am I not able to see the tables.
