Saving StringBuilder To SQL 2008

[weirddemon]

I'm scanning a bunch of files and writing the results to a StringBuilder. I then want to save this data to a single column, which I've set as nvarchar(MAX).

But no matter what I do, it always appears as a blank string in SQL. I wrote the results to a text file as well and everything is there.

command.Parameters.AddWithValue("@TempFilesLog", TestData.TempFileLog.ToString)

Any ideas why it isn't working? Should I not save the data this way?

[szlamany]

Just to pinpoint the problem a bit - have you tried something like this

command.Parameters.AddWithValue("@TempFilesLog", "abc")

and seen if it works??

[weirddemon]

Just to pinpoint the problem a bit - have you tried something like this

command.Parameters.AddWithValue("@TempFilesLog", "abc")

and seen if it works??

Yes and it does.

[szlamany]

How about

command.Parameters.AddWithValue("@TempFilesLog", TestData.TempFileLog.ToString.Substring(0,100))

How long is the TempFileLog string??

[weirddemon]

How about

command.Parameters.AddWithValue("@TempFilesLog", TestData.TempFileLog.ToString.Substring(0,100))

How long is the TempFileLog string??

I'll try that. It's about 900 lines. But, usually, if the space in the column is not large enough, a very specific error message would appear.

[weirddemon]

How about

command.Parameters.AddWithValue("@TempFilesLog", TestData.TempFileLog.ToString.Substring(0,100))

How long is the TempFileLog string??

Hm... well, that worked. But do you know how I can save all of the data?

[szlamany]

I ask for length - you give me lines?

Seems you have non-alpha like characters in this data??

Try using

varbinary(max)

as the datatype instead.

I store jpg images in sql tables with this datatype...

[szlamany]

Don't use addwithvalue - try that next - define you parameter more clearly against the datatype in the DB...

[weirddemon]

I ask for length - you give me lines?

Seems you have non-alpha like characters in this data??

Try using

varbinary(max)

as the datatype instead.

I store jpg images in sql tables with this datatype...

I don't know the length without parsing it out. You weren't concerned with the exact length. You were concerned about the length, in terms of it being too large. Telling you it's 900 lines gives you a good idea that it's pretty long.

[szlamany]

Well - 4000 and 8000 are magic numbers in field max sizes in SQL - that's why I was curious...

Why couldn't you do

debug.writeline TestData.TempFileLog.ToString.Length
command.Parameters.AddWithValue("@TempFilesLog", TestData.TempFileLog.ToString)

[weirddemon]

Well - 4000 and 8000 are magic numbers in field max sizes in SQL - that's why I was curious...

Why couldn't you do

debug.writeline TestData.TempFileLog.ToString.Length
command.Parameters.AddWithValue("@TempFilesLog", TestData.TempFileLog.ToString)

It was just an extra step at the time. The length is 823,548. And this is on a low end. The log file could be exponentially larger.

[techgnome]

wait... hang on... how are you determining that the field is blank? if you select from the table in SSMS and SEND THE RESULTS TO TEXT... and not grid.... do you then see your results? I've seen cases where a leading crlf shows up in the results... since the result grid doesn't do multiline, it just shows the first result, an empty line... by going into text mode though, you get the results in all its mulit-lined glory goodness.

-tg

[weirddemon]

wait... hang on... how are you determining that the field is blank? if you select from the table in SSMS and SEND THE RESULTS TO TEXT... and not grid.... do you then see your results? I've seen cases where a leading crlf shows up in the results... since the result grid doesn't do multiline, it just shows the first result, an empty line... by going into text mode though, you get the results in all its mulit-lined glory goodness.

-tg

Hm... well... apparently the data is there. If I search for it via a View, it shows as blank data. But if I do a normal query, it shows up.

View Search:


Normal Query:


That seems odd...

[szlamany]

Show the script that creates the view please...

[weirddemon]

So even though the data is saved, it's not all there. It gets truncated.

Any ideas on the best way to save this data? Could I maybe serialize the physical log file into the database?

[weirddemon]

Show the script that creates the view please...

SELECT     TOP (100) PERCENT Date_Test, StopTime, StartTime, Test_Line, Test_Slot, CSO_N, Unit_SN, Family, Bios_Rev, CPU, CPU_TestTime, CPU_HighestTemp, Diags_Ver, 
                      Pass_Flag, FailureCD, GoldHDD_Flag, Client_HDDSN, TestStep_Flag, PassMark_TestTime, Bat_1stRead, Bat_2ndRead, ManualPasses, HasBiosUpdated, 
                      HasExtendedTest, AdminEID, TempFilesLog, TempFileSizeRaw
FROM         dbo.tbl_TestPassYeild
WHERE     (CSO_N = N'ABC12345')
ORDER BY CSO_N, Date_Test DESC

[techgnome]

That is interesting... any chance that the view is toasting it? what if you select from the view, join to the table, and pull the field directly from the table rather than through the view? if you get your data then, I'd start researching the view...

-tg

[techgnome]

Ummm... no it's not... SSMS has an option to truncate large fields... crap... you're going to make me look it up too aren't you? It's in Options Result Window somewhere... quite often the default is like 800 or so... I tyhink it supports a max of 8120 or something like that... don't know if it's been updated to account for the new varchar(max) option....it's got spinner buttons, set it to some rediculously high number then hit hte up value button and see how far it spins

-tg

[szlamany]

Under query options menu - but I see that is for RESULTS to TEXT. Grid should not have those issues - but I'm not running SSMS 2008...

[weirddemon]

Ummm... no it's not... SSMS has an option to truncate large fields... crap... you're going to make me look it up too aren't you? It's in Options Result Window somewhere... quite often the default is like 800 or so... I tyhink it supports a max of 8120 or something like that... don't know if it's been updated to account for the new varchar(max) option....it's got spinner buttons, set it to some rediculously high number then hit hte up value button and see how far it spins

-tg

I set the maximum characters to be displayed, to the max. Which was 8192.

It shows me more data, but I think I'm missing which property you're referring to. SMSS is definitely truncating the data. Not just the viewable data either. It truncates most of the actual data.

I exported that record to excel and it only kept about 50 of the 900 lines.

[szlamany]

Select Len(TempFilesLog) From ... will tell you if all the bytes made it into the field.

And knowing the value it truncated it to would be helpful - if in case it did truncate and we aren't all chasing a red herring...

[techgnome]

honestly, I think it is a herring... and a green one at that... what you REALLY need to do is write some VB code, run the select and see what you get THERE... because in the end, THAT's what's important... not what Query Analyzer/SSMS is returning (which in my opinion is notoriously inaccurate, but with good reason). And DON'T go by the results from the Server Explorer either... I wouldn't trust it either.

-tg