string or binary data would be truncated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

string or binary data would be truncated

Author

Topic

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2012-04-17 : 03:50:18

Hi All,

i have an issue while inserting the Vendorname.
in my table i have specified the length as varchar(100) & in Stored procedure also i have specified varchar(100). but when i am inserting exactly 100 characters it is throwing error that.
String or binary data would be truncated..
can any one help what is the reason for this.

Thanks in Advance,
Kiran Murali

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-17 : 04:34:58

So you have
- a destination column in your table varchar(100)
- a variable or what in your stored procedure which is varchar(100)?

How do you insert exactly 100 characters?

We can't see anything from here, no code, table definition - nothing. So it is hard to help.

No, you're never too old to Yak'n'Roll if you're too young to die.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-04-17 : 04:36:43

Check once again your data length. It may problem.

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-04-17 : 05:42:19

or there may be an implicit conversion in your code you aren't aware of.

Post the code and the sample data.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2012-04-17 : 07:24:29

this is my store procedure

ALTER PROCEDURE [dbo].[SP_RECRUITMENT_MANAGEVENDOR_ADD]

-- The below are the input arguments for calling the procedure
@SOURCING INT,
@VENDORNAME VARCHAR(100),
@SHORTNAME VARCHAR(5),
@WEBSITE_ADDRESS VARCHAR(100),
@CONTACTPERSONNAME VARCHAR(50),
@CONTACT_PERSONEMAIL VARCHAR(100),
@CONTACT_PERSONMOBILE VARCHAR(20),
@CONTACT_PERSONPHONE VARCHAR(20),
@VENDOR_ADDRESS VARCHAR(250),
@DESCRIPTION VARCHAR(250),
@PASSWORD VARCHAR(100)

AS

-- The data obtained as input arguments is inserted into PNET_RECRUITMENT_MASTER_VENDOR table
INSERT INTO PNET_RECRUITMENT_MASTER_VENDOR
(
SOURCING,
VENDORNAME,
SHORTNAME,
WEBSITE_ADDRESS,
CONTACT_PERSONNAME,
CONTACT_PERSONEMAIL,
CONTACT_PERSONMOBILE,
CONTACT_PERSONPHONE,
VENDOR_ADDRESS,
DESCRIPTION
)

VALUES
(
@SOURCING,
@VENDORNAME,
@SHORTNAME,
@WEBSITE_ADDRESS,
@CONTACTPERSONNAME,
@CONTACT_PERSONEMAIL,
@CONTACT_PERSONMOBILE,
@CONTACT_PERSONPHONE,
@VENDOR_ADDRESS,
@DESCRIPTION
);

DECLARE @VENDORID INT
SELECT @VENDORID= VENDORID FROM PNET_RECRUITMENT_MASTER_VENDOR WHERE VENDORNAME=@VENDORNAME AND SOURCING=@SOURCING
AND WEBSITE_ADDRESS=@WEBSITE_ADDRESS AND DEL_FLAG=0
UPDATE PNET_RECRUITMENT_MASTER_VENDOR
SET
USER_NAME=@VENDORNAME+cast(@VENDORID as varchar(10)),
PASSWORD=@PASSWORD
WHERE
VENDORID=@VENDORID
AND DEL_FLAG=0

and the table description is as below:

CREATE TABLE [dbo].[PNET_RECRUITMENT_MASTER_VENDOR](
[VENDORID] [int] IDENTITY(1,1) NOT NULL,
[VENDORNAME] [varchar](100) NOT NULL,
[SHORTNAME] [varchar](5) NOT NULL,
[WEBSITE_ADDRESS] [varchar](100) NOT NULL,
[CONTACT_PERSONNAME] [varchar](50) NOT NULL,
[CONTACT_PERSONEMAIL] [varchar](100) NOT NULL,
[CONTACT_PERSONMOBILE] [varchar](20) NOT NULL,
[CONTACT_PERSONPHONE] [varchar](20) NOT NULL,
[DESCRIPTION] [varchar](250) NOT NULL,
[DEL_FLAG] [bit] NOT NULL,
[DEL_TIME] [datetime] NULL,
[DEL_USERID] [varchar](256) NULL,
[DEL_IP_ADDRESS] [varchar](20) NULL,
[SOURCING] [int] NULL,
[VENDOR_ADDRESS] [varchar](250) NULL,
[USER_NAME] [varchar](100) NULL,
[PASSWORD] [varchar](100) NULL,
PRIMARY KEY CLUSTERED
(
[VENDORID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[SHORTNAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[WEBSITE_ADDRESS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[VENDORNAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PNET_RECRUITMENT_MASTER_VENDOR] ADD CONSTRAINT [DF_PNET_RECRUITMENT_MASTER_VENDOR_DEL_FLAG] DEFAULT ((0)) FOR [DEL_FLAG]
GO

regards,
Kiran Murali

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-04-17 : 07:26:15

SET
USER_NAME=@VENDORNAME+cast(@VENDORID as varchar(10)),

gives 100+... if you have a VENDORNAME with already 100 chars

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources