This will go into further description of Microsoft SQL Server Linked Server. This tool is within the MS SQL Server Manager that comes with MS SQL Server.
Linked Server is used to connect ASE and MS SQL Server together. This linked server will show you the tables and allow you to execute t-sql.
With this tool you can retrieve, join, or combine data across platforms. To connect ASE to MS Linked Server together ODBC or OLEDB drivers are used.
When deciding what driver to use, remember that OLEDB is End of Life and is no longer supported by MS or SAP.
It is required that the MDA scripts are updated on the ASE. The SDK will have an sp folder for each driver. This is used to update the ASE MDA scripts so that the driver is compatiblie and has the correct data it needs. To test if this is required you can run ASE command sp_version. If this version is lower than the driver being used, the ASE MDA scripts need to be updated.
To update the MDA scripts, go to your SDK installation and run install_<driver>_sprocs <sql.ini name> <username> <password>.
C:\>Sybase\DataAccess\OLEDB\SP\install_oledb_sprocs redhead sa password
You can check if the installation worked by running sp_version on ASE.
Microsoft is full of permissions and permission issues. DCOM security needs to be setup so that we do not run into any error messages trying to access the drivers and output results.
First check in Services for what account/user is running MSSQLServer. This account needs to be added to the DCOM Security. DCOM security can be accessed through dcomcnfg.exe > Component Services > Computers > Right click My computer > Properties > COM Security. Add your user and MSSQLServer account to Access Permissions and Launch and Activation Permissions.
Along with DCOM security settings, you have to open up the DTC security settings. This is in dcomcnfg.exe > Component Services > Computers > My Computer > Distributed Transaction Coordinator > Right click Local DTC > Properties > Security.
Make sure that Network DTC Access is checked
Make sure that Allow Remote Clients is checked
Make sure that Allow Inbound is checked
Make sure that Allow Outbound is checked
Make sure that Enable XA transactions is checked
Make sure that the No Authentication Required radio button is selected
After implementing the changes reboot the server for the changes to take effect.
The two providers that can be used with Linked Server are ASEOLEDB and MSDASQL (ODBC). These providers have settings for all Linked Server connections that fall within.
Dyanmic Prepare (Checked) - This options allows the provider with '?' parameter marker syntax. This allows the parameterized queries to be executed.
Nested queries - Enables nested select statements in the from clause.
Level zero only - Only level 0 OLE DB interfaces are invoked against the provider
Allow inprocess (Checked) - Unchecked the provider is ran outside of MSSQLServer and in doing so allows MSSQLServer to be less affected by errors/dumps. Checked the provider is ran inside the SQLServer and requires less permissions.
Non transacted updates - SQL Server allows updates, even if ITransactionLocal is not available. If this option is enabled, updates against the provider are not recoverable, because the provider does not support transactions.
index as access path - SQL Server attempts to use indexes of the provider to fetch data. By default, indexes are used only for metadata and are never opened.
Disallow adhoc access - SQL Server does not allow ad hoc access through the OPENROWSET and OPENDATASOURCE functions against the OLE DB provider. When this option is not set, SQL Server also does not allow ad hoc access.
Supports 'Like' operator - Indicates that the provider supports queries using the LIKE keyword.
After the provider is setup you can create a Linked Server to connect to an ASE. The first tab is General, Provider will either be SAP ASE OLE DB Provider with Product name ASEOLEDB.
If you want to use ODBC, this would be Microsoft OLE DB Provider for ODBC Drivers and Product name MSDASQL. Data Source can be used with a DSN you created on the box or ASEHOST:ASEPORT to connect. Provider string will allow you to add connection string properties. Catalog is used to enter the database you wish to use.
Security section is where you can login with a username / password. Use the last radio button Be made using this security context.
Server Options section:
Collation Compatible (false) - Enabled, this assumes all characters are compatible with local server. Disabled SQL Server always evaluates characters.
Data Access (true)- Enables and disables a linked server for distributed query access.
RPC (true) - Enable RPC from the server
RCP Out (true) - Enable RPC to the server
Use Remote Collation (true) - Only SQL Server can use this setting on false.
Collation Name (blank) - Set this option to assign a specific collation for queries and transactions
Connection Timeout - Set the timeout value for connections
Query Timeout - Set the timeout value for queries
Distributor (false) - Enabled participating in replication of a distribution publisher
Publisher (false) - Enabled the linked server is a replication publisher
Subscriber (false) - Enabled the linked server is a replication subscriber.
Lazy Schema Validation (false) - Enabled schema used by the linked server data is validated when the local instance of SQL Server is idle.
Enable Promotion of Distributed Transactions (true) - Enabled this uses MS DTC transaction.
When you have a Linked Server, we can run queries to the ASE in different ways. Select New Query and a blank screen will appear. From this blank screen you can query MSSQLServer or Linked Servers. Here are a few ways to run queries.
4 Part query
SELECT * FROM ODBC.pubs2.dbo.syscolumns
SELECT * FROM OPENQUERY(ODBC, 'SELECT * FROM pubs2.dbo.syscolumns')
EXEC (‘SELECT * FROM authors WHERE au_fname = “ANN”’) AT ODBC
Limited stored procedure support when using Linked Server with MSSQL Server and ASEOLEDB
Installing the MDA scripts for the drivers