By Olivier - 5/3/2012
Hi ,
I make upsizing from foxpro in 2009 to SqlServer, and during this date the field was in numeric 10,0 (for a floor an apartement)
Today we want to transform the database in the best quality , and in DDT we put the field Floor in "INT"
Unfortunately, all fields was in numeric, they don't cast in INT with DDT.
I don't know Why, may be DDT does not recognize the type numeric.
Have you an idea to apply the cast ?
thanks
Olivier,
|
By Trent L. Taylor - 5/3/2012
Oliver:
I understand coming from VFP, as we were in that boat at one point ourselves. In this particular example, you will want to use the Decimal data type in SQL Server. This is the closest translation from the VFP Numeric. An integer, in SQL and .NET, represents a whole number only, so no decimals.
There are some articles online about VFP to SQL data type translations. Let me know if this doesn't get you going.
|
By Michel Levy - 5/3/2012
Hi Trent,
the problem I've seen on Olivier's computer is a weird mapping, that I'm not able to reproduce on any one of mine...
for some historic reasons (VFP background, but that is really old ages), in its SQL server, he handles integer through Decimal (N,0) columns. And in the BO mapper, directly on the tables (no DDT at this step), all these Decimal columns are mapped to Int32!
Of course, when running the app, there is an invalid cast error bubbling from the ODBC layer. 
Having the same database on my local SQL Server, the same SF version, I'm unable to reproduce this error. Going on its development machine, I see this error in BO mapper. I'm going here and there with this error for a couple of days, still not understanding the origin.
I can't find which part of the SF source code could set this incorrect mapping, and I suspect a bad SQL Native Client. What piece of code would help you to help us?
|
By Olivier - 5/3/2012
Hello Michel,
We have find why the bomapper get int32 cause we have modify in DDT an numeric 10,0 to INT and
the boMapper get Int32 it's correct.
But DDT doesnt alter the column numeric to INT in Sqlserver.
Olivier
|
By Olivier - 5/3/2012
Hello Trent,
thanks for your reply,
In fact, when i import in DDT from SqlServer (reverse engineering),
the data type numeric 20,0 was translate in DDT INT (13).
Also since that, we don't cast the FIELD numeric to INT.
I hope to understand me,
ps : i attach pictures ,
thanks
Olivier,
|
By Michel Levy - 5/3/2012
Hi Olivier,
I confirm your last post!
Having still Decimal(N,0) as datatype in SQL Server, I may see on my dev machine that BO mapper map it as decimal (fine, but will trigger an error on runtime), but that DDT on the same data map these columns as Int (I have NOT ask for any modification, just imported the data structure)
|
By Trent L. Taylor - 5/3/2012
I will take a look at that. Thanks for the heads up. It has been a very long time since I have personally imported a VFP table. So it is possible that there is something going on there.
|
By Olivier - 5/3/2012
Trent ,
Excuse me for my poor english.
If you want to reproduce this, you haven't to use Foxpro.
You put in sqlserver a field in numeric 20,0 or numeric 10,0 and you import your database
in DDT to see that.
thanks
Olivier,
|
By Trent L. Taylor - 5/3/2012
Oliver, thanks. That makes the setup a lot easier. I will give that a go.
|
By Olivier - 5/3/2012
Trent,
Just a last test, to force the update column, i test the field int to BIGint ,
and i run a deployment => it's ok.
But i change the type from bigint to Int , i have a error : "String or binary data would be truncated"
If i run in TSQL : Alter table [Imhotep].[dbo].[affaires] alter column [etage] [int] not null => it's OK
Do you think ? we could be have a parameters to force the alter column in DDT without stop the process ?
thanks
Olivier
|
By Trent L. Taylor - 5/3/2012
When are you getting the error? A BIGINT can't fit into an INT and would in fact be truncated. I am just trying to understand where you are getting the error.
|
By Olivier - 5/3/2012
Yes, I want to force DDT cast my field numeric to int, so i put in BigInt and after in Int
but i getting a error cause it doesn't want truncated.
And if i run a script : Alter table [Imhotep].[dbo].[affaires] alter column [etage] [int] not null
SqlServer can truncated
Can we insert a script in DDT (tsql) ?
DDT is very useful, and maybe the other developer use it, like as add or rename column,
but may be not to change the data type.
Cause may upsizing from foxpro get a bad datatype,we use DDT to make a beautiful and good database
for my application.
I hope , i was clear in my text.
thanks
Olivier
|
|