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

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

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

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

Select Statement Not Importing All Rows From AS400

Oct 4, 2006

Hello all,
I am trying to select a specific amount of rows from an AS400 table and when I do so it only shows a specific amount of rows regardless of how many there actually is. If we run it multiple times, it displays the same amount of rows. We went into the iSeries ODBC and changed the timeout setting and the Record Blocking Size (increased it) and it did pull more rows but still not what we were looking for. We suspect it has something to do with pulling the specific amount of blocks and instead of continue to pull, it stops when it reaches the first blocking limit. I can however pull the information through MS Access with the same settings. The query we are using to pull the information is

Select * Into Table From Openquery(AS400,'Select * From Tablename')

Was wondering if anyone has seen this before and if so if they found a fix so we can pull everything we are looking for.

Thanks!

View 2 Replies View Related

Db_datawriter Member Could Not Select???

Nov 1, 2000

Hi:

Here is the question:
I add a user with "sp_adduser 'ABC', 'ABC', 'db_datawriter'" to the database role. However, the ABC login and password was rejected from select?

in ASP include file:
strConnect= "driver={SQL Server};server=ServerA;uid=ABC;pwd=ABC;database=Cu stDB"
--Error: [Microsoft][ODBC SQL Server Driver][SQL Server]
--SELECT permission denied on object 'tblCustomer', database 'CustomerDB', owner 'dbo'.

Do I have to give db_datawriter selection permission?
According to the books on line: this role has insert, delete and update rights

thanks
David

View 3 Replies View Related

Transfering Sql Data Into Csv File

Jul 20, 2006

Is there any way the i can write query in sql statment and transfer the result set into csv file with header info and one row option.

Thanks,

View 1 Replies View Related

Transfering Data From DB2 To SQL Server

Jun 27, 2000

I've read about using starsql odbc and sql server 7.0 DTS utility to transfer the database or data from a DB2 to SQL Server.
I have a SQL Server 6.5 and need to transfer data from a DB2. Is the operation the same? Is there a utility in sql server 6.5 that allows this?

View 1 Replies View Related

Transfering Data From One Machine To Another.....

Sep 6, 2003

Hello all

I have a SQL Server 2000 database in a remote host and I want to move all my data to another remote (virtual) host, where on both machines I do not have direct access.

Can you advise me what is the best way to make this transition?

Thank You in advance

Nick

View 1 Replies View Related

Transfering Data Between Tables At Different DB's

Dec 27, 2007

HELLO.

I have two similar tables at different databases. Their ID columns are same. I want to update one of them like:

Table A
ID Name

Table B
ID Name

UPDATE DB1.dbo.TableA SET Name = (SELECT Name FROM @DB2.dbo.TableB)...

I have problem with WHERE clauses. Can't figure it out. Can you help me?

Thanks in advance.

View 1 Replies View Related

Transfering Data To SQL Express

Apr 24, 2008

I have a database that I have created and I would like to transfer it to an Sql Express instance I am not sure how to do it... Can anyone help?

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

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

Need Help Transfering Data From A Texfile To A SQL Table

Nov 29, 2007

Hello all,I am currently working on upgrading the old website to an asp.net website.Current website uses flat-files as its database, so I need to convert a few files into SQL tables.The file that I am having difficulties with has the following structure (tab and coma separated):OrderID CustomerID ItemNO1 1 1,2,3,42 1 5,6,73 1 4,5,67,4576A,234 2 4,56,678,DF23,75 2 78,2,567,4556,3,45,FG456 3 2,45, F35 etc.... and I need my table to be structured like this: OrderID CustomerID ItemNO1 1 11 1 21 1 3 1 1 42 1 52 1 62 1 7.......................The import function in SQL database will not do this. Is it possible to automate this somehow? Thanks

View 4 Replies View Related

Date Format In Transfering Data In DTS

Aug 24, 2000

Dear all DBA,

i really need some help from all of you.
I currently have some DTS package which will transfer data from
text file to the SQL 7.0
This transformation is schedule using the Sql Agent.

The problem is the transfering data which have the Date datatype.
the Date format is in the dd/mm/yyyy. But the default language i set in my
sql server is english which suppose will use the date format mm/dd/yyyy.

therefore, the Sql server will treat my data which in dd/mm/yyyy as mm/dd/yyyy. so, the data like '08/09/2000' (in dd/mm/yyyy) become '08/09/2000'
(in mm/dd/yyyy)

i already set my user'language who will login in the sql agent as 'british english'. I also the OLE connection in the DTS package that i built using 'british english'.

when i run the agent in the Sql Enterprice Manager, it use the dd/mm/yyyy format which is correct. BUt when i log out and let it run as services which mean let the agent excute the DTS. it use back the mm/dd/yyyy fromat which cause the data become wrongs.

i hope someone can give me some advice on this.
Thanks for your help

from
samuel yeong

View 1 Replies View Related

Transfering Data From Informix To Sql Server

Feb 6, 2004

I need to transfer seven tables from Informix DB to SQL Server every night. Any suggestions for the best way of doing it? Is DTS the best way? How about through replication?

Thanks in advance.

View 1 Replies View Related

Transfering Data From DB2 OS/390 To SQL Server 2005

Aug 3, 2006

How can I make a connection in SSIS Data Flow to connect to DB2 on the mainfrain? Have several DTS packages in SQL Server 2000 that uses the ODBC connection.

Please help!

