Two years ago, I had tested to access SQL Anywhere databases from MS SQL 2000 via the MS LinkedServer feature. At that time, attempts to use the "4-part syntax" failed in my tests, both with SA 11.0.1.2250 and older servers, and independent of the used OLEDB driver (SAOLEDB.11 vs. ASAProv.80).

Therefore we have used the OPENQUERY syntax for remote access.

In current tests with the same OLEDB driver against a v12.0.1.3324 database, the 4-part syntax seems to work - here in a sample with a remote procedure call to the linked server SVR_SA:

::SQL
-- a) via OPENQUERY -> works
select * from OPENQUERY(SVR_SA, 'call dbo.STP_Stat(''2009-01-01'', ''2009-07-31'')')

-- b) via 4-part syntax -> works with SA 12.0.1, has not worked with v11.0.1.2250
exec SVR_SA..dbo.STP_Stat '2009-01-01', '2009-01-31'

IIRC, I had tried to solve the problem then ago but it was unclear whether the failure was on the MS SQL or the SA side (and found very little information on this topic), and so I did not investigate further. Now, as the MS SQL side is left unchanged, I conclude that there's an improvement in SQL Anywhere 12.x that makes the 4-part syntax possible.

Is this (very welcome!) change in behaviour a known improvement? (If so, I have missed the notification...)


Looking back, I noticed it was nearly two years ago when I started these investigations, cf. the following threads in the sybase.public.sqlanywhere.general NG:

  • "Problem with distributed transactions between MS SQL Server and ASA 8.0.3" from 2009-05-08
  • "SA 11 / MS SQL Server integration: MS 4-part-syntax fails" from 2009-07-13

asked 28 Apr '11, 06:19

Volker%20Barth's gravatar image

Volker Barth
29.6k294444650
accept rate: 32%

edited 28 Apr '11, 06:39


Ah, I see it now. There was indeed a fix on the SA side. In fact, the reason things work now is due to two combined fixes. Issue #620298 fixes a problem with using MS DTC with SA and the followup issue #631330 resolves some metadata problems within the OLE DB support. Both fixes together resolve the MS Linked Server problems; and, both fixes are available in later SA 11.0.1 EBFs.

Karim

permanent link

answered 28 Apr '11, 07:44

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

edited 28 Apr '11, 08:03

Volker%20Barth's gravatar image

Volker Barth
29.6k294444650

Thanks for the explanation. I've done some tests with the sample database of SA 11.0.1.2527 and 12.0.1.3324 and have not had issues with the 4-part syntax - and according to your note, both versions are fixed in that respect.

FWIW, these is a short test script - here for the SA11 demo database:

:::SQL
-- add linked server (DSN-less) with default dba login
exec sp_addlinkedserver 'SVR_SA', 'SA Test', 'SAOLEDB.11', null,
   null, 'ENG=demo11;DBN=demo;LINKS=TCPIP;CON=MSLinkedSvr', null
exec sp_addlinkedsrvlogin 'SVR_SA', 'false', null, 'dba', 'sql'

-- set necessary options
exec sp_serveroption 'SVR_SA', 'collation compatible', 'true'
exec sp_serveroption 'SVR_SA', 'data access', 'true'
exec sp_serveroption 'SVR_SA', 'rpc', 'true'
exec sp_serveroption 'SVR_SA', 'rpc out', 'true'

-- check results
exec sp_helpserver 'SVR_SA'
exec sp_helplinkedsrvlogin 'SVR_SA'

-- test queries in 4-part syntax
select * from SVR_SA..groupo.Contacts
exec SVR_SA..groupo.ShowContacts 1

-- drop linked server
exec sp_droplinkedsrvlogin 'SVR_SA', null
exec sp_dropserver 'SVR_SA'
(28 Apr '11, 08:02) Volker Barth

Is there any more info on the first CR you mentioned? (The public description seems empty.)

IIRC, the MS DTC setup was one of the biggest hurdles to get the LinkedServer feature running against SQL Anywhere, so more info would be welcome. (In general, I'm just doing read access against SQL Anywhere, so I usually would think there would be no need for distributed transactions at all. FWIW, the above test seems to work without having to setup MS DTC on my box...)

(28 Apr '11, 08:07) Volker Barth

Yes, you are correct, the public description for the first CR is indeed empty. The problem had to do with the order in which MS SQL Server issues the two-phase commit request and the connection close request. Since the actual problem was out of the user's control, the public description was left empty. Suffice it to say that both fixes are necessary to get Linked Servers to fully work. Your test does not perform updates so DTC probably is not necessary.

(28 Apr '11, 08:13) Karim Khamis
Replies hidden

...so I can conclude I could try to get rid of the DTC involvement as long as I really do not perform write access against the SQL Anywhere side? (Yes, I appreciate the word "probably" in your statement.)

Big thanks, Karim!

(28 Apr '11, 08:16) Volker Barth

No, I don't think it is as simple as that. I believe that even if you do not perform any updates, SQL Server may still get DTC involved if you attempt to access more than one Linked Server on the same SQL Server connection. So my guess is that DTC will get involved eventually.

(28 Apr '11, 08:35) Karim Khamis

BTW, DCX should be back now.

(28 Apr '11, 08:38) Karim Khamis
1

Well, I do think "it's not as simple as that" is a valid resume when comparing the whole Linked Server stuff against an SQL Anywhere database with the opposite situation (Remote data access from SQL Anywhere against a MS SQL database).

I'm blessed that I usually just have to access MS SQL from SQL Anywhere and not vice versa.

(28 Apr '11, 10:17) Volker Barth
showing 4 of 7 show all flat view

Volker,

No specific fix/change comes to my mind; others might have an idea as to what might have changed. I believe I was involved in that original newsgroup discussion and at that time had determined that there was some strangeness on the MS SQL Server side. But, to investigate the problem/resolution further, can you set up two linked servers, one for SA 11 and one for SA 12. Run both SA 11 and SA 12 with request level logging turned on and then execute your "4-part" query against both linked servers. It would be worthwhile to see if MS SQL Server sends identical queries to both servers or not.

Karim

permanent link

answered 28 Apr '11, 07:16

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
accept rate: 40%

Well, I just noticed the doc page "Setting up a Microsoft Linked Server using OLE DB" takes about 4-part syntax in v 12.0.1 but doesn't in v11.0.1 - that made me think something has indeed changed. - I can't check for v12.0.0 or link the DCX URLs as DCX seems unavailable at the moment...

(28 Apr '11, 07:19) Volker Barth
Replies hidden

I have to correct myself: Even the 10.0.1 docs talk about the 4-part syntax. It's just the index that includes them only in v12. - Yes, DCX is back:)

(28 Apr '11, 09:40) Volker Barth
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:

×406
×28

question asked: 28 Apr '11, 06:19

question was seen: 2,668 times

last updated: 28 Apr '11, 10:17