Re: AS400 Linked Server - Error when performing UPDATE

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.