Insertion Problem
-
Hi I have an SP like following..... I am getting an error like 'String or binary data would be truncated' when am trying to insert a value in the table.
I am not getting an error if enter less characters like small words, but it errors like if enter many characters .. but am sure am not entering more than 40 characters like i defined in procedure.
please help me solving this issue.
ALTER PROCEDURE [dbo].[sp_CUSTOM_SaveOtherMajor]
@MajorCode as int = NULL,
@EducationCodeDesc as nvarchar(40)
AS
DECLARE @DUPE as int
DECLARE @INC as int
DECLARE @CODE as int
BEGIN
SET NOCOUNT ON;
INSERT INTO tEDUCATION_CODES (
EDucationCodedomainIdno,
EducationCode,
EducationCodeType,
EducationCodeDescription,
Educationcodefromeffectdate,
Educationcodetoeffectdate,
Languageid,
EducationcodeDefaultCodeind)
VALUES(
--@INC,
'1440',
@EducationCodeDesc,
'J',
@EducationCodeDesc,
'1900-01-02',
'3000-01-01',
'en-us',
'1')
END
SELECT @@IDENTITY AS EducationCodeidno
UPDATE tEDUCATION_CODES set EDucationCodedomainIdno = @@IDENTITY where Educationcodeidno = @@IDENTITY
-
anjana.maganti (4/23/2009)
Hi I have an SP like following..... I am getting an error like 'String or binary data would be truncated' when am trying to insert a value in the table.I am not getting an error if enter less characters like small words, but it errors like if enter many characters .. but am sure am not entering more than 40 characters like i defined in procedure.
please help me solving this issue.
ALTER PROCEDURE [dbo].[sp_CUSTOM_SaveOtherMajor]
@MajorCode as int = NULL,
@EducationCodeDesc as nvarchar(40)
AS
DECLARE @DUPE as int
DECLARE @INC as int
DECLARE @CODE as int
BEGIN
SET NOCOUNT ON;
INSERT INTO tEDUCATION_CODES (
EDucationCodedomainIdno,
EducationCode,
EducationCodeType,
EducationCodeDescription,
Educationcodefromeffectdate,
Educationcodetoeffectdate,
Languageid,
EducationcodeDefaultCodeind)
VALUES(
--@INC,
'1440',
@EducationCodeDesc,
'J',
@EducationCodeDesc,
'1900-01-02',
'3000-01-01',
'en-us',
'1')
END
SELECT @@IDENTITY AS EducationCodeidno
UPDATE tEDUCATION_CODES set EDucationCodedomainIdno = @@IDENTITY where Educationcodeidno = @@IDENTITY
I indicated above where you are using that variable twice in your insert statement. I bet that one of those columns is less than 40 characters.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
That error comes from the table, not the proc directly. Check field sizes in the table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
On another note.
You should not use @@IDENTITY, use SCOPE_IDENTITY instead.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
Cool.. I never found that.. Thank You so much ..
Could you please help how to get First part of the (or 15 char length) from the @EducationCodeDesc paramenter in the procedure itself?
i mena like using any fuction..
Anyways you are great help for me today.. i appriciate it..
-
anjana.maganti (4/23/2009)
Could you please help how to get First part of the (or 15 char length) from the @EducationCodeDesc paramenter in the procedure itself?i mena like using any fuction..
Anyways you are great help for me today.. i appriciate it..
It sounds like the LEFT function would do what you want. Check it out in BOL.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Hi, Thanks again.
I have used sql substring function.. and it worked 🙂
-
Hi Again,
i have made changes and deployed it to live server ad now i received a error like...
'A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'groupo', table 'tEDUCATION_CODES', column 'EducationCode'. The statement has been terminated.
Date & Time: 4/23/2009 5:01:27 PM'
And my SP is the following one ... Please help me to solve this.
ALTER PROCEDURE [dbo].[sp_CUSTOM_SaveOtherMajor]
@MajorCode as int = NULL,
@EducationCodeDesc as nvarchar(40)
AS
DECLARE @SUBSTR as nvarchar(40)
DECLARE @CODE as int
BEGIN
SET NOCOUNT ON;
SET @SUBSTR = substring(@EducationCodeDesc,1,15)
INSERT INTO tEDUCATION_CODES (
EDucationCodedomainIdno,
EducationCode,
EducationCodeType,
EducationCodeDescription,
Educationcodefromeffectdate,
Educationcodetoeffectdate,
Languageid,
EducationcodeDefaultCodeind)
VALUES(
--@INC,
'1440',
@SUBSTR,
'J',
@EducationCodeDesc,
'1900-01-02',
'3000-01-01',
'en-us',
'1')
END
SET @CODE = SCOPE_IDENTITY()
SELECT @CODE AS EducationCodeidno
UPDATE tEDUCATION_CODES set EDucationCodedomainIdno = EducationCodeidno where Educationcodeidno = EducationCodeidno
-
anjana.maganti (4/23/2009)
Hi Again,i have made changes and deployed it to live server ad now i received a error like...
'A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'groupo', table 'tEDUCATION_CODES', column 'EducationCode'. The statement has been terminated.
Date & Time: 4/23/2009 5:01:27 PM'
Your tEDUCATION_CODES table has a rule on the EducationCode column that is prohibiting the data from being imported. You need to either change the rule, cleanse the data before loading, or filter the data to prevent the row(s) with bad data from being loaded.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
HI,
I do not have any access to those tables at all. I can modify them.
I think it is not happening all the time. But sometimes i get this.
Is there any way to get rid of that error in future?
Please let me know.
-
Oops i meant i can not modify them !!!!
-
Oops i meant i can not modify them !!!
-
Run this and post what you got
SELECT SC.name, SC.xtype, SC.length, SC.domain , C.text
FROM syscolumns SC
INNER JOIN sysobjects SO ON SC.id = SO.ID
AND SO.name = 'tEDUCATION_CODES'
LEFT OUTER JOIN syscomments C ON C.id = SC.domain
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
-
name xtype length domain text
EducationCodeIdNo5640NULL
EducationCodeDomainIdNo5640NULL
EducationCode239301313439753CREATE RULE rDenyEmptyString AS DATALENGTH(RTRIM(@field)) > 0
EducationCodeType17511313439753CREATE RULE rDenyEmptyString AS DATALENGTH(RTRIM(@field)) > 0
EducationCodeDescription231800NULL
EducationCodeFromEffectDate6180NULL
EducationCodeToEffectDate6180NULL
LanguageId17550NULL
EducationCodeDefaultCodeInd10410NULL
Timestamp18980NULL
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply