Altering Table && Trans. Replication
Jul 23, 2005Hi,
How can I modify table with publication (change of one column length)
without completely breaking replication.
Thanks in advance
ADVERTISEMENT
Réplication Trans. And Pk ?
Feb 1, 2001Hello, During an intantanée arrest of replication of data base sql server 7 sp2 (nt4),
I lose keys on the subscriber, how don't have it? Thank you in advance easter
pascal
Disable Trans. Replication - HELP!
Mar 20, 2000Hi,
I am trying to disable transactional replication, but am having some problems. I used the wizard, however, it has taken 7 hours so far and is still not done. SQL Server (7.0) is showing the connection as runnable but it seems as if nothing is being removed. Am I missing something? Should I have done something else before running the wizard? I can't even kill the SPID.
Can anyone help or offer some advice?
Thanks in advance...
How Do I Shrink The Log Of A DB Used In Trans Replication?
May 2, 2006I am sure this has been asked multiple times before, but I have a DB (SQL 2K) that is involved in transactional replication. The log periodically grows to a seriously big size. How can I shrink it without damaging replication? I have opened SSIS (SQL 2005 is the subscriber) and tried to shrink the file, but to no avail. It reckons I could shrink it to 0MB, but I feel this would throw out replication.
Any help appreciated.
Trans Replication - Filtering && Deleting
Jul 14, 2003Here's what I'm trying to do (Maybe there's a better way)
I'm using Transaction Replication and a pull subscription to get all the transactions from the ORDERS table within the last year from Server_A to Server_B. I filter the rows "WHERE (ORDR_DATE > (GETDATE()) - 365)"
That works fine, BUT, the old rows in the subscriber's ORDERS table do not get deleted, so I have all the records for the last year, plus the older records that I don't want any more. Do I need another step some where that does a "Delete WHERE (ORDR_DATE < (GETDATE()) - 365)" But then I have to maintain my date logic in more than 1 place if I ever want to change it.
Any suggestions, thoughts, improvements will be appreciated .... Thanks a lot
Trans Replication Agent Error
Aug 4, 2003What does this error message mean ?
The process could not bulk copy out of table '[dbo].[syncobj_0x3732313945323046]'.
I ran transaction replication successfully last night. Then it was scheduled to run again in the morning, but failed .... any ideas ?
SQL2K SP2
Trans Replication Works Under Certain Conditions.
Mar 6, 2006I have 2 SQL 2000 servers (both have SP4) and are running on Win2003 SP1. We will call them SQL1 and SQL2.
SQL1 is the publisher and distributor for trans replication, SQL2 is the subscriber with immediate updating, and queued updating as failover.
I configured the publisher and subscriber. The snapshot replicates fine to the subscriber and all the agents are working fine. There is only 1 table article configured for replication.
Let's say I am trying to update a single row.
I can make as many updates on this row to the publisher as I want and they all replicate just fine to the subscriber. (Note: an update to publisher row causes a new GUID to be generated in the "msrepl_tran_version" column.) The updated data and the new GUID are successfully replicated to subscriber. I can continue to successfully make as many updates to this row on the publisher just fine.
Now I want to make an update to this row at the subscriber: When I do this (via enterprise manager), I update column 1 with a new value, but a new GUID is NOT created on the subscriber. The column I updated successfully replicates back to the publisher on the first update attempt. This update causes the publisher to create a new GUID for the row, but the new GUID does not replicate back to the subscriber. (at this point the publisher and subscriber do not have matching GUID values).
Further updates to this row on the subscriber cause an error "...rows do not match between publisher and subscriber...", and further updates to the publisher do not cause an error message, but simply does not update back to the subscriber.
Any help would be greatly appreciated!
Altering Table Definition On A Linked Server Table.
Aug 11, 2000SQL Server 7.0 (SP1)
Error:
------
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.
A column was deleted from the a table on the linked server and now this message appears when using the linked server definition to access the table. Deleting/Recreating the Linked Server has no effect. I found an earlier note on this...but it just kind of ended with no resolution. Anyone have any thoughts on this now.
Thanks for any input.
Altering Table
Dec 2, 2003Hai All.
I want to know ,is there any way to modify a table's field like adding of new field to a table.
If any one have idea plz enlighten me.
Bye
Regards,
Karthik.A
Altering Table With Default Value
Aug 19, 2004Hi, How to alter a table with default value?
I am using the below statement, But, it is not working..Any pointers?
Thx..
-------------------------------
alter table action_item ALTER COLUMN STATUS default 0
Altering A Published Table
Feb 12, 2004Hi! I've added a column to a published table in the Publisher using SP_REPLADDCOLUMN. After doing this the replication triggers for that table(i.e. del_%,upd_%,ins_%) are doubled both at the Publisher and at the Subscriber. If i update anyone of the column in the table at the Subscriber; i get the following error:
Server: Msg 208, Level 16, State 1, Procedure upd_3E6DE124B82D42A5AEB169557C0D757C, Line 60
Invalid object name 'ctsv_3E6DE124B82D42A5AEB169557C0D757C'.
Any ideas, what has gone wrong????
I have the following SQL setup.
Publisher: Enterprise Edition, SP3.
Subscriber: Standard-Edition, SP3.
The error is at the Subscriber.
Altering Table Replicated
Feb 2, 2007How can i change my Table Structure that is replicated?
I need to add a new field.
Altering Strings Depending On Data In A Table.
Sep 10, 2007Hi Guys,
I'm wondering if an idea I'm playing with is feasible and if so, how you would recommend implementing it.
Let's say I have a Dictionary table, 2 columns:
Word | Definition
And I have a string - "The cat sat on the dog"
If there's a definition for "cat" in the dictionary table, I want to alter the string so it becomes "The >>cat<< sat on the dog"
At the same time, if there's also a definition for "dog" then my string now becomes "The >>Cat<< sat on the >>Dog<<"
The idea being that when I manipulate the data in my ASP I can replace() the >><< with specific HTML code. (I'm trying to recreate the "in text" advertising thing that lots of people seem to be using - but not doing adverts, just information for our users - Someone hovers over a highlighted word, and with a little bit of Ajax, I can pull the definition out...
I'm not sure (but I'm suspecting) that it would make more sense to do this as I'm storing the string in a table, rather than as I'm pulling it out ready for use (don't want to be slowing my end users down )
Any ideas?
Thanks in advance
-Craig
Altering SQL Server 2000 Table Design
Jul 20, 2005I'm trying to do a simple alteration to the table design of one of ourSQL 2k tables, simply changing an identity row so that its not 'notfor replication', and its taking absolutely ages to do so, and stopsthe sql server from working.Whilst it's attempting the update, no one can access the database, thesqlservr.exe memory usage shoots up and enterprise manager reports anot responding status. Eventually after about 10 minutes, it bombs outreporting,Unable to modify tableCould not allocate space for object 'Tmp_TableName' in database'DBNAME' because the 'PRIMARY' filegroup is full.The table i'm attempting to change has only about 4000 records sothere's not a huge amount of data.Any ideas what's causing this and how i can get around it?A similar thing happens when i attempt to change the length of avarchar too.Thanks in advance for any suggestionsDan Williams.
View 3 Replies View RelatedTransact SQL :: Altering A Table Without Impacting The Performance?
Sep 15, 2015Altering a table which is having more than 100 million rows. Would like to know the best possible way to add a new column to this table without impacting the performance much.
View 7 Replies View RelatedT-SQL (SS2K8) :: Error Altering Field Spec In A Table
Apr 18, 2014I have a db1.tPersonJobHist where I have two columns
[WorkflowCoordinatorFlag] [dbo].[shrsFlag] NOT NULL,
[HRBusinessPartnerCode] [dbo].[shrsCode] NULL,
Our developer mistakenly made HRbusinesspartnercode field as nullable. He now wants to change it to Not Nullable.
So there were 4 rows where the values were not null(not sure how). We edited those rows and changed those values to NUll. Now we have no Nulls in that column.
So we brought up the table designer and made the change but got the following error
'tPersonJobHist' table
- Unable to modify table.
Cannot insert the value NULL into column 'HRBusinessPartnerCode', table 'BD6578.dbo.Tmp_tPersonJobHist'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Question is why wouldn't it let me alter the table design. and what is tmp_tpersonjobhist.?
How can I make this column Not-nullable.
Trans Replication With Updating Subscriber On Sql2000 (single Quote In The Data As Char Data Type)
Nov 17, 2006Hi,
I am trying to setup Trans Replication with updating subscriber on sql2000. One column on few tables got data with single quote (').
How do I handle in this case? Did any one come across such case?
Can I Change default QUOTED IDENTIFIER from ' (single quote) to something else (@@@) on SQL2000?
If yes, how to do?
Thanks
mka
Help Altering Database Table From NULL To NOT NULL
Jun 28, 2004I would drop and add the table but the data can't be deleted. So if anyone could help with the statement it would be greatly appreciated. Thanks
View 7 Replies View RelatedAdding New Table In Replication And Changing One Column Replication Database
Jan 17, 2002Hi,
In my production box is running on SQL7.0 with Merge replication and i want add one more table and i want add one more column existing replication table. Any body guide me how to add .This is very urgent
Regards
Don
Altering Data
Jul 20, 2005Hello!I have a problem that I really could use some help to solve. I have a tablewhich looks like this:id1, id2, id3, rate, ratenrExamples of a select * from this table would be:1047336399 21000 1 617 11047336399 21000 1 624B 11047336399 21000 1 621D 11047336399 21000 2 624B 11047336399 21000 2 612A 11047336399 21000 2 621D 11047336399 21000 3 617 11047336399 21000 3 624B 11047336399 21000 3 621D 1I would like to transform this table into something like this:1047336399 21000 1 617 1 624B 1 621D 11047336399 21000 2 624B 1 612A 1 621D 11047336399 21000 3 617 1 624B 1 621D 1the three first columns should be the primary key.Any help is appreciatedGunnar
View 1 Replies View RelatedPermissions For Altering Databases
Oct 18, 2001I have an environment where I would like to grant the ability to perform certain functions to specific users for all databases on one of my SQL Servers. Specifically, I want to allow someone to alter table structures on the server, without having the ability to modify data within any databases on the server and without having the ability to create new databases on the server. Is this possible, and if so, how?
View 2 Replies View RelatedAltering Logical Filenames
Oct 24, 2001Pros,
I would like to change the logical filenames for a database on my test server. I know this is done with ALTER DATABASE, but I'm not sure of the exact syntax. Can anyone help?
rb
Altering A Job Scheduled In EM Of SQL2000
May 4, 2000I was able to create a scheduled job in Enterprise Manager 8.0 on a server running 7.0, but I couldn't find a way to disable it. I've used the sp_delete_job stored procedure to delete my job and re-entered it with new parameters.
Is using the sp_delete_job sp only way to modify scheduled jobs?
If yes, I have a problem since my 7.0 Enterprise Manager is gone.
Any ideas?
Pete
Altering A Computed Column?
Feb 27, 2008somehow I am not able to figure this out.
How do I change a computed column using the ALTER TABLE ALTER COLUMN... command?
Altering Column Names?
Jan 25, 2004I have been looking in the SQL Server Help Files, but I cant seem to find the syntax to change the name of a column name..?
Can it be done? if so, what is the syntax?
SQL 2012 :: Altering The DB Role?
Sep 19, 2014I have created a login with some restrictions ,By default the Login has DB_Owner Role,Now i want to change that Role to DB_datareader.
How can i alter the Login role from db_owner to db_datareader.
Creating A Job To Altering Trigger
May 22, 2008Hi Everyone,
I have a database which once every week gets backup/restored and then renamed for training purpose. The problem is that I got trigger on some of the tables and once the database gets renamed the trigger failed to work as it referring to the old database name. I have idenify all the trigger and done a ALTER TRIGGER. However I can't seem to get it to run as a JOB. Tried to create a store procedure but get a error as the ALTER TIGGER is after CREATE PROCEDURE I could do it as a SQLstring but theirs alot of trigger, want to know if there any other ways of doing it. Please help
Altering The Datatype Of A Variable?
Jul 21, 2006The DATEDIFF(s, time1, time2) function output is of the 'datetime' datatype.
How can I alter the 'datetime' to 'int' (or 'bigint') datatype to make it compatible with other variables in my calculations?
Altering Linked Tables
Jul 20, 2005I have a table (in Access) that is linked to the SQL server, and Ineed to add a column to it. I wrote the following:ALTER TABLE CensusADD COLUMN 'ActiveFacility' BIT;and I get a syntax error that I do not know how to solve. The columnneeds to contain yes/no data for each record.Any help for a struggling newbie?Thanks,Christine
View 1 Replies View RelatedHelp Altering/Creating Columns
May 19, 2008I need help in SQL in 2 things:
How do i modify a column to change it to a primary key
How do i add a column that is of type bit, with no nulls, and a default value of ((0))
Thanks
Kevin
MS SQL 2005 Log Shipping And Altering A DB
Feb 26, 2008Question: I have Log Shipping setup and i am wanting to verify it will do what we need. That is when a table is changed in our production enviroment that change is replicated into our other server. IE I rename a field in a table or create a new table... You know change the structure... How do I replicate that stuff to my other servers?
View 6 Replies View RelatedMerge Altering Metadata
Aug 8, 2007I have a column going into a merge DT_WSTR length 50 on both(left/right) sides, yet when viewing the resulting output metadata, the length is 16. (I am viewing the metadata by clicking on the arrows)
This is obviously wrong.
NB originally the length was not set to this. As an attempt to try and fix the problem, I have added a placeholder column before all of my conditional splits of str50 type.
Why is it doing this?
This is causing my 0xC02020C1 error, I am convinced of it.
One of the errors has now gone away but the other two merges still have the error.
SSIS seems to assume that you will make no mistakes and will never want to go back and change anything, woe betide you if you have to go back and alter it later.
I have also noticed that certain (other) errors go away merely by opening and closing the tasks. Shocking.
TRANS Log
Oct 27, 1999I wnat to have a log file with all the updates and inserts made to the DB.
I understand I have to do a trans-log,how?
in order to make a query a transaction do I only have to declare trans...commit ??
tanx,
eyal