Best Way To Connect To An AS400 ?
Nov 30, 2000Looking for the fastest way to transfer data from an AS400 to a SQL server 7.0 database. Anyone with experience doing this?
View 3 Replies
ADVERTISEMENT
How To Connect To AS400 Using VB6
Nov 9, 2005Hi All,
I had tried this connection before using ODBC to connec to AS400 using the SQL statment "SELECT * FROM TableName1 LEFT JOIN TableName2 ON fieldname1=fieldname2" but Im using the same statement but only one table to be queried and is giving me an error, I use this one :
...
strAs400="DSN=DsnName"
connAS400.Open strAS400,UserID,Password
rsAS400.activeconnection=connAS400
rsAS400.Open "SELECT * FROM TableName1 WHERE fieldDate >=" & RangeDateFr
" AND TableName1 <=" & RangeDateTo ,,adopendynamic,adlockoptimistic
...
On the above codes, I dont know what might wrong. It is giving me an error message ...SQL0104 - token fieldDate was not valid. Valid Tokens: FOR WITH FETCH...
Please help if someone knows this. I would appreciate it a lot...
God BLess and best regards,
Ronald
Connect To AS400 Table
Nov 6, 2007Hi,Is there anyway to connect to AS400 table (something like link table feature in MS Access). OR. I have to use the DTS package and schedule it to update in intervals, this i am not preferring due to the performance issue ? your help highly appreciate thanksMurali.
View 1 Replies View RelatedBest Way To Connect To AS400 Machine??
Nov 28, 2007
Hi All,
What is the best way to connect to a AS400 IBM machine from SSIS or SQL2005. Then I want to write those libraries to a SQL2005 Database
Thanks in advance,
Tim
AS400
Jan 4, 2002Hi Gurus.
One of my client is using AS400 machine and wants to migrate from flat files to SQL server/Oracle. But they do not want to buy a new hardware. They like to use the same machine.
Is there any Emulator or simulator available for AS400 machine to simulate NT or Alpha environment?
Any suggestions?
-MAK
Sql To AS400
Jun 6, 2000I was wondering if anyone could give some insight into the tools or applications needed to utilize a AS400 to query info. from a sql database to gain performance and reliability. I need to know about methods for connection, library changes needed, software etc. Please Help,I do not have much experience with databases.
View 1 Replies View RelatedAs400
Apr 8, 2004Hi, all!
I try run DTS from SQL with connection to AS400 (Client Access 32-bit ver. 8).I know this should be about 9000 records, but after 5000 records
it's stopped.
Plz , explain!
I'll gone to be crazy.... :(((
FTP From AS400
Jul 7, 2006 I have been handed the directive that I am to ftp flat files from our AS400 to the SQL server where the web interfaces will read the data.
I need to know if anyone out here knows how to automate the FTP download from the AS400 system.
I don't know any UNIX and even if I did the SQL box is also running a 3rd party shipping label system that disallows the use/installation of MSK Toolkit.
I thought I could use the FTP Task in the DTS package but I don't know what to put for the internet location and this is certainly not a Mapped path.
Please Help!
IBM AS400 OLE DB Provider
Jun 8, 2000Has anyone succcessfully configured aa AS/400 as a Linked Server with MS SQL 7.0 using IBMDA400 - IBM AS400 OLE DB Provider.
Thanks
Getting The Last Member Of As400
Mar 14, 2004Hi,
I need some help here. I am trying to do a DTS from As400 from SQL Server 2000. However, the file that I require have members. IF I just do a select statement, it will retrieve the earliest member.
Does anyone know how I can access the latest member?
Thanks a lot!
As400 && SQL SERVER
Feb 15, 2005I'm trying to connect SQL SERVER with my AS/400.
I linked my as400 with linkedserver.
When I execute a query with analyse query it works fine, but if I make a store procedure as schedule this job to get information from my as400 I got this msg:
Executed as user: NT AUTHORITYSYSTEM. OLE DB provider 'IBMDA400' reported an error. Access denied. [SQLSTATE 42000] (Error 7399) OLE DB error trace [OLE/DB Provider 'IBMDA400' IUnknown::QueryInterface returned 0x80070005: Access denied.]. [SQLSTATE 01000] (Error 7300). The step failed.
Why? anyone have any idea?
thx
SQL Server And The AS400??
Feb 17, 2005I have been tring to connect to the as400 through the IBM ODBC (IBMDA400),
but have run into a wall. I was wondering, could i set up the 400 in SQL Server 2K as a DB or Table or something and maybe access it through SQLOLEDB?
Thanks
fvlmasl2
As400 To SS2K
Sep 11, 2007Simplest, fastest way to copy 7 tables from as400 to SS2K weekly. Any idea?
I have a as400 server and 2 SS.
as400
SSbox1: db1 and db2 (two databases)
SSbox2: db2 (one database called db2)
SSbox1: db1 is getting data from as400 then sending it into db2 daily.
SSbox2: DTS is copying everything from SSbox1.db2 into SSbox2.db2 weekly.
I wanna simplify process from SSbox2. Maybe a linked server from SSbox2.db2 to SSbox1.db2?
let me know if you don't understand.
=============================
http://www.sqlserverstudy.com
AS400 And SELECT *
Jul 31, 2007I am migrating from 2000 to 2005 and dts to SSIS on several projects. I have not been able to successfully (without error) pull from an AS400 table using "SELECT *". If I reference all field names, it works fine. SELECT * actually works in the sense that it pulls over all the data, but it fails at the end, almost like an unepected end of stream. Normally it wouldn't be a problem tp qualify each field, but when I do the data gets sorted. In this instance I can't have it sorted because (without going into the stupid details) the production needs to match the development to suit my boss.
Connection = .Net Provider for OleDb/IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider
Data Source = DataReader Source (I have yet to be able to use and OLE Source for AS400 without error, although my colleagues can - weird)
Here is the error I get when I change:
SELECT FieldName1, FieldName2, FieldName3 FROM LIBRARY.FILENAME
to
SELECT * FROM LIBRARY.FILENAME
Error: 0xC0047038 at DTF_LoadSqlServer_BOSS_Tebosspf8H, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE_SRC_Tebosspf8H" (4492) returned error code 0x80004003. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at DTF_LoadSqlServer_BOSS_Tebosspf8H, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
I
Any suggestions? Is there a property on the task or connection that will fix this? Or any idea why I can't use an OLE Source to pull from AS400?
AS400 To SQL With Different MEMBER Name
Apr 7, 2007I have been trying to transfer some data from a file located in a AS400 Server to SQL , but the file has more than one Member Name. I'm not sure how to specify a different member name on the SQL query . Please help.
The name of the file is:
Library = MTGLIBP2
File Name = CHSAVQPL
Member Name = INS
this is the query I have so far but I still need to reference the Member Name
SELECT *
FROM OPENQUERY(AS400PL,'SELECT * FROM mtglibp2.CHSAVQPL')
I have been trying to transfer some data from a file located in a AS400 Server to SQL , but the file has more than one Member Name. I'm not sure how to specify a different member name on the SQL query . Please help.
The name of the file is:
Library = MTGLIBP2
File Name = CHSAVQPL
Member Name = INS
this is the query I have so far but I still need to reference the Member Name
SELECT *
FROM OPENQUERY(AS400PL,'SELECT * FROM mtglibp2.CHSAVQPL')
Connecting To AS400
Apr 26, 2006Hi,
I am trying to transfer information from the AS400 to SQL Server tables but I am having problems trying to connect and get information from the AS400. In the data source I select the iseries connection and type in the ip address, username, and password. When I click the test connection button, it connects but under the initial catalog all I get is the computer name. If anyone has any solutions to my problem that would be great
Thnaks
Brian
AS400 Member
Aug 7, 2006I need to query data through SSIS from what I was told is an AS400 DB2 member table. I am assuming this is a sub-table of the main table. I was going to write a correlated sub-query in SQL to get this data, however our AS400 contracted programmer says that there is an easier way and she pointed me to the main table's member. I do not know how to go about accessing this.
Has anybody had experience with this? The AS400 programmer is familiar with SQL syntax, however she does not know how to have SQL grab the data from a member table.
If all else fails, I will just construct my correlated sub-query.
Thanks for the information.
Writting Into AS400
Jul 2, 2007Hallo
We got a €œSql Server€? and an €œAS400€? System. I can Read the information from the AS400 using a DataReader- Source my problem ist that I can not manage to write in the €œAS400€?. Using a DataReader- Destination I am not able to specify the destination table.
Does any one know what I am missing to Configure?
Lookup On AS400
Dec 14, 2006Hi everybody,
I'm new in SSIS and I'm trying to activate a lookup on a table reside on DB2/AS400. Iget the following message when I try to join the input column to the lookup column:
TITLE: Microsoft Visual Studio
------------------------------
The following columns cannot be mapped:
[STOREK_S, STOREK_S]
One or more columns do not have supported data types, or their data types do not match.
------------------------------
BUTTONS:
OK
------------------------------
I know both columns have the same format on the DB2 table.
What I'm doing wrong ?
P.S: I'm using the IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider as connection.
If I try with the Microsoft OLE DB Provider for DB2, I was unable to get the list of my table on teh AS400.
Could you help me ?
IBM DB2/AS400 And Parameters
Oct 11, 2007
Hi All,
Does anyone know how to use parameters in Reporting Services with IBM/DB2? Keeps giving me errors whenever I try to use parameters (@).
Regards,
Joseph
SSIS And DB2 On AS400
Feb 27, 2008Hi,
Getting this error. Any ideas?
Test connection failed because of an error in initializing provider. The host resource could not be found. Check that the Initial Catalog value matches the host resource name. SQLSTATE: HY000, SQLCODE: -360
Thanks
Transferring AS400 DB To SQL Server 7.0
Aug 27, 1999I have been asked to make it possible for our SQL 7 server to pull infromation from AS/400 and utilize it in web applications, and such. Is there any way of doing this? I have heard that you can export the data on the as400 to a DB2 file, and import it with SQL. Is this the only way of access info off of the 400?
View 2 Replies View RelatedLink SQL 2000 To AS400
Sep 16, 2002Hello
I am trying to link SQL2000 to AS400. I have created the link and can access the data on the Server, but when I try to access from a client I get the following error:
Error 7399: OLE DB Provider 'MSDASQL' Reported an error. Data Source Name not found and no default driver specified.
Has anyone run into this and if so does anyone have a solution to this problem?
Any help would be greatly appreceiated.
Thanks
Phil
Moving From An AS400 To SQL Server
Jun 24, 2004Hello...Our company has been on an AS400 for eternity. We have chosen a new erp package called Syteline 7 which runs on 5 servers on of them being a SQl server. I currently have 1 IT person on staf now. Can anyone tell me what my workload for my one IT person maintaining these 5 servers be? I would have 35 users and 50 PC's in total.....also does anyone have any negative comment about Syteline 7?Thanks
View 1 Replies View RelatedODBC Driver For AS400
Sep 1, 2004Hi,
I am presently using Client Access ODBC driver (32-bit) to connect to the AS400. I have set up a linked server that enables me to run queries against the AS400 using the driver. However I seek to have a driver that could give better performance. Right now I can extract 6 million rows from the AS400 table in like 2 hrs. Now is there an ODBC driver that can do better than that? Also I seek an evaluation edition of the driver if possible. Moreover I am the only developer and so a single user license is what I can have my supervisor budget.
Thanks,
Vivek
AS400 Driver Issue
Oct 14, 2004I am connecting to an AS400 from SQL Server using the iSeries Client Access. I am getting the following error when attempting to select from 1 table:
Server: Msg 7317, Level 16, State 1, Line 1 OLE DB provider 'MSDAORA' returned an
invalid schema definition.
The select looks something like: SELECT * FROM <instance>..<owner>.<tablename>
Selecting a subset of the fields (instead of all) does not make a difference.
All other table selects work fine - this is only a problem for one table
Any ideas? Your insight would be greatly appreciated. Thanks!
Can SQL Server Run In AS400 Machine?
Mar 21, 2006Can SQL Server run in AS400 Machine?
Thanks.
-vince
Linked Servers To DB2 And AS400
Apr 9, 2004Hi!
I would like to set up linked servers to DB2 and AS400 in SQL server and update the tables in these two databases via stored procedures in SQL servers.
I have read articles on Microsoft site which indicate installing Microsoft Host integration server and use SNA server 4.0 Service pack 4.0 to
configure DB2OLEDB drivers.
Could any one suggest how do go about doing this.
Do I need to buy Host integration server or is there any other way to do it.
your help is much appreciated
Nalina
Querying AS400 Using OPENQUERY
Apr 16, 2004Im currently trying to query an AS400 table which I know contains 18 rows. However when executing a query through Query Analyzer' using
SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT col1,col2,col3 FROM DS.TABLE')
I'm only getting one row returned.
Has anyone any experience of this kind of issue, and if so a resolution?
Cheers
Can You CREATE A Member On The AS400 From SQL?
Apr 13, 2008I am trying to CREATE a member in a file on the AS400 using SQL. The member name is a variable and will change everytime the Insert command is issued. I have tried using the Alias command, but I am either doing something wrong, or it is not meant to be used to create a member that had not existed prior to the Alias statement.
On the As400, the command to add a member is ADDPFM. Then an Override Data base file command (OVRDBF) is issued to point to the right member.
Any help would be greatly appreciated!
Thanks!
Import Data From AS400
Jul 1, 2007Hello,
I have de same problem that you have to get data from AS/400 to SQL Server 2005 using the SQL Server Import and Export Wizard.
Did you successfully? If yes how please?
I have used:
IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider,
IBM DB2 UDB for iSeries IBMDARLA OLE DB Provider,
IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider,
Microsoft OLE DB Provider for DB2
Regards
Rui Martins Santos
RMS
INSERT INTO AS400 - NUMERIC Value
May 11, 2007Hello!
I try to use "Execute SQL Task" to insert a numeric value into AS400.
The following command works:
INSERT INTO "MSSPRDDAT"."MSSDATE"
VALUES 1070528
But I need to use a parameter insteed. I have tried several Data Types, but non seems to work.
When I use NUMERIC, LARGE_INTEGER, ULARGE_INTEGER or NVARCHAR I get the following error message:
[Execute SQL Task] Error: Executing the query "INSERT INTO "MSSPRDDAT"."MSSDATE" VALUES ?" failed with the following error: "SQL0301: Input host variable DATESTR??? or argument 7 not valid. Cause . . . . . : The value in relative position 7 in the statement is a type that is not compatible with the requested operation. The value is host variable DATESTR???, entry 7 in an SQLDA, or argument DATESTR??? in a CALL statement. A name *N indicates that a user's SQLDA was used or that a constant or special register was specified on the CALL statement. Recovery . . . : Do one of the following and try the request again: -- Use a host variable that is the correct type. -- Specify an argument in the CALL that is the correct type. -- Change the type specified for parameter 7 in the DECLARE PROCEDURE statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
When I use DECIMAL I get the following error message:
[Execute SQL Task] Error: Executing the query "INSERT INTO "MSSPRDDAT"."MSSDATE" VALUES ?" failed with the following error: "The type is not supported.DBTYPE_DECIMAL". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Can anyone help me?
Best regards,
Tina
Syntax OpenRowset With AS400?
Oct 25, 2007
Hi All,
Does anyone know the syntax of OpenRowSet when used to connect to AS400?
Regards,
Joseph