Removing System Replication Tables.
Jul 23, 2005
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 ***
View 1 Replies
ADVERTISEMENT
May 25, 2007
Hello all,
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?
Thank you.
Hernan.
View 4 Replies
View Related
May 8, 2003
Hi,
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?
Please help.
Thanks,
Shaili
View 5 Replies
View Related
Jun 28, 2001
Hi all,
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.
TIA.
Ravi
View 1 Replies
View Related
Jan 13, 2005
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.
Any advice?
Thanks Ed
View 1 Replies
View Related
May 22, 2007
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?
View 3 Replies
View Related
Jan 17, 2007
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.
Please Help!
View 16 Replies
View Related
Jan 21, 2008
Hi,
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.
Regards,
Greg
View 4 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Mar 16, 2006
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
Jim
View 8 Replies
View Related
Aug 29, 2007
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)?
Thanks.
View 2 Replies
View Related
May 19, 2008
Hi guys,
please help.
I use to copy one column from one database to another database. What query should I use?
moreover,
How can I convert this
code
000001
the result would be
code
1
thanks!
View 5 Replies
View Related
Aug 21, 2014
When I create a table in sql server database name appears in prefix of table.
My database name: Digitall
My table name: Digitall.News
How can I remove Digitall prefix from my tables?
View 1 Replies
View Related
Mar 11, 2015
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
[code]...
View 2 Replies
View Related
Sep 30, 2007
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...
Any suggestions?
View 7 Replies
View Related
Oct 23, 2007
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 ?
Thank you.
View 5 Replies
View Related
Oct 2, 2015
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.
View 3 Replies
View Related
Jun 8, 2004
how to implement the replication with the System Stored Procedures or the Transact-SQL ?
i want to encapsula my replication and make it be reusable and ease to use.So i want to do the replication in a script file.how can i do this?
Thanks.
View 9 Replies
View Related
May 14, 2007
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)
distribution
DB size: 3111 MB
Logging: Simple (at this point)
View 1 Replies
View Related
Sep 21, 2006
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?
-mike
View 5 Replies
View Related
Jul 19, 2000
Does anyone know what system tables are used when a sp_spaceused is executed?
Thanks in advance
View 2 Replies
View Related
May 26, 2000
I want to get rid of systemtables or systemstored procedures which displays when ever i creat new database.
How can I get rid of system tables ?
View 1 Replies
View Related
Jan 26, 2001
Hi All
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.
Thanks in Advance
Regards
Ram
View 3 Replies
View Related
Apr 15, 1999
Hello,
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.
Many Many thanks,
Ana.
View 1 Replies
View Related
Nov 29, 1998
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.
View 1 Replies
View Related
Jul 10, 2002
Hello,
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.
Thanks
Qinglee
View 1 Replies
View Related
Jun 2, 2008
Hi Gurus,
select * from sys.server_principals is not working for me on 2000 SERVER.
This works fine on 2005 SERVER..
Where Iam going wrong ..please advice..
Thanks,
ServerTeam
View 3 Replies
View Related
Sep 28, 2006
Hello all.
can anyone advise me where I can find a relationship diagram for the system tables in SQL Server2000.
Thanks all.
View 5 Replies
View Related
Oct 31, 2007
Hi,
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.
thanks
Sean
View 2 Replies
View Related
Jul 20, 2005
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
View 2 Replies
View Related
Oct 26, 2006
Hello,
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.
Please assist
View 1 Replies
View Related
