SQL Implicit error 260 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Implicit error 260

Author

Topic

jpendleton
Starting Member

2 Posts

Posted - 2011-11-29 : 13:50:04

I am stumped on this issue. I am using an Insert into a table. One of the fields continuosly gives me this error

Disallowed implicit conversion from data type datetime to data type decimal

The field is defined as decimal(16,8),Null

I've tried inserting Null, 0, casting, converting etc... the error does not go away. Here is the query.
---------------------------------------------------------
Select top 0 * Into #StgItem From acuity_app.dbo.StgItem
SET IDENTITY_INSERT #StgItem ON
Insert Into #StgItem

Select
-99 As RowKey,
1 As AllowCostOvrd,
0 AS AllowDecimalQty,
0 AS AllowDropShip,
0 AS AllowPriceOvrd,
0 AS AllowRtrns,
0 AS AllowRtrnsCustomBTO,
0 AS AllowRtrnsPartialBTO,
NULL AS CommClassID,
NULL AS CommodityCodeID,
'00000000000' AS COSAcctNo,
GetDate() AS DateEstab,
NULL AS DfltSaleQty,
0 AS DfltWhseID,
GLCode + '51' AS ExpAcctNo,
0 AS ExplOnInvc,
NULL AS FreightClassID,
0 as HazMAT,
1 AS InclOnPackList,
NULL AS InternalLongDesc,
0 AS IntrnlDeliveryReq,
'NODEF' AS ItemClassID,
ItemID,
3 AS ItemType,
LongDesc,
NULL AS MatchToleranceID,
0 AS MinGrossProfitPct,
0 AS MinSaleQty,
0 AS PerUsageOrdLimit,
0 AS PriceAsCompTotal,
0 AS PriceSeq,
UOM AS PriceUnitMeasID,
NULL AS ProdDetlLink,
NULL AS ProdPriceGroupID,
UOM AS PurchUnitMeasID,
1 AS RcptReq,
0 AS RestockRate,
'00000000000' AS ReturnsAcctNo,
1 AS SaleMultiple,
'00000000000' as SalesAcctNo,
0 AS SalesProdLineID,
UOM AS SalesUnitMeasID,
0 AS Seaconal,
NULL AS SerialIncrement,
NULL AS SerialMask,
0 AS ShelfLife,
ShortDesc,
Active AS Status,
NULL AS STaxClassID,
0 AS StdPrice,
0 AS StdUnitCost,
UOM AS StockUnitMeasID,
0 AS SubjToTradeDisc,
0 AS TargetMargin,
0 AS TrackMethod,
Null AS UserFld1,
Null AS UserFld2,
Null AS UserFld3,
Null AS UserFld4,
Null AS UserFld5,
Null AS UserFld6,
0 AS ValuationMeth,
0 AS WarrantyDays,
0 AS WarrantyProvider,
0 AS ProcessStatus,
1 AS SessionKey

From Items
Where ItemId = 'DYND10300'
SET IDENTITY_INSERT #StgItem OFF

Been working on this for two days.

Any ideas?

jpendleton
Starting Member

2 Posts

Posted - 2011-11-29 : 15:25:13

Oh, very dumb. To clarify - the field is DfltSaleQty.

Server: Msg 260, Level 16, State 1, Line 6
Disallowed implicit conversion from data type datetime to data type decimal, table 'tempdb.dbo.#StgItem____________________________________________________________________________________________________________000000027A84', column 'DfltSaleQty'. Use the CONVERT function to run this query.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-29 : 17:55:22

Could be something as simple as you have got some of the columns the wrong way round in the select statement,

Its lazy and potentially dangerous to INSERT blindly.

You should use


INSERT <tableName> ( Column List )
SELECT ( Column List )

To be on the safe side -- then if someone messes with the ordinal position of your columns then your code will still work. (say someone drops a column and recreates it for instance).

What's happening here, is exactly what the message says.

You are trying to stuff a DATETIME value into a DECIMAL column. It won''t let you. You probably don't want to do that.

If you check the position of the [DfltSaleQty] column in the temp table then you should be able to count terms in your select statement to find out what value that corresponds to.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources