Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Transfer data b/w SQL Server & IBM UDB

  1. #1
    Join Date
    Oct 2002
    Posts
    123

    Transfer data b/w SQL Server & IBM UDB

    I need to transfer some data from SQL Server to IBM UDB on a regular basis. Is there a way to do this through DTS or any other functionality in DQL server.

    Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Yes, you can use either DTS or linked server. When you define a connection in DTS you can use Other DB Connection to use a DSN.

  3. #3
    Join Date
    Oct 2002
    Posts
    123
    Ok, here’s what I have done:
    I crated an ODBC System DSN to my UDB database & tested the connection successfully. Now in the DTS package, I can use Other DB connection to point to the DSN I created, & transfer table/data from UDB to SQL Server. However when I try to transfer table/data from SQL Server to UDB, I am able to create the table, but it is not able to transfer any data. I get an error saying Driver is not capable to do this.
    So, it can create a table on UDB, but not transfer any data… what could the problem be?

    Also, how can I set up a linked server? Won’t I need OLE DB Provider for DB2 for that?

    Thanks for all your help!

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Did you try transfer data only from sql to existing udb table? You can create linked server with Microsoft ole db provider for odbc drivers.

  5. #5
    Join Date
    Oct 2002
    Posts
    123
    I am not sure of what information to enter in the following fields for creating a new linked server after choosing the provider as Microsoft ole db provider for odbc drivers:

    Product name
    Data source
    Provider String
    Location
    Catalog


    Thanks…

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Only need odbc dsn as data source.

  7. #7
    Join Date
    Oct 2002
    Posts
    123
    I just gave the DSN name & the server name and added the server.
    When I click on Tables it says, Data source name not found.


    Does this DSN need to be on th server or can it reside on the client?

    Thanks...

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    Try query linked server in query analyzer. I always have problem to list tables for non-sql liked server in em so not trust it anymore.

  9. #9
    Join Date
    Oct 2002
    Posts
    123
    I tried it in EM... & it gave the following error again:

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'MSDASQL' reported an error.
    [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]

    I have the DSN on my client machine...doe sit need to be on the server? also does the server need to have DB2 client?

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    Yes, should create dsn on sql server. Query linked server with query analyzer instead of em.

  11. #11
    Join Date
    Oct 2002
    Posts
    123
    Sorry, in my earlier post I meant to say query analyzer.... u tried runningit on QA & got that error.

    but my DSN is on my client... let me create it on the server & then try again.

    thanks!

  12. #12
    Join Date
    Oct 2002
    Posts
    123
    Rmiao,
    As suggested by you, I created a DSN on the sql server itself & created a linked server. Now I can query the UDB database directly using:
    SELECT *
    FROM OPENQUERY(Dev, 'SELECT * FROM dsnp.Test')
    GO

    Now if I want to insert some data into the tables….what syntax do I use? Also, how can I use this linked server to transfer data from any sql database to this new database. Can I use DTS at all?

    Thanks a bunch for all your help

  13. #13
    Join Date
    Sep 2002
    Posts
    5,938
    Something like

    INSERT INTO OPENQUERY (Dev, 'SELECT column_to_insert FROM dsnp.Test') values (value_to_insert)

  14. #14
    Join Date
    Oct 2002
    Posts
    123
    I get the following error:

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'MSDASQL' reported an error.
    [OLE/DB provider returned message: [IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=S1C00]

  15. #15
    Join Date
    Sep 2002
    Posts
    5,938
    Then should check db2 odbc driver version to see if it supports those transactions.

Posting Permissions

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