Error: Cannot update. Database is read-only? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Error: Cannot update. Database is read-only?

Author

Topic

accesser2003
Starting Member

14 Posts

Posted - 2007-07-26 : 21:38:22

How to get full permission:Add, Delete, Update, Edit in the SQL server database

--------------------------------------------------------------------------------

I wrote the following statements to update a SQL server database:

Dim MyDb As Database
Dim MyRs As Recordset

Set MyDb = DBEngine.Workspaces(0).OpenDatabase("AMD", dbDriverPrompt, False, "ODBC;DATABASE=AMD;DSN=Remote")

Set MyRs = MyDb.OpenRecordset("SELECT AllAttendanceEvents.* FROM AllAttendanceEvents ORDER BY AllAttendanceEvents.EntryTime DESC")

MyRs.MoveFirst

While Not MyRs.EOF
MyVal = MyRs!EntryTime
Debug.Print MyVal
MyRs.Edit
MyRs!Ix = 50099
MyRs.Update
MyRs.MoveNext
Wend

When it execute the red line statement, it generates the error:Cannot update. Database or object is read-only.
This error is generated isnpite that I already checked the boxes: SELECT, INSERT, UPDATE, DELETE in the Permissions page of the tables?

Please advise.

Kristen
Test

22859 Posts

Posted - 2007-07-27 : 01:52:09

Is the database itself set to READ ONLY?

accesser2003
Starting Member

14 Posts

Posted - 2007-07-27 : 03:09:33

how can I know if it is itself READ ONLY? Would you please tell me how to?

Thankful

Kristen
Test

22859 Posts

Posted - 2007-07-27 : 03:47:20

This might do the trick:


select [Database]  = convert(varchar(255), name),
       [ReadOnly]  = substring('NY', (status / 1024 % 2) + 1, 1)
from master..sysdatabases

Kristen

accesser2003
Starting Member

14 Posts

Posted - 2007-07-27 : 04:18:50

As I am a beginner, would you please tell me step by step where I should write this SQL statement, and what should be the result to know if the database is READ-ONLY or not?

Thanks,

accesser2003
Starting Member

14 Posts

Posted - 2007-07-27 : 04:28:29

I wrote this SQL in the Query Analyzer editor and then excuted it. The result was as follow:

Database ReadOnly
AMD N
master N
model N
msdb N
Northwind N
pubs N
tempdb N

What does this means

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-27 : 04:56:05

it tells you the read only status of all databases.

considering all of them are N which means 'No'
all of your db's are NOT rean only.

this means that you probably have permission problems.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-28 : 00:16:38

How do you connect to sql in odbc dsn? Did you grant permission to the sql id used in dsn?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources