-
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!
-
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.
-
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!
-
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.
-
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…
-
Only need odbc dsn as data source.
-
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...
-
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.
-
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?
-
Yes, should create dsn on sql server. Query linked server with query analyzer instead of em.
-
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!
-
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
-
Something like
INSERT INTO OPENQUERY (Dev, 'SELECT column_to_insert FROM dsnp.Test') values (value_to_insert)
-
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]
-
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
-
Forum Rules
|
|