Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    36

    Unanswered: Import data from a foreign database using a stored procedure and a ODBC connection

    Hi folks,

    I got a database in MS SQL 2000 that needs to be converted temporarily to MSSQL 2008 (year 2011) that will then be converted to PostgreSQL in the beginning of 2012.

    There is a DTS package in MSSQL 2000 that pumps data from a third party helpdesk request software (BMC Remedy) to a temp table in MSSQL 2000. The DTS runs on a daily schedule.

    Since DTS is now deprecated in MSSQL 2008, I converted the DTS package to SSIS. It works, but I'm looking for a more "cross-platform" solution that would be usable in PostgreSQL without having to re-write the whole code.

    I would like to code a stored procedure that connects to BMC Remedy using that same ODBC connection as before. All I want is to detach from Microsoft proprietary solutions.

    Is it possible to SELECT a foreign database using ODBC is MS SQL 2008?

    Thank you for sharing your expertise.
    Charles M.
    DB2 DEV server : DB2 Express-C / Windows 2008 Server Standard Edition
    DB2 PROD server : DB2 9.5 Workgroup / Windows 2008 Server Standard Edition

  2. #2
    Join Date
    Apr 2011
    Location
    Pakistan
    Posts
    28
    1.Download ODBC drive from ODBCng

    https://projects.commandprompt.com/p...wiki/Downloads
    http://www.postgresql.org/ftp/odbc/versions/msi/

    or and install it

    2.Set up a System DNS that connects to your PostgreSQL server. I named mine POSTGRESQL, which is used in the next couple of steps

    3. the following code in SSMS to create the linked server. This assumes a PostgreSQL instance on the local machine (hence localhost):

    EXEC master.dbo.sp_addlinkedserver @server = N'POSTGRESQL', @srvproduct=N'Microsoft OLE DB Provider for ODBC Driver', @provider=N'MSDASQL', @datasrc='PostgreSQL', @location='localhost', @catalog='public'

    4. the following code in SSMS to create a login mapping for the linked server:

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'POSTGRESQL', @useself=N'False', @locallogin=NULL, @rmtuser='', @rmtpassword=''

    5.Issue statements such as:

    SELECT * FROM OpenQuery(POSTGRESQL, 'select my_column from my_table limit 10')

    if this Linked Server will run then you can easily query to POSTGRESQL database and you can create ODBC connection for SSIS packages too

    Regards,

    Syed Jahanzaib Bin Hassan
    MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    A|U|R|E|U|S – S|A|L|A|H
    Last edited by SJahanzaib; 04-27-11 at 12:38.

  3. #3
    Join Date
    Jun 2010
    Posts
    36
    Sounds good! I will

    BMC Remedy runs on Oracle, so basically I would like to use the ODBC provided by BMC Remedy and issue a INSERT statement in order to pump the data from it.

    Will this solution work on PostgreSQL? I mean pumping data from Oracle TO PostgreSQL on a daily basis?

    Thanks
    Charles M.
    DB2 DEV server : DB2 Express-C / Windows 2008 Server Standard Edition
    DB2 PROD server : DB2 9.5 Workgroup / Windows 2008 Server Standard Edition

  4. #4
    Join Date
    Jun 2010
    Posts
    36
    Sorry.. double post.
    Last edited by CharleyDC5; 04-27-11 at 13:42.
    Charles M.
    DB2 DEV server : DB2 Express-C / Windows 2008 Server Standard Edition
    DB2 PROD server : DB2 9.5 Workgroup / Windows 2008 Server Standard Edition

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •