Transact SQL :: Reading Text From BLOB / Varbinary Objects

Aug 26, 2015

We have an interface where we receive data from an external supplier. One of the fields in the interface is of type BLOB (the source is an Oracle database), which would be read into our MSSQL database as image. This can also be converted to varbinary, and a typical field value looks something like:

0x70697A5F8F000000789C0DCCBD0DC2301....etc. etc.

However, we know that the origin only contains text, and we even know the text from the GUI they supply us with. The text could typically be "Delayed by 3 minutes because of water damage" or something like that.

What I want to do, is to extract that text from the field.

First, I have stored the incoming data stream in a table, where one column is of type varbinary(max). It looks like this goes swell. But I don't know which command to use in order to get the text extracted.

I have tried these:

1) select master.dbo.fn_varbintohexstr(Myfield) from Mytable
-> Returns just the text "0x70697A5F8...." which I have no interest in

2) select cast(Myfield as varchar(max)) from Mytable
-> Returns just Chinese signs.

3) select cast(Myfield as nvarchar(max)) from Mytable
-> Returns just Chinese signs.

4) declare @ptrval varbinary(16)
select @ptrval = TEXTPTR(MyField) from MyTable   -- with MyField defined as image
READTEXT MyTable.MyField @ptrval 1 30
-> Returns just the text "0x697A5F8...." which I have no interest in

Of course, since only text is stored in this field, the field should never have been defined as BLOB in the first place. But the source system is external, and it's a standard system, so we may not alter it in any way.

View 2 Replies


ADVERTISEMENT

Transact SQL :: Export BLOB (varbinary Max) Column To Excel Or CSV File?

May 20, 2015

I have a table in one of my databases that stores files in one of its columns. I need to be able to export this BLOB column into either a CSV or Excel file. I am forbidden from using xp_Cmdshell so I was wondering if there was a way to do this in the Cmd prompt.

View 5 Replies View Related

BLOB Or VARBINARY(MAX) As SSIS Source.

Jan 4, 2007

Hi all,

Can a SSIS package treat a file from a table ( VARBINARY(MAX) or BLOB) as a source for migration ?

Thanks in advance,

DBAnalyst

View 3 Replies View Related

Reading Blob From Sql Server Database

Jul 7, 2006

Hello,
I could save the file in the sql server database. The problem i am having is, I am not able to read the document from the database. I do not want to use response.outputstream......
I have to read the file for specific contents. Anyhelp would be great.
- Suresh
 

View 2 Replies View Related

How To Export BLOB Objects ?

Dec 7, 2007


Hi all,

One of my friend needs to Export BLOB object data to flat file for migration of database.

Can anyone tell us a possible way of doing this...

Cheers !
ash

View 1 Replies View Related

Tiff File Corrupted After Reading From Blob Column

Apr 11, 2006

I use this code to get Tiff files from db , but output file seems to be corrupted :
Dim pubsConn As SqlConnection = New SqlConnection("Server=(local);uid=sa2;pwd=sony;database=pubs;")        Dim logoCMD As SqlCommand = New SqlCommand("SELECT pub_id, logo FROM pub_info", pubsConn)
        Dim fs As FileStream                 ' Writes the BLOB to a file (*.bmp).        Dim bw As BinaryWriter               ' Streams the binary data to the FileStream object.
        Dim bufferSize As Integer = 100      ' The size of the BLOB buffer.        Dim outbyte(bufferSize - 1) As Byte  ' The BLOB byte() buffer to be filled by GetBytes.        Dim retval As Long                   ' The bytes returned from GetBytes.        Dim startIndex As Long = 0           ' The starting position in the BLOB output.
        Dim pub_id As String = ""            ' The publisher id to use in the file name.
        ' Open the connection and read data into the DataReader.        pubsConn.Open()        Dim myReader As SqlDataReader = logoCMD.ExecuteReader(CommandBehavior.SequentialAccess)
        Do While myReader.Read()            ' Get the publisher id, which must occur before getting the logo.            pub_id = myReader.GetString(0)                        ' Create a file to hold the output.            fs = New FileStream("\Server1shared1logo" & "KK" & ".tiff", FileMode.OpenOrCreate, FileAccess.Write)            bw = New BinaryWriter(fs)
            ' Reset the starting byte for a new BLOB.            startIndex = 0
            ' Read bytes into outbyte() and retain the number of bytes returned.            retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize)
            ' Continue reading and writing while there are bytes beyond the size of the buffer.            Do While retval = bufferSize                bw.Write(outbyte)                bw.Flush()
                ' Reposition the start index to the end of the last buffer and fill the buffer.                startIndex += bufferSize                retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize)            Loop
            ' Write the remaining buffer.            bw.Write(outbyte, 0, retval - 1)            bw.Flush()            ' Close the output file.            bw.Close()            fs.Close()        Loop
        ' Close the reader and the connection.        myReader.Close()        pubsConn.Close()
        Me.Div1.InnerHtml = ("<embed height=650 width=100% toolbar='' src= '" & "\Server1shared1logoKK.tiff" & "' type='application/x-alternatiff'>")
 

View 1 Replies View Related

Blob As Text

Jun 13, 2006

Hi,

I have a conversion application which convertts an access database to an sql server(different versions). I'm using stored procedures. The thins is that I export the OLe Object form access to SQL varbinary. what I do is to convert the binary data from the OLE Object to string using ToBase64String. The thing is that when I execute the SQL statement I get the following error:

Error:Operand type clash: text is incompatible with varbinary.

Can anyone tell me what I do wrong and how can I fix this? Thanks.

View 12 Replies View Related

Blob Text Or Varchar?

Jan 10, 2002

hi everyone a question for all you wise men out there! ;-)

i run a db with thousands of reviews, interviews, article and so on. What's better: 1) leaving all the articles on .txt files and then using the file system object to show them in the page; 2) insert the articles in the db splitting them in several varchar fields 3) insert the articles in the db using the text/blob fields

thanx, it's a very hard question for me!

View 1 Replies View Related

Convert BLOB To Text

Mar 14, 2008

I have a source DB that contains a BLOB which is really a bunch of text. I am trying to use SSIS to read this BLOB and stream the chars as text to a flat file which will later be FTP'ed to another server and reloaded as nvarchar records.

In my data flow task I have my source (SQL DB BLOB) and target (Flat File) of course...in between I am using a script component to try and convert the BLOB. Here is my code:


Dim intBlobLength As Integer = Convert.ToInt32(Row.denoteblob.Length)
Dim intFinish As Integer = intBlobLength - 1
Dim byteBlob(intFinish) As Byte
byteBlob = Row.denoteblob.GetBlobData(0, intFinish)

Row.outdenoteline. = byteBlob
Row.outdebtorrowid = Row.debtorrowid



I get a pipeline is to small error. I cant seem to adjust the size of the outdenoteline within the script componenet.

please help!

Regards.

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

Updating A BLOB Text Doesn't Work

Jun 28, 2006

Hi all,
I have the following weird situation.
I must update a "text" column in my mssql db with a string that is more than 10000 character long.
I know that "text" is able to store up to 2Gbyte of data, but what is happening?
that when i excecute the query (in PHP, using the odbc_exec function), my poor Apache got stuck!!!
and afterwards, i am not even able to access that field (i mean Apache go nut again).

Have you ever experienced somthing like that? Any suggestion?

thank you

View 1 Replies View Related

Extract Text From Sqlserver Varbinary (office Document)

Dec 5, 2007

 Hi all,I've the following scenario: One Full Text Search enabled SQL Server Table with one image type field that holds document uploaded from users.The idea is to store binary document(.doc. xls, .pdf, .ppt, html, .xml and so on) and using SQL server full text search i can retrieve record that contains certaind word or words.I have no problems with them. So imagine i do a simple select * from Documents where a=b and i get one collumn with binary document field.With this scenario, i want to "extract" that text from that document + 20 words left and 20 right to show user some info and help him to search desired document (not only for its type or title). Like search engines do.But believe me, i cant find out any component, class or something to do such a thing.I think, the hard work is done with full text search engine...Sql server has that data, but it can decodeI'm so desperate. I would accept answers such "sorry, it can't be done", from a experienced user, but i need to know  Thanks in advance 

View 1 Replies View Related

No Results Returned For Full Text Search On Varbinary(max) Column

Jul 10, 2007

Hi, I was wondering if any SQL Server gurus out there could help me...I
have a table which contains text resources for my application. The text
resources are multi-lingual so I've read that if I add a html language
indicator meta tag e.g.<META NAME="MS.LOCALE" CONTENT="ES">and
store the text in a varbinary column with a supporting Document Type
column containing ".html" of varchar(5) then the full text index
service should be intelligent about the language word breakers it
applies when indexing the text. (I hope this is correct technique for
best multi-lingual support in a single table?)However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.DECLARE @SearchWord nvarchar(256)SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column.Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.

View 1 Replies View Related

No Results Returned For Full Text Search On Varbinary(max) Column

Jul 10, 2007

Hi, I was wondering if any SQL Server gurus out there could help me...

I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g.
<META NAME="MS.LOCALE" CONTENT="ES">
and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)

However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.
DECLARE @SearchWord nvarchar(256)
SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.
SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)

I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column.

Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.

View 1 Replies View Related

[MSSQL] Blob-upload In Table With Full Text Index Goes Slow

Oct 31, 2005

i have a MSSQL 2000 database with about 30 tables in it. On one of those tables i've defined an full text index on an image field. In this table are around 500 records with binary files.

it functioned well for a time but now when i try to upload a file into the table this goes extremely slow (300 KB takes over 3 minutes).

i tried disabling "change tracking" but this didn't help a thing

adding blobs to other tables (without fulltext index on it) still goes fast.

what could be a reason that the uploading goes so slow??

View 3 Replies View Related

SQL 2005 Bug? Cannot Create Full Text Index For VARBINARY Column That's Populated With Converted NVARCHAR Values

Jul 11, 2007

Hi, I was wondering if any SQL Server gurus out there could help me...I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value - I've narrowed it down to this specifically, populating with non nvarchar text seems to work fine.To re-create the problem quickly...If I populate the column viaCONVERT(varbinary(max), 'test text')then there is no problem, I get results as expected.However if I populate the column viaCONVERT(varbinary(max), CAST('test text' as nvarchar(max)))no results are ever returned.Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary.I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out).The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it).Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.

View 14 Replies View Related

SQL 2005 Bug? Cannot Create Full Text Index For Varbinary Column That's Populated From Converted Nvarchar Values

Jul 11, 2007

Hi, I was wondering if any SQL Server gurus out there could help me...

I have a table I'm trying to apply a full text catalog to, however no results are ever returned due to the text column being cataloged being of varbinary(max) that's being populated from a converted nvarchar(max) value.

To re-create the problem quickly...

If I populate the column via
CONVERT(varbinary(max), 'test text')
then there is no problem, I get results as expected.

However if I populate the column via
CONVERT(varbinary(max), CAST('test text' as nvarchar(max)))
no results are ever returned.

Is this a bug with SQL Server 2005 Full Text Indexing? I'm happily creating full text catalogs when an nvarchar is not getting converted into a varbinary.

I'm setting the Document Type column to '.html' (I've tried changing this to '.txt' in case it was a fault with the html ifilter but the problem persists so I believe I can rule this out).

The reason I need to convert an nvarchar to varbinary is that the table holds multi-lingual text and I'm adding a html meta tag <META NAME="MS.LOCALE" CONTENT="ES"> to the beginning in order for the full text indexing word breaker to select the correct language to catalog the text with. The aim being to provide more relevant searches in users native languages (I've read a few articles that describe this technique, but it's the first time I've tried to apply it).

Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.

UPDATE: Below is a T-SQL script you can run to demonstrate the effect I'm experiencing...




Code Snippet

-- Create test database
CREATE DATABASE FullTextTest
GO
USE FullTextTest
GO

-- Create test data table
CREATE TABLE TestTable
(
pk UNIQUEIDENTIFIER NOT NULL CONSTRAINT tablePK PRIMARY KEY,
varbinarycol VARBINARY(MAX),
documentExtension VARCHAR(5),
)
GO

-- The below single entry WILL BE FOUND (the text source is being entered directly)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX),'<META NAME="MS.LOCALE" CONTENT="EN">test entry 1'), '.html')

-- The bellow two entries below WILL NOT BE FOUND (the text source is taken from an NVARCHAR(MAX) value)
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 2' AS NVARCHAR(MAX))), '.html')
INSERT INTO TestTable (pk, varbinarycol, documentExtension) VALUES (NEWID(), CONVERT(VARBINARY(MAX), CAST('<META NAME="MS.LOCALE" CONTENT="EN">test entry 3' AS NVARCHAR(MAX))), '.html')
GO

-- Create the full text catalog
sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG TEST AS DEFAULT
GO
CREATE FULLTEXT INDEX ON TestTable (varbinarycol TYPE COLUMN documentExtension LANGUAGE 1033)
KEY INDEX tablePK
GO

-- NOTE: You might need to give the catalog a chance to build before running the script below.

-- Now do a search that SHOULD RETURN 3 ROWS of data, but ONLY 1 ROW IS RETURNED
SELECT CAST(varbinarycol AS NVARCHAR(MAX)) FROM TestTable WHERE CONTAINS(varbinarycol, 'test')

View 10 Replies View Related

Transact SQL :: Reading XML Data

May 26, 2015

I have a table with a column  with data type XML.I dont know the contents of the XML structure etc and I need to extract it

View 5 Replies View Related

Reading In A Text File

Sep 25, 2007

I need to know how to import a text file into a stored procedure as one big varchar.  I don’t want to import the data straight into my tables.  I need to be able to work with it in the stored proc.

View 2 Replies View Related

READING TEXT FILES

Oct 24, 2001

I NEED TO READ A TEXT FILE INTO A SQL SERVER 6.5 TABLE. THE FILE HAS VARIABLE LENGTH FIELDS AND THE FIELDS ARE SEPARATED BY PLUS SIGNS ("+"). ANY IDEAS ? THANKS FOR YOUR TIME.

View 1 Replies View Related

Reading A Text File

Jan 18, 2005

hi

I need to read a hex file, the file path is in the database, and I need to read the file from sql.

Any help would be appresiated

View 2 Replies View Related

Reading A Text File

May 2, 2007

Guys, need help! I know this is not area for VBScript question, but possible I will find someone to help. Here is my question.

How can I read a text file of product IDs (ProductID contain only the first three character at the bigining of each line -- for example 220)and retrieve just those lines that meet a specified pattern?

Thk

View 4 Replies View Related

Reading Text Into Varchar

Jul 20, 2005

Hello,I have a column (text datatype) and has to send an email as a text(not attachment) using cdonts. I am reading the data from text columnstoring in a varchar field and saying cdonts.body = [data].This way I can send email to as a text format. Now, my problem is whenlength of data is greater than the 8000 chars it truncates the rest ofthe data.......and email I send is a truncated email.......loosingimporatnt data.How should I resolve this situation.......I am trying some differentideas but not worked yet. Finally, I am writting the entire content ina file and sending it as attachment but the reaquirement is to send itas a body text.Any ideas?Let me know if you need more details!Thanks,-Hayatwww.mysticssoft.com

View 1 Replies View Related

Reading Text File

Oct 16, 2006

does anyone here have a sample to how mssql can read a comma delimeted textfile...

View 1 Replies View Related

Reading Tab Delimited Text File

Mar 28, 2008

Is there anyway Sql Server reads a "Tab Delimited Text File" and Compare each record with the Column in a table..

my question is..

I've a Country_Code table which has 3 letter Country Code and the Actual Country names are listed in a Tab Delimited Text File "Country Data" with Country Code and Country Name, how do i read each record and compare to get the Actual Country Name for Display.

any ideas/suggestions.

thanks

View 3 Replies View Related

Reading Data From A Text File

Apr 21, 2007

I have text output files which are semi-structured.(Headers + irregular length tables below)

Is there a simple method of getting them into sql format(line by line) to try and extract data from them?

I know this won't be easy but its been worrying me for a long time. I have a method of importing the data into excel, but although difficult, it must be possible to get a system to get it into sql server. This must be a fairly common issue.

John

View 3 Replies View Related

Reading Data From Text File Database

Jun 6, 2007

 Hi everyone I have a directory that contains a lot of text files that have data I need to draw from.  I want to know if it is possible to write a program that will read all of the text files in the directory and pull out data and save it to a new textfile. For example: Each text file is formatted this wayColumn1, Column2, Column3"1","xxxx","yyyy""2", "xxxx", "yyyy""3", "XXXX", "yyyy" I want to put all lines that begin with 1 in one text file, all the lines that begin with two in another text file, and the same with all lines that begin with 3. my problem is I want to be able to point at the folder that contains those files and have it read every text file in the folder and perform the operation.  If this is possible can someone point me in the right direction on how to get started.Thank you for any help!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! 

View 1 Replies View Related

Reading Text File Into SQl SERVER 2005

Mar 25, 2008

Hi All,
I Need to load the text(CSV) files into sql server using text reader. Please can any one give me the code for that.
I want read that file in web page only. I can't use Bulk Insert.
First I will read the file into data set. Then i wanna update that in sql server table.
 Thank you,

View 1 Replies View Related

Output And Reading Data From A Text File??

Sep 1, 1999

Hi,

How would I be able to query a table (ie. all people with last name 'Smith'), have that set of data outputted to a regular text file (in a formatted way)

And what's the best way to manipulate that set of data to let's say update a Yes/No field in that table to mark that that those individuals('Smith') which were outputted in that text file?

What about the reverse? If I got a regular text file with Last Name, Social Security(delimited by tab), etc is there a way I can get SQL Server to read that text file and make an update to the database based on the Social security in that text file.

Any help would be immensely appreciated!
Angel

View 3 Replies View Related

How To Change Text On Web Automatically From Reading A SQL Server?

Jun 17, 2004

A section of this company's intranet site where I just started interning at has little company anniversary and birthday sections that look like (for the anniversary section.. in the birthday section, it looks the same, except it doesn't say how old the comployee is):

-Steve Cunningham 6/1 - 6 yrs
-Andrew Brown 6/3 - 11yrs
-Lisa Stone 6/4 - 3 yrs

How can I get it so instead of manually changing that text every month, it will look at a SQL database and automatically change that text every month? I'm guessing the pseudocode would be if the b-day or anniv. month matches the current month, display the first and last name, the date, and number of years (which would have to be calculated maybe?) Any help would be GREAT! Thanks!!

~Michael

View 2 Replies View Related

Reading Large Text Files With 2005 CE?

Dec 19, 2007

Hi€¦
During my web search looking for a solution I ran across SQL CE 3.5 articles. My questions about SQL CE 3.5 are:
1) Can SQL CE 3.5 handle a 4 €“ 6 GB file
- Read
- Parse (SQL)
2) Can SQL CE 3.5 act as a standalone client that a user can view a large (4-6 GB) text file?
- Will I need a .NET (small) client to read the large (4-6 GB) text file?
More info:
The text file will reside on the machine where the SQL CE 3.5 is installed. There is no pull to get the data.

Thank you (in advance)€¦

SQL CE 3.5

View 3 Replies View Related

BUG:(?)Transact SQL &&amp; Pop-up Objects List.

Mar 19, 2008

Hello everybody. When I start to test SQL 2008 Febuary CTP I detect that sume of new options work incorrectly. This is description of this bug from Microsoft Connect:"I don`t know, may be somebody detect this issue too. Sorry If I create duplicate. I start SQL Server Managment Studio & click new query. After that I try to create simple query like this
Uses Master
Select "column name" from "table".
I was surprised because when start write column name I saw the pop-up objects list. But this was very strange list, because I didn`t find necessary columns. This list show only "common" objects(such as database nemes, functions & ect). I think that such list(after first select) should show only children objects(columns, keys, triggers, indexes & ect.), after that is you write "from" such list should show only parents objects (Such as table name, database name & ect).
PS. Sorry for my english." After that(today) I get the answer from MS SQL Team "Hi,

Your observation is correct. Selection list (column names) can suggests correct candidates once user specifies FROM clause.

Regrads,
Eric Kang
SQL Server Program Manager" & bug was closed as "By Design". Then I reopen this bug with such comments "

But It`s lokks very strange. If I want to select column name from the list I should: 1. write SELECT 2. Press Space 2. Write FROM "Table name" 3. Returt to free space after SELECT 4. Start Write "Column name". 5. Choose the necessary Column name from the list. There are too much for such simple operation.
Posted by stanisluv on 3/19/2008 at 11:45 AM

Too much steps." Sorry I make a typo. I mean: "looks". If anybody else argee with me please validate & vote the feedback. This is the link for it: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=333047

View 1 Replies View Related

Reading Past CR-LF In Flat File Text Input

Jul 13, 2007

Hi,



I'm wondering if there is any way to get SSIS to notice, in the Flat File Source, that a "Ragged right" text input file has a record that is too short to populate all the specified columns.



I am reading data from a file that is supposed to be fixed length records, but record 193,591 (out of approx. 500,000) is 20 bytes short of the fixed length (60 bytes). So I changed the input to "ragged right" and found that I can thereby continue to read the file, and load the data (after setting the "maximum errors" to a number greater than the initial "1"). (Without this change to "ragged right", every record after the bad one was "out of synch" with the column arrangement -- so they never made it into the database table destination.)



But the "failures" I am now getting are during the Data Conversion step, when I try to convert some columns to integers (from text, in the input stream). And by looking at the data with a "Redirect Row" setting for the Data Conversion step, I am able to see that the Flat File Source is reading "right past the end of the row."



Is there a way to get the Flat File Source to honor the CR-LF record terminator, and decide that some text columns should contain "nothing" (NULL or zero-length strings), rather than the bytes that contain the CR-LF and the initial text from the next record? Can this somehow be noticed as an error condition?



Dan

View 12 Replies View Related







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