does linked server solve performance issue??

Hi,
I've this query using four tables of 3 different databases residing on
the same server
select top 5 * from dblezen.dbo.ads ta
left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
In the near future we'll propably put the 3 databases on 3 different
physical servers. So I'll have to create linked servers, meaning I've
got to execute something like this :
select top 5 * from server1.dblezen.dbo.ads ta
left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
First question:
Can I fix that the servernames server1, server2 and server3 are logical
names (different than the computername) all refering to the same local
server?
If this is possible I guess I can adapt all my stored procedures before
effectively put the 3 databases on 3 different servers, and if
necessary, I can go back to 1 server again afterwards, without having to
change my stored procedures again.
Second question:
If it can be done, will it have consequences on the execution time of
the queries? Will there be overhead 1) because SQL server is going to
use distributed transaction instead of local transactions or 2) because
SQL server has to translate the logical server names into physical
server names while it's actually not necessary when the databases
reside on the same server.
Third question:
Is moving the 3 databases to 3 different servers and start using linked
server the obvious best option to resolve the performance problem of our
database server?
At the moment we have one IIS-server, running ASP.NET and one database
server, running SQL Server, using 3 databases.
- dbingeven is mainly used to insert new rows
- dblezen is mainly used to read rows (full text indexed)
- dbalgemeen contains general data used by the other two (user data,
parameters, statistical data, ...)
Data is continuously inserted in dbingeven and continuously copied
(after processing) to dblezen.
All 3 databases contain stored procedures refering each other all the
time (joins as in the query above as well as calling each others stored
procedures).
Fourth question:
Is it predictable the gain of performance win (on CPU, and disk access)
by spreading the data will be lost on network traffic and distributed
transactions processes, meaning our problem will not be really solved?
Thanks in Advance,
Peter Van Wilrijk.
Why don't you replicate the data back to the server that you're query is run
on, and select from there?
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:N_Vte.126388$I62.7018547@.phobos.telenet-ops.be...
> Hi,
> I've this query using four tables of 3 different databases residing on the
> same server
> select top 5 * from dblezen.dbo.ads ta
> left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
> left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> In the near future we'll propably put the 3 databases on 3 different
> physical servers. So I'll have to create linked servers, meaning I've
> got to execute something like this :
> select top 5 * from server1.dblezen.dbo.ads ta
> left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid =
> tb.usr_id
> left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
> td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> First question:
> Can I fix that the servernames server1, server2 and server3 are logical
> names (different than the computername) all refering to the same local
> server?
> If this is possible I guess I can adapt all my stored procedures before
> effectively put the 3 databases on 3 different servers, and if necessary,
> I can go back to 1 server again afterwards, without having to change my
> stored procedures again.
> Second question:
> If it can be done, will it have consequences on the execution time of the
> queries? Will there be overhead 1) because SQL server is going to use
> distributed transaction instead of local transactions or 2) because SQL
> server has to translate the logical server names into physical server
> names while it's actually not necessary when the databases
> reside on the same server.
> Third question:
> Is moving the 3 databases to 3 different servers and start using linked
> server the obvious best option to resolve the performance problem of our
> database server?
> At the moment we have one IIS-server, running ASP.NET and one database
> server, running SQL Server, using 3 databases.
> - dbingeven is mainly used to insert new rows
> - dblezen is mainly used to read rows (full text indexed)
> - dbalgemeen contains general data used by the other two (user data,
> parameters, statistical data, ...)
> Data is continuously inserted in dbingeven and continuously copied (after
> processing) to dblezen.
> All 3 databases contain stored procedures refering each other all the time
> (joins as in the query above as well as calling each others stored
> procedures).
> Fourth question:
> Is it predictable the gain of performance win (on CPU, and disk access)
> by spreading the data will be lost on network traffic and distributed
> transactions processes, meaning our problem will not be really solved?
>
> Thanks in Advance,
> Peter Van Wilrijk.
>
>
>
>
>
|||ChrisR wrote:
> Why don't you replicate the data back to the server that you're query is run
> on, and select from there?
>
Thanks, Good question?
I surely must start checking out how to implement replication ... but I
guess it will not be an option for our website, because our users, while
surfing ... read, insert and update data in all three databases. I
guess this means we should replicate continuously in two directions, so
updates, deletes and inserts on server 2 must be immediately available
on server 1 and vice versa. Can replication do that?
Kind regards,
Peter Roothans.
|||Yes. Look up Transactional Replication in BOL. You will want to use the
Immediate Updating option.
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
> ChrisR wrote:
> Thanks, Good question?
> I surely must start checking out how to implement replication ... but I
> guess it will not be an option for our website, because our users, while
> surfing ... read, insert and update data in all three databases. I guess
> this means we should replicate continuously in two directions, so updates,
> deletes and inserts on server 2 must be immediately available on server 1
> and vice versa. Can replication do that?
> Kind regards,
> Peter Roothans.
>
|||Thanks ChrisR.
I just found out you can give 1 server multiple names as follows.
sp_addlinkedserver N'SRVDBI', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBL', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBA', ' ', N'SQLOLEDB', N'SRV-WEBDB'
So, this way I can move my database to any server without the need to
adapt all code. The only thing to do than, is to let refer the logical
name to another physical server.
I've read BOL concerning Transactional Replication. I'm certainly going
to try it out, but since I'm not familiair with it and since the
document warns for loopback detection when replicating multiple related
databases, I'll start with the linked server solution.
Thanks,
Kind regards,
Peter Van Wilrijk
ChrisR wrote:
> Yes. Look up Transactional Replication in BOL. You will want to use the
> Immediate Updating option.
>
> "Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
> news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
>
>

does linked server solve performance issue??

Hi,
I've this query using four tables of 3 different databases residing on
the same server
select top 5 * from dblezen.dbo.ads ta
left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
In the near future we'll propably put the 3 databases on 3 different
physical servers. So I'll have to create linked servers, meaning I've
got to execute something like this :
select top 5 * from server1.dblezen.dbo.ads ta
left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
First question:
Can I fix that the servernames server1, server2 and server3 are logical
names (different than the computername) all refering to the same local
server?
If this is possible I guess I can adapt all my stored procedures before
effectively put the 3 databases on 3 different servers, and if
necessary, I can go back to 1 server again afterwards, without having to
change my stored procedures again.
Second question:
If it can be done, will it have consequences on the execution time of
the queries? Will there be overhead 1) because SQL server is going to
use distributed transaction instead of local transactions or 2) because
SQL server has to translate the logical server names into physical
server names while it's actually not necessary when the databases
reside on the same server.
Third question:
Is moving the 3 databases to 3 different servers and start using linked
server the obvious best option to resolve the performance problem of our
database server'
At the moment we have one IIS-server, running ASP.NET and one database
server, running SQL Server, using 3 databases.
- dbingeven is mainly used to insert new rows
- dblezen is mainly used to read rows (full text indexed)
- dbalgemeen contains general data used by the other two (user data,
parameters, statistical data, ...)
Data is continuously inserted in dbingeven and continuously copied
(after processing) to dblezen.
All 3 databases contain stored procedures refering each other all the
time (joins as in the query above as well as calling each others stored
procedures).
Fourth question:
Is it predictable the gain of performance win (on CPU, and disk access)
by spreading the data will be lost on network traffic and distributed
transactions processes, meaning our problem will not be really solved'
Thanks in Advance,
Peter Van Wilrijk.Why don't you replicate the data back to the server that you're query is run
on, and select from there?
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:N_Vte.126388$I62.7018547@.phobos.telenet-ops.be...
> Hi,
> I've this query using four tables of 3 different databases residing on the
> same server
> select top 5 * from dblezen.dbo.ads ta
> left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
> left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> In the near future we'll propably put the 3 databases on 3 different
> physical servers. So I'll have to create linked servers, meaning I've
> got to execute something like this :
> select top 5 * from server1.dblezen.dbo.ads ta
> left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid =
> tb.usr_id
> left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid =
> td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> First question:
> Can I fix that the servernames server1, server2 and server3 are logical
> names (different than the computername) all refering to the same local
> server?
> If this is possible I guess I can adapt all my stored procedures before
> effectively put the 3 databases on 3 different servers, and if necessary,
> I can go back to 1 server again afterwards, without having to change my
> stored procedures again.
> Second question:
> If it can be done, will it have consequences on the execution time of the
> queries? Will there be overhead 1) because SQL server is going to use
> distributed transaction instead of local transactions or 2) because SQL
> server has to translate the logical server names into physical server
> names while it's actually not necessary when the databases
> reside on the same server.
> Third question:
> Is moving the 3 databases to 3 different servers and start using linked
> server the obvious best option to resolve the performance problem of our
> database server'
> At the moment we have one IIS-server, running ASP.NET and one database
> server, running SQL Server, using 3 databases.
> - dbingeven is mainly used to insert new rows
> - dblezen is mainly used to read rows (full text indexed)
> - dbalgemeen contains general data used by the other two (user data,
> parameters, statistical data, ...)
> Data is continuously inserted in dbingeven and continuously copied (after
> processing) to dblezen.
> All 3 databases contain stored procedures refering each other all the time
> (joins as in the query above as well as calling each others stored
> procedures).
> Fourth question:
> Is it predictable the gain of performance win (on CPU, and disk access)
> by spreading the data will be lost on network traffic and distributed
> transactions processes, meaning our problem will not be really solved'
>
> Thanks in Advance,
> Peter Van Wilrijk.
>
>
>
>
>|||ChrisR wrote:
> Why don't you replicate the data back to the server that you're query is r
un
> on, and select from there?
>
Thanks, Good question?
I surely must start checking out how to implement replication ... but I
guess it will not be an option for our website, because our users, while
surfing ... read, insert and update data in all three databases. I
guess this means we should replicate continuously in two directions, so
updates, deletes and inserts on server 2 must be immediately available
on server 1 and vice versa. Can replication do that?
Kind regards,
Peter Roothans.|||Yes. Look up Transactional Replication in BOL. You will want to use the
Immediate Updating option.
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
> ChrisR wrote:
> Thanks, Good question?
> I surely must start checking out how to implement replication ... but I
> guess it will not be an option for our website, because our users, while
> surfing ... read, insert and update data in all three databases. I guess
> this means we should replicate continuously in two directions, so updates,
> deletes and inserts on server 2 must be immediately available on server 1
> and vice versa. Can replication do that?
> Kind regards,
> Peter Roothans.
>|||Thanks ChrisR.
I just found out you can give 1 server multiple names as follows.
sp_addlinkedserver N'SRVDBI', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBL', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBA', ' ', N'SQLOLEDB', N'SRV-WEBDB'
So, this way I can move my database to any server without the need to
adapt all code. The only thing to do than, is to let refer the logical
name to another physical server.
I've read BOL concerning Transactional Replication. I'm certainly going
to try it out, but since I'm not familiair with it and since the
document warns for loopback detection when replicating multiple related
databases, I'll start with the linked server solution.
Thanks,
Kind regards,
Peter Van Wilrijk
ChrisR wrote:
> Yes. Look up Transactional Replication in BOL. You will want to use the
> Immediate Updating option.
>
> "Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
> news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
>
>
>

