Click here to Skip to main content
14,329,910 members
Rate this:
Please Sign up or sign in to vote.
See more:
Today i am having a wearied issue.

I have updated a column datatype varchar(2000) to nvarchar(MAX)

like this ..

ALTER TABLE catalog_seo
ALTER COLUMN vc_bottom_content nvarchar(MAX) not null



Before that this column was restricted for 2000 char. The issue i am getting that still data is not being saved for greater then 2000 char.

I mean if i try to insert 2500 character it silently remove last 500 char.

Please help me on this ..
Thnxx in advance.
Posted
Updated 26-Mar-14 9:38am
v2
Comments
Sergey Alexandrovich Kryukov 26-Mar-14 15:46pm
   
It means that MAX is equal to 2000. :-)
—SA
PIEBALDconsult 26-Mar-14 15:50pm
   
No it isn't.
Sergey Alexandrovich Kryukov 26-Mar-14 15:58pm
   
Then what is that? It behaves like that... :-)
—SA
k@ran 26-Mar-14 15:51pm
   
but as i can see in documentation

Maximum storage size of nvarchar(MAX) : 2^31-1 bytes (2 GB)

PIEBALDconsult 26-Mar-14 15:51pm
   
How does the data get into the table?
k@ran 26-Mar-14 15:52pm
   
It removes last exceeded character.
PIEBALDconsult 26-Mar-14 15:54pm
   
I understand that part, but what process/code causes data to be inserted?
k@ran 26-Mar-14 16:00pm
   
This is the code in procedure..

IF (EXISTS (SELECT 1 FROM dbo.catalog_seo WHERE in_catalog_id = @in_catalog_id AND in_website_id = @in_website_id))

BEGIN

IF NOT EXISTS (SELECT 1 FROM dbo.catalog_seo WHERE in_catalog_id = @in_catalog_id AND in_website_id = @in_website_id AND vc_page_title = @vc_page_title AND

vc_meta_keywords = @vc_meta_keywords AND vc_meta_description = @vc_meta_description AND vc_top_content = @vc_top_content AND vc_bottom_content = @vc_bottom_content AND

vc_short_brand_description = @vc_short_brand_description AND vc_availability_desc = @vc_availability_desc AND bt_active = @bt_active)

BEGIN

UPDATE dbo.catalog_seo SET vc_page_title = @vc_page_title, vc_meta_keywords = @vc_meta_keywords,

vc_meta_description = @vc_meta_description, vc_top_content = @vc_top_content, vc_bottom_content = @vc_bottom_content,

vc_short_brand_description = @vc_short_brand_description, vc_availability_desc = @vc_availability_desc, dt_updated = GETDATE(),

in_updatedby_user_id = @in_updatedby_user_id, bt_active = @bt_active

WHERE in_catalog_id = @in_catalog_id AND in_website_id = @in_website_id

END

END


.......
When i tries to execute using execute command manually again it inserts only 2000 char.


EXECUTE dbo.catalog_seo_sp_update @in_type = 1, @in_catalog_id = 371, @in_website_id = 1 , @vc_page_title = '', @vc_meta_keywords = '', @vc_meta_description = 'Altair eyeglasses* superior design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fra',
@vc_top_content = '',
@vc_bottom_content = 'Mrinal test
mrinal teat jjjgdfgdfgdfjghnkj h
ghfg
fghd
hgf
hgff
ghgfh
ghf
gh
Altair eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair eyeglassesAltair
mrinal
Altair eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair
eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair eyeglassesAltair
Eyeglasses Eyewear superior design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form the foundation
for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair
eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form
the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio
of fraAltair eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair eyeglassesAltair Eyeglasses Eyewear superior design and quality
craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair
offers a diverse portfolio of fraAltair
PIEBALDconsult 26-Mar-14 16:07pm
   
OK, then, what populates @vc_bottom_content ? What is the definition of the parameter for the procedure?
k@ran 26-Mar-14 16:23pm
   
I found the cause of this issue

Actually parameter was set to 2000 as eirler. I forgot to change this.
@vc_bottom_content varchar(2000) = '',

I changed it to @vc_bottom_content nvarchar(MAX) = ''

It worked..

Thanks PIEBALDconsult
k@ran 26-Mar-14 16:10pm
   
one thing i would like to share that when i udate table using update command it update properly..
UPDATE dbo.catalog_seo set vc_bottom_content = 'Mrinal test
mrinal teat jjjgdfgdfgdfjghnkj h
ghfg
fghd
hgf
hgff
ghgfh
ghf
gh
Altair eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair eyeglassesAltair
mrinal
Altair eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair
eor design and quality craftmanship form the foundation for every Altair eyeglasse
Altair eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair
eor design and quality craftmanship form the foundation for every Altair eyeglasse
Altair eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair
eor design and quality craftmanship form the foundation for every Altair eyeglasse
Altair eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair
eor design and quality craftmanship form the foundation for every Altair eyeglasses frame. Altair offers a diverse portfolio of fraAltair eyeglassesAltair Eyeglasses Eyewear superior design and quality
craftmanship form the foundation for every Altair eyeglasses
eor design and quality craftmanship form the foundation for every Altair eyeglasse
Altair eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanshi
frame. Altair offers a diverse portfolio of fraAltair eyeglassesAltair Eyeglasses Eyewear superior design and quality craftmanship form the foundation for every Altair eyeglasses
frame. Altair
offers a diverse portfolio of fraAltair eyeglassesAltairvvvvvaaaaaaamm hghh hhgf*'
where in_catalog_id = 371 and in_website_id = 1

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

I found the cause of this issue Actually parameter of procedure was set to 2000 as eirler.
I forgot to change this. @vc_bottom_content varchar(2000) = '',


I changed it to @vc_bottom_content nvarchar(MAX) = ''

It worked..


Thanks PIEBALDconsult
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100