We're about to migrate an old MS SQL 2000 server (32-bit) to a MS SQL 2008 R2 server (64-bit). As part of the install, we use the MS Linked Server feature with some SQL Anywhere 12.0.1 (32-bit) databases.

While I remember that setting up Linked Servers in the old server was nasty enough (particulary compared to the according Remote Data Access), we don't get the new system to access our SA tables.

What we have achieved:

  • We deployed a 64-bit custom install (just the ODBC and OLEDB client interfaces and custom resources) to the MS box, using build 12.0.1.3554.
  • We have installed the linked server (with a script, therefore very similar to the one running against the old server).
  • The options are set identically on both servers (except the old one uses a SA 11.0.1.2376 client lib).
  • The linked server itself seems to work, and displaying remote tables and catalogs does work. The credentials are verified.
  • We have setup MSDTC and tested this with the DTCPing tool.

When using a simple four-part-query like the following:

select * from SVR_SA.MyDb.dbo.MyTable

the following error with error code 7312 is returned (translated from German):

Message 7312, level 16, state 1, row 1
Invalid use of schema and/or catalog for OLE DB provider 'SAOLEDB.12'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.

So how can I make the provider expose the desired interfaces?

asked 13 Feb '12, 10:11

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 13 Feb '12, 10:13


Sigh.

One should never forget to set the "AllowInProcess" option. Now it works.

I should've read the docs better - though this is definetely different between MS SQL 2000 and newer versions:

Choose the Allow Inprocess provider option.

The technique for doing this varies with different versions of Microsoft SQL Server. In SQL Server 2000, there is a Provider Options button that takes you to the page where you can choose this option. For SQL Server 2005, right-click the SAOLEDB.12 provider under Linked Servers ยป Providers and choose Properties. Make sure the Allow Inprocess checkbox is selected. If the Inprocess option is not selected, queries fail.

Oh, yes, and how nicely they fail:)

permanent link

answered 13 Feb '12, 10:34

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

edited 13 Feb '12, 10:38

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×438
×41
×10

question asked: 13 Feb '12, 10:11

question was seen: 7,118 times

last updated: 13 Feb '12, 10:38