Orbiting the AS400

I need to link to tables in an AS400 on a customer site. They use IBM CA and
have the latest 5.2 version. (IBM CA includes both ODBC and OLE DB drivers).
I tried configuring the IBM OLE DB for AS400 driver using both graphical and
sp_addlinkedserver, but with no success. The best I can get is a link that
appears to connect without errors but the clicking on the tables or vies
icon I just get an empty recordset back. I have not been able to figure out
exactly what **should** be the correct configuration, in particular most
examples give connection strings, and this option does not appear to be
available with this driver:-(
Worse is to come, thinking that the problem may just be with access to the
schema information, I tried running a simple query against the AS400 even
thougth the list of tables was empty. SQL Server crashed, and it was a
production system. So, I installed CA on my laptop and tried again using my
"local" db. Identical.
Surfing around for help I found that many people seem to have similar
problems, and the suggested cure was to use the OLE DB for ODBC driver, and
it **almost** works. I see the tables and can run queries, but results are
sometimes incomplete. Selecting a table of 469 rows in query analyzer I only
got back 401 rows. Tinking it was a buffer problem I tried changing the
number of columns, but always got 401, except when I used Select * which
yields 392. At least one field is unique and I have tried using this on it's
own.
The ODBC DSN runs fine and returns 469 rows in queries sent from Access and
Excel.
Back to qury analyzer, Select Count(AnyField) always returns 469 rows,
suggesting that it is a datagrid/display problem, so I tried something like:
Select sqltable.GPART from sqltable where Not Exists (Select GPART from
as400table )
this returns all the GPARTs in the sqltable that are missing from the
"Select * from as400 table".
So, I surfed for solutions to this problem, and found that I was not alone
with this type of problem either. Using OLE DB for ODBC with the CA ODBC
driver can return missing rows.
Suggested cures were updating the ODBC driver (have been using latest and
greatest version from outset) or.....switching to OLE DB.
So having done the whole loop, I wonder if anybody had any idea where I can
go from here. I would very much like to hear if **anybody** has ever
succesfully linked in an AS400.
BTW, I know there is a Microsoft OLE DB driver for the AS400, but as far as
I can make out it requires an SNA server installation. Is this correct? I
have seen the license fee is around $2500, which is a bit steep if all you
are interested in is the OLE DB driver!Hi Rog,
Thanks for your post. The Microsoft OLE DB Provider for DB2, distributed
with Microsoft? Host Integration Server 2000, allows Microsoft SQL Server?
2000 distributed queries to query data in DB2 databases. The network
components needed to communicate with an IBM computer run in an SNA network.
For more information regarding Microsoft OLE DB Provider for DB2, please
refer to the following articles:
OLE DB Provider for DB2
http://msdn.microsoft.com/library/d...-us/acdata/ac_8
_qd_12_1gvm.asp
218590 INF: Configuring Data Sources for the Microsoft OLE DB Provider for
DB2
http://support.microsoft.com/?id=218590
216428 Configuring Microsoft ODBC Driver for DB2
http://support.microsoft.com/?id=216428
Also, as to the problem with IBM OLE DB for AS400 driver provided by IBM, I
am not familiar with the detailed information of this driver, therefore,
the help I am able to provide is very limited. For the best help, I suggest
you talk with IBM support engineers about this driver problem.
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Oracle-style exception handling in SQL2000

Hello!

I'm working on a project that migrates an Oracle based DB application into SQL2000 server.

The original version of the application uses the Oracle exception-handling mechanism. In SQL2000 I wasn't able to find exception handling. Of course, there is an error handling mechanism, but it is far less sophisticated.

The raised error doesn't exit the procedure, neither propagate the "error state" through the whole call-stack, which means that I have to simulate the exception handling by putting error check after virtually all the DB access statements, which is a painstaking job.

Is there any better solutions?

Thanks for your help!

BalageNope..

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 1
Select @.Error_Type = 50001
GOTO Load_Ledger_Init_sp_Error
END

If @.Month1 = '99' Or @.Month2 = '99' Or @.Month3 = '99'
BEGIN
SELECT @.Error_Loc = 2
SELECT @.Error_Message = 'Month Not set for File name. Check System Variables. Values are: '
+ ' Proof_Year=~' + @.Proof_Year + '~'
+ ' Proof_Quarter=~' + @.Proof_Quarter + '~'
SELECT @.Error_Type = 50002
GOTO Load_Ledger_Init_sp_Error
END

One check for system error, one for business logic error|||We are a small company and we specialise in Database Migration. We have our toolbox that we have developed after long years of research and experience. However, even then, there were these hard nuts that we could never crack.

Of late, we have found a powerful utility in a product called Vaman DataServer. This product is essentially a DBMS, but what sets them apart, is their utility to migrate data from any source to any destination. I was a little sceptical to begin with, but eventually I figured that this tool is a nifty little thing that every migration company can use.

Right now it is free, but we are in talks with them for a commercial version, which they say migrates DDLs as well.

You could check out this link
http://www.vaman.net/vmndataserver.asp

You need to login for the download.

Hope this helps.

Oracle not supported?

I installed de trial version of SQL Server 2005 Workgroup, then created a
small report using a Oracle Database.
In Visual Studio the report previews fine but after being deployed to SSRS
i't tells me that the data extension is not registered.
Is this a trial version issue or is the Oracle support for SSRS have to be
configured after instalation?
Juan Ignacio Herreralook at this page:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
the workgroup edition support only data source against the same SQL Servre
installation only.
you can't directly access Oracle or any external source.
but... maybe you can create a linked server to your oracle database and
create your queries using the openquery SQL command.
the queries will come from your local server and not from your oracle
database.
"Juan Ignacio Herrera" <nacho(quitar)@.delta.com.gt> wrote in message
news:uQ8sNyvGGHA.3176@.TK2MSFTNGP12.phx.gbl...
>I installed de trial version of SQL Server 2005 Workgroup, then created a
> small report using a Oracle Database.
> In Visual Studio the report previews fine but after being deployed to SSRS
> i't tells me that the data extension is not registered.
> Is this a trial version issue or is the Oracle support for SSRS have to be
> configured after instalation?
> Juan Ignacio Herrera
>

Oracle Datasource with sql express version

Hi,
do you know if it's possible to connect Reporting Services, installed
with the sql express version, to an oracle datasource.
The oracle server is on the same machine.
This work fine in dev environnement but the deployment failed with this
message: "provider is not registered on the local machine".
Thank'sI don't think so. I think the express version is for reporting against
express data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<amfred@.gmail.com> wrote in message
news:1156321685.686065.70410@.m73g2000cwd.googlegroups.com...
> Hi,
> do you know if it's possible to connect Reporting Services, installed
> with the sql express version, to an oracle datasource.
> The oracle server is on the same machine.
> This work fine in dev environnement but the deployment failed with this
> message: "provider is not registered on the local machine".
> Thank's
>