|
|
|
date: Mon, 24 Sep 2007 13:04:04 -0400,
group: microsoft.public.sqlserver.odbc
back
OLEDB-ODBC-iSeries linked server. Select is fine, Create-Update-Delete do not work
I have AS400 database attached as a linked server to my SQL2005:
EXEC master.dbo.sp_addlinkedserver @server = N'THE', @srvproduct=N'THE',
@provider=N'MSDASQL', @datasrc=N'THE'
"THE" is the ODBC DSN pointing to the specific ip.
I have no problems retrieving the data from the linked server this way:
select * from THE.AS400US.MODISF.USLLS00F
When I'm trying to delete, insert or update, I'm getting error messages that
do not lead everywhere, example:
delete from THE.AS400US.MODISF.USLLS00F where LLSMA=0
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "THE" reported an error. The
provider did not give any information about the error.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "DELETE FROM "AS400US"."MODISF"."USLLS00F" WHERE
"LLSMA"=(0.)" against OLE DB provider "MSDASQL" for linked server "THE".
OpenQuery sintax doesn't work either:
delete openquery(THE, 'select LLSMA from AS400US.MODISF.USLLS00F where
LLSMA=0')
Msg 7345, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "THE" could not delete from
table "select LLSMA from AS400US.MODISF.USLLS00F where LLSMA=0". There was a
recoverable, provider-specific error, such as an RPC failure.
Login has full access for the table I'm experimenting with.
Any ideas how to troubleshoot this issue?
Regards,
D.
date: Mon, 24 Sep 2007 13:04:04 -0400
author: Dmitry Duginov am
RE: OLEDB-ODBC-iSeries linked server. Select is fine, Create-Update-Delete do not work
Hello Dmitry,
I understand that when you run delete/update query via linked server to
AS400 databases, you receive errors from OLE DB Provider.
Based on my experience, this issue could occur if there are some improper
configuration for DB2 client. I saw similar issues was resolved by adding
following lines to the db2cli.ini file:
DISABLEKEYSETCURSOR=1
DISABLEUNICODE=1
PATCH2=6
QUERYTIMEOUTINTERVAL=0
USESERVERKEYSETCURSOR=0
Also, a unique index is required to support update or delete statements
against remote tables.
If above suggestions doesn't help. You may want to contact IBM support for
more details on client configurations to see if there are other possible
clues.
Please let's know if you have any updates or comments. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
date: Tue, 25 Sep 2007 02:24:37 GMT
author: (Peter YangMSFT])
Re: OLEDB-ODBC-iSeries linked server. Select is fine, Create-Update-Delete do not work
Hello Dmitry,
Since the odbc driver for DB2/AS400 is provided by third party or IBM, you
may want to contact them for more information on how to configure the
options if they exist.
Also, you may want to enable journaling in AS/400 for that particular table
(physical file) for which you want to do an INSERT, UPDATE, or DELETE to
test the situation:
1. Create the journal receiver (CRTJRNRCV).
2. Create the journal (CRTJRN) by inputting the journal receiver created in
step 1.
3. Start the journal physical file (STRJRNPF) by inputting the journal and
journal receiver created in steps 1 and 2 and associating the journal to a
specific SQL table (physical file).
I appreciate your understanding on this. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
date: Thu, 27 Sep 2007 08:47:54 GMT
author: (Peter YangMSFT])
Re: OLEDB-ODBC-iSeries linked server. Select is fine, Create-Update-Delete do not work
""Peter YangMSFT]"" wrote in message
news:5bfzZMOAIHA.4728@TK2MSFTNGHUB02.phx.gbl...
> Hello Dmitry,
>
> Since the odbc driver for DB2/AS400 is provided by third party or IBM, you
> may want to contact them for more information on how to configure the
> options if they exist.
>
> Also, you may want to enable journaling in AS/400 for that particular
> table
> (physical file) for which you want to do an INSERT, UPDATE, or DELETE to
> test the situation:
Thank you for the information, but time constraints do not allow me to try
all those options.
What I end up doing is making those DELETEs "directly" from .NET application
using ODBC .NET managed provider, rather than calling MSSQL stored
procedures. This is ugly in terms of architecture, but it works. I failed to
find the proper set of options to make SQL server do this with the linked
server. That's pretty said, because now it's obvious there are no problems
on AS400 side, it is MSSQL issue.
Regards,
Dmitry
> 1. Create the journal receiver (CRTJRNRCV).
> 2. Create the journal (CRTJRN) by inputting the journal receiver created
> in
> step 1.
> 3. Start the journal physical file (STRJRNPF) by inputting the journal and
> journal receiver created in steps 1 and 2 and associating the journal to a
> specific SQL table (physical file).
>
> I appreciate your understanding on this. Thank you.
>
> Best Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> ======================================================
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================
>
>
>
>
date: Fri, 28 Sep 2007 09:52:29 -0400
author: Dmitry Duginov am
Re: OLEDB-ODBC-iSeries linked server. Select is fine, Create-Update-Delete do not work
Sorry If I don't understand, but it's like some problem with driver or
something like that.
Can you try test another conection and do the same delete, insert or update
query.
If you can, try use HIS2006.
See my last post. You'll understand! :
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-setup/8317/SQL-2005-and-Linked-Server-for-DB2#78e77f7577405uwe
Dmitry Duginov wrote:
>I have AS400 database attached as a linked server to my SQL2005:
>
>EXEC master.dbo.sp_addlinkedserver @server = N'THE', @srvproduct=N'THE',
>@provider=N'MSDASQL', @datasrc=N'THE'
>
>"THE" is the ODBC DSN pointing to the specific ip.
>
>I have no problems retrieving the data from the linked server this way:
>
>select * from THE.AS400US.MODISF.USLLS00F
>
>When I'm trying to delete, insert or update, I'm getting error messages that
>do not lead everywhere, example:
>
>delete from THE.AS400US.MODISF.USLLS00F where LLSMA=0
>
>Msg 7399, Level 16, State 1, Line 1
>
>The OLE DB provider "MSDASQL" for linked server "THE" reported an error. The
>provider did not give any information about the error.
>
>Msg 7320, Level 16, State 2, Line 1
>
>Cannot execute the query "DELETE FROM "AS400US"."MODISF"."USLLS00F" WHERE
>"LLSMA"=(0.)" against OLE DB provider "MSDASQL" for linked server "THE".
>
>OpenQuery sintax doesn't work either:
>
>delete openquery(THE, 'select LLSMA from AS400US.MODISF.USLLS00F where
>LLSMA=0')
>
>Msg 7345, Level 16, State 1, Line 1
>The OLE DB provider "MSDASQL" for linked server "THE" could not delete from
>table "select LLSMA from AS400US.MODISF.USLLS00F where LLSMA=0". There was a
>recoverable, provider-specific error, such as an RPC failure.
>
>Login has full access for the table I'm experimenting with.
>Any ideas how to troubleshoot this issue?
>
>Regards,
>D.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-connectivity/200710/1
date: Mon, 01 Oct 2007 14:55:23 GMT
author: Krisnamourt via SQLMonster.com u21487@uwe
|
|
