Trouble modifying a table
-
Hi There,
I am trying to add new fields to a table using enterprise manager by right clicking on the table, choosing design table and adding the new field/column. But when I choose save changes, I receive an error message:
------------------------------------------------------------
Errors were encountered during the save process. Some of the database objects were not saved.
'Comments' Table
-Unable to modify table
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL SERVER] ...
... Cannot drop the table 'dbo.Comments' because it is being used for replication
-----------------------------------------------------------
If it helps I am trying to add a binary (bit) field and a text field of 50 characters. I think the replication it refers to is a merge replication (not sure though is there a way i can find out in EM?)
I have read some articles which refer to this problem that suggest using stored procedures but I really dont know how to do that, having never used them before. I need an novice guide please!
I have tried using an 'ALTER TABLE...' statement but receive a similiar error message when I try to use it.
A bit of background info on the system. The front end is Access using linked tables from the sql server backend. There are a number of pc's which update the database directly and a number of laptops that can be taken off site. The user then had to connect the laptop to the network when they return and click 'synchronize' the update the information on their machine and the server (this is why the replication is necessary i guess?)
Let me know if I need to provide more info although as you can tell I don't fully understand what this replication business is all about or know much about sql server.
Thanks a lot
-
you need to use sp_Repladdcolumn, sp_Repldropcolumn to add/drop columns for tables that are being replicated.
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
Thanks. Are they stored procedures? How do you use those?
sorry really am a complete beginner
-
Hi, here are examples. You would run this on the publisher database. RM00101 is the name of a table. No I did not pick the name....
Your best bet is to look at the help file (aka BOL) for the instructions.
sp_repladdcolumn @source_object = 'RM00101'
, @column = 'LastChangedBy'
, @typetext = 'varchar(50)'
, @publication_to_add = 'none'
GO
sp_repladdcolumn @source_object = 'RM00101'
, @column = 'CreatedBy'
, @typetext = 'varchar(50)'
, @publication_to_add = 'none'
GO
-
Hi Thanks
I am still stuck i'm afraid. Heres what I tried:
I opened my database
Right-clicked on stored procedures and chose new stored procedure.
In the pop-up window I replaced the text that was there with what you gave me above just replacing the tablename and publication name with real ones.
I cheked the syntax which told me it was fine and then tried to click ok but got this message:
------------------
Error 21001: [SQL-DMO]Stored procedure definition must include name and test (for Standard StoredProcedure) or libraryname (for Extended StoredProcedure).
------------------
There is a name field which I can't edit - it just says Stored Procedure name or somethig but it is grayed out.
In the list of stored procedures I cant see sp_repladdcolumn is that right?
Any other ideas? I really appreciate the help!
-
Hi cd,
I read your post, and since you mentioned that you were using EM, I forgot that you were posting in the 2K5 replication forum.
Are you on SQL 2000 or 2005?
jeff
-
er....it must be 2k then? -
sorry if I am in the wrong place (if so can it be moved)
just an update - I think I have figured out how to add a new column using that sp_repladdcolumn. Just a general query, the column I added now appears after the rowguid column. Does that matter in any way?
-
Hi cd.
No worries about the wrong forum AFAIK. It happens. I just wanted to make sure.
On to your other question. The position of a column in a table should never matter, provided that none of the user code expects a particular order. So, we would always specify the column order when we issue selects and inserts.
I've got plenty of tables with columns after the rowguid.
-
Thanks for all the help Jeff.
Having now successfully added the field I am having trouble actually adding any data to it from the access front end. I get messages similiar to these 'Another user is trying to edit this record' or 'Changes have been made to this record since you first started...drop the changes?'
Any ideas what this could mean?
-
Hi cd,
If you are using Access with linked tables, I think you have to delete and re-link a table (IN the Access database, not on the SQL server) after you make changes.
jeff
-
Tried deleting and relinking to no avail. Anything I can try server side?
-
I assume that this was working before you added the new column. I also assume that nothing else has been done to use the new column.
So, I don't see that there would be anything on the server that would suddenly start making changes that would invalidate the row in the Access database.
It is likely that there is something that has to happen in the Access database to make this work again. The only thing I can think of is that perhaps you didn't pick the correct columns for the unique key when you linked the table.
If that is not the case, I would pose this as a new issue in the Access forum. I haven't used an Access front end in a very long time.
-
Yep it was working before, and it works if I drop that column so that everything is in its original state. Strange one.
Thanks for the help this far, Ill keep trying!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply