locked
Created read-only table RRS feed

  • Question

  • I created a new table using CREATE TABLE.  No errors.  However, my table is read-only.  How do I change it so it's not?  Thanks.

    Monday, July 25, 2011 11:56 PM

Answers

  •  

    Question#1: What text should I use going forward to designate a PK in my script?

    Question #2: MS Access has an Auto-Number data type. Is there a similar type in SQL Server?


    Below is a DDL example to create a table with a primary key IDENTITY column:

    CREATE TABLE dbo.MyTable(
    	MyTableID int NOT NULL IDENTITY(1,1)
    		CONSTRAINT PK_MyTable PRIMARY KEY
    	,MyOtherData int NOT NULL);
    
     
     

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Tuesday, July 26, 2011 1:02 AM
    Answerer
  • Based on Sivaprasad's earlier response, the underlying problem wass that no unique row identifier (e.g. primary key) existed on the table.  Access requires unique columns(s) in order for the data to be updatable.  Sivaprasad table is no longer read only have a primary key was created.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Tuesday, July 26, 2011 12:07 PM
    Answerer

All replies

  • Hi,

    Are you placed that table in read only file group or what ,How did you confirm that it is a read only ?

    If that is the case you can move that table to read /write file group.


    Sivaprasad.L Together We can Achieve
    Tuesday, July 26, 2011 12:06 AM
  • No, I didn't place it in a read-only group.  Here's a little bit more info.  I'm using Access 2007 as my front-end linked to a SQL Server 2008 Compact edition back-end.  Normally, I add tables simply by going into SQL Server Management Studio -> System Database -> Tables -> New Table and creating it manually.  I currently have around 20 tables.  This was my first attempt at creating a new table using a query-based script.  After running it, I received no errors, so I thought all was well.

    However, when my Access app tried to add a new record, I received the Read-Only error.  Since writing this message initially, I have since discovered that I forgot to create a Primary Key.  Since fixing this, I no longer am getting the Read-Only issue. 

    Question#1: What text should I use going forward to designate a PK in my script?

    Question #2: MS Access has an Auto-Number data type.  Is there a similar type in SQL Server?

    Thanks.

     

     

     

     

     

    Tuesday, July 26, 2011 12:43 AM
  •  

    Question#1: What text should I use going forward to designate a PK in my script?

    Question #2: MS Access has an Auto-Number data type. Is there a similar type in SQL Server?


    Below is a DDL example to create a table with a primary key IDENTITY column:

    CREATE TABLE dbo.MyTable(
    	MyTableID int NOT NULL IDENTITY(1,1)
    		CONSTRAINT PK_MyTable PRIMARY KEY
    	,MyOtherData int NOT NULL);
    
     
     

    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Tuesday, July 26, 2011 1:02 AM
    Answerer
  • Management Studio -> System Database -> Tables -> New Table and creating it manually !!!

    which system database you are using to create tables ,dont use system database , create new user database and use it for creating new objects(tables) .

    Question #2: MS Access has an Auto-Number data type.  Is there a similar type in SQL Server?

    I am not familiar with Access , we have identity value(which will increase while inserting new records) for table in sql it is similar with auto number data type in access.

    Hi Experts,

    Correct me if i am wrong

     


    Sivaprasad.L Together We can Achieve
    • Proposed as answer by Peja TaoEditor Wednesday, July 27, 2011 6:47 AM
    • Unproposed as answer by Peja TaoEditor Wednesday, July 27, 2011 6:48 AM
    Tuesday, July 26, 2011 2:56 AM
  • Hmm if you are allowed to CREATE TABLE that means  you are the owner, so what do you mean by READ ONLY? If you issue INSERT INTO that table it is failed?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, July 26, 2011 5:16 AM
  • There is no such thing as a read-only TABLE. A TABLE hold data, it is the user that has (or does not have) rights to it.

    Another application may mark it as read-only based on user-rights, or in Access, the lack of a PRIMARY KEY. Regardless, this is not an attribute of the TABLE itself.

    Tuesday, July 26, 2011 11:27 AM
    Answerer
  • Based on Sivaprasad's earlier response, the underlying problem wass that no unique row identifier (e.g. primary key) existed on the table.  Access requires unique columns(s) in order for the data to be updatable.  Sivaprasad table is no longer read only have a primary key was created.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Tuesday, July 26, 2011 12:07 PM
    Answerer