How to Import Tables From SQL Server 2012 Into SQL Anywhere 16

[Former Member]

I installed SQL Anywhere 16 Developer and need to import almost 200 Tables originating in Microsoft Access. I have tried the following:

1. Installed Microsoft SQL Server Express 2012
2. Imported Access Tables into SQL Server
3. Added SQL Server to ODBC Source Files (x86)
4. Created a SQL Anywhere 16 connection / database
5. Tried the Migration tool: Resulted in copying a bunch .sys and .dbo files that I did not recognize

Is there an easy way "import" tables into SQL Anywhere from MS SQL Server database? The migration tool is a little confusing and I am a "newbie" to writing source code.

In google searching, I see lots of information for importing from SQL Anywhere to SQL Server but not much on importing from SQL Server to SQL Anywhere. I need an easy solution so I can move on to other design & visual basic programming tasks.

[Breck_Carter]

Please tell us exactly what you mean by "Tried the Migration tool".

The Sybase Central - Tools - SQL Anywhere 16 - Migrate Database wizard does not create any ".sys and .dbo files", it creates and fills table in a SQL Anywhere 16 database...

Sybase Central - create and connect to a running SQL Anywhere 16 database

Tools - SQL Anywhere 16 - Migrate Database...

Create Remote Server Now...

(NOTE: the following uses SQL Server, but you can migrate directly from Access as well... just pick you poison from the list)

Connection information: DSN=MSSTEST;uid=sa;pwd=j68Fje9#fyu489

Create an external login...

Login name: sa

Password: j68Fje9#fyu489

The following SQL will be executed...

CREATE SERVER "MSS" CLASS 'MSSODBC' USING 'DSN=MSSTEST;uid=sa;pwd=j68Fje9#fyu489';

CREATE EXTERNLOGIN "DBA" TO "MSS" REMOTE LOGIN 'sa' IDENTIFIED BY '***';

Select Tables - Add All >>

Which user do you want to own the migrated tables? DBA

[Former Member]

Thanks - I answered my own question (see above)

[Former Member]

Thanks Breck,

I figured it out on my own after doing something else and letting my subconscious think about how to do it.

I used the Open ODBC Administrator to create an ODBC source file (64-Bit) pointing to the Access database I wanted to import into SQL Anywhere 16.

Then I used the Migration Wizard to connect to the Access ODBC source file and selected "all tables" - it worked great!

My last step was to delete all "Proxy" tables.

Bob Sheridan www.acuity-pos-systems.com