View 3 Replies View Related

Transfering Data From A Web Form Into Sql Server Database

Sep 20, 2005

Hi I am trying to develop a web based application. I am trying to insert data from my web form into sql server database using stored procedures. I am having a problem while i try to exceute the command. The error given by system is System.InvalidCastException: Object must implement IConvertible. at System.Data.SqlClient.SqlCommand.ExecuteNonQueryany suggestion what am i missing?With Regards,Sameer Jindal

View 2 Replies View Related

Which Method Is Better For Transfering Data Using Direct Inserts/SSIS?

Feb 7, 2007

Hi

I'm transfering legecy data to SQL Server.

Can anybody tell which method is best.

My boss wants cutome user interface to choose options and Need to update UI during processing.

Currently I'm using Direct INSERT Stmt (T-sql) Execution.

Can Anybody suggest the best.

View 1 Replies View Related

Problem In Transfering Of Tables Data From Lotus Notes Db To SQL Db

May 29, 2008

Here is the different problem which is irretating me alot from last 2 weeks. Could any body suggest on this problem?

I am using the ADO.net providerODBC provider connection for Data Reader source. I don't know why the total records are not populating from source(lotun notes db) to Destination (SQL server 2005). The package is populating only 2000 records out of 10000 records. Please suggest me how to fix this problem. There is no error message is coming for this problem

View 8 Replies View Related

Transfering Tables And Data From Oracle To Sql Server 2005

Nov 15, 2006

I have a oracle database that creates a table (to store call records) everyday and places data into it. How can i use Interation services to transfer the newly created tables to an SQL server database (SQL server 2005).

View 4 Replies View Related

Import Data From AS400

Jul 1, 2007

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

View 2 Replies View Related

Transferring Data From AS400 - Please Help!

Nov 3, 2005

I am attempting to transfer a table from an AS400 to SQL Server 2005 through SSIS. I am using the IBM DB2 UDB ISeries IBMDASQL OLEDB Provider as the source connector. I have tried to set up the source connector using the Table or View option but it says that No Tables Could Be Loaded. I can specify an SQL command and it shows the columns and appears to set up correctly except for showing a warning indicating that it cannot retrieve the column code page information from the OLEDB provider. When I run the job, it fails with the following error:

View 16 Replies View Related

Accessing AS400 Data

Jun 17, 2005

I've been trying to use the integrations services to access AS400 data with almost no success. Any suggestions or help you can give me would be appreciated. Some of the issues I can't make sense of are the following.

View 11 Replies View Related

Transferring Data From AS400 To SQL Server

Jul 31, 2007

Hi all,
I am working at insurance company that using AS400 as it main server. All transaction data is already kept for about 10 years (or even more..). This data is growing larger from time to time, and after several upgrades (that cost a lot!), my supervisor has an idea to partly move the data from AS400 to SQL Server. (since the cost for upgrading sql server is cheaper than AS400).

So.. let say, we only want to kept data in AS400 from 4 years before until now (2004 � 2007), and the rest of data is kept in SQL Server.

So first, all transaction data from 1997 � 2003 is transferred to SQL and deleted in AS400.
If user queried data and didn�t found the data in AS400, it will search the SQL, if data is found in SQL, then data is transferred back to AS400 and deleted in SQL.

I�m using SQL Server 2000 DTS (use HiT OLEDB) to transfer the data from AS400 to SQL and vice versa.

I wanna ask if anyone has done this before? What�s the difficulties by using this approach? (btw, I will implement the DTS using user control in vb.net)

Or anyone has a better solution to overcome this problem?

Thanks,
[RU]

View 2 Replies View Related

Import As400-DB2 Data Into SQL2005

May 19, 2008

How do i import As400 data in to SQL2005(standard version) using SSIS?? Please help!!!

View 1 Replies View Related

Move Data To DB2 On AS400 With SSIS

Dec 26, 2006

Anyone writing data to DB2 on an AS400 with SSIS?

I cannot get the OLEDB destination configured correctly. I can set the destination up with a SELECT sql query, and preview the resultset.

View 38 Replies View Related

New SQL 2005 Old SQL 7 And AS400 Need To Transfer Data

Nov 14, 2007

I am creating a migration tool that retrieves data from an AS400 and an SQL 7 server to create a new database in SQL 2005. I have managed to create a linked server in SQL 2005 to the AS400. I don't know if it works or not. I've been trying to create a linked server to the SQL 7 server with no luck. The AS400 and SQL servers are on the same network. The SQL 2005 server is hosted somewhere else. I have access to everything. The SQL 2005 server is running Workstation. I tried to use Integration Services but finally discovered it was unavailable to Workstation.

I could use some good ideas or help.

View 1 Replies View Related

AS400 Data Pull Fails As Job

Aug 4, 2007

When I execute the package in Debug mode, it works.

I then import the package into SQL Server 2005 and it is showing under Stored Packages --> MSDB - AS400Package

I can right click and Run Package from there and it works fine.

When I set the package up to run as a job, it fails. What I have noticed is that the Sign On that I use in the package shows that there was in "invalid login" after the job fails.

I hope I have this post in the right place and hope I have left enough information behind to help. If not let know what else I need to post. I have only been using Business Intelligence Development for a couple days and SQL Server 2005 for about a week or so. All of the SQL DTS packages that I have created seem to be working fine with their jobs as well as my stored procedures.

Thanks for any help you can give me.

View 11 Replies View Related