Can't attach db - says read-only

Hi all,
SQL novice here.
I had a SQL Server 2000 machine that died, but it had some
data I need, so i pulled the drive, found the SQL data folder
and copied it to a network location.
On my SQL Server 2003 machine runnig SQL Server Express, I
right click on Databases, select Attach, point to the database
I want, and the process fials saying the data is read-only, make
it read write and try again.
Problem is, the files are not read-only. I checked and reset
them several times, and each attempt fails with the same message.
How can I attach this database to my sql server?
Thanks,
kpg
There is a article on Microsoft's web site that briefly discusses this issue
on SQL 2005 Express:
http://msdn2.microsoft.com/en-us/library/bb264564.aspx
If you've already checked the permissions on the file, see if you can check
the permissions you have on the database server and folder where the file is
being written to. Your permissions may be different than what they were on
the original DB server.
"kpg*" wrote:

> Hi all,
> SQL novice here.
> I had a SQL Server 2000 machine that died, but it had some
> data I need, so i pulled the drive, found the SQL data folder
> and copied it to a network location.
> On my SQL Server 2003 machine runnig SQL Server Express, I
> right click on Databases, select Attach, point to the database
> I want, and the process fials saying the data is read-only, make
> it read write and try again.
> Problem is, the files are not read-only. I checked and reset
> them several times, and each attempt fails with the same message.
> How can I attach this database to my sql server?
> Thanks,
> kpg
>
|||> There is a article on Microsoft's web site that briefly discusses this
> issue on SQL 2005 Express:
> http://msdn2.microsoft.com/en-us/library/bb264564.aspx
> If you've already checked the permissions on the file, see if you can
> check the permissions you have on the database server and folder where
> the file is being written to. Your permissions may be different than
> what they were on the original DB server.
Thank you * 100!
I copied the db file into the data folder of the sql 2005 instance
and yes, I was able to attach it. So it was a folder permission
issue.
Thanks again!
kpg
sql

Can't attach db - says read-only

Hi all,
SQL novice here.
I had a SQL Server 2000 machine that died, but it had some
data I need, so i pulled the drive, found the SQL data folder
and copied it to a network location.
On my SQL Server 2003 machine runnig SQL Server Express, I
right click on Databases, select Attach, point to the database
I want, and the process fials saying the data is read-only, make
it read write and try again.
Problem is, the files are not read-only. I checked and reset
them several times, and each attempt fails with the same message.
How can I attach this database to my sql server?
Thanks,
kpgThere is a article on Microsoft's web site that briefly discusses this issue
on SQL 2005 Express:
http://msdn2.microsoft.com/en-us/library/bb264564.aspx
If you've already checked the permissions on the file, see if you can check
the permissions you have on the database server and folder where the file is
being written to. Your permissions may be different than what they were on
the original DB server.
"kpg*" wrote:
> Hi all,
> SQL novice here.
> I had a SQL Server 2000 machine that died, but it had some
> data I need, so i pulled the drive, found the SQL data folder
> and copied it to a network location.
> On my SQL Server 2003 machine runnig SQL Server Express, I
> right click on Databases, select Attach, point to the database
> I want, and the process fials saying the data is read-only, make
> it read write and try again.
> Problem is, the files are not read-only. I checked and reset
> them several times, and each attempt fails with the same message.
> How can I attach this database to my sql server?
> Thanks,
> kpg
>|||> There is a article on Microsoft's web site that briefly discusses this
> issue on SQL 2005 Express:
> http://msdn2.microsoft.com/en-us/library/bb264564.aspx
> If you've already checked the permissions on the file, see if you can
> check the permissions you have on the database server and folder where
> the file is being written to. Your permissions may be different than
> what they were on the original DB server.
Thank you * 100!
I copied the db file into the data folder of the sql 2005 instance
and yes, I was able to attach it. So it was a folder permission
issue.
Thanks again!
kpg

Can't attach db - says read-only

Hi all,
SQL novice here.
I had a SQL Server 2000 machine that died, but it had some
data I need, so i pulled the drive, found the SQL data folder
and copied it to a network location.
On my SQL Server 2003 machine runnig SQL Server Express, I
right click on Databases, select Attach, point to the database
I want, and the process fials saying the data is read-only, make
it read write and try again.
Problem is, the files are not read-only. I checked and reset
them several times, and each attempt fails with the same message.
How can I attach this database to my sql server?
Thanks,
kpgThere is a article on Microsoft's web site that briefly discusses this issue
on SQL 2005 Express:
http://msdn2.microsoft.com/en-us/library/bb264564.aspx
If you've already checked the permissions on the file, see if you can check
the permissions you have on the database server and folder where the file is
being written to. Your permissions may be different than what they were on
the original DB server.
"kpg*" wrote:

> Hi all,
> SQL novice here.
> I had a SQL Server 2000 machine that died, but it had some
> data I need, so i pulled the drive, found the SQL data folder
> and copied it to a network location.
> On my SQL Server 2003 machine runnig SQL Server Express, I
> right click on Databases, select Attach, point to the database
> I want, and the process fials saying the data is read-only, make
> it read write and try again.
> Problem is, the files are not read-only. I checked and reset
> them several times, and each attempt fails with the same message.
> How can I attach this database to my sql server?
> Thanks,
> kpg
>|||> There is a article on Microsoft's web site that briefly discusses this
> issue on SQL 2005 Express:
> http://msdn2.microsoft.com/en-us/library/bb264564.aspx
> If you've already checked the permissions on the file, see if you can
> check the permissions you have on the database server and folder where
> the file is being written to. Your permissions may be different than
> what they were on the original DB server.
Thank you * 100!
I copied the db file into the data folder of the sql 2005 instance
and yes, I was able to attach it. So it was a folder permission
issue.
Thanks again!
kpg

Cannot update. Database or object are read-only

I have to build application Excel (interface) - SQL Server (backend)

I've created Data Source File "test.dsn" and tested succesfully.
SQL Server database in on the other machine.
This is a code that I am using.

And error message I am geting is "Cannot update. Database or object are read-only".

Dim cn As ADODB.Connection
Dim strSQL As String

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\pd\Desktop\Development\test.xls;" & _
"Extended Properties=Excel 8.0"


strSQL = "INSERT INTO [DSN = test.dsn].Region Values(5, 'ab');"


cn.Execute strSQL

cn.Close
Set cn = Nothing

Read-only of databse properties is not selected.If I get it right you're attempting to insert a row into excel. As long as I know it's not possible because you cannot update, insert records throught Jet provider. mojza|||Your query is unclear . Understanding you are trying to update region table In sql database .
if that's case you should then rather open an ODBC or OLE DB connection to SQL server

cn.Provider = "sqloledb"
cn.Properties("Data Source") = ServerName
cn.Properties("Initial Catalog") = DatabaseName

' If Windows NT authentication.
cn.Properties("Integrated Security")= "SSPI"

'If SQL Authentication
cn.Properties("User ID")= UserID
cn.Properties("Password") = pwd

' Open the database.
cn.Open

Dim strSQL as string

strSQL "INSERT INTO dbo.Region Values(5, 'ab')"

cn.execute strSQL

cn.close
set cn = nothing

cannot retrieve data from linked server with read-only database

I have linked a SQL Server 6.5 to a SQL Server 2000. The database I'm retrie
ving data from is read-only (a user database, not master).
When I do a select column1, column2 from [linked server].[database].dbo.[table]
it returns some error like
cannot begin transaction because the database is read-only.
What setting I can use to specify that I'm interested in just read-only acce
ss, and no transactions whatsoever?
P.S. I was watching the traffic with Network Monitor, and after a lot of log
in frames and metadata retrieval, the offending statements came (approximate
d from memory):
set implicit_transactions on
begin transaction
set @.@.ROWCOUNT = 1
select * from [the table][QUOTE]
if (@.@.TRANCOUNT > 0) rollback tran
It seems that 2k was trying to check if either the database accepts transact
ions, or whether it is read-only.
Thanks,
MikeHi ,
Can you remove the transaction handling and try executing the script. What
is the reason you are using a Begin tran and Rollback tran with in a select
statement?
There is no need to rollback a select statement.
Incase if you need to do some DML (Insert , delete / update) , Then do the
transaction later in your script.
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:8CB39B05-68FC-4783-824B-17DC987E4583@.microsoft.com...

quote:

---

> I have linked a SQL Server 6.5 to a SQL Server 2000. The database I'm

---

retrieving data from is read-only (a user database, not master).

quote:

---

> When I do a select column1, column2 from [linked

---

server].[database].dbo.[table]

quote:

---

> it returns some error like
> cannot begin transaction because the database is read-only.
> What setting I can use to specify that I'm interested in just read-only

---

access, and no transactions whatsoever?

quote:

---

> P.S. I was watching the traffic with Network Monitor, and after a lot of

---

login frames and metadata retrieval, the offending statements came
(approximated from memory):

quote:

---

> set implicit_transactions on
> begin transaction
> set @.@.ROWCOUNT = 1
> select * from [the table]
> if (@.@.TRANCOUNT > 0) rollback tran
> It seems that 2k was trying to check if either the database accepts

---

transactions, or whether it is read-only.

quote:

---

> Thanks,
> Mike
>

---

|||Hari,
The only statement issued by me was
select column1, column2 from [linked server].[database].dbo.[table]
The sequence of statements where you saw transactions were generated by the
SQL Server while attempting to establish communication with the linked serve
r, and captured with Network Monitor. There is a protocol implemented intern
ally, including retrieving
metadata from the linked server. Part of this protocol is what disturbed my
attempt to read from a read-only database, because it tries something with t
ransactions, that has nothing to do with my simple select statement.
I am looking for a setting in the linked server properties to stop it trying
to do the transactions test, and stick with read-only operations.
Mike|||Hi,
Try execute the below statment before running the select statement
SET XACT_ABORT OFF
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:0286FFDD-66C4-475B-8CB4-4191BE872A35@.microsoft.com...

quote:

---

> Hari,
> The only statement issued by me was
> select column1, column2 from [linked server].[database].dbo.[table]
> The sequence of statements where you saw transactions were generated by

---

the SQL Server while attempting to establish communication with the linked
server, and captured with Network Monitor. There is a protocol implemented
internally, including retrieving metadata from the linked server. Part of
this protocol is what disturbed my attempt to read from a read-only
database, because it tries something with transactions, that has nothing to
do with my simple select statement.

quote:

---

> I am looking for a setting in the linked server properties to stop it

---

trying to do the transactions test, and stick with read-only operations.

quote:

---

> Mike
>

---

|||Hi I found a workaround. The problem was that I have set my read-only databa
se as the default catalog in the linked server properties. If I leave it bla
nk, its gonna use master as default catalog, wich is read-write.
The set xact_abort off didn't work, so I still don't know what I would have
done if the whole server was read-only, not just a database.
Mike
-- Hari wrote: --
Hi,
Try execute the below statment before running the select statement
SET XACT_ABORT OFF
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:0286FFDD-66C4-475B-8CB4-4191BE872A35@.microsoft.com...

quote:

---

> Hari,
> The only statement issued by me was
> select column1, column2 from [linked server].[database].dbo.[table]
the SQL Server while attempting to establish communication with the linked
server, and captured with Network Monitor. There is a protocol implemented
internally, including retrieving metadata from the linked server. Part of
this protocol is what disturbed my attempt to read from a read-only
database, because it tries something with transactions, that has nothing to
do with my simple select statement.[QUOTE]
trying to do the transactions test, and stick with read-only operations.[QUOTE]
>

---

cannot retrieve data from linked server with read-only database

