[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1311
  • Last Modified:

Insert into as400 from linked server

is there a way to insert into the as400 via sql server linked server without turning on journaling?  everything i see says it has to be turned on, but i would like a solution in which i do not need it on (if possible).  i'm using sql server 2005

INSERT INTO LINKSERVER.as400server.libraryname.tablename
(tagno, timestmp, location, result)
VALUES
('750-JB-000','2011/04/19 16:01', 'PR', 1)

i get this message
"SQL7008: tablename in libraryname not valid for operation.

thanks
0
Fraser_Admin
Asked:
Fraser_Admin
  • 4
  • 2
1 Solution
 
Gary PattersonVP Technology / Senior Consultant Commented:
Just establish a connection with commitment control disabled, and that eliminates the requirement for journaling.  Post your connection string so I can see what provider you're using and I can help you with syntax if you don't know how to disable yourself.

- Gary Patterson
0
 
jimtpowersCommented:
When we still had our AS400, turning journaling on was the only way we could make it work. We opened tickets with both Microsoft and IBM and that was the only solution provided.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
We can almost certainly make inserts work without journaling, as long as we can control the COMMIT level of the connection to DB2/400 through connection parameters. The actual mechanism varies a bit from provider to provider, but the basic process is to set the connection commit level to NONE (NC).

This works with the native (IBM-supplied) ODBC, OLEDB, ADO, and .NET providers supplied with IBM Client Access (IBM iSeries Access, IBM i Access, etc).

I'm not surprised that jimtpowers didn't get a satisfactory response from IBM or MS on this.  Neither one plays very well with the other, in my experience.

For example, if you use the MSDASQL provider (ODBC provider), with the IBM-supplied ODBC driver, you would go into the ODBC Data Source Administrator for the DSN you are using, go to the Server Tab, Press Advanced, and select "Commit mode" of "Commit immediate (*NONE)".

http://msdn.microsoft.com/en-us/library/ms190479.aspx

- Gary Patterson
0
Independent Software Vendors: 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!

 
jimtpowersCommented:
I think I was stuck between the two not playing well together.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Yeah, the IBM guys point the finger at Windows, and the Windows guys point the finger at IBM.  About the only time they agree is when they both decide to blame "the network".

We've gotten really good at solving this cross-platform stuff on our own.

- Gary Patterson
0
 
SQLSergentMikeCommented:
Linked servers and the AS400, we ended up going direct on several projects using ADO and the IBM drivers, this seems to be the best approach. Linked servers are very unpredicatable between platforms, and for that reason we took the programming approach. Unless you have some magic bullet that fixes the linked servers issues between platforms, I would change paths.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
We certainly prefer a code-based approach, though we've had clients make applications work using linked servers, too.  

The IBM-supplied data providers for MS are very solid in recent years (just need to stay on top of IBM service packs and database PTFs as Windows keeps automatically updating!):

ODBC, OLEDB, and the .NET providers are all nice and stable (and don't forget the JTOpen / Java Toolbox JDBC drivers for the rest of the non-MS world), and IBM publishes some really nice resources that explain exactly how to use them, complete with sample code.  I'm particularly fond of the Redbooks, which are written by teams consisting of IBM customers, Business Partners, and IBM staff,and tend to take a practical, task-oriented approach, as opposed to dry, syntax-oriented "product documentation".

iSeries Access for Windows Database Programming (IBM iSeries Information Center)
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=%2Frzaik%2Frzaikcaapidb.htm

Integrating DB2 UDB for iSeries with Microsoft ADO.NET
http://www.redbooks.ibm.com/abstracts/sg246440.html

A Fast Path to AS/400 Client/Server Using AS/400 OLE DB Support
http://www.redbooks.ibm.com/abstracts/sg245183.html

- Gary Patterson
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now