SQL Server 2012 :: Determine Which Column Is Causing Error Converting Data Type Varchar To Numeric?
Aug 14, 2014
I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
My problem is that Line 6 is:
set @brn_pk = '0D4BDE66347C440F'
so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?
View 3 Replies
ADVERTISEMENT
Oct 30, 2014
OK, so I have this query where I'm trying to subtract values like this, when I do this I am getting (Arithmetic overflow error converting varchar to data type numeric.) I have tried many different things, and now of these work, it'll either return 0 because it loses the .XXXXX.
Convert(DECIMAL(10,7),CAST([TIME_OF_COMPLETION] as DECIMAL(10,7)) - Convert(DECIMAL(10,7),CAST([OPR_DELIVERED_TIME] as DECIMAL(10,7)) round(cast(cast(hist.[TIME_OF_COMPLETION] AS float) as DECIMAL(15, 5)) - CAST(hist.[OPR_DELIVERED_TIME] AS FLOAT),1 SELECT convert(FLOAT,CAST('735509.00053' AS DECIMAL(10,5))) - convert(FLOAT,CAST('735509.00047' AS DECIMAL(10,5)))
View 1 Replies
View Related
Jun 17, 2015
As per my requirment using below script to convert from varchar to numeric.
declare @stg varchar(50)
set @stg='12,000,00'
select CONVERT(NUMERIC(17,2),@stg)
Getting error : Error converting data type varchar to numeric
View 6 Replies
View Related
Dec 10, 2014
I have a sql code that I am having some difficulty with. All I'm trying to do is get each department and sum the amount by month based on the date in the table. I am getting "error converting data type varchar to numeric".
Code:
select DEPARTMENT,
CASE
WHEN ((DATE >= '06/01/2014') AND (DATE <= '06/30/2014')) THEN (cast(sum(Amount) as decimal(10,2)))
ELSE '' END AS 'JUNE',
CASE
WHEN ((DATE >= '07/01/2014') AND (DATE <= '07/30/2014')) THEN (cast(sum(Amount) as decimal(10,2)))
ELSE '' END AS 'JULY'
from L27_PHAROS_DETAIL_DATA
WHERE Department = 'TECHNOLOGY SERVICES'
GROUP BY DEPARTMENT, DATE
ORDER BY DEPARTMENT
View 5 Replies
View Related
Jun 13, 2008
Hi Group:
I try to JOin to tables: Enterprise has a decimal(5,3) and COHIER has varchar 5. All I am getting is an error on converting data. How can I fix this problem.
SELECT a.Security_Value, b.Enterprise_Number, b.Enterprise_Description
FROM dbo.COHIER a INNER JOIN
dbo.Enterprise b ON a.Security_Value = b.Enterprise_Number
Thanks!!!!!!
View 6 Replies
View Related
Feb 14, 2007
Select SchoolNumber + ' ' + ShortSchoolName as SchoolList from tblSchoolsSubset
This is my simple sql statement which generates the error in the subject line.
SchoolNumber is type numeric and ShortSchoolName is nvarchar
There are only numbers in SchoolNumber.
View 2 Replies
View Related
Feb 26, 2007
Yes again a topic like this, I couldn't get an answer for my problem so I started a new topic.
Im getting this error for these lines:
$registreer = "INSERT INTO Gebruikerstelefoon (gebruikersnaam, telefoonnummer, volgnr) VALUES ('".$_SESSION['gebruikersnaam']."', '".$_POST["telefoonnummer"]."', '')";
mssql_query($registreer) or die("Fout bij toewijzen telefoonnummer.");
'telefoonnummer' (phonenumber) is nummeric, users can insert their phonenumber in a input field (text) and register along.. ( this is a second query for the column phonennumber) though this doesn't work..
View 3 Replies
View Related
Mar 20, 2007
Hi guys,
Been a while, also tried doing a search but no go.
Im getting this error in sql analyser:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric
Both queries work fine individually, im trying to append them together, with column columns as below:
SELECT PERIOD.STARTDATE,
PERIOD.DESCRIPTION,
COMMISSION_TXN.PERIODSEQ,
COMMISSION_TXN.SUPER_DISTRIBUTOR_CODE,
COMMISSION_TXN.PREMISE_CODE,
COMMISSION_TXN.DISTRIBUTOR_CODE,
COMMISSION_TXN.INTERNAL_EXTERNAL,
COMMISSION_TXN.SERVICE_ID,
COMMISSION_TXN.ACCOUNT_NUMBER,
COMMISSION_TXN.PROMOTION_CODE,
COMMISSION_TXN.PRODUCTDESCRIPTION,
COMMISSION_TXN.PRODUCTID,
COMMISSION_TXN.PROMOTION_NAME,
COMMISSION_TXN.EVENTTYPEID,
COMMISSION_TXN.MEASUREMENT_NAME,
Mobile_revenue = ('No Mobile revenue for txn level'),
COMMISSION_TXN.COMMISSION_AMOUNT,
product_id = ('No product id for txn level')
FROM COMMISSION_TXN INNER JOIN
PERIOD ON COMMISSION_TXN.PERIODSEQ = PERIOD.PERIODSEQ
union all
SELECT DISTINCT
PERIOD.STARTDATE,
PERIOD.DESCRIPTION,
TC_Mobile_Usage.PERIODSEQ,
Dealer_Hierarchy.SUPER_DISTRIBUTOR_CODE,
TC_Mobile_Usage.GENERICATTRIBUTE1 AS Dealer_premise_code,
Dealer_Hierarchy.DISTRIBUTOR_CODE,
internal_external = ('External'),
TC_Mobile_Usage.GENERICATTRIBUTE2 AS Service_Id,
account_number = ('none for txn file'),
promotion_code = ('Usage'),
Product_description = ('Usage'),
Product_id = ('Usage'),
promotion_name = ('Usage'),
TC_Mobile_Usage.EVENTTYPEID,
measurement_name = ('Usage'),
sum(TC_Mobile_Usage.VT_VALUE) AS Mobile_revenue,
sum(TC_Mobile_Usage.CONTRIBUTIONVALUE) AS Commission_Amount,
TC_Mobile_Usage.PRODUCTID
FROM TC_Mobile_Usage INNER JOIN
PERIOD ON TC_Mobile_Usage.PERIODSEQ = PERIOD.PERIODSEQ INNER JOIN
Dealer_Hierarchy ON TC_Mobile_Usage.GENERICATTRIBUTE1 = Dealer_Hierarchy.PARTICIPANTID
WHERE (NOT (TC_Mobile_Usage.PRODUCTID = 'iddv')) AND (NOT (Dealer_Hierarchy.POSITIONTYPEID LIKE 'MI %')) AND
(TC_Mobile_Usage.PERIODSEQ IN (107))--, 108, 110, 111, 112, 114))
group by PERIOD.STARTDATE,
PERIOD.DESCRIPTION,
TC_Mobile_Usage.PERIODSEQ,
Dealer_Hierarchy.SUPER_DISTRIBUTOR_CODE,
TC_Mobile_Usage.GENERICATTRIBUTE1,
Dealer_Hierarchy.DISTRIBUTOR_CODE,
TC_Mobile_Usage.GENERICATTRIBUTE2,
TC_Mobile_Usage.EVENTTYPEID,
TC_Mobile_Usage.PRODUCTID
Any suggestion on how to fix it?
Champinco
View 1 Replies
View Related
Jun 27, 2007
Hi
Please help me out
declare @CID numeric(3,0)
set @CID = 121
SELECT *
from Table_name
where ID Like ''''+@CID+'%'''
I am getting error like
Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to numeric.
T.I.A
View 5 Replies
View Related
Oct 5, 2007
I am having difficulty with my sql and I am not sure what do to. I am new to this. Any help you can give would be helpful.
Here is the statement that is giving me trouble. If I take this out of my Sql query everything runs fine.
CASE clm_att1
WHEN 'NG' THEN (clm_sppo)*(clio_fee04/100)
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN (clm_sppo)*(clio_fee04/100)*.65
ELSE ''
END as AccessFeeFinal,
Below is my full query.
SELECT
--clms_sku,
--clms_line,
--clm_prod,
clio_type,
clm.clm_id1,
clm.clm_rcvd,
CLM_DOUT,
clm_wkpct,
clm.clm_1a,
clm.clm_12a,
clm.clm_12b,
clm.clm_55d,
clm.clm_clir,
clm.clm_65a,
clm.clm_medb2,
clm.clm_tchg,
clm.clm_base,
clm.clm_stades,
clm.clm_meda4,
CLM_H30 =
Case
When CLM_H30 = 0.00 Then clm_sppo
else clm_H30
End,
clm_prod =
CASE
WHEN CLMS_sku = 'NEGO' THEN 'NEG'
WHEN CLMS_sku = '% OF CHGS' THEN 'NEG'
ELSE clm_prod
End,
"clm_nego" =
CASE
WHEN CLM_ATT1 = 'NA' THEN 0.00
WHEN CLM_ATT2 = 'NA' THEN 0.00
WHEN CLM_ATT3 = 'NA' THEN 0.00
WHEN CLM_ATT4 = 'NA' THEN 0.00
ELSE clm_nego
END,
"clm_sppo" =
CASE
WHEN CLM_ATT1 = 'NA' THEN 0.00
WHEN CLM_ATT2 = 'NA' THEN 0.00
WHEN CLM_ATT3 = 'NA' THEN 0.00
WHEN CLM_ATT4 = 'NA' THEN 0.00
ELSE clm_sppo
END,
clm_1e,
clm.CLM_ATT1 as Note,
CASE clm_att1
WHEN 'NG' THEN (clm_sppo)*(clio_fee04/100)
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN (clm_sppo)*(clio_fee04/100)*.65
ELSE ''
END as AccessFeeFinal,
CLM_ATT2,
CLM_ATT3,
clio_fee04 as "ACCESSFEEIMPACT",
"(clm_sppo/CLM_TCHG) * 100" =
CASE
WHEN CLM_ATT1 = 'NA' THEN 0.00
WHEN CLM_ATT2 = 'NA' THEN 0.00
WHEN CLM_ATT3 = 'NA' THEN 0.00
WHEN CLM_ATT4 = 'NA' THEN 0.00
ELSE (clm_sppo/CLM_TCHG) * 100
END,
clio_wrk1 as MAS90#
FROM dbo.clm clm Join
dbo.cli cli ON clm.clm_clir = cli.cli_id1 JOIN
dbo.clip clip ON cli.cli_id1 = clip.clip_id1 JOIN
dbo.clio clio ON cli.cli_id1 = clio.clio_id1
INNER JOIN dbo.clms clms ON clms.clms_id = clm.clm_id1
WHERE
(clip.clip_prd IN ('NEG', 'ADV')) AND
(clm_prod = 'ADV' OR clms_sku = 'NEGO' OR clms_sku = '% OF CHGS')AND
(CLM_STADES IN ('DONE','DUPO','DUPL'))and
clm_adjto = '' AND CLM_TCHG > 0. and
(clio.clio_type = 'AC')
View 4 Replies
View Related
Oct 17, 2007
I am having problems with a statment that I am writing.
The prop_uac is a alpha field and prop_disc is a numeric field. What I was trying to do is when the alpha field is not populated automatically pull from the numeric field. Then I wanted to put all of the results into one column. Can this be done? below is my statement. I hope you can help me.
Prop_uac =
CASE
WHEN prop_uac = '' THEN prop_disc
else prop_uac
End
View 2 Replies
View Related
Mar 24, 2008
hello everyone... i have some problem to with my trigger.. after execute the trigger.. i got this errro "Msg 8114, Level 16, State 5, Procedure trg_InsertPVReadingMeter, Line 14
Error converting data type varchar to numeric."
below is my command sql table PVReadingMeter
quote:USE [PVMC Database]
GO
/****** Object: Table [dbo].[PVReadingMeter] Script Date: 03/25/2008 01:18:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PVReadingMeter](
[PV_reading_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_PVReadingMeter_PV_reading_id] DEFAULT (newid()),
[PV_reading_date_time] [datetime] NOT NULL,
[PV_reading_meter] [decimal](8, 2) NOT NULL,
[PV_normalized_monthly_yield] [float] NOT NULL,
[PV_normalized_monthly_energy_yield] [float] NOT NULL,
[PV_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_PVReadingMeter_PV_application_id] DEFAULT (newid()),
CONSTRAINT [PK_PVReadingMeter] PRIMARY KEY CLUSTERED
(
[PV_reading_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [PVMC Database]
GO
ALTER TABLE [dbo].[PVReadingMeter] WITH CHECK ADD CONSTRAINT [FK_PVReadingMeter_Photovoltaic] FOREIGN KEY([PV_application_id])
REFERENCES [dbo].[Photovoltaic] ([PV_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE
below is my trigger
quote:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE Trigger [trg_InsertPVReadingMeter]
on [dbo].[PVReadingMeter]
for insert
as
begin
DECLARE @PVReadingMeter VARCHAR(100)
DECLARE @PVNormalizedMonthlyYield VARCHAR(100)
SELECT @PVReadingMeter = (SELECT PV_reading_meter + '' FROM Inserted)
SELECT @PVNormalizedMonthlyYield = (SELECT PV_normalized_monthly_yield + '' FROM Inserted)
-- Print the name of the new author
PRINT 'The new photovoltaic reading meter"' + @PVReadingMeter +'" is added.'
PRINT 'The photovoltaic normalized monthly yield is "' + @PVNormalizedMonthlyYield +'"'
end;
now, this is command to insert the values into PVReadingMeter table using trigger that has been created....
quote:insert into PVReadingMeter (PV_reading_date_time, PV_reading_meter, PV_normalized_monthly_yield,
PV_normalized_monthly_energy_yield, PV_application_id)
values (getDate(), 1.1, 4234.33, 43.44, '3D23C4FE-F6FC-475E-8CEC-23FE4E738EB9')
so, i got this error... Msg 8114, Level 16, State 5, Procedure trg_InsertPVReadingMeter, Line 14
Error converting data type varchar to numeric.
what should i do???
View 2 Replies
View Related
Mar 28, 2008
im trying to convert this Code from Varchar to Numeric(CAST(ISNULL(del.[Day 60 AMT],0) AS NUMERIC(6,3))+ CAST(ISNULL(del.[Day 90 AMT],0) AS NUMERIC(6,3)) + CAST(ISNULL(del.[Day 120 AMT],0)AS NUMERIC(6,3)) + CAST(ISNULL(del.[Day 150 AMT],0)AS NUMERIC(6,3)) + CAST(ISNULL(del.[Day 180 AMT],0)AS NUMERIC(6,3)) + CAST(ISNULL(del.[Day 210+ AMT],0)AS NUMERIC(6,3))) > 0
i got an error please help.
View 4 Replies
View Related
Sep 25, 2007
DECLARE @ENTITY nvarchar (100)
set @ENTITY = 'AccidentDimension'
DECLARE @FIELD nvarchar (100)
set @FIELD = 'JurisdictionState'
DECLARE @KEYID nvarchar (100)
SET @KEYID = '1234567890'
DECLARE @VALUE nvarchar (100)
SET @VALUE = 'WI'
DECLARE @WC_TABLE NVARCHAR(100)
SET @WC_TABLE = 'WorkingCopyAdd' + @ENTITY
DECLARE @SQL1 NVARCHAR (1000)
SET @SQL1 = 'INSERT INTO ' + @WC_TABLE+ ' (Claim, '+ @Field +') VALUES ('''+ @KEYID +''', '''+@VALUE+''')'
EXECUTE sp_executesql @SQL1
Can somebody help me. I get this error:
Error converting data type varchar to numeric.
while executing this Dynamic TSQl Command
View 15 Replies
View Related
Aug 13, 2007
Hi,
Thank you in advance for your comments/suggestions. I am trying to create a View of a Table. The table is created by another application so I am unable to recreate it they way I want, also the data that is in the columns that I want to CAST are "numbers" not letters and will only be numbers. In the view I need certain columns to be CAST as numeric from varchar.
Here is the syntax that I am currently using:
Code Snippet
SELECT CAST(szF1 AS datetime) AS [Login Date/Time], szF2 AS [User Name], CAST(szF3 AS numeric) AS [Documents Indexed], CAST(szF4 AS datetime) AS [Logout Date/Time], CAST(szF5 AS numeric) AS [Documents Sent to QC], CAST(szF6 AS numeric) AS [Documents Reconciled], szF7 AS [Reject Reason], CAST(szF8 AS datetime) AS [Report Date]
FROM dbo.F_Report_Data AS a
WHERE (szF3 <> 'Blank')
When I open the view I get the error message about converting varchar to numeric.
Thanks,
Erik
View 16 Replies
View Related
Feb 3, 2005
I have always programmed my ASP pages with MS Access DB's and we recently got a SQL Server 2000. So, I'm totally new to SQL Server 2000.
I trying to retrieve data from a table in SQL, based on values from two input fields (text fields) on a form in the web page.
To process the form, I am using the following syntax:
Set oRs = oConn.Execute("SELECT BBILNAM, BTWP, BTXP, BPARCEL, BBILADD, BBILCTYST, BBILZIP5 FROM TESTdb WHERE BTWP = 'TWP' AND BPARCEL = 'PARCEL'")
SQL Fields in Table TESTdb are (BTWP = numeric field) and (BPARCEL = text field)
I realize that the problem is that I'm comparing a numeric field in my table, to a text field from my form, but don't know how to convert the text field to a numeric field, prior to the execution of the select statement. I'm hoping someone can point me in the right direction. Thanks!
View 2 Replies
View Related
Nov 17, 2015
Below is garbage data and structure (I think enough to get point across). How can I perform calculations needed?
Create Table #1234
(
abcd decimal(16,4)
,defg decimal(16,4)
,hijk decimal(16,4)
,logon datetime
,logoff datetime
[code]....
View 2 Replies
View Related
Aug 28, 2012
Here's the SP
Code:
USE [byrndb]
GO
/****** Object: StoredProcedure [dbo].[sp_GetLikeJobsByJobNumber] Script Date: 08/28/2012 15:17:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
Why when I run
Code:
USE [byrndb]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[sp_GetLikeJobsByJobNumber]
@number = N'23913'
SELECT'Return Value' = @return_value
GO
Am I getting an "Msg 8114, Level 16, State 5, Procedure sp_GetLikeJobsByJobNumber, Line 16
Error converting data type varchar to numeric." error? Line 16 is "@number varchar = null"
View 5 Replies
View Related
Aug 6, 2014
I am trying to add a field (bank_chk_amt] from a table in to my query to bring in a dollar amount....I originally had this below
SELECT dbo.CBPAYMENT.REFERENCE_NO, 'CCMCCHBREF' AS Literal, RTrim([description]) + ', ' + [bank_chk_amt] + ', ' + convert(char(10),[check_date],101) + ', ' + 'Refund check sent to ' + [payee_name] AS [Free Text]
FROM dbo.CBPAYMENT;
but I would get "Error converting data type varchar to numeric".So my co-worker modified it and added (str([bank_chk_amt]) to my query which worked, but I noticed it dropped of the cents. So instead of 80.35 it would show 80 And I noticed it rounded 100.50 to 101...How can I bring in the full dollar amount and without adding?
View 2 Replies
View Related
Jun 5, 2015
In my inner query,
I have a column "Cost" (data type=varchar) and another column "Qty" (data type=int)
Now in outer query, I need to multiply this 2 columns Cost*Qty.But after trying some cast operation still getting "Error converting data type varchar to numeric."
View 7 Replies
View Related
Sep 1, 2014
I have the following code and i want to passed more than one value:
DECLARE @myvendedor AS varchar(255)
SET @myvendedor = '87,30'
print @myvendedor
SELECT top 10 ECOM.COM1,* from ecom (nolock) WHERE ecom.PORVEND=1 AND ECOM.VENDEDOR IN (@myvendedor)
Table Field ECOM.VENDEDOR is Numeric(4,0)
This error occur:
87,30 --Result of PRINT
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
I change :
DECLARE @myvendedor AS numeric(4,0)
and this error appear:
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
View 9 Replies
View Related
Apr 3, 2014
I am trying to setup an indicator value for an SSRS report to show green and red values on a report, based on the NRESULT value. The problem I am facing is that I have several different CASE statements that have the same logic, and they are processing just fine. NRESULT is a decimal field, so no conversion should be necessary. I do not know why I am getting the "Arithmetic overflow error converting varchar to data type numeric." error message.
Below is the CASE statement where the error is occurring. It is in the part of the ELSE CASE. The first CASE works just fine when the ELSE CASE is commented out. If I also change the ELSE CASE statement to say "else case when LEFT(NRESULT,1) = '-' then '0'", then it processes fine, too, so it has to be something I am missing something in the check on negative values. I do need the two checks, one for positive and one for negative values, to take place.
case when LEFT(NRESULT,1) <> '-' then --This portion, for checking positive values, of the CASE statement works fine.
CASE WHEN LEFT(ROUND(NRESULT,2),4) between 0.00 and 0.49 THEN '2' --Green
ELSE CASE WHEN LEFT(ROUND(NRESULT,2),4) > 0.49 THEN '0' --Red
ELSE '3' --White
END
END
else case when LEFT(NRESULT,1) = '-' then --This portion, for checking negative values, of the CASE statement is producing the conversion error message.
[code]....
I checked the NRESULT field, and there are not any NULL values in there, either.
View 1 Replies
View Related
Feb 2, 2013
when i execute my store procedure it give this error.
USE [CWMNEW]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[BOQ]
SELECT'Return Value' = @return_value
GO
[code]....
View 1 Replies
View Related
Oct 14, 2005
Hi,
This is my complete trigger.
ALTER TRIGGER DeleteTriggerC_Middleware_Exception ON dbo.C_Middleware_Exception AFTER DELETE AS
BEGIN
DECLARE @RowCount AS VARCHAR
SET @RowCount = @@ROWCOUNT
DECLARE @TableName_Deleted AS VARCHAR(50)
DECLARE @ErrorMsg_Deleted AS VARCHAR(255)
DECLARE @AddlErrMsg_Deleted AS VARCHAR(255)
DECLARE @SubjectArea_Deleted AS VARCHAR(25)
DECLARE @CHKPT_Deleted AS VARCHAR(10)
SELECT @TableName_Deleted = table_name , @ErrorMsg_Deleted = cast(error_msg AS varchar(255)) , @AddlErrMsg_Deleted = cast(addl_error_msg AS varchar(255)),
@SubjectArea_Deleted = cast(SUBJECT_AREA AS varchar(25)), @CHKPT_Deleted = cast(CHKPT AS varchar(255))FROM DELETED
--where subject_area = 'inventory'
UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE Table_Name = @TableName_Deleted
IF @@ROWCOUNT = 0
BEGIN
UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE @ErrorMsg_Deleted like Error_Msg and Subject_Area IS NULL and ChkPoint IS NULL
END
IF @@ROWCOUNT = 0
BEGIN
UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE @AddlErrMsg_Deleted like Addl_Error_Msg
END
IF @@ROWCOUNT = 0
BEGIN
UPDATE dbo.Error_Log SET No_Of_Occurance = No_Of_Occurance + @RowCount WHERE @ErrorMsg_Deleted like Error_Msg and Subject_Area = @SubjectArea_Deleted and ChkPoint = @CHKPT_Deleted
END
END
when i am executing the follwing query i am gettin systax error.
Query :delete from dbo.c_middleware_exception where subject_area = 'eap_room'
Error :
Server: Msg 245, Level 16, State 1, Procedure DeleteTriggerC_Middleware_Exception, Line 17
Syntax error converting the varchar value '*' to a column of data type int.
what could be the solution.
Thanks in advance
yvnsmca
View 1 Replies
View Related
Jul 20, 2005
I have an inventory database that Im trying to create a report out ofthe IP address are a lookup on a seperat table but I keep getting theabove error can I change the table row to something to fix this orwhat.SELECT i.INVENTORY_ITEM_ID AS [IP Address],i.HOST_NAME AS [ServerName], '' AS Flag, i.MEMO AS Comments, 'Seattle' AS City, 'Washington'AS State,CASE WHEN fv.value = 'EL EET1410' THEN '1111 3rdAve.' WHEN fv.value = 'EL WFL17' THEN '999 3rd Ave.' ELSE '' END AS[Address 1],' ' AS [Address 2], CASE WHEN fv.value = 'ELEET1410' THEN '1111' WHEN fv.value = 'EL WFL17' THEN '2222' ELSE ''END AS [Building ID],fv.VALUE AS Building, '98101' AS Zip,CASE WHEN fv.value = 'EL EET1410' THEN '14' WHENfv.value = 'EL WFL17' THEN '17' ELSE '' END AS [Computing FacilityLevel],CASE WHEN fv.value = 'EL EET1410' THEN '14' WHENfv.value = 'EL WFL17' THEN '17' ELSE '' END AS [Bldg Floor],CASE WHEN fv.value = 'EL EET1410' THEN 'C028'WHEN fv.value = 'EL WFL17' THEN 'C123' ELSE '' END AS WSPID,i.LOCATION_IN_FACILITY AS Location,i.SERIAL_NUMBER AS [Serial Number], i.ASSET_TAG AS [Asset Tag], ' ' AS[Second Asset Tag(s)],vv.VALUE AS Manufacture, mv.VALUE AS Model,'Yes' AS [Rack Mountable], rv.VALUE AS [Rack Units], 'DEV' AS [ServerEnviroment],lv.VALUE AS [Sever Type], ov.VALUE AS OS,CASE WHEN lv.value = 'Server - Intel Blade' THEN'Windows' WHEN lv.value = 'Server - Intel' THEN 'Windows' WHENlv.value = 'Server - Unix' THEN 'Unix'ELSE '' END AS [OS Type], '19x28' AS Footprint,ev.VALUE AS [Technical Owner of Server], 'N/A' AS [SLA Category],i.ON_BOARD_NIC_PORT_COUNT AS [NW Connectionquantity], 'Devlopment/Test Machine' AS [Application(s) Name],ev.VALUE AS [PW Contact],'N/A' AS RTO, 'N/A' AS RPO, 'No' AS [Is BoxClustered?], 'N/A' AS [Buisness Critical],CASE WHEN mv.value = 'Proliant DL580G1' THEN'90lbs' WHEN mv.value = 'Proliant DL360G2' THEN '85lbs' WHEN mv.value= 'Enterprise 220R' THEN'45lbs' ELSE '' END AS Weight,i.LAST_MODIFIED_DATE AS [Date of Last Install], 'No' AS [DedicatedCircuit]FROM dbo.INVENTORY_ITEM i LEFT OUTER JOINdbo.IP_TO_INVENTORY pv ON pv.IP_ADDRESS = i.INVENTORY_ITEM_IDLEFT OUTER JOINdbo.LOOKUP_VALUE lv ON lv.LOOKUP_VALUE_ID =i.DEVICE_TYPE_ID LEFT OUTER JOINdbo.LOOKUP_VALUE fv ON fv.LOOKUP_VALUE_ID =i.FACILITY_ID LEFT OUTER JOINdbo.LOOKUP_VALUE vv ON vv.LOOKUP_VALUE_ID =i.VENDOR_ID LEFT OUTER JOINdbo.LOOKUP_VALUE mv ON mv.LOOKUP_VALUE_ID =i.MODEL_ID LEFT OUTER JOINdbo.LOOKUP_VALUE ov ON ov.LOOKUP_VALUE_ID =i.SOFTWARE_VERSION_ID LEFT OUTER JOINdbo.LOOKUP_VALUE ev ON ev.LOOKUP_VALUE_ID =i.CHECKED_OUT_BY_ID LEFT OUTER JOINdbo.LOOKUP_VALUE rv ON rv.LOOKUP_VALUE_ID =i.U_HEIGHT_ID
View 2 Replies
View Related
Aug 29, 2007
SQL server 2000
Note : GRP_ID integer
declare @SchDnGRP VARCHAR(50)
SET @SchDnGRP='29,30'
SELECT USR_ID_ARY from AD_USR_GRP Where GRP_ID in(@SchDnGRP)
i got error like
Syntax error converting the varchar value '29,30' to a column of data type int.
if need any sql server Configuration requie, please advice to me
View 5 Replies
View Related
Feb 10, 2006
I have a table(tab1) with a column(col1) of type varchar. I insert a row with an integer value(1). And when i query the table using the sql, select col1 from tab1 where col1 = 1, it works fine.
But after i insert a varchar, say 'a' and then do the same query, i get an error message saying, "Syntax error converting the varchar value 'a' to a column of data type int.". Why is this so? Please reply.
View 11 Replies
View Related
Nov 4, 2005
Well I am doing a simple insert through a stored procedure..., but I am getting this error. In my table I have the UserID as a VARCHAR 100. I am storing from a textbox that will have number because its there email. But I am not converting anything. Do I need to convert in this scenario??
Server Error in '/pickem' Application.
Syntax error converting the varchar value 'johndoe@hotmail.com' to a column of data type int. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'johndoe@hotmail.com' to a column of data type int.Source Error:
Line 113:
Line 114: dbConnection.Open
Line 115: Dim Dr As System.Data.IDataReader = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Line 116:
Line 117: Dim rowsAffected As Integer = 0
Here is the code: Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("connString"))
Dim myCommand as New SqlCommand("prcAddTeam", dbConnection) myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add("@UserID", SqlDbType.Varchar, 100).Value = TRIM(emailBox.Text) myCommand.Parameters.Add("@TeamName", SqlDbType.Varchar, 40).Value = TRIM(teamBox.Text) myCommand.Parameters.Add("@Address1", SqlDbType.Varchar, 50).Value = TRIM(addressBox.Text) myCommand.Parameters.Add("@Address2", SqlDbType.Varchar, 50).Value = TRIM(address2Box.Text) myCommand.Parameters.Add("@City", SqlDbType.Varchar, 50).Value = TRIM(cityBox.Text) myCommand.Parameters.Add("@State", SqlDbType.Varchar, 30).Value = stateList1.SelectedItem.Value myCommand.Parameters.Add("@Zip", SqlDbType.Varchar, 11).Value = TRIM(zipBox.Text) myCommand.Parameters.Add("@FirstName", SqlDbType.Varchar, 50).Value = TRIM(firstNameBox.Text) myCommand.Parameters.Add("@LastName", SqlDbType.Varchar, 50).Value = TRIM(lastnameBox.Text)
dbConnection.Open Dim Dr As System.Data.IDataReader = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Dim rowsAffected As Integer = 0
dbConnection.OpenStored procedure:CREATE PROCEDURE prcAddTeam @UserID VARCHAR(100) ,@TeamName VARCHAR(40) ,@Address1 VARCHAR(50) ,@Address2 VARCHAR(50) ,@City VARCHAR(50) ,@State VARCHAR(30) ,@Zip VARCHAR(11) ,@FirstName VARCHAR(50) ,@LastName VARCHAR(50) ASSET NOCOUNT ONDECLARE @ID INTSET @ID = 0SELECT @ID = ISNULL((SELECT UserID from tblUserInfo where UserID = @UserID),0)IF @ID = 0 BEGIN INSERT INTO tblUserInfo (UserID,TeamName,Address1,Address2,City,State,Zip,FirstName,LastName,CreateDate) VALUES(@UserID,@TeamName,@Address1,@Address2,@City,@State,@Zip,@FirstName,@LastName,GetDate()) SET @ID = @@IDENTITY END
View 5 Replies
View Related
Aug 31, 2006
I am using a stored procedure which returns a value of charecter datatype 'V' to the calling program.I am getting an sql exception System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type inti didnot define any int datatype in my tablethis is my codeSqlCommand com = new SqlCommand("StoredProcedure4", connection);com.CommandType = CommandType.StoredProcedure; SqlParameter p1 = com.Parameters.Add("@uname", SqlDbType.NVarChar);SqlParameter p2 = com.Parameters.Add("@opwd", SqlDbType.NVarChar);SqlParameter p3 = com.Parameters.Add("@role", SqlDbType.NVarChar);p3.Direction = ParameterDirection.ReturnValue;p1.Value = username.Text.Trim();p2.Value = password.Text.Trim();com.ExecuteReader();lblerror2.Text = (string)(com.Parameters["@role"].Value); can your figure out what is the error ? Is it a coding error or error of the databse
View 3 Replies
View Related
Jun 10, 2014
when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric
declare @a numeric(16,4)
set @a=99362600999900.0000
The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.
View 2 Replies
View Related
Mar 21, 2006
Guys
I'm getting the above when trying to populate a variable. The values in question are :
@N = 21
@SumXY = -1303765191530058.2251000000
@SumXSumY = -5338556963168643.7875000000
When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
When I try the following ie to populate a variable with that value I get the error -
SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)
Any ideas ??
View 6 Replies
View Related
Aug 12, 2014
I am doing a Case statement to create a unified field for account type and vendor, in the code below I am receiving the error in the subject, because the account numbers have alpha characters in the string, I need to make them as OTHER if the first 2 left chars are Alpha, I thought how I have ISNUMERIC would do that but I am still getting the error.
I am also including example of how the account_numbers are formatted.
R222209
R222220
R222222
R222212
R221123
F707768
[Code] .....
View 5 Replies
View Related
Oct 13, 2012
When I run the following sql query:
"update table set price = price * 1.1 "..
I get the following error : "Msg 8115, Level 16, State 8, Line 1.. Arithmetic overflow error converting nvarchar to data type numeric. The statement has been terminated."
The table is set to nvarchar, and i am just trying to make the prices go up 10%.
View 9 Replies
View Related
