Creating a view from a linked server

tolisss

Hi

i have created a new database and a new linked server that points to an
AccessDB using an ODBC DSN.
Now inside that new sql db i have create i need to created a new view so
i open EM went to views and paste the following

select * from openquery (AccessLinkedSe rver,'select * from mytable')

i press run and i see the data ok .but when i try to save the view i get
the following

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable
to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: [DataDirect][ODBC dBase driver]Optional feature not
implemented.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned
0x8004d00a].

*** Sent via Developersdex http://www.developersdex.com ***

Jul 23 '05 #1

Subscribe Reply

14410

Erland Sommarskog

tolisss (no****@devdex. com) writes:

i have created a new database and a new linked server that points to an
AccessDB using an ODBC DSN.
Now inside that new sql db i have create i need to created a new view so
i open EM went to views and paste the following

select * from openquery (AccessLinkedSe rver,'select * from mytable')

i press run and i see the data ok .but when i try to save the view i get
the following

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable
to begin a distributed transaction.

Try creating the view from Query Analyzer. I seem to recall
that when you create a view from Enterprise Manager, it starts a
transaction. Later when you come to create the view itself, the OLE DB
provider cannot upgrade the transaction to a distributed one.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 23 '05 #2

Similar topics

8378

Alternative to Creating View with Union in two databases?

by: Matt D | last post by:

I attempted to create a view in SQL Server 2000 that Unions two queries. The first part of the query gets data from the local server, the second part gets info from a linked server. (The query works fine in Query Analyzer.) I received this error when I tried to save the query: ODBC error: The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.

Microsoft SQL Server

3386

Creating New Views on SQL 2000

by: RJMAL | last post by:

Recently we upgraded our server from SQL 7.0 to SQL 2000. I created a new view in Enterprise Manager which pulls data from a linked server's database. When I write the view... CREATE VIEW dbo.VIEW1 AS SELECT * FROM LinkedServerName.DatabaseName.dbo.TableName and I click on the run button it changes to:

Microsoft SQL Server

4479

View performance, linked servers, query specifiying uniqueidentifier

by: Bernie | last post by:

Greetings, I have 3 servers all running SQL Server 2000 - 8.00.818. Lets call them parent, child1, and child 2. On parent, I create a view called item as follows: CREATE view Item as select * from child1.dbchild1.dbo.Item union all select * from child2.DBChild2.dbo.Item

Microsoft SQL Server

10124

Problem creating indexed views with linked servers

by: Ariel | last post by:

Hi, I have a problem, maybe someone can help me. I'm traing to create a view with a Linked Server This query works great: select id, descr from SERVER.DB.dbo.TABLE When I tray to create the view:

Microsoft SQL Server

2631

Slow MDB Linked View

by: Neil | last post by:

I previously posted re. this, but thought I'd try again with a summary of facts. I have an Access 2000 MDB with a SQL Server 7 back end. There is a view that is linked to the database via ODBC and has been in place for several years without any performance problems. Recently I added a couple of fields to the output of the view, and it became very slow when scrolling. When just opened in the database window, the linked view takes about...

Microsoft SQL Server

3675

Creating Tables on the Fly

by: Neil | last post by:

I have an Access 2000 MDB with ODBC linked tables to a SQL Server 7 back end. I currently have a selections table in the front end file which the users use to make selections of records. The table has two fields -- primary key (which matches primary key of main, SQL Server table), and a boolean field. The table is linked to the main table in a heterogeneous inner join. I'm looking to move the table to the back end, while still giving each...

Microsoft SQL Server

2676

Problem with HOST_NAME Function with Linked View

by: Neil | last post by:

I have an Access 2000 MDB file with a SQL 7 back end. I have a main table with 50,000 records; and I have a selections table with 50,000 records for each machine that uses the database (about 25-50). This allows each user to have their own set of selections. The selections table has three fields: ID (int), Sel (bit), MachName (varchar). ID and MachName comprise the primary key. I have a view that combines the main table and the entries...

Microsoft SQL Server

1940

Linked sql view changing datatype

by: Eric J Owens | last post by:

I have an A2k mdb that uses a linked SQL 2k view. In SQL Server, the underlying table uses datatype 'nvarchar'. When I try to view the data in Access (SQL server works fine for the view), I get: ODBC--call failed. Invalid character value for cast specification (#0) If I use design view to look at the linked view, I see that the 'nvarchar' columns are now 'number'. I have deleted and re-linked the tables in

Microsoft Access / VBA

7042

Problem With View and Changing Field Length

by: Neil | last post by:

I had a strange situation with a view in SQL 7, that I could use some input on. I had a very simple view -- select a, b, c from table1 where x=y and z=q. Field a in table1 originally was varchar 70. A long time ago I changed it to varchar 95. I used this view as an ODBC linked table in an Access MDB. Recently, there was one row which has a value in field a that was more than 70 characters long. This caused an error when the view as...

Microsoft SQL Server

8462

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8802

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8586

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

7405

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6209

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

4206

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4384

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2028

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1787

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General