ODBC error when trying to modify a view...
I have an SQL Server 2000 database that includes a linked server - which
happens to be a Pervasive PSQL database (ver 10) containing our accounting
I tried to modify a view which contains fields from one of the linked
server's database tables but whenever I modify and attempt the save the view
on the SQL Server, I get the following error... ( I can modify no probs and
it runs - I just can't SAVE it - that's when I get the error)
"ODBC Error [Microsoft][ODBC SQL Server Driver][SQL Server]MSDTC on server
TRUESQLBKUP is unavailable."
To access the linked tables from within our SQL Server views, we created a
system DSN called "TNLHBV" on our SQL Server (truesqlbkup) using the ODBC 32
bit administrator. I made sure that we were using the most recent Pervasive
ODBC Engine driver as well by downloading the most recent drivers from their
site. Once I had that all set up, I then went into the SQL Server and added
a new "linked server" and set the provider to the "Microsoft OLE DB
provider for ODBC", set up the access and saved. I am able to see the data
using the existing views with no probs and our external VB.net apps can also
work with the data no probs. I am just unable to modify any existing views
the use any of the linked server's tables and fields...
No matter what kind of a change I try to make, I get this error. I have
tried deleting and adding the ODBC driver again, deleting and adding the
linked server etc...etc... all to no avail. I get the same error.