April 6, 2007 at 12:01 pm
Hi,
i need help on this,
when i run the following i get the following error
Msg 8115, Level 16, State 2, Line 32
Arithmetic overflow error converting expression to data type nvarchar.
DECLARE
varchar,
@test2
varchar,
@test3
varchar,
@test4
varchar,
@test5
varchar,
@test6
char,
@test7
char,
@test8
char,
@test9
nvarchar,
@test10
datetime,
@test11
datetime,
@test12
datetime,
@test13
nvarchar
DECLARE
Header$_Cursor CURSOR FOR
SELECT
[District Code],
[District Name]
,
[School Code]
,
[School Name]
,
[Project #]
,
[Architect]
,
[General Contractor]
,
[Project Manager]
,
[MSBA Audit Firm]
,
[Project Start Date]
,
[Project End Date]
,
[Project Eligibility Cutoff Date]
,
[ Grant Rate ]
FROM
[Header$];
OPEN
Header$_Cursor;
FETCH
NEXT FROM Header$_Cursor
INTO
,
@test2
,
@test3
,
@test4
,
@test5
,
@test6
,
@test7
,
@test8
,
@test9
,
@test10
,
@test11
,
@test12
,
@test13
;
WHILE
@@FETCH_STATUS = 0
BEGIN
UPDATE Audit_Project_Header
SET LEA_Code =@test1,
District_Name
=@test2,
School_Code
=@test3,
School_Name
=@test4,
Architect
=@test6,
General_Contractor
= @test7,
Project_Manager
= @test8,
MSBA_Audit_Firm_Name
= @test9,
Project_Start_Date
= @test10,
Project_End_Date
= @test11,
Project_Eligibility_Cutoff_Date
=@test12,
Grant_Rate
=@test13
WHERE MSBA_ID=@test5
IF (@@ROWCOUNT=0) AND (@@ERROR=0)
INSERT INTO Audit_Project_Header
(LEA_Code,
District_Name
,
School_Code
,
School_Name
,
MSBA_ID
,
Architect
,
General_Contractor
,
Project_Manager
,
MSBA_Audit_Firm_Name
,
Project_Start_Date
,
Project_End_Date
,
Project_Eligibility_Cutoff_Date
,
Grant_Rate
)
VALUES(@test1,
@test2
,
@test3
,
@test4
,
@test6
,
@test7
,
@test8
,
@test9
,
@test10
,
@test11
,
@test12
,
@test13
,
@test5
)
FETCH NEXT FROM Header$_Cursor
INTO @test1,
@test2
,
@test3
,
@test4
,
@test6
,
@test7
,
@test8
,
@test9
,
@test10
,
@test11
,
@test12
,
@test13
,
@test5
;
END;
CLOSE
Header$_Cursor;
DEALLOCATE
Header$_Cursor;
following is my table structure
CREATE TABLE [dbo].[Audit_Project_Header](
[MSBA_ID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LEA_Code] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[District_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[School_Code] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[School_Name] [varchar](55) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Architect] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[General_Contractor] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Project_Manager] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MSBA_Audit_Firm_Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Project_Start_Date] [datetime] NULL,
[Project_End_Date] [datetime] NULL,
[Project_Eligibility_Cutoff_Date] [datetime] NULL,
[Grant_Rate] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--------------------------------------------------------------
CREATE TABLE [dbo].[Header$](
[District Code] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[District Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[School Code] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[School Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Project #] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Architect] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[General Contractor] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Project Manager] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MSBA Audit Firm] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Project Start Date] [datetime] NULL,
[Project End Date] [datetime] NULL,
[Project Eligibility Cutoff Date] [datetime] NULL,
[ Grant Rate ] [float] NULL
) ON [PRIMARY]
April 6, 2007 at 12:21 pm
The column [GRANT RATE] has a different data type in both tables in one it is a float in the other it is a nvarchar.
Anyway it will be the conversion of the float to an nvarchar that is causing the overflow as you have decalre @test13 as nvarchar and it will be this that is overflowing.
e.g. try this it will fail with your error
declare
@float float, @test-2 as nvarchar
set
@float = 1.23
select
select
Anyway I would think that this should be the same datatype in both tables and since float are imprecise you may want to make it a decimal.
hth
David
April 6, 2007 at 12:23 pm
Best,
This looks to be the UPSERT that you've been working on. Is there a reason why you've chosen a cursor to do this when so many have warned against it? There is no reason to use a cursor here and depending on how many rows you have in your table, a cursor will be significanly slower than the set based solutions that you've been given here:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=356263
With that said, the overflow error looks to be comming from your variable type mis-matches between the values in your header table and the declared values of your variables. I would suggest scrapping this whole approach and writing this in a set based operation as has been explained in the other few posts on this topic.
April 6, 2007 at 12:29 pm
Here's your set-based UPSERT (you'll need to add in whatever error processing methods you've standardized or you are used to):
UPDATE APH
SET APH.LEA_Code =H.[District Code],
APH.District_Name =H.[District Name],
APH.School_Code =H.[School Code],
APH.School_Name =H.[School Name],
APH.Architect =H.[Architect],
APH.General_Contractor = H.[General Contractor],
APH.Project_Manager = H.[Project Manager],
APH.MSBA_Audit_Firm_Name = H.[MSBA Audit Firm],
APH.Project_Start_Date = H.[Project Start Date],
APH.Project_End_Date = H.[Project End Date],
APH.Project_Eligibility_Cutoff_Date= H.[Project Eligibility Cutoff Date],
APH.Grant_Rate = H.[ Grant Rate ]
FROM Audit_Project_Header APH
INNER JOIN [Header$] H
ON APH.MSBA_ID = H.[Project #]
INSERT INTO Audit_Project_Header (
LEA_Code,
District_Name,
School_Code,
School_Name,
MSBA_ID,
Architect,
General_Contractor,
Project_Manager,
MSBA_Audit_Firm_Name,
Project_Start_Date,
Project_End_Date,
Project_Eligibility_Cutoff_Date,
Grant_Rate)
SELECT H.[District Code],
H.[District Name],
H.[School Code],
H.[School Name],
H.[Project #],
H.[Architect],
H.[General Contractor],
H.[Project Manager],
H.[MSBA Audit Firm],
H.[Project Start Date],
H.[Project End Date],
H.[Project Eligibility Cutoff Date],
H.[ Grant Rate ]
FROM [Header$] H
LEFT JOIN Audit_Project_Header APH
ON APH.MSBA_ID = H.[Project #]
WHERE APH.MSBA_ID IS NULL
P.S. - you'll need to test this as I did not!
April 6, 2007 at 1:07 pm
Hi ,
i get the following error when i run the above query
(0 row(s) affected)
Msg 8152, Level 16, State 2, Line 18
String or binary data would be truncated.
The statement has been terminated.
April 6, 2007 at 1:16 pm
In looking at the data types between like columns in your Header and Audit tables, there are many differences where trying to insert Header table values into your Audit table columns could cause this error. If you are not bound to the schema on your Audit table, I would suggest changing the column data types to match the Header columns (that is, if the Audit table was created specifically to contain an Audit of your History data). Otherwise, you'll need to either CAST each of the values in your UPDATE and INSERT statements or do a SUBSTRING on them to only pull the length that your Audit columns can handle.
April 6, 2007 at 1:24 pm
i cannot change the datatypes.can you show me how to use CAST for one of the values for an update and insert statement in my case or a Substring so i can get an idea of it.
Thanks for your help
April 6, 2007 at 1:49 pm
BOL
Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])
-- Cory
April 6, 2007 at 1:54 pm
i know the syntax ,what iam trying to ask here is how will i use in my update/insert statement?can you show me for one of the values.
Thanks
April 6, 2007 at 2:42 pm
For each History column in the UPDATE and INSERT, you'll need to encapsulate the column name with the CAST function and modify the casted datatype to match the datatype for the corresponding column in the Audit table. Anyone else have a better idea?
example:
CAST(H.[District Name] as varchar(50))
April 6, 2007 at 2:55 pm
My suggestion is that we start asking Best to show us what he has done before doing his work for him. Perhaps then, we can help his to start helping himself rather than relying on us for everything.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply