Linked Server Query Problem

[ashwingawande]

Hi All,
I have created a linked server on SQL Server 2005 to access data from DB2.
I am able to execute it with the command "SELECT * FROM OPENQUERY(DB2SRV, 'SELECT * FROM dbname.dbowner.tblname') however, not able to execute the same thing with "SELECT * FROM DB2SRV.dbname.dbowner.tblname". Seems very strange.
Any help in this regard will be appreciated.
Thanks in advance for any reply.

Ashwin

[iburyak]

In Enterprise Manager when you go to Security – Linked Servers can you see DB2SRV name?
If yes when you expend this name can you see Tables?
If yes, when you click on it, can you see all tables?
If answer to any of these questions is ‘NO’ it means your linked is not configured properly.



Good Luck.

[ashwingawande]

Hi,
Thanks for your reply. Further, I have successfuly configured the linked server
but now another issue.

I have set up Linked Server to DB2 using MS OLEDB FOR ODBC DRIVER (MSDASQL) i am able to execute select, insert and delete but there are few tables in DB2 where data type of some of the columns is CLOB.

I am not able to select, update or insert in to the DB2 tables. I guess it is the problem of data type mismatch between SQL Server and DB2. Can any body help me how to select DB2's CLOB data type in SQL Server through OPENQUERY and how to insert a text data value into DB2 CLOB data type column.

Currently when i Insert the following error is encountered :

OLE DB provider "MSDASQL" for linked server "TRYLINK" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 7344, Level 16, State 1, Line 1

The OLE DB provider "MSDASQL" for linked server "TRYLINK" could not INSERT INTO table "[MSDASQL]" because of column "MEMO_FALSEPOSITIVE". Could not convert the data value due to reasons other than sign mismatch or overflow.

When i try to select a CLOB column in DB2 the following error is encountered:

OLE DB provider "MSDASQL" for linked server "TRYLINK" returned message "Requested conversion is not supported.".

Msg 7341, Level 16, State 2, Line 1

Cannot get the current row value of column "[MSDASQL].MEMO_VULNERABILITYSOLUTION" from OLE DB provider "MSDASQL" for linked server "TRYLINK".

Thanks in advance for any answer.

Ashwin

In Enterprise Manager when you go to Security – Linked Servers can you see DB2SRV name?
If yes when you expend this name can you see Tables?
If yes, when you click on it, can you see all tables?
If answer to any of these questions is ‘NO’ it means your linked is not configured properly.



Good Luck.

[iburyak]

Sorry I am not a DB2 programmer and can't help you with this but can you create a view on DB2 side where you convert to text this column before returning to SQL so conversion on SQL side would not require.

To update you probably can create a stored procedure on DB2 side which will do conversion for you.


Not sure if it is even possible but it is just a thought.

You can go to DB2 forum for help.

Good Luck.

[ashwingawande]

Hi,
Thanks for the reply. Well I had the same thought but the problem is there is no text data type in DB2 another way is varchar is there in DB2 but CLOB type data will not be accomodated in varchar so no sense in that. Another way i found is to have DB2 column get converted in XML data type which is also there in SQL Server 2005 so tried that but another problem is when i run select query using OPENQUERY to access xml data type from DB2 the error is
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
My query goes like this................

SELECT str_tmp.value('(/tbltmp/@Description) [1]', 'varchar(max)')
FROM OPENQUERY(VTDEV149, 'SELECT str_tmp.value('(/tbltmp/@Description) [1]', 'varchar(max)')' from tvng.tvnguser.tbltmp)

I don't understand where i am wrong.

Please suggest something if u can.

bye..
Ashwin

Sorry I am not a DB2 programmer and can't help you with this but can you create a view on DB2 side where you convert to text this column before returning to SQL so conversion on SQL side would not require.

To update you probably can create a stored procedure on DB2 side which will do conversion for you.


Not sure if it is even possible but it is just a thought.

You can go to DB2 forum for help.

Good Luck.