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.
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
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)
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?
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.
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??
We are debating what is industry “best practice� for serving huge numbers of images in an industrial scale website. More directly, which approach produces the best performance and the best scalability? For example, how do sites like ebay, Amazon, and other large sites handle the millions or billions of images they must deal with?
Store as BLOB in sql server?
Store in /images folder and store url text into sql server?
We always assumed that the second approach is what most sites must do. But do they?
One developer on our team maintains that storing one million or more image files in a directory will most certainly result in poor performance, because the server must scan the directory, searching for the correct file, each time a web request is made. The directory is not indexed (?) so performance must eventually suffer.
Other developer counters that storing millions of images as BLOBs into sql server will result in poor performance and HUGE database. An additional layer of access (webserver to sql server, back to webserver, then to client) causes a delay and performance hit. Who is right? What do the gurus as the world class sites do?
I have had an application running successfully in production for two years. In the last three months the app has become a document management system as well. During the addition of images, it was decided that no images would be stored in the database but on the file system. We now have over 500,000 images averaging in size of 92k each. I had to upgraid the raid once and I can only assume that I will again. I am not expecting the image count to exceed 700,000 in the next 6 months; By this time next year, I am expecting 1.2 million. Should I reconsider storing the images in the database? If they are stored in the database, what are the effects on performance? Thanks in Advance Wes
Does sql server has the data type similar to BLOB (Binary largerobject)which is available to DB2. BLOB in DB2 can support up to 2 G(variable-length data )if it does have, which one offers better functionalitiesany advice will be greatly appreciated!
Hi,One of our third-party software vendors is planning to implement BLOBin their database for storing certain documents. We are not toothrilled about it, since it can be a drain on our resources, but Iwould like to get the expert opinion out there on the pros and cons ofimplementing this.Also, the database is in Full recovery mode and we back up thetransaction log every 15 minutes. We also do a process similar to logshipping. We have two servers to which these transaction logs arerestored to periodically. What will the impact on the transaction logsdue to changes to the BLOB fields.If you could also point me to any resources that talks in detail aboutperformance, backup and recovery in relation to BLOB that would begreat.Thanks in advanceKR
can anyone help ,me out here with some design consideration reguarding importing of BLOB data to a SQL server 2000 using T-SQL statements?
I want to make an import of some documents which are stored in a Access database, to an Ms SQL server 2000. The documents are stored in the access database as a OLE Object, by now I thought of using the base64String function to convert the data from the access field and write it to the T-SQL statements which will written in a text batch file. And then I apply the SQL Convert function something like:
INSERT INTO testBin VALUES(convert(image,'base64sting_encoded')) go
Can anyone get me pointed in the right direction or even better specific instructions on how to export BLOB's to .JPG's ? They are in SQL 2005 and I have about 1500 that I need to export. I ran across another site that said to use SSIS but I havent had much luck.
Can someone please show me an example on how to read & write blob data to a Database? For example if I have the query below (Northwind), how do I actually place the blob item in a picture box on a windows form?SELECT Picture FROM CategoriesWHERE CategoryID = 5
Can someone please give me an example in C# on how to retrieve an Image from a Table and store i into a Picture box on a windows form? In addition, how to insert a blob record into a table as well.
We are using DTS to transfer database from 6.5 to 7.0. The data of those tables with text datatype can not be transferred to the server with version 7.0 though the table structure is transferred. The error message we got is "Error at Destionation for Row Number 1. Error encountered so far in this task: 1. query based insertion or updating of BLOB values can not be supported. The source we use is Microsoft ODBC driver for SQL Server. Thanks in advance. Su Ge
I'm trying to transfer a table from a sql 7 server to an sql 6.5 server. When I try to select OLE db as the destination it gives me an error saying I can't do OLE DB unless I have sql 7.... So I chose ODBC
When it tries to transfer the table I get this error:
QUERY BASED INSERTION OR UPDATING OF BLOB VALUES CAN NOT BE SUPPORTED
what the hell? Is this due to the transfer from 7 to 6.5? How do I get around this?
----- What I'm trying to do is change the datatype of a table in this 6.5 database from smallint to integer... (I wasn't the idiot that designed this database with a smallint primary key) There is not enough room to copy the table into a new table, and when I ran through the above process it dropped the table and I can't restore it because I'm not the admin. Is there a way of changing the primary key datatype without using up a large amount of database space? or can I do a transfer from 7-6.5 some how?
This is a nightmare... I wish my client would just upgrade to 7 then this would have taken 5 seconds instead of all day long
I currently have a problem with blobs being cut off.
From powerbuilder, I am trying to pull in an image that is stored as a blob. This has always worked fine in our software, until a more recent version, and is now presenting me with this problem. Here are the details...
When the software is installed with a Sybase Database, everything is working great. It pulls in the full size, and there are no problems here.
However, when the software is installed with a SQL Server DB, problems arise.
The main problem: When using a ADO.NET DBMS interface to the SQL Server DB, the select statement is only pulling in 32000 bytes.
Secondary problem: This one may present a problem in the future, if and when i fix the first problem. To narrow it down to see if it was the ADO.NET interface giving me the problem, i connected to the same table on the same server, but using an ODBC interface as opposed to ADO.NET. This gave me the first 32768 bytes.
So a) ADO.NET when interfacing with SQL Server is only giving me the first 32000 bytes in my selectblob statement. I have narrowed it down to ADO.NET, as the code works fine with Sybase, and ODBC interfacing with SQL Server does not limit it at 32000 bytes.
and if I get a solution to that, something may then be limiting the blob read in size at 32768. Maybe, maybe not... but it is happening with ODBC|SQL Server.
Does anyone have any ideas. This is driving me wild. I have pounded google searching for ADO.NET known blob limitations but cannot find anything.
I'm dying here. Anyone who can help me out would be great. Thanks
Is it possible to import BLOB data using SQL statements stored in a file to an SQL server. Can the actual INSERT statement contain binary data? Or how should I convert the binary data in order to work with an INSERT statement?
I am using OLE DB source component to read a blob data column from a table. Then I want to Pass this column to a script component,w hich in it's script task should be able to convert it to a string and send it as an email. I have tried several ways but none seems to work. Please advise
I have a table in a SQL Server database that contains a field(data type image) that contains a text file. I'm trying to retrieve this file and save it onto the hard drive. I'm using the code below, but get I get the error 'Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.' at line 11. This code worked fine when I tested it on the pubs database and exporting the logo field out of pub_info. I'm not quite sure why this doesn't work for my database. Can anyone see where I'm going wrong? 1 Dim cn As ADODB.Connection2 Dim rs As ADODB.Recordset3 Dim mstream As ADODB.Stream 4 cn = New ADODB.Connection5 cn.Open("Provider=SQLOLEDB;data Source=server;Initial Catalog=database;User Id='userid';Password='password'") 6 rs = New ADODB.Recordset7 rs.Open("Select * from filesubmissions where bundleId = 'F0000014.bun'", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic) 8 mstream = New ADODB.Stream9 mstream.Type = ADODB.StreamTypeEnum.adTypeBinary10 mstream.Open()11 mstream.Write(rs.Fields("BLOB").Value)12 mstream.SaveToFile("c:export.txt", ADODB.SaveOptionsEnum.adSaveCreateOverWrite) 13 rs.Close()14 cn.Close() Thanks
Dear All, I am trying to run the some code which is designed to return an image blob. However it throws an exceptionException Details: System.IndexOutOfRangeException: ToolbarLogoSource Error:
Line 55: Response.ClearHeaders() ;Line 56: Response.ContentType = "image/gif";Line 57: byte[] arr = (byte[]) sdr["ToolbarLogo"];Line 58: Line 59: Response.BinaryWrite(arr); I can run the sql SP query fine and it returns binary data ok: Does anyone have any pointers I should look at as to why the error is thrown? Do you think my blobs are corrupt? Here is the code: Thank you for takin the time to look public class GetBanner : System.Web.UI.Page { protected System.Web.UI.WebControls.Image Image1; protected void Page_Load(object sender, EventArgs e) { SqlDataReader sdr = null; int brand_id = 0; try { brand_id = Int32.Parse(Request.QueryString["brand_id"].ToString()); } catch (Exception) { // commented below. Since it is a image handleer //Response.Write("<HTML>You must supply a brand_id</HTML>"); return; } { SqlDataAdapter daGetBanner = new SqlDataAdapter(); string connectionInfo = ConfigurationSettings.AppSettings["ConnectionInfo"]; using(SqlConnection dbConnection = new SqlConnection(connectionInfo)) { SqlCommand objCommand = new SqlCommand("usp_get_new_guid_r", dbConnection); objCommand.CommandType = CommandType.StoredProcedure; dbConnection.Open(); sdr = objCommand.ExecuteReader(); while (sdr.Read()) { Response.ClearHeaders() ; Response.ContentType = "image/gif"; byte[] arr = (byte[]) sdr["ToolbarLogo"]; Response.BinaryWrite(arr); Response.Flush(); } sdr.Close(); dbConnection.Close(); } } }
I'm trying to retrieve an image that I have stored in a SQL Server db, and display it in an .aspx page. It's supposed to retrieve just one image, according to a unique key that's passed. However, when I view the page, nothing appears... just the Internet Explorer missing image placeholder. I've tried code from several diff tutorials, and can't seem to get it to work. I'm aware that some people think it's not a good idea to store images in a db, but I have to in this case, since the requirements for this project are that the images are stored in the database. I already know how to store references to file paths of images located in a server folder. I've just never done it this way... storing the whole images in the database. The table has 3 columns, one for the id, "FileType" stores the image type, and the "ImageBinary" is the image itself. If anyone can shine some light on why my code doesn't display anything, I'd greatly appreciated your help.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("strConn")) Response.Write("Page Loaded") 'This is just for testing to see if the page loads at all Dim myCommand As New SqlCommand("Select * from UserImages WHERE id=1234", myConnection) Try myConnection.Open() Dim DR As SqlDataReader DR = myCommand.ExecuteReader(CommandBehavior.CloseConnection) If DR.Read() Then Response.ContentType = DR.Item("FileType") Response.BinaryWrite(DR("ImageBinary")) Else Response.Write("no image found") End If myConnection.Close() Response.Write("<br>Image successfully retrieved!") Catch SQLexc As SqlException Response.Write("Read Failed : " & SQLexc.ToString()) End Try End Sub
I've spent quite a few hours on this one... I've successfully gotten tables to datapump between source and destination datbases, however... I get the following message when I hit a table with a blob field:
Step 2 failed, error: x80040E21, x80040000 + 3617 General error -2147217887 (80040E21). (Microsoft Data Transformation Services (DTS) Data Pump (80040e21): Cannot instantiate Storage Object for source column 6 ('description') Blob data. Your provider may require that all Blob columns be rightmost in the source result set.) (Microsoft OLE DB Provider for ODBC Drivers (80040e21): Errors occurred)
I've written a VB com dll that does a simple datapump... but am stuck on this "Storage Object" instantiation thingy...
Any recommendations as to what I can put in my datapump object to allow just a simple xfer of blob types?
We are currently working on a method to store Visio drawings in a SQL Server database. One approach we looked at was to take and store the coordinates of each individual object in the drawing in a table, and then pull those coordinates back out when we need to render the drawing.
The second approach we are examining is the use of BLOB's; specifically, store the entire Visio drawing as a BLOB in SQL Server 7. We have only been able to find examples utilizing ADO. Is anyone aware of any method to save BLOB's using stored procedures? We'd like to use this method fore simplicity and maintenance sake.
Does anyone have any experience of this type of data please? I have been asked to work on a project where it looks like we will be taking MS Office files and scanned images and storing them in a SQL 2000 db so that they can be aquired by a third party application. I am most intereested in the size of the records as the server may need upgrading, for space and performance. I am also interested in how a BLOB record is created, is it a particular save process from Word / Excel etc or can you specify to import as a BLOB from a SQL script?
Any info welcomed as i really dont know where to start !!!
How do I create a large column type in MSSQL similar to the Blob field in MySql. I trying to upload data to mssql but keep on receiving the error tat the columns buffer size is not big enough ?
I've tried using varchar with size 8000, but stil NOT working
Ok, I am pretty new to BLOB's on SQL SERVER 200 so I need some answeres.
1. Can BLOB Fields hold .doc and/or .pdf documents? 2. If yes, can the contents of the documents be searchable. 3. If documents are updated do BLOB Fields lengtht change/grow dynamically?
I would apreciate any answers in this matter and if possible examples were I can get a better idea on how to configure the BLOB fieds on my DB and/or documentation regarding BLOB's.
Hi all,It was my understanding (Please correct me if I'm wrong on this!) thatBLOB data actually reside on their own separate pages and a BLOB fieldonly holds a pointer to the location of the actual data, therefore theBLOB data per se would not get written to the log, only the pointerwould be written.If log shipping works by applying the transaction log to the standbydatabase, then what happens to the BLOB data?Related question, how does transactional replication work? Is it alsobased on the transaction log?TIA,Ellen
I have a column in my table that is an Image (blob). The data storedin this blob is basically pdf files. I need a query to determine thetotal size of these blob's in the database. Here is what ishappening, if I run it as below I get 23736000 for a result.select sum(datalength (cast(document AS binary(8000)))) fromplan_reportIf I run it like this select sum(datalength (cast(document ASvarbinary(8000)))) from plan_reportI get 23736000 also as a resultIf I run it like this select sum(datalength (cast(document ASvarbinary))) from plan_reportI get 89010 this as a resultIf I run this: select datalength(document) from plan_report then Iget a result for each row and when I sum those rows I get 5584452854this as a result.I do not believe the first 3 queries are returning correct result setsdue to the limit on the fields (binary/varbinary) and since this is animage field I cannot convert to anything else.....I am out of ideas, and any help you can give me will be greatlyappreciated...
