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.
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?
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')
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.
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')
Analysis manager won’t let me create dimension unless fact table filed has value init. Other words, how do I create MT dimension(dimension without member)
I am trying to create a calculated member for Cumulative sum using Fiscal Calendar using the below code:
WITH MEMBER [Measures].[No of Accounts Cumulative] AS 'Sum(PeriodsToDate([DimFinancialDate].[FiscalMonth].[(all)],[DimFinancialDate].[FiscalMonth].CURRENTMEMBER), [Measures].[No of Accounts])' SELECT   {[Measures].[No of Accounts],[Measures].[No of Accounts Cumulative]} ON COLUMNS,   [DimFinancialDate].[FiscalMonth].Members ON ROWS FROM [Acquisition] where [DimFinancialDate].[Year -  Quarter -  Month -  Date].[FiscalYear].&[2014-04-01T00:00:00];
Getting below OUTPUT:
No of Accounts No of Accounts Cumulative All 1763 1763 Apr 14 116 353 May 14 30 383 Jun 14 284 667 Jul 14 112 779 Aug 14 38 817
[code]....
Basically, It is taking all the members of the Fiscal date dimension to compute the output, But what i am expecting is to compute only for Fiscal calendar that is from April 2014 - March 2015.
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 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'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.
At the moment, I'm doing this:
Code Snippet
create member [MIA DW].[DATE TIME].[Date].[Yesterday] as [DATE TIME].[Date].&[2007-01-01T00:00:00]
select [Measures].members on 0, non empty [DATE TIME].[Date].members on 1 from [MIA DW] But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?
So I guess there's two questions:
1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)
2)How come the Yesterday member is not returned by the .members function?
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?
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.
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.
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.
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.
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?
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...
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?
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
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.
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.
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
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?
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?
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
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.
