Skip to content

Pushing data from MS SQL 2005 to Sybase IQ 15.2

February 6, 2012

 

Introduction

As part of the data warehouse maintenance, sometimes we need to write data from SM SQL to Sybase IQ.  Although there are several methods to accomplish this task, in this post I explore writing directly to Sybase IQ using linked servers and openquery.

Configuration

I tested this procedure using MS SQL 2005 SE SP2 hosted on MS Windows Server 2003 SE SP2, Sybase IQ 15.2 ESD#3 hosted on Solaris 10, and Sybase IQ ODBC Driver version 11.00.01.5615 (DBODBC11.DLL)

Steps

  1. Download and install Sybase IQ ODBC driver on the MS Windows Server.  IQ ODBC driver is part of the Sybase IQ Network Client.
  2. On the Windows server, configure a new ODBC DS to the Sybase IQ database.
  3. On the MS SQL Server, on the SAOLEDB.11 linked servers provider, set the options “Dynamic parameters” and “Allow inprocess”.
  4. On the MS SQL Server, create a new linked server to the Sybase IQ database.  The important parameters are: Provider = ‘SQL Anywhere OLE DB Provider 11’; Provider String = ‘dsn=<the data DSN of the DS created en step 2>;uid=<Sybase IQ user id>;pwd=<Sybase IQ password’>.  Also, set the local server login to remote server login mapping’ and the server option “Data Access = True; RPC = True’.
  5. On the Sybase IQ server, set the option FORCE_UPDATABLE_CURSORS = ‘ON’

Test

To test if you can read from the Sybase IQ database:

From MS SQL Server execute:

select * from openquery(MyLinkedServer, ‘select * from <youruser>.<yourtable>’)

To test if you can write to Sybase IQ database:

From MS SQL Server execute:

insert into openquery(MyLinkedServer, ‘select <columnName> from <youruser>.<yourtable>’)

values (‘YourValue’)

Remark

This is a high level guide.  There is abundant information on the internet about de detailed procedures to perform each of the steps specified.

Especial thanks to the Sybase IQ technical support team for helping me in solving some issues while testing this procedure.

Advertisements

From → MSSQL, SAP IQ

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: