Does anybody have good documentation on setting up a linked server to a
DB2/AS400 database? I've been unable to set one up in Enterprise Manager or
through Query Analyszer. Thanks"sp_addlinkedserver"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
"Microsoft Host Integration Server 2000 Product Overview"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnhis/html/his_hiserver2000.asp
"Host Integration Server 2000 Resource Kit Chapter 14 - Deploying Data
Access"
http://www.microsoft.com/resources/documentation/host/2000/all/reskit/en-us/part3/hisrkc14.mspx
Cristian Lefter, SQL Server MVP
"blue_nirvana" <bluenirvana@.discussions.microsoft.com> wrote in message
news:3A853550-643E-468F-B244-8010A5FDA916@.microsoft.com...
> Does anybody have good documentation on setting up a linked server to a
> DB2/AS400 database? I've been unable to set one up in Enterprise Manager
> or
> through Query Analyszer. Thanks
Monday, February 20, 2012
Linked Server to cluster fails (sometimes)
Howdy.
Here's the question: why does my linked server function properly only part
of the time and how do I fix it?
Details-
I have a linked server set up in a production environment. The link is to a
database that is on the same server as the linked server, but I want to redi
rect the link to another copy of the database on a *cluster* elsewhere on th
e network. I am trying to t
est this by setting up a link to the production database on a test SQL Serve
r.
The linked server is used by another database-I will call this the "primary"
database. Generally the primary database is accessed via stored procedure c
alls and in some cases these stored procedures invoke stored procedures that
exist within the linked da
tabase. No writes are done through the linked server.
The linked server is set up to use a specified security context that is a re
ad-only SQL user that has appropriate permissions in the pertinent database
on the cluster.
The SQL Server on which I have created the linked server is *not* my develop
ment box. When I use Enterprise Manager running on my development box and cl
ick on "Tables" under the linked server, I get this error: "Error 17: SQL Se
rver does not exist or acce
ss denied." However, when I log directly onto the SQL Server box (e.g., via
Terminal Services) and do the same thing I am able to view the list of table
s in the linked database with no error.
I also have two .NET applications that touch the linked server. These applic
ations invoke the same stored procedures within the primary database which i
n turn remotely invoke the same stored procedures via the linked server. One
of these applications is a
"rich client" application that uses Windows forms controls deployed onto a w
eb page via Fusion technology and the other is a standard web application (A
SP.NET). The rich client application gets its data remotely via calls to a w
eb service while the data
access tier for the web application runs in the same process space as the ap
plication's server side code. The rich client application accesses data via
the linked server correctly while the web application reports the same error
as the one reported when I
try to view the linked tables on my development box via Enterprise Manager (
"SQL Server does not exist or access denied"). I need to get both of these
applications to work.
Can anyone shed any light on this problem?
Thanks in advance for any help anyone can offer.
MattHey Matt,
In addition to what Allan suggests:
If the linked server works when the cluster is failed over to one node and
not the other;
Check to see if one side has an alias created for the remote server.
Use the SQL Client network utility to view this on each node.
Or check the registry key;
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\ConnectTo
Otherwise, run the test while making a network trace on both the client
machine, and the Cluster.
Verify that it is using the same protocols as a connection made from the
local node of the cluster.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Allan:
Thank you for your help. I will forward your comments to the DBA who is hel
ping me. In addition, I will insert comments below.
Matt
"Allan Hirt" wrote:
> Some things to think about :
> 1. Make sure you are referencing the virtual server
> itself, not the address/IP of any of the individual nodes.
Hmmm. I am referencing the cluster by name, not IP, so I assume that is the
virtual server you mention. I apologize for being unfamiliar with clusters
, but this is my first exposure to them.
> 2. Remember that upon failover, any connection to the
> virtual server will probably be lost and you need to take
> that into account in your application/DB. You should test
> to see what happens in a failover and what it means to
> your app.
Are you talking about failover within the cluster? Do I need to manage conn
ections made via the Linked Server? I'm assuming that, if a failover occurs
either in the middle of a call across the linked server or just before the
call is made, then I will a
t least have an exception to handle. I'm also assuming that once the failov
er occurs functionality will be restored to the linked server since it was s
et up pointing to the virtual server. Is this correct?
> 3. If it is elsewhere on the network, make sure you have
> the routing to be able to get there.
This shouldn't be an issue.
> 4. By your error, going with #3, you either do not have
> access or the routing is possibly weird.
Well, the problem isn't occurring in conjunction with a failover. To my kno
wledge, no failovers have occurred during my testing. There seems to be som
ething different (under the seams) about the way my two applications use the
linked server, and this is
what I'm having trouble identifying.
>
>|||Kevin:
Thank you for your help. As with Allan's reply, I will forward this to the
DBA.
Like I mentioned to Alan, I don't believe the error is tied to a failover.
I will ask the DBA to do the trace as you suggested.
Matt
"Kevin McDonnell [MSFT]" wrote:
> Hey Matt,
> In addition to what Allan suggests:
> If the linked server works when the cluster is failed over to one node and
> not the other;
> Check to see if one side has an alias created for the remote server.
> Use the SQL Client network utility to view this on each node.
> Or check the registry key;
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\ConnectTo
> Otherwise, run the test while making a network trace on both the client
> machine, and the Cluster.
> Verify that it is using the same protocols as a connection made from the
> local node of the cluster.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Network traces aren't that difficult to do. They can really help solve
some of the communication related problems posted. Best advice is to make
a working trace and compare it to the failing one.
Read this kb, and it will help you understand what the traffic should look
like.
Q169292 The Basics of Reading TCP/IP Traces
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Here's the question: why does my linked server function properly only part
of the time and how do I fix it?
Details-
I have a linked server set up in a production environment. The link is to a
database that is on the same server as the linked server, but I want to redi
rect the link to another copy of the database on a *cluster* elsewhere on th
e network. I am trying to t
est this by setting up a link to the production database on a test SQL Serve
r.
The linked server is used by another database-I will call this the "primary"
database. Generally the primary database is accessed via stored procedure c
alls and in some cases these stored procedures invoke stored procedures that
exist within the linked da
tabase. No writes are done through the linked server.
The linked server is set up to use a specified security context that is a re
ad-only SQL user that has appropriate permissions in the pertinent database
on the cluster.
The SQL Server on which I have created the linked server is *not* my develop
ment box. When I use Enterprise Manager running on my development box and cl
ick on "Tables" under the linked server, I get this error: "Error 17: SQL Se
rver does not exist or acce
ss denied." However, when I log directly onto the SQL Server box (e.g., via
Terminal Services) and do the same thing I am able to view the list of table
s in the linked database with no error.
I also have two .NET applications that touch the linked server. These applic
ations invoke the same stored procedures within the primary database which i
n turn remotely invoke the same stored procedures via the linked server. One
of these applications is a
"rich client" application that uses Windows forms controls deployed onto a w
eb page via Fusion technology and the other is a standard web application (A
SP.NET). The rich client application gets its data remotely via calls to a w
eb service while the data
access tier for the web application runs in the same process space as the ap
plication's server side code. The rich client application accesses data via
the linked server correctly while the web application reports the same error
as the one reported when I
try to view the linked tables on my development box via Enterprise Manager (
"SQL Server does not exist or access denied"). I need to get both of these
applications to work.
Can anyone shed any light on this problem?
Thanks in advance for any help anyone can offer.
MattHey Matt,
In addition to what Allan suggests:
If the linked server works when the cluster is failed over to one node and
not the other;
Check to see if one side has an alias created for the remote server.
Use the SQL Client network utility to view this on each node.
Or check the registry key;
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\ConnectTo
Otherwise, run the test while making a network trace on both the client
machine, and the Cluster.
Verify that it is using the same protocols as a connection made from the
local node of the cluster.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Allan:
Thank you for your help. I will forward your comments to the DBA who is hel
ping me. In addition, I will insert comments below.
Matt
"Allan Hirt" wrote:
> Some things to think about :
> 1. Make sure you are referencing the virtual server
> itself, not the address/IP of any of the individual nodes.
Hmmm. I am referencing the cluster by name, not IP, so I assume that is the
virtual server you mention. I apologize for being unfamiliar with clusters
, but this is my first exposure to them.
> 2. Remember that upon failover, any connection to the
> virtual server will probably be lost and you need to take
> that into account in your application/DB. You should test
> to see what happens in a failover and what it means to
> your app.
Are you talking about failover within the cluster? Do I need to manage conn
ections made via the Linked Server? I'm assuming that, if a failover occurs
either in the middle of a call across the linked server or just before the
call is made, then I will a
t least have an exception to handle. I'm also assuming that once the failov
er occurs functionality will be restored to the linked server since it was s
et up pointing to the virtual server. Is this correct?
> 3. If it is elsewhere on the network, make sure you have
> the routing to be able to get there.
This shouldn't be an issue.
> 4. By your error, going with #3, you either do not have
> access or the routing is possibly weird.
Well, the problem isn't occurring in conjunction with a failover. To my kno
wledge, no failovers have occurred during my testing. There seems to be som
ething different (under the seams) about the way my two applications use the
linked server, and this is
what I'm having trouble identifying.
>
>|||Kevin:
Thank you for your help. As with Allan's reply, I will forward this to the
DBA.
Like I mentioned to Alan, I don't believe the error is tied to a failover.
I will ask the DBA to do the trace as you suggested.
Matt
"Kevin McDonnell [MSFT]" wrote:
> Hey Matt,
> In addition to what Allan suggests:
> If the linked server works when the cluster is failed over to one node and
> not the other;
> Check to see if one side has an alias created for the remote server.
> Use the SQL Client network utility to view this on each node.
> Or check the registry key;
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Client\ConnectTo
> Otherwise, run the test while making a network trace on both the client
> machine, and the Cluster.
> Verify that it is using the same protocols as a connection made from the
> local node of the cluster.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Network traces aren't that difficult to do. They can really help solve
some of the communication related problems posted. Best advice is to make
a working trace and compare it to the failing one.
Read this kb, and it will help you understand what the traffic should look
like.
Q169292 The Basics of Reading TCP/IP Traces
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Linked Server to cluster fails (sometimes)
Howdy.
Here's the question: why does my linked server function properly only part of the time and how do I fix it?
Details-
I have a linked server set up in a production environment. The link is to a database that is on the same server as the linked server, but I want to redirect the link to another copy of the database on a *cluster* elsewhere on the network. I am trying to t
est this by setting up a link to the production database on a test SQL Server.
The linked server is used by another database-I will call this the "primary" database. Generally the primary database is accessed via stored procedure calls and in some cases these stored procedures invoke stored procedures that exist within the linked da
tabase. No writes are done through the linked server.
The linked server is set up to use a specified security context that is a read-only SQL user that has appropriate permissions in the pertinent database on the cluster.
The SQL Server on which I have created the linked server is *not* my development box. When I use Enterprise Manager running on my development box and click on "Tables" under the linked server, I get this error: "Error 17: SQL Server does not exist or acce
ss denied." However, when I log directly onto the SQL Server box (e.g., via Terminal Services) and do the same thing I am able to view the list of tables in the linked database with no error.
I also have two .NET applications that touch the linked server. These applications invoke the same stored procedures within the primary database which in turn remotely invoke the same stored procedures via the linked server. One of these applications is a
"rich client" application that uses Windows forms controls deployed onto a web page via Fusion technology and the other is a standard web application (ASP.NET). The rich client application gets its data remotely via calls to a web service while the data
access tier for the web application runs in the same process space as the application's server side code. The rich client application accesses data via the linked server correctly while the web application reports the same error as the one reported when I
try to view the linked tables on my development box via Enterprise Manager ("SQL Server does not exist or access denied"). I need to get both of these applications to work.
Can anyone shed any light on this problem?
Thanks in advance for any help anyone can offer.
Matt
Hey Matt,
In addition to what Allan suggests:
If the linked server works when the cluster is failed over to one node and
not the other;
Check to see if one side has an alias created for the remote server.
Use the SQL Client network utility to view this on each node.
Or check the registry key;
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\ConnectTo
Otherwise, run the test while making a network trace on both the client
machine, and the Cluster.
Verify that it is using the same protocols as a connection made from the
local node of the cluster.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Allan:
Thank you for your help. I will forward your comments to the DBA who is helping me. In addition, I will insert comments below.
Matt
"Allan Hirt" wrote:
> Some things to think about:
> 1. Make sure you are referencing the virtual server
> itself, not the address/IP of any of the individual nodes.
Hmmm. I am referencing the cluster by name, not IP, so I assume that is the virtual server you mention. I apologize for being unfamiliar with clusters, but this is my first exposure to them.
> 2. Remember that upon failover, any connection to the
> virtual server will probably be lost and you need to take
> that into account in your application/DB. You should test
> to see what happens in a failover and what it means to
> your app.
Are you talking about failover within the cluster? Do I need to manage connections made via the Linked Server? I'm assuming that, if a failover occurs either in the middle of a call across the linked server or just before the call is made, then I will a
t least have an exception to handle. I'm also assuming that once the failover occurs functionality will be restored to the linked server since it was set up pointing to the virtual server. Is this correct?
> 3. If it is elsewhere on the network, make sure you have
> the routing to be able to get there.
This shouldn't be an issue.
> 4. By your error, going with #3, you either do not have
> access or the routing is possibly weird.
Well, the problem isn't occurring in conjunction with a failover. To my knowledge, no failovers have occurred during my testing. There seems to be something different (under the seams) about the way my two applications use the linked server, and this is
what I'm having trouble identifying.
>
>
|||Kevin:
Thank you for your help. As with Allan's reply, I will forward this to the DBA.
Like I mentioned to Alan, I don't believe the error is tied to a failover. I will ask the DBA to do the trace as you suggested.
Matt
"Kevin McDonnell [MSFT]" wrote:
> Hey Matt,
> In addition to what Allan suggests:
> If the linked server works when the cluster is failed over to one node and
> not the other;
> Check to see if one side has an alias created for the remote server.
> Use the SQL Client network utility to view this on each node.
> Or check the registry key;
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\ConnectTo
> Otherwise, run the test while making a network trace on both the client
> machine, and the Cluster.
> Verify that it is using the same protocols as a connection made from the
> local node of the cluster.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||Network traces aren't that difficult to do. They can really help solve
some of the communication related problems posted. Best advice is to make
a working trace and compare it to the failing one.
Read this kb, and it will help you understand what the traffic should look
like.
Q169292 The Basics of Reading TCP/IP Traces
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Here's the question: why does my linked server function properly only part of the time and how do I fix it?
Details-
I have a linked server set up in a production environment. The link is to a database that is on the same server as the linked server, but I want to redirect the link to another copy of the database on a *cluster* elsewhere on the network. I am trying to t
est this by setting up a link to the production database on a test SQL Server.
The linked server is used by another database-I will call this the "primary" database. Generally the primary database is accessed via stored procedure calls and in some cases these stored procedures invoke stored procedures that exist within the linked da
tabase. No writes are done through the linked server.
The linked server is set up to use a specified security context that is a read-only SQL user that has appropriate permissions in the pertinent database on the cluster.
The SQL Server on which I have created the linked server is *not* my development box. When I use Enterprise Manager running on my development box and click on "Tables" under the linked server, I get this error: "Error 17: SQL Server does not exist or acce
ss denied." However, when I log directly onto the SQL Server box (e.g., via Terminal Services) and do the same thing I am able to view the list of tables in the linked database with no error.
I also have two .NET applications that touch the linked server. These applications invoke the same stored procedures within the primary database which in turn remotely invoke the same stored procedures via the linked server. One of these applications is a
"rich client" application that uses Windows forms controls deployed onto a web page via Fusion technology and the other is a standard web application (ASP.NET). The rich client application gets its data remotely via calls to a web service while the data
access tier for the web application runs in the same process space as the application's server side code. The rich client application accesses data via the linked server correctly while the web application reports the same error as the one reported when I
try to view the linked tables on my development box via Enterprise Manager ("SQL Server does not exist or access denied"). I need to get both of these applications to work.
Can anyone shed any light on this problem?
Thanks in advance for any help anyone can offer.
Matt
Hey Matt,
In addition to what Allan suggests:
If the linked server works when the cluster is failed over to one node and
not the other;
Check to see if one side has an alias created for the remote server.
Use the SQL Client network utility to view this on each node.
Or check the registry key;
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\ConnectTo
Otherwise, run the test while making a network trace on both the client
machine, and the Cluster.
Verify that it is using the same protocols as a connection made from the
local node of the cluster.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Allan:
Thank you for your help. I will forward your comments to the DBA who is helping me. In addition, I will insert comments below.
Matt
"Allan Hirt" wrote:
> Some things to think about:
> 1. Make sure you are referencing the virtual server
> itself, not the address/IP of any of the individual nodes.
Hmmm. I am referencing the cluster by name, not IP, so I assume that is the virtual server you mention. I apologize for being unfamiliar with clusters, but this is my first exposure to them.
> 2. Remember that upon failover, any connection to the
> virtual server will probably be lost and you need to take
> that into account in your application/DB. You should test
> to see what happens in a failover and what it means to
> your app.
Are you talking about failover within the cluster? Do I need to manage connections made via the Linked Server? I'm assuming that, if a failover occurs either in the middle of a call across the linked server or just before the call is made, then I will a
t least have an exception to handle. I'm also assuming that once the failover occurs functionality will be restored to the linked server since it was set up pointing to the virtual server. Is this correct?
> 3. If it is elsewhere on the network, make sure you have
> the routing to be able to get there.
This shouldn't be an issue.
> 4. By your error, going with #3, you either do not have
> access or the routing is possibly weird.
Well, the problem isn't occurring in conjunction with a failover. To my knowledge, no failovers have occurred during my testing. There seems to be something different (under the seams) about the way my two applications use the linked server, and this is
what I'm having trouble identifying.
>
>
|||Kevin:
Thank you for your help. As with Allan's reply, I will forward this to the DBA.
Like I mentioned to Alan, I don't believe the error is tied to a failover. I will ask the DBA to do the trace as you suggested.
Matt
"Kevin McDonnell [MSFT]" wrote:
> Hey Matt,
> In addition to what Allan suggests:
> If the linked server works when the cluster is failed over to one node and
> not the other;
> Check to see if one side has an alias created for the remote server.
> Use the SQL Client network utility to view this on each node.
> Or check the registry key;
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\ConnectTo
> Otherwise, run the test while making a network trace on both the client
> machine, and the Cluster.
> Verify that it is using the same protocols as a connection made from the
> local node of the cluster.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>
|||Network traces aren't that difficult to do. They can really help solve
some of the communication related problems posted. Best advice is to make
a working trace and compare it to the failing one.
Read this kb, and it will help you understand what the traffic should look
like.
Q169292 The Basics of Reading TCP/IP Traces
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Linked Server to AS400 via ODBC Codepage Problem
Hi!
I am connecting to an as400 server (v5r2) from my sql 2000 server
using the oledb provider for odbc. In the provider string i use:
Driver={iSeries Access ODBC Driver};System=myServer;DefaultLibraries
=QG
PL;QUERYTIMEOUT=0;CCSID=1253;UNICODESQL=
1;TRANSLATE=0;
which works fine with english data i try to retrieve. However
some of the data is in greek. All the greek data comes back intelligible.
the codepage on the as400 system is 37.
Could any one please help me?
Thank you all in advance.Well,
In America it's common that when something is illegible, we say "It looks li
ke Greek to me". So maybe they figured if you want it back in Greek, they w
ould just give you junk
I am connecting to an as400 server (v5r2) from my sql 2000 server
using the oledb provider for odbc. In the provider string i use:
Driver={iSeries Access ODBC Driver};System=myServer;DefaultLibraries
=QG
PL;QUERYTIMEOUT=0;CCSID=1253;UNICODESQL=
1;TRANSLATE=0;
which works fine with english data i try to retrieve. However
some of the data is in greek. All the greek data comes back intelligible.
the codepage on the as400 system is 37.
Could any one please help me?
Thank you all in advance.Well,
In America it's common that when something is illegible, we say "It looks li
ke Greek to me". So maybe they figured if you want it back in Greek, they w
ould just give you junk
Linked server to AS400 using iSeries ODBC
Hello all,
I see several older posts on this topic but I can't find a resolution
outside of enable journaling on the as400 which is out of my control.
I have a linked server setup in sql server 2k to an as400 using
Microsoft OLE DB Provider for ODBC and the DSN uses the the iSeries
Access for Windows ODBC data source driver. Initially I tested this
in a DTS package and I was able to use the ODBC connection to select,
insert, update and delete. However, when I try to insert, update or
delete in query analyzer by using either four part name or openquery,
I can't do it. I get a pretty generic error...
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IOpenRowset::OpenRowset
returned 0x80004005: The provider did not give any information about
the error.].
But if I use MS Access and link the table and open it within access
and try to update a row I get something to the affect of iSeries
driver can not perform operation.
Permissions are correct, unique indexes are in place, is there
anything that can be done for this other than to enable journaling on
the AS400? I will try that but this will not be up to me so I'm
hoping there is something else that I can do. Any help would be
appreciated, thanks!I believe you have to enable journaling on AS/400 to be able to execute
statements that change data (insert, update, and delete). At least this is
what I had to do and it always worked.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
I see several older posts on this topic but I can't find a resolution
outside of enable journaling on the as400 which is out of my control.
I have a linked server setup in sql server 2k to an as400 using
Microsoft OLE DB Provider for ODBC and the DSN uses the the iSeries
Access for Windows ODBC data source driver. Initially I tested this
in a DTS package and I was able to use the ODBC connection to select,
insert, update and delete. However, when I try to insert, update or
delete in query analyzer by using either four part name or openquery,
I can't do it. I get a pretty generic error...
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IOpenRowset::OpenRowset
returned 0x80004005: The provider did not give any information about
the error.].
But if I use MS Access and link the table and open it within access
and try to update a row I get something to the affect of iSeries
driver can not perform operation.
Permissions are correct, unique indexes are in place, is there
anything that can be done for this other than to enable journaling on
the AS400? I will try that but this will not be up to me so I'm
hoping there is something else that I can do. Any help would be
appreciated, thanks!I believe you have to enable journaling on AS/400 to be able to execute
statements that change data (insert, update, and delete). At least this is
what I had to do and it always worked.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Linked server to AS400 using iSeries ODBC
Hello all,
I see several older posts on this topic but I can't find a resolution
outside of enable journaling on the as400 which is out of my control.
I have a linked server setup in sql server 2k to an as400 using
Microsoft OLE DB Provider for ODBC and the DSN uses the the iSeries
Access for Windows ODBC data source driver. Initially I tested this
in a DTS package and I was able to use the ODBC connection to select,
insert, update and delete. However, when I try to insert, update or
delete in query analyzer by using either four part name or openquery,
I can't do it. I get a pretty generic error...
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IOpenRowset::OpenRowset
returned 0x80004005: The provider did not give any information about
the error.].
But if I use MS Access and link the table and open it within access
and try to update a row I get something to the affect of iSeries
driver can not perform operation.
Permissions are correct, unique indexes are in place, is there
anything that can be done for this other than to enable journaling on
the AS400? I will try that but this will not be up to me so I'm
hoping there is something else that I can do. Any help would be
appreciated, thanks!
I believe you have to enable journaling on AS/400 to be able to execute
statements that change data (insert, update, and delete). At least this is
what I had to do and it always worked.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
I see several older posts on this topic but I can't find a resolution
outside of enable journaling on the as400 which is out of my control.
I have a linked server setup in sql server 2k to an as400 using
Microsoft OLE DB Provider for ODBC and the DSN uses the the iSeries
Access for Windows ODBC data source driver. Initially I tested this
in a DTS package and I was able to use the ODBC connection to select,
insert, update and delete. However, when I try to insert, update or
delete in query analyzer by using either four part name or openquery,
I can't do it. I get a pretty generic error...
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IOpenRowset::OpenRowset
returned 0x80004005: The provider did not give any information about
the error.].
But if I use MS Access and link the table and open it within access
and try to update a row I get something to the affect of iSeries
driver can not perform operation.
Permissions are correct, unique indexes are in place, is there
anything that can be done for this other than to enable journaling on
the AS400? I will try that but this will not be up to me so I'm
hoping there is something else that I can do. Any help would be
appreciated, thanks!
I believe you have to enable journaling on AS/400 to be able to execute
statements that change data (insert, update, and delete). At least this is
what I had to do and it always worked.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Subscribe to:
Posts (Atom)