AS400 To SQL With Different MEMBER Name

Apr 7, 2007

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')

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')

View 1 Replies

Getting The Last Member Of As400

Mar 14, 2004

Hi,

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!

View 3 Replies View Related

AS400 Member

Aug 7, 2006

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

View 5 Replies View Related

Can You CREATE A Member On The AS400 From SQL?

Apr 13, 2008

I 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!

View 1 Replies View Related

How To Select A Different Member Name When Transfering Data From AS400 To SQL

Apr 4, 2007

View 1 Replies View Related

A New Member Could Not Be Added To A Local Group Because The Member Has The Wrong Account Type

Mar 23, 2007

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG

Thanks

View 18 Replies View Related

A New Member Could Not Be Added To A Local Group Because The Member Has The Wrong Account Type

Mar 23, 2007

View 4 Replies View Related

Master Data Services :: How To Add A Reference To Another Entity Member When Creating A Member

Oct 23, 2014

I need to create a member that one of its Attributes (maybe my term is wrong) is a reference to another entity member named group
the code

createRequest.Members.MemberType = MemberType.Leaf;
createRequest.Members.Members = new System.Collections.ObjectModel.Collection<Member> { };
Member aNewMember = new Member();
aNewMember.MemberId = new MemberIdentifier() { Name = uag.groupName, MemberType = MemberType.Leaf };

[code]....

"The attribute data type is not valid".Is it wrong to add the reference as attribute? How can I embed the reference in the new member?

View 4 Replies View Related

SQL 2012 :: Delete Rows If Member Does Not Exists In The Member Table

Sep 24, 2015

I have a table where I need to delete rows if the member does not exists in the member table. Will the following syntax work ?

Delete T
FROM Event_Temp_Lead_Screen T
left join member M on ( M.MemberID = T.MemberID )
where
T.Gender is NULL

View 9 Replies View Related

AS400

Jan 4, 2002

Hi 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

View 1 Replies View Related

Sql To AS400

Jun 6, 2000

I 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 Related

As400

Apr 8, 2004

Hi, 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.... :(((

View 1 Replies View Related

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!

View 1 Replies View Related

IBM AS400 OLE DB Provider

Jun 8, 2000

Has anyone succcessfully configured aa AS/400 as a Linked Server with MS SQL 7.0 using IBMDA400 - IBM AS400 OLE DB Provider.
Thanks

View 2 Replies View Related

Best Way To Connect To An AS400 ?

Nov 30, 2000

Looking 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 View Related

As400 && SQL SERVER

Feb 15, 2005

I'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

View 3 Replies View Related

SQL Server And The AS400??

Feb 17, 2005

I 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

View 1 Replies View Related

How To Connect To AS400 Using VB6

Nov 9, 2005

Hi 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

View 2 Replies View Related

As400 To SS2K

Sep 11, 2007

Simplest, 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

View 20 Replies View Related

AS400 And SELECT *

Jul 31, 2007

I 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?

View 1 Replies View Related

Connecting To AS400

Apr 26, 2006

Hi,

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

View 2 Replies View Related

Writting Into AS400

Jul 2, 2007

Hallo

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?

View 4 Replies View Related

Lookup On AS400

Dec 14, 2006

Hi 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 ?

View 10 Replies View Related

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

View 2 Replies View Related

SSIS And DB2 On AS400

Feb 27, 2008

Hi,

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

View 8 Replies View Related

Transferring AS400 DB To SQL Server 7.0

Aug 27, 1999

I 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 Related

Link SQL 2000 To AS400

Sep 16, 2002

Hello

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

View 5 Replies View Related

Moving From An AS400 To SQL Server

Jun 24, 2004

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

ODBC Driver For AS400

Sep 1, 2004

Hi,

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

View 1 Replies View Related

AS400 Driver Issue

Oct 14, 2004

I 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!

View 2 Replies View Related

Can SQL Server Run In AS400 Machine?

Mar 21, 2006

Can SQL Server run in AS400 Machine?

Thanks.
-vince

View 3 Replies View Related

Connect To AS400 Table

Nov 6, 2007

Hi,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 Related

Linked Servers To DB2 And AS400

Apr 9, 2004

Hi!
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

View 1 Replies View Related