One of our users tried to set replication solution in a sqlserver (the idea was given up).
SQL Server added in each table a column related to replication.
We want to remove theses columns and I used the following script :
select 'ALTER TABLE dbo.'+object_name(id)+' DROP CONSTRAINT '+object_name(constid)+' GO'
+'ALTER TABLE dbo.'+object_name(id)+' DROP COLUMN '+'msrepl_tran_version GO'
from sysconstraints where object_name(constid) like '%msrep%'
Question:
1. I want to know how to introduce a carraige return in order to have some thing like this :
...
ALTER TABLE dbo.T_CommandCopyFile
DROP CONSTRAINT DF__T_Command__msrep__44AB0736
GO
ALTER TABLE dbo.T_CommandCopyFile
DROP COLUMN msrepl_tran_version
...
2. Is there any other solution to do this more simply ?
Hi I am facing an issue running an SSIS package on a SQL Server 2005 SP2 machine
Here's more information:
1) The SSIS package that dumps data returned from a stored procedure call to an excel (CSV) file. The package owner is a SQL Login called AppAdmin. The login has user memberships/roles in two databases:
1.1) Database AppDB - Has execute and select permissions.
1.2) Database msdb - SQLAgentUser role
The package has one step that is run as a SSIS Proxy account PrxApp. The Proxy is based on a credential CrdApp which is based on a windows account <domain>AppAccount. This account is part of the Local Administrators group and also has SA rights in on the server. The same account is also used to run all the SQL Server Services on this machine. The machine is part of a 2-node cluster (A/P).
When I try to execute this package (as a schedule or one-off), I am getting the error mentioned in the Subject. I have checked a few posts using google and have acted upon as many suggestions that I possibly could (e.g. change this, change that...did not do any registry modification, etc). Many of these posts have been talking about changing local security policy and adding the user to several groups. Did that, but still does not work in a SQL Agent Job.
The account currently has the following rights (local security policy)
Act as part of OS Perf. volume maint tasks Lock pages in memory Replace process level token
One thing that I did notice and don't know if that affects the issue is that the following groups (and SQL Logins) are missing from my Windows groups listing (Computer ManagementUsers and GroupsGroups). Note that this is a Windows 2003 machine.
(a) SQLServer2005MSSQLUser$<machine_name>
(b) SQLServer2005SQLAgentUser$<machine_name>
Now these groups (and logins in SQL Server) do exist on some other SQL2005 machines that I have checked to see what could be wrong.
Any help at all is greatly appreciated. If you need any more information please let me know.
I have a table that I have created a table and desire to do some basic math by adding a few new columns. The problem is that i cant get this to work without create many new select statements. The new columns that I wish to add refer to other newly created columns. Is there a way I can do this with CTW or subqueries? Unless it is a best practice to chain out the logic for the newly created columns
I have an example from AdventureWorksDW since the data is very accessible. I can safely create EMP_TENURE and PTO_REMAINING is this select statement. I would then need to create a new select statement to define 'BONUS' and then another select statement to define 'NEW_COL1' and so on.
Im still pretty new at SQL and am trying to learn how to complete such a task using subqueries or CTE.
I am trying to do a PIVOT on a query result, but the column names created by the PIVOT function are dynamic.
For example (modified from the SQL Server 2005 Books Online documentation on the PIVOT operator) :
SELECT Division, [2] AS CurrentPeriod, [1] AS PreviousPeriod FROM ( SELECT Period, Division, Sales_Amount FROM Sales.SalesOrderHeader WHERE ( Period = @period OR Period = @period - 1 ) ) p PIVOT ( SUM (Sales_Amount) FOR Period IN ( [2], [1] ) ) AS pvt
Let's assume that any value 2 is selected for the @period parameter, and returns the sales by division for periods 2 and 1 (2 minus 1).
Division CurrentPeriod PreviousPeriodA 400 3000 B 400 100 C 470 300 D 800 2500 E 1000 1900
What if the value @period were to be changed, to say period 4 and it should returns the sales for periods 4 and 3 for example, is there a way I can change to code above to still perform the PIVOT while dynamically accepting the period values 4 and 3, applying it to the columns names in the first SELECT statement and the FOR ... IN clause in the PIVOT statement ?
Need a way to represent the following [2] and [1] column names dynamically depending on the value in the @period parameter.
[2] AS CurrentPeriod, [1] AS PreviousPeriod
FOR Period IN ( [2], [1] )
I have tried to use the @period but it doesn't work.
Hi, all. I have two server(Svr01, Svr02) that are replicated each other (Merge replication). Svr01 must be Publisher distributor.. but, by mistake Svr02 is set to distributor, publisher. (Publisher/distributor are on the same comp..)
I want to fix this.. How?
I think I have to remove replication on both side and Reset from the first. but, I don't know how to remove replication either..
I had a HDD failure that caused the database to become SUSPECT. Thats ok we have backups to fall back on. Restoring the database failed because the database is used for repliacation, the database status was set to LOADING (in middle of restore). Because of this status I am unable to remove the repliaction to allow the database restore.
Whats a way to change the database status from loading? Or is there any way of removing it completely and starting again from a backup? Can I force repliaction to be removed?
Thanks for taking the time to look at my problem, all help and suggestions are much appreciated.
I try to remove a replication with from my MS SQL 2000
DECLARE @publication AS sysname DECLARE @publicationDB AS sysname SET @publication = N'MyDB_merge' SET @publicationDB = N'MyDB' -- Remove the merge publication. USE [MyDB] EXEC sp_dropmergepublication @publication = @publication; -- Remove replication objects from the database. USE master EXEC sp_replicationdboption @dbname = @publicationDB, @optname = N'merge publish', @value = N'false' GO
but seems to do nothing, how many time cost this operation? MSSQL search something ?
There is a solution for remove rapidly a replication from my database.
Just wondering if i could get some help with this,
I have a large table of decimal values, many of the columns contain only zero values. I would like to write a stored procedure which loops through these columns, takes the SUM and if it returns zero , then deletes the column from the table.
Any help would be appreciated, thanks in advance, this site has already helped me heaps
Problem: I have a user who wants to remove table, but when she tries to remove it ( drop the table), she gets an error: =========================================================== MSG 3724, Level 16, State 2, Line 6: Cannot drop the table, 'dbo.<table name>' because it is being used for replication. MSG 2714, Level 16, State 6, Line 2 There is already an object named '<table name>' in the database. ============================================================
To investigate, I saw the following:
In the SSMS---> Replcation---> Local Publication--> [XYZ].Pub_XYZ [SERVER Name].[XYZ_Replication]
Local Subscriptions: [Reference DB].[ServerInstance].[Instance Name]:ABC.ABC_TO_XYZ.Reference DB).
How do I remove that table? Do I need to edit Replication? How? Which one?
When I accept my input data, it's convenient to read it in from a flat file as a fixed width file with a width of 100. Then I run a splitter to split the input into two different paths (path_1 and path_2, say), based on an indicator field.
The output of path_1 has 1 column with width of 100, but now I want to split it up into lots of little columns. The output of path_2 also has 1 column with a width of 100, but I want to split it up, too, only using a different fixed width map than i used for path_1.
When you set the results from a query to text, very often you end up with a format like:
job_id name ---------------------------------------------------------------------- ------------------------------------------------------------------------------------------------
Is there a way to get rid of the extra space between two columns? Sometimes it's more than the width of my screen between columns
I am planning to use transacational replication (instead of merge replication) on my SQL server 2000. My application is already live and is being used by real users.
How can I ensure that replicated data on different server would have exact same values of identity columns and date columns (where every I set default date to getdate())?
It is very important for me to have a mirror image of data (without using clustering servers).
hi, I have not done any replication process before . but I am trying to learn it . so I have the sql server reside in a window NT4.0 (first NT server )and I am using window workstation which has a client utility to manage the database. What I am thinking to do is to have a nother pc that has a window NT4.0 (second NT server) and sql server 6.5 installed. so what is my question.
Can I control the replication process from the workstation by using the utility tools to replicate the database from the first NT server ( source database ) into the second NT server
I need to query some hierarchical data. I've written a recursive query that allows me to examine a parent and all it's related children using an adjacency data model. The scenario is to allow users to track how columns are populated in an ETL process. I've set up the sample data so that there are two paths:
1. col1 -> col2 -> col3 -> col6 2. col4 - > col5
You can input a column name and get everything from that point downstream. The problem is, you need to be able to start at the bottom and work your way up. Basically, you should be able to put in col6 and see how the data got from col1 to col6. I'm not sure if it's a matter of rewriting the query or changing the schema to invert the relationships.
Within the LinkingID, there are duplicates in ID1 and ID2 but just in opposite columns. I have been trying to figure out a way to remove these set based. It doesn't matter which duplicate is removed. Essentially these are just endpoints and I don't care which side they are on. The solution must recognize the duplicates and not just remove based on every 2nd row.
We have a database with hundreds of tables, each with "CreatedByLoginId" and "ModifiedByLoginId" FK columns back to the Login table. This is all fine and well, but 500+ tables all link back to Login table every time a record is inserted or updated.
For strictly performance reasons, what do you think of us REMOVING the FK constraints on all of our tables? While this does mean that a GUID that is not a valid LoginId could potentially be put in a table, I'm not too worried about it because users don't have direct access to the database.
Hi I have a problem. I have a replicated database. Now I have a couple of tables within the database where I delete the content row by row and caluted the new record and insert them. Now when I do this on the development system that is unreplicate it is fine. However on production that is replicated I get this error:
System.Web.HttpUnhandledException: Exception of type System.Web.HttpUnhandledException was thrown. ---> System.Data.SqlClient.SqlException: INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VARLISTS_USERVARLISTS'. The conflict occurred in database 'BHP', table 'USERVARLISTS', column 'AutoID'. INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VARLISTS_USERVARLISTS'. The conflict occurred in database 'BHP', table 'USERVARLISTS', column 'AutoID'. INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VARLISTS_USERVARLISTS'. The conflict occurred in database 'BHP', table 'USERVARLISTS', column 'AutoID'.
Now I persume this is caused by the foreign key being contrained by the replicated database. I had though about unticking the box 'Enforce Relationship for replication' but I am not sure what problems this may cause.
Woudl this fix my problem? If not do you have any idea what would.
Windows 2012 R2, SQL 2012 (Primary Replica) SQL 2012 (Seondary Replica) SQL 2012 (Secondary Replica over WAN site)
There are database replicating on three SQL servers. WAN line is having performance issue because of limited bandwidth I have to remove SQL secondary replica over WAN site temporarily and add it again later when the WAN line is upgraded with between bandwidth What is the best practice to remove secondary replica and replicating database and add later from SQL management studio without interruptions on databases?
I have SSRS report that has around 80+ columns. I have requirement where in dynamically hideshow columns in report based on user selection. I could able to do it by setting expression for "Visiblity" property and having report parameter thro' which columns to display can be choosen.
My problem is 2 points
1. fox example if columns 2 and 4 to be hidden, then there is an empty column between 1 and 3 and 5 columns. How to avoid this
2. When i export to PDF / Excel these spaces prevail.
As per attachment, i have been created replications but in local subscription it is not populated any thing at the same time, Subscription database has been created but tables is not populated as per publication table.
The process could not execute 'sp_repldone/sp_replcounters' on 'sqldb2008'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011).The specified LSN {00000000:00000000:0000} for repldone log scan occurs before the current start of replication in the log {001317bf:0000f736:0008}. (Source:
MSSQLServer, Error number: 18768).The process could not set the last distributed transaction. (Source: MSSQL_REPL, The process could not execute 'sp_repldone/sp_replcounters' on 'sqldb2008'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
i had configured a merge replication betwen 2 server and i was running fine. but due to network problems my network admin has changed the ip address and name of the publisher, distributor and subscriber.
i just want to delete all the publication and subscription.. but i cannt' do this.. i m getting some sql server error.. is there any command that i can execute to reset the replication process that will delete all the publication and subcription. and then i create a new publication and subscription
After I setup the merge replication in SQL SERVER 7.0, there is a rowguid column (datatype uniqueidentifier) inserted into each table in the both the source and destination database.
I need to get rid of the rowguid column in all tables. I deleted the replication, but the column still existed.
Is there an easy way to get rid of all the rowguid column in the database? Thanks for your reponse in advanced.
We are using SQl-2000 Sp2 clustured on two compaq servers We have to modify our database shema every month. We are using sp_repladdcolum and sp_repldropcolum to modify table without snapshot.
Owever I would like to add/delete entire table in shema database without using snapshot ?
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?
I have found some articles with no publication in our transactional replication.
For example, running this:
select p.publication, a.publication_id, a.article from dbo.MSArticles as a left outer join dbo.MSpublications as p on a.publication_id = p.publication_id
shows this:
NULL1org_Community NULL3org_Community Purchasing to EDW5org_Community NULL1org_Division NULL3org_Division Purchasing to EDW5org_Division
How can I get rid of the articles that are not part of a publication?
I can't use sp_droparticle because it requires a publication which these articles do not have.
I've created a SSIS package which takes a matrix from Excel file and insert into SQL table. It works perfectly! However, if I would add a new column into that matrix in Excel. Unpivot tool should take into process dynamically. Is there a way to provide this automatically?
Hi, I am trying to replicate a production database server,on sql server 2000 at a particular geographic location to a new failover database server on sql server 2000 in a different geographic location via internet. The intention here is to use the failover database during times when the production server is down or busy and the synchronization needs to be sceduled for every 10 min. It will be of huge help if any expert could give the detailed process involved and any precautions that need to be taken. I also need to keep inmind to use the ever/odd sequence number generator during the replication implementation. Also, Whether to use row-level or column-level tracking?
