About Convert Issue From AS400 To SQL Server 2005

Mar 10, 2008



Hi all,
I have a issue need your great help. Would you please help to check it and tell mw how to fix this issue? Thanks.

*****All of softwares are English version****
Server:
WIndows Server 2003 SP2 + SQL Server 2005 SP2
AS400:
V5R4

Client :
Winsows XP SP2 + SQL Server 2005 SP2
Client Access V5R4 Full Version.
*****All of softwares are English version****

Issue:
I have a file in the AS400 with Chinese characters and need to dowload to SQL Sever 2005 daily.
In the SQL Server, I created a database and table which collection is "SQL_Latin1_General_CP1_CI_AS", and I modified one of field which collection is from "SQL_Latin1_General_CP1_CI_AS" to "Chinese_Taiwan_Stroke_CS_AS" (( the filed data type is "nvarchar" and the length is 42).
That mean that field can display the Chinese characters correctly.

In the SSIS,
1.Craeted a OLE DB Source to get the data ( The field name is "CUST_CHINESE_NAME" and the length is 42) from AS400
2.Craete a Data Conversion to convert that field from "String" to "Unicode String[DT_WSTR]" (The new field name is "Copy of CUST_CHINESE_NAME")
3.Create a Data Conversion 1 to convert that field from "Unicode String[DT_WSTR]" to "String[DT_STR]" and code page is change to "950"(The new field name is "Copy of Copy of CUST_CHINESE_NAME")
4. Creatd a OLE DB Destination to get that convert data and input the SQL Server ( the filed data type is "nvarchar" and the length is 42).


When I execute that SSIS, I got the error message in the third step.
****************************************************************************************************************************
[Data Conversion 1 [521]] Error: Data conversion failed while converting column "Copy of CUST_CHINESE_NAME" (487) to column "Copy of Copy of CUST_CHINESE_NAME" (544). The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

[Data Conversion 1 [521]] Error: The "output column "Copy of Copy of CUST_CHINESE_NAME" (544)" failed because truncation occurred, and the truncation row disposition on "output column "Copy of Copy of CUST_CHINESE_NAME" (544)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion 1" (521) failed with error code 0xC020902A. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC020902A. There may be error messages posted before this with more information on why the thread has exited.
****************************************************************************************************************************





View 10 Replies


ADVERTISEMENT

Port Data From AS400 To SQL Server 2005

Jan 24, 2007

Is there a good way to port data from an AS400 over to SQL server 2005? If anyone has any experience with this, can you tell me where to go to get info?

View 3 Replies View Related

Replicate Data From DB2/AS400 To SQL Server 2005

Sep 11, 2007

Hi Guys

I am trying to replicate data from DB2/AS400 to SQL Server2005 (ENT edition) currently we use 3rd party tool to replicate data from DB2 to SQL Server2000 (ENT edition) and like to get rid of this 3rd party tool. I am searching for the last 3 weeks but didn€™t get a good starting point. I have linked DB2 to SQL Server2005 and can run queries against DB2/AS400 box. Now I want to set up transactional replication from DB2 to SQL Server 2005. I have read about peer to peer topologies but I don€™t know if that€™s the route I have to take?



So can someone please help me? I really appreciate your help.



Thanks

Tariq

View 1 Replies View Related

Sql Server 2005 SSIS And AS400 Connectivity

Mar 29, 2006

Hi all

I am trying to connect to an AS400 to download data to a 64bit sql server 2005 server. I am able to do this easily by migrating existing sql2000 DTS packages but how do i do this is in an SSIS project creating the process from scratch? and how do i incorporate activex transforms like you could in DTS2000 into the trasnform with copy columns?

Please help i am slowly turning grey trying to get this to work.

thanks

Chris

View 1 Replies View Related

Can I Implement A Replication Between Sql Server 2005 And IBM AS400 DB?

Jun 7, 2007

Hello,



I want to set up As400 DB as a publisher and the sql server 2005 as a subscriper, so that I can read some data from AS400 DB in sql server 2005 and every evening, I need the replicated data from AS400 to be refreshed.

Can I do it ? If yes, how to do it?



Thanks

View 1 Replies View Related

Creating A Linked Server To AS400 In SQL Server 2005

May 24, 2006

I am trying to create a linked server in SQL Server 2005 to show tables in our AS400. I made the connection, however, the tables are not showing up under the Linked Server name.

How do you get the linked tables to display in the Linked Server folder?

David



View 4 Replies View Related

Help On Replication From SQL 2005 To DB2/AS400

Jun 2, 2006

I was trying to set up a replication process to move data from SQL 2005 to DB2/AS400.

To get it started, I have create a simplest table (say, PERSON) on SQL 2005 server, and the same table (PERSON) on DB2/AS400. I have only one column SSN (of type int). I have journaled the table on DB2 end. I was able to have successfully configured publisher (on SQL 2005) and subscriber (i.e. a pushed subscription). I used transactional replication. And I also installed HIS 2004 to have DB2OLEDB ready. I was able to use query/insert/delete against table on DB2 based the linked server (that I created for testing purpose).

I did a lot of reading on this and my impression is that replicating to DB2/AS400 is supported from SQL 2005. But I always have this error from replication monitor. Can anyone enlighten me what was wrong.



Command attempted:


CREATE TABLE MY_DB.PERSON(
SSN
INT NOT NULL)


(Transaction sequence number: 0x00000020000000F9000100000004, Command ID: 4)

Error messages:


A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 42710, SQLCODE: -601 (Source: MSSQL_REPL_DB2, Error number: -601)
Get help: http://help/-601

A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 42710, SQLCODE: -601 (Source: MSSQL_REPL_DB2, Error number: -601)
Get help: http://help/-601



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

Move Data From SQL 2005 To DB2/as400 Using DB2OLEDB

Mar 22, 2006



I have a simple SSIS package that I want to move data from SQL 2005 to DB2/AS400. In order to use OLEDDB destination, I installed HIS2004 that provides DB2OLEDB support.

For simplicity and testing purpose, I created a connection pointing to a SQL table that contains only one ccolumn "last_name" of type "nvarchar(50)". This is my source, I then created another connection using DB2OLEDB pointing to a table on DB2 that contains only one field "name" of type "GRAPHIC(50)". This is my destination and the connection works fine to DB2 database. When I run the package, it has type conversion error as follows:

[OLE DB Destination [34]] Error: An OLE DB error has occurred. Error code: 0x00040EDA. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E07 Description: "Data or literal value could not be converted to the type of the column in the data source, and the provider was unable to determine which columns could not be converted. Data overflow or sign mismatch was not the cause.".

If I manually assign a vaule to the "last_name" using the "Derived Column" task like "Hello John", it just works fine.

Anyone knows what went wrong? Did I miss anything here? Thanks.

As a FYI: DTS works great in moving data from SQL 2000/2005 to DB2.

Your help will be appreciated.

View 2 Replies View Related

Sending Sql 2005 Table Back To DB2 AS400

Apr 10, 2008

I am trying to send a file from SQL 2005 back to our AS400 DB2 db. First time trying to get this to work in SSIS.

I have a couple fields that are datetime in SQL Server and see SQL server sees datetime fields from DB2 as string so I am converting them to Strings using derived columns. That part seems to work fine. Get no errors on that part.

The conversions seem like they are working but it is faling when it tries to send it to as/400. I get the following error: any ideas on what is wrong?

[OLE DB Destination [1569]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "IBMDA400 File Rowset" Hresult: 0x80004005 Description: "CPF5029: Data mapping error on member DSSCNTL. ". An OLE DB record is available. Source: "IBMDA400 File Rowset" Hresult: 0x80004005 Description: "CPF5035: Data mapping error on member DSSCNTL. ". An OLE DB record is available. Source: "IBMDA400 File Rowset" Hresult: 0x80004005 Description: "CPF5035: Data mapping error on member DSSCNTL. ".


I even created a sql table with same field sizes as one I am sending to AS400 and its sucessful there. So seem like it mght have somthing to do with sending to AS400 but not sure what.

Any ideas?

Stacy

View 1 Replies View Related

How To Import Data From AS400 Files To Sql 2005 Tables?

Apr 18, 2007

Hi All,

I want to Import data from AS400 to Sql2005. From the Sql Management studio I invoke the Import Data wizard. For the source I connect to the ISereis system and for the Destination I select the Sql2005 Database , When I go to the next step i.e select source tables I get the error
"An error has occured which the SQL Server Integration Wizard was not prepared to handle
No error message available,result codeB_E_CANTCANCEL(0*80040E15).(System.Data)" .After this I am unable to proceed further. I am using client Access tool to connect to ISereis

IBM DB2 UDB for isereis IBMDA400 OLE DB Provider





Regds,

Anu

View 2 Replies View Related

SQL Server 2005 - Convert .BAK File To .MDF

Mar 27, 2008

Hello There,
I would really appreciate if you can help me with this problem. I am currently using SQL Server 2005 Express for one of my web application. I have a .bak file (backup file) provided by my client which I want to use for testing purpose. Now I just want to know how can I use that backup file without installing SQL SERVER 2005 as one way to do this I know is to restore the database in SQL Server 2005 Enterprise Manager but I can't install SQL Server 2005.
To my knowledge, SQL Server Express uses .mdf file for database but what I have is .BAK file..
I would be really glad to see your quick answers.
Thank you for reading this far.
Sincerely,
Zulfiqar

View 2 Replies View Related

Convert MySQL To MS SQL Server 2005

Jul 27, 2007

Can anyone recommend me how I can convert MySQL database to MS SQL server 2005 database? I am new to the SQL server 2005. Is there any ODBC I can use? Thanks in advance

View 3 Replies View Related

Convert MySQL To SQL Server 2005 Express

Oct 3, 2007

I have a database that is in MySQL and would like to import it into SQL Server 2005 Express.

Can this be done? Any tutorials anywhere?

Thanks.

View 7 Replies View Related

SQL Server 2005 - How To Convert A Query Into A Table?

Sep 11, 2007

I'm going through and restructuring a database scheme and was wondering if, after I make a query (view), how can I convert that into a table?

Thanks

View 4 Replies View Related

How To Convert A SQL Server 2005 Database To SQL Server Express

Jun 29, 2006

Hi,
I have a SQL Server 2005 Database that I would like to export to SQL Server Express.
How do I go about this?
I've tried backing it up, creating a new (blank) SQL Server Express Database and trying to restore it, but get the following message:
TITLE: Microsoft SQL Server Management Studio------------------------------
Restore failed for Server 'PRODSOL-LAPTOP1SQLEXPRESS'.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'ProdsolPGC' database. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
Neither of the links provide any more information and I'm sure that I'm missing a step out.  I've tried scripting the existing database and then running the script against the new database, but I can't get that to work either - these blooming newbies!
Please help!
Thanks very much.
Regards
Gary

View 2 Replies View Related

Convert Sql Server 2000 Database To Sql Express 2005

Jul 26, 2007

Hi;
I have a database in sql server 2000 and i want to convert it to sql express 2005, so please tell me how I can do this. Thanks
Adnan

View 3 Replies View Related

Convert A Existing SQL Server 2000 Database To 2005

Jul 23, 2005

Hey @ all,I have a question:It is possible to convert an existing SQL Server 2000 Database to SQL Server2005?best wishes!

View 1 Replies View Related

SQL Server 2005 Express:Is It Possible To Convert Existing Data From .mdb To .mdf?

Sep 21, 2006

Hi all,

I need to use the SQLDataSource in Visual Web Developer (VWD) 2005 Express websites. The SQLDataSource must have the .mdf data in the project application. But I have a volume of the .mdb data for evaluation by using T-SQL of the SQL Server 2005 Express and VWD 2005 Express program. Is it possible to convert existing data from .mdb to .mdf? If it is possible, please tell me what software program to use and how to do it.

Thanks,

Scott Chang

View 8 Replies View Related

A Related Question - How To Convert From Sqlexpress To Sql Server 2005

Apr 1, 2006

how does one convert from sqlexpress (mdf files) to sql server 2005 and vise-versa, ie from SQL Server 2005 to SQLExpress mdf files?

View 5 Replies View Related

Convert Oracle Connect By Level &&< 10 Into MS SQL Server 2005

Oct 2, 2006

I want to convert oracle "SELECT LEVEL R FROM CONNECT BY LEVEL <=10" my bottom requirement is get 1 to 10 as a dynamic table inside a query.

Ex: Select id, name, R from names N, (1,2,3,4,5,6,7,8,9,10) R WHERE id < 1000

If any one know something regarding this please reply me.

Thx,

Buddhika

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

Convert Access Database To Sql Server 2005 Compact Edition

Nov 7, 2007



Is there any way to convert an Access or SQL server 2005 database to SQL server 2005 compact edition database??



Thank you very much!!

View 8 Replies View Related

Sql Server 2005 Auto Convert Text To UPPER Case

Jul 13, 2006

Our sql server 2005 database is receiving data from a third part program over which we have no control. We need to be able to automatically convert data entered in one column of one table to UPPER case only.

How can this be done in the table itself?

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

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

Can SQL Server Run In AS400 Machine?

Mar 21, 2006

Can SQL Server run in AS400 Machine?

Thanks.
-vince

View 3 Replies View Related

Access AS400 DB2 In SQL Server

Jun 4, 2007



Hi,

Im finding the way of connecting to DB 2 database which is exixst in the AS400

machine to do some data manupulation.Please attach the SQL cording.



Thanks

Pubudu

View 1 Replies View Related

Convert Sql Server 2005 To Sql Server 2000

Jun 30, 2006

I have to downgrade a eCommerce database from Sql Server 2005 to Sql Server 2000 due to hosting server's support compatibility. Almost all features in the original (2005) version are available in 2000.
Any recommendations as on an easy conversion? Thanks a lot.
-Yubo

View 12 Replies View Related

Creating A Linked Server To AS400.

Mar 15, 2001

I wanted to know whether it is possible to query a database in AS400 server from MS SQL Server 7.0 using linked server. If it is possible, could you show some pointers as to how it can/should be implemented.
Thanks in Advance.
Manojkumar
DMT Team
DaimlerChrysler Capital Services
Phone - 203-845-7326
EMail - manoj.kumar@dcxcapital.com

View 1 Replies View Related

How Can I Update Linked Server (AS400 DB)?

May 16, 2000

Hi!

Please

I got the following Error Message when trying to update a AS400 Database
"(Table Name) on (Data Source) not valid for operation"

My question is? Does Sql Server require journaling the files? How Should I change this option?. Please Help!

I did try to fix the above error, but then I got a new message:

OLE DB provider 'MSDASQL' supplied inconsistent metadata. The object '(user generated expression)' was missing expected column 'Bmk1000'.
Could you help me?

I will appreciate your help.
Thanks in advance

Ivette V

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







Copyrights 2005-15 www.BigResource.com, All rights reserved