Post reply

Stored procedure and ODBC

  • January 5, 2004 at 9:38 am

    #107568

    We have some data source configured with ODBC connection.

    Is there anyway I can use inside stored procedure to access this ODBC data source and copy them over to local SQL server.  ( Not the DTS import/export package)

     

    Thanks,

     

    HG

     

  • January 5, 2004 at 10:44 am

    #488668

    Create a linked server to the ODBC data source and use the linked server in your stored procedure or use OPENROWSET keyword..  Check OPENQUERY and/or OPENROWSET in BOL.


    Joseph

  • January 5, 2004 at 11:01 am

    #488672

    My ODBC data source is DB2, no OLE DB lib provided by MS to build the linkdb.

    Is there any other solution for Stored Procedure.

    If DTS is the way, Can I define cursor inside DTS so that I can see each record I pumped.

    Thanks,

  • January 5, 2004 at 11:17 am

    #488677

    Won't Microsoft OLE DB Provider for ODBC Driver work?

    Since DTS can 'see' your ODBC, I'm sure you could create a liked server to it.

    I'd stay clear of cursors in DTS.  Use ActiveX instead.


    Joseph

  • January 5, 2004 at 12:46 pm

    #488694

    Joseph,

    You are right, OLE DB for ODBC driver works for building linkdb for DB2. But Microsoft OLE DB for DB2 would't work.

    DTS connect to ODBC no problem.

    Thanks

    HG

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply