CODEWITHSUNDEEP
c#sql-serverodbc
user1577242
user1577242

Reputation: 413

Error when insert through a link server from sql server

I'm trying to insert some data from a c# application to an AS400 server.I made a DSN in Control panel,Data Sources :

Data source name : Newissues
Description :iSeries Access for Windows ODBC data source
Connection type : use odbc access mode

In C# I used OdbcConnection .My connection string is : 

DSN=newissues; UID=strUser; PWD=strPassword;DBQ=DBName; QueryTimeOut=0;

From C# I can open database and make select ,but when I try to insert or delete I received this message:

ERROR [HY000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - test in DBName not valid for operation.

My insert command is :

INSERT INTO DBName.test VALUES('0730','  10230526')

I try also to insert data from sql server where i have a linked server but i receive the same message error:

insert openquery(MyLinkServer,'select "IACO","IAPOLNUM" from DBName .test WHERE 1=0') VALUES('0730','  10230526')

Error:

OLE DB provider "MSDASQL" for linked server "MyLinkServer" returned message "[IBM]   [System i Access ODBC Driver][DB2 for i5/OS]SQL7008 - test in DBName not valid for operation.".
Msg 7343, Level 16, State 2, Line 1
The OLE DB provider "MSDASQL" for linked server "MyLinkServer" could not INSERT INTO table "[MSDASQL]".

Can somebody help me to resolve this problem? Thanks

Upvotes: 0

Views: 2821

Answers (4)

sweet_potatoe
sweet_potatoe

Reputation: 7

using (iDB2Transaction db2Transaction = db2Command.Connection.BeginTransaction(IsolationLevel.Chaos))
{
  db2Command.Transaction = db2Transaction;
  Update/Insert/Delete
}

db2Command = iDB2Command

Note: IsolationLevel.Chaos is not recommended, because on the IBM i this results in a transaction which autocommits, and cannot be rolled back. Try other values in the IsolationLevel enum if it's gonna work.

Upvotes: 1

Gats
Gats

Reputation: 3462

Don't you have to reference the schema for a full qualified insert?

INSERT INTO DBName.dbo.test VALUES('0730','  10230526')

Upvotes: 0

steve cook
steve cook

Reputation: 3224

If in doubt, google the error: http://www.querytool.com/help/876.htm

Error SQL7008 while updating a DB2 for iSeries table

When updating a DB2 for iSeries table you can get error

SQL7008 -(table) in (file) not valid for the operation

This occurs because the table you are trying to update is not being journalled, and your update is being run within a transaction.

There are a number of ways of resolving this problem:

  • switch off Options > Run SQL > Safe Update Mode. When Safe Update mode is selected, AQT will run Updates and Deletes within a transaction.
  • ensure Options > Technical Parameters > Auto Commit is checked.
  • if you are using DB2/Connect, ensure that AutoCommit is set to Yes. This is configured with the DB2 Configuration Assistant.
  • If you are using DB2 Client Access Express, set the Commit mode to Commit immediate (*NONE ). This is configured on the ODBC Configuration for your Datasource.

Did you try all these already?

Upvotes: 1

Romesh
Romesh

Reputation: 2274

Here is the Reference

Change your connection string and add the blow code

CommitMode=0

Upvotes: 2

Related Questions