Monitoring a remote server?

Okay, my company is being a tight wad and doesn't want to spend the money for a good SQL monitoring tool. As a result, I am destined to custom write what I need. Most is done, but here is my current dilemma...

I have 2 servers, the monitoring server and the activity server. My monitoring server is the central machine that handles all my monitoring tasks and error notifications. I need a way to monitor if another SQL Server "is alive" from the central monitoring server. If the remote server is dead, the monitoring server will post an error that will be handled per the existing error routines.

How can I monitor the "alive" status of one SQL Server from another? One though I have is to write a simple DTS job (SELECT @.@.SERVERNAME) that runs from the monitoring server connecting to the remote server. I would then schedule that DTS job on the monitoring server. Then I would add a step the scheduled job to handle the error processing. Is there a better way to do this without pulling in DTS?It could be just a job rather than a DTS package.

Or "AT" from a command prompt running an ISQL script.|||On your local machine register both the servers in EM and regularly refer to SQL error log for any information.

Also you can setup a job to check whether the Server is running by using OSQL -L option which returns the available on the network.|||bglass, In our shop we have, using your terms, about 23 activity servers providing various mission critical and non-critical data. We need to know a bit more about our servers, are there any failed jobs, is drive space getting low, any long running jobs, current user load, etc. Each activity server is required to gather and hold all this info on a schedule that best fits that servers role. At regular intervals our monitoring server talks to each activity server and copies the above info for historical analysis and purges the copied data. Our monitoring server makes all this data available to a web server and by next month will have the ability to send out warnings and alerts to the appropriate DBA.

Does this help or do you have more questions?|||I already have the activity server (I have numerous of them, but only used two for this representation) gathering lots of info, including internal errors, and transmitting them to the monitoring server. The monitoring server then sends out the proper notifications (since we use Lotus Notes, sending email is not that easy, so I have it consolidated).

The problem is proactive notification if a SQL Server is not responding. We are a 24/7 flight operations center. Right now we get notified after hours by the end users if the system has gone down. Not a good solution.

The only other alternative I have come up with so far is to use linked servers. That way I can have a job on the monitoring server that runs a simple query on the linked server. If that step fails, I have an issue. But I HATE LINKED SERVERS.

What about some sort of ActiveX/VBScript step in a job that could query an external server?|||Not sure what the problem is with linked servers...

How about using xp_cmdshell and OSQL? Each job step could call a differenet server and any unexpected results would result in a page.|||Hi,
try perhaps so:
1) develop a VB program, that runs on that server and pools for instance every 1 minute to check if SERVICE MSSQLSERVER is running.
If no - send a message to:
a) Lotus Notes
b) SMS to cell
c) a file.
d) sends a message into the DB (special notification table)

If c) then develop another VB PG that pools and looks into that PG for checking for the messages, which are writing into it. You can do then waht you want.

if d) then you can make an INSERT trigger, which do some thing, which are needed to you

Is it enough for you?

Zbig|||Please check out SQLCentric at http://www.pearlknows.com. SQLCentric is a fully functional web-based database network monitoring and alert system that is deployed on your company intranet.
We offer 5, 10 and 25 license starter kits. Please download a copy to evaluate for yourself.

Money, SQL Server and C#

Argggg...

Please help.

I need to store money in SQL Server.
I am using C# and a stored procedure to insert.

How do you accomplish this? Specifically what datatype should the money variable be in C# during the insert? It's initial value is a string as it is entered from a text field.

I have been trying for over an hour now to simply insert money into SQL Server using C# and have it stored as a money type!

Thanks

Try using decimal. hth

|||

I did get it working using decimal.. thanks!

Decimal rate = Convert.ToDecimal(txtRate.Text);

Stored procedure looks like this.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE sp_UpdateHorsepowerRate

@.HorsePower int,
@.Rate money,
@.UserId uniqueidentifier,
@.EmployerId int

AS

INSERT INTO EmployerHorsePower (HorsePower, Rate, UserId, EmployerId) VALUES (@.HorsePower, @.Rate, @.UserId, @.EmployerId)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Rob

|||

I would recommend that you not begin your stored procedures with "sp" (refer to:http://www.sql-server-performance.com/rd_optimizing_sp_recompiles.asp).

Also, when setting up the SqlParameter be sure that you consider the precision and scale. For themoney datatype, it would be precision=19, scale = 4.

Money type limitation

Does anyone know if Microsoft is planning to expand sql server Money
data type to larger what it is in sql server 2000; that is larger than
922,337,203,685,477.5807?
Thanks
HilalI haven't heard of such plans. You can always request at sqlwish@.microsoft.com. Or use NUMERIC
instead.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hilal Issa" <hilal@.edm.com.lb> wrote in message news:u9ak8h9tDHA.2340@.TK2MSFTNGP12.phx.gbl...
> Does anyone know if Microsoft is planning to expand sql server Money
> data type to larger what it is in sql server 2000; that is larger than
> 922,337,203,685,477.5807?
> Thanks
> Hilal
>|||I don't know about future versions but if you want values larger than that
today use DECIMAL/NUMERIC which handles up to 38 digits preceision.
--
David Portas
--
Please reply only to the newsgroup
--
"Hilal Issa" <hilal@.edm.com.lb> wrote in message
news:u9ak8h9tDHA.2340@.TK2MSFTNGP12.phx.gbl...
> Does anyone know if Microsoft is planning to expand sql server Money
> data type to larger what it is in sql server 2000; that is larger than
> 922,337,203,685,477.5807?
> Thanks
> Hilal
>|||Thanks for the replies.
I will send an e-mail to sqlwish@.microsoft.com as Tibor suggested.
In fact, money data type would enough except in some cases where some values
might go beyond its boundaries; ex.: I have a table with 3 fields Qty, Unit
Amount and SubTotal (=Qty * Unit Amount). If we set Qty = 9999999 (seven
9's) and Unit Price = 99999999 (eight 9's)and Now calculate SubTotal = Qty *
Unit Amount, an overflow error is generated.
Indeed, I was trying converting all my fields using money datatype to
Decimal (38, 5) that is allowing 5 decimal places. I did not finish testing
with it yet. But I am guessing from now that I will have this problem with
wider range. Float (up to 1.79E + 308) would have been great but its
floating issue is not good at all in my case.
Best Regards
Hilal
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:SPidnZLuAOq5aleiRVn-hg@.giganews.com...
> I don't know about future versions but if you want values larger than that
> today use DECIMAL/NUMERIC which handles up to 38 digits preceision.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "Hilal Issa" <hilal@.edm.com.lb> wrote in message
> news:u9ak8h9tDHA.2340@.TK2MSFTNGP12.phx.gbl...
> > Does anyone know if Microsoft is planning to expand sql server Money
> > data type to larger what it is in sql server 2000; that is larger than
> > 922,337,203,685,477.5807?
> >
> > Thanks
> >
> > Hilal
> >
> >
>|||A bigger range for a money datatype is rediculous. Even the biggest
budget of the biggest country will not exceed 900 trillion.
I would suggest you simply handle any overflows. Who is going to spend
900 trillion dollars in your shop by buying 10 million units that cost
100 million dollars each? That just doesn't make sense.
My 5 cents,
Gert-Jan
Hilal Issa wrote:
> Thanks for the replies.
> I will send an e-mail to sqlwish@.microsoft.com as Tibor suggested.
> In fact, money data type would enough except in some cases where some values
> might go beyond its boundaries; ex.: I have a table with 3 fields Qty, Unit
> Amount and SubTotal (=Qty * Unit Amount). If we set Qty = 9999999 (seven
> 9's) and Unit Price = 99999999 (eight 9's)and Now calculate SubTotal = Qty *
> Unit Amount, an overflow error is generated.
> Indeed, I was trying converting all my fields using money datatype to
> Decimal (38, 5) that is allowing 5 decimal places. I did not finish testing
> with it yet. But I am guessing from now that I will have this problem with
> wider range. Float (up to 1.79E + 308) would have been great but its
> floating issue is not good at all in my case.
> Best Regards
> Hilal
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:SPidnZLuAOq5aleiRVn-hg@.giganews.com...
> > I don't know about future versions but if you want values larger than that
> > today use DECIMAL/NUMERIC which handles up to 38 digits preceision.
> >
> > --
> > David Portas
> > --
> > Please reply only to the newsgroup
> > --
> >
> > "Hilal Issa" <hilal@.edm.com.lb> wrote in message
> > news:u9ak8h9tDHA.2340@.TK2MSFTNGP12.phx.gbl...
> > > Does anyone know if Microsoft is planning to expand sql server Money
> > > data type to larger what it is in sql server 2000; that is larger than
> > > 922,337,203,685,477.5807?
> > >
> > > Thanks
> > >
> > > Hilal
> > >
> > >
> >
> >|||On Tue, 02 Dec 2003 23:49:37 +0100, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>A bigger range for a money datatype is rediculous. Even the biggest
>budget of the biggest country will not exceed 900 trillion.
>I would suggest you simply handle any overflows. Who is going to spend
>900 trillion dollars in your shop by buying 10 million units that cost
>100 million dollars each? That just doesn't make sense.
Whilst in principle I agree with you, consider applications that must
handle foreign currencies. In an application I worked on recently for a
bank, I was somewhat surprised when a summary report returned figures in
the trillions. As it happened, it was due to my not converting foreign
currencies to local currency (AUD), but be aware that there are
situations where trillions of currency units are quite within realistic
bounds.
cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander

Money type confusion

I need a little advice for the Money datatype. When entering a value like $18.20 into a column of datatype Money, it stores and returns a value of 18.2 . It is set to the default paramaters. How can I change this so that it is correctly displayed as $18.20. My results are printed onscreen through VB, is that where I need to reformat?You sure it's money?

DECLARE @.x money

SELECT @.x = 18.20

SELECT @.x

EDIT: It's a presentation layer issue...

Then There Also

SELECT CONVERT(varchar(15),@.x,1)|||Positive it's Money|||Where's the sql being executed from? a stored procedure or called from a front end?

Did you try the code I posted in QA?|||It's a .asp page with VB. It's a dynamic SQL statment. And after running your code I got 18.2000. So it looks like it's good to 4 decimal places. This may be dumb to ask now, but should I kill that column and recreate it?|||You should be able to use the Convert function to format your output as a string, but formatting is best handled by your application interface.

blindman|||Do you know how to script the table so we can see the DDL?

Did you use the convert from the page?|||I'm a graphic designer doing the basics on MSSQL, I didn't even know you could script a table let alone tell you what a DDL is. But I'm a smart guy, I'll catch on quick.

And by converting do you mean something like CSTR to cast the returned value into a string or is it done within the SQL statment?

Originally posted by Brett Kaiser
Do you know how to script the table so we can see the DDL?

Did you use the convert from the page?|||SELECT CONVERT(varchar(15),@.x,1)

Just replace the @.x with the column name

Do you have SQL Server client tools installed on your desktop?

Do you know what Enterprise Manager is?|||Actually you should make the varchar(25) to handle all possible money values...

money and smallmoney
money

Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.

smallmoney

Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.|||[i]Actually you should make the varchar(25) to handle all possible money values...

quote:
------------------------

money and smallmoney
money

Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.

smallmoney

Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.

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

Brett ...
That sounds like an awful lot of Money. Hope the person having so much can lend some to me :D|||HAHA, after 5 minutes of staring at the wrong webpage I was working on, I got myself straightened out and found the right one. The Convert expression works like magic.

And yeah, i'm working in Enterprise Manager. I guess I'm a little above basic.|||I thought 15 was enough...but you still have to take in the right side of the decimal, the decimal itself, commas, and a sign...25

922 Trillion Dollars...|||Thanks for you help, I should be able to manage from here|||If we were dealing with 922 Trillion Dollars do you think I'd be posting to a free forum for help? I think they'd have someone a little more experienced on this.|||Actually, it's more a matter of a development methodolgy...always code for what can happen...always...

That way there will never be any holes...|||Originally posted by stmaher
If we were dealing with 922 Trillion Dollars do you think I'd be posting to a free forum for help? I think they'd have someone a little more experienced on this.

There go my hopes down the drain :D.

BTW Brett is right. The code should take care of all possible conditions.

Brett ... though I think varchar(15) will take care of Bill Gates wealth for sure|||Free forum !? What is your mailing address ? Or you can call the toll free number and submit your credit card information.|||What? Have NONE of you guys been receiving your checks?

Must be a problem in the payroll database...

blindman|||Just send me your SSN's and mailing addresses and I will get you on the payroll. If you receive any unexpected mail, like credit cards ..., ignore it and just forward them to me. :-)|||> That sounds like an awful lot of Money

hang on a sec...you must be assuming USD. I wonder whether the amount would still look so large if it was to refer to Japanese Yen. Not to mention some inflated currency from a developing country.|||You'd be surprized.
We have a thing in the IT dept. we call the "2 comma rule".

In other words, if the number (currency) doesn't have at least 2 commas, it isn't worth worrying about.

Swear to God.

Money to Varchar

From query analyzer how can I change the field datatype from money to varchar?
Alter table tablenaame alter column columnname varchar(30)--Is not working.Can you describe "not working" in a bit more detail? Does the command appear to run, but the column datatype doesn't change? Does it give an error message? Does your server crash and spit blue smoke?

-PatP|||You cannot change the datatype to money from (n)varchar, because this conversion cannot be done implicitly. You'll have to create a new table with desired datatype (money), use CONVERT/CAST function to insert the data into it, then drop the original table, create a new table with the same name, and insert the data back in.

Money to Varchar

How can I alter a field datatype from money to varchar?To change the column type you will have to create a new table and copy the data over using the convert function, example below.

--create table x
--(a int null,
--b money null)

CREATE TABLE dbo.Tmp_x
(
a int NULL,
b nvarchar(50) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.x)
EXEC('INSERT INTO dbo.Tmp_x (a, b)
SELECT a, CONVERT(nvarchar(50), b) FROM dbo.x')
GO
DROP TABLE dbo.x
GO
EXECUTE sp_rename N'dbo.Tmp_x', N'x', 'OBJECT'
GO|||You can also add a temporary column to your table of the varchar type and fill it with the data from the money column to see if all values can be converted. If so, empty the money column, change the datatype of the money column to varchar and fill it with the data of the temporary column. Finally delete the temporary column.

To add a column:

alter table owner.your_table
add temp_column varchar(10)

To modify a column datatype:

alter table owner.your_table
alter column your_column varchar(10)

To drop a column:

alter table owner.your_table
drop column temp_column

money in stored procedure

Hi there,

I have a table products with product_price money(8) field. I have a stored procedure,

CREATE procedure p_dat_update_product(
@.product_id int,
@.product_price decimal
)
as
set nocount on

declare
@.msg varchar(255) -- error message holder

-- remove any leading/trailing spaces from parameters
select @.product_id = ltrim(rtrim(@.product_id))
select @.product_price = ltrim(rtrim(@.product_price))

-- turn [nullable] empty string parameters into NULLs
if (@.product_id = N'') select @.product_id = null
if (@.product_price = N'') select @.product_price = null

-- start the validation
if @.product_id is null
begin
select @.msg = 'The value for variable p_dat_update_product.@.product_id cannot be null!'
goto ErrHandler
end

-- execute the query
if exists (select 'x' from product where product_id = @.product_id)
begin
update dbo.product
set
product_price = @.product_price
where product_id = @.product_id
end

if (@.@.ERROR <> 0) goto Errhandler

return
ErrHandler:
raiserror 30001 @.msg
return
GO

And a class function

public void Edit_Product(string product_id,string product_price)
{

decimal dec_product_price = decimal.Parse(product_price);

SqlDataServ oMisDb = new SqlDataServ("LocalSuppliers");
oMisDb.AddParameter("@.product_id", product_id);
oMisDb.AddParameter("@.product_price", dec_product_price.ToString());
oMisDb.ExecuteCmd("p_dat_update_product");
}

But i keep getting this error,

Error converting data type nvarchar to numeric

oMisDb.ExecuteCmd("p_dat_update_product");

I do not know what to do and solve this. Is there something that i have done wrong.

thanks in advance.

PS: still learning aspx and ms sql

Hi

the stored proc accepts int and decimal

priteshchandra:

CREATE procedure p_dat_update_product(
@.product_id int,
@.product_price decimal
)

while you are passing string

priteshchandra:

public void Edit_Product(string product_id,string product_price)

so you need to cast them to appropriate datatypes

priteshchandra:

oMisDb.AddParameter("@.product_id", product_id);
oMisDb.AddParameter("@.product_price", dec_product_price.ToString());

should be

oMisDb.AddParameter("@.product_id", convert.toint32(product_id));
oMisDb.AddParameter("@.product_price", convert.toDecimal(dec_product_price));

hope that helps.

thanks,

satish.

|||

I tired ur solution but got this error.

Compiler Error Message:CS0103: The name 'convert' does not exist in the current context

oMisDb.AddParameter("@.product_price", convert.toDecimal(dec_product_price));

|||

aargh my friend

Convert.ToDecimal() , do you've .net documentation/MSDN or not?

its straight forward naming convention of C#.

thanks,

satish.

|||

I think he meant Decimal.Parse() and Int32.Parse()?

|||

Motley:

I think he meant Decimal.Parse() and Int32.Parse()?

yes infact TryParse would be efficient.

|||

Just replace this line:

oMisDb.AddParameter("@.product_price", dec_product_price.ToString());

with this:

oMisDb.AddParameter("@.product_price", dec_product_price);

because you already converted it to decimal here:

decimal dec_product_price = decimal.Parse(product_price);

|||

kipo:

Just replace this line:

oMisDb.AddParameter("@.product_price", dec_product_price.ToString());

with this:

oMisDb.AddParameter("@.product_price", dec_product_price);

because you already converted it to decimal here:

decimal dec_product_price = decimal.Parse(product_price);

I tired this before and i kept getting this error,

CS1502:The best overloaded method match for'MISUtilities.DataServices.SqlDataServ.AddParameter(string, string)'has some invalid arguments

Source Error:

Line 359: oMisDb.AddParameter("@.product_price", dec_product_price);

|||

satish_nagdev:

aargh my friend

Convert.ToDecimal() , do you've .net documentation/MSDN or not?

its straight forward naming convention of C#.

thanks,

satish.

Sory, i do not have MSDN. am using Express Edition for now since me learning .NET

|||

I believe you are looking for this syntax:

oMdb.Parameters.Add("@.param",SqlDbType.Int).Value={something}

or

oMdb.Parameters.AddWithValue("@.param", {something})

of course, replace {something} with a constant, variable, etc.

|||

After working to finding a solution, i kept failing. eventually i came up with this.

i changed the field from money to nvarchar and everything worked fine on wards.

question:

replacing money with nvarchar, will it affect my data in any way already stored?

|||

Hi Pritesh,

you said money datatype and in actual post i could see you are accepting decimal datatype. i think that is the actual problem.

regarding replacing money with nvarchar is not a good idea. there might be cases when you need to work on numeric (money) so you'll do conversion back and forth to money and varchar.

so in your table the column is of type money right? if yes modify your sp to accept money instead of decimal and in c# code use overloaded parameter constructor and pass datatype like sqlint etc

thanks,

satish.

|||

well, i tired placing money in the stored procedure but i kept getting error that money is not valid in sp.

Money in SQL and ASP.NET

Hi,
I'm having some trouble with my asp.net page and my sql database. What I'm trying to do is allow the user to upload an number to the database, the number is a money amount like 2.00 (￡2.00) or 20.00(￡20.00). I've tried using money and smallmoney datatypes but the numbers usually end up looking like this in the database...
I enter 2.00 and in the database it looks like 2.00000, and even if I enter the information directly into the database I get the same results. I'm not going to be using big numbers with lots of decimal places like this 1000,000,0000. Can anyone help me? All I want is to know what to set the value to on my aspx page and what setting to set the field to in my database, I'd just like two pound to appear as 2.00. any help would be great.

I'm using Microsoft Visual Web Developer 2005 Express Edition and Microsoft SQL Server 2005 if that helps.

Thanks.

Hi,

The money or smallmoney datatypes are the right ones to use in your case. By design, they have four decimal at the end and you can find relevent infromation related to these two from Books Online. As to your question, you use either of these types in your database to hold your data and when it is time to show your data on your asp.net page, you can format them into what you need. For example, {0,c2} will give you two decimals as you want. In GridView:

<asp:BoundField HeaderText="Price/Unit" DataField="UnitPrice" DataFormatString="{0:c2}"HtmlEncode="false"> </asp:BoundField>

Or

<asp:TemplateFieldHeaderText="UnitPrice"><ItemTemplate><asp:LabelID="Label2"runat="server"Text='<%# Eval("UnitPrice","{0:c2}") %>'></asp:Label></ItemTemplate></asp:TemplateField>|||

Hi,

You don't say what type of controls you're trying to display this in. However, one simple way (that I think you can apply to any text box or label control) is this:

TextBox1.Text = (512.23).ToString("c")

If you need to set the page to a different currency than your own, one way is to set the UICulture of the page directive:

<%@. Page Language="VB" AutoEventWireup="false" UICulture="en-GB" CodeFile="Default4.aspx.vb" Inherits="Default4" %>

You can also set this in the web.config file (under globalization)

How the numbers are stored in SQL aren't relevant to how they'll be displayed - as you've discovered!

Hope this helps.

Paul

MONEY FORMAT presentation

Hi all,
I am tryng to locate the SQLServer vesion of Informix's DBMONEY env variable
which is used in the Informix env by tools like ISQL and I4GL to format
values stored as type MONEY into a nice presentation pattern. Is there such
a
property for SQLServer?
Why am I looking for this? Basically the application that we are building
handles all this preso stuff at the front-end, problem is we are trying to
rapid prototype a number of MIS reports (in SQLAnalyser) and the cllient is
used to seeing the numbers in a pre-formatted pattern as they currently use
ACCESS and the FORMAT function. Myself and my buddy are going slowly
blind/mad having to cast/convert values stored as money into strings to get
$'s and commas!
Slainte,
TaggartTaagart
DECLARE @.m AS DECIMAL (18,3)
SET @.m=100554545.36
SELECT convert(varchar,cast(@.m as money),1)
"Taggart" <Taggart@.discussions.microsoft.com> wrote in message
news:11E0F555-2BEF-4424-807C-3D6CEF31F6B7@.microsoft.com...
> Hi all,
> I am tryng to locate the SQLServer vesion of Informix's DBMONEY env
> variable
> which is used in the Informix env by tools like ISQL and I4GL to format
> values stored as type MONEY into a nice presentation pattern. Is there
> such a
> property for SQLServer?
> Why am I looking for this? Basically the application that we are building
> handles all this preso stuff at the front-end, problem is we are trying to
> rapid prototype a number of MIS reports (in SQLAnalyser) and the cllient
> is
> used to seeing the numbers in a pre-formatted pattern as they currently
> use
> ACCESS and the FORMAT function. Myself and my buddy are going slowly
> blind/mad having to cast/convert values stored as money into strings to
> get
> $'s and commas!
> Slainte,
> Taggart
>
>

MONEY FORMAT presentation

Hi all,
I am tryng to locate the SQLServer vesion of Informix's DBMONEY env variable
which is used in the Informix env by tools like ISQL and I4GL to format
values stored as type MONEY into a nice presentation pattern. Is there such a
property for SQLServer?
Why am I looking for this? Basically the application that we are building
handles all this preso stuff at the front-end, problem is we are trying to
rapid prototype a number of MIS reports (in SQLAnalyser) and the cllient is
used to seeing the numbers in a pre-formatted pattern as they currently use
ACCESS and the FORMAT function. Myself and my buddy are going slowly
blind/mad having to cast/convert values stored as money into strings to get
$'s and commas!
Slainte,
TaggartTaagart
DECLARE @.m AS DECIMAL (18,3)
SET @.m=100554545.36
SELECT convert(varchar,cast(@.m as money),1)
"Taggart" <Taggart@.discussions.microsoft.com> wrote in message
news:11E0F555-2BEF-4424-807C-3D6CEF31F6B7@.microsoft.com...
> Hi all,
> I am tryng to locate the SQLServer vesion of Informix's DBMONEY env
> variable
> which is used in the Informix env by tools like ISQL and I4GL to format
> values stored as type MONEY into a nice presentation pattern. Is there
> such a
> property for SQLServer?
> Why am I looking for this? Basically the application that we are building
> handles all this preso stuff at the front-end, problem is we are trying to
> rapid prototype a number of MIS reports (in SQLAnalyser) and the cllient
> is
> used to seeing the numbers in a pre-formatted pattern as they currently
> use
> ACCESS and the FORMAT function. Myself and my buddy are going slowly
> blind/mad having to cast/convert values stored as money into strings to
> get
> $'s and commas!
> Slainte,
> Taggart
>
>|||Hi Uri,
That's much better, but still missing the $'s. Apart from hardwiring the $
sign and then concatanating this to the result, any ideas?
What I don't get is the need to cast a column that is already set as money
(pity I didn't use decimal!?) to money, maybe we are missing something
fundamental but isn't this just consuming CPU resource for no apparent reason?
As ex-informix guys we are somewhat puzzled by this and, on a similar point,
all the manipulation one has to undertake for datetime columns when you only
want to use the date portion - presumably something to do with no date
datatype in SQLServer, which is something we find very strange both in the
additional manipulation and also in terms of datastorage?
Slainte,
Taggart|||On Sun, 4 Jun 2006 19:53:02 -0700, Taggart wrote:
>Hi Uri,
>That's much better, but still missing the $'s. Apart from hardwiring the $
>sign and then concatanating this to the result, any ideas?
Hi Taggart,
SQL Server is not intended to be used for formatting - that task is
usually handled by the front-end. As a result, SQL Server doesn't have
as much formatting features as some other tools.
If you're sure that your application is only used in dollar-using
countries, hardcoding the $ sign is probably the best solution. If the
app might be used all over the world, you'd be better off letting the
front-end determine the correct currency symbol from the useer's locale
settings and append that symbol to the amount.
>What I don't get is the need to cast a column that is already set as money
>(pity I didn't use decimal!?) to money, maybe we are missing something
>fundamental but isn't this just consuming CPU resource for no apparent reason?
There's no need for the extra CAST - Uri used it becuase the variable he
used in his example was not money. If your column is monmey, you can
just use
SELECT convert(varchar, Column_Name, 1)
>As ex-informix guys we are somewhat puzzled by this and, on a similar point,
>all the manipulation one has to undertake for datetime columns when you only
>want to use the date portion - presumably something to do with no date
>datatype in SQLServer, which is something we find very strange both in the
>additional manipulation and also in terms of datastorage?
Not having seperate date and time datatypes is indeed a pity.
However, there's no need for much manipulation to remove the time
portion from a datetime column. If you need the result as datetime (only
withoout time portion - or rather, with the default midnight time
portion), use
SELECT DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
And if you need it in character format (for presentation purposes), use
CONVERT with an appropriate style parameter and define the length of the
result such that the time will be cut off, for instance
SELECT CONVERT(char(8), CURRENT_TIMESTAMP, 112)
--
Hugo Kornelis, SQL Server MVP|||Thanks Hugo your use of convert is much. much neater than my attempt!
"Hugo Kornelis" wrote:
> On Sun, 4 Jun 2006 19:53:02 -0700, Taggart wrote:
> >Hi Uri,
> >
> >That's much better, but still missing the $'s. Apart from hardwiring the $
> >sign and then concatanating this to the result, any ideas?
> Hi Taggart,
> SQL Server is not intended to be used for formatting - that task is
> usually handled by the front-end. As a result, SQL Server doesn't have
> as much formatting features as some other tools.
> If you're sure that your application is only used in dollar-using
> countries, hardcoding the $ sign is probably the best solution. If the
> app might be used all over the world, you'd be better off letting the
> front-end determine the correct currency symbol from the useer's locale
> settings and append that symbol to the amount.
> >What I don't get is the need to cast a column that is already set as money
> >(pity I didn't use decimal!?) to money, maybe we are missing something
> >fundamental but isn't this just consuming CPU resource for no apparent reason?
> There's no need for the extra CAST - Uri used it becuase the variable he
> used in his example was not money. If your column is monmey, you can
> just use
> SELECT convert(varchar, Column_Name, 1)
> >
> >As ex-informix guys we are somewhat puzzled by this and, on a similar point,
> >all the manipulation one has to undertake for datetime columns when you only
> >want to use the date portion - presumably something to do with no date
> >datatype in SQLServer, which is something we find very strange both in the
> >additional manipulation and also in terms of datastorage?
> Not having seperate date and time datatypes is indeed a pity.
> However, there's no need for much manipulation to remove the time
> portion from a datetime column. If you need the result as datetime (only
> withoout time portion - or rather, with the default midnight time
> portion), use
> SELECT DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
> And if you need it in character format (for presentation purposes), use
> CONVERT with an appropriate style parameter and define the length of the
> result such that the time will be cut off, for instance
> SELECT CONVERT(char(8), CURRENT_TIMESTAMP, 112)
> --
> Hugo Kornelis, SQL Server MVP
>

Money format error

I use asp.net 2.0 and sql server 2005 for a web site (and Microsoft enterprise library).When I run aplication at local there is no problem but at the server I take this error:

Disallowed implicit conversion from data type varchar to data type money, table 'dbname.dbo.shopProducts', column 'productPrice'. Use the CONVERT function to run this query.

productPrice coloumn format is money. It was working correctly my old server but now it crashed.How can I solve this problem? And why it isn't work same configuration?

My code:

decimal productPrice;

Database db =DatabaseFactory.CreateDatabase("connection");

string sqlCommand ="update shopProducts set .................,productPrice='" + productPrice.ToString().Replace(",",".") +"',................where productID=" + productID +" ";

db.ExecuteNonQuery(CommandType.Text, sqlCommand)

Decimal should be able to covert to SqlMoney implicitly, why you convert the price to string before update it to the table? However, it's better to useParameterized Queries as possible as you can.

money format ..need some help..please

Hi everybody.
Here's my Product table

ID int,
Title nvarchar(50),
Price money

How can I get value from price field like this
IF PRICE is 12,000.00 it will display 12,000
IF PRICE is 12,234.34 it will display 12,234.34

Thanks very much...I am a beginner. Sorry for foolish question

Formatting is generally applied at the page where the data is going to be displayed as opposed to altering the datasource itself.

If you were going to databind your sql data to something such as a GridView, then you can alter the data's format by specifying a DataFormatString.

for currency you can use: {0:c}
for numeric data with 2 digits after the decimal you can use: {0:n2}

For example:

 
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="Price" DataFormatString="{0:c}" HtmlEncode=False HeaderText="With Currency Symbol" /> <asp:BoundField DataField="Price" DataFormatString="{0:n2}" HtmlEncode=False HeaderText="Without Currency Symbol" /> </Columns></asp:GridView>
  

If you are actually looking to alter the data in sql server, please let us know.

|||

Thanksmbanavige very much for your answer. I use DataList to display my items and I want to display1 pricein my page. I try to use this code

<%If((Eval("Price") *100) mod 100 >0) { %>

<%#Eval("Price","{0:N2}")%>

<% } else {

%> <%#Eval("Price","{0:c}") %>

<% } %> . But it seem wrong. How can I do something like that ?

|||

I'm not sure i follow what you're trying to do with that code. If you want one price, then choose only one of the sample formats i provided

use this: <%#Eval("Price","{0:c}") %>

or this: <%#Eval("Price","{0:N2}")%>

but not both at the same time.

Unless you're dealing with unpredictalbe currencies, i'd probably just go with: <%#Eval("Price","{0:c}") %>

|||

mbanavige:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="Price" DataFormatString="{0:c}"
HtmlEncode=False HeaderText="With Currency Symbol" />
</Columns>
</asp:GridView>

Hi Mike,

What would cause a page with say ten colums, all formated in the same manner, to only have five colums display currency correctly and the remainder to just be numbers?

I have this weird scenario going on and it is not a syntax error, as I have checked in dozens of times.

|||

Did you set HtmlEncode to False for all the columns?

|||

Yes I did. I just did a character by character study of two similar pages. The first page is for one State the next page for a different State. Every single character is identical. Therefore I have no option to conclude that there must be some fundamental change in the SQL database tables themselves. each State has it's own table. I will go in and make sure that each field is set to exactly the same data type.

If there is any other possibility that you can think of, please let me know. I am certain that this is not a code issue, the code is working, the reason I am not getting the formatting to work is probably because what is expected in the DB table, is something else. I'll drop you a line after I have checked the tables.

|||

Oaky, I was correct, the problem was in the database. The fields in question on the working table were 'money' and the same fields in the non-working table were 'float'. The instant I changed over to 'money', the web pages formatted perfectly.

There is an interesting sideline to this. In the beginning all state tables were identical, and the database itself was hosted on remote servers in Dallas, Texas in the USA. The database is now hosted on remote servers in Sydney, Australia and when the databse was transferred it somehow mysteriously changed. The technique of restoring a backup on the new server, then attaching it, was how we did the transfer, but it was not 100% flawless. We could not then nor now figure out why some very minor changes occured, perhaps there may have been some slight version differences on server software.

At any rate it's all fixed now, so the moral to the tale is this, don't always assume your code is incorrect, sometimes, it might be other things, as in this case, an issue with the database.

Best wishes.

money format

Hi.
I have a money field and its value is greater than a thousand.For example its value = 32.885,60
I want to show the field's format like this (this number's format).
I mean the thousand separator should be .(dot) and the decimal separator should be ,(comma)
And I want two digit after the decimal separator.All these conditions matches with this number(32.885,60)
Could you help me?

hi muhsin,

write an embeddede function which returns your amount like this: amount.ToString("N")

|||

Dear,

Write the embedded fucntion (=format(field,'fomat')

HT

from

sufian

|||Can you write for me please?
I tried but it didn't work.
What should I write in the format style area?|||

Could you format the string in code and then do a string replace to replace the , with a . and the . with a ,?

|||

Hi Muhsin,

sorry this isn't an answer for you, but very curious as to the currency you are working in?

I am used to formatting it the other way around (32,885.60)

99

|||

this is your function:

shared function GetCurrencyFormat(byVal Amount As Double) As String

return Amount.ToString("N")

end function

to call the function go to your layout and write in the cell or textbox or whatever the following:

=Code.GetCurrencyFormat(Amount)

thats it

|||

SpaceCadet wrote:

this is your function:

shared function GetCurrencyFormat(byVal Amount As Double) As String

return Amount.ToString("N")

end function

to call the function go to your layout and write in the cell or textbox or whatever the following:

=Code.GetCurrencyFormat(Amount)

thats it

But this is not I want.
This is already a format in the properties of the textbox.
You don't need to write a function for this.
I want the thousand seperator to be dot and the decimal seperator to be comma.
Your solution's result is for example 15,250.30.
But I want 15.250,30.
Thanks anyway.
If you find another solution , please share it with me!|||You can accomplish this by adjusting the Language property for the textbox, or the entire report to a language that uses this format. The language settings can be specific to just number formats when just setting the NumeralLanguage property on the textbox. After setting these properties, just use "N" as the format code.

See the following link for more information about International Considerations for Reporting Services:
http://msdn2.microsoft.com/en-us/library/ms156493.aspx

Money format

hello everyone...,

i have problem in money format...

i have moneytable is containning:

userid money

A 20000,0000

B 40000,0000

userid type varchar(50)

money type money

i have store procedure like this:

ALTER PROCEDURE [dbo].[paid]
(
@.userid AS varchar(50),

@.cost AS money,
@.message as int="1" output
)
AS

begin transaction
declare @.money as money

select @.money = money
from moneytable
where userid=@.userid

if (@.money > @.cost)
begin
set @.money = @.money - @.cost
UPDATE moneytable
SET money = @.money
WHERE userid=@.userid
set @.message ='1'
end
else
begin
set @.message = '2'
end

COMMIT TRANSACTION

when i execute this procedure. i insert value to

userid A

cost 100,0000

it can not decrease, because cost 100,0000 is same with 1000000, why is like that?

i want cost 100,0000 is same with 100.

how can i do that?

thx...

use the dot. character in ploce of comma, "," is used for sepration not for decreasing the amount of number.

e.g:

100.0000=100

100,0000=1000000

thanks

|||

thx hkhaled...,

how can i make it todot. format?

because the system automatic to make all money incomma, format

in my city, i use money format like this example:

Rp 7.000,00 =7000

Rp 100,00 =100

how can i do it?

is there need to change type in database?

thx...

|||

this is usually inhereted from your system Control Panel if you want to have it changed go to your Computer Control Panel and then under theRegional and Language Options, Format tab,Customize this Format, click oncurrency tab and change theDecimal Symbol and Digits Grouping Symbol to the choice of your own.

thanks

|||

thx hkhaled..

it can not too..

my friend said to change money type in visual studio 2005 system. is it true?

how to change it?

thx..

|||

Maybe this example will help you:

<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />

protected void Button1_Click(object sender, EventArgs e)
{
decimal dec = decimal.Parse(TextBox1.Text);
System.Globalization.NumberFormatInfo inf = new System.Globalization.NumberFormatInfo();
inf.CurrencyDecimalSeparator = ".";
Response.Write(dec.ToString(inf));
}

|||

thx kipo..

but i am not use code behind.

i am only execute data in store procedure..

when i execute, it display page told me to insert value like that:

userid default

cost default

message default

so i insert the value like that:

userid A

cost 100,0000

message default

after i click ok, it should decrease that cost with money in the table.

my moneytable display like that:

userid money

A 20000,0000

B 40000,0000

but it not decrease . because the system read 100,0000 is same with 1000000.

if i insert 1,0000 , it can decrease. and the money from userid A in table became 10000,0000.

i am confusion... the system can read data in table like 10000,0000=10000 .but can not read in store procedure..

it seem in store procedure read ike 100,0000=1000000.

how should i do? is there i need change code in store procedure?

thx...

money format

Hi.
I have a money field and its value is greater than a thousand.For example its value = 32.885,60
I want to show the field's format like this (this number's format).
I mean the thousand separator should be .(dot) and the decimal separator should be ,(comma)
And I want two digit after the decimal separator.All these conditions matches with this number(32.885,60)
Could you help me?

hi muhsin,

write an embeddede function which returns your amount like this: amount.ToString("N")

|||

Dear,

Write the embedded fucntion (=format(field,'fomat')

HT

from

sufian

|||Can you write for me please?
I tried but it didn't work.
What should I write in the format style area?|||

Could you format the string in code and then do a string replace to replace the , with a . and the . with a ,?

|||

Hi Muhsin,

sorry this isn't an answer for you, but very curious as to the currency you are working in?

I am used to formatting it the other way around (32,885.60)

99

|||

this is your function:

shared function GetCurrencyFormat(byVal Amount As Double) As String

return Amount.ToString("N")

end function

to call the function go to your layout and write in the cell or textbox or whatever the following:

=Code.GetCurrencyFormat(Amount)

thats it

|||

SpaceCadet wrote:

this is your function:

shared function GetCurrencyFormat(byVal Amount As Double) As String

return Amount.ToString("N")

end function

to call the function go to your layout and write in the cell or textbox or whatever the following:

=Code.GetCurrencyFormat(Amount)

thats it

But this is not I want.
This is already a format in the properties of the textbox.
You don't need to write a function for this.
I want the thousand seperator to be dot and the decimal seperator to be comma.
Your solution's result is for example 15,250.30.
But I want 15.250,30.
Thanks anyway.
If you find another solution , please share it with me!|||You can accomplish this by adjusting the Language property for the textbox, or the entire report to a language that uses this format. The language settings can be specific to just number formats when just setting the NumeralLanguage property on the textbox. After setting these properties, just use "N" as the format code.

See the following link for more information about International Considerations for Reporting Services:
http://msdn2.microsoft.com/en-us/library/ms156493.aspx

money for nothing that works

What is $5 and 15 minutes when it can make you earn $100,000 ?
Not much, if you ask me...
You've probably seen a lot of those kind of messages over forums and
newsgroups,
and the explanation for this can be given in two words: IT WORKS!
But how on earth, will you ask me, does it actually works? How is such a
thing
possible?
This is a simple pyramid scheme. To explain it to you with realistic
numbers,
lets take a simple example:
Imagine you folow the instructions given below, and post your message to
500
newsgroups (wich isn't much). 10 people read your message and also folow
the
instructions: that's $10 for you.
These 10 people get 10 people each to join the process: that's $100 for
you.
These 100 people get 10 people each to join the process: that's $1,000
for you!
These 1,000 people get 10 people each to join the process: that's
$10,000 for
you!!
These 10,000 people get 10 people each to join the process: that's
$100,000 for
you!!!
After the whole process, you have therefore made $111,110! And it took
you only
15 minutes and $5!
If you think this might work (and it does, I can tell you that), then
folow
these simple instructions:
1) Open a FREE PayPal account (!Business or Premier, not the simple
one!), and
place $5 on it.
2) Using PayPal, pay $1 to the folowing 5 e-mail adresses, with 'Mailing
list
subscription' as subject and no message.
1- monkysnaks@.comcast.net
2- troost19@.zonnet.nl
3- jp2rocks@.vat.it
4- rdl1978@.gmail.com
5- monique_sloan@.hotmail.com
3) Make the changes you want to this message, and remove e-mail adress
number 1.
Then, place e-mail adress number 2 in number 1, 3 in number 2, 4 in
number 3, 5
in number 4, and add your own e-mail adress (the one you used on
PayPal!) in
number 5.
4) Send your new message to all the newsgroups on your server (or at
least 500
of them)!
(!) I suggest you run a IP-hiding software like Steganos Internet Anonym
(free
download on www.steganos.com) while posting with UltraNNTP.
That's it! It will take you 15 minutes and will cost you $5, but it will
also
make you earn more than $100,000!
All you have to do now is wait, and check your inbox to see PayPal money
flying
to you!
So, what is $5 and 15 minutes when it can make you earn $100,000 ?And the chicks for free! :P

Money field

I am using Sql Server reporting services 2000. I have a money field which should show postive or negative amount. I used the format as currency. But it shows as ($40.00) for negative amount(it is placing it in braces) and $40.00 for positive amount. How can I show -$40.00??

HI, Nissan:

You can use this expression instead: =FormatCurrency(Fields!UnitPrice.Value,,,False,)

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

HI, Nissan:

We are marking this issue as "Answered". If you have any new findings or concerns, please feel free to unmark the issue.
Thank you for your understanding!

Money datatype Substring

I have a sql query listed below, I would like to count the number of values
that
the first 5 characters are '80438' from iNum field.
Please help me correct the sql query listed below.
Thank You,
SELECT count(iNum) from Call_Movement
where substring(cast(iNum as money) as char(20),1,5) like '80438'
iNum Money Format
iNum Data In Call_Movement Table
803482000146220.0000
803482000147143.0000
803482000153805.0000Try,
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
go
Expect a table or index scan.
AMB
"Joe K." wrote:

> I have a sql query listed below, I would like to count the number of value
s
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe
create table #test (c1 varchar(100))
insert into #test values ('80438hwdgyde5787kioui')
insert into #test values ('5838hwdgyde5787kioui')
insert into #test values ('80438mjji')
select count(c1) from #test where c1 like '80438%'
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:A5FC4F29-928C-4D6D-B8F6-4A964D6711B2@.microsoft.com...
> I have a sql query listed below, I would like to count the number of
> values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||I received the following error when applying the query listed below.
Please help me resolve the query error listed below.
Thanks,
Error:
Implicit conversion from data type money to varchar is not allowed. Use the
CONVERT function to run this query.
SQL Query:
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
________________________________________
___________________________
"Joe K." wrote:

> I have a sql query listed below, I would like to count the number of value
s
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe,
SELECT count(iNum)
from Call_Movement
where convert(varchar(50), iNum, 0) like '80348%'
AMB
"Joe K." wrote:
[vbcol=seagreen]
> I received the following error when applying the query listed below.
> Please help me resolve the query error listed below.
> Thanks,
> Error:
> Implicit conversion from data type money to varchar is not allowed. Use th
e
> CONVERT function to run this query.
> SQL Query:
> SELECT count(iNum)
> from Call_Movement
> where ltrim(iNum) like '80348%'
> ________________________________________
___________________________
> "Joe K." wrote:
>

Money datatype Substring

I have a sql query listed below, I would like to count the number of values
that
the first 5 characters are '80438' from iNum field.
Please help me correct the sql query listed below.
Thank You,
SELECT count(iNum) from Call_Movement
where substring(cast(iNum as money) as char(20),1,5) like '80438'
iNum Money Format
iNum Data In Call_Movement Table
803482000146220.0000
803482000147143.0000
803482000153805.0000Try,
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
go
Expect a table or index scan.
AMB
"Joe K." wrote:
> I have a sql query listed below, I would like to count the number of values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe
create table #test (c1 varchar(100))
insert into #test values ('80438hwdgyde5787kioui')
insert into #test values ('5838hwdgyde5787kioui')
insert into #test values ('80438mjji')
select count(c1) from #test where c1 like '80438%'
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:A5FC4F29-928C-4D6D-B8F6-4A964D6711B2@.microsoft.com...
> I have a sql query listed below, I would like to count the number of
> values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||I received the following error when applying the query listed below.
Please help me resolve the query error listed below.
Thanks,
Error:
Implicit conversion from data type money to varchar is not allowed. Use the
CONVERT function to run this query.
SQL Query:
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
___________________________________________________________________
"Joe K." wrote:
> I have a sql query listed below, I would like to count the number of values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe,
SELECT count(iNum)
from Call_Movement
where convert(varchar(50), iNum, 0) like '80348%'
AMB
"Joe K." wrote:
> I received the following error when applying the query listed below.
> Please help me resolve the query error listed below.
> Thanks,
> Error:
> Implicit conversion from data type money to varchar is not allowed. Use the
> CONVERT function to run this query.
> SQL Query:
> SELECT count(iNum)
> from Call_Movement
> where ltrim(iNum) like '80348%'
> ___________________________________________________________________
> "Joe K." wrote:
> >
> > I have a sql query listed below, I would like to count the number of values
> > that
> > the first 5 characters are '80438' from iNum field.
> >
> > Please help me correct the sql query listed below.
> >
> > Thank You,
> >
> > SELECT count(iNum) from Call_Movement
> > where substring(cast(iNum as money) as char(20),1,5) like '80438'
> >
> > iNum Money Format
> >
> > iNum Data In Call_Movement Table
> > 803482000146220.0000
> > 803482000147143.0000
> > 803482000153805.0000
> >

Money datatype- simple question!

Hi there!
In SQL server 2000, how do you change the number of decimal digits of money datatype field? By default it is 4 digits. I want to have only 2 digits after decimal point. Please help me!Instead of using money, you can use decimal with a scale of 2 or cast the money type to decimal with a scale of 2.|||Hi there!
Thanks for your reply. Now that I have changed the money datatype to decimal.

But the following line of code gives an 'Type Mismatch' error.

Dim Amt
Amt=rs("Rate")+rs("Interest") 'error in this line
Response.write Amt

'Rate' and 'Interest' are fields of decimal datatype.
rs - is a ADO recordset.
Can you tell me why? I have tried with 'Numeric' datatype also, and got the same error.|||Since your using VB, why not leave it as money and then use the FORMAT function?

MyValue = Format(DBValue,"###,###.##")

OR

In SQL Server you could use the CAST function on the MONEY field

try this

declare @.amt money

set @.amt = 0.2
select CAST(@.amt AS VARCHAR)|||It looks like you are doing asp with vbscript - it that correct ? I have tested it and it works fine. Do you have vb - if so test it with vb. How are you connecting to the database - post your code leading up to the recordset. Which version of sql server are you using - which version of ado/asp are you using ? Can you just set one of the field values equal to amount ?|||Hi rnealejr,
Thanks for the reply. Yes, you are right, I am using ASP with VBscript.

Version: SQL server 2000 & ASP3.0[IIS5.0].

Connection String: "Provider=SQLOLEDB;User ID=sa;password=xxx;Initial Catalog=myDb;Data Source=(local);"

set rs=server.createobject("ADODB.Recordset")
sql="Select * from myTable"
rs.open sql,con
if not rs.EOF then
Amt=rs("Rate")+rs("Interest") 'error in this line
end if

Can you just set one of the field values equal to amount ?
What do you mean by this? I didn't get you.

More Info: I am looking for a solution that is related with SQL server, rather than ASP. [because if the solution is ASP related, then I may have to change a lot of forms - around 150]

Please help me|||I will look into your code. Can you do the following:

Amt = rs("Rate")

and/or

Amt = rs("Interest")|||Hi,
Thanks again for your reply!
When I used Amt = rs("Rate"), it assigns the value.