Application is ocr'ing tiff image files and then storing the resultant text data in a text field in SQL 2005 database. This field is then used with the full text catalog.
All works fine,
However, am I using the correct field type to store the text files for efficiency and space saving?
If I use a binary field, does this reduce the size of the database by compressing the text data in the binary field? Also, is there a limit as to the size of text file that I could store in a binary field?
It would be good to get feedback on this before I go too far down the wrong road.
So, text field to store the text data or binary field to store the actual text file?
This may be a stupid question but I can't find an easy answer for what Iwant to do. I need a blob data field that can store both binary and textdata like the sql_variant field. I am using vb.net to populate a sql table.I want to store both string and binary data.
Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn
I'm importing an Access database to SQL Server 2000. The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).
I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.
Is this some sort of an encoding problem that arose during database import? I would appreciate any pointers.
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
i've a reasonable amount of experience with MS Access and less experience with SQL Server. I've just written an .NET application that uses an SQL Server database. I need to collate lots of data from around the company in the simplest way, that can then be loaded into the SQL Server database.
I decided to collect the info in Excel because that's what most people know best and is the quickest to use. The idea being i could just copy and paste the records directly into the SQL Server database table (in the same format) using the SQL Server Management Studio, for example.
Trouble is, i have a problem with line feed characters. If an Excel cell contains a chunk of text with line breaks (Chr(10) or Chr(13)) then the copy'n'paste doesn't work - only the text up to the first line break is pasted into the SQL Server database cell. The rest is not pasted for some reason.
I've tried with MS Access too, copying and pasting the contents of a memo field into SQL Server database, but with exactly the same problem. I've tried with 'text' or 'varchar' SQL Server database field formats.
Since i've no experience of using different types of databases interacting together, can someone suggest the simplest way of transferring the data without getting this problem with the line feeds? I don't want to spend hours writing scripts/programs when it's just this linefeed problem that is preventing the whole lot just being cut'n'pasted in 5 seconds!
Can anyone point me any solution how to export a MEMO field from an Access database to a TEXT field from an MS SQL Server 2000. The import export tool from SQL server doesn't import these fields if they are very large - around 9000 characters.
I am very new to using SQL. Our department usually uses Brio to query the various databases under our control. However, I have recently come against a problem that prompted me to create a custom SQL query which works well as far as it goes. My problem is looking for specific conditions in billing information I receive monthly. I would like to compare on of the date fields contained in the database with a field in the form of YYYYMM (200710, for October 2007) I have created a custom column generator that forms a date from the YYYYMM. I would like, however, do the translation on the fly and make the comparison during the query. The problem is that query without the date check returns a mass of data, only about 1 percent of which is what I want.
The beginning of the SQL query looks like this:
FROM From.T_Crs_Tran_Dtl WHERE T_Crs_Tran_Dtl.Crs_Bill_Yr_Mo IN ('200710', '200711', '200712') AND ((T_Crs_Tran_Dtl.Crs_Cde IN ('1G', '1V') AND (T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND (T_Crs_Tran_Dtl.Prev_Stats_Cde IN (' ', 'TK', 'TL') AND T_Crs_Tran_Dtl.Cur_Stats_Cde IN ('TK', 'TL') AND T_Crs_Tran_Dtl.Std_Tran_Typ_Cde='B') OR (T_Crs_Tran_Dtl.Prev_Stats_Cde='UN' AND T_Crs_Tran_Dtl.Cur_Stats_Cde='XX' AND€¦
It is the €ś(T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND€? part of the query that is just plain wrong. The business part of this statement takes the YYYYMM field and turns it into a date which is the last day of YYYYMM.
I hope someone out there can help me with making this comparison.
Hi, I have asked this question on 3 forums now and never get an answer, I don't know what is so hard about this question but I will try it here. I am using SHA512 in C# to convert a password and its salt to hashed. I need to store the password hash and the salt hash in the database in two fields. I was told to use binary field to store the hash data and that the output of SHA512 would ALWAYS be the same no matter how long the password is. I modified this hash example to use only SHA512 and to work with byte array instead of plain text. All I need to know now is what size I need to make my binary field to hold this password that is hashed. http://www.obviex.com/samples/Code.aspx?Source=HashCS&Title=Hashing%20Data&Lang=C%23 Say I have a password which is 30 characters max, and a salt which is 16 characters max. The password and the hash are stored in seperate fields in the same table. They are both hashed using SHA512 and are both being stored as byte arrays in C#, what size to I need to make the binary data type in order to hold the password, and to hold the salt. Thanks!
hi I have used the following code (mostly created by MSDN) to retrive a binary field from SQL database. it works but I have extra space between characters. for example if I save a text file with "Hello world" text, after retriving I have it like "H e l l o w o r l d". what is the problem?????? I am really looking forward your answers private void retrive() { public void a() { SqlConnection connection = new SqlConnection("Some Connection string"); SqlCommand command = new SqlCommand("Select * from temp", connection); // Writes the BLOB to a file FileStream stream; // Streams the BLOB to the FileStream object. BinaryWriter writer; // Size of the BLOB buffer. int bufferSize = 50; // The BLOB byte[] buffer to be filled by GetBytes. byte[] outByte = new byte[bufferSize]; // The bytes returned from GetBytes. long retval; // The starting position in the BLOB output. long startIndex = 0; // Open the connection and read data into the DataReader. connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess); while (reader.Read()) { // Create a file to hold the output. stream = new FileStream( "C:\file.txt", FileMode.OpenOrCreate, FileAccess.Write); writer = new BinaryWriter(stream); // Reset the starting byte for the new BLOB. startIndex = 0; // Read bytes into outByte[] and retain the number of bytes returned. retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize); // Continue while there are bytes beyond the size of the buffer. while (retval == bufferSize) { writer.Write(outByte); writer.Flush(); // Reposition start index to end of last buffer and fill buffer. startIndex += bufferSize; retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize); } // Write the remaining buffer. if (retval != 0) writer.Write(outByte, 0, (int)retval - 1); writer.Flush(); // Close the output file. writer.Close(); stream.Close(); } // Close the reader and the connection. reader.Close(); connection.Close(); } }
Can anyone point me in the right direction to find documentation for the problem below?I need to store and retrieve ten fields of 16-bits each for testing 16 true-false conditions (a total of 160 bits in each record) so I think I'd like to use ten 2-byte binary fields (160 "bit" fields would be quite unmanageble, if even possible [I think there is some kind of limit to the number of fields in a single record]). I'm not quickly finding in the SQL Server's online documentation how to test for, use and update binary fields. I'll keep looking, but can anyone point me in the right direction? I'm using VB, if that makes any difference.
I want to have an SSIS package that processes a file in the normal insert, update style. But at the end I want to store the file as a binary field to another table for archive purposes. I am having trouble finding a good way to do this. Any samples, ideas, or articles would be appreciated.
hello dear friends I am trying to save a binary file to database with the following code: public static void memorystreamToDb() { MemoryStream mst = new MemoryStream(); UnicodeEncoding u = new UnicodeEncoding(); string Textn = "Test"; byte[] b = u.GetBytes(Textn); mst.Write(b ,0,Textn.Length ); BinaryReader reader = new BinaryReader(mst); byte[] file = reader.ReadBytes((int)mst.Length); using (SqlConnection connection = new SqlConnection("Some Connection String")) { SqlCommand command = new SqlCommand("INSERT INTO temp (examplefile) Values(@File)", connection); command.Parameters.Add("@File", SqlDbType.Binary, file.Length).Value = file; connection.Open(); command.ExecuteNonQuery(); } reader.Close(); mst.Close(); } or with the other method from a real file (not memory stream) public static void Addfile(string path) { CommonMethods_class k = new CommonMethods_class(); byte[] file = GetFile(path); using (SqlConnection connection = new SqlConnection(k.Get_connection_string())) { SqlCommand command = new SqlCommand("INSERT INTO temp (examplefile) Values(@File)", connection); command.Parameters.Add("@File", SqlDbType.Binary, file.Length).Value = file; connection.Open(); command.ExecuteNonQuery(); } } and after running each of them seams that the file have been saved; but I can not retrive the files. I have tried some solutions from msdn but inside the created file is empty. the point that i really look for it is to just working with memory not in the disk before saving. and then retriving each field that I want. looking forward your points thank you in advance
i need to store a bmp or zipped file in a field of a ms sql db. i read the file using vb6 o.net, and in my mind i think sto store it in binary mode. the files could be more of 12 mega. whitch kind of field a could use?
Im a programmer for an university webportal which uses php and msssql. When an user creates a new entry and his text is too long the entry is cut short and weird characters appear at the end of the entry.
For example: http://www.ttz.uni-magdeburg.de/scripts/test-messedb/php/index.php?option=show_presse&funktion=presse_show_mitteilung&id=333
How can I set the text limit to unlimited? Could it be something else? Is there a way of splitting an entry to several text fields automatically?
Thanks in advance for any help you can give me, Chris
In SQL 2012, this fails with the error message, cannot find the text qualifer for field.
To get around this, we are having to import the data into a Dirty Data column of aTEMP table, ID, Dirty Data, Clean data - perform multiple updates and change the text qualifier and ensure they are only changed in the right places so we can keep the ". In this example, we changed the text qualifier to PIPES.
After these updates, we then export the data from CLEAN data back out to CSV, then reimport it into the origional destination table with a new text qualifer.
I have a column in a table that has a type TEXT,when I pull the length of a row it returns 88222 but when I select from that column it dows not show all the text in the result set.
Hi,I been reading various web pages trying to figure out how I can extract some simple information from the XML below, but at present I cannot understand it. I have a MS SQL 2005 database with which contains a field of type text (external database so field type cannot be changed to XML)The text field in the database is similar to the one below but I have simplified it by remove many of the unneeded tags in the <before> and <after> blocks. I also reformatted it to show the structure (original had no spaces or returns) For each text field in the SQL table contain the XML I need to know the OldVal and the NewVal. <ProductMergeAudit> <before> <table name="table1" description="Test Desc"> <product id="OldVal"> </table> </before> <after> <table name="table1" description="Test Desc"> <product id="NewVal"> </table> </after></ProductMergeAudit>
I am attempting to write a script to add onto the end of a text field the words " -- Disposed " (About 60 rows worth). The field is a TEXT field, so unlike a varchar field I can't just use Update as shown below.
Code:
Update AR_Primary_asset Set AR_Primary_asset.description = AR_Primary_asset.description + ' -- Disposed' Where AR_Primary_Asset.ASSET_REF in ('1','2','4')
I found on the Mircosoft pages about UPDATETEXT, but this only seem to work to update one row (In the case below Asset_ref = 3, was the only row effected) .
Code:
DECLARE @Dispose binary(16) SELECT @Dispose = TEXTPTR(DESCRIPTION) FROM AR_PRIMARY_ASSET WHERE AR_Primary_Asset.ASSET_REF in ('1','2','3')
CREATE TABLE [dbo].[instructions]( [site_no] [int] NOT NULL, [instructions] [text] NULL ) Select top 3 * from instructions
Output
Site_noInstructions 20Request PIN then proceed 21Request PIN if wrong request name 22Request PIN allowed to use only numbers
All text instructions start with “Request PIN” but after that the text are different for every site_no
I need insert in all site_no rows and after the “Request PIN” the text “and codeword” keeping the current rest of text
Desired output
Site_noInstructions 20Request PIN and codeword then proceed 21Request PIN and codeword if wrong request name 22Request PIN and codeword allowed to use only numbers
Im building up a query in my code behind. When I execute this query it returns a list of users who match the criteria.BUT, I want to add something extra. In my DB in the tblUserData there's a field "interests" datatype nvarchar(30) which contains the numbers (comma-delimited) of the interestsID in my tblInterests. For example, my interestsfield may contain something like: 1,4,8Now if someone wants to find someone who has number 4 and 8 as interests, how can I search in this textfield?!?I wrote this in a SP, but I'd really like it to be possible from code-behind and build the query myself...declare @s varchar(20)set @s='4,8'EXEC('SELECT * FROM tblUserData WHERE Interests in ('+@s+')')
I have a textbox field that take 2000 characters from user..Then I used a store procedure to save that user input into database through an insert statement, but for some reason it just never store the whole string of 2000 characters but only store some of it (like 100 or something) .. Seems like a data type problem…(I am using SQL server 2000)
I am haveing a field called as description in a table called info in the database. Now when I give the following query:
select * from info where description = 'test'
it gives me error:
Microsoft OLE DB Provider for SQL Server (0x80040E14) The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Hiive got a table and contains a surname text field. Why cant i do a select statement ORDER BY surname. I get an error saying i cant sort a text field! how do i go around it!thanks
hi, I am trying to split a text field . what I want to do is - split a text field and insert chars 1 to 8 in one field and then from 9 to 20 in another and so on. Can someone help me in solving this. TIA. PD
I try to update a field of text datatype using WRITETEXT statement. The information that I try to change has both the single(') and double (") quotes in it. How can I get it done ?
I have a table with millions of records, table has three fields: case_id,line_no and notedata field, notedata field is 60 chars long, datatype varchar.for each case_id there could be as many as 2000 line_no meaning 2000 notes. I need to compress these notes into one note by case_no, For example case_no 1 could have 2000 lines of notes but my comressed table shoul have only one line containing all 2000 notes in line_no sequence.
my compressed table contains two fields case_no and notetext, notetext is a text field.
here is the script I am trying to use to accomplish the task but it does not append more than 8000 chars in one case, so my notes are chopping of, how should I do this, please let me know of any suggestions..
Thanks.
truncate table eldoecinotescompressed insert into eldoecinotescompressed (app_code, case_no) select distinct app_code, substring(system_key, 6,8) from eldoecinotes
DECLARE notes_cursor CURSOR FOR select substring(system_key, 6,8) case_no, line_no, rtrim(notedata) notedata FROM EldoECINotes where substring(system_key, 6,8)<>'' order by 1,2; OPEN notes_cursor; FETCH NEXT FROM notes_cursor into @case, @lineno, @note; WHILE (@@FETCH_STATUS = 0) BEGIN
BEGIN TRANSACTION; update eldoecinotescompressed set notetext = (case when isnull(datalength(notetext), 0) >= 0 then substring(isnull(notetext,''), 1, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 8000 then substring(isnull(notetext,''), 8001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 16000 then substring(isnull(notetext,''), 16001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 24000 then substring(isnull(notetext,''), 24001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 32000 then substring(isnull(notetext,''), 32001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 40000 then substring(isnull(notetext,''), 40001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 48000 then substring(isnull(notetext,''), 48001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 56000 then substring(isnull(notetext,''), 56001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 64000 then substring(isnull(notetext,''), 64001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 72000 then substring(isnull(notetext,''), 72001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 80000 then substring(isnull(notetext,''), 80001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 88000 then substring(isnull(notetext,''), 88001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 96000 then substring(isnull(notetext,''), 96001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 104000 then substring(isnull(notetext,''), 104001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 112000 then substring(isnull(notetext,''), 112001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 120000 then substring(isnull(notetext,''), 120001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 128000 then substring(isnull(notetext,''), 128001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 136000 then substring(isnull(notetext,''), 136001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 144000 then substring(isnull(notetext,''), 144001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 152000 then substring(isnull(notetext,''), 152001, 8000) else '' end ) + (case when isnull(datalength(notetext), 0) > 0 then char(13) + char(10) else '' end) + isnull(@note,'') where case_no=@case; commit; FETCH NEXT FROM notes_cursor into @case, @lineno, @note; END CLOSE notes_cursor; DEALLOCATE notes_cursor;
I have a table that has a TEXT field and I want to replicate it. It already has a primary key field in it. I read that in SQL Server 6.5, you can do transactional replication on a table that has a TEXT field in it. I'm wondering if I need to do any other special configuration to replicate this table, i.e. enable the truncate on checkpoint option or anything else? Thanks, Hoang
