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
>
