While Transfering Data from AS400 to SQL Server ...error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I am trying to get data from IBM AS400 into SQL Server. I need to check it
very 10 minutes. So I created a Windows Services and it worked like 28 Hours.

Suddenly when I try to test the dataTransfer manully I get an error:

"The data value could not be converted for reasons other than sign mismatch
or data overflow. For example, the data was corrupted in the data store but
the row was still retrievable."

I checked every where and I couldn't find any answer.

When I use my SQL string using VS.NET 2003 Server Explorer and connected it
worked fine. My Windows Services also worked fine.

The manual (The menu button) also did work but now I am getting the error
that I mention above.

Can anyone knows why I am getting this error?

Thank you for reading my mail.

Rgds,
GC
 
How are you moving the data? BCP? DTS? DataReader?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Hi Bill,

I use the dataadapter to fill the dataset. It worked before. Than I worked
with dataset take it the information that I need and pass it to SQL Server.

If I use VS.NET 2003 Server Explorer to conect to AS400 and run my SQL
command it works. It worked before as well.

I used data adapter, than I change it to datareder and still I get same
error. My Windows Services that I created works fine. Well when the table
updated in AS400 the Windows Services that I created inserted the 0 value to
each column in Table that reside on SQL server. I set the timing to be update
the SQL Server in every 10 minutes. And 10 min later I checked the SQL Server
and data was updated.

In my Windows Services I created the class and I copied that class and I
used in my Program so user also can update the SQL Server table as manualy.

In that position that user was going to update manully to SQL Server than I
started to get the error.

Day before I created small VB.NET Windows apllication to test my class and
it worked.

After talking to AS400 guys everything start to fail.

I am not sure but I am gussing I might having internal personal problem with
AS400 Programmer. They don't like what we do as using Microsoft product. This
is the 4th time when I get their permision to access the tables in AS400
sudenly data comes unrecognizable to ADO.NET.

I have read only access to any table in AS400 and if I don't tell them weaht
table I am working everythings looks okay.

But also what is strange things that the program worked fine (5 months)
accessing same table in AS400. After creating the windows services (the
services works fine) and tell my boss taht I did it things start to get worse.

Plus how can I be able to see and get the data using VS.NET Server Explorer
and I can't be able to get with my VB.NET code? Plus same code worked fine
with the accesing same table more than a 5 months. Now both application that
accesing same table fails with an error.

I checked with Microsoft and I find a article that talks about
ICommand::Execute, IMultiple::GetResults, IRowset::GetData,
IRowsetRefresh::GetLastVisibleData and so on.

My question is how can I get data or see data even if I dont know the data
type?

The Error is somehow is not clear.

"The data value could not be converted for reason other than sign mistmach
or data overflow. For example, the data was corrupted in the data store but
row still retrievable."

What do you mean by "data value could not be converted "?
How can be "the data was corrupted in the data store"?
What do you mean by saying "data store"? Is it talking about my data adapter?
If "row still retrievable" than how can I work with it?

I thank you for your kind understanding to reading my mail.

Rgds,
GC
 
You might try discovering the data types on your AS400 table. You may have
some datatypes that don't covert easily to SQL Server.

To discover the datatypes you might try something like the following code
(you need to be using a DB2 data provider).


Dim cn As New iDB2Connection
Dim cmd As New iDB2Command
Dim schemaTable As DataTable
Dim myReader As iDB2DataReader
Dim myField As DataRow
Dim dc As DataColumn
Dim NumCols As Int16
Dim NumProp As Int16
Dim sw As StreamWriter
NumCols = 0
NumProp = 0
cn.ConnectionString =
"DataSource=servername;LibraryList=library1,library2;Naming=SQL"
cn.Open()
cmd.Connection = cn
cmd.CommandText = "Select * from library1.tablename"
myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo)
schemaTable = myReader.GetSchemaTable
sw = New StreamWriter("C:\vfp\db2ddl\EE007P.TXT")
For Each myField In schemaTable.Rows
NumCols += 1
NumProp = 0
'For Each dc In schemaTable.Columns
'NumProp += 1
'sw.WriteLine(NumProp & " = " & dc.ColumnName & " = " &
myField(dc).ToString())
Select Case myField("DataType").ToString
Case "System.String"
sw.Write("""")
sw.Write(myField("ColumnName").ToString)

sw.Write("""; ""CHAR(")
sw.Write(myField("ColumnSize").ToString)
sw.Write(")""; """)
sw.Write(myField(18).ToString)
sw.WriteLine("""")
Case "System.Decimal"
sw.Write("""")
sw.Write(myField("ColumnName").ToString)
sw.Write("""; ""DECIMAL(")
sw.Write(myField("NumericPrecision").ToString)
sw.Write(",")
sw.Write(myField("NumericScale").ToString)
sw.Write(")""; """)
sw.Write(myField(18).ToString)
sw.WriteLine("""")
End Select
'sw.WriteLine(myField("ColumnName").ToString)
'sw.WriteLine(myField("ColumnSize").ToString)
'sw.WriteLine(myField("NumericPrecision").ToString)
'sw.WriteLine(myField("NumericScale").ToString)
'sw.WriteLine(myField("DataType").ToString)
'sw.WriteLine(myField(18).ToString)
'Next
'sw.WriteLine()
Next
sw.Close()

'For i As Int16 = 0 To NumCols - 1
' For ii As Int16 = 0 To NumProp - 1
' Debug.WriteLine("Property# = " & ii & " Field# = " & i & "
- " & schemaTable.Rows(i).Item(ii).ToString)

' Next
'Next
End
 
Hi Wesley,

I have to thank you for your kind contribution to my problem. I will try to
test the code that you were provided to me on Monday 24th October.

I will be psoting here my result.

By the way In my PC (Win XP SP2) I install the IBM Client-Server for Windows
application and I guess the version was 5.24 or something. I will check the
right version on Monday and post in here. Then I use the connection object as:
-------
Public Shared strConnAS400 As String =
"Provider=IBMDA400.DataSource.1;Persist Security Info=False;User
ID=myID;Password=myPassword;Data Source=101.101.101.101;Protection
Level=None;Transport Product=Client Access;SSL=DEFAULT;Force
Translate=65535;Default Collection=myCollection;Convert Date Time To
Char=TRUE;Cursor Sensitivity=3"
-------

Does the above connection string will be okay to use with your code?

I will going to try it and see if it works or not and I will be post the
result.

Again I truly appreciated your kind contribution to my problem.

Thank you.

Rgds,
GC
 
Hi Wesley,

Thank you for your kind input. I finaly realize what mistake they did in the
AS400 Table. When I told them that doata was corrupt they checked it and it
worked in my VB.NET.

The data in Table (in AS400) after 5th row was corrupt and they fixed it.

Again I thank you for your kind contribution to my problem.

Rgds,
GC
 
Hi Wesley,

Actualy I wasn't quite right. When my Regional Setting was English (United
State) the data transfer from AS400 to SQL Server was worked.

But when I set PC Regional setting to Turkish I am getting same error again.
Do you know why this is happining when I change to regional settings foir my
PC?

Rgds,
GC
 
That I would have no idea about.

The latest update for Client Access comes with an iSeries data provider for
dotnet. You should see if you have that and try to use it.
 
Back
Top