August 28, 2018 at 8:59 am
I am trying to insert into table the approximately 74 thousand characters but Text datatype allows me to insert only 44 thousand characters. The Text data type can store up to 2,147,483,647 bytes. Any idea why SQL Server cut my text data? I would appreciate any advise how to fix this problem. Thank you.
August 28, 2018 at 10:28 am
rkordonsky 63916 - Tuesday, August 28, 2018 8:59 AMI am trying to insert into table the approximately 74 thousand characters but Text datatype allows me to insert only 44 thousand characters. The Text data type can store up to 2,147,483,647 bytes. Any idea why SQL Server cut my text data? I would appreciate any advise how to fix this problem. Thank you.
Would need to see your exact insert query, as there are a number of ways for there to be a problem. Not sure why you wouldn't be able to change the data type to varchar(max), unless you have some type of application dependency... Please post back with the CREATE TABLE statement for the table involved, and the actual attempted INSERT statement. You can (and should) always obfuscate anything confidential. Also include the exact error message.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 28, 2018 at 10:34 am
rkordonsky 63916 - Tuesday, August 28, 2018 8:59 AMI am trying to insert into table the approximately 74 thousand characters but Text datatype allows me to insert only 44 thousand characters. The Text data type can store up to 2,147,483,647 bytes. Any idea why SQL Server cut my text data? I would appreciate any advise how to fix this problem. Thank you.
The TEXT data type has been deprecated. You should be using VARCHAR(MAX) instead.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 28, 2018 at 10:34 am
I'm thinking this might be some setting in SSMS. How are you running the insert?
Does this work?DROP TABLE dbo.TestText;
GO
CREATE TABLE dbo.TestText(myText text NOT NULL);
GO
INSERT INTO dbo.TestText(myText) VALUES (REPLICATE('X',50000))
GO
SELECT DATALENGTH(myText) FROM TestText
Why aren't you using nvarchar(MAX) or varchar(MAX)?
August 28, 2018 at 10:59 am
sgmunson - Tuesday, August 28, 2018 10:28 AMrkordonsky 63916 - Tuesday, August 28, 2018 8:59 AMI am trying to insert into table the approximately 74 thousand characters but Text datatype allows me to insert only 44 thousand characters. The Text data type can store up to 2,147,483,647 bytes. Any idea why SQL Server cut my text data? I would appreciate any advise how to fix this problem. Thank you.Would need to see your exact insert query, as there are a number of ways for there to be a problem. Not sure why you wouldn't be able to change the data type to varchar(max), unless you have some type of application dependency... Please post back with the CREATE TABLE statement for the table involved, and the actual attempted INSERT statement. You can (and should) always obfuscate anything confidential. Also include the exact error message.
Here is my Create table statement:
GO
/****** Object: Table [dbo].[TextTest] Script Date: 8/28/2018 12:56:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TextTest](
[Product_Code] [varchar](31) NOT NULL,
[Transcript] [varchar](max) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
August 28, 2018 at 11:02 am
drew.allen - Tuesday, August 28, 2018 10:34 AMrkordonsky 63916 - Tuesday, August 28, 2018 8:59 AMI am trying to insert into table the approximately 74 thousand characters but Text datatype allows me to insert only 44 thousand characters. The Text data type can store up to 2,147,483,647 bytes. Any idea why SQL Server cut my text data? I would appreciate any advise how to fix this problem. Thank you.The TEXT data type has been deprecated. You should be using VARCHAR(MAX) instead.
Drew
Thank you. I used in my second version of my TextTable VARCHAR(MAX) but the result was the same. It cut the same amount of characters.
August 28, 2018 at 11:23 am
Where and how are you getting the data to insert? It's likely it's being truncated somewhere before you actually insert it.
August 28, 2018 at 11:36 am
andycadley - Tuesday, August 28, 2018 11:23 AMWhere and how are you getting the data to insert? It's likely it's being truncated somewhere before you actually insert it.
As you understand I do not put the whole text I am trying to insert.
INSERT INTO dbo.TestText (MyCode, MyText) Values ('2187491WVD','>>: But I think we can begin with the introductions...')
August 28, 2018 at 11:54 am
rkordonsky 63916 - Tuesday, August 28, 2018 11:02 AMdrew.allen - Tuesday, August 28, 2018 10:34 AMrkordonsky 63916 - Tuesday, August 28, 2018 8:59 AMI am trying to insert into table the approximately 74 thousand characters but Text datatype allows me to insert only 44 thousand characters. The Text data type can store up to 2,147,483,647 bytes. Any idea why SQL Server cut my text data? I would appreciate any advise how to fix this problem. Thank you.The TEXT data type has been deprecated. You should be using VARCHAR(MAX) instead.
Drew
Thank you. I used in my second version of my TextTable VARCHAR(MAX) but the result was the same. It cut the same amount of characters.
What are you using to determine whether data has been cut or not?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2018 at 11:59 am
Jonathan AC Roberts - Tuesday, August 28, 2018 10:34 AMI'm thinking this might be some setting in SSMS. How are you running the insert?
Does this work?DROP TABLE dbo.TestText;
GO
CREATE TABLE dbo.TestText(myText text NOT NULL);
GO
INSERT INTO dbo.TestText(myText) VALUES (REPLICATE('X',50000))
GO
SELECT DATALENGTH(myText) FROM TestText
Why aren't you using nvarchar(MAX) or varchar(MAX)?
I used your code to create two tables one Text and another Varchar(Max) data types and in both cases it DATALENGTH return value 8000.
August 28, 2018 at 12:01 pm
Jeff Moden - Tuesday, August 28, 2018 11:54 AMrkordonsky 63916 - Tuesday, August 28, 2018 11:02 AMdrew.allen - Tuesday, August 28, 2018 10:34 AMrkordonsky 63916 - Tuesday, August 28, 2018 8:59 AMI am trying to insert into table the approximately 74 thousand characters but Text datatype allows me to insert only 44 thousand characters. The Text data type can store up to 2,147,483,647 bytes. Any idea why SQL Server cut my text data? I would appreciate any advise how to fix this problem. Thank you.The TEXT data type has been deprecated. You should be using VARCHAR(MAX) instead.
Drew
Thank you. I used in my second version of my TextTable VARCHAR(MAX) but the result was the same. It cut the same amount of characters.
What are you using to determine whether data has been cut or not?
I compare text inserted into the table with original text.
August 28, 2018 at 12:35 pm
rkordonsky 63916 - Tuesday, August 28, 2018 11:36 AMandycadley - Tuesday, August 28, 2018 11:23 AMWhere and how are you getting the data to insert? It's likely it's being truncated somewhere before you actually insert it.As you understand I do not put the whole text I am trying to insert.
INSERT INTO dbo.TestText (MyCode, MyText) Values ('2187491WVD','>>: But I think we can begin with the introductions...')
I have no idea what you mean by "As you understand I do not put the whole text I am trying to insert". I suggested you obfuscate data that is confidential or private, but not change the length. But rather than keep going, try the following instead:SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_PADDING ON;
GO
CREATE TABLE #TextTest (
Product_Code varchar(31) NOT NULL,
Transcript varchar(max) NOT NULL
) ON [PRIMARY];
GO
SET NOCOUNT ON;
DECLARE @X AS varchar(max) = 'X',
@RC AS int = 21839,
@ProductCode AS varchar(31),
@TranscriptDataLen AS int,
@Transcript AS varchar(max),
@StringPos AS int = 1;
INSERT INTO #TextTest (Product_Code, Transcript)
SELECT
Product_Code,
Transcript
FROM (
VALUES ('2187491WVD', REPLICATE(@X, @rc) + CHAR(13) + CHAR(10) + REPLICATE(@X, @rc))
) AS X (Product_Code, Transcript);
SELECT
@ProductCode = TT.Product_Code,
@TranscriptDataLen = DATALENGTH(TT.Transcript),
@Transcript = TT.Transcript
FROM #TextTest AS TT;
PRINT @ProductCode;
PRINT @TranscriptDataLen;
WHILE @StringPos <= DATALENGTH(@Transcript) - 8000
BEGIN
PRINT SUBSTRING(@Transcript, @StringPos, 8000);
SET @StringPos = @StringPos + 8000;
END;
PRINT SUBSTRING(@Transcript, @StringPos, 8000);
DROP TABLE #TextTest;
It may be that the most you can practically actually see is that smaller number of characters due to limitations outside of SQL Server. Check that datalength value and you'll know just how long the value that gets inserted is. Play around with this query for a while...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 28, 2018 at 12:58 pm
rkordonsky 63916 - Tuesday, August 28, 2018 12:01 PMJeff Moden - Tuesday, August 28, 2018 11:54 AMWhat are you using to determine whether data has been cut or not?
I compare text inserted into the table with original text.
How are you getting the inserted data back out of the table?
What does the DATALENGTH function tell you about the number of characters in the column?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 28, 2018 at 2:52 pm
rkordonsky 63916 - Tuesday, August 28, 2018 12:01 PMJeff Moden - Tuesday, August 28, 2018 11:54 AMrkordonsky 63916 - Tuesday, August 28, 2018 11:02 AMdrew.allen - Tuesday, August 28, 2018 10:34 AMrkordonsky 63916 - Tuesday, August 28, 2018 8:59 AMI am trying to insert into table the approximately 74 thousand characters but Text datatype allows me to insert only 44 thousand characters. The Text data type can store up to 2,147,483,647 bytes. Any idea why SQL Server cut my text data? I would appreciate any advise how to fix this problem. Thank you.The TEXT data type has been deprecated. You should be using VARCHAR(MAX) instead.
Drew
Thank you. I used in my second version of my TextTable VARCHAR(MAX) but the result was the same. It cut the same amount of characters.
What are you using to determine whether data has been cut or not?
I compare text inserted into the table with original text.
Using what to do the comparison??? And what did you use to add the text you inserted? You're not giving us a whole lot to work on to help you with.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2018 at 7:05 am
Jeff Moden - Tuesday, August 28, 2018 2:52 PMrkordonsky 63916 - Tuesday, August 28, 2018 12:01 PMJeff Moden - Tuesday, August 28, 2018 11:54 AMrkordonsky 63916 - Tuesday, August 28, 2018 11:02 AMdrew.allen - Tuesday, August 28, 2018 10:34 AMrkordonsky 63916 - Tuesday, August 28, 2018 8:59 AMI am trying to insert into table the approximately 74 thousand characters but Text datatype allows me to insert only 44 thousand characters. The Text data type can store up to 2,147,483,647 bytes. Any idea why SQL Server cut my text data? I would appreciate any advise how to fix this problem. Thank you.The TEXT data type has been deprecated. You should be using VARCHAR(MAX) instead.
Drew
Thank you. I used in my second version of my TextTable VARCHAR(MAX) but the result was the same. It cut the same amount of characters.
What are you using to determine whether data has been cut or not?
I compare text inserted into the table with original text.
Using what to do the comparison??? And what did you use to add the text you inserted? You're not giving us a whole lot to work on to help you with.
Sorry guys. The problem was not in SQL Server. In order to see the text I copied it to Notepad. And ALWAYS got the same result. But after that I decided to use Right(myText, 100) to check and found out that whole text was inserted into the field. After that I add more data into the table and had no problem. Thank you guys so much for your help!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply