Problems inserting decimal numbers

Hi,
I'm having trouble inserting a decimal numbre in SQL
Server. I have an app built on .Net.
I'm passsing the number "98.5" to a store procedure and
SQL Server is saving the number but like this 985.00.
What seems to be the problem?
The regional settings of the computer at first where:
decimal separator ","
List separator "."
group separator "."
I have changed them to
decimal separator "."
List separator ","
group separator ","
But is looks like its not working....
Please help....Manrique,
we know that SQL Server does not do that, so the answer is to find where the
values gets changed: is it changed by the application? Is it changed within
the stored procedure? I think that's what you need look at. Have the
stored proc return the value of the variable at various stages of the stored
proc, I believe you would see a "whoops" somewhere.
Quentin
"Manrique" <mulloa@.grupo-polymer.com> wrote in message
news:02ee01c38c31$d5624820$a301280a@.phx.gbl...
> Hi,
> I'm having trouble inserting a decimal numbre in SQL
> Server. I have an app built on .Net.
> I'm passsing the number "98.5" to a store procedure and
> SQL Server is saving the number but like this 985.00.
> What seems to be the problem?
> The regional settings of the computer at first where:
> decimal separator ","
> List separator "."
> group separator "."
> I have changed them to
> decimal separator "."
> List separator ","
> group separator ","
> But is looks like its not working....
> Please help....

Problems adding % sign to a number

Hi,
I have a stored procedure which outputs a number as decimal(5,1). I
would like to add a % sign to the end of this number, however when I do
this using CAST(x as VARCHAR)+'%' I get the following error:
Microsoft OLE DB Provider for SQL Server error '80040e07'
Error converting data type varchar to numeric.
The following code works:
SELECT
CASE dbo.ListProfiles.Value
WHEN 0 THEN NULL
ELSE
CAST(((Val1 - Val2)/Val1)*100 AS DECIMAL(5,1))
END AS [Variance],
but when i change it to add the percentage sign (as follows) i get the
above error:
CASE dbo.ListProfiles.Value
WHEN 0 THEN NULL
ELSE
(CAST(
CAST(((Val1-Val2)/Val1)*100 AS DECIMAL(5,1))
AS VARCHAR) + '%')
END AS [Variance],
Can anyone shed any light onto this annoying problem. I have a feeling
that it may just be syntax related, but i've spent ages trying other
things without any success.
Many thanks,
ChrisChris
DECLARE @.dec AS DECIMAL(5,1)
SET @.dec=18.1
SELECT CAST(@.dec AS VARCHAR(10))+'%'
--OR
SELECT CAST(CAST((100-20)/2*100 AS DECIMAL(5,1))AS VARCHAR(10))+'%'
"Chris Dunigan" <chris.dunigan@.agwsha.nhs.uk> wrote in message
news:1113220485.699029.101780@.l41g2000cwc.googlegroups.com...
> Hi,
> I have a stored procedure which outputs a number as decimal(5,1). I
> would like to add a % sign to the end of this number, however when I do
> this using CAST(x as VARCHAR)+'%' I get the following error:
> Microsoft OLE DB Provider for SQL Server error '80040e07'
> Error converting data type varchar to numeric.
> The following code works:
> SELECT
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> CAST(((Val1 - Val2)/Val1)*100 AS DECIMAL(5,1))
> END AS [Variance],
> but when i change it to add the percentage sign (as follows) i get the
> above error:
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> (CAST(
> CAST(((Val1-Val2)/Val1)*100 AS DECIMAL(5,1))
> AS VARCHAR) + '%')
> END AS [Variance],
> Can anyone shed any light onto this annoying problem. I have a feeling
> that it may just be syntax related, but i've spent ages trying other
> things without any success.
> Many thanks,
> Chris
>|||It's usually best to perform data formatting in application code rather than
in Transact-SQL. That approach is much more robust and scalable.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Dunigan" <chris.dunigan@.agwsha.nhs.uk> wrote in message
news:1113220485.699029.101780@.l41g2000cwc.googlegroups.com...
> Hi,
> I have a stored procedure which outputs a number as decimal(5,1). I
> would like to add a % sign to the end of this number, however when I do
> this using CAST(x as VARCHAR)+'%' I get the following error:
> Microsoft OLE DB Provider for SQL Server error '80040e07'
> Error converting data type varchar to numeric.
> The following code works:
> SELECT
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> CAST(((Val1 - Val2)/Val1)*100 AS DECIMAL(5,1))
> END AS [Variance],
> but when i change it to add the percentage sign (as follows) i get the
> above error:
> CASE dbo.ListProfiles.Value
> WHEN 0 THEN NULL
> ELSE
> (CAST(
> CAST(((Val1-Val2)/Val1)*100 AS DECIMAL(5,1))
> AS VARCHAR) + '%')
> END AS [Variance],
> Can anyone shed any light onto this annoying problem. I have a feeling
> that it may just be syntax related, but i've spent ages trying other
> things without any success.
> Many thanks,
> Chris
>|||Thanks guys,
nested casts worked a treat
Chris
*** Sent via Developersdex http://www.examnotes.net ***

Problem with writting a Report (SQL slow running)

Hi

I am trying to write a report that calculates the average number of sales
over 7, 14, 31 and 365 days for each hourly period of the day. the problem
is it takes over 4 minutes to run.

e.g.
Average Xactions per Hour
7 Days 14 Days 31 Days 365 Days
00:00 - 01:00 1,141.6 579.2 261.6 28.8
01:00 - 02:00 1,298.0 649.6 293.4 30.0

The report was use to be purely ASP running SQL Statements.
I then changed it to ASP Running a SP 24 times - this reduced running time
by about 1 minute.
I then changed it so that the stored proc looped internally 24 times and
returns the data.

I have ran the Index Tuning Wizard on the SQL and Implemented the indexes
suggested - this actually increase execution time by 20 seconds.

Below is the stored procedure I am currently using that loops internally 24
times and returns the data.

Can anyone suggest a better way / any improvements I could make ?

Many Thanks

Steve

-----------------------
--------

CREATE procedure ams_RPT_Gen_Stats
@.strResult varchar(8) = 'Failure' output,
@.strErrorDesc varchar(512) = 'SP Not Executed' output,
@.strTest varchar(1),
@.strCurrency varchar(3),
@.strVFEID varchar(16)
as
declare @.strStep varchar(32)

set @.strStep = 'Start of Stored Proc'

/* start insert sp code here */

create table ##Averages (
TheHour varchar(2),
Day7Avge float ,
Day14Avge float ,
Day31Avge float ,
Day365Avge float
)

declare @.numHour varchar(2)
declare @.strSQL varchar(2000)
declare @.Wholesalers varchar(64)

declare MyHours cursor FORWARD_ONLY READ_ONLY for
select convert(char(2), timestamp,14) as TheHour
from xactions
group by convert(char(2), timestamp,14)
order by convert(char(2), timestamp,14)

if @.strTest = 'Y'
select @.Wholesalers = VALUE FROM BUSINESSRULES WHERE NAME =
'TEST_Wholesalers'

open MyHours

fetch next from MyHours into @.numHour

while @.@.fetch_status = 0

begin

set @.strSQL = 'insert into ##Averages (TheHour, Day7Avge) ( select ''' +
@.numHour + ''', ' +
'count(*) / 7.00 ' +
'FROM ' +
'XACTIONS INNER JOIN ' +
'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 8) and ' +
'xactions.xactiontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @.numHour + ''' '

if @.strTest = 'Y'
set @.strSQL = @.strSQL + ' and retailer.BillOrgID not in (' +
@.Wholesalers + ') '

if @.strCurrency <> '*'
set @.strSQL = @.strSQL + ' and xactions.XACTIONCURRENCY = ''' +
@.strCurrency + ''' '

if @.strVFEID <> '*'
set @.strSQL = @.strSQL + ' and xactions.VFEID = ''' + @.strVFEID + ''''
set @.strSQL = @.strSQL + ')'

exec ( @.strSQL )

set @.strSQL = 'update ##Averages set Day14Avge = ( select ' +
'count(*) / 14.00 ' +
'FROM ' +
'XACTIONS INNER JOIN ' +
'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 15) and ' +
'xactions.xactiontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @.numHour + ''' '

if @.strTest = 'Y'
set @.strSQL = @.strSQL + ' and retailer.BillOrgID not in (' +
@.Wholesalers + ') '

