Results 1 to 13 of 13

Thread: Can't change the contents of a table in SQL Server2005

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Dec 2008
    Posts
    73

    Can't change the contents of a table in SQL Server2005

    Hi,
    I have a problem that in a database (named DDS) i can change or delete records in all tables but there is one which i can't. There isn't any foreigh keys(There is one dependencies, a view). So could you please tell me that is there any way that while designing a database in SQL Server we can lock a table and is there any way to open it and change the values of a field. There are two constraints as:

    USE [DSS]
    GO
    ALTER TABLE [dbo].[tblGlob] DROP CONSTRAINT [DF_tblGlob_BYDEFT]

    USE [DSS]
    GO
    ALTER TABLE [dbo].[tblGlob] DROP CONSTRAINT [DF_tblGlob_SetValue]

    Even if i delete these constaints, still i can't change the data in this table.
    Here is the image of error i get:-
    Name:  S1.JPG
Views: 262
Size:  25.6 KB

    The login has all rights for that database.
    Is there any one who knows how to do this.

    Ayyaz

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Can't change the contents of a table in SQL Server2005

    It sounds like either you don't have a primary key or you are changing the PK value to the same as another row.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Dec 2008
    Posts
    73

    Re: Can't change the contents of a table in SQL Server2005

    I checked there is no primary key for this table. No triggers, only those two CONSTAINTS which i have mentioned above, on those two fields( BYDEFT & SETVALUE, there data type is bit). Basically i am trying to change a field named COMPANY_ADDRESS but i wont let me do this. Could you please also tell me what is the meaning of these two constraints.
    Ayyaz

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Dec 2008
    Posts
    73

    Re: Can't change the contents of a table in SQL Server2005

    please i need quick help.
    Ayyaz

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Can't change the contents of a table in SQL Server2005

    Why is there no primary key? It's a rare thing that it's appropriate for a table not to have a primary key. It's especially rare when you want to make changes to the data in that table. How exactly do you expect to uniquely identify a record in order to update it if you don't have a primary key?

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Dec 2008
    Posts
    73

    Re: Can't change the contents of a table in SQL Server2005

    Here is the DDL for this table:

    USE [DSS]
    GO
    /****** Object: Table [dbo].[tblGlob] Script Date: 02/21/2013 14:09:40 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[tblGlob](
    [PrinterName] [varchar](50) NULL,
    [GlobalExpiry] [varchar](50) NULL,
    [DBSTATUS] [bit] NULL,
    [BYDEFT] [bit] NULL,
    [CompanyName] [varchar](200) NULL,
    [CompanyAddress] [varchar](200) NULL,
    [SettingName] [varchar](500) NULL,
    [SetValue] [bit] NULL,
    [Organization] [varchar](200) NULL,
    [Phone] [varchar](50) NULL,
    [MODIFIER] [varchar](50) NULL,
    [LAST_MODIFIED] [datetime] NULL,
    [OPERATION_TYPE] [varchar](50) NULL,
    [BankName] [varchar](500) NULL,
    [Logo] [image] NULL,
    [Email] [varchar](200) NULL,
    [Website] [varchar](200) NULL,
    [EmailServer] [varchar](500) NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Can't change the contents of a table in SQL Server2005

    The questions I asked in my previous post were not rhetorical, i.e. I asked them because I wanted answers to them.

  8. #8
    Lively Member
    Join Date
    Apr 2011
    Posts
    75

    Re: Can't change the contents of a table in SQL Server2005


  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Can't change the contents of a table in SQL Server2005

    Given that there is no PKey ... if what we are to believe what we are told ... I wonder if there is another table that is using CompanyAddress as a foreign key to this table... as a result it is acting as a pseudo pkey. Or.... keep reading, another thought occurred...

    The two "constraints" you dropped are not constraints at all... they are defaults... they set the default value of a couple of fields for cases when no specific value is given... dropping them does no good, and could potentially do harm as now they are lost... hopefully you scripted their definition somewhere before hand so you can add them back in.

    What you need to be looking for is not constraints but indexes... It's possible there is an index with a unique and/or clustered index on the field...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Can't change the contents of a table in SQL Server2005

    I'm pretty sure this is a "bug" in SqL Server. (I'm not sure it's truly a bug but if it's WAD then the D is ropey, IMO). I know it existed in 2005, not sure after that.

    If you have no primary key on a table then SQL Server can only tell the difference between the records based on the complete set of field values. It does't reliably maintain some underlying unique ID as you might expect. That's fine... right up until you have two records in the heap that have exactly the same set of values. Once you create that situation SQL Server can no longer uniquely identify the records. Even that's not a serious problem until you come to do an update at which point the update mechanism can't work out which record to update and it barfs. You'd think it would update both if they meet the criteria specified in the where clause but it doesn't, it just barfs. You can't delete the records either, for the same reason. Neither can you creaet a primary key retrospectively becasue the records aren't, of course, unique.

    The only solution I found when I got bit by this a few years ago was to:-
    1. Script the table for create, making sure you sript and indexes, constraints etc.
    2. Do a Select unique * Into CopyTable (The unique will remove the duplicates)
    3. Drop the original table
    4. Recreate it using your scripts
    5. Copy the data back again using an Insert Into
    6. Even if you genuinely intended the records not to be unique, give the table an arbitrary primary key anyway so you don't get stung again
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11
    Frenzied Member
    Join Date
    Mar 2004
    Location
    Orlando, FL
    Posts
    1,618

    Re: Can't change the contents of a table in SQL Server2005

    Yeah the issue is you have a identical rows with no primary key. I'd probably see if it was on or two, if so, drop them with a t-sql script, add a primary key field, and then do your alter table...
    Sean

    Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.

  12. #12
    Serge's Avatar
    Join Date
    Feb 1999
    Location
    Scottsdale, Arizona, USA
    Posts
    2,744

    Re: Can't change the contents of a table in SQL Server2005

    Quote Originally Posted by FunkyDexter View Post
    I'm pretty sure this is a "bug" in SqL Server. (I'm not sure it's truly a bug but if it's WAD then the D is ropey, IMO). I know it existed in 2005, not sure after that.

    If you have no primary key on a table then SQL Server can only tell the difference between the records based on the complete set of field values. It does't reliably maintain some underlying unique ID as you might expect. That's fine... right up until you have two records in the heap that have exactly the same set of values. Once you create that situation SQL Server can no longer uniquely identify the records. Even that's not a serious problem until you come to do an update at which point the update mechanism can't work out which record to update and it barfs. You'd think it would update both if they meet the criteria specified in the where clause but it doesn't, it just barfs. You can't delete the records either, for the same reason. Neither can you creaet a primary key retrospectively becasue the records aren't, of course, unique.

    The only solution I found when I got bit by this a few years ago was to:-
    1. Script the table for create, making sure you sript and indexes, constraints etc.
    2. Do a Select unique * Into CopyTable (The unique will remove the duplicates)
    3. Drop the original table
    4. Recreate it using your scripts
    5. Copy the data back again using an Insert Into
    6. Even if you genuinely intended the records not to be unique, give the table an arbitrary primary key anyway so you don't get stung again
    I would second this except the indexes. I would add all indexes(except PK) afterwards. I always do that because most of the time I move a lot of data and populating a table with lots of data is slow. Adding indexes afterwards is actually faster. I've done this exercise many times with millions of records. If you create your table with indexes right of the bat, the inserts are a lot slower as it has to physically reorder every insert. Other than that, a good advice.

  13. #13
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Can't change the contents of a table in SQL Server2005

    Quote Originally Posted by Serge View Post
    I would second this except the indexes. I would add all indexes(except PK) afterwards. I always do that because most of the time I move a lot of data and populating a table with lots of data is slow. Adding indexes afterwards is actually faster. I've done this exercise many times with millions of records. If you create your table with indexes right of the bat, the inserts are a lot slower as it has to physically reorder every insert. Other than that, a good advice.
    ONLY if they are clustered indexes... which should only be based on a single, or two fields tops... but non-clustered indexes should have minimal, if any impact. I rarely use clustered indexes, unless there is a specific index that would benefit greatly from it... other than that, I index fields which tend to get used in where clauses, or in lookups... then create covering indexes which includes multiple fields.

    Example: If I have a table where I've got two fields that keep coming up in where clauses, and sometimes I have one, sometimes I have the other, and sometimes I have both... I'll create an index on each field individually, then create an index on top of that with both fields.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width