Numeric Representation of Data

In MS Access, for numeric fields, the decimal places shown can be defined as "Auto" meaning that the database will determine the number of decimal places to show based on the content of the field (i.e. 1.0, 0.75, 1.125).

In SQL Server for the same field, it appears that decimal precision is hard coded resulting in a fixed representation (i.e. 1.000, 0.750, 1.125)

Is there a way to make the decimal representation in SQL Server more like Access where trailing zeros are truncated?

See SQL Server 2005 Books Online topic:

float and real (Transact-SQL)

http://msdn2.microsoft.com/en-us/library/ms173773.aspx

|||

Perfect.

thanks for your help.

numbering in group

I want to add numbering in group in the reports.(number each row in the detail)

when I worked with access it was possible.

can I do it in the report services?

thanks!Ok I find it:

=rownumber("MyGropName")

thankssql

Number formating

Just moving from Access to SQL. My end goal is to have SQL mail all my fini
shed reports for me but my users need to having numbers separated by commas
and all monetary values prefixed with ￡. Have looked at books on line for
formats and number separat
ors but cannot find anything.
SELECT tblTradeData.[Date] AS Today,
SUM(TradeData.Units) AS TotalUnits,
SUM(tblTradeData.[Value]) AS TotalValue,
SUM(tblTradeData.Cost) AS TotalCost
FROM qryToday INNER JOIN
tblTradeData ON qryToday.TodayDate = tblTradeData.[Date]
GROUP BY tblTradeData.[Date]SQL Server can easily format float, real, money and datetimes... ( See the
convert command in books on line.)... For other formatting, as you want, you
will either have to do it manually using replace, etc... or (probably a
better solution) use a report writing tool. (Crystal, MS Report Services.)
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:66329495-D5D4-440D-A985-7C4DFED5CA13@.microsoft.com...

quote:

---

> Just moving from Access to SQL. My end goal is to have SQL mail all my

---

finished reports for me but my users need to having numbers separated by
commas and all monetary values prefixed with . Have looked at books on
line for formats and number separators but cannot find anything.

quote:

---

>
> SELECT tblTradeData.[Date] AS Today,
> SUM(TradeData.Units) AS TotalUnits,
> SUM(tblTradeData.[Value]) AS TotalValue,
> SUM(tblTradeData.Cost) AS TotalCost
> FROM qryToday INNER JOIN
> tblTradeData ON qryToday.TodayDate =

---

tblTradeData.[Date]

quote:

---

> GROUP BY tblTradeData.[Date]

---

|||I' am not completely sure what Kevin ask's but here is a suggestion :
SELECT
'$ <<-use Pound sign instead '+ convert(varchar(20), tblTradeData.[Date])
AS Today,
', $ '+ convert(varchar(20), SUM(TradeData.Units)) AS TotalUnits,
', $ '+ convert(varchar(20), SUM(tblTradeData.[Value])) AS TotalValue,
', $ '+ convert(varchar(20), SUM(tblTradeData.Cost)) AS TotalCost
FROM qryToday INNER JOIN
tblTradeData ON qryToday.TodayDate =
tblTradeData.[Date]
GROUP BY tblTradeData.[Date]
Depending on the length of the 'values' varchar(20) can be altered to
a longer or shorter string, for fixed length char can also be used instead
of
varchar.
Does this help ?
ben brugman
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:#sJJK2#6DHA.2480@.TK2MSFTNGP12.phx.gbl...

quote:

---

> SQL Server can easily format float, real, money and datetimes... ( See the
> convert command in books on line.)... For other formatting, as you want,

---

you

quote:

---

> will either have to do it manually using replace, etc... or (probably a
> better solution) use a report writing tool. (Crystal, MS Report Services.)
>
> "Kevin" <anonymous@.discussions.microsoft.com> wrote in message
> news:66329495-D5D4-440D-A985-7C4DFED5CA13@.microsoft.com...
> finished reports for me but my users need to having numbers separated by
> commas and all monetary values prefixed with . Have looked at books on
> line for formats and number separators but cannot find anything.
> tblTradeData.[Date]
>

---

|||In addition to my last mail.
The [no lock] hint, does increase the change on problems,
because of this hint, if there is a update on the number
from another process which has not committed yet, the
code will keep looping.
If the [no lock] hint is taken out the process will wait in
this place until the other process finishes and then continue.
This will consume less CPU.
If the [no lock] was placed because of a (logical) deadlock,
(because two processes both ask for two numbers but
in a different order, one asks first for the Tracking_id and then
for the Info_ID, the other process the other way round) and
both are done in a transaction context then these two processes
keep the stored procedure looping. (Both have one number and
won't stop till they get the other number). With the [no lock] hint
taken out the sql-server will detect a deadlock and one of the
processes will be choosen as a deadlock victim.
(A deadlock victim is better than looping processes).
Please keep us informed, of what your problem was and which
solution you have choosen,
ben brugman
"ben brugman" <ben@.niethier.nl> wrote in message
news:#QEjaS$6DHA.2628@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I' am not completely sure what Kevin ask's but here is a suggestion :
> SELECT
> '$ <<-use Pound sign instead '+ convert(varchar(20), tblTradeData.[Date])
> AS Today,
> ', $ '+ convert(varchar(20), SUM(TradeData.Units)) AS TotalUnits,
> ', $ '+ convert(varchar(20), SUM(tblTradeData.[Value])) AS TotalValue,
> ', $ '+ convert(varchar(20), SUM(tblTradeData.Cost)) AS TotalCost
> FROM qryToday INNER JOIN
> tblTradeData ON qryToday.TodayDate =
> tblTradeData.[Date]
> GROUP BY tblTradeData.[Date]
> Depending on the length of the 'values' varchar(20) can be altered to
> a longer or shorter string, for fixed length char can also be used instead
> of
> varchar.
> Does this help ?
> ben brugman
>
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:#sJJK2#6DHA.2480@.TK2MSFTNGP12.phx.gbl...
the[QUOTE]
> you
Services.)[QUOTE]
my[QUOTE]
by[QUOTE]
>

---

Number formating

Just moving from Access to SQL. My end goal is to have SQL mail all my finished reports for me but my users need to having numbers separated by commas and all monetary values prefixed with £. Have looked at books on line for formats and number separators but cannot find anything
SELECT tblTradeData.[Date] AS Today,
SUM(TradeData.Units) AS TotalUnits,
SUM(tblTradeData.[Value]) AS TotalValue,
SUM(tblTradeData.Cost) AS TotalCos
FROM qryToday INNER JOI
tblTradeData ON qryToday.TodayDate = tblTradeData.[Date
GROUP BY tblTradeData.[Date]SQL Server can easily format float, real, money and datetimes... ( See the
convert command in books on line.)... For other formatting, as you want, you
will either have to do it manually using replace, etc... or (probably a
better solution) use a report writing tool. (Crystal, MS Report Services.)
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:66329495-D5D4-440D-A985-7C4DFED5CA13@.microsoft.com...
> Just moving from Access to SQL. My end goal is to have SQL mail all my
finished reports for me but my users need to having numbers separated by
commas and all monetary values prefixed with £. Have looked at books on
line for formats and number separators but cannot find anything.
>
> SELECT tblTradeData.[Date] AS Today,
> SUM(TradeData.Units) AS TotalUnits,
> SUM(tblTradeData.[Value]) AS TotalValue,
> SUM(tblTradeData.Cost) AS TotalCost
> FROM qryToday INNER JOIN
> tblTradeData ON qryToday.TodayDate =tblTradeData.[Date]
> GROUP BY tblTradeData.[Date]|||I' am not completely sure what Kevin ask's but here is a suggestion :
SELECT
'$ <<-use Pound sign instead '+ convert(varchar(20), tblTradeData.[Date])
AS Today,
', $ '+ convert(varchar(20), SUM(TradeData.Units)) AS TotalUnits,
', $ '+ convert(varchar(20), SUM(tblTradeData.[Value])) AS TotalValue,
', $ '+ convert(varchar(20), SUM(tblTradeData.Cost)) AS TotalCost
FROM qryToday INNER JOIN
tblTradeData ON qryToday.TodayDate = tblTradeData.[Date]
GROUP BY tblTradeData.[Date]
Depending on the length of the 'values' varchar(20) can be altered to
a longer or shorter string, for fixed length char can also be used instead
of
varchar.
Does this help ?
ben brugman
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:#sJJK2#6DHA.2480@.TK2MSFTNGP12.phx.gbl...
> SQL Server can easily format float, real, money and datetimes... ( See the
> convert command in books on line.)... For other formatting, as you want,
you
> will either have to do it manually using replace, etc... or (probably a
> better solution) use a report writing tool. (Crystal, MS Report Services.)
>
> "Kevin" <anonymous@.discussions.microsoft.com> wrote in message
> news:66329495-D5D4-440D-A985-7C4DFED5CA13@.microsoft.com...
> > Just moving from Access to SQL. My end goal is to have SQL mail all my
> finished reports for me but my users need to having numbers separated by
> commas and all monetary values prefixed with £. Have looked at books on
> line for formats and number separators but cannot find anything.
> >
> >
> > SELECT tblTradeData.[Date] AS Today,
> > SUM(TradeData.Units) AS TotalUnits,
> > SUM(tblTradeData.[Value]) AS TotalValue,
> > SUM(tblTradeData.Cost) AS TotalCost
> > FROM qryToday INNER JOIN
> > tblTradeData ON qryToday.TodayDate => tblTradeData.[Date]
> > GROUP BY tblTradeData.[Date]
>|||In addition to my last mail.
The [no lock] hint, does increase the change on problems,
because of this hint, if there is a update on the number
from another process which has not committed yet, the
code will keep looping.
If the [no lock] hint is taken out the process will wait in
this place until the other process finishes and then continue.
This will consume less CPU.
If the [no lock] was placed because of a (logical) deadlock,
(because two processes both ask for two numbers but
in a different order, one asks first for the Tracking_id and then
for the Info_ID, the other process the other way round) and
both are done in a transaction context then these two processes
keep the stored procedure looping. (Both have one number and
won't stop till they get the other number). With the [no lock] hint
taken out the sql-server will detect a deadlock and one of the
processes will be choosen as a deadlock victim.
(A deadlock victim is better than looping processes).
Please keep us informed, of what your problem was and which
solution you have choosen,
ben brugman
"ben brugman" <ben@.niethier.nl> wrote in message
news:#QEjaS$6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> I' am not completely sure what Kevin ask's but here is a suggestion :
> SELECT
> '$ <<-use Pound sign instead '+ convert(varchar(20), tblTradeData.[Date])
> AS Today,
> ', $ '+ convert(varchar(20), SUM(TradeData.Units)) AS TotalUnits,
> ', $ '+ convert(varchar(20), SUM(tblTradeData.[Value])) AS TotalValue,
> ', $ '+ convert(varchar(20), SUM(tblTradeData.Cost)) AS TotalCost
> FROM qryToday INNER JOIN
> tblTradeData ON qryToday.TodayDate => tblTradeData.[Date]
> GROUP BY tblTradeData.[Date]
> Depending on the length of the 'values' varchar(20) can be altered to
> a longer or shorter string, for fixed length char can also be used instead
> of
> varchar.
> Does this help ?
> ben brugman
>
> "Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
> news:#sJJK2#6DHA.2480@.TK2MSFTNGP12.phx.gbl...
> > SQL Server can easily format float, real, money and datetimes... ( See
the
> > convert command in books on line.)... For other formatting, as you want,
> you
> > will either have to do it manually using replace, etc... or (probably a
> > better solution) use a report writing tool. (Crystal, MS Report
Services.)
> >
> >
> > "Kevin" <anonymous@.discussions.microsoft.com> wrote in message
> > news:66329495-D5D4-440D-A985-7C4DFED5CA13@.microsoft.com...
> > > Just moving from Access to SQL. My end goal is to have SQL mail all
my
> > finished reports for me but my users need to having numbers separated
by
> > commas and all monetary values prefixed with £. Have looked at books on
> > line for formats and number separators but cannot find anything.
> > >
> > >
> > > SELECT tblTradeData.[Date] AS Today,
> > > SUM(TradeData.Units) AS TotalUnits,
> > > SUM(tblTradeData.[Value]) AS TotalValue,
> > > SUM(tblTradeData.Cost) AS TotalCost
> > > FROM qryToday INNER JOIN
> > > tblTradeData ON qryToday.TodayDate => > tblTradeData.[Date]
> > > GROUP BY tblTradeData.[Date]
> >
> >
>

Null values in queries...

I am relatively new to SQL and Access...

I am experiencing a problem that involves 3 queries in my 2000 access db.
The first 2 queries each perform a different multiplication calculation, and both work fine when I run them individually. But on the third query, I am trying to sum the values derived in the 2 prior queries (grouping by pk RAA_Num), and am finding that if the derived value of EITHER ONE of the first 2 queries is NULL, then on the third query, NEITHER will show up...

This is the code for that third query I have so far...I am trying to make it so that if a value for one query is null, it will be replaced with 0...

SELECT Expense.RAA_Num, (Sum(IIf(isnull([AA TOTAL CAR LODGE PERDIEM].AATOTALCARLODGEPERDIEM=0, [AA TOTAL MISC].AATOTALMISC=0)))) AS Expr1

FROM (Expense INNER JOIN [AA TOTAL CAR LODGE PERDIEM] ON Expense.RAA_Num = [AA TOTAL CAR LODGE PERDIEM].RAA_Num) INNER JOIN [AA TOTAL MISC] ON [AA TOTAL CAR LODGE PERDIEM].RAA_Num = [AA TOTAL MISC].RAA_Num

GROUP BY Expense.RAA_Num;

Any suggestions would be greatly appreciated! I am so stuck!NZ(column, 0)|||Nvl(column,0)
Cheers|||NVL is Oracle, not Access.

Null Values in Destination

Hi to all!

I created a simple package – data flow task, containing OLE DB Source, with Data Access Mode – SQL command and OLE DB Destination.

My SQL command is

update support_incident

set date_closed = rn_edit_date

where status_text = 'Closed' and date_closed is null

DECLARE @.CMonth as datetime

DECLARE @.LMonth as datetime

SET @.CMonth = '10/1/2006'

SET @.LMonth = DateAdd (m, -1, @.CMonth)

select

@.LMonth as Reporting_Date,

(select count(support_incident_id) from support_Incident where rn_create_date >= @.LMonth and rn_create_date < @.CMonth) as Opened,

(select count(support_incident_id) from support_Incident where Date_Closed >= @.LMonth and Date_Closed < @.CMonth) as Closed,

(select count(support_incident_id) from support_Incident where rn_create_date < @.CMonth and (Date_Closed >= @.CMonth or Date_Closed is null)) as Backlog,

(select count(support_incident_id) from support_Incident where priority in (0,1) and (rn_create_date < Dateadd(d,-3,@.CMonth)) and support_incident_id in (select support_incident_id from support_Incident where rn_create_date < @.CMonth and (Date_Closed >= @.CMonth or Date_Closed is null))) as Critical_Backlog,

((select count(support_incident_id) from support_Incident where rn_create_date < @.CMonth and (Date_Closed >= @.CMonth or Date_Closed is null)) - (select count(support_incident_id) from support_Incident where priority in (0,1) and (rn_create_date < Dateadd(d,-3,@.CMonth)) and support_incident_id in (select support_incident_id from support_Incident where rn_create_date < @.CMonth and (Date_Closed >= @.CMonth or Date_Closed is null)))) as Operational_Backlog

from support_incident

where support_incident_code = '1000'

The SQL statement is successfully parsed and I can see the columns and numbers in Preview Query Results. The package is executed without errors and warnings with success. In Destination server, the table is created with columns according to query, but the problem is no numbers are written in rows, all values are nulls.

I can’t figure out what is the reason. Can anybody to help me?

Thank you.

First avenue of investigation would be to use a data viewer to see if any data enters the pipeline (and what that data is).

-Jamie

|||

Thank you Jamie for your respond. Created data viewers as you suggested, and saw that at OLE DB Source Output Data Viewer appears : Total rows: 0, buffers:0.

So, although OLE DB Source is "successful'', I need to search another approach of sending the data. May be the reason in SQL statement?

Anybody had such problems?

Thank you.

|||Have you tried wrapping this script code in a stored procedure? I suspect that the nested select statement return columns could be confusing SSIS. It's possible that SSIS could detect the return columns differently when the code is in a stored procedure.

I don't know if it will fix the problem or not. However, I have a lot of code like this in stored procedures that I'm calling from SSIS without any problems.

Thanks,
Greg Van Mullem|||Here is another idea. Try putting this command at the top of your script. This command fixes a lot of odd problems in SQL 2000 DTS. Definetly worth a try here too.

SET NOCOUNT ON;

Thanks,
Greg Van Mullem|||

Thank you Greg.

No, I did not try. I do not have much experience with stored procedures, only college projects practicing.

Now I am going to read MS SQL Server 2005 - how to create stored procedures - make this script to run monthly. Actually, I have to create and "automate" Crystal Reports using this monthly information. So, I need to change

SET @.CMonth = '10/1/2006'

in the procedure in such a way it would run monthly.

Thanks once again to all.

|||

Now, I received an Error Message and My OLE DB Source is red. I am afraid, I cannot use this statement. My Error message:

SSIS package "PivSupportmonthly.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Error: 0xC020901C at Data Flow Task, PivSupportIncident [1]: There was an error with output column "Reporting_Date" (625) on output "OLE DB Source Output" (11). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

Error: 0xC020902A at Data Flow Task, PivSupportIncident [1]: The "output column "Reporting_Date" (625)" failed because truncation occurred, and the truncation row disposition on "output column "Reporting_Date" (625)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "PivSupportIncident" (1) returned error code 0xC020902A.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.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (1657)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at PivSupportmonthly: The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "PivSupportmonthly.dtsx" finished: Failure.

Thanks Greg for your following.

|||

"Text was truncated or "

That's your error, right there. Something is getting truncated somewhere. You are trying to push a string value into somthing that isn't big enough for it.

-Jamie

|||

You are right, Jamie. I changed the size of the default VARCHAR and it is successful. But there are no inserting data in the table:

SSIS package "PivSupportmonthly.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.

Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (1657)" wrote 0 rows.

SSIS package "PivSupportmonthly.dtsx" finished: Success.

Thanks once again.

|||I think the issue might be with your query - although it may execute fine in Query Analyser - in SSIS source query - first statement should be SELECT (even if you have Stored Proc) - and in your case its UPDATE & then SET & finally SELECT|||So try a simple SELECT statement in the query to see if data passes through|||

Thanks to all your suggestions, I could run successfully my package. I changed default type VARCHAR to DATETIME and used SET NOCOUNT ON. Thanks once again.

Now, I have to make the package would run monthly. Where can I find information about how to schedule?

Thank you very much.

|||

Corres wrote:

Now, I have to make the package would run monthly. Where can I find information about how to schedule?

Use SQL Server's built-in scheduler (aka SQL Server Agent)

-Jamie

|||Thanks Jamie.