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!
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)
The strange in this problem is that i got this problem suddenly and before all was running without any error.
The script that cause the error is in the trigger for insertion on a table, there is somewhere where i convert from varchar to bigint, but all seems fine to me i printed the varchar variable and the conversion looks valid, also i included several print statement in the script for debugging purposes and i feel everything is right and there shouldn't be any error.
I don't think anything changed in my db, so i wonder where this problem is comming from ? this is the code:
declare @posSpace int set @posSpace = charindex(' ', @tmpmsg)
if @posSpace = 0 -- Not found (the message format is like this 'pri 3432434 ' or 'pri dfsdffsd ' begin set @msg_error = @msg_error1 -- Insert the message into the filtration screen to be fixed by a filtrator, the recipient_id will be set to 0 insert into mobile_chat (sender_id, recipient_id, message, datein, timein) Values (@sender_id, 0, @tmpmsg, @thedate, @thetime) end else begin set @strprofileid = left(@tmpmsg, @posSpace-1) -- first char separating profileid and util message if isnumeric(@strprofileid) = 1 begin set @profileid = @strprofileid declare @utilmsg nvarchar(200) set @utilmsg = ltrim(right(@tmpmsg, len(@tmpmsg)-@posSpace))
-- Right message format print 'Profile id=' + ltrim(str(@profileid)) print 'Message=' + @utilmsg declare @recmsisdn varchar(16) select @recmsisdn = msisdn From profiles where profile_id=@profileid -- if the receiver profile doens't exist, send him a message indicating that if @recmsisdn is not null begin -- All fine: msg format, sender and receiver if left(@recmsisdn,3)='000' Insert into Inbox (sender_id, recipient_id, message, datein, timein) Values (@sender_id, @profileid, @utilmsg, @thedate, @thetime) else insert into mobile_chat (sender_id, recipient_id, message, datein, timein) Values (@sender_id, @profileid, @utilmsg, @thedate, @thetime) end else begin -- Insert the message into the filtration screen to be fixed by a filtrator, the recipient_id will be set to 0 insert into mobile_chat (sender_id, recipient_id, message, datein, timein) Values (@sender_id, 0, @tmpmsg, @thedate, @thetime) set @msg_error = @msg_error3 end end else begin set @msg_error = @msg_error1 end end end else -- The sender profile is not registered with us set @msg_error = @msg_error2 if @msg_error is not null begin print @msg_error if left(@msisdn, 3) = '966' -- We can only send replies to KSA mobiles INSERT INTO cgi_sms_services.dbo.cgi_sms_in(msisdn, sms_code, message, reqport, smstype) VALUES (@msisdn, '1000000002', @msg_error, 86367, 'wcsms') end end -- Private Sending End
I getting the above error can someone please help me solve it, here is the code: public void InsertHost() { // TODO // - Call stored procedure to write to a log file writeToLog using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"])) { SqlCommand cmd = new SqlCommand("writeToLog", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@pAction", "action"); cmd.Parameters.AddWithValue("@pHostName", "Hostname"); cmd.Parameters.AddWithValue("@pUserNUm", "requestorID"); cn.Open(); cmd.ExecuteNonQuery(); } } Here is the storedprocedure: ALTER PROCEDURE dbo.writeToLog(@pAction varchar(10), @pUserNUm bigint, @pHostName varchar(25))AS INSERT INTO dbo.hostNameLog (action, requestorID, HostName)VALUES (@pAction, @pUserNUm, @pHostName) Here is the table: HostName - varchar, action - varchar, requestorID - bigint I can't seem to find the error.
I am attempting to setup a replication from SQL Server 2005 that will be read by SQL Server Compact Edition (beta). I'm having trouble getting the Publication Wizard to create the Publication. Sample table definition that I'm replicating:
USE dbPSMAssist_Development; CREATE TABLE corporations ( id NUMERIC(19,0) IDENTITY(1964,1) NOT NULL PRIMARY KEY, idWas NUMERIC(19,0) DEFAULT 0, logIsActive BIT DEFAULT 1, vchNmCorp VARCHAR(75) NOT NULL,
vchStrtAddr1 VARCHAR(60) NOT NULL, vchNmCity VARCHAR(50) NOT NULL, vchNmState VARCHAR(2) NOT NULL, vchPostalCode VARCHAR(10) NOT NULL, vchPhnPrimary VARCHAR(16) NOT NULL, ); CREATE INDEX ix_corporations_nm ON corporations(vchNmCorp, id); GO
When the wizard gets to the step where it is creating the publication, I get the following error message:
Arithmetic overflow error converting expression to data type bigint. Changed database context to 'dbPSMAssist_Development'. (Microsoft SQL Server, Error: 8115).
I can find no information on what this error is or why I am receiving the error. Any ideas on how to fix would be appreciated.
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
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"
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?
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.
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.
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
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
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..
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,
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')
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
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
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....
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
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.
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...
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.
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?
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.
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.
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!
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.
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
