VARCHAR (MAX) - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

VARCHAR (MAX)

Author

Topic

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-08 : 10:22:36

Hi, I created a table in sql server 2005 with a varchar(max) field.

CREATE TABLE [dbo].[CPDPLP06](
[REQUEST_DATE] [datetime] NOT NULL DEFAULT (getdate()),
[CODER_ID] [char](4) NULL,
[LNB_RECORD] [varchar](max) NULL
) ON [PRIMARY]

This table gets updated from a NETCobol code from where I'm passing 13326 chars of data to LNB_RECORD. But it fails in the insert saying truncation occurred. Can it not handle 13326 chars of data?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-08 : 10:29:19

Vijay, are you sure the right field is being filled with the right data. Show us the INSERT statement. Your problem could be something else...liek the code itself :) hope it is not those crazy joins :)

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-08 : 11:03:36

Hey buddy..its a COBOL code that does the insert.

EXEC SQL
SELECT CONVERT(VARCHAR(MAX),:TBL-LNB-RECORD)
INTO :TBL-VARCHAR-LNB
END-EXEC

EXEC SQL
INSERT INTO "CPDPLP06"
(
CODER_ID,
LNB_RECORD
)
VALUES (
:TBL-CODER-ID,
:TBL-VARCHAR-LNB
)
END-EXEC

Its a fairly simple code...but I guess its not just converting into the varchar(max) field.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-08 : 11:16:33

what is the length of TBL-LNB-RECORD, maybe it has leading and trailing space that you need to trim?
I tested it as follows and it works

CREATE TABLE [dbo].[CPDPLP06Test](
[REQUEST_DATE] [datetime] NOT NULL DEFAULT (getdate()),
[CODER_ID] [char](4) NULL,
[LNB_RECORD] [varchar](max) NULL
) ON [PRIMARY]

DECLARE @LNB_RECORD VARCHAR(MAX)
SET @LNB_RECORD = ''
WHILE LEN(@LNB_RECORD) < 13326
BEGIN
SET @LNB_RECORD = @LNB_RECORD + 'a'
END

INSERT INTO dbo.CPDPLP06Test (
REQUEST_DATE,
CODER_ID,
LNB_RECORD
) VALUES ( GETDATE(), '1234', @LNB_RECORD)

SELECT LEN(LNB_RECORD), * FROM dbo.CPDPLP06Test

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources