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

SQL 2012 :: Parsing Large Varbinary Fields

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

Use Decimal Or Varbinary Data Type?

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

Inserting .doc Data Into Varbinary Column

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

Programmatically Remove Varbinary Data From Table

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

Programmatically Move Varbinary Data From One Table To Another

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

Timeouts On Delete With Image And Varbinary(max) Data

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

Problem Creating Varbinary(max) Data Type

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

Transact SQL :: Any Way To Encrypt Varbinary Column Data?

Nov 4, 2015

Is there a way to encrypt 'varbinary' column data?

View 9 Replies View Related

Data Types Question - Varbinary And Timestamps

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

Export Varbinary Data Types To Local PC From SQL Database

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

Insert Varbinary Data Using Command-line Sqlcmd

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

Parsing Data

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

Parsing XML Data

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

Parsing Data

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

Parsing Data...

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

SQL XML :: Parsing Xml Data

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

Help With Parsing Data

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

T-SQL (SS2K8) :: Detect / Determine Data Stored In Varbinary Field

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

Using Varbinary(max) And UPDATE .WRITE To Store Data In A Streaming Fashion

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

SQL String Data Parsing

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

Parsing Data Question.

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

Store Varbinary Data Result Into SSIS Variable Through Execute SQL Task

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

Data Parsing In SQL Server Views

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

Parsing A Field Data And Populating Other Fields

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

Parsing The Data From One Column To Individual Columns?

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

SQL 2012 :: Parsing Out Data String With Text Delimiters

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

SQL Server 2008 :: Parsing Data To Select Certain Values From XML

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

SQL 2005 - .Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0 - Error Parsing Statements

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

Varbinary(0)????

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

Varbinary(max)

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

Varbinary Cast

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

Replace Varbinary

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







Copyrights 2005-15 www.BigResource.com, All rights reserved