Can't save view - linked server

Our customer has an external SQL server on which they want to make a
lookup into a table/view.
I have setup a Linked server and retrieve data from the linked server.
Example:
EXEC sp_addlinkedserver
@.server = 'NPRSQL',
@.srvproduct = 'SQLServer OLEDB Provider',
@.provider = 'SQLOLEDB',
@.datasrc = 'SQL01'
Go
SELECT *
FROM NPRSQL.DTS.dbo.ItemView
However I can "create" the view, get the data, but when I try to save
the view I get the following error:
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
operation could
not be performed because the OLE DB provider 'SQLOLEDB' was unable to
begin a
distributed transaction.[Microsoft][ODBC SQL Server Driver][SQL
Server]
[OLE/DB
provider returned message: New transaction cannot enlist in the
specified transaction
coordinator.]
How can it be that I can get the data but not save the view?
I have tried to make a similarly scenario on the Northwind database,
create a view on the linked server and the "call" this view via a view
on my local server with the same result.
/refdkHi
see this link
http://support.microsoft.com/kb/839279
try this first and let us know
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181027684.578393.61140@.o5g2000hsb.googlegroups.com...
> Our customer has an external SQL server on which they want to make a
> lookup into a table/view.
> I have setup a Linked server and retrieve data from the linked server.
> Example:
> EXEC sp_addlinkedserver
> @.server = 'NPRSQL',
> @.srvproduct = 'SQLServer OLEDB Provider',
> @.provider = 'SQLOLEDB',
> @.datasrc = 'SQL01'
> Go
> SELECT *
> FROM NPRSQL.DTS.dbo.ItemView
> However I can "create" the view, get the data, but when I try to save
> the view I get the following error:
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
> operation could
> not be performed because the OLE DB provider 'SQLOLEDB' was unable to
> begin a
> distributed transaction.[Microsoft][ODBC SQL Server Driver][SQ
L
> Server]
> [OLE/DB
> provider returned message: New transaction cannot enlist in the
> specified transaction
> coordinator.]
> How can it be that I can get the data but not save the view?
> I have tried to make a similarly scenario on the Northwind database,
> create a view on the linked server and the "call" this view via a view
> on my local server with the same result.
>
> /refdk
>|||I had already done most of the stuff in the kb-issue. However, I have
now completed every step but the error persists.
/refdk|||Hi
Can you post the view statement
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"refdk" <fuhlendorf@.gmail.com> wrote in message
news:1181109437.668792.166410@.q75g2000hsh.googlegroups.com...
>I had already done most of the stuff in the kb-issue. However, I have
> now completed every step but the error persists.
> /refdk
>|||SELECT dbo.Hornsh=F8j$Item.No_ AS [No], dbo.Hornsh=F8j
$Item.Description, dbo.Hornsh=F8j$Item.[Unit Price], SUM(dbo.[Hornsh
=F8j
$Item Ledger Entry].Quantity)
AS Quantity
FROM dbo.Hornsh=F8j$Item INNER JOIN
dbo.[Hornsh=F8j$Item Ledger Entry] ON dbo.Hornsh=F8j
$Item.No_ =3D dbo.[Hornsh=F8j$Item Ledger Entry].[Item No_]
GROUP BY dbo.Hornsh=F8j$Item.No_, dbo.Hornsh=F8j$Item.Description,
dbo.Hornsh=F8j$Item.[Unit Price]|||> How can it be that I can get the data but not save the view?
Are you trying to create the view inside of a transaction? Why is it going
through ODBC/OLEDB? Shouldn't you be creating the view in a query window
connected to that server, instead of trying to create the view through your
app via the linked server? (Well, that's how I would do it, anyway.)