Saving StringBuilder To SQL 2008
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.
VB.NET Code:
command.Parameters.AddWithValue("@TempFilesLog", TestData.TempFileLog.ToString)
Any ideas why it isn't working? Should I not save the data this way?
Re: Saving StringBuilder To SQL 2008
Just to pinpoint the problem a bit - have you tried something like this
Code:
command.Parameters.AddWithValue("@TempFilesLog", "abc")
and seen if it works??
Re: Saving StringBuilder To SQL 2008
Quote:
Originally Posted by
szlamany
Just to pinpoint the problem a bit - have you tried something like this
Code:
command.Parameters.AddWithValue("@TempFilesLog", "abc")
and seen if it works??
Yes and it does.
Re: Saving StringBuilder To SQL 2008
How about
Code:
command.Parameters.AddWithValue("@TempFilesLog", TestData.TempFileLog.ToString.Substring(0,100))
How long is the TempFileLog string??
Re: Saving StringBuilder To SQL 2008
Quote:
Originally Posted by
szlamany
How about
Code:
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.
Re: Saving StringBuilder To SQL 2008
Quote:
Originally Posted by
szlamany
How about
Code:
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?
Re: Saving StringBuilder To SQL 2008
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...
Re: Saving StringBuilder To SQL 2008
Don't use addwithvalue - try that next - define you parameter more clearly against the datatype in the DB...
Re: Saving StringBuilder To SQL 2008
Quote:
Originally Posted by
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...
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.
Re: Saving StringBuilder To SQL 2008
Well - 4000 and 8000 are magic numbers in field max sizes in SQL - that's why I was curious...
Why couldn't you do
Code:
debug.writeline TestData.TempFileLog.ToString.Length
command.Parameters.AddWithValue("@TempFilesLog", TestData.TempFileLog.ToString)
Re: Saving StringBuilder To SQL 2008
Quote:
Originally Posted by
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
Code:
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.
Re: Saving StringBuilder To SQL 2008
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
Re: Saving StringBuilder To SQL 2008
Quote:
Originally Posted by
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
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:
http://i.snag.gy/JvYIC.jpg
Normal Query:
http://i.snag.gy/vcCiE.jpg
That seems odd...
Re: Saving StringBuilder To SQL 2008
Show the script that creates the view please...
Re: Saving StringBuilder To SQL 2008
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?
Re: Saving StringBuilder To SQL 2008
Quote:
Originally Posted by
szlamany
Show the script that creates the view please...
SQL Code:
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
Re: Saving StringBuilder To SQL 2008
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
Re: Saving StringBuilder To SQL 2008
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
Re: Saving StringBuilder To SQL 2008
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...
Re: Saving StringBuilder To SQL 2008
Quote:
Originally Posted by
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
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.
Re: Saving StringBuilder To SQL 2008
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...
Re: Saving StringBuilder To SQL 2008
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