Question Re: Linked Sybase from SQL Server in Technology Q&A

Linked Sybase from SQL Server

former_SQLA_member1694872 — Tue, 27 Jan 2015 12:59:23 GMT

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

Re: Linked Sybase from SQL Server

Former Member — Tue, 27 Jan 2015 16:42:42 GMT

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

Re: Linked Sybase from SQL Server

VolkerBarth — Wed, 28 Jan 2015 03:29:40 GMT

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:)

Re: Linked Sybase from SQL Server

Former Member — Thu, 29 Jan 2015 11:39:36 GMT

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}

Re: Linked Sybase from SQL Server

VolkerBarth — Fri, 30 Jan 2015 04:25:08 GMT

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.

Re: Linked Sybase from SQL Server

Breck_Carter — Fri, 30 Jan 2015 12:07:04 GMT

...easier, faster, safer, better in every imaginable way 🙂

Re: Linked Sybase from SQL Server

former_SQLA_member1694872 — Fri, 30 Jan 2015 15:27:52 GMT

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;

Re: Linked Sybase from SQL Server

VolkerBarth — Fri, 30 Jan 2015 15:43:53 GMT

The local proxy table (i.e. in ASA9) can certainly have the same name as in the remote database (MS SQL) but it can not have the same name as other tables in the same ASA9 database (at least not with the same owner).

That being said, what exact error or problem do you see? ("Not getting it" does not tell that much...)

Re: Linked Sybase from SQL Server

VolkerBarth — Sat, 31 Jan 2015 05:28:09 GMT

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...

Re: Linked Sybase from SQL Server

Former Member — Wed, 11 Feb 2015 21:22:13 GMT

You can use PowerPivot in Excel 2010 and then get it to SQL Server.