if @.strCurrency <> '*'
set @.strSQL = @.strSQL + ' and xactions.XACTIONCURRENCY = ''' +
@.strCurrency + ''' '

if @.strVFEID <> '*'
set @.strSQL = @.strSQL + ' and xactions.VFEID = ''' + @.strVFEID + ''' '

set @.strSQL = @.strSQL + ') where TheHour = ''' + @.numHour + ''' '

exec ( @.strSQL )

set @.strSQL = 'update ##Averages set Day31Avge = ( select ' +
'count(*) / 31.00 ' +
'FROM ' +
'XACTIONS INNER JOIN ' +
'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 32) and ' +
'xactions.xactiontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @.numHour + ''' '

if @.strTest = 'Y'
set @.strSQL = @.strSQL + ' and retailer.BillOrgID not in (' +
@.Wholesalers + ') '

if @.strCurrency <> '*'
set @.strSQL = @.strSQL + ' and xactions.XACTIONCURRENCY = ''' +
@.strCurrency + ''' '

if @.strVFEID <> '*'
set @.strSQL = @.strSQL + ' and xactions.VFEID = ''' + @.strVFEID + ''' '

set @.strSQL = @.strSQL + ' ) where TheHour = ''' + @.numHour + ''' '

exec ( @.strSQL )

set @.strSQL = 'update ##Averages set Day365Avge = ( select ' +
'count(*) / 365.00 ' +
'FROM ' +
'XACTIONS INNER JOIN ' +
'RETAILER ON XACTIONS.RETAILERID = RETAILER.RETAILERID ' +
'WHERE ' +
'(DATEDIFF(DAY , xactions.timestamp , GETDATE() ) < 366) and ' +
'xactions.xactiontotal <> 0 and ' +
' convert(char(2) , timestamp, 14) = ''' + @.numHour + ''' '

if @.strTest = 'Y'
set @.strSQL = @.strSQL + ' and retailer.BillOrgID not in (' +
@.Wholesalers + ') '

if @.strCurrency <> '*'
set @.strSQL = @.strSQL + ' and xactions.XACTIONCURRENCY = ''' +
@.strCurrency + ''' '

if @.strVFEID <> '*'
set @.strSQL = @.strSQL + ' and xactions.VFEID = ''' + @.strVFEID + ''' '

set @.strSQL = @.strSQL + ' ) where TheHour = ''' + @.numHour + ''' '

exec ( @.strSQL )

fetch next from MyHours into @.numHour

end -- while fetch

close MyHours
deallocate MyHours

select * from ##Averages order by TheHour

drop table ##Averages

/* end insert sp code here */

if (@.@.error <> 0)
begin
set @.strResult = 'Failure'
set @.strErrorDesc = 'Fail @. Step :' + @.strStep + ' Error : ' +
CONVERT(VARCHAR,@.@.Error)
return -1969
end
else
begin
set @.strResult = 'Success'
set @.strErrorDesc = ''
end

return 0

GOMany Thanks

I'll have a look at making those changes.

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93E5F2C02272EYazorman@.127.0.0.1...
> Steve Thorpe (stephenthorpe@.nospam.hotmail.com) writes:
> > I am trying to write a report that calculates the average number of
> > sales over 7, 14, 31 and 365 days for each hourly period of the day.
> > the problem is it takes over 4 minutes to run.
>
> Troubleshooting performance problems over newsgroups is often difficult
> because, without access to the database it's not possible to test
> various scenarios. And without complete knowledge about the tables
> involved it is even more difficult. Just seeing the procedure code,
> may sometimes be sufficient, but not always.
> Anyway, I have two suggestions for your procedure, although none of
> them are likely to improve performance radically.
> The first is that you use a global temptable. Change this to a local
> temp table. This avoids problems if two users run this procedure
> simultaneously.
> The other is that you rewrite the procedure to not use dynamic SQL.
> As far as I can see, the only reason that you use dynamic SQL, is
> that you intended to have a comma-separated list in @.Wholesalers.
> I would suggest that you handle the list like this:
> CREATE TABLE #wholesalers (id int NOT NULL)
> INSERT #wholesalers (id)
> SELECT number FROM iter_intlist_to_table(@.wholesalers)
> You find the code for iter_intlist_to_table at
> http://www.algonet.se/~sommar/array...ist-of-integers.
> Armed with this temp table, you can, as far as I can see, rip out the
> dynamic SQL and replace it with static. This may give some performance
> benefit, but only some single second.
> However, once you have rewritten the code into static SQL, it will be
> more pleasant to take a look at it.
> It also helps if you post CREATE TABLE and CREATE INDEX statements
> for the involved table. Some hints about data sizes is also good.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Problem with using Round() in mdx

I am using Round function in MDX. It is not giving expected result. It always gives nearest even number as result.

I am running following mdx:

with member

[Measures].[Calculated Score25]

as 'Round(2.5, 0)'

member [Measures].[Calculated Score35]

as 'Round(3.5, 0)'

select {

[Measures].[Calculated Score25],

[Measures].[Calculated Score35]}

on columns from Cube1

This mdx gives following result

Calculated Score25 Calculated Score35
2 4

When expected result is 3, 4

I agree it's strange, and when I first looked at your results I thought this was a bug. But then I looked at some descriptions of the round function and I suspect this behaviour is intentional:

http://www.techonthenet.com/access/functions/numeric/round.php

Anyway, you can get the value you want using the Excel round function, assuming you have the Excel function library available on your server:

with member

[Measures].[Calculated Score25]

as 'excel!round(2.5,0)'

member [Measures].[Calculated Score35]

as 'excel!Round(3.5,0)'

select {

[Measures].[Calculated Score25],

[Measures].[Calculated Score35]}

on columns from [Adventure Works]

HTH,

Chris

|||

This MS Support article explains various rounding algorithms - for example, VBA Round() performs "Banker's Rounding", whereas Excel Round() does "symmetric arithmetic rounding":

How To Implement Custom Rounding Procedures

...

SUMMARY

There are a number of different rounding algorithms available in Microsoft products. Rounding algorithms range from Arithmetic Rounding in Excel's Worksheet Round() function to Banker's Rounding in the CInt(), CLng(), and Round() functions in Visual Basic for Applications. This article describes what the various Visual Basic for Applications rounding functions do and provides samples of using the functions. In addition, the article includes sample functions that implement various rounding algorithms.

But if you need asymmetric arithmetic rounding, it looks like inverted VBA Int() would work:

with

member [Measures].[Score25] as 2.5

member [Measures].[Score35] as 3.5

member [Measures].[Calculated Score25]

as '-int(-[Measures].[Score25])'

member [Measures].[Calculated Score35]

as '-int(-[Measures].[Score35])'

select {

[Measures].[Calculated Score25],

[Measures].[Calculated Score35]}

on columns from [Adventure Works]

-

Calculated Score25 Calculated Score35
3 4

Problem with user prompt

I want to prompt the user for a specific ID number or use a * for all
records at the time of running the report
So in my criteria for the employee ID field I put "= @.empid"
In the report the input box is there. If I put in an ID number it
works great. However if I put in an asterisk "*" nothing shows up.
Any ideas?Your where clause should be written like this:
where
(isnull(@.EmpID, '*') = '*')
or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
this lets your users enter a value for EmpID (then the second part of the
where clause will deal with it)
or leave the parameter field empty - then all their records will be selected
You could also replace the '*' with a '' (empty string) - this way, if the
users leave the parameter field blank, it will behave like a '*'.
Andrei.
"Bruce Lawrence" <BL32375@.gmail.com> wrote in message
news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
>I want to prompt the user for a specific ID number or use a * for all
> records at the time of running the report
> So in my criteria for the employee ID field I put "= @.empid"
> In the report the input box is there. If I put in an ID number it
> works great. However if I put in an asterisk "*" nothing shows up.
> Any ideas?
>|||Ok you lost me a little bit.
Here is my current where clause.
WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX')) AND (pshstj.empid =@.ClockNo)
The "@.clockno" section is where I prompt them for a clock number.
If I put a * it doesn't work. If I put a good number in it works fine.
Where does your 'where' clause fit into this?
Andrei wrote:
> Your where clause should be written like this:
> where
> (isnull(@.EmpID, '*') = '*')
> or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
> this lets your users enter a value for EmpID (then the second part of the
> where clause will deal with it)
> or leave the parameter field empty - then all their records will be selected
> You could also replace the '*' with a '' (empty string) - this way, if the
> users leave the parameter field blank, it will behave like a '*'.
> Andrei.
>
> "Bruce Lawrence" <BL32375@.gmail.com> wrote in message
> news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
> >I want to prompt the user for a specific ID number or use a * for all
> > records at the time of running the report
> >
> > So in my criteria for the employee ID field I put "= @.empid"
> >
> > In the report the input box is there. If I put in an ID number it
> > works great. However if I put in an asterisk "*" nothing shows up.
> >
> > Any ideas?
> >|||I'd write an IIF statement (either in a stored procedure, or in the
Report Query):
IF @.clockno = '*'
BEGIN
select ...
from ...
where (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX'))
END
ELSE
BEGIN
select ..
from ..
where (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX')) AND (pshstj.empid =@.ClockNo)
END
Bruce Lawrence wrote:
> Ok you lost me a little bit.
> Here is my current where clause.
> WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
> (pshstj.trn = 'RO1' OR
> pshstj.trn = 'WO1' OR
> pshstj.trn = 'XXX')) AND (pshstj.empid => @.ClockNo)
> The "@.clockno" section is where I prompt them for a clock number.
> If I put a * it doesn't work. If I put a good number in it works fine.
> Where does your 'where' clause fit into this?
>
> Andrei wrote:
> > Your where clause should be written like this:
> >
> > where
> > (isnull(@.EmpID, '*') = '*')
> > or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
> >
> > this lets your users enter a value for EmpID (then the second part of the
> > where clause will deal with it)
> > or leave the parameter field empty - then all their records will be selected
> >
> > You could also replace the '*' with a '' (empty string) - this way, if the
> > users leave the parameter field blank, it will behave like a '*'.
> >
> > Andrei.
> >
> >
> > "Bruce Lawrence" <BL32375@.gmail.com> wrote in message
> > news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
> > >I want to prompt the user for a specific ID number or use a * for all
> > > records at the time of running the report
> > >
> > > So in my criteria for the employee ID field I put "= @.empid"
> > >
> > > In the report the input box is there. If I put in an ID number it
> > > works great. However if I put in an asterisk "*" nothing shows up.
> > >
> > > Any ideas?
> > >|||WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate)
AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX'))
AND
(
(isnull(@.ClockNo, '*') = '*')
or
( (isnull(@.ClockNo, '*') <> '*') and (pshstj.empid =lockNo) )
)
"Bruce Lawrence" <BL32375@.gmail.com> wrote in message
news:1164828482.158109.84050@.j44g2000cwa.googlegroups.com...
> Ok you lost me a little bit.
> Here is my current where clause.
> WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
> (pshstj.trn = 'RO1' OR
> pshstj.trn = 'WO1' OR
> pshstj.trn = 'XXX')) AND (pshstj.empid => @.ClockNo)
> The "@.clockno" section is where I prompt them for a clock number.
> If I put a * it doesn't work. If I put a good number in it works fine.
> Where does your 'where' clause fit into this?
>
> Andrei wrote:
>> Your where clause should be written like this:
>> where
>> (isnull(@.EmpID, '*') = '*')
>> or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
>> this lets your users enter a value for EmpID (then the second part of the
>> where clause will deal with it)
>> or leave the parameter field empty - then all their records will be
>> selected
>> You could also replace the '*' with a '' (empty string) - this way, if
>> the
>> users leave the parameter field blank, it will behave like a '*'.
>> Andrei.
>>
>> "Bruce Lawrence" <BL32375@.gmail.com> wrote in message
>> news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
>> >I want to prompt the user for a specific ID number or use a * for all
>> > records at the time of running the report
>> >
>> > So in my criteria for the employee ID field I put "= @.empid"
>> >
>> > In the report the input box is there. If I put in an ID number it
>> > works great. However if I put in an asterisk "*" nothing shows up.
>> >
>> > Any ideas?
>> >
>|||Andrei,
I'm not sure how... but it works.
I love you

Problem with updating a mobile sql database

This is strange. Cause earlier I could update the mobile sql server with no problem, but now I am getting this parsing error

it says Token line number = 1, ....

The windows mobile emulator works, but not in the actual pda.

The sql query is following
sql2 = "update flexi_pump set difference = " + divergence + " where tag = '" + tag + "'";

selecting data from the database works fine, no problem with it. only writing to it is the problem I would recommend using command parameters to avoid occasional string concatenation mishaps and possible SQL injection attacks on your code. If the tag variable is a string, it may contain a quote and it will break the syntax.