Numeric Data Type

[SymbionA]

I have noticed that I can run a query in SQL server and it pulls out records that are = 0 but have been qualified in my SQL statement as TEXT i.e = '0'

eg. Select * from Rep_Sum where trackingNumber ='0'

TrackingNumber is a numeric field

However, in MS Access I get a data type mismatch error.

Is there anyway to get SQL server to throw the same data type mismatch error as in Access?

 

[mrdenny]

Not in this case no. In this case what SQL Server is doing is converting the numeric data in the trackingnumber field to a text value and doing the comparision.

SQL Server doesn't automatic data conversation when it sees a problem such as this.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLBill]

In the BOL, use the Index tab, enter "implicit data type conversions", select the option for CAST and CONVERT. Scroll down to the end of the page and look at the chart. SQL Server will do an implicit conversion between VARCHAR/CHAR and NUMERIC.

-SQLBill

Posting advice: FAQ481-4875