Remove Columns Created By Replication Process

May 12, 2004

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 ?

View 3 Replies


ADVERTISEMENT

The Process Could Not Be Created For Step 1 Of Job

Jun 11, 2008

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.



Thanks

Shishir Khandekar

View 6 Replies View Related

T-SQL (SS2K8) :: Calculate On Newly Created Columns

Aug 2, 2014

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.

SELECT
NAME = [LastName] + ',' + [FirstName]
,HireDate
,Title
,DepartmentName
,BaseRate
,VacationHours
,SickLeaveHours

[Code] ....

View 1 Replies View Related

PIVOT With Dynamic Columns Names Created

Aug 3, 2007

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.

Thanks in advance.

Kenny

View 1 Replies View Related

Replication Created But Not Started Error

Jun 20, 2008

Dear All,
i've created replication between two databases, but while trying to view synchronization status, it is saying that

you must crate a job for this subscription.

what should i do now?

please guide me

Arnav
Even you learn 1%, Learn it with 100% confidence.

View 16 Replies View Related

How To Remove Replication?

Jun 28, 2004

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..

Please reply with any thougt....

View 3 Replies View Related

Cannot Remove Replication

May 6, 2006

Hi All,

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.

Cheers,
Dan

View 4 Replies View Related

Remove Replication

Aug 27, 2007

Hi all,

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.

Thank's a lot bye
Bye
Hid

View 2 Replies View Related

Remove Columns Stored Procedure

May 6, 2007

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

View 14 Replies View Related

How To Remove Replication Completely?

Sep 25, 2001

When i uninstall replication, it failed to remove completely. How do i remove them completely. so that make replication again

I would appriciate any help.

Thanks in advance

Yads

View 1 Replies View Related

How To Remove A Table From Replication

Mar 17, 2015

Version: SQL 2008 R2

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?

View 15 Replies View Related

Splitting Up Columns Mid-process

Jun 7, 2007

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.

Any ideas?

Thanks!

Jim Work

View 1 Replies View Related

Remove Space Between Columns In Result To Text

Sep 20, 2006

Hello

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

Thanks!

Worf

View 4 Replies View Related

Identity Columns And Date Columns On Transactional Replication

Sep 16, 2006

Hi,

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).

Any help would be appreciated.

Thanks,

-Niraj

View 1 Replies View Related

Replication Process

Mar 25, 1999

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

thanks
Ali

View 3 Replies View Related

Remove Article From Transactional Replication?

May 21, 2015

I want to remove article from my Transactional replication, can I just simply uncheck it from GUI, or it requires anything else.

View 3 Replies View Related

Remove Reference Key In Replication Environment

Mar 22, 2007

Good day,

Currently, i'm using SQL 2000 & having a replication database from district to HQ. Then, I need to remove a reference key from one of replicate table.

So, what I need to do & how to remove reference key? I wish someone here can help me out..

Beside, any impact might happen if remove reference key?

View 2 Replies View Related

Transact SQL :: How Columns Are Populated In ETL Process

Apr 30, 2015

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.

DECLARE @table_loads TABLE (column_id INT, parent_id INT, table_name VARCHAR(25), column_name VARCHAR(25))
DECLARE @column_name VARCHAR(10)
INSERT INTO @table_loads(column_id, parent_id, table_name, column_name)
SELECT 1,NULL,'table1','col1'

[code]...

View 4 Replies View Related

SQL Server 2014 :: Remove Duplicates In Opposite Columns

Jan 22, 2015

I have a table containing the following data:

LinkingIDID1 ID2
166202180659253178
166202253178180659
166334180380253179
166334253179180380
166342180380180659
166342180659180380
166582253179258643
166582258643253179
264052258642258643
264052258643258642
264502258643258663
264502258643259562

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.

View 8 Replies View Related

DB Design :: Remove Foreign Keys For Audit Columns?

Aug 25, 2015

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.

View 4 Replies View Related

How To Abort From A Replication Process

Nov 25, 2005

Hi guys.

View 1 Replies View Related

What Happens If I Remove The 'Enforce Relationship For Replication' Setting?

Sep 28, 2005

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.

Thanks Ed

View 1 Replies View Related

Replication :: Remove Secondary Available Replica From 2012?

Oct 14, 2015

Environment:-

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?

View 6 Replies View Related

Reporting Services :: Dynamically Add / Remove Columns - SSRS 2012

May 16, 2015

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.

View 3 Replies View Related

SQL Server Admin 2014 :: Replication - Subscription Database Created But Tables Not Populated

Nov 6, 2015

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.

View 2 Replies View Related

Replication :: Process Could Not Set Last Distributed Transaction

Apr 29, 2015

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)

View 2 Replies View Related

T-sql Script To Reset The Replication Process

Feb 28, 2008



Hi guys.

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


Thanks and looking forward.
-malik

View 7 Replies View Related

After Merge Replication - Wanted To Remove Rowguid Column

Jul 28, 2001

Hi all,

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.

View 2 Replies View Related

How To Addd/remove Entire Table In Replication Shema ?

Aug 19, 2002

Hi everybody,

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 ?

Is it possible ?
How to do ?

Thanks.
Fabio

View 1 Replies View Related

Replication: Remove Merge Subscription Without Existing Publisher?

Feb 26, 2004

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

View 4 Replies View Related

SQL 2012 :: Transactional Replication - How To Remove Orphaned Articles

Jan 27, 2015

We are runnning on SQL Server 2012 SP1 + CU9.

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.

View 1 Replies View Related

Integration Services :: SSIS Add / Remove Columns Dynamically Into Unpivot Object

Jul 9, 2015

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? 

View 4 Replies View Related

SQL Server 2000 Merge Replication Process

Jun 22, 2004

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?

thanks
SV

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved