Showing posts with label linked. Show all posts
Showing posts with label linked. Show all posts

Thursday, March 22, 2012

Can't Add a linked table to SQL Server 2005 with Access 2003

Hi,

I have an Access 2003 front end that contains a number of linked tables on SQL Server 2005 SE. I recreated the application on a second network for testing and was able to use the Linked Table Manager to refresh the database connections. The problem is when I try and add another linked table. I select Link Tables from the menu and then when I select 'ODBC Databases()' from the 'Files of Type' list box, the Link window closes right away.

Any suggestions?

I would post to MS Access newsgroups to get help on this, sounds like a problem with Microsoft Access ->

http://www.microsoft.com/office/community/en-us/default.mspx

My only feeble guess for you is that somehow ODBC is not installed correctly on the computer, try re-installing latest MDAC.

|||if you are running Norton AV , turn off the Office Plug in, Anti-virus/Options/Misc|||You can't modify tables in access 2003 against SQL 2005. I use access 2007 beta with good results.|||

I was having the same problem as bonkers1963 and I found this posting. I have Norton AV and the Office Plugin option was set on. I turned off the Office Plugin option and restared Access then I could link tables with no problem.

Thanks for the info.

Can't Add a linked table to SQL Server 2005 with Access 2003

Hi,

I have an Access 2003 front end that contains a number of linked tables on SQL Server 2005 SE. I recreated the application on a second network for testing and was able to use the Linked Table Manager to refresh the database connections. The problem is when I try and add another linked table. I select Link Tables from the menu and then when I select 'ODBC Databases()' from the 'Files of Type' list box, the Link window closes right away.

Any suggestions?

I would post to MS Access newsgroups to get help on this, sounds like a problem with Microsoft Access ->

http://www.microsoft.com/office/community/en-us/default.mspx

My only feeble guess for you is that somehow ODBC is not installed correctly on the computer, try re-installing latest MDAC.

|||if you are running Norton AV , turn off the Office Plug in, Anti-virus/Options/Misc|||You can't modify tables in access 2003 against SQL 2005. I use access 2007 beta with good results.|||

I was having the same problem as bonkers1963 and I found this posting. I have Norton AV and the Office Plugin option was set on. I turned off the Office Plugin option and restared Access then I could link tables with no problem.

Thanks for the info.

sql

Tuesday, March 20, 2012

Can't access OLEDB datasource

The following problem is occuring when I try to access a
table either via an oledb connection or via a linked
server
I can enter the query in the rs/text box and run it
sucessfully. If I either goto edit the query via the
query tool or select the data option. Visual Studio
crashes.
This scenario reproduces the error
select * from link...table, however
this scenario works
select * from openquery(link,'select * from table')
I have downloaded sp1 for reporting services
thanks
DanAre you using the generic query designer, or are you using the graphical
query desinger?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:b92401c479a8$2a760220$a601280a@.phx.gbl...
> The following problem is occuring when I try to access a
> table either via an oledb connection or via a linked
> server
> I can enter the query in the rs/text box and run it
> sucessfully. If I either goto edit the query via the
> query tool or select the data option. Visual Studio
> crashes.
> This scenario reproduces the error
> select * from link...table, however
> this scenario works
> select * from openquery(link,'select * from table')
> I have downloaded sp1 for reporting services
> thanks
> Dan|||Thank you for the response, I can type in a query in the
generic designed, however if I select edit which is
supposed to launch the graphic tool, it crashes. This is
the same behavior whether I use the oledb interface or
try to access the same table via a linked-server
Regards
>--Original Message--
>Are you using the generic query designer, or are you
using the graphical
>query desinger?
>-Lukasz
>
>--
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:b92401c479a8$2a760220$a601280a@.phx.gbl...
>> The following problem is occuring when I try to access
a
>> table either via an oledb connection or via a linked
>> server
>> I can enter the query in the rs/text box and run it
>> sucessfully. If I either goto edit the query via the
>> query tool or select the data option. Visual Studio
>> crashes.
>> This scenario reproduces the error
>> select * from link...table, however
>> this scenario works
>> select * from openquery(link,'select * from table')
>> I have downloaded sp1 for reporting services
>> thanks
>> Dan
>
>.
>|||Unfortunately, the graphical query editor is a component of Visual Studio
which we reuse. If you send me your query, I can file a bug on their side.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"dan" <anonymous@.discussions.microsoft.com> wrote in message
news:befe01c479d1$f409b860$a501280a@.phx.gbl...
> Thank you for the response, I can type in a query in the
> generic designed, however if I select edit which is
> supposed to launch the graphic tool, it crashes. This is
> the same behavior whether I use the oledb interface or
> try to access the same table via a linked-server
> Regards
>>--Original Message--
>>Are you using the generic query designer, or are you
> using the graphical
>>query desinger?
>>-Lukasz
>>
>>--
>>This posting is provided "AS IS" with no warranties, and
> confers no rights.
>>
>>"Dan" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:b92401c479a8$2a760220$a601280a@.phx.gbl...
>> The following problem is occuring when I try to access
> a
>> table either via an oledb connection or via a linked
>> server
>> I can enter the query in the rs/text box and run it
>> sucessfully. If I either goto edit the query via the
>> query tool or select the data option. Visual Studio
>> crashes.
>> This scenario reproduces the error
>> select * from link...table, however
>> this scenario works
>> select * from openquery(link,'select * from table')
>> I have downloaded sp1 for reporting services
>> thanks
>> Dan
>>
>>.|||Thanks
Here is the example: SELECT * FROM NSXLINK...KNA1
NSXLINK is an OLEDB compliant linked server kna1 is the
table. If you select edit you get the error.
If however you submit the following query
SELECT * FROM OPENQUERY(NSXLINK,'SELECT * FROM NSXLINK')
it works. The same problem will appear if you use the
OLEDB provider directly, without the linked server
If you want I can get the error from the program that is
generated
thanks again
dan
>--Original Message--
>Unfortunately, the graphical query editor is a component
of Visual Studio
>which we reuse. If you send me your query, I can file
a bug on their side.
>-Lukasz
>
>--
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>"dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:befe01c479d1$f409b860$a501280a@.phx.gbl...
>> Thank you for the response, I can type in a query in
the
>> generic designed, however if I select edit which is
>> supposed to launch the graphic tool, it crashes. This
is
>> the same behavior whether I use the oledb interface or
>> try to access the same table via a linked-server
>> Regards
>>--Original Message--
>>Are you using the generic query designer, or are you
>> using the graphical
>>query desinger?
>>-Lukasz
>>
>>--
>>This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>>
>>"Dan" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:b92401c479a8$2a760220$a601280a@.phx.gbl...
>> The following problem is occuring when I try to
access
>> a
>> table either via an oledb connection or via a linked
>> server
>> I can enter the query in the rs/text box and run it
>> sucessfully. If I either goto edit the query via the
>> query tool or select the data option. Visual Studio
>> crashes.
>> This scenario reproduces the error
>> select * from link...table, however
>> this scenario works
>> select * from openquery(link,'select * from table')
>> I have downloaded sp1 for reporting services
>> thanks
>> Dan
>>
>>.
>
>.
>

Can't access linked server, except from

Hi, (excuse my poor english).
I want to access data stored on a SQL2000 server using
"SELECT * FROM openquery(SQL2000srv, ..." from a
connection on my SQL2005 server.
I checked "Allow remote connections" on both servers, and
configured Nammed pipes as prefered protocol.
Security is Windows authentification only for both servers.
If I test my query on Management studio on my 2005 server,
everything is OK. But when I test from any other machine,
I have this error : (sorry, I translated it from french :
OLE DB 'SQLNCLI' from linked server returns 'Connection timeout'.
Msg 5, level 16, state 1, Line 0
Nammed pipes : can't open connection to SQL Server [5]. ".).
I can connect to both servers using any connection, so it's not
a user security problem, I think.
Do you have any clues ?
Thank you,
Jid.Hi Jide,
you have to ensure that Named Pipes is turned on as a Client Protocol.
You do this by opening up SQL Server Configuration Manager from the Sql
Server program group.
Underneath the SQL Native Client Configuration you find the Client Protocols
- here the Named Pipes protocol have to be enabled...
That may be the reason - at least its disabled at default.
Regards,
Mogens Nielsen
WM-data Danmark
"jidehem" wrote:

> Hi, (excuse my poor english).
> I want to access data stored on a SQL2000 server using
> "SELECT * FROM openquery(SQL2000srv, ..." from a
> connection on my SQL2005 server.
> I checked "Allow remote connections" on both servers, and
> configured Nammed pipes as prefered protocol.
> Security is Windows authentification only for both servers.
> If I test my query on Management studio on my 2005 server,
> everything is OK. But when I test from any other machine,
> I have this error : (sorry, I translated it from french :
> OLE DB 'SQLNCLI' from linked server returns 'Connection timeout'.
> Msg 5, level 16, state 1, Line 0
> Nammed pipes : can't open connection to SQL Server [5]. ".).
> I can connect to both servers using any connection, so it's not
> a user security problem, I think.
> Do you have any clues ?
> Thank you,
> Jidé.
>
>|||Mogens Nielsen - WM-Data A/S wrote:
> Hi Jide,
> you have to ensure that Named Pipes is turned on as a Client Protocol.
> You do this by opening up SQL Server Configuration Manager from the
> Sql Server program group.
> Underneath the SQL Native Client Configuration you find the Client
> Protocols - here the Named Pipes protocol have to be enabled...
> That may be the reason - at least its disabled at default.
Nammed pipes was already my prefered protocol for both servers
and in Client configuration utility. But thank you for your answer.
Another idea ?
Jid.|||Thought it was the simple problem like not turning named pipes on. I don't
think I've got any other good ideas...
Well, you could try connect by using an IP-adress instead of using the
server name, but then again - you shouldn't be able to connect through the
enterprise manager.
I think this is not of much help...
Maybe someone else has a clue !?
"jidehem" wrote:

> Mogens Nielsen - WM-Data A/S wrote:
> Nammed pipes was already my prefered protocol for both servers
> and in Client configuration utility. But thank you for your answer.
> Another idea ?
> Jidé.
>
>|||Mogens Nielsen - WM-Data A/S wrote:
> I think this is not of much help...
> Maybe someone else has a clue !?
Thank you again for your answers. I found the problem :
it's was "double hop" authentification problem.
In active directory, you need to activate delegation for
the user of the account of the SQL Server service, and for
the computer.
I have manually registred with setspn my linked server:
setspn -A MSSQLSvc/server.mydomaine.local:1433 account
(and not :
setspn -A MSSQLSvc/server:1433 account
nor
setspn -A MSSQLSvc/server:1433 mydomaine\account).
Now, all is OK.
Jid.

Can't access linked server through Query Analyzer

Hi, i created a linked server and can see the tables shown in Enterprise Manager. When I log into Query Analyzer, I try to access the remote tables with the 4 part naming convention and i get
SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
Servidor: mensaje 208, nivel 16, estado 1, lXnea 1
El nombre de objeto 'nodo_sp.bts.caso' no es válido.
which means "Object name is invalid"
I've tried changing its case, login through windows authentication and sqlserver authentication, but I get the same problem.
I created a linked server "backwards", that is from server B to A, and it works fine. Other servers have a linked server to this same server B (PGJE_SP_06) and they work fine.
Thanks for your help
Israel,
1. Did you try selecting "Tools --> Query Analyzer" from the Enterprise Manager -- this should open the QA connection with the same credentials with which the Enterprise Manager is running.
2. Did you confirm that your FROM clause does not have any typos? (e.g., Is the linked server name correct? Did you check on your server "B" that "SELECT COUNT(*) FROM nodo_sp.bts.caso").
3. Did you try to use the OPENQUERY (LinkedServerName, 'Select count(*) from Table') syntax?
4. Can you double check your linked server settings match the settings (e.g. Provider) that you have on the servers which are able to execute the query successfully.
Thanks,
Tea C.
"Israel Perez" wrote:

> Hi, i created a linked server and can see the tables shown in Enterprise Manager. When I log into Query Analyzer, I try to access the remote tables with the 4 part naming convention and i get
> SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
> Servidor: mensaje 208, nivel 16, estado 1, lXnea 1
> El nombre de objeto 'nodo_sp.bts.caso' no es válido.
> which means "Object name is invalid"
> I've tried changing its case, login through windows authentication and sqlserver authentication, but I get the same problem.
> I created a linked server "backwards", that is from server B to A, and it works fine. Other servers have a linked server to this same server B (PGJE_SP_06) and they work fine.
> Thanks for your help

Can't access linked server through Query Analyzer

Hi, i created a linked server and can see the tables shown in Enterprise Man
ager. When I log into Query Analyzer, I try to access the remote tables with
the 4 part naming convention and i get
SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
Servidor: mensaje 208, nivel 16, estado 1, l_nea 1
El nombre de objeto 'nodo_sp.bts.caso' no es válido.
which means "Object name is invalid"
I've tried changing its case, login through windows authentication and sqlse
rver authentication, but I get the same problem.
I created a linked server "backwards", that is from server B to A, and it wo
rks fine. Other servers have a linked server to this same server B (PGJE_SP_
06) and they work fine.
Thanks for your helpIsrael,
1. Did you try selecting "Tools --> Query Analyzer" from the Enterprise Mana
ger -- this should open the QA connection with the same credentials with wh
ich the Enterprise Manager is running.
2. Did you confirm that your FROM clause does not have any typos? (e.g., Is
the linked server name correct? Did you check on your server "B" that "SELE
CT COUNT(*) FROM nodo_sp.bts.caso").
3. Did you try to use the OPENQUERY (LinkedServerName, 'Select count(*) from
Table') syntax?
4. Can you double check your linked server settings match the settings (e.g.
Provider) that you have on the servers which are able to execute the query
successfully.
Thanks,
Tea C.
"Israel Perez" wrote:

> Hi, i created a linked server and can see the tables shown in Enterprise M
anager. When I log into Query Analyzer, I try to access the remote tables wi
th the 4 part naming convention and i get
> SELECT COUNT(*) FROM PGJE_06_sp.nodo_sp.bts.caso
> Servidor: mensaje 208, nivel 16, estado 1, l_nea 1
> El nombre de objeto 'nodo_sp.bts.caso' no es válido.
> which means "Object name is invalid"
> I've tried changing its case, login through windows authentication and sql
server authentication, but I get the same problem.
> I created a linked server "backwards", that is from server B to A, and it
works fine. Other servers have a linked server to this same server B (PGJE_S
P_06) and they work fine.
> Thanks for your helpsql

Thursday, March 8, 2012

Cannot update ID because of no existing relationship

I have one table Phone and a table SmsMessage that are linked by the Cellnumber. Cellnumber is the primary key in Phone.

For some reason in the table Phone the Cellnumbers are stored with extra spaces like: '+27000000000 ', but in the SmsMessage table the same value is stored as '+27000000000'. However when I want perform an update to trim the Cellnumbers, I get the message 'Cannot modify values Cellnumber in Phone because there are dependent values in SmsMessage.

The fact is that there are no dependent values, but for MSSQL '+27000000000' and '+27000000000 ' is the same??! Note that the function Len(Cellnumber) gives me the length of the string WITHOUT the spaces as well.

Even if I remove all relationships from Phone, I still get the same error. Are there more places in MSSQL where relationships are stored besides the Diagrams?

Or is there a command that tells MSSQL to ignore all relationships for the next query?

Any ideas?I suspect that the data is stored in the column without the trailing spaces, although it may be presented with spaces added. This is common when retrieving data in columnar form. To test this, retrieve an expression rather than the raw column to see what you actually get, something like:SELECT myColumn, '[' + myColumn + ']'
FROM myTableThe real relationship information is stored in system tables in the form of constraints. The graphic representation is easier to understand, but the constraints are the "final answer" in terms of the relationships that MS-SQL enforces.

-PatP

Cannot update

Hi,
I have the following update statement but using a linked server
Update txq005dev1.sprint.dbo.becinfo set unit = a.unit from becinfo a inner
join txq005dev1.sprint.dbo.becinfo b on a.HRGType = b.HRGType and
a.ChargSubType = b.ChargSubType
I am not sure why it doesn't work. Can I not use Linked server in an Update
Statement?
Thanks
Ed
The following is an error message:
Server: Msg 7306, Level 16, State 2, Line 2
Could not open table '"sprint"."dbo"."becinfo"' from OLE DB provider
'SQLOLEDB'. The provider could not support a row lookup position. The
provider indicates that conflicts occurred with other properties or
requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...What product are you linking to?
ML|||Ed,
Does the LS table have a PK or UNIQUE constraint? If not, might try adding
one and then trying again.
HTH
Jerry
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:154D0BE7-5EDC-4897-A9BD-C7903ABF1165@.microsoft.com...
> Hi,
> I have the following update statement but using a linked server
> Update txq005dev1.sprint.dbo.becinfo set unit = a.unit from becinfo a
> inner
> join txq005dev1.sprint.dbo.becinfo b on a.HRGType = b.HRGType and
> a.ChargSubType = b.ChargSubType
> I am not sure why it doesn't work. Can I not use Linked server in an
> Update
> Statement?
> Thanks
> Ed
> The following is an error message:
> Server: Msg 7306, Level 16, State 2, Line 2
> Could not open table '"sprint"."dbo"."becinfo"' from OLE DB provider
> 'SQLOLEDB'. The provider could not support a row lookup position. The
> provider indicates that conflicts occurred with other properties or
> requirements.
> [OLE/DB provider returned message: Multiple-step OLE DB operation
> generated
> errors. Check each OLE DB status value, if available. No work was done.]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset
> returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600
> STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True
> STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True
> STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
>|||Try this
Update txq005dev1.sprint.dbo.becinfo set unit = (SELECT unit from becinfo a
inner join txq005dev1.sprint.dbo.becinfo b on a.HRGType = b.HRGType and
a.ChargSubType = b.ChargSubType)
if you are sure that inner query results only one value
--
Regards
R.D
--Knowledge gets doubled when shared
"Jerry Spivey" wrote:

> Ed,
> Does the LS table have a PK or UNIQUE constraint? If not, might try addin
g
> one and then trying again.
> HTH
> Jerry
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:154D0BE7-5EDC-4897-A9BD-C7903ABF1165@.microsoft.com...
>
>

Tuesday, February 14, 2012

Cannot see data from SYS.CONVERSATION_ENDPOINTS over a linked server

Hello,

I have a server that is used to query other servers to ensure that things are functioning correctly. On some of our servers we are running ServiceBroker and some of the monitoring involves querying the SYS.CONVERSATION_ENPOINTS view on these servers.

When I query the SYS.CONVERSATION_ENPOINTS view over a linked server it returns zero rows, though when I run the same query locally it returns data. I initially thought it was a permission issue but it seems I can see data across the linked server to other system views within the sys schema (ie sys.all_objects).

Is there something different/special about SYS.CONVERSATION_ENDPOINTS that prevents me from seeing its data across linked servers or have I simply got permission problems.

Thanks

Ian

It probably is a permissions issue. sys.conversation_endpoints permissions are governed by 'RECEIVE' permission on the queue of the service the convresation endpoint belongs to.

Sunday, February 12, 2012

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
>