table ; linked server : read only

[Korsakow]

I work on an MS Access project ( *.apd - file) that is linked with a
SQL-express server 2005 (local installed). On this server are 2 databases x
and y.
I linked some tables of database x to y and here's the problem: the linked
tables are read-only.
My authentication for the server is Windows authentication and I'm
administrator.
I say this because I think the problem is a security problem.
Can somebody help me to make the linked tables are not read-only? What is
the problem, what did I wrong?

Thanks a lot!

 

[Tom van Stiphout]

On Sat, 12 Apr 2008 13:52:30 +0200, "Korsakow" <[email protected]>
wrote:

If you connect with an account that has readonly access to the db.
then yes, it's a security issue. Of course if you use Management
Studio with the same account you will have the same readonly
experience. If not, then that's not the problem.

Often it is the lack of Primary Keys. Access has a hard time working
with tables without PK. After all when you edit a row, how would it
know which row to update? 99% of the time that points to bad database
design anyway, so it should be fixed immediately.

-Tom.

 

[Korsakow]

I'm logged in as administrator and i have all rights, also in management
studio. Als my tables has a PK. I can change the records if I'm connected
directly to the database, but the table is read-only if I open the other
database where the same table is linked to .

 

[Korsakow]

If I try to change data in the linked table in Microsoft SQL Server
Management Studio Express, I get the follow error: "No row was updated. The
data in row 22 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: MSDTC on server 'JUMA_SERVER\SQLEXPRESS' is unavailable.

Correct the errors and retry or press ESC to cancel the change(s)."

 

[Tom van Stiphout]

Sounds like your MSDTC service is not running.
In the Services applet, look for "Distributed Transaction Coordinator"

-Tom.

 

[Korsakow]

That was right: i started the service, but when I try to change data in the
linked table in Microsoft SQL ServerNo row was updated.

The data in row 12 was not committed
Error source: .Net SqlClient Data provider.
Error Message: Transaction context in use by another session.

Correct the errors an retry or press ESC to cancel the change(s).

All other programs are close, except MSE

thanks a lot!!!!!

 

[Korsakow]

Is it possible that the problem is that de database is on the same server?

 

[Tom van Stiphout]

No.
You're definitely getting some very unusual errors. I would try
running your code against another server. Perhaps that one is not
messed up

-Tom.

 

[Korsakow]

I get the same errors on the other server, but I've the same error:

The data in row 12 was not committed
Error source: .Net SqlClient Data provider.
Error Message: Transaction context in use by another session.

I spended a lot off time looking for an solution, without result...

Please can u help me?

 

[Tom van Stiphout]

If you have a reproducible scenario, you may be able to hire a
consultant to work on it. This is likely not a problem that can be
fixed via the newsgroups. "Microsoft Solution Provider" in your yellow
pages may be a good place to start.

Sorry the news isn't better.

-Tom.

 

[Sylvain Lafontaine]

If both databases are located on the *same instance* of sql-server, then you
don't have to use a linked database to access x from y. All you have to do
is to use the notation Database.Schema.Table (or Database.Owner.Table if you
are with SQL-Server 2000 instead of 2005+) as in:

select xTable1.*, table2.* from table2 inner join x.dbo.table1 as xTable1 on
......

In your case, you don't say how you etablished the linked server (did you
use the integrated linked database wizard of ADP or did you etablish the
linked server yourself?) and what are the connection
parameters/authentification method/driver used.

If both databases are on the save server, you shouldn't have any problem
using a linked server. In fact, you shouldn't have to use a linked server
at all.