Parsing Varbinary Data In T-SQL
Apr 11, 2008
Hi,
We serialize a custom object into a byte array (byte[1000]) and store it in a SQL Server 2005 table column as varbinary(1000).
There are a lot of rows retrieved with each SqlDataReader from C# code: up to 3,456,000 rows at a time (that is, roughly 3.5 million rows).
I am trying to figure out what will be more efficent in terms of CPU usage and processing time.
We have come up with quite a few approaches to solve this problem.
In order to try a few of them, I have to know how I can extract certain "pieces" of data from a varbinary value using T-SQL.
For example, out of those 1000 bytes, at any given moment we need only the first 250 bytes and the third 250 bytes.
Total: 1000 -> [250-select][250-no-need][250-select][250-no-need]
One approach would be to get everything and parse it in C#: get the 1st and the 3rd chunks of data and discard the unneeded 2nd and 4th.
This is WAY TOO BAD.
Another approach would be to delegate the "filtering" job to SQL Server so that SqlDataReader gets only what it needs.
I am going to try a SQL-CLR stored procedure, but when I compared performance of T-SQL vs. SQL-CLR stored procs a few weeks ago, I saw that the same job is done by T-SQL a bit faster AND (more importantly for us) with less CPU consumption than SQL-CLR.
So, my question is:
how do I select certain "pieces" of varbinary column data using T-SQL?..
In other words, instead of
SELECT MyVarbinary1000 FROM MyTable
how do I do this:
SELECT <first 250 from MyVarbinary1000>, <third 250 from MyVarbinary1000> FROM MyTable
?
Thank you.
View 4 Replies
ADVERTISEMENT
Jun 9, 2015
I have a table with raw scientific test results in a single field, some of which are over 25Mb field. I need to parse into the field to find and aggregate selected values from the field.
Table structure is
CREATE TABLE [dbo].[Gxxx_Data](
[id] [uniqueidentifier] NOT NULL,
[Status] [nvarchar](50) NULL,
[GxxxItem_ID] [int] NULL,
[Stats_Data] [varbinary](max) NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
[code]...
From which I need to parse and summarize the (Assembler) opcodes (MOV,CMPi, SHR etc...)I need to parse the large field [Stats_Data] to locate the target data.The internal result strings are delimited with Char(10), conservative counts are from 64k to over 100k lines in each record. Is there a way to parse the individual lines into another table (temp) that would be queried/regexed ?
View 9 Replies
View Related
Jul 19, 2007
I need to store 256 bit hash (SHA-2 alogrithmn) in one of the table'sprimary key. I would prefer to use numeric data type rather varcharetc.* Decimal datatype range is -10^38 +1 to 10^38 -1. I can split my 256bit hash into two decimal(38, 0) type columns as composite key* I can store the hash as varbinary. I never used it and don't havemuch understanding in terms of query writing complexities and dealingit through ADO (data type etc.)It would be heavy OLTP type of systems with hash based primary keyused in joins for data retrieval as well.Please provide your expert comments on this.RegardsAnil
View 1 Replies
View Related
Aug 18, 2007
I need to put .doc data into a varbinary column for full text searching. I have created the db and columns but am unsure as to how to insert the varbinary data. I have found some discussions about inserting images but nothing explicitly on .doc files. Can anyone suggest resources or sample code?
View 3 Replies
View Related
Mar 29, 2008
Hey all,
I have a profile table for members of a website that allows them to upload an avatar for use throughout the site. I decided to add the option to just remove the avatar if they want to no longer use one at all and I am stuck.
I am running an update statement against thier entire profile incase they change more then just removing thier avatar but of course when I try this statement.
myCommand.Parameters.AddWithValue("@avatar", "")
I get a conversion error, which makes sence. So what instead of "" do I need for code to just insert empty data to overwirte thier current avatar data.
Any help would be great, thanks for you time.
View 5 Replies
View Related
Apr 6, 2008
Hey all,
Another varbinary question.
I am trying to move an image stored in a table varbinary(max) directly from one table to another programmatically.
The rest of the data is just nvarchar(50) so I just use a T-SQL select statement in the code behind and feed all of the date into an SqlDataReader, I do this becuse there is some user interaction when the data is moved, so there may be some new values updated when transfering from one table to another, so once the old and possibly new data is stored in seperate variables then I use a T-SQL insert statement to move all of the data into the other table.
Problem is I am not really sure how to handle the image data(varbinary(max)) to just do a straight up transfer from the one table to another. I get conversion errors when trying to handle the data as a string which makes sense.
Not sure what to put for code examples since I really am stumped with this, but here is what is not working.
Dim imageX As String
SqlDataReader Code - imageX = reader("imageData")
Insert code - myCommand.Parameters.AddWithValue("@imageData", imageX)
Thanks in advance,
View 6 Replies
View Related
Oct 8, 2007
I have a database that I am using as an archive for emails and am storing them in varbinary(max) data types. The database works fine for inserts and retrieval but I cannot delete large sets of records ( > 30K) without it timing out. I assume this is because SQL server isn't simply releasing the handle to the blob but is instead doing a lot of work reclaiming the space.
Is there any flag I can set or approach I can use to resolve this issue?
I was considering moving the blobs into a seperate simple table and putting async triggers on the primary to delete the blobs, will this work?
Any ideas are appreciated. Besides the "store files in the file system" idea.
- Christopher.
View 4 Replies
View Related
Sep 11, 2006
I'm unable to create a field with the type varbinary(max). When I try doing this with Management Studio, it tells me that the maximum length is 8000 bytes. I've also tried creating the field with DDL as shown below, but that doesn't work either. If I create the varbinary field with a length of 8000 or less, it works fine. Is there some trick to using varbinary(max)?
Thank you.
create table images (filename nvarchar(250) primary key,photo varbinary(max))
View 4 Replies
View Related
Nov 4, 2015
Is there a way to encrypt 'varbinary' column data?
View 9 Replies
View Related
Nov 27, 2006
Greetings once again SSIS friends,
I have some source tables which contain timestamp fields (that's timestamp data type not datetime). My dimension table holds the maximum timestamp value as a varbinary(8).
I want my package to have a variable that holds that value but I don't know which data type to use for this. The reason for this is because I want to use that variable to then retrieve all records from my source table that have a timestamp value greater than the value stored in the variable.
Please advise on what data type is suitable.
Thanks for your help in advance.
View 2 Replies
View Related
Aug 22, 2006
Could someone help me with writing the code to export the contents of a varbinary field in my database to make the contents be written to the local harddrive? I can do this in Visual Foxpro but my company wants it in C# and I have no clue about C#.
Thank you:eek:
View 3 Replies
View Related
May 23, 2008
Hi,
I have a SQL script file which creates a table and and inserts all data required by the client app into the table.
The table also holds images in a varbinary field. How do I insert an image held on the file system (e.g. "C:ImagesMyImage.gif") into a varbinary field using a script that is run using sqlcmd on the command line?
Any pointers greatly appreciated!
Mike
View 8 Replies
View Related
Apr 21, 2000
Do anyone know of any functions I can use to parse the following data eg.
M 3480-7 should be 3480
M 3477-19 should be 3477
M 28-10 should be 28
Thanks in advance,
Vic
View 1 Replies
View Related
Nov 7, 2003
I have a SQL Server table that holds XML documents. Is there a known SQL Server XML parser ?....how can I export XML data into a readable format ?
thanks
View 1 Replies
View Related
Sep 13, 2005
Does anyone know any good URL's for examples on parcing data using SQL?
As an example, i've got First/Middle/Last name of a person inside a single field, I want to turn that into 3 fields.
Thanks!
Caden
View 8 Replies
View Related
Mar 5, 2008
Table1 includes a column for email address. I'd like to parse everything up to 8 chars before the '@' symbol in this column. My script doesn't appear to be working, and I'd appreciate any assistance in troubleshooting it!
select substring(UPPER(substring(email,1,charindex('@',email)-1)),1,8)
from table1
The error produced is;
Invalid length parameter passed to the substring function.
What am I doing wrong? The logic in the script looks good to me...
View 7 Replies
View Related
Jul 29, 2015
Having never used SQL for anything serious I have been asked to look at importing multiple data files into a database and have come to a dead stop. The XML file is a repeating structure with but the number of <SampleVial>s and the number of <Particles>s in each file is a variable...example shown below
<AcquisitionIntervals>
<SampleVial id="1">
<AcquisitionInterval>
<StartOfIntervalUpdate>
<Id>0</Id>
[code]....
I need to pull all of the particle data associated with each <SampleVial>.
View 3 Replies
View Related
Apr 28, 2008
This is what I have:
Field1
bob johns 555-123-5678
barry molsen 111-222-3333
333-444-5555 mary smith
566-234-0987 randy miller
This is what I want to have:
Field 1
Field 2
555-123-5678
bob johns
111-222-3333
barry molsen
333-444-5555
mary smith
566-234-0987
randy miller
I am looking for a way to take information from one and split it into 2 fields.
We are currently using one for two different type of information. Name and phone#. The info in the original field is mixed with the name first sometimes and other times the phone # is first.
Can you think of a way to using sql to separate this data into 2 fields?
There are 30,000 rows with the info like this.
We are using sql server 2005
Thanks
Blair
View 8 Replies
View Related
Oct 21, 2014
I have several tables a varbinary column in a database. They have names like CSB_BLOB or OBJECT_BLOB. Now I am having intermittent success with getting the data out.
For example this query returns readable text from this data.
0x46726F6D3A20226465616E6E6167726.....etc --data as stored in the column
SELECT CAST(CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM OBJECT_BLOB
However this column has the following query results.
0x0001000000FFFFFFFF01000000000000000C....etc. --data as stored in column
--this query returns empty result
SELECT (CSB_BLOB AS VARCHAR(MAX)) AS 'Message' FROM CSB_STATUS_LOG
--this query returns no change???
SELECT CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), CSB_BLOB, 2), 2) FROM CSB_STATUS_LOG
0001000000FFFFFFFF01000000000000000C....etc
Obviously there is a difference between the two but I am not educated enough to interpret this difference. What do I need to learn / read so I can look at the data in one of these BLOB columns and know how to convert it to something meaningful?
Something like:
1. Try to cast as varchar to see if it is text.
2. Turn into a byte array and see if it is a jpg
3. Turn into a byte array and see if it is a pdf
4. Convert it to hex and then cast as varchar
5. etc....
View 3 Replies
View Related
Nov 1, 2007
I have the following table:
CREATE TABLE [dbo].[IntegrationMessages]
(
[MessageId] [int] IDENTITY(1,1) NOT NULL,
[MessagePayload] [varbinary](max) NOT NULL,
)
I call the following insertmessage stored proc from a c# class that reads a file.
ALTER PROCEDURE [dbo].[InsertMessage]
@MessageId int OUTPUT
AS
BEGIN
INSERT INTO [dbo].[IntegrationMessages]
( MessagePayload )
VALUES
( 0x0 )
SELECT @MessageId = @@IDENTITY
END
The c# class then opens a filestream, reads bytes into a byte [] and calls UpdateMessage stored proc in a while loop to chunk the data into the MessagePayload column.
ALTER PROCEDURE [dbo].[UpdateMessage]
@MessageId int
,@MessagePayload varbinary(max)
AS
BEGIN
UPDATE [dbo].[IntegrationMessages]
SET
MessagePayload.WRITE(@MessagePayload, NULL, 0)
WHERE
MessageId = @MessageId
END
My problem is that I am always ending up with a 0x0 value prepended in my data. So far I have not found a way to avoid this issue. I have tried making the MessagePayload column NULLABLE but .WRITE does not work with columns that are NULLABLE.
My column contains the following:
0x0043555354317C...
but it should really contain
0x43555354317C...
My goal is to be able to store an exact copy of the data I read from the file.
Here is my c# code:
public void TestMethod1()
{
int bufferSize = 64;
byte[] inBuffer = new byte[bufferSize];
int bytesRead = 0;
byte[] outBuffer;
DBMessageLogger logger = new DBMessageLogger();
FileStream streamCopy =
new FileStream(@"C:vsProjectsSandboxBTSMessageLoggerInSACustomer3Rows.txt", FileMode.Open);
try
{
while ((bytesRead = streamCopy.Read(inBuffer, 0, bufferSize)) != 0)
{
outBuffer = new byte[bytesRead];
Array.Copy(inBuffer, outBuffer, bytesRead);
string inText = Encoding.UTF8.GetString(outBuffer);
Debug.WriteLine(inText);
//This calls the UpdateMessage stored proc
logger.LogMessageContentToDb(outBuffer);
}
}
catch (Exception ex)
{
// Do not fail the pipeline if we cannot archive
// Just log the failure to the event log
}
}
View 7 Replies
View Related
Sep 16, 2007
<p>
Hi everybody, I was hoping to get some advice something I can't quite get my head around. I have a SQL db which contains a table with ratings using the AJAX rating control.
When someone rates an object, I need to select the current rating and then use those numbers to;
- calculate the new average
- add new score to total score
- increment number of votes by one.
I thought this can be best achieved using the SELECT statement and then parsing the SELECT string. (is the string comma separated?)
using each array, i'd need to convert this into integers and then do the calculation. and re-upload the data to the ratings table (using the UPDATE statement).
Is this the best way of proceeding? I have tried initially to write the code using three sql statements. But that would mean to many requests from the server, right?
Below is the conde I have writting already.int myrating;
myrating = Rating1.CurrentRating;string getscore = "SELECT " +
"RatingScore" +"FROM Rating " +
"WHERE ItemID= '" + _ItemID+ "'";string getcount = "SELECT " +
"RatingCount" +"FROM Rating " +
"WHERE ItemID = '" + _ItemID + "'";string getaverage = "SELECT " +
"RatingAverage " +"FROM Rating " +"WHERE ItemID = '" + _ItemID + "'";
int _ratingscore;int _newscore; _ratingscore = int.Parse(getscore);
_newscore = _ratingscore + myrating; //add new rating score to old scoreint _ratingcount;
int _newcount;_ratingcount = int.Parse(getcount);
_newcount = _ratingcount + 1; //increase count by 1int _ratingaverage;
int _newaverage;_ratingaverage = int.Parse(getaverage);
_newaverage = _newscore / _newcount; //calculate new average rating
otherwise
otherwise would i be best off to do the following?...
string[] dbRatings = SQLstring.Split(',');
??
Any help would be appreciated.
Many thanks in advance.
Phil
</p>
View 2 Replies
View Related
Apr 24, 2000
Can some body please give me the correct syntex for parsing data from the
following alphanumeric field: 0014990328
I want to get the 3rd number/character and put it in a separate field.
I also want to get the 4th number/character and ...
I tried substring( column.. but nt getting the correct result.
Thanks in advance.
M.Khan
View 1 Replies
View Related
Feb 13, 2008
I cannot find the data type for parameter mapping from Execute SQL Task Editor to make this works.
1. Execute SQL Task 1 - select max(columnA) from tableA. ColumnA is varbinary(8); set result to variable which data type is Object.
2. Execute SQL Task 2 - update tableB set columnB = ?
What data type should I use to map the parameter? I tried different data types, none working except GUI but it returned wrong result.
Does SSIS variable support varbinary data type? I know there's a bug issue with bigint data type and there's a work-around. Is it same situation with varbinary?
Thanks,
-Ash
View 8 Replies
View Related
Jul 23, 2005
If column1 in SQL Server column is text: 19980701What is the syntax in the select statement to convert it to a datelike: 07/01/1998Thanks for any helpRbollinger
View 1 Replies
View Related
Nov 3, 1999
I have just started to look at SQL and have a theory question that I could apply to a test I want to run. I have some legacy data from a previous project and the database was not designed properly (in my opinion). They have ONE field to capture City and State information. All the data is formatted City, State .
Does SQL have commands that can look at data in a field, strip out info before and info after a comma and then write that to other fields?
So, I would like to normalize this to take the data in a field called CityState and parse it, trim it and then populate two new fields 1) City and 2) State.
Thanks for your help!
Scott
View 1 Replies
View Related
Aug 16, 2012
I have data in SQLSever 2008 R2 in one column as following. I would like to run a sql statement and capture them into individual columns. Would that be possible? The column separator is |
MSH|^~&|HL7ADM|PYXIS|PAH|HL7PRX_PAH|201208131129 46||ZPM^ZPM|9442|P|2.3.1||||||| ZPM|C|console|N-HEART-ST|1|23|43025204|DOBUTAMINE 1000MCG/ML|U|1|1|1|CS3808|TEST, MONKEY|||1|||||0|1|0|20120813112839||||||||0||IV|| |||||||||||
View 4 Replies
View Related
Nov 5, 2014
I'm trying to parse out a line of data that is separated by the text "atc1.", "atc2." etc.
For example,
[atc1.123/atc2.456/atc3.789/atc4.xyz/]
If I only want the data after atc2., then I could search the string for "atc2." and collect all the characters afterwards. But how can I make sure to trim off all the data after "atc3." to make sure I'm only collecting "456" from the example above?
View 2 Replies
View Related
Mar 13, 2015
I have results that are XML data and I am trying to figure out how to parse the data to select certain values from the xml.
example
<InformationRequest xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" teamid="TEAM003341507" playerid="PL341508" gameid="G000000852" playertype="Starter" FolderName="Test" CurrentYear="2015" Ultimateid="P00000688505" xmlns="http://schemas.sports.com/Messages/Stats" />
I would like to write a statement that just pulls the game id G000000852. So just the id right of gameid=.
Not really sure where to start. Table is GAME, and column is XMLDATA.
View 0 Replies
View Related
Nov 2, 2006
Hi,
Does anyone else have this error message pop up in SSMS when you try to parse sql statements:
.Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
There was a thread back in March 2006 that mentioned this error, but the posted resolution was to install SP1. I have SP1 installed but I still get the error.
I only receive the error when I'm parsing statements, if I run the statement it's fine.
Thanks
Matt
View 8 Replies
View Related
Jun 30, 2006
Does anybody can expain me what is the reason for varbinary accept the value 0 for length? We can insert files with >0 length in it?????
View 1 Replies
View Related
Jan 22, 2007
Hi,
We have a large database table with an IMAGE field holding documents of < 1MB size.
Over time this table has become quite large and operations on it have become slow.
My question is, would changing from IMAGE to varbinary(max) make the binary storage more efficient thereby making the table operations faster.
Also, would the table size be smaller with varbinary(max)?
Thanks
View 1 Replies
View Related
Sep 16, 2004
Can anyone tell me what varbinary casts to ?? can I do this int[] temp=(int)DataReader["varbinaryColumn"]. Or is byte[] temp the appropiate protocol.
View 1 Replies
View Related
Dec 12, 2006
hello.
i want to do something with this database but i don't know how:
there is this column that is varbinary and i want to replace just one part of its content, for example:
this is its content:
'CC08FF806785'
and i just want to change the FF for AA.
i tried using query analyzer with this:
update table
set column=replace(column,'FF','AA')
but it says something like conversion isn't allowed from data varchar to varbinary and that i should use the function "convert" but i don't know how to use that!
does anyone know how to do that?
thanks!
View 15 Replies
View Related