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

asked 27 Jan '15, 12:59

Walmir%20Taques's gravatar image

Walmir Taques
625293146
accept rate: 13%


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%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

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

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;
permanent link

answered 30 Jan '15, 15:27

Walmir%20Taques's gravatar image

Walmir Taques
625293146
accept rate: 13%

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

(30 Jan '15, 15:43) Volker Barth

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:

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

permanent link

answered 31 Jan '15, 05:28

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 32%

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

permanent link

answered 11 Feb '15, 21:22

mysorian's gravatar image

mysorian
1186713
accept rate: 0%

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:

×101

question asked: 27 Jan '15, 12:59

question was seen: 1,233 times

last updated: 11 Feb '15, 21:22