We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Insert into as400 from linked server

Medium Priority
1,594 Views
Last Modified: 2012-06-21
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
Comment
Watch Question

Gary PattersonVP Technology / Senior Consultant
CERTIFIED EXPERT

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
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.
Gary PattersonVP Technology / Senior Consultant
CERTIFIED EXPERT

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
I think I was stuck between the two not playing well together.
Gary PattersonVP Technology / Senior Consultant
CERTIFIED EXPERT

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
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.
VP Technology / Senior Consultant
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.