SAP SQL Anywhere

Question

How do I create (step by step) one sybase linked server to SQL Server In ODBC not appear Ole Provider driver for creation of Alias I do not know where to start. :( I am using Sybase ASA 9 and SQL Server 2012

Answer 1

0

ASA 9 is past support for a number of years now and has never been tested for use with a current release of SQL Server. but the concepts are nothing new.

You will want to review proxy tables and remote servers. Do review the version 9 doc set that comes with the product but the equivalent section from 10.0.1 is viewable online

http://dcx.sap.com/index.html#1001/en/dbugen10/ug-proxy-omni-using.html

Another alternative is to reverse that connection. Version 9 has an OLE/DB driver and I believe others have used that to make a remote data access for SQL Server to ASA (though that combination has had no official testing either, of course).

best of luck

permanent link

answered 27 Jan '15, 16:42

Nick Elson S...
7.3k●35●107
accept rate: 32%

I believe others have used

I guess most of these (including me) have experienced that it's faaaaaar easier to access data in SQL Anywhere from a remote MS SQL Server (aka remote data access/proxy tables) than the opposite (using MS SQL server's Linked server feature). If you need to do the later, here are some of those FAQs on that topic:

http://sqlanywhere-forum.sap.com/search/?q=linked+server+ms+sql&Submit=search&t=question

That's not to say remote data access would not have its obstacles, either:)

(28 Jan '15, 03:29) Volker Barth

Thanks Volker. We try to be helpful that way.

I suggested the other approach, only because, sometimes it makes more sense to use the technology that matches the data movement requirements or one that allows processing (SQL executing, views, permissions, ... ) at the desired point of control; even if that means using that "other" technology.

Caveat Given the untested combination of product versions involved, you may find one approach a little more stable than the other. Testing for stability is highly recommended because of post-maintenance level of support available for SQL Anywhere 9. Of course, that observation does justify upgrading to current releases more than it does the picking the lesser of 2 evils. {pls. no Master & Commander references}

(29 Jan '15, 11:39) Nick Elson S...

Replies hidden

Comment Text Removed

Nick, I surely agree that's most important to choose the approach that fits the requirement, so if Walmir has to use data within ASA 9 from a remote MS SQL Server ASA's remote data access will be the way to go whereas when he needs to use ASA9 data within a MS SQL Server environment, MS SQL Server's Linked Server feature will be more appropriate. I am not sure what he is about to do.

I just wanted to mention that me (and others like Breck, AFAIK) who had to use both approaches usually have felt that ASA's remote data access feature is much easier to use.

(30 Jan '15, 04:25) Volker Barth

...easier, faster, safer, better in every imaginable way :)

(30 Jan '15, 12:07) Breck Carter

flat view

Answer 2

Accessed the SQLServer database using remote access / proxy tables. Actually a lot easier, but tried to make a cursor to generate the proxies tables and I'm not getting. I'm trying as follows: (generate via Sybase Central does not have to do all the tables at once and because the proxy table can not have the same table name of the table that exists in my ASA database?)

PS: What I want to do is insert ASA database data in SQLServer database.

Below code to generate the proxy tables, thank you!

begin
   declare qry long varchar;
   FOR I AS CURSOR_TABELA cursor for SELECT table_name from systable where substr(table_name,1,2) = 'TB' DO
      EXECUTE('CREATE EXISTING TABLE '||STRING(TABLE_NAME)||'_2'||' AT '||STRING('SQL;dbSQL;dbo;'||STRING(TABLE_NAME)));
   END FOR;
end;

Answer 3

What I want to do is insert ASA database data in SQLServer database.

Will this be a regular activity or just a one-time action (say, for data migration purposes)?

Breck has written several blog articles on data transfer between SQL Anywhere and MS SQL Server, cf. these:

In-Memory Downloading from SQL Server (1) - and the further parts listed there
UNLOAD SELECT BULK INSERT - that does not use proxy tables, but unload and the FORWARD TO statement to execute real MS SQL statements directed from the SQL Anywhere side...

I can't tell whether they will apply to your situation but just wanted to show that there are possible further methods available than one might think of...

Answer 4

-1	You can use PowerPivot in Excel 2010 and then get it to SQL Server. permanent link answered 11 Feb '15, 21:22 mysorian 118●9●9●13 accept rate: 0% flat view

SAP SQL Anywhere

Linked Sybase from SQL Server

Follow this question

Related questions