[RESOLVED] How to insert a more thatn 8000chars in an nvarchar(max).

[sapator]

Hi.
I was using this:

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

when i inserted more than 4000chars the column or seatdata became empty.
I tried to use this:

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

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.test.txt

[FunkyDexter]

As I understand it a varchar(max) will take MUCH more than 4000 or 8000 characters. According to Gail Shaw (who's pretty well respected) it'll actually take some 2 billion so I don't think that's your problem. My suspicion is that the error is exactly what it says, you've filled the disk.

Check the disk location of the TempDB and make sure you've got plenty of room there.

Edit> I don't think 422597778210816 is referring to the size of the object. I think it's actually some kind of identifier but I'd have expected either something meaningful or a GUID so I'm not sure.

[sapator]

Hey funky.
The data is devided to 2 disk that hav 66 adn 32 gb free space.
Not sure how to check the tempdb database.
Thanks.

[sapator]

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!!!!

[digitalShaman]

i experienced a 4k limit when passing a parameter from a vb script to a stored proc, i think it got trunctated(?). there was a way around it, i could post tomorrow if it is not resolved by then

[sapator]

Hmmm.
Long story short, the grid will actually get "confused" after a finite amount of characters shown. If i do a select i get the characters.
You learn something new with MS every time i guess.
Thanks all.

[FunkyDexter]

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

OK, I'm not sure exactly what you're seeing or where but you may be experiencing the mechanism SQLServer uses to actually store the data. Anything up to 8000 characters (I think 4000 for an nvarchar) is actually stored as characters. Above that limit it's stored as a blob. That blob is automatically transformed back and forward by the drivers as you insert or read the data. So if you're storing over that limit the content of the field will be a blob, not characters. I'm guessing the mechanism you're using to view it doesn't know how to render the blob so it's not showing, it is actually there though.

My advice would be to handle this whole thing through a front end (either web or desktop) that accesses the data via ADO or similar. That should do all the interpretation for you.


BTW, Hope everything's OK over there and your keeping your head above water. Tsipras did fold in the negotiations.

[sapator]

Hey funky. I show that you can "play" with the query result using tools-options-query results but i did bother for now. Maybe later.
Thanks.
P.S. We are OK here i guess. Tsipras was a pu$$y. Instead of threaten to leave the Euro, he got threatened and signed everything, even with the referendum going again the sign of a memorandum. Personally Am OK as, as you know, I'm kinda in a big corporation but we all going to be hit with some of the new issues somehow. VAT,extra taxation (that would include me), pension cutting etc. But we don't go on a riot as other countries do with such conditions. Not sure if that is good or bad though :/
Anyhow, Will be vacating in a week, so I'm looking forward to that first and will see from September