I have linked a SQL Server 6.5 to a SQL Server 2000. The database I'm retrieving data from is read-only (a user database, not master)
When I do a select column1, column2 from [linked server].[database].dbo.[table
it returns some error like
cannot begin transaction because the database is read-only
What setting I can use to specify that I'm interested in just read-only access, and no transactions whatsoever
P.S. I was watching the traffic with Network Monitor, and after a lot of login frames and metadata retrieval, the offending statements came (approximated from memory)
set implicit_transactions o
begin transactio
set @.@.ROWCOUNT = select * from [the table
>> (here came the frame with the error response from 6.5
if (@.@.TRANCOUNT > 0) rollback tra
It seems that 2k was trying to check if either the database accepts transactions, or whether it is read-only
Thanks
MikHi ,
Can you remove the transaction handling and try executing the script. What
is the reason you are using a Begin tran and Rollback tran with in a select
statement?
There is no need to rollback a select statement.
Incase if you need to do some DML (Insert , delete / update) , Then do the
transaction later in your script.
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:8CB39B05-68FC-4783-824B-17DC987E4583@.microsoft.com...
> I have linked a SQL Server 6.5 to a SQL Server 2000. The database I'm
retrieving data from is read-only (a user database, not master).
> When I do a select column1, column2 from [linked
server].[database].dbo.[table]
> it returns some error like
> cannot begin transaction because the database is read-only.
> What setting I can use to specify that I'm interested in just read-only
access, and no transactions whatsoever?
> P.S. I was watching the traffic with Network Monitor, and after a lot of
login frames and metadata retrieval, the offending statements came
(approximated from memory):
> set implicit_transactions on
> begin transaction
> set @.@.ROWCOUNT = 1
> select * from [the table]
> >> (here came the frame with the error response from 6.5)
> if (@.@.TRANCOUNT > 0) rollback tran
> It seems that 2k was trying to check if either the database accepts
transactions, or whether it is read-only.
> Thanks,
> Mike
>|||Hari
The only statement issued by me wa
select column1, column2 from [linked server].[database].dbo.[table
The sequence of statements where you saw transactions were generated by the SQL Server while attempting to establish communication with the linked server, and captured with Network Monitor. There is a protocol implemented internally, including retrieving metadata from the linked server. Part of this protocol is what disturbed my attempt to read from a read-only database, because it tries something with transactions, that has nothing to do with my simple select statement
I am looking for a setting in the linked server properties to stop it trying to do the transactions test, and stick with read-only operations
Mik|||Hi,
Try execute the below statment before running the select statement
SET XACT_ABORT OFF
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:0286FFDD-66C4-475B-8CB4-4191BE872A35@.microsoft.com...
> Hari,
> The only statement issued by me was
> select column1, column2 from [linked server].[database].dbo.[table]
> The sequence of statements where you saw transactions were generated by
the SQL Server while attempting to establish communication with the linked
server, and captured with Network Monitor. There is a protocol implemented
internally, including retrieving metadata from the linked server. Part of
this protocol is what disturbed my attempt to read from a read-only
database, because it tries something with transactions, that has nothing to
do with my simple select statement.
> I am looking for a setting in the linked server properties to stop it
trying to do the transactions test, and stick with read-only operations.
> Mike
>|||Hi I found a workaround. The problem was that I have set my read-only database as the default catalog in the linked server properties. If I leave it blank, its gonna use master as default catalog, wich is read-write.
The set xact_abort off didn't work, so I still don't know what I would have done if the whole server was read-only, not just a database.
Mike
-- Hari wrote: --
Hi,
Try execute the below statment before running the select statement
SET XACT_ABORT OFF
Thanks
Hari
MCDBA
"Mike U." <anonymous@.discussions.microsoft.com> wrote in message
news:0286FFDD-66C4-475B-8CB4-4191BE872A35@.microsoft.com...
> Hari,
> The only statement issued by me was
> select column1, column2 from [linked server].[database].dbo.[table]
>> The sequence of statements where you saw transactions were generated by
the SQL Server while attempting to establish communication with the linked
server, and captured with Network Monitor. There is a protocol implemented
internally, including retrieving metadata from the linked server. Part of
this protocol is what disturbed my attempt to read from a read-only
database, because it tries something with transactions, that has nothing to
do with my simple select statement.
>> I am looking for a setting in the linked server properties to stop it
trying to do the transactions test, and stick with read-only operations.
>> Mike
>