default field type when importing

I asked this question in the .dts newsgroup and got no response. If someone
can help me here, that would be most appreciated.
I have a customer that does alot of importing of .txt files into SQL
databases. In the past, the default field type seemed to be varchar( 255).
Now, it seems that all the fields import as varchar( 8000 ), which messes up
some of their third-party programs that they use. So, whenever they import,
they have to go through every field and change it's type.
Does anyone know why this is happening and how I can change it back?
I have a feeling that this change occurred when I installed the MDAC
security update. Is this possible?
Thanks,
MikeWhat is your version of SQL Server. This is mine.
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
Where do you see (8000). I just asked DTS to create a table for me from a
CSV and it created it as (255).
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in message
news:eoJjWLIoDHA.2272@.tk2msftngp13.phx.gbl...
> I asked this question in the .dts newsgroup and got no response. If
someone
> can help me here, that would be most appreciated.
> I have a customer that does alot of importing of .txt files into SQL
> databases. In the past, the default field type seemed to be varchar(
255).
> Now, it seems that all the fields import as varchar( 8000 ), which messes
up
> some of their third-party programs that they use. So, whenever they
import,
> they have to go through every field and change it's type.
> Does anyone know why this is happening and how I can change it back?
> I have a feeling that this change occurred when I installed the MDAC
> security update. Is this possible?
> Thanks,
> Mike
>|||Here's what I get:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
It's SQL Server 2000 SP3 running on Windows 2000 Server SP4. This also
happens on SQL Server 2000 SP4 running on Windows 2000 SP4. Are you running
yours on a WinXP Pro workstation?
Once I have the source selected, there's a button with an (...) to view the
transform. That's where the varchar( 8000 ) can first be seen -- and
changed.
Have you kept up with all the Windows security updates? Including the MDAC
and JET updates that came out in the last couple of months?
My MDAC version is 2.71.9030.9. That's the MDAC 2.7 SP1 Refresh version
that got installed with the SQL Server 2000 SP3a.
Thanks for your help.
Mike
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eyLO6UIoDHA.1672@.TK2MSFTNGP09.phx.gbl...
> What is your version of SQL Server. This is mine.
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
> Where do you see (8000). I just asked DTS to create a table for me from a
> CSV and it created it as (255).
>
> --
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
> "Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in message
> news:eoJjWLIoDHA.2272@.tk2msftngp13.phx.gbl...
> > I asked this question in the .dts newsgroup and got no response. If
> someone
> > can help me here, that would be most appreciated.
> >
> > I have a customer that does alot of importing of .txt files into SQL
> > databases. In the past, the default field type seemed to be varchar(
> 255).
> > Now, it seems that all the fields import as varchar( 8000 ), which
messes
> up
> > some of their third-party programs that they use. So, whenever they
> import,
> > they have to go through every field and change it's type.
> >
> > Does anyone know why this is happening and how I can change it back?
> >
> > I have a feeling that this change occurred when I installed the MDAC
> > security update. Is this possible?
> >
> > Thanks,
> > Mike
> >
> >
>|||I am on WinXP yes
MDAC = 9.0.608.0 (Rollback version is the same as yours)
Ahhhhhhhhhhhh. I just spotted where you get your values from. You get them
in the wizard. Sorry I never use the wizard. I therefore do not know if
this is changed behaviour. I know that in the Designer i.e. building a
package without the wizard it is 255.
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in message
news:OUlFMJJoDHA.2140@.TK2MSFTNGP09.phx.gbl...
> Here's what I get:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
> It's SQL Server 2000 SP3 running on Windows 2000 Server SP4. This also
> happens on SQL Server 2000 SP4 running on Windows 2000 SP4. Are you
running
> yours on a WinXP Pro workstation?
> Once I have the source selected, there's a button with an (...) to view
the
> transform. That's where the varchar( 8000 ) can first be seen -- and
> changed.
> Have you kept up with all the Windows security updates? Including the
MDAC
> and JET updates that came out in the last couple of months?
> My MDAC version is 2.71.9030.9. That's the MDAC 2.7 SP1 Refresh version
> that got installed with the SQL Server 2000 SP3a.
> Thanks for your help.
> Mike
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:eyLO6UIoDHA.1672@.TK2MSFTNGP09.phx.gbl...
> > What is your version of SQL Server. This is mine.
> >
> > Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> > May 31 2003 16:08:15
> > Copyright (c) 1988-2003 Microsoft Corporation
> > Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
> >
> > Where do you see (8000). I just asked DTS to create a table for me from
a
> > CSV and it created it as (255).
> >
> >
> > --
> >
> >
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >
> >
> > "Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in message
> > news:eoJjWLIoDHA.2272@.tk2msftngp13.phx.gbl...
> > > I asked this question in the .dts newsgroup and got no response. If
> > someone
> > > can help me here, that would be most appreciated.
> > >
> > > I have a customer that does alot of importing of .txt files into SQL
> > > databases. In the past, the default field type seemed to be varchar(
> > 255).
> > > Now, it seems that all the fields import as varchar( 8000 ), which
> messes
> > up
> > > some of their third-party programs that they use. So, whenever they
> > import,
> > > they have to go through every field and change it's type.
> > >
> > > Does anyone know why this is happening and how I can change it back?
> > >
> > > I have a feeling that this change occurred when I installed the MDAC
> > > security update. Is this possible?
> > >
> > > Thanks,
> > > Mike
> > >
> > >
> >
> >
>|||Thanks for your help. I'm not too familiar with DTS. I don't even know how
to get started in it. Can you do all the same things in the designer as in
the wizard?
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:enEPzlJoDHA.3504@.TK2MSFTNGP11.phx.gbl...
> I am on WinXP yes
> MDAC = 9.0.608.0 (Rollback version is the same as yours)
>
> Ahhhhhhhhhhhh. I just spotted where you get your values from. You get
them
> in the wizard. Sorry I never use the wizard. I therefore do not know if
> this is changed behaviour. I know that in the Designer i.e. building a
> package without the wizard it is 255.
>
> --
>
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
>
> "Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in message
> news:OUlFMJJoDHA.2140@.TK2MSFTNGP09.phx.gbl...
> > Here's what I get:
> >
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> > Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on
Windows
> > NT 5.0 (Build 2195: Service Pack 4)
> >
> > It's SQL Server 2000 SP3 running on Windows 2000 Server SP4. This also
> > happens on SQL Server 2000 SP4 running on Windows 2000 SP4. Are you
> running
> > yours on a WinXP Pro workstation?
> >
> > Once I have the source selected, there's a button with an (...) to view
> the
> > transform. That's where the varchar( 8000 ) can first be seen -- and
> > changed.
> >
> > Have you kept up with all the Windows security updates? Including the
> MDAC
> > and JET updates that came out in the last couple of months?
> >
> > My MDAC version is 2.71.9030.9. That's the MDAC 2.7 SP1 Refresh version
> > that got installed with the SQL Server 2000 SP3a.
> >
> > Thanks for your help.
> > Mike
> >
> >
> > "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> > news:eyLO6UIoDHA.1672@.TK2MSFTNGP09.phx.gbl...
> > > What is your version of SQL Server. This is mine.
> > >
> > > Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> > > May 31 2003 16:08:15
> > > Copyright (c) 1988-2003 Microsoft Corporation
> > > Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
> > >
> > > Where do you see (8000). I just asked DTS to create a table for me
from
> a
> > > CSV and it created it as (255).
> > >
> > >
> > > --
> > >
> > >
> > > Allan Mitchell (Microsoft SQL Server MVP)
> > > MCSE,MCDBA
> > > www.SQLDTS.com
> > > I support PASS - the definitive, global community
> > > for SQL Server professionals - http://www.sqlpass.org
> > >
> > >
> > >
> > >
> > > "Mike Downey" <mike@.ProphetTechnologies.nospamxxx.com> wrote in
message
> > > news:eoJjWLIoDHA.2272@.tk2msftngp13.phx.gbl...
> > > > I asked this question in the .dts newsgroup and got no response. If
> > > someone
> > > > can help me here, that would be most appreciated.
> > > >
> > > > I have a customer that does alot of importing of .txt files into SQL
> > > > databases. In the past, the default field type seemed to be
varchar(
> > > 255).
> > > > Now, it seems that all the fields import as varchar( 8000 ), which
> > messes
> > > up
> > > > some of their third-party programs that they use. So, whenever they
> > > import,
> > > > they have to go through every field and change it's type.
> > > >
> > > > Does anyone know why this is happening and how I can change it back?
> > > >
> > > > I have a feeling that this change occurred when I installed the MDAC
> > > > security update. Is this possible?
> > > >
> > > > Thanks,
> > > > Mike
> > > >
> > > >
> > >
> > >
> >
> >
>sql

Default date using datepicker

Hi
I'm working on a report in RS2005 and are using a start and enddate
parameter. They are of the datatype DateTime. By using this type it is
possible to use the datepicker on the report. However, I would like the
report to have a default date range say 01/01/2006 as startdate and
12/31/2006 as enddate, with the option to change this using the datepicker.
How do I set this up. I've tried using the Non-queried field on the parameter
wihtout any luck.
Thank you.This is how I do it
Create a dataset against some SQL database, either the db you're querying or
the Report Server.
Query : Select getdate() as Today, DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
as FirstDayOfYear, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() )+1,
0)) as LastDayOfYear
In your report, open the parameters pane.
Set the parameter data type to DateTime
Set the default values to "From Query", select the dataset you created and
select the column you want to use from your query.
The date picker control seems to need the time as well as the date, so don't
format the values from the SQL query.
The date picker will also display this timestamp if it's not 00:00:00 so you
might have to tweak the output a bit.
More tips on calculating different sql server dates at:
http://www.databasejournal.com/features/mssql/article.php/3076421
Kaisa M. Lindahl Lervik
"Martin GC" <MartinGC@.discussions.microsoft.com> wrote in message
news:26850882-D269-4F3C-8BA7-BECD2B1382C0@.microsoft.com...
> Hi
> I'm working on a report in RS2005 and are using a start and enddate
> parameter. They are of the datatype DateTime. By using this type it is
> possible to use the datepicker on the report. However, I would like the
> report to have a default date range say 01/01/2006 as startdate and
> 12/31/2006 as enddate, with the option to change this using the
> datepicker.
> How do I set this up. I've tried using the Non-queried field on the
> parameter
> wihtout any luck.
> Thank you.

Default Data Types in SQl Server

Is there a way to change the default data type in SQL Server? If I import a database into SQL Server it makes every non-numeric field a nvarchar field. Is there a way to change this so that these fields would be varchar or char by default? I cannot find any way to redo these as a batch/group update and have to modify every single field manually. Any insights greatly appreciated.If I import a database into SQL Server it makes every non-numeric field a nvarchar field.

What method are you using to import the database?

Regards,

hmscott|||And what is your datasource? Are you talking about importing delimited text files to tables?|||I use the DTS wizard (ad-hoc, not a scheduled job). I usually import MS-Access tables, but have the same problem with Sybase tables or flat files - every non-numeric field is created as nvarchar. I thought the model database might be the place to override/change this behavior, but no luck there either. Thanks.|||I use the DTS wizard (ad-hoc, not a scheduled job). I usually import MS-Access tables, but have the same problem with Sybase tables or flat files - every non-numeric field is created as nvarchar. I thought the model database might be the place to override/change this behavior, but no luck there either. Thanks.

You will probably have to define the destination data types in the data pump destination tab. If you are importing to a new table, click on the "create" tab and a sample create table script will appear. Edit the destination columns to be of the desired data type.

Regards,

hmscott

Declare a variable in an SP

Hi.
I have this sql query, which works:
INSERT INTO tblac
(type, t_id, startdate)
SELECT
tblac.type, ***182*** AS Expr1, tblac.startdate
FROM
tblac
WHERE
tblac.t_id = @.t_id
This works, but I want to put it into an SP. I can't figure out from
books online, how to replace the integer, 182, with a variable passed to
the SP.
I've tried:
CREATE PROCEDURE copyDates AS
(
DECLARE @.new_t_id int,
@.t_id int
)
INSERT INTO tblac
(type, t_id, startdate)
SELECT
tblac.type, @.new_t_id AS Expr1, tblac.startdate
FROM
tblac
WHERE
tblac.t_id = @.t_id
GO
..
but that doesn't work.
thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***Mark wrote:
You need to declare the variables you want to pass (the parameters)
BEFORE the AS.
CREATE PROCEDURE copyDates (@.new_t_id int, @.t_id int) AS
INSERT INTO tblac
(type, t_id, startdate)
SELECT
tblac.type, @.new_t_id AS Expr1, tblac.startdate
FROM
tblac
WHERE
tblac.t_id = @.t_id
GO
HTH,
Stijn Verrept.

Decimals Converting Back to Integers? Whats Going On?

I am trying use the decimal data type for a field in SQL Server. When I input the values below, they round off.

73.827 Rounds to 74

1925.1 Rounds to 1925

119.79 Rounds to 120

What am I missing? Access never gave me this issue. Do you see any reason this would happen? I am entering the values into the table directly!

What do you mean by "entering the values into the table directly"? And, where are you seeing them rounded?

|||

If I open the table in Enterprise Manager and view all my data. I can add data as well. You know, direct data entry in a table.

When I type 2.756, It rounds to the nearest integer. 2.756 will round to 3. The datatype for the column is decimal. Seems simple problem, but I can't seem to find the resolution.

|||

defyant_2004:

If I open the table in Enterprise Manager and view all my data. I can add data as well. You know, direct data entry in a table.

When I type 2.756, It rounds to the nearest integer. 2.756 will round to 3. The datatype for the column is decimal. Seems simple problem, but I can't seem to find the resolution.

What precision and scale do you have the decimal field set for?

|||

By default the precision will be set to 0 for decimal fields. You might need to adjust it.

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

Hi,
I have a derived column transformation which adds a new column of type Decimal with scale 2.

The expression is (6800 / 464)

Runs fine but it returns

14.65

While if I run any of the following queries in query analyzer

SELECT 6800 / 464

OR

SELECT CONVERT(DECIMAL(10,2), 6800 / 464)

They give me 14.66.

My question is, why both the tool of same product differs in the way they work ? and How could I have SSIS to work like TSQL ? I tried typecasting in derived column expression and ROUND function too. But still, the output is same.

Any help in this will sincerely be appreciated.

Thanks

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

|||

DarrenSQLIS wrote:

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

But the intuitive way doesn't work: (both report 14.65)

(DT_NUMERIC,6,2)((DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464)
(DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464|||I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.|||

DarrenSQLIS wrote:

I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

The only reason I say that casting the inputs is because in this example, they would be considered integers, not numerics.

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

|||

Phil Brammer wrote:

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

It is important to note that the SSIS expression language follows C-style rules, not T-SQL. Behaviour in this case is a little different.

Phil Brammer wrote:

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

In the case of the SSIS expression language, there should indeed be no difference between

(DT_NUMERIC,4,2)3948/38

and

(DT_NUMERIC,4,2)3948/(DT_NUMERIC,4,2)38

since for binary operators, the operands will be implicitly cast to an appropriate result type before the operation is performed.

Thanks
Mark

|||Thankyou so much for your effort guys.

The problem was, SSIS Derived Column Trasnformation was truncating while TSQL was rounding.

So what I did is, I would force SSIS to compute higher precision value and then ROUND it to the precision I want.

so

ROUND( ((DT_DECIMAL,4)6800) / ((DT_NUMERIC, 4) 464) , 2)

and it returned me the 14.66 which matched the result of TSQL.

Thanks once again for your inputs which helped me

DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

Hi,
I have a derived column transformation which adds a new column of type Decimal with scale 2.

The expression is (6800 / 464)

Runs fine but it returns

14.65

While if I run any of the following queries in query analyzer

SELECT 6800/ 464

OR

SELECTCONVERT(DECIMAL(10,2), 6800/ 464)

They give me 14.66.

My question is, why both the tool of same product differs in the way they work ? and How could I have SSIS to work like TSQL ? I tried typecasting in derived column expression and ROUND function too. But still, the output is same.

Any help in this will sincerely be appreciated.

Thanks

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

|||

DarrenSQLIS wrote:

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

But the intuitive way doesn't work: (both report 14.65)

(DT_NUMERIC,6,2)((DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464)
(DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464|||I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.|||

DarrenSQLIS wrote:

I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

The only reason I say that casting the inputs is because in this example, they would be considered integers, not numerics.

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

|||

Phil Brammer wrote:

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

It is important to note that the SSIS expression language follows C-style rules, not T-SQL. Behaviour in this case is a little different.

Phil Brammer wrote:

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

In the case of the SSIS expression language, there should indeed be no difference between

(DT_NUMERIC,4,2)3948/38

and

(DT_NUMERIC,4,2)3948/(DT_NUMERIC,4,2)38

since for binary operators, the operands will be implicitly cast to an appropriate result type before the operation is performed.

Thanks
Mark

|||Thankyou so much for your effort guys.

The problem was, SSIS Derived Column Trasnformation was truncating while TSQL was rounding.

So what I did is, I would force SSIS to compute higher precision value and then ROUND it to the precision I want.

so

ROUND( ((DT_DECIMAL,4)6800) / ((DT_NUMERIC, 4) 464) , 2)

and it returned me the 14.66 which matched the result of TSQL.

Thanks once again for your inputs which helped me

DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

Hi,
I have a derived column transformation which adds a new column of type Decimal with scale 2.

The expression is (6800 / 464)

Runs fine but it returns

14.65

While if I run any of the following queries in query analyzer

SELECT 6800 /

464

OR

SELECT CONVERT(DECIMAL(10,2), 6800 / 464)

They give me 14.66.

My question is, why both the tool of same product differs in the way they work ? and How could I have SSIS to work like TSQL ? I tried typecasting in derived column expression and ROUND function too. But still, the output is same.

Any help in this will sincerely be appreciated.

Thanks

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

|||

DarrenSQLIS wrote:

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

But the intuitive way doesn't work: (both report 14.65)

(DT_NUMERIC,6,2)((DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464)
(DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464|||I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.|||

DarrenSQLIS wrote:

I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

The only reason I say that casting the inputs is because in this example, they would be considered integers, not numerics.

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

|||

Phil Brammer wrote:

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

It is important to note that the SSIS expression language follows C-style rules, not T-SQL. Behaviour in this case is a little different.

Phil Brammer wrote:

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

In the case of the SSIS expression language, there should indeed be no difference between

(DT_NUMERIC,4,2)3948/38

and

(DT_NUMERIC,4,2)3948/(DT_NUMERIC,4,2)38

since for binary operators, the operands will be implicitly cast to an appropriate result type before the operation is performed.

Thanks
Mark

|||Thankyou so much for your effort guys.

The problem was, SSIS Derived Column Trasnformation was truncating while TSQL was rounding.

So what I did is, I would force SSIS to compute higher precision value and then ROUND it to the precision I want.

so

ROUND( ((DT_DECIMAL,4)6800) / ((DT_NUMERIC, 4) 464) , 2)

and it returned me the 14.66 which matched the result of TSQL.

Thanks once again for your inputs which helped me

Decimal vs. Float Data Type

I've been looking at the Decimal and Float data types. When it come to
storing a value such as 10.55%, 25.00% or 99.99%, which is the most
appropriate?I recommend decimal instead of float if you need exact decimal values .
Float and real follow the IEEE 754 specification on approximate numeric data
types and cannot store all decimal values accurately. See "approximate data
types" in the the Books Online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
news:43D7259F-B754-4753-AB5B-85CEABDA319F@.microsoft.com...
> I've been looking at the Decimal and Float data types. When it come to
> storing a value such as 10.55%, 25.00% or 99.99%, which is the most
> appropriate?|||So in this case, 10.55%, 100.99%, etc. I would use Decimal(5.2), Precision =5, Scale = 2?
Thanks
"Dan Guzman" wrote:
> I recommend decimal instead of float if you need exact decimal values .
> Float and real follow the IEEE 754 specification on approximate numeric data
> types and cannot store all decimal values accurately. See "approximate data
> types" in the the Books Online for details.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
> news:43D7259F-B754-4753-AB5B-85CEABDA319F@.microsoft.com...
> > I've been looking at the Decimal and Float data types. When it come to
> > storing a value such as 10.55%, 25.00% or 99.99%, which is the most
> > appropriate?
>|||> So in this case, 10.55%, 100.99%, etc. I would use Decimal(5.2), Precision
> => 5, Scale = 2?
It depends on the range you need to store. Decimal(5,2) is sufficient to
store -999.99 through +999.99.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
news:D535955C-A6AF-4C3B-B689-22BEC7D84A44@.microsoft.com...
> So in this case, 10.55%, 100.99%, etc. I would use Decimal(5.2), Precision
> => 5, Scale = 2?
> Thanks
>
> "Dan Guzman" wrote:
>> I recommend decimal instead of float if you need exact decimal values .
>> Float and real follow the IEEE 754 specification on approximate numeric
>> data
>> types and cannot store all decimal values accurately. See "approximate
>> data
>> types" in the the Books Online for details.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Greg" <AccessVBAnet@.newsgroups.nospam> wrote in message
>> news:43D7259F-B754-4753-AB5B-85CEABDA319F@.microsoft.com...
>> > I've been looking at the Decimal and Float data types. When it come to
>> > storing a value such as 10.55%, 25.00% or 99.99%, which is the most
>> > appropriate?|||I actually had this doubt as well for a long time. The number of bytes that
each datatype use does not make much difference (check BOL). So when I
require exactness and it comes to numbers with not many decimal positions
and depending on the operation (which in some cases can make the exact
numeric data types behave not so exact), I definitely prefer decimal data
type. In some other cases the float can be useful.
I think you can read Hugo Kornelis' article 'So-called "exact" numerics are
not at all exact!' on SQLblog.com, which helped me a lot to understand
better when to use each datatype.
Alan Ferrandiz Langley [MCT]
"Greg" <AccessVBAnet@.newsgroups.nospam> escribió en el mensaje
news:43D7259F-B754-4753-AB5B-85CEABDA319F@.microsoft.com...
> I've been looking at the Decimal and Float data types. When it come to
> storing a value such as 10.55%, 25.00% or 99.99%, which is the most
> appropriate?
>|||On Mon, 28 Jan 2008 01:47:39 -0500, Alan Ferrandiz Langley [MCT] wrote:
(snip)
>I think you can read Hugo Kornelis' article 'So-called "exact" numerics are
>not at all exact!' on SQLblog.com, which helped me a lot to understand
>better when to use each datatype.
Hi Alan,
Thanks for the free plug, and the nice words. For anyone interested,
here is a direct link to that article:
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/10/17/so-called-exact-numerics-are-not-at-all-exact.aspx
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Decimal vs currency....

It seems that the "decimal" type allows more flexibility but also uses more storage than "money". On the other hand, does "money" have any special t-sql functions that understand its unquie nature? (e.g. adding two money values together with no cents round-off error).

For example, would the number 12345.12 be declared as a decimal(7,2) or currency?

Barkingdog

There aren't any special functions that explicitly recognize a monetary data type. How you declare it really depends upon what you are storing in there. If I'm storing pricing or something else that is in monetary units, I use money. If I'm doing complex financial caculations, I use decimal, because the money data type is accurate to 4 decimal places and for calculating a lot of financial stuff, you need better accuracy than that.

decimal to numeric conversion

Hi guys,

how do i convert a decimal to numeric data type?

nhoyti:

convert a decimal to numeric data type

what i understood is, you are having a field in database with Decimal datatype and you want to retrieve it as Numeric Field...

you can use CAST() function as..

SELECT CAST(colName AS INT) FROM TABLE

here colName is the Field with Decimal DataType...

hope it helps./.

|||

i have a column in my database which is a decimal... and i want to display the decimal to numeric data type

|||

kaushalparik27:

what i understood is, you are having a field in database with Decimal datatype and you want to retrieve it as Numeric Field...

you can use CAST() function as..

SELECT CAST(colName AS INT) FROM TABLE

here colName is the Field with Decimal DataType...

did you try as above solution ??... i think it will convert Decimal to Numeric DataType...

Decimal Places

I have 2 fields X and Y, both are type decimal(38,20). I need to have as many decimal places as possible in my calculations for these numbers. This is the first time I've had to deal with this type of "precise" data so if you see something completely wrong just say so...

Anyway, I need to divide 2 numbers to get an answer...

I'm dividing: -118.84 by 867561.993

selectcast(net_expenses/fund_shares_outstanding asdecimal(35,20)) from xyz where blah blah

My query returns: -0.00013600000000000000

This same calculation with the same numbers in Excel returns -0.00013698156553522500

What do I need to do to get the more precise answer?

Precision, scale, of output cannot exceede precision of input.

-0.00013698156553522500 is gibberish past the third decimal place unless you are working with

-118.84000000000000000000

and

867561.99300000000000000000

DECLARE @.float1float

DECLARE @.float2float

SET @.float1=-118.84

SET @.float2= 867561.993

SELECT @.float1/@.float2

Decimal or Float Type in Percent

Below is a sniplet of a select expression that returns a decimal number like
.6153329998
The result is correct, but when I say FormatPercent(objRS("myPercent"),3) in
ASP, I get a type mismatch error.
Both myField1 and myField2 are integer type. I have to CONVERT myField1 into
decimal in order to get a correct return in QA.
Can someone suggest a better data type to convert myField1 so not only will
myPercent render correct in QA, but also allow me to use FormatPercent in
ASP?
CODE:
SUM(CONVERT(DECIMAL(18, 10), myField1)) / SUM(myField2) AS myPercentHi
You may want to use your convert function after summing
CONVERT(DECIMAL(18, 10), SUM(myField1))/ SUM(myField2)
You may want to stick with DECIMAL but change your scale and precision.
John
"Scott" <sbailey@.mileslumber.com> wrote in message
news:O74yeO1AGHA.4080@.TK2MSFTNGP14.phx.gbl...
> Below is a sniplet of a select expression that returns a decimal number
> like .6153329998
> The result is correct, but when I say FormatPercent(objRS("myPercent"),3)
> in ASP, I get a type mismatch error.
> Both myField1 and myField2 are integer type. I have to CONVERT myField1
> into decimal in order to get a correct return in QA.
> Can someone suggest a better data type to convert myField1 so not only
> will myPercent render correct in QA, but also allow me to use
> FormatPercent in ASP?
>
> CODE:
> SUM(CONVERT(DECIMAL(18, 10), myField1)) / SUM(myField2) AS myPercent
>|||what would the synta be to convert to float?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ebALxS%23AGHA.808@.TK2MSFTNGP11.phx.gbl...
> Hi
> You may want to use your convert function after summing
> CONVERT(DECIMAL(18, 10), SUM(myField1))/ SUM(myField2)
> You may want to stick with DECIMAL but change your scale and precision.
> John
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:O74yeO1AGHA.4080@.TK2MSFTNGP14.phx.gbl...
>|||Hi
The same but use float as the data type, the syntax for convert is described
in Books online as:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
John
"Scott" <sbailey@.mileslumber.com> wrote in message
news:eEj4bU$AGHA.3840@.TK2MSFTNGP15.phx.gbl...
> what would the synta be to convert to float?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:ebALxS%23AGHA.808@.TK2MSFTNGP11.phx.gbl...
>

Decimal Digits Lost When Using Decimal(9,2) Parameter

Hello,
I am trying to use a decimal type parameter in my stored procedure.
When I pass any decimal value to it (for example: 12.34), the decimal
part is always truncated off. I am left with only 12 as the value that
get put into my table. I have checked the obvious...the datatype is
decimal in the procedure. The data type for the column is decimal(9,2)
in my table. Any idea about what is going on here?Can you show some code so we can repro/diagnose?
<joey.powell@.topscene.com> wrote in message
news:1125598102.549921.46630@.g47g2000cwa.googlegroups.com...
> Hello,
> I am trying to use a decimal type parameter in my stored procedure.
> When I pass any decimal value to it (for example: 12.34), the decimal
> part is always truncated off. I am left with only 12 as the value that
> get put into my table. I have checked the obvious...the datatype is
> decimal in the procedure. The data type for the column is decimal(9,2)
> in my table. Any idea about what is going on here?
>|||joey.powell@.topscene.com wrote:
> Hello,
> I am trying to use a decimal type parameter in my stored procedure.
> When I pass any decimal value to it (for example: 12.34), the decimal
> part is always truncated off. I am left with only 12 as the value that
> get put into my table. I have checked the obvious...the datatype is
> decimal in the procedure. The data type for the column is decimal(9,2)
> in my table. Any idea about what is going on here?
You are proably using the value in a calculation that includes an
integer data type. Make sure all operations use decimal as the data
type.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Joey,
The datatype DECIMAL is shorthand for DECIMAL(18,0). Change
the data type of the procedure parameter to DECIMAL(9,2).
Steve Kass
Drew University
joey.powell@.topscene.com wrote:

>Hello,
>I am trying to use a decimal type parameter in my stored procedure.
>When I pass any decimal value to it (for example: 12.34), the decimal
>part is always truncated off. I am left with only 12 as the value that
>get put into my table. I have checked the obvious...the datatype is
>decimal in the procedure. The data type for the column is decimal(9,2)
>in my table. Any idea about what is going on here?
>
>

Decimal data type: storage in bytes?

SQL 7.0
What is the storage in bytes of decimal data type?
Thanks,
DonDepends on the precision you want. Can go from 5 to 17 bytes. Look up the
topic "decimal and numeric (T-SQL)" in SQL Server 7.0 or 2000 Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:95ff01c4338d$ae53cf70$a501280a@.phx.gbl...
SQL 7.0
What is the storage in bytes of decimal data type?
Thanks,
Don

Decimal data type: storage in bytes?

SQL 7.0
What is the storage in bytes of decimal data type?
Thanks,
Don
Depends on the precision you want. Can go from 5 to 17 bytes. Look up the
topic "decimal and numeric (T-SQL)" in SQL Server 7.0 or 2000 Books Online.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:95ff01c4338d$ae53cf70$a501280a@.phx.gbl...
SQL 7.0
What is the storage in bytes of decimal data type?
Thanks,
Don

Decimal data type: storage in bytes?

SQL 7.0
What is the storage in bytes of decimal data type?
Thanks,
DonDepends on the precision you want. Can go from 5 to 17 bytes. Look up the
topic "decimal and numeric (T-SQL)" in SQL Server 7.0 or 2000 Books Online.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:95ff01c4338d$ae53cf70$a501280a@.phx.gbl...
SQL 7.0
What is the storage in bytes of decimal data type?
Thanks,
Don

decimal data type serialized to binary

Hi,
I have the value 1234.1234 in a decimal field, converted to binary
(cast(myField as binary(13)), it gives 0x000000000013060001883a8f49. When I
use SqlDecimal in .NET, for the same value, I get 0x883a8f49 which makes
0x1306 the precision and scale (precision 19, so 0x13 and scale 6, so 0x06),
but what is the 0x0001? moreover, when I export to a file using bcp native
file type, the 0x0001 becomes 0x01 (loses a byte)
Can someone shed a light on this please?
Thanks
ThunderMusic
I think I found it. It would be to indicate a positive number. I still don't
understand why the cast operation gives 0001 and bcp gives 01, but still, it
seems to match anyway... Can someone confirm?
Thanks
ThunderMusic
"ThunderMusic" <NoSpAmdanlatathotmaildotcom@.NoSpAm.com> wrote in message
news:%23Ebg7yEgHHA.4260@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have the value 1234.1234 in a decimal field, converted to binary
> (cast(myField as binary(13)), it gives 0x000000000013060001883a8f49. When
> I use SqlDecimal in .NET, for the same value, I get 0x883a8f49 which makes
> 0x1306 the precision and scale (precision 19, so 0x13 and scale 6, so
> 0x06), but what is the 0x0001? moreover, when I export to a file using bcp
> native file type, the 0x0001 becomes 0x01 (loses a byte)
> Can someone shed a light on this please?
> Thanks
> ThunderMusic
>

Decimal Data Type losing scale?

I'm trying to update a table that has decimal values. They are defined at
precision of 15 and scale of 2.
When I use a T-Sql update query, I'm sending a value "with pennies", but the
table is only reflecting the integer portion. I've even tried
UPDATE (myTableName)
SET myCost = CAST(@.Cost AS decimal(15,2))
without success.
When I step through the code of my VB.NET program and view the value of item
I've added to the parameters collection of my update query command object, I
DO SEE the pennies. When the query has executed, they aren't in the table.
I CAN TYPE the pennies into the record in the table with Enterprise Mgr. An
d
I can retrieve them with my program. But I can't send new values with
pennies and get them respected in the new table values.
I have seen in Books on Line that we are supposed to explicitly CAST our
decimal values. But shouldn't this take care of it?Hello Q,
When you run profiler what do you see the values being sent as? If you have
your precision and scale not matching exactly in your VB.NET application
it can cause it to send it incorrectly to the database.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I'm trying to update a table that has decimal values. They are
> defined at precision of 15 and scale of 2.
> When I use a T-Sql update query, I'm sending a value "with pennies",
> but the
> table is only reflecting the integer portion. I've even tried
> UPDATE (myTableName)
> SET myCost = CAST(@.Cost AS decimal(15,2))
> without success.
> When I step through the code of my VB.NET program and view the value
> of item I've added to the parameters collection of my update query
> command object, I DO SEE the pennies. When the query has executed,
> they aren't in the table. I CAN TYPE the pennies into the record in
> the table with Enterprise Mgr. And I can retrieve them with my
> program. But I can't send new values with pennies and get them
> respected in the new table values.
> I have seen in Books on Line that we are supposed to explicitly CAST
> our decimal values. But shouldn't this take care of it?
>|||Is it possible that you've failed to specify precision and scale
for your decimal parameter? The default precision and scale for
a decimal parameter is precision 18, scale 0. If this doesn't seem
to help, could you post the relevant VB.NET code dealing with
the parameter?
Steve Kass
Drew University
Q Johnson wrote:

>I'm trying to update a table that has decimal values. They are defined at
>precision of 15 and scale of 2.
>When I use a T-Sql update query, I'm sending a value "with pennies", but th
e
>table is only reflecting the integer portion. I've even tried
> UPDATE (myTableName)
> SET myCost = CAST(@.Cost AS decimal(15,2))
>without success.
>When I step through the code of my VB.NET program and view the value of ite
m
>I've added to the parameters collection of my update query command object,
I
>DO SEE the pennies. When the query has executed, they aren't in the table.
>I CAN TYPE the pennies into the record in the table with Enterprise Mgr. A
nd
>I can retrieve them with my program. But I can't send new values with
>pennies and get them respected in the new table values.
>I have seen in Books on Line that we are supposed to explicitly CAST our
>decimal values. But shouldn't this take care of it?
>
>

Decimal data type

If I create a column with data type Decimal(10,4), when a data is being
stored in that column, will it format the data to be Decimal(10,4) ?
For example: if I enter 1.5, will it be 1.5000, or it will stay as 1.5 ?
When I tried it, it stays at 1.5.
Thanks.fniles wrote:

> If I create a column with data type Decimal(10,4), when a data is
> being stored in that column, will it format the data to be
> Decimal(10,4) ? For example: if I enter 1.5, will it be 1.5000, or
> it will stay as 1.5 ? When I tried it, it stays at 1.5.
No it will always stay 1.5 because 1.5000 is just a string
representation of a number. You best handle that in your application.
HTH,
Stijn Verrept.|||"fniles" <fniles@.pfmail.com> wrote in message
news:umjqImN$FHA.2520@.TK2MSFTNGP15.phx.gbl...
> If I create a column with data type Decimal(10,4), when a data is being
> stored in that column, will it format the data to be Decimal(10,4) ?
> For example: if I enter 1.5, will it be 1.5000, or it will stay as 1.5 ?
> When I tried it, it stays at 1.5.
> Thanks.
I'm going to guess that you're looking at the data in Enterprise Manager.
Go to Query Analyser and Select from that table.
In my SQL Server, EM formats it as 1.5 but in QA, 1.500.|||numbers are not stored with a format. format is a matter of display -
e.g., in Query Analyzer look in Tools \ Options \ Connections - see if
Use regional settings is checked.
fniles wrote:
> If I create a column with data type Decimal(10,4), when a data is being
> stored in that column, will it format the data to be Decimal(10,4) ?
> For example: if I enter 1.5, will it be 1.5000, or it will stay as 1.5 ?
> When I tried it, it stays at 1.5.
> Thanks.
>|||"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:e3yclwN$FHA.4028@.tk2msftngp13.phx.gbl...
> "fniles" <fniles@.pfmail.com> wrote in message
> news:umjqImN$FHA.2520@.TK2MSFTNGP15.phx.gbl...
> I'm going to guess that you're looking at the data in Enterprise Manager.
> Go to Query Analyser and Select from that table.
> In my SQL Server, EM formats it as 1.5 but in QA, 1.500.
Add an extra 0 to that output.
One rule to remember, SQL does not FORMAT data in tables, it stores data.
It will make your life simpler if you remember that rule.