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:|||Hari,
> Thanks,
> Mike
>
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:|||Hi I found a workaround. The problem was that I have set my read-only databa
> Mike
>
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]
>
No comments:
Post a Comment