Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rolandsantos
Starting Member
24 Posts |
 Posted - 2008-02-29 : 05:29:22
|
| hi, your help is deeply appreciated. here is my problem..i created a view in server1 and server2:create view vwtestasselect srv, cnt from server1.test.dbo.test770union allselect srv, cnt from server2.test.dbo.test771i have no problem whe i issue a SELECT statement both on server1 and server 2select * from vwtest will resultsrv cnt--- ---770 1770 2771 1771 3however when i execute aupdate vwtestset cnt = 5 where cnt = 1returns an error:Server: Msg 4451, Level 16, State 19, Line 1Views referencing tables on multiple servers are not updatable on this SKU of SQL Server.what is this error?also, i tried it to other servers knowing that my problem is SQL version related, but i also got this error:Server: Msg 7391, Level 16, State 1, Line 1The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].BOL does'nt offer any solutions, i followed all the rules in distributed views but to no avail.. help pls.... |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-02-29 : 06:32:23
|
What is your version of SQL-server? From BOL under "Creating a Partitioned View":quote:
Data Modification RulesIn addition to the rules defined for updatable partitioned views, data modification statements referencing the view must adhere to the rules defined for INSERT, UPDATE and DELETE statements.Note: You can modify data through a partitioned view only if you install Microsoft SQL Server 2000 Enterprise Edition or Microsoft. SQL Server 2000 Developer Edition.
--Lumbago"SELECT Rum, Coke, Lime, Ice FROM bar WHERE ClosingTime = 'Late' AND FemaleMaleRatio > 4" |
 |
|
|
rolandsantos
Starting Member
24 Posts |
Posted - 2008-02-29 : 06:57:26
|
| FYI, i tried this on two windows version thats why i have two different errorsprint @@version returns this version:from the 1st error, server1 and server2 (Windows 2000 Server machines):Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)error is:Server: Msg 4451, Level 16, State 19, Line 1Views referencing tables on multiple servers are not updatable on this SKU of SQL Server.from 2nd error, both server1 and server2 (Windows XP machines):Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)error is:Server: Msg 7391, Level 16, State 1, Line 1The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
|
|
rolandsantos
Starting Member
24 Posts |
Posted - 2008-02-29 : 07:09:37
|
| thanks a lot, ill check those sites... |
|
|
|
rolandsantos
Starting Member
24 Posts |
Posted - 2008-03-03 : 21:12:27
|
| I tried all the solutions suggested on those sites but it still fails. FYI, i am testing this on SQL 2000 Developer Edition running on Windows XP SP2 machines. Is there any issue with that? What is the best practice in partitioned views?thanks... |
|
|
|
|
|
|
|
|
partitioned views