Table is opening in Read only Mode

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Table is opening in Read only Mode

Author

Topic

Anuradha
Starting Member

6 Posts

Posted - 2005-09-09 : 06:49:40

Hi all,
I have created a table in dbo user, when I open the table in query analyzer the table opens in read-only mode. All the other table do not have this problem. Below is the syntax of the table
CREATE TABLE RCPTTRAN
(
ITEM_CODE VARCHAR (8) NOT NULL,
ITEM_DESC VARCHAR (20) NOT NULL,
VEND_CODE VARCHAR (8) NOT NULL,
VEND_DESC VARCHAR (30) NOT NULL,
UNIT_OF_MES VARCHAR (3) NOT NULL,
RCPT_DATE CHAR(10) NOT NULL DEFAULT
substring(convert(char (10),getdate(),21),1,10),
ITEM_RATE DECIMAL (10, 2) NOT NULL,
DC_NO VARCHAR (10) NOT NULL,
DC_DATE CHAR(10) NOT NULL DEFAULT
substring(convert(char (10),getdate(),21),1,10),
QTY_RECV INT NOT NULL,
QTY_REJ INT NOT NULL,
QTY_ACPT INT NOT NULL,
TRAN_TYPE CHAR (1) NOT NULL
)
The dbo user has all the permissions, yet table opens in read-only mode.What should I change?

Regds,
Anu

Kristen
Test

22859 Posts

Posted - 2005-09-09 : 06:55:59

Hi Anuradha, Welcome to SQL Team!

You need a Primary Key to be able to update a row [EDIT: in Query Analyser] (otherwise it won't know which one to update when you save it)

Is there a good reason why you are storing dates as CHAR(10)? Much better to use a native datetime format so that you can compare them chronologically

Kristen

Anuradha
Starting Member

6 Posts

Posted - 2005-09-09 : 07:14:05

Hi,
Do you mean to say that you cannot create a table(that has to be inserted or updated) without primary key?

Regds,
Anu

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-09 : 07:47:09

man i want to say yes....
but no that's not what he meant.
QA only allows a table into edit mode if the table has a primary key on it.
a primary key should be something that uniquely identifies a row.

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-09 : 07:52:51

>>man i want to say yes....

That should be woman

Mladen, did you mean opening the .sql file (that has create table stucture) in QA will result in this error?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-09 : 07:58:37

"did you mean opening the .sql file ..."

Nope ... run the CREATE TABLE in QA, and then open Object Viewer, then RightClick the new table and choose OPEN. No editing allowed (in QA)

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-09 : 08:02:04

Cool. Thats new thing to me

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-09 : 08:07:30

Now you mention it I've never used it - I do those sorts of "ghastly hacks"

from Enterprise Manager because I can put a query on the table to restrict the number of rows.

I'll try to remember its there for the future though - particularly a brand new table that I want to put a couple of rows into.

Kristen

Anuradha
Starting Member

6 Posts

Posted - 2005-09-09 : 08:11:05

Hi,
This is getting nowhere. Please tell me how can I create the table in an editable mode.

Regds,
Anu

Kristen
Test

22859 Posts

Posted - 2005-09-09 : 08:13:22

"This is getting nowhere"

Sorry?

Just create a Primary Key on the table

Kristen

Anuradha
Starting Member

6 Posts

Posted - 2005-09-09 : 08:15:45

Hi,
I dont want the primary key on any of the columns!!! I created the table in Enterprise Server but still form QA the table is Read-Only. How can I create a table that is editable and without any Primary key constraint.

Regds,
Anu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-09 : 08:21:30

You want it to be editable to update records. Right?
Then dont use Object browser option, write simple queries to update values

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-09 : 08:21:33

and without a primary key it will stay read only.
you can update it with update statements in insert new data with insert into statements.

i should point out that creating any serious table without PK is silly and it will lead to trouble in the future.

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-09-09 : 08:31:45

"How can I create a table that is editable and without any Primary key constraint"

As in "editable" from Query Analyser?

You can't.

That's what we've being saying.

To edit a table from Query Analyser it has to have a Primary key.

Kristen

Anuradha
Starting Member

6 Posts

Posted - 2005-09-09 : 08:36:27

Hi,
I am using cobol programs which connect to sql server via odbc. My sql is embedded in cobol and when i execute the application these sql will insert/update the rows in sql server.But sql server is not allowing me to insert/update. I get a cobolcode error that the table cannot be inserted/updated. When I open from QA the table shows read-only. Now how do I insert into table without creating PK

Regds,
Anu

Kristen
Test

22859 Posts

Posted - 2005-09-09 : 08:40:56

Do this in Query Analyser:


INSERT INTO RCPTTRAN
SELECT
	ITEM_CODE ='aaa', 
	ITEM_DESC ='aaa', 
	VEND_CODE ='aaa', 
	VEND_DESC ='aaa', 
	UNIT_OF_MES ='aaa', 
	RCPT_DATE = '2005-09-09', 
	ITEM_RATE = 123.45, 
	DC_NO ='aaa', 
	DC_DATE = '2005-09-09', 
	QTY_RECV = 1,
	QTY_REJ = 1,
	QTY_ACPT = 1,
	TRAN_TYPE = 'X'

Do you get an error? If so what does it say?

Kristen

Anuradha
Starting Member

6 Posts

Posted - 2005-09-09 : 08:46:24

1 Row is inserted when executed from QA. When theses values are hardcoded in cobol prgm and executed then give error

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-09 : 08:48:12

Did you get error when you execute query from Cobol program?
If so you need to fix your Cobol code
Post that code you used

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-09 : 08:48:17

Then the problem is something to do with the Cobol end, the table itself is not Read Only (for the user that you are logging onto Query Analyser as)

What's the error message from the Cobol end?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-09 : 08:52:21

well then there's something wrong either with your odbc driver or with the syntax.
what's the exact error you get?

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources