[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Difference in UPDATE between 2000 and 2005

Andrew Hayes

3/28/2007 12:22:00 AM

SQL command:

UPDATE tblGrossUpFixedIncome SET FixedIncome = '3,4,5,6' WHERE KeyID = 1

SQL Server 2000 result:

"Cannot implicitly convert varchar to money. Use CONVERT."

SQL Server 2005 result:

FixedIncome field now contains 3456.

How do I get the UPDATE command in 2005 to give the same result as 2000?
That is, it gives the error result. The 2005 database is set for 2000
compatibility level.
7 Answers

SivaCh

3/28/2007 1:53:00 AM

0

Hi Andrew,
You can try formatting the money column like below, to see the same error as
in 2000.

convert(varchar(12), FixedIncome , 1)

or you can use SET XACT_ABORT ON if you want to avoid the error.

Hope that helps.

regards,
Siva

"Andrew Hayes" wrote:

> SQL command:
>
> UPDATE tblGrossUpFixedIncome SET FixedIncome = '3,4,5,6' WHERE KeyID = 1
>
> SQL Server 2000 result:
>
> "Cannot implicitly convert varchar to money. Use CONVERT."
>
> SQL Server 2005 result:
>
> FixedIncome field now contains 3456.
>
> How do I get the UPDATE command in 2005 to give the same result as 2000?
> That is, it gives the error result. The 2005 database is set for 2000
> compatibility level.
>
>

Andrew Hayes

3/28/2007 2:06:00 AM

0

Thank you Siva. I had considered using Convert in 2005 but hadn't considered
setting XACT. Unfortunately, both options, while useful, won't resolve the
problem in the way I would like as they would require changing the code.

Essentially, the application is written to work with 2000 and the particular
errors it generates. Running against a 2005 backend looks like it'll cause
all sorts of problems.

What I would like is 2005 to behave like 2000 when it comes to implicit and
explicit conversions between data types. I had hoped that 2000 compatibility
mode would mean just that.

Yes, I know that's like having a Nissan Skyline and wanting it to behave
like a Toyota Celica, but when you have a box of parts all stamped Toyota you
can hardly add them to the Nissan.

"SivaCh" wrote:

> Hi Andrew,
> You can try formatting the money column like below, to see the same error as
> in 2000.
>
> convert(varchar(12), FixedIncome , 1)
>
> or you can use SET XACT_ABORT ON if you want to avoid the error.
>
> Hope that helps.
>
> regards,
> Siva
>
> "Andrew Hayes" wrote:
>
> > SQL command:
> >
> > UPDATE tblGrossUpFixedIncome SET FixedIncome = '3,4,5,6' WHERE KeyID = 1
> >
> > SQL Server 2000 result:
> >
> > "Cannot implicitly convert varchar to money. Use CONVERT."
> >
> > SQL Server 2005 result:
> >
> > FixedIncome field now contains 3456.
> >
> > How do I get the UPDATE command in 2005 to give the same result as 2000?
> > That is, it gives the error result. The 2005 database is set for 2000
> > compatibility level.
> >
> >

Uri Dimant

3/28/2007 6:26:00 AM

0

Andrew

> How do I get the UPDATE command in 2005 to give the same result as 2000?

But you got
> "Cannot implicitly convert varchar to money. Use CONVERT."

Can you post table's structure along with sample data?



"Andrew Hayes" <AndrewHayes@discussions.microsoft.com> wrote in message
news:D557E85B-523E-4B96-8AE9-308F333EAE58@microsoft.com...
> SQL command:
>
> UPDATE tblGrossUpFixedIncome SET FixedIncome = '3,4,5,6' WHERE KeyID = 1
>
> SQL Server 2000 result:
>
> "Cannot implicitly convert varchar to money. Use CONVERT."
>
> SQL Server 2005 result:
>
> FixedIncome field now contains 3456.
>
> How do I get the UPDATE command in 2005 to give the same result as 2000?
> That is, it gives the error result. The 2005 database is set for 2000
> compatibility level.
>
>


Andrew Hayes

3/28/2007 8:46:00 AM

0

Structure and sample as requested:

CREATE TABLE [dbo].[tblGrossUpFixedIncome](
[KeyID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[ValidDate] [datetime] NOT NULL,
[FixedIncome] [money] NULL,
[FixedYN] [bit] NOT NULL CONSTRAINT [DF_tblGrossUpFixedIncome_FixedYN]
DEFAULT (0),
CONSTRAINT [PK_tblGrossUpFixedIncome] PRIMARY KEY CLUSTERED
(
[KeyID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

1 2220 2002/05/01 0:00:00 1234567.0000 False
2 2194 2002/04/01 0:00:00 2136200.0000 False
3 2222 2002/04/01 0:00:00 1102100.0000 False
4 2220 2002/06/01 0:00:00 1817960.0000 False
5 2194 2002/07/01 0:00:00 1265000.0000 False
6 2222 2002/07/01 0:00:00 1016600.0000 False
7 2222 2002/08/01 0:00:00 2033200.0000 False
8 2237 2002/10/01 0:00:00 369700.0000 False
9 2194 2002/10/01 0:00:00 393800.0000 False
10 2238 2002/11/01 0:00:00 279560.0000 False

I'm sure you'll find the same result, and what I'm looking for is a 2005
option setting for either the server or the database so that trying to set a
money field using a string gives me an error without having to rewrite any
stored procedures, command text, or change the table structure.

Uri Dimant

3/28/2007 9:10:00 AM

0

Andrew
Run it on SQL Server 2005 SP2a (db=80 level)
CREATE TABLE [dbo].[tblGrossUpFixedIncome](

[KeyID] [int] IDENTITY(1,1) NOT NULL,

[EmployeeID] [int] NOT NULL,

[ValidDate] [datetime] NOT NULL,

[FixedIncome] [money] NULL,

[FixedYN] [bit] NOT NULL CONSTRAINT [DF_tblGrossUpFixedIncome_FixedYN]

DEFAULT (0),

CONSTRAINT [PK_tblGrossUpFixedIncome] PRIMARY KEY CLUSTERED

(

[KeyID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

INSERT INTO [tblGrossUpFixedIncome]([EmployeeID],[ValidDate],

[FixedIncome],[FixedYN]) VALUES

(2220, '2002/05/01 0:00:00', 1234567.0000 ,0)





SELECT * FROM [tblGrossUpFixedIncome]

UPDATE tblGrossUpFixedIncome SET FixedIncome = '3,4,5,6' WHERE KeyID = 1

--I got 3456.00

What do you suppose to get?









"Andrew Hayes" <AndrewHayes@discussions.microsoft.com> wrote in message
news:5FD0C316-30CB-4503-8F84-46785D086EC0@microsoft.com...
> Structure and sample as requested:
>
> CREATE TABLE [dbo].[tblGrossUpFixedIncome](
> [KeyID] [int] IDENTITY(1,1) NOT NULL,
> [EmployeeID] [int] NOT NULL,
> [ValidDate] [datetime] NOT NULL,
> [FixedIncome] [money] NULL,
> [FixedYN] [bit] NOT NULL CONSTRAINT [DF_tblGrossUpFixedIncome_FixedYN]
> DEFAULT (0),
> CONSTRAINT [PK_tblGrossUpFixedIncome] PRIMARY KEY CLUSTERED
> (
> [KeyID] ASC
> )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON
> [PRIMARY]
> ) ON [PRIMARY]
>
> 1 2220 2002/05/01 0:00:00 1234567.0000 False
> 2 2194 2002/04/01 0:00:00 2136200.0000 False
> 3 2222 2002/04/01 0:00:00 1102100.0000 False
> 4 2220 2002/06/01 0:00:00 1817960.0000 False
> 5 2194 2002/07/01 0:00:00 1265000.0000 False
> 6 2222 2002/07/01 0:00:00 1016600.0000 False
> 7 2222 2002/08/01 0:00:00 2033200.0000 False
> 8 2237 2002/10/01 0:00:00 369700.0000 False
> 9 2194 2002/10/01 0:00:00 393800.0000 False
> 10 2238 2002/11/01 0:00:00 279560.0000 False
>
> I'm sure you'll find the same result, and what I'm looking for is a 2005
> option setting for either the server or the database so that trying to set
> a
> money field using a string gives me an error without having to rewrite any
> stored procedures, command text, or change the table structure.
>


Andrew Hayes

3/28/2007 11:22:00 AM

0

> UPDATE tblGrossUpFixedIncome SET FixedIncome = '3,4,5,6' WHERE KeyID = 1
>
> --I got 3456.00
>
> What do you suppose to get?

That is the correct result for SQL Server 2005, but what I am after is the
same response as SQL Server 2000, which would be an error saying it cannot
implicitly convert a varchar to a money.

Without the error, the exception handling that informs the user that they
entered an incorrect value ( "3,4,5,6" is not really a valid money value ),
doesn't work and while it does update the record the problem lies in users
who miss a digit when entering numbers like "1,230,21". What they really
wanted was 1230210 but instead 123021 gets stored, and then they wonder why
the payroll is all screwed up.

SQL Server 2005 is too smart sometimes. :-)

Mike D

2/19/2011 7:53:00 PM

0


> If you looking to move a game from VA to FL - you should contact Don
> Bosworth.  He is moving games on the East Coast in an enclosed
> trailer.  I know he is doing a run from FL to West VA soon, and should
> be at Allentown too.
>
> Kory

I appreciate the info. However, I already had Pilot Air pick the game
up last week. The price was higher than I'd like, but not as bad as
the $580 quote from NAVL. Oh well, no point in crying over spilled
milk. Next time I'll contact STI, which is what I should have done.

Mike