RE: AS400 Linked Server - Error when performing UPDATE

["Prowak, Dave" <dProwak@xxxxxxxxxxxxxxxxx>]

< 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.