SQLTeam.com | Weblogs | Forums

How to insert a more thatn 8000chars in an nvarchar(max)


#1

Hi.

I was using this:

[code] USE [VrExternal]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[vsInsertSeatOrderNew]
(
@SessionId nvarchar(256),
@LogKeyUsed nvarchar(150),
@SeatQuantity smallint,
@Seats nvarchar(50),
@SeatData nvarchar(max),
@SeatLib nvarchar(max),
@RealAllocated nvarchar(2),
@LibSeatsToRemove nvarchar(50)
)
AS
BEGIN
--- will return one partner on branch , cannot has another partner with same uname,pass.
/****** Script for SelectTopNRows command from SSMS ******/
INSERT INTO vsSeatOrderMid(SessionId,SeatQuantity,DateTimeOrdered,LogKeyUsed,Seats,SeatData,SeatDataLib,RealSeat,LibSeatsToRemove)
values (@SessionId,@SeatQuantity,getdate(),@LogKeyUsed,@Seats,@SeatData,@SeatLib,@RealAllocated,@LibSeatsToRemove)
END [/code]
when i inserted more than 4000chars the column or seatdata became empty.

I tried to use this:

[code] USE [VrExternal]
GO
/****** Object: StoredProcedure [dbo].[vsInsertSeatOrderNew] Script Date: 30/7/2015 6:34:21 μμ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[vsInsertSeatOrderNew]
(
@SessionId nvarchar(256),
@LogKeyUsed nvarchar(150),
@SeatQuantity smallint,
@Seats nvarchar(50),
@SeatData nvarchar(max),
@SeatLib nvarchar(max),
@RealAllocated nvarchar(2),
@LibSeatsToRemove nvarchar(50)
)
AS
BEGIN
declare @s1 nvarchar(max)
set @s1 = Replicate(cast(@seatdata as nvarchar(max) ),10000)
--- will return one partner on branch , cannot has another partner with same uname,pass.

INSERT INTO  vsSeatOrderMid(SessionId,SeatQuantity,DateTimeOrdered,LogKeyUsed,Seats,SeatData,SeatDataLib,RealSeat,LibSeatsToRemove)
values (@SessionId,@SeatQuantity,getdate(),@LogKeyUsed,@Seats,@s1,@SeatLib,@RealAllocated,@LibSeatsToRemove)

END [/code]

No i get "Could not allocate space for object 'dbo.Large Object Storage
System object: 422597778210816' in database 'tempdb' because the
'PRIMARY' filegroup is full. Create disk space by deleting unneeded
files, dropping objects in the filegroup, adding additional files to the
filegroup, or setting autogrowth on for existing files in the
filegroup."

I went to db files and used autogrowth to unlimited size but i still get that error.

Basically i have a full theater view in a string that is 8-10000chars
and it must be inserted in an nvarchar column. How do i do that without
issues?

I could try to post the text object below but i am not sure that the site will let me.

A test notepad shows 110kb size. I don't think it is that big, so what
is going on with the " 422597778210816' in database " ? I am not
inserting something that big.

Thanks


#2

Can you run the following query and post the results?

USE tempdb
GO
SELECT name, 
	size,
	max_size,
	size/128 AS SizeMB,
	CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceMB
FROM 
	sys.database_files

#3

I agree that it should not silently insert the row with an empty [SeatData] column.

The [SeatData] column is Nvarchar (rather than Varchar) presumably?

How are you checking that [SeatData] is empty? Is it NULL or Empty-String? (I'm thinking that on Nvarchar LEN() might be unreliable and perhaps it needed DATALENGTH() instead? but thats a Long-Shot!!!)

The "Could not allocate space for object" might be Disk Full on the drive holding TEMPDB - on a bog-standard install that might be in "c:\program files..." :cry:


#4
name    size    max_size    SizeMB    AvailableSpaceMB
tempdev    89600    -1    700    11.000000
templog    14080    -1    110    69.875000
tempdev1    76800    -1    600    8.687500
tempdev2    76800    -1    600    8.625000
tempdev3    13568    -1    106    3.437500

Kristen the seatdata is an empty string. Now that you say it, I vaguely remember getting data a couple of days again. Not 100% sure since there is a job that delete the rows. If that is the case then ,i will be checking a full disk.
So to sum it up. Using the first query would have to insert data if the disk is not full? Or it limits to 4000 characters?
Thanks.


#5

Hm. The data is divided to 2 disks. First has 66gb free second has 32gb free. So i do not think that there is a problem there.


#6

What the?

Turns out that if i copy paste the empty column. It has all the data, but i am seeing a big empty text instead!!

What is going on here?

I just manually go and insert the data. If i reach a limit on what i
insert then the column will show blank but will have the data.

If i do not reach that limit then the data is showing fine. (so i just
run the first sproc doing exec vsinsertseatordernew '2','44d', etc etc ,
'biggggggggggggggggggg dataaaaaaaaaaaaaaaaaaaaa', etc etc

After a limit of data insert, it will show blank but if i copy paste it will have the data!!!!


#7

Your use of "show", does that mean you are using the grid to look at the data? The grid will not always show all characters.


#8

You are correct!
I can get the data back but the grid will get "confused" after an amount of characters.
Thanks all.


#9

The amount of data to show is a configurable item in SSMS.
From the toolbar select Tools->Options->Query Results->SQL Server. From there you'll need to configure the output size for the various destinations(Grid, Text, etc.)


#10

Would be handy if GRID changed colour (say) if the data length exceeded the displayed length ...

I do

SELECT LEN(MyColumn) AS [Len], MyColumn, ...

when using a grid with large data so I can eyeball if the data I'm seeing is likely to be correct ... but if you actually want it output (so you can cut & paste it somewhere else) I don't know how to do that with SSMS as I can't see the grid allowing output of MB? ... GB?? into a single cell. I have a little VBScript command line program I use when I have that requirement ... but perhaps there is a way to do it with SSMS these days? I'm old and prone to be resistant to change!!


#11

Note that REPLICATE only goes up to 8000 bytes, which for nvarchar would be 4000 characters.


#12

If you do want REPLICATE to output more than 8000/4000, the expression to be replicated has to be varchar(max) or nvarchar(max).

From here

REPLICATE ( string_expression ,integer_expression )

If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

So I think the OP is ok - the code he posted is casting string expression to nvarchar(max)


#13

Interesting, didn't know that, thanks James & Scott.

I did a little test:

DECLARE @SeatData nvarchar(max) = 'Foobar'
declare @s1 nvarchar(max)
set @s1 = Replicate(cast(@seatdata as nvarchar(max) ),10000)
SELECT LEN(@s1), DATALENGTH(@s1)
--
Result:
-------------------- -------------------- 
60000                120000

in fact

set @s1 = Replicate(@seatdata, 10000)

seems to work OK too (SQL2008) so that presumably is implicitly nvarchar(MAX)

However

set @s1 = Replicate(N'Foobar', 10000)
gives:
-------------------- -------------------- 
3996                 7992

but

set @s1 = Replicate(cast(N'Foobar' as nvarchar(max) ), 10000)

works correctly


#14

I think what the blurb from MSDN meant to say is that if the expression is not already a large-value data type, THEN it must be cast explicitly to a large value type. In your example, @SeatData is already nvarchar(max) so it shouldn't need to be cast again. But a literal reading of the text would seem to suggest otherwise.


#15

Hi.
Also found out today that the tempdb had only 512kb space. So that should also suggest a problem.
However since it works as it meant to be in the original query, I left is so because the second out (cast to nvarchar) take much much more time.
Thanks again.