|
Next: How to limit OUTER join to a single row?
|
| Author |
Message |
External
Since: Apr 04, 2007
Posts: 11
|
(Msg. 1) Posted: Thu Jan 11, 2007 9:35 am
Post subject: SQL 2005 view based on linked server table can't be manually updated
Archived from groups: microsoft>public>sqlserver>programming (more info?)
|
|
|
Hi everyone!
I have two SQL Server 2005, Server_A and Server_B. On Server_A, I
created a linked server (Server_B). On Server_A, I created a view that
looks like:
CREATE VIEW [dbo].[vwTest] AS
SELECT LastName, FirstName
FROM [Server_B].test.dbo.table_1
The view returns correctly the records. If I execute something like:
UPDATEdbo.vwTest
SETLastName='FF'
it works fine!
But if I open the view and I try to manually change a value (directly
typing in a record), I get an error message that looks like this (my PC
is in french, so it's my own translation) "(..) The operation was
cancelled because the OLE DB 'SQLNCLI' provider from the linked server
'Server_B' wasn't able to start a distributed transaction. (...)"
Anyone has a clue?
Thanks! |
|
| Back to top |
|
 | |
External
Since: Apr 04, 2007
Posts: 11
|
(Msg. 2) Posted: Thu Jan 11, 2007 10:08 am
Post subject: Re: SQL 2005 view based on linked server table can't be manually updated [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Apr 04, 2007
Posts: 11
|
(Msg. 3) Posted: Thu Jan 11, 2007 11:22 am
Post subject: Re: SQL 2005 view based on linked server table can't be manually updated [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Apr 04, 2007
Posts: 11
|
(Msg. 4) Posted: Thu Jan 11, 2007 11:42 am
Post subject: Re: SQL 2005 view based on linked server table can't be manually updated [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Apr 04, 2007
Posts: 11
|
(Msg. 5) Posted: Thu Jan 11, 2007 12:06 pm
Post subject: Re: SQL 2005 view based on linked server table can't be manually updated [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Apr 04, 2007
Posts: 11
|
(Msg. 6) Posted: Thu Jan 11, 2007 12:17 pm
Post subject: Re: SQL 2005 view based on linked server table can't be manually updated [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Apr 04, 2007
Posts: 11
|
(Msg. 7) Posted: Thu Jan 11, 2007 1:07 pm
Post subject: Re: SQL 2005 view based on linked server table can't be manually updated [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Apr 04, 2007
Posts: 11
|
(Msg. 8) Posted: Thu Jan 11, 2007 1:24 pm
Post subject: Re: SQL 2005 view based on linked server table can't be manually updated [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Aug 28, 2003
Posts: 86
|
(Msg. 9) Posted: Thu Jan 11, 2007 3:00 pm
Post subject: Re: SQL 2005 view based on linked server table can't be manually updated [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
The short answer is not to use SMS or EM to "open" a view or a table for
editing. When using these tools for editing, you have no control over what
exactly the tool does to get the data (or metadata) and how it uses that
information to update the database. Use QA (or a query window in SMS)
instead. If you are curious, you can use the profiler to monitor the
commands that SMS is using. >> Stay informed about: SQL 2005 view based on linked server table can't be manual.. |
|
| Back to top |
|
| |
External
Since: Jan 24, 2007
Posts: 38
|
(Msg. 10) Posted: Thu Jan 11, 2007 6:41 pm
Post subject: Re: SQL 2005 view based on linked server table can't be manually updated [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Try starting MSDTC (distributed transaction coordinator), it seems it isnt
started.
MC
wrote in message
> Hi everyone!
>
> I have two SQL Server 2005, Server_A and Server_B. On Server_A, I
> created a linked server (Server_B). On Server_A, I created a view that
> looks like:
>
> CREATE VIEW [dbo].[vwTest] AS
> SELECT LastName, FirstName
> FROM [Server_B].test.dbo.table_1
>
>
> The view returns correctly the records. If I execute something like:
>
> UPDATEdbo.vwTest
> SETLastName='FF'
>
> it works fine!
>
> But if I open the view and I try to manually change a value (directly
> typing in a record), I get an error message that looks like this (my PC
> is in french, so it's my own translation) "(..) The operation was
> cancelled because the OLE DB 'SQLNCLI' provider from the linked server
> 'Server_B' wasn't able to start a distributed transaction. (...)"
>
> Anyone has a clue?
>
> Thanks!
> >> Stay informed about: SQL 2005 view based on linked server table can't be manual.. |
|
| Back to top |
|
| |
External
Since: Jan 24, 2007
Posts: 38
|
(Msg. 11) Posted: Thu Jan 11, 2007 8:18 pm
Post subject: Re: SQL 2005 view based on linked server table can't be manually updated [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Jan 24, 2007
Posts: 38
|
(Msg. 12) Posted: Thu Jan 11, 2007 8:25 pm
Post subject: Re: SQL 2005 view based on linked server table can't be manually updated [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
|
|
| Back to top |
|
| |
External
Since: Jan 24, 2007
Posts: 38
|
(Msg. 13) Posted: Thu Jan 11, 2007 8:52 pm
Post subject: Re: SQL 2005 view based on linked server table can't be manually updated [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Ufff, my mistake. Try using XACT_ABORT ON. If that doesnt help, all I can
think of is checking MSDTC properties.
MC
wrote in message
>I tried this without success:
>
> SET ARITHABORT ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE VIEW dbo.vwTest
> AS
> SELECT LastName, FirstName
> FROM [CA-MTL-SRV01].test.dbo.table_1
>
> GO
>
> SET ARITHABORT OFF
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> >> Stay informed about: SQL 2005 view based on linked server table can't be manual.. |
|
| Back to top |
|
| |
External
Since: Jan 23, 2007
Posts: 5
|
(Msg. 14) Posted: Fri Jan 12, 2007 7:55 am
Post subject: Re: SQL 2005 view based on linked server table can't be manually updated [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Read about it in BOL. It all depends on a number of things, mainly how do
you handle errors and transactions. If you need to update more then one row,
I would really suggest that you write a script to do it.
MC
wrote in message
> Allright got it!
>
> I had to set on my local server, where the view is, the XACT abort
> option under the server properties/connections.
>
> Does anyone know if setting this option can cause other problems or if
> I can leave it checked without any worry?
>
> Thanks!
> >> Stay informed about: SQL 2005 view based on linked server table can't be manual.. |
|
| Back to top |
|
| |
FAQ
Search
Profile
Private Messages
Log in