does linked server solve performance issue??

Hi,
I've this query using four tables of 3 different databases residing on
the same server
select top 5 * from dblezen.dbo.ads ta
left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
In the near future we'll propably put the 3 databases on 3 different
physical servers. So I'll have to create linked servers, meaning I've
got to execute something like this :
select top 5 * from server1.dblezen.dbo.ads ta
left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
where ta.ads_boonline > dateadd(n, -100, getdate())
First question:
Can I fix that the servernames server1, server2 and server3 are logical
names (different than the computername) all refering to the same local
server?
If this is possible I guess I can adapt all my stored procedures before
effectively put the 3 databases on 3 different servers, and if
necessary, I can go back to 1 server again afterwards, without having to
change my stored procedures again.
Second question:
If it can be done, will it have consequences on the execution time of
the queries? Will there be overhead 1) because SQL server is going to
use distributed transaction instead of local transactions or 2) because
SQL server has to translate the logical server names into physical
server names while it's actually not necessary when the databases
reside on the same server.
Third question:
Is moving the 3 databases to 3 different servers and start using linked
server the obvious best option to resolve the performance problem of our
database server'
At the moment we have one IIS-server, running ASP.NET and one database
server, running SQL Server, using 3 databases.
- dbingeven is mainly used to insert new rows
- dblezen is mainly used to read rows (full text indexed)
- dbalgemeen contains general data used by the other two (user data,
parameters, statistical data, ...)
Data is continuously inserted in dbingeven and continuously copied
(after processing) to dblezen.
All 3 databases contain stored procedures refering each other all the
time (joins as in the query above as well as calling each others stored
procedures).
Fourth question:
Is it predictable the gain of performance win (on CPU, and disk access)
by spreading the data will be lost on network traffic and distributed
transactions processes, meaning our problem will not be really solved'
Thanks in Advance,
Peter Van Wilrijk.Why don't you replicate the data back to the server that you're query is run
on, and select from there?
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:N_Vte.126388$I62.7018547@.phobos.telenet-ops.be...
> Hi,
> I've this query using four tables of 3 different databases residing on the
> same server
> select top 5 * from dblezen.dbo.ads ta
> left outer join dbalgemeen.dbo.users tb on ta.ads_usrid = tb.usr_id
> left outer join dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join dbingeven.dbo.edition td on tc.ads_editionid = td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> In the near future we'll propably put the 3 databases on 3 different
> physical servers. So I'll have to create linked servers, meaning I've
> got to execute something like this :
> select top 5 * from server1.dblezen.dbo.ads ta
> left outer join server2.dbalgemeen.dbo.users tb on ta.ads_usrid => tb.usr_id
> left outer join server3.dbingeven.dbo.ads tc on ta.ads_ovid = tc.ads_adid
> left outer join server3.dbingeven.dbo.edition td on tc.ads_editionid => td.ed_id
> where ta.ads_boonline > dateadd(n, -100, getdate())
> First question:
> Can I fix that the servernames server1, server2 and server3 are logical
> names (different than the computername) all refering to the same local
> server?
> If this is possible I guess I can adapt all my stored procedures before
> effectively put the 3 databases on 3 different servers, and if necessary,
> I can go back to 1 server again afterwards, without having to change my
> stored procedures again.
> Second question:
> If it can be done, will it have consequences on the execution time of the
> queries? Will there be overhead 1) because SQL server is going to use
> distributed transaction instead of local transactions or 2) because SQL
> server has to translate the logical server names into physical server
> names while it's actually not necessary when the databases
> reside on the same server.
> Third question:
> Is moving the 3 databases to 3 different servers and start using linked
> server the obvious best option to resolve the performance problem of our
> database server'
> At the moment we have one IIS-server, running ASP.NET and one database
> server, running SQL Server, using 3 databases.
> - dbingeven is mainly used to insert new rows
> - dblezen is mainly used to read rows (full text indexed)
> - dbalgemeen contains general data used by the other two (user data,
> parameters, statistical data, ...)
> Data is continuously inserted in dbingeven and continuously copied (after
> processing) to dblezen.
> All 3 databases contain stored procedures refering each other all the time
> (joins as in the query above as well as calling each others stored
> procedures).
> Fourth question:
> Is it predictable the gain of performance win (on CPU, and disk access)
> by spreading the data will be lost on network traffic and distributed
> transactions processes, meaning our problem will not be really solved'
>
> Thanks in Advance,
> Peter Van Wilrijk.
>
>
>
>
>|||ChrisR wrote:
> Why don't you replicate the data back to the server that you're query is run
> on, and select from there?
>
Thanks, Good question?
I surely must start checking out how to implement replication ... but I
guess it will not be an option for our website, because our users, while
surfing ... read, insert and update data in all three databases. I
guess this means we should replicate continuously in two directions, so
updates, deletes and inserts on server 2 must be immediately available
on server 1 and vice versa. Can replication do that?
Kind regards,
Peter Roothans.|||Yes. Look up Transactional Replication in BOL. You will want to use the
Immediate Updating option.
"Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
> ChrisR wrote:
>> Why don't you replicate the data back to the server that you're query is
>> run on, and select from there?
> Thanks, Good question?
> I surely must start checking out how to implement replication ... but I
> guess it will not be an option for our website, because our users, while
> surfing ... read, insert and update data in all three databases. I guess
> this means we should replicate continuously in two directions, so updates,
> deletes and inserts on server 2 must be immediately available on server 1
> and vice versa. Can replication do that?
> Kind regards,
> Peter Roothans.
>|||Thanks ChrisR.
I just found out you can give 1 server multiple names as follows.
sp_addlinkedserver N'SRVDBI', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBL', ' ', N'SQLOLEDB', N'SRV-WEBDB'
sp_addlinkedserver N'SRVDBA', ' ', N'SQLOLEDB', N'SRV-WEBDB'
So, this way I can move my database to any server without the need to
adapt all code. The only thing to do than, is to let refer the logical
name to another physical server.
I've read BOL concerning Transactional Replication. I'm certainly going
to try it out, but since I'm not familiair with it and since the
document warns for loopback detection when replicating multiple related
databases, I'll start with the linked server solution.
Thanks,
Kind regards,
Peter Van Wilrijk
ChrisR wrote:
> Yes. Look up Transactional Replication in BOL. You will want to use the
> Immediate Updating option.
>
> "Peter Van Wilrijk" <pro@.koopjeskrant.be> wrote in message
> news:Tu9ue.126847$Xj7.7072901@.phobos.telenet-ops.be...
>>ChrisR wrote:
>>Why don't you replicate the data back to the server that you're query is
>>run on, and select from there?
>>
>>Thanks, Good question?
>>I surely must start checking out how to implement replication ... but I
>>guess it will not be an option for our website, because our users, while
>>surfing ... read, insert and update data in all three databases. I guess
>>this means we should replicate continuously in two directions, so updates,
>>deletes and inserts on server 2 must be immediately available on server 1
>>and vice versa. Can replication do that?
>>Kind regards,
>>Peter Roothans.
>
>

Does Linked Server for MS Access support transaction?

Dear all,

We have connected a Access to a MS SQL Server 2005 as a Linked Server with the followoing settings:

1) Provider: Microsoft Jet 4.0 OLE DB Provider

2) Product Name: Access

3) Data source: X:\XXXXX.mdb

4) Provider string: ;pwd=YYYYYY;

5) Collation Compatible: False

6) Data Access: True

7) Rpc: False

8) Rpc Out: False

9) Use Remote Collation: True

10) Collation Name:

11) Connection Timeout: 0

12) Query Timeout: 0

We found that when cannot have any insert/update/delete statement for this linked server if transaction is began. Otherwise, we will have the following exception.

============

Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ZZZZZZ" does not support the required transaction interface.

===========

We wonder whether the problem is because Access does not support distributed transaction. If so, do you know any workaround will work for us?

Thanks and regards,
William

Hi William,

I have come across your post related to Transaction Interface. Now i had a similar problem while connectiong the the Paradox database using linked server. Can you please suggest me on this Please. Thanks in advance.

Rakesh

|||

You can use pass-through openquery().

e.g.

update openquery(your_access_linkedserver,'select * from tb')

set col1 = 123

...

|||

Access does not provide any transactional support. No Rollbacks or Commits. Period.

Other than eliminating Access, or resort to not making transaction calls in ANY transaction involving Access, I can think of no workaround.

Does Linked Server for MS Access support transaction?

Dear all,

We have connected a Access to a MS SQL Server 2005 as a Linked Server with the followoing settings:

1) Provider: Microsoft Jet 4.0 OLE DB Provider

2) Product Name: Access

3) Data source: X:\XXXXX.mdb

4) Provider string: ;pwd=YYYYYY;

5) Collation Compatible: False

6) Data Access: True

7) Rpc: False

8) Rpc Out: False

9) Use Remote Collation: True

10) Collation Name:

11) Connection Timeout: 0

12) Query Timeout: 0

We found that when cannot have any insert/update/delete statement for this linked server if transaction is began. Otherwise, we will have the following exception.

============

Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ZZZZZZ" does not support the required transaction interface.

===========

We wonder whether the problem is because Access does not support distributed transaction. If so, do you know any workaround will work for us?

Thanks and regards,
William

Hi William,

I have come across your post related to Transaction Interface. Now i had a similar problem while connectiong the the Paradox database using linked server. Can you please suggest me on this Please. Thanks in advance.

Rakesh

|||

You can use pass-through openquery().

e.g.

update openquery(your_access_linkedserver,'select * from tb')

set col1 = 123

...

|||

Access does not provide any transactional support. No Rollbacks or Commits. Period.

Other than eliminating Access, or resort to not making transaction calls in ANY transaction involving Access, I can think of no workaround.

Does Linked Server for MS Access support transaction?

Dear all,

We have connected a Access to a MS SQL Server 2005 as a Linked Server with the followoing settings:

1) Provider: Microsoft Jet 4.0 OLE DB Provider

2) Product Name: Access

3) Data source: X:\XXXXX.mdb

4) Provider string: ;pwd=YYYYYY;

5) Collation Compatible: False

6) Data Access: True

7) Rpc: False

8) Rpc Out: False

9) Use Remote Collation: True

10) Collation Name:

11) Connection Timeout: 0

12) Query Timeout: 0

We found that when cannot have any insert/update/delete statement for this linked server if transaction is began. Otherwise, we will have the following exception.

============

Msg 7390, Level 16, State 2, Line 1
The requested operation could not be performed because OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ZZZZZZ" does not support the required transaction interface.

===========

We wonder whether the problem is because Access does not support distributed transaction. If so, do you know any workaround will work for us?

Thanks and regards,
William

Hi William,

I have come across your post related to Transaction Interface. Now i had a similar problem while connectiong the the Paradox database using linked server. Can you please suggest me on this Please. Thanks in advance.

Rakesh

|||

You can use pass-through openquery().

e.g.

update openquery(your_access_linkedserver,'select * from tb')

set col1 = 123

...

|||

Access does not provide any transactional support. No Rollbacks or Commits. Period.

Other than eliminating Access, or resort to not making transaction calls in ANY transaction involving Access, I can think of no workaround.

Does DTC needed to do linked server queries

I have a server B registered as a linked server on Server A and have DTC off
on both servers.
I could still run a query from Server A as
select * from serverB.db.dbo.tablename
So what is DTC used for then ?Hassan,
A select does not involve a transaction between the two servers so DTC is
not required. If you were to modify something you would need it.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23kZqbgzbDHA.616@.TK2MSFTNGP11.phx.gbl...
> I have a server B registered as a linked server on Server A and have DTC
off
> on both servers.
> I could still run a query from Server A as
> select * from serverB.db.dbo.tablename
> So what is DTC used for then ?
>
>|||Try doing something that involves both servers. DTC is for distributed
transactions. That means where the changes are distribute across both
servers but must be contained in one transaction. Take a look at MS DTC in
BooksOnLine for lots of details.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uIkA1wzbDHA.1128@.tk2msftngp13.phx.gbl...
> Well i did run an update such as
> update serverB.db.dbo.tablename
> set col2 ='XYZ'
> where col1='ABC'
> It ran successfully and had the DTCs off on both sides.Can you give me an
> example of what i need to run so that I need to have DTC running ?
> I am using SQL 2000 btw
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O1NMRozbDHA.3444@.tk2msftngp13.phx.gbl...
> > Hassan,
> >
> > A select does not involve a transaction between the two servers so DTC
is
> > not required. If you were to modify something you would need it.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:%23kZqbgzbDHA.616@.TK2MSFTNGP11.phx.gbl...
> > > I have a server B registered as a linked server on Server A and have
DTC
> > off
> > > on both servers.
> > > I could still run a query from Server A as
> > > select * from serverB.db.dbo.tablename
> > >
> > > So what is DTC used for then ?
> > >
> > >
> > >
> >
> >
>

Dodgy Results AS400 linked server

Hi
I have an AS400 linked server in SQL Server 2000. Using IBM's iSeries Access
ODBC Driver (filename : CWBODBC.DLL, Ver 9.00.08.00).
When I run a query in query analyser it does not return all of the records.
I am getting "Error converting data type DBTYPE_DBTIMESTAMP to datetime."
It definitely is not returning all of rows as SELECT COUNT(*) FROM ...
reveals that there are more records. Whereas SELECT * ... returns only a
subset of the data and reports the error above.
If I omit the date field I get all of the rows. i.e. SELECT ColA as
NonDateField FROM ...
Also if I select just the datefield, I get more rows i.e. SELECT ColB as
ThisIsTheDateField FROM ... I get more rows than SELECT * FROM ... but not
all of them.
Whats going on?!!!
It almost seems as though there is some sort of buffer/bytes limit on the
data that it can return.
Do you guys who have a linked AS400 get all of your required data ?
Sorry this bit in my previous post ;
"Also if I select just the datefield, I get more rows i.e. SELECT ColB as
ThisIsTheDateField FROM ... I get more rows than SELECT * FROM ... but not
all of them. "
is incorrect please ignore (I get the same number of rows)
|||Interfacing with an AS400 can work and be stable but you
need the right driver or provider, need to keep it current,
need to keep up on any necessary service packs for it, etc.
I used Client Access and the HIT software providers for
AS400s before. HIT providers were good and we didn't run
into many problems(if any really) with their drivers or
providers. IBMs drivers weren't as stable.
What you are hitting isn't likely to be a SQL Server issue -
more likely related to the driver or the client setup,
dependent files for the driver.
-Sue
On Wed, 2 May 2007 07:18:01 -0700, Jane
<Jane@.discussions.microsoft.com> wrote:

>Sorry this bit in my previous post ;
>"Also if I select just the datefield, I get more rows i.e. SELECT ColB as
>ThisIsTheDateField FROM ... I get more rows than SELECT * FROM ... but not
>all of them. "
>is incorrect please ignore (I get the same number of rows)

Dodgy Results AS400 linked server

Hi
I have an AS400 linked server in SQL Server 2000. Using IBM's iSeries Access
ODBC Driver (filename : CWBODBC.DLL, Ver 9.00.08.00).
When I run a query in query analyser it does not return all of the records.
I am getting "Error converting data type DBTYPE_DBTIMESTAMP to datetime."
It definitely is not returning all of rows as SELECT COUNT(*) FROM ...
reveals that there are more records. Whereas SELECT * ... returns only a
subset of the data and reports the error above.
If I omit the date field I get all of the rows. i.e. SELECT ColA as
NonDateField FROM ...
Also if I select just the datefield, I get more rows i.e. SELECT ColB as
ThisIsTheDateField FROM ... I get more rows than SELECT * FROM ... but not
all of them.
Whats going on'!!!
It almost seems as though there is some sort of buffer/bytes limit on the
data that it can return.
Do you guys who have a linked AS400 get all of your required data ?Sorry this bit in my previous post ;
"Also if I select just the datefield, I get more rows i.e. SELECT ColB as
ThisIsTheDateField FROM ... I get more rows than SELECT * FROM ... but not
all of them. "
is incorrect please ignore (I get the same number of rows)|||Interfacing with an AS400 can work and be stable but you
need the right driver or provider, need to keep it current,
need to keep up on any necessary service packs for it, etc.
I used Client Access and the HIT software providers for
AS400s before. HIT providers were good and we didn't run
into many problems(if any really) with their drivers or
providers. IBMs drivers weren't as stable.
What you are hitting isn't likely to be a SQL Server issue -
more likely related to the driver or the client setup,
dependent files for the driver.
-Sue
On Wed, 2 May 2007 07:18:01 -0700, Jane
<Jane@.discussions.microsoft.com> wrote:

>Sorry this bit in my previous post ;
>"Also if I select just the datefield, I get more rows i.e. SELECT ColB as
>ThisIsTheDateField FROM ... I get more rows than SELECT * FROM ... but no
t
>all of them. "
>is incorrect please ignore (I get the same number of rows)