Solved

Receiving METADATA error when updating AS400 using IBMDASQL from MS SQL SERVER 2008 R2

Posted on 2010-11-24
6
2,763 Views
Last Modified: 2013-12-06
I am getting the following error message when I use the following TSQL statement to update a field on our AS400 (iSeries) table using a SQL Linked Server.  I am using SQL Server 2008 R2 with the IBM OLE DB provider IBMDASQL.  I can issue a command to update the AS400 table with a simple command setting one field back to itself and I do not receive any error messages.  The problem seems to be in one of the settings or with the provider in general when joining the SQL table.  I have provider options “Nested queries”, “Allow inprocess” and “Disallow adhoc access” checked on.  I have linked server options “Data Access”,  “Use Remote Collation” and “Enable Promotion of Distributed Transactions” checked on.  

Anyone have any suggestions?

Statement:

update SQLSERVER_TO_AS400.S105XMWM.LOANS.K667BL45
set CPYMT  = b.loanrec_CPYMT
from SQLSERVER_TO_AS400.S105XMWM.LOANS.K667BL45 a
inner join tblas400tables b
on a.CLOAN = b.loanrec_CLOAN
where logid = 7485

Error message:

The OLE DB provider "IBMDASQL" for linked server "SQLSERVER_TO_AS400" supplied inconsistent metadata. The object "(user generated expression)" was missing the expected column "Bmk1000".
0
Comment
Question by:RLKNOX
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 

Author Comment

by:RLKNOX
ID: 34207408
SQL Server
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 34212330
Please take a look at this IBM bulletin, "SQL Server Restrictions That Affect the Use of IBMDASQL" and evaluate whether these restrictions apply to your environment

http://www-01.ibm.com/support/docview.wss?uid=nas13405e887099cc85f86257315005f23dc
0
 

Author Comment

by:RLKNOX
ID: 34312779
Waiting on a solution that does not require using MS DB2OLEDB connector...
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 45

Expert Comment

by:aikimark
ID: 34313122
I recommend you click the Request Attention link and ask for some more expert visibility on this question.
0
 

Author Comment

by:RLKNOX
ID: 34314525
thanks.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34326822
@RLKNOX

Did you mean to close this question?  I thought you were going to ask for more expert eyes on this thread.
0

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question