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?
Thanks.
Hi,Can we insert BLOB data using store procedure using Oracle and ASP.Net.I have inserted BLOB data using insert command, now i want to insert that BLOB via store procedure....any links/tips will be helpful...
View 1 Replies View Related
This is simple enough and I'm stumped. Why doesn't this work?
Code Snippet
use w
go
-- Create image warehouse
create table dbo.ImageWarehouse (
[ImageWarehouseID] int identity(1,1) primary key,
[ImageName] varchar(100),
Photo varbinary(max))
Go
-- Import image
Insert into dbo.ImageWarehouse
([ImageName]) values ('testingimage.gif')
update dbo.ImageWarehouse
set Photo =
(SELECT * FROM OPENROWSET(BULK 'c: estingimage.gif', SINGLE_BLOB)AS x )
WHERE [ImageName]='testingimage.jpg'
go
-- Check population
--delete from dbo.ImageWarehouse
select * from dbo.ImageWarehouse
go
-- Export image
declare @SQLcommand nvarchar(4000)
set @SQLcommand = 'bcp "SELECT top 1 Photo FROM w.dbo.ImageWarehouse WHERE ImageName = ''testingimage.gif''" queryout "c: estingimage_out.gif" -T -N -S [my server name]'
exec xp_cmdshell @SQLcommand
go
I successfully see
Code SnippetImageWarehouseID ImageName Photo
1 testingimage.gif 0x4749463839615802C (long hex string)
then
Code Snippet
output
NULL
Starting copy...
NULL
1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1
NULL
But testingimage_out.gif is unreadable. It's the same size (28k) as the original trestingimage.gif. Same result with a .jpg file. Try to open the file and it's just red X.
No SQL errors. Just result error. What am I overlooking?
I am having a problem with MMSQL BLOB with VB, Sorry to say I am new in Programming using VB 6 and MSSQL and I have never touch BLOB in my live.
I just wish anyone could give me any ideal, like, white pages, or manual on how do I insert BLOB data (Images) to MSSQL 2000 database using VB 6. I need to know exspecially the VB Code and the SQL Portion if you have a store procedure code for that it will be nice.
:confused:
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!
View 4 Replies View RelatedHi,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
View 3 Replies View RelatedHi,
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
Does this work? Is it correct what I am doing?
Thanks.
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.
View 1 Replies View RelatedCan 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
View 2 Replies View RelatedCan 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.
View 4 Replies View RelatedWhat's a good way to work with Blobs and TableAdapters, in terms of declaring compatible column types in SQL Server and DataTable fields?
View 2 Replies View RelatedWe 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
Hi,
I know in Mysql one can use a blob field(instead of varchar) to insert big amounts of data into a field - e.g User Notes or Long detailed descriptions
How do I create a field using Enterprise Manager(MSSQL) to be able to handle this amounts of data ???
I can't seem to find the BLOB-option upon creating a field in MSSQL, am I missing something here ???
Many thanks
Hello,
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
What is BLOB field in MS SQL? How to use insert and access from this field?
View 2 Replies View RelatedHi folks,
i have a problem with the result of my query.
There is a colum(a stored BLOB) witch appears like this:
8t¦–XEÄ
ð_JÑ |eÂ?ØÊ/TÅ® 0ù¯ó£1XðÃðpŸ8ãÖð'1ª@ˆ8^j֤K§ì©P=@Ÿú‡¿`T)áGä^ÄÞ˜ÖPfìrç(GC=8ȍ?‹T¼²¤÷ôœ1Ogu… J
K £J�8n1+CD"¹¯ÖE°-§¡�0(Ä¡‡6K? =L: Dþfþ¿€s¬ ð0ÀçPä]C·Y1ÄÅ¿(ßû‡Tâg@T $û?äþ0¯R@¥8NTÄhÄ “œã17D?i
I have a progam(QMF for Windows) in which i can selcet the colum and can chose bettween hexadezimal and binary.
Now my question: How can i make it works, that it is displayed as hexadezimal directly? An what is that above?
Many thanks in advance!
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
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.
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
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!
Hello all,
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?
Thanks...
- Ed
edh@americanid.com
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.
Any hepl would be appreciated.
Thank You.
Anthony
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 !!!
TIA
Hi,
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
Any ideas ????
Thanks
Hi,
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.
Thank you, Pepe.
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
View 4 Replies View RelatedI 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...
View 4 Replies View RelatedSay i want to send a blob in a message from queue A to queue B. These locations can be geographically different. How can i implement this ?
Also anything which i want to send as a message should be stored in the database. and then I should store it in a variable. this can be used as the message body. am I right ?