Help With Error Converting Data Type Varchar To Float

Oct 17, 2007

Error converting data type varchar to float.

I am getting this error. Any one there to guide me

Error Converting Data Type Varchar To Float.

Mar 25, 2008

hello anyone... i got this message "Error converting data type varchar to float" when i was trying to insert values into table using instead of trigger...

below is my table ClimateData

quote:USE [PVMC Database]
GO
/****** Object: Table [dbo].[ClimateData] Script Date: 03/26/2008 03:04:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ClimateData](
[Climate_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClimateData_Climate_application_id] DEFAULT (newid()),
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[Altitude] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[Climate_type] [varchar](100) COLLATE Latin1_General_CI_AI NULL,
[PV_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_ClimateData_1] PRIMARY KEY CLUSTERED
(
[Climate_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [PVMC Database]
GO
ALTER TABLE [dbo].[ClimateData] WITH CHECK ADD CONSTRAINT [FK_ClimateData_Photovoltaic] FOREIGN KEY([PV_application_id])
REFERENCES [dbo].[Photovoltaic] ([PV_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE

Below is photovoltaic table

quote:USE [PVMC Database]
GO
/****** Object: Table [dbo].[Photovoltaic] Script Date: 03/26/2008 03:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Photovoltaic](
[PV_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Photovoltaic_PV_application_id] DEFAULT (newid()),
[PV_site] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_state] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_type_of_system] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_nominal_power] [float] NULL,
[PV_module] [varchar](150) COLLATE Latin1_General_CI_AI NULL,
[PV_mounting] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_building_type] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_topology] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_new_or_retrofit] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_design] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_construction] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_commissioning_date] [datetime] NULL CONSTRAINT [DF_Photovoltaic_PV_commissioning_date] DEFAULT (getdate()),
[PV_site_photo] [varbinary](max) NULL,
[PV_peak_nominal_rating] [float] NULL,
[User_application_id] [uniqueidentifier] NULL,
[Org_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_Photovoltaic_1] PRIMARY KEY CLUSTERED
(
[PV_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [PVMC Database]
GO
ALTER TABLE [dbo].[Photovoltaic] WITH CHECK ADD CONSTRAINT [FK_Photovoltaic_OrganizationDetail] FOREIGN KEY([Org_application_id])
REFERENCES [dbo].[OrganizationDetail] ([Org_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Photovoltaic] WITH CHECK ADD CONSTRAINT [FK_Photovoltaic_Users] FOREIGN KEY([User_application_id])
REFERENCES [dbo].[Users] ([User_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE

Below also my command for instead of trigger

quote:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE trigger [tr_v_PhotovoltaicClimateData] on [dbo].[v_PhotovoltaicClimateData] instead of insert as
BEGIN

insert Photovoltaic (PV_site, PV_state, PV_type_of_system, PV_nominal_power, PV_module,
PV_mounting)
select distinct inserted.PV_site, inserted.PV_state, inserted.PV_type_of_system,
inserted.PV_nominal_power, inserted.PV_module, inserted.PV_mounting
from inserted left join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
and inserted.PV_state = Photovoltaic.PV_state
and inserted.PV_type_of_system = Photovoltaic.PV_type_of_system
and inserted.PV_nominal_power = Photovoltaic.PV_nominal_power
and inserted.PV_nominal_power = Photovoltaic.PV_module
and inserted.PV_nominal_power = Photovoltaic.PV_mounting
where Photovoltaic.PV_site IS NULL /*** Exclude Organization Detail already in the table ***/

insert ClimateData (Latitude, Longitude, Altitude, Climate_type, PV_application_id)
select distinct inserted.Latitude, inserted.Longitude, inserted.Altitude, inserted.Climate_type,
Photovoltaic.PV_application_id
from inserted inner join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
left join ClimateData on inserted.Latitude = ClimateData.Latitude
and inserted.Longitude = ClimateData.Longitude
and inserted.Altitude = ClimateData.Altitude
and inserted.Climate_type = ClimateData.Climate_type
where ClimateData.Latitude IS NULL /*** Exclude Organization Types already in the table ***/

END -- trigger def

and finally, i hav tried using this command to insert into table v_PhotovoltaicClimateData.. this is the command to insert

quote:insert into v_PhotovoltaicClimateData(PV_site, PV_state, PV_type_of_system, PV_nominal_power,
PV_module, PV_mounting, Latitude, Longitude, Altitude, Climate_type)
values ('CETDEM', 'Sequ Inspection', 'Cheras', 34.44, 'ew', 'ewew',
34343.44, 323.32, 'ewew', 'ewewe')

and i got this error...

quote:Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6
Error converting data type varchar to float.

so anyone... plzz help me...

View 19 Replies View Related

Error Converting Data Type Varchar To Float

Mar 28, 2008

Hello everyone... i have some problem with instead of trigger... after insert values into v_PhotovoltaicClimateData, i got this error, Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6
Error converting data type varchar to float.

below is my DDL for Photovoltaic table

quote:CREATE TABLE [dbo].[Photovoltaic](
[PV_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Photovoltaic_PV_application_id] DEFAULT (newid()),
[PV_site] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_state] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_type_of_system] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_nominal_power] [float] NULL,
[PV_module] [varchar](150) COLLATE Latin1_General_CI_AI NULL,
[PV_mounting] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_building_type] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_topology] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_new_or_retrofit] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_design] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_construction] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_commissioning_date] [datetime] NULL CONSTRAINT [DF_Photovoltaic_PV_commissioning_date] DEFAULT (getdate()),
[PV_site_photo] [varbinary](max) NULL,
[PV_peak_nominal_rating] [float] NULL,
[User_application_id] [uniqueidentifier] NULL,
[Org_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_Photovoltaic_1] PRIMARY KEY CLUSTERED
(
[PV_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Below is my DDL for ClimateData table

quote:CREATE TABLE [dbo].[ClimateData](
[Climate_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClimateData_Climate_application_id] DEFAULT (newid()),
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[Altitude] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[Climate_type] [varchar](100) COLLATE Latin1_General_CI_AI NULL,
[PV_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_ClimateData_1] PRIMARY KEY CLUSTERED
(
[Climate_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

below is my v_PhotovoltaicClimateData

quote:CREATE VIEW [dbo].[v_PhotovoltaicClimateData]
AS
SELECT dbo.Photovoltaic.PV_site, dbo.Photovoltaic.PV_state, dbo.Photovoltaic.PV_type_of_system, dbo.Photovoltaic.PV_nominal_power,
dbo.Photovoltaic.PV_module, dbo.Photovoltaic.PV_mounting, dbo.ClimateData.Latitude, dbo.ClimateData.Longitude, dbo.ClimateData.Altitude,
dbo.ClimateData.Climate_type
FROM dbo.ClimateData INNER JOIN
dbo.Photovoltaic ON dbo.ClimateData.PV_application_id = dbo.Photovoltaic.PV_application_id

below is my instead of trigger command...

quote:CREATE trigger [tr_v_PhotovoltaicClimateData] on [dbo].[v_PhotovoltaicClimateData] instead of insert as
BEGIN

insert Photovoltaic (PV_site, PV_state, PV_type_of_system, PV_nominal_power, PV_module,
PV_mounting)
select distinct inserted.PV_site, inserted.PV_state, inserted.PV_type_of_system,
inserted.PV_nominal_power, inserted.PV_module, inserted.PV_mounting
from inserted left join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
and inserted.PV_state = Photovoltaic.PV_state
and inserted.PV_type_of_system = Photovoltaic.PV_type_of_system
and inserted.PV_nominal_power = Photovoltaic.PV_nominal_power
and inserted.PV_nominal_power = Photovoltaic.PV_module
and inserted.PV_nominal_power = Photovoltaic.PV_mounting
where Photovoltaic.PV_site IS NULL /*** Exclude Photovoltaic already in the table ***/

insert ClimateData (Latitude, Longitude, Altitude, Climate_type, PV_application_id)
select distinct inserted.Latitude, inserted.Longitude, inserted.Altitude, inserted.Climate_type,
Photovoltaic.PV_application_id
from inserted inner join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
left join ClimateData on inserted.Latitude = ClimateData.Latitude
and inserted.Longitude = ClimateData.Longitude
and inserted.Altitude = ClimateData.Altitude
and inserted.Climate_type = ClimateData.Climate_type
where ClimateData.Latitude IS NULL /*** Exclude Climate Data already in the table ***/

END -- trigger def

this is my commad insert values using instead of trigger that i've created...

quote:insert into v_PhotovoltaicClimateData(PV_site, PV_state, PV_type_of_system, PV_nominal_power,
PV_module, PV_mounting, Latitude, Longitude, Altitude, Climate_type)
values ('CETDEM', 'Sequ Inspection', 'Cheras', 34.44 , 'ew', 'ewew',
34343.44, 323.32, 'ewew', 'ewewe')

after execute this commad, i got this error..
quote:Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6
Error converting data type varchar to float.

what should i do??? anyone help me.

View 3 Replies View Related

Error Converting Data Type Varchar To Float

Jul 23, 2005

I have created a stored procedure that contain this field (below) inorder to meet certain criteria. But my problem is when I try to runthe stored procedure I encounter an error "Error converting data typevarchar to float".CASE Final WHEN 0 THEN '--' ELSE Final END AS FinalGradeThe Final field is a float data type.Could anyone teach me how to fix this problem?

View 1 Replies View Related

Transact SQL :: Error Converting Data Type Nvarchar To Float

Sep 24, 2015

In the following code I want to compare 2 values: AccessVal and SQLVal. The values are stored as nvarchars, so I'm isolating the numeric values in a subquery. Notice I'm only selecting 1 row. The commented line, where I compare the values, is throwing the error.

SELECT QA_AutoID, AccessVal, SQLVal
,ROUND(ABS(CONVERT(float, AccessVal,1)),0) as AccessFloat
,ROUND(ABS(CONVERT(float, SQLVal,1)),0) as SQLFloat
FROM QA
WHERE QA_AutoID in (
SELECT TOP 1 QA_AutoID
FROM QA
WHERE ISNUMERIC(SQLVal) = 1 AND ISNUMERIC(AccessVal) = 1
)
--AND ROUND(ABS(CONVERT(float, AccessVal,1)),0) <> ROUND(ABS(CONVERT(float, SQLVal,1)),0)
ORDER BY ROUND(ABS(CONVERT(float, AccessVal,1)),0) DESC
,ROUND(ABS(CONVERT(float, SQLVal,1)),0) DESC

Here is the output with the comparison commented out...

Here's what I get with the comparison line activated:

I've tried converting to numeric, int and bigint instead of float. I've tried CAST instead of CONVERT. Nothing works.

View 13 Replies View Related

Error Converting Data Type Varchar To Int.

Oct 9, 2007

can anyone see as to why I would get this error with the following SP?
ALTER PROCEDURE [dbo].[SP]
@ID int = 0,
@emailFrom VARCHAR(50) = Null,
@emailDate VARCHAR(50) = Null,
@emailSubj VARCHAR(50) = Null,
@emailTxtBody VARCHAR(1000) = Null,
@emailHtmlBody VARCHAR(1000) = Null

AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @Notes VARCHAR (8000)
DECLARE @TicketID INT
DECLARE @emailBody VARCHAR (1000)
DECLARE @Length Int

SET @Notes = ''
SET @Length = LEN(@emailSubj)

-- insert a new entry
INSERT INTO PopEmail
( emailFrom, emailDate, emailSubj, emailTxtBody, emailHtmlBody )
VALUES
( @emailFrom, @emailDate, @emailSubj, @emailTxtBody, @emailHtmlBody )
-- get the new ID
SET @ID = @@identity

If ( @ID <> 0 ) AND ( ISNUMERIC(@emailSubj) = 1 )

Begin
IF @emailTxtBody IS NULL
BEGIN
Set @emailBody = @emailHtmlBody
PRINT '@emailHtmlBody: ' + @emailBody
END

ELSE

BEGIN
SET @emailBody = @emailTxtBody
PRINT '@emailTxtBody: ' + @emailBody
END

SET @TicketID = CAST( @emailSubj AS int )
SET @Notes = @emailFrom + ', ' + @emailDate + ', ' + @emailBody
Select @ID = ID From TicketDetails Where TicketDetails.TicketID = @TicketID

Exec differentSP @ID, @TicketID, @Notes

PRINT 'Subject: [' + @emailSubj + ']'
print 'length: ' + CAST(@Length as varchar (10))
Print 'emailSubj: ' + CAST( @emailSubj AS int )
PRINT 'ID: ' + Cast( @ID as varchar ( 10 ) )
PRINT 'TicketID: ' + Cast( @TicketID AS Varchar ( 10 ) )
PRINT 'Notes: ' + @Notes
PRINT 'ID: ' + Cast( @ID as varchar ( 10 ) )
END
ELSE

BEGIN
Print 'ID: ' + CAST(@ID AS VarChar(10))
PRINT 'ISNUMERIC: ' + CAST(ISNUMERIC(@emailSubj) AS VarChar (10))
PRINT 'Subject: [' + @emailSubj + ']'
END

View 2 Replies View Related

Error Converting Data Type Varchar To Int

Mar 11, 2000

I have a table with a column (locationID) of int data type, I want to pass a list of locationID's to a stored procedure using the IN operator (e.g. WHERE LocationID IN (list of locationID's)). What can I do to achieve this????

My stored procedure looks like this:

CREATE PROCEDURE [sp_test]
@sLocIDs varchar(30)
AS
select count(distinct TimeZoneID) As timezones,
from dimLocation
WHERE LocationID IN (@sLocIDs)

When I test it:
exec sp_test
@sLocIDs = '1,2,10'
I get "Error converting data type varchar to int"

View 1 Replies View Related

Error Converting Data Type Varchar To Datetime.

Feb 16, 2007

Hi,
It is not exactly what I stated in the subject - It's an outcome - exception thrown while executing non-query command.
I get this exception when I try to execute my stored procedure that takes datetime as one of its parameters.
I am using dataset designer to create table adapters and build queries. Then I simply use objectdatasource component that uses one of the table adapters and bind it to for example a detailsview control.
When I run this in debug mode and trace the objects everything looks perfect including these datetime parameters. It is sql server that throws the exception. I ran the sql profiler to see what exactly is going on, and I captured the command that is sent by ADO - it's broken into several lines right in the middle of my datetime parameters... this is the source of the problem. Everything is working fine when I take this command and execute it as a single line in the sql management studio.
Is there anything about ADO that I do not know?

View 2 Replies View Related

Error Converting Data Type Varchar To Bigint.

Aug 25, 2005

I have the following query which has been giving me headaches for days : SELECT SCCode [Service Catalog Code], FileName [File Name], FullName [Full Name], FileExtension [Extension], FileSize [Size], Author [Author], CONVERT(VARCHAR(50),CAST(LastModified AS SMALLDATETIME), 120) [Last Modified], CONVERT(VARCHAR(50),CAST(LastAccessed AS SMALLDATETIME), 120) [Last Accessed], TimesAccessed [Downloads], UploadedBy [Uploaded By], CONVERT(VARCHAR(50),CAST(UploadedAt AS SMALLDATETIME), 120) [Uploaded At] FROM #TempTable WHERE id > @firstitem AND id < @lastitem ORDER BY CASE WHEN @sortcolumn = 'Service Catalog Code' THEN SCCode WHEN @sortcolumn = 'File Name' THEN FileName WHEN @sortcolumn = 'Extension' THEN FileExtension WHEN @sortcolumn = 'Size' THEN FileSize WHEN @sortcolumn = 'Author' THEN Author WHEN @sortcolumn = 'Last Modified' THEN CONVERT(VARCHAR(50),CAST(LastModified AS SMALLDATETIME), 120) WHEN @sortcolumn = 'Last Accessed' THEN CONVERT(VARCHAR(50),CAST(LastAccessed AS SMALLDATETIME), 120) WHEN @sortcolumn = 'Downloads' THEN TimesAccessed WHEN @sortcolumn = 'Uploaded By' THEN UploadedBy WHEN @sortcolumn = 'Uploaded At' THEN CONVERT(VARCHAR(50),CAST(UploadedAt AS SMALLDATETIME), 120) END ASCWhen my @sortcolumn parameter is either FileSize (BIGINT) or TimesAccessed (BIGINT) then the query returns the data without any problem. However, if I use a different @sortcolumn value such as "Author" then I keep getting "Error converting data type varchar to bigint."A solution that seems to work is to drop CASE and use lots of IF statements instead. I don't prefer to do this because it requires me to repeat the same select statement for each and every possible @sortcolumn value!Does anyone know how to solve this? Help!

View 2 Replies View Related

Error Converting Data Type Varchar To Datetime

Dec 4, 2004

hi
i want to pass a datetime variable from a asp page to MSSQL through a stored procedure, & i get this error:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetime.

this is the code that i use in page:

strsql="sp_inventory @timee=' " & RecievedDate & " ' "

also i use this one:
but i still get this error

strsql="sp_inventory @timee=' " & cdate(RecievedDate) & " ' "

this is maybe useful that i get this errot on some PCs, not all the computeres.
sometimes work well, & sth not work.

View 2 Replies View Related

Error Converting Data Type Varchar To Datetime.

Dec 22, 2004

Here is the stored procedure
the data type for the column b_trn_tentdte is decimal(8,0).

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Procedure Prc_New_cus_wo_prev_posted
@b_trn_tendte_Beg_Date datetime
,@b_trn_tendte_End_Date datetime
@b_trn_tendte_Beg_Date1 datetime
,@b_trn_tendte_End_Date2 datetime

as

declare @Beg_tentdte datetime
declare @End_tentdte datetime
declare @Beg_tentdte1 datetime
declare @Beg_tentdte2 datetime

set @Beg_tentdte = convert(varchar(10), @b_trn_tendte_Beg_Date, 112)
set @End_tentdte = convert(varchar(10), @b_trn_tendte_End_Date, 112)
set @Beg_tentdte1 = convert(varchar(10), @b_trn_tendte_Beg_Date1, 112)
set @Beg_tentdte2 = convert(varchar(10), @b_trn_tendte_End_Date2, 112)

/* this query looks for customers with posted
transactions but none prior to the date range
in question */

Select distinct b_cus_cname
from
bar_cus_db_rec
,bar_trn_db_rec b
where b.b_trn_instid = ''
and b.b_trn_instid = b_cus_instid
and b.b_trn_actid = b_cus_cusid
and convert(varchar(10), b.b_trn_tentdte) between @Beg_tentdte and @End_tentdte
and not exists (select c.b_trn_actid
from bar_trn_db_rec c
where c.b_trn_instid = b.b_trn_instid
--and c.b_trn_tentdte between 19970901 and 20030229
and convert(varchar(10), c.b_trn_tentdte) between @Beg_tentdte1 and @Beg_tentdte2
and c.b_trn_actid = b.b_trn_actid)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

every time I run the query, I get this error message:
Error converting data type varchar to datetime.

thanks for your assistance.

View 5 Replies View Related

Error Converting Data Type Varchar To Datetime

Jun 21, 2006

An application i am using creates an error.

Using SQL-profiler i catch this statement:

exec sp_executesql N'insert into RECORDING_SCHEDULE (programme_oid, capture_source_oid, filename, status, recording_group, recording_type, manual_channel_oid,
manual_start_time, manual_end_time, quality_level, pre_pad_minutes, post_pad_minutes) values (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P1 2)',N'@P1 int,@P2 int,@P3
nvarchar(19),@P4 int,@P5 int,@P6 int,@P7 int,@P8 datetime,@P9 datetime,@P10 int,@P11 int,@P12 int',177748,2,N'Lilla r�da traktorn',4,40,5,16,''2006-06-21
17:00:00:000'',''2006-06-21 20:00:00:000'',1,1,2

After removing the double ' around the date and time parts i get the following error when running in QA:
Msg 8114, Level 16, State 5, Line 0
Error converting data type varchar to datetime.

If a remove the time portion it works.

The DDL for the table is:

CREATE TABLE [dbo].[RECORDING_SCHEDULE](
[oid] [int] IDENTITY(1,1) NOT NULL,
[programme_oid] [int] NULL,
[capture_source_oid] [int] NULL,
[status] [smallint] NOT NULL,
[filename] [varchar](255) NULL,
[recording_type] [int] NULL,
[recording_group] [int] NULL,
[manual_start_time] [datetime] NULL,
[manual_end_time] [datetime] NULL,
[manual_channel_oid] [int] NULL,
[quality_level] [int] NULL CONSTRAINT [DF__RECORDING__quali__38996AB5] DEFAULT ((0)),
[pre_pad_minutes] [int] NULL CONSTRAINT [DF__RECORDING__pre_p__398D8EEE] DEFAULT ((0)),
[post_pad_minutes] [int] NULL CONSTRAINT [DF__RECORDING__post___3A81B327] DEFAULT ((0)),
CONSTRAINT [aaaaaRECORDING_SCHEDULE_PK] PRIMARY KEY NONCLUSTERED

What am I missing?

View 2 Replies View Related

Error Converting Data Type Varchar To Numeric

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

Error Converting Data Type Varchar To Numeric

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

Error Converting Data Type Varchar To Bigint

Oct 18, 2013

i have this data:

BookedIDBooked_Date
1141996362013-01-09 14:55:21.740

would like to get the output into txt format with delimited in between columns.

i tried this but getting this error: Error converting data type varchar to bigint.

select BookedID+'|'+Booked_Date from tableA

View 5 Replies View Related

Error Converting Data Type Varchar To Numeric.

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

Error Converting Data Type Varchar To Numeric

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

Error Converting Data Type Varchar To Numeric

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

Error Converting Data Type Varchar To Numeric

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

Error Converting Data Type Varchar To Numeric.

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

Error Converting Data Type Varchar To Numeric.

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

Error Converting Data Type Varchar To Numeric.

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

ERROR CONVERTING DATA TYPE VARCHAR TO NUMERIC?

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

Error Converting Data Type Varchar To Numeric.

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

Error Converting Data Type Varchar To Bigint

Oct 5, 2007

I have designed a SP where i need to update all the records for a table where ErrorId is not equal to the ones provided.In this stored procedure i am parsing and all the errorids delimited by ',' into a varchar variable which i would be using for updating the table.On the second last line i get the error mentioned in the subject line.any help would be appreciated.

ALTER PROCEDURE [dbo].[sp_ParseAndUpdateDetails]

@NozzleID int,

@ParserString varchar(MAX)

AS

BEGIN

DECLARE @NextPos int

DECLARE @LoopCond tinyint

DECLARE @PreviousPos int

DECLARE @FlgFirst bit

DECLARE @QueryCondition varchar(MAX)

SET @LoopCond=1

SET @NextPos =0

SET @FlgFirst=0

SET @QueryCondition=''

WHILE (@LoopCond=1)

BEGIN

--Retrieving the Position of the delimiter

SET @NextPos =@NextPos + 1

SET @NextPos = CHARINDEX(',',@ParserString, @NextPos)

--Retreiving the last substring

IF(@NextPos=0)

BEGIN

PRINT SUBSTRING(@ParserString,@PreviousPos + 1,(LEN(@ParserString)+1)- @PreviousPos)

SET @QueryCondition= @QueryCondition + ' AND ErrorId <> ' + CAST(SUBSTRING(@ParserString,@PreviousPos + 1,(LEN(@ParserString)+1)- @PreviousPos) AS bigint)

SET @PreviousPos = @NextPos

BREAK

END

--Retrieving the individual substrings

If @FlgFirst=0

--Retreiving the first substring

BEGIN

SET @FlgFirst=1

PRINT SUBSTRING(@ParserString,1, @NextPos-1)

SET @QueryCondition= @QueryCondition + CAST(SUBSTRING(@ParserString,1, @NextPos-1) AS bigint)

SET @PreviousPos = @NextPos

END

ELSE

--Retreiving the internmediate substrings

BEGIN

PRINT SUBSTRING(@ParserString,@PreviousPos + 1,(@NextPos-1)-@PreviousPos)

SET @QueryCondition= @QueryCondition + ' AND ErrorId <> ' + CAST(SUBSTRING(@ParserString,@PreviousPos + 1,(@NextPos-1)-@PreviousPos) AS bigint)

SET @PreviousPos = @NextPos

END

END

print 'ErrorId <>' + @QueryCondition

UPDATE [ESMS2_DBMS].[dbo].[ErrorDetails]

SET ErrorRectifyDateTime=GETDATE()

WHERE (NozzleId = @NozzleId) AND (ErrorRectifyDateTime IS NULL) AND (ErrorId <> @QueryCondition)

END

View 8 Replies View Related

Error Converting Data Type Varchar To Numeric

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

'Error Converting Data Type Varchar To Int' : Solution

Jul 3, 2007

Here is the solution to this error message if anyone gets one in the future...It took me about two days to figure out.

This error occurs if you save your stored procedure before execute it. Always execute first before you save. If you save before executing, the server will store its own defaults, usually integers.

This error aslo occurs if your datatypes do not match when passing values from code into the variables of a stored procedure.

It also occurs if the datatypes in your sql file do not match those of the original stored procedure.

You can check to see if the file saved in the "Projects" folder matches with the original by doing the following:

Expand Database, Expand Programmability, Expand Stored Procedures, Expand (Your Stored Procedure), Expand Parameters. Read the datatypes that are revealed in the tree.

Then go to File>Open>Projects>(save sql file). Click open.

View the datatypes in the file. If the datatypes in the file do not match the datatypes in the tree, what you must do to correct, as one solution, is delete both the file and stored procedure.

Then create a new stored procedure by right clicking the stored procedure node. Rewrite the store procedure, execute and then save. Everything should be okay.

View 3 Replies View Related

SQL2K - Converting Data Type Varchar To Numeric Error

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

Syntax Error Converting The Varchar Value '*' To A Column Of Data Type Int.

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

Syntax Error Converting The Varchar Value '10.136.20.20' To A Column Of Data Type Int.

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

Sp_executesql - Error Converting Data Type Varchar To Datetime

Oct 13, 2006

Hi,

I am havin problems with the following giving a message

Msg 8114, Level 16, State 4, Line 0

Error converting data type varchar to datetime.

exec sp_executesql

@stmt=N'UPDATE CUSTOMER

SET [REQUEST] = @19, [DISC_EXPRY] = @28, [GROUP1] = @29, [GROUP2] = @31, [PR_LEVEL] = @48, [MOD_DATE] = @55, [MEM_CODE] = @63, [MEM_DATE] = @64, [HO_MOD] = @66, [LASTUPDATE] = @78

WHERE [ID] = @Old_1'

, @params=N'@19 nvarchar(4),@28 DateTime,@29 nvarchar(5),@31 nvarchar(5),@48 nvarchar(1),@55 DateTime,@63 nvarchar(7),@64 DateTime,@66 Integer,@78 Float,@Old_1 Integer',

@19= 'NRMA',@28= '1752-09-14',@29= 'ALBUM',@31= 'FRAME',@48= 'A',@55= '2006-10-10',@63= '1003.50',@64= '2006-10-10',@66= 3,@78= 39000.190633,@Old_1= 454636

however, as soon as I remove the datetime fields it works

exec sp_executesql

@stmt=N'UPDATE CUSTOMER

SET [REQUEST] = @19, [GROUP1] = @29, [GROUP2] = @31, [PR_LEVEL] = @48, [MEM_CODE] = @63, [HO_MOD] = @66, [LASTUPDATE] = @78

WHERE [ID] = @Old_1'

, @params=N'@19 nvarchar(4),@29 nvarchar(5),@31 nvarchar(5),@48 nvarchar(1),@63 nvarchar(7),@66 Integer,@78 Float,@Old_1 Integer',

@19= 'NRMA',@29= 'ALBUM',@31= 'FRAME',@48= 'A',@63= '1003.50',@66= 3,@78= 39000.190633,@Old_1= 454636

what am I doing wrong with the datetime parameters?

Grimhael

View 4 Replies View Related

Transact SQL :: ERROR Converting Data Type Varchar To Numeric

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

Syntax Error Converting The Varchar Value To A Column Of Data Type Int

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