Setting Up Adaptive Server Enterprise to Query MS SQL Server

Set up Adaptive Server and Component Integration Services (CIS) to query MS SQL Server through DirectConnect.

1. Add an entry to the ASE interfaces file to connect to mssql:

   mssql

   master tcp ether hostname 12530

   query tcp ether hostname 12530

2. Enable CIS and remote procedure call handling from the ASE server. For example, if CIS is already enabled as the default:

   sp_configure 'enable cis'

   Parameter Name Default Memory Used Config Value Run Value

   enable cis 						1 		0 				1 			1

   (1 row affected)
   (return status=0)

   sp_configure 'cis rpc handling', 1

   Parameter Name Default Memory Used Config Value Run Value

   enable cis 						0 		0 				0 			1

   (1 row affected) 
   Configuration option changed. The SQL Server need not be restarted since the option is dynamic.

   You may need to restart Adaptive Server Enterprise server after enabling CIS remote procedure call handling in older versions such as Sybase IQ 12.5.

3. Add the DirectConnect server to the ASE server’s SYSSERVERS system table.

   sp_addserver mssql, direct_connect, mssql

   Adding server 'mssql', physical name 'mssql'
   Server added. 
   (Return status=0)

4. Create the user in Adaptive Server Enterprise that will be used in Sybase IQ to connect to ASE.

   sp_addlogin tst, tsttst

   Password correctly set. 
   Account unlocked. New login created. 
   (return status = 0)

   grant role sa_role to tst
   use tst_db
   sp_adduser tst

   New user added.
   (return status = 0)

5. Add an external login from the master database:

   use master
   sp_addexternlogin mssql, tst, chill, chill

   User 'tst' will be known as 'chill' in remote server 'mssql'.
   (return status = 0)

6. Create an ASE proxy table as the added user from the desired database:

   isql -Utst -Ttsttst
   use test_db
   create proxy_table billing_tst at 'mssql.pubs..billing'
   select * from billing_tst

   status    name          telno
   ------    -----------   -----
   D         BOTANICALLY   1
   B         BOTANICALL    2
   (2 rows affected)