× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Dave,

When you define the linked server, you specify what userID to use to connect.

As far as an example....

I don't have goods ones right at hand, since I was working with this
at a prior job.

However, a bit of googling leads to:
update openquery('my_linked_server, 'select column_1, column_2 from
table_schema.table_name where pk = pk_value')
set column_1 = 'my_value1′, column_2 = 'my_value2′;

and
delete from openquery('my_linked_server, 'select * from
table_schema.table_name where pk = pk_value')

A quick test,
delete openquery(FALCON, 'Select * from CINTASDTA.INACTCAB where
locnbr = ''00014'' and
cusnbr = 5 and untnbr = 11');

Worked fine.

Note, if I recall correctly, the table you're modifying as to have a
unique (primary?) key defined.

Also, with the openquery() what's happening is the results set is
pulled backed to MSSQL before the rows are deleted and/or updated.
Thus performance isn't great.

I seem to recall there's another way, that performs better but has
some other limits, but I can't remember the details.

Charles

On Tue, Jun 9, 2009 at 12:40 PM, Prowak, Dave<dProwak@xxxxxxxxxxxxxxxxx> wrote:
< You can update tables via the linked server.
< It's not quite as easy as updating a local MS SQL table, but you can do it.

Can you provide some example code?

I can run SELECTS, but my UPDATE give me an error:
" the current user does not have permissions on that object."

Any idea how to determine what user the SQL query uses when it connects to the AS400?

TIA,
Dave


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Tuesday, June 09, 2009 12:29 PM
To: Midrange Systems Technical Discussion
Subject: Re: AS400 Linked Server - Error when performing UPDATE

Incorrect.

You can update tables via the linked server.

It's not quite as easy as updating a local MS SQL table, but you can do it.


Charles

On Tue, Jun 9, 2009 at 11:17 AM, Pat
Landrum<PLandrum@xxxxxxxxxxxxxxxxxxxxxxx> wrote:
Dave,

If I recall correctly, you can only "retrieve but not update" data on
the iSeries from a linked server connection. You could create a stored
procedure to do the updating.

Regards,
Pat Landrum
Senior Programmer/Analyst
Hanover County Public Schools
200 Berkley Street
Ashland, VA 23005
Email: plandrum@xxxxxxx
Phone: 804-365-4658 Fax: 804-365-4628
Never trust a computer you can't throw out a window - Steve Wozniak

Notice: This message or any accompanying documents may contain
confidential or privileged information of Hanover County Public Schools.
If you are not the intended recipient, disclosure, copying or
distribution is strictly prohibited by state and federal law. If you
received this message in error, please notify the sender as soon as
possible.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of David Prowak
Sent: Tuesday, June 09, 2009 10:49 AM
To: midrange-l@xxxxxxxxxxxx
Subject: AS400 Linked Server - Error when performing UPDATE

Hi,
I've got my AS400 setup as a linker server to my SQL Server 2005.

When I run a "simple" update (I am updating a char field), I get this
error:

*OLE DB provider "MSDASQL" for linked server "AS400 HSGLIB" returned
message
"Unspecified error".*

*OLE DB provider "MSDASQL" for linked server "AS400 HSGLIB" returned
message
"[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0440 - Routine
SQLSTATISTICS in SYSIBM not found with specified parameters.".*

*Msg 7311, Level 16, State 2, Line 1*

*Cannot obtain the schema rowset "DBSCHEMA_INDEXES" for OLE DB provider
"MSDASQL" for linked server "AS400 HSGLIB". The provider supports the
interface, but returns a failure code when it is used.*

Does anyone know what causes this error or what the workaround is?

TIA,

Dave
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.