Retrieve BLOB From SQL Server

Jun 13, 2007

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.Connection
2         Dim rs As ADODB.Recordset
3         Dim mstream As ADODB.Stream

4         cn = New ADODB.Connection
5         cn.Open("Provider=SQLOLEDB;data Source=server;Initial Catalog=database;User Id='userid';Password='password'")

6         rs = New ADODB.Recordset
7         rs.Open("Select * from filesubmissions where bundleId = 'F0000014.bun'", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

8         mstream = New ADODB.Stream
9         mstream.Type = ADODB.StreamTypeEnum.adTypeBinary
10        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

 

View 2 Replies


ADVERTISEMENT

To BLOB Or Not To BLOB, That Is The Question.

Aug 1, 2006

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?

View 2 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

Problem With Blob Field In SQL Server

Feb 19, 2004

In SQLServer database i have stored a blob using updateblob function,
type of the table column is image.
When i use Selectblob statement to get the blob into a blob variable, i am getting only 32 KB of data. But with ASA i am getting the complete data..
How to get Complete data in SQL Server?

regds.,
ranga

View 1 Replies View Related

SQL Server - Data Type Blob

May 26, 2000

1. How many blob data types can be defined in a table on SQL Server 7.0

2. What is the max size for a blob data type.

View 1 Replies View Related

Saving BLOB Into SQL Server CE Database

Apr 8, 2008

Hi all,

I have a replicated table with a column which is used to store sound files - it is of type varbinary(MAX).

What is the best way to store a file from the filesystem inside this column on the PDA side (i.e. inside SQL Server CE) without loading the whole file into the PDA's memory, i.e. using some sort of buffering?

On full SQL Server 2005 I make use of UPDATE.WRITE() to save buffers inside the column - is there an alternative to this on SQL Server CE? Can anyone provide sample code / point me to samples that deal with SQL Server CE & BLOBs.

Thanks for your help!

View 11 Replies View Related

How To Free The Memory Occupied By Blob In MS Sql Server

Apr 18, 2006

i'm working in microsoft sql server and i got following problem:

I have a text files Asia.txt in E: folder with some data in it as shown below

Asia.txt

1, Mizuho, Fukushima, Tokyo
2, Minika, Pang, Taipei
3, Jen, Ambelang, India
4, Jiang, Hong, Shangai
5, Ada, Koo, HongKong

And I have a table Region, in the database Companies, as shown below.

1>CREATE TABLE REGION (ID INT,REGION VARCHAR(25),DATA varbinary(MAX))
2>GO

I queried all the data from Asia.txt, using the OPENROWSET function.

1>INSERT INTO REGION (ID, REGION, DATA)
2>SELECT 1 AS ID, 'ASIA' AS REGION,
3> * FROM OPENROWSET( BULK 'E:Asia.txt',SINGLE_BLOB)
4>AS MYTABLE
5>GO

it occupied some memory then i deleted this record using follwoing query

1>DELETE REGION
2>GO

then it deletes the record successfully but memory is not getting freed

can anyone help me out on this problem

View 4 Replies View Related

Load Blob Into DB Using A Stored Proc, SQL Server 2000

May 15, 2008



Hi,

I want to load a file into a blob column using a stored proc. The file will be on a UNC path on the network. Is there a system stored proc or any utility that can be fired off from the sp, using xp_cmdshell that will take a file path from the filesystem and load it into a blob column in a database table.

I have worked with blob fields in the past but in those instances I loaded the blobs into the database from front end ADO.net code. This time around I need to pull this off stricktly from a stored proc.

Any help will be appreciated.

thank you.

View 1 Replies View Related

SQL Server Admin 2014 :: Re-balancing Blob And Lob Data In Multiple Files On Primary File Group

May 7, 2014

A little background on what I am trying to achieve first. We are moving to Azure virtual machines and we will have 8 disks on the SQL Server box. I am adding more files to the primary file group and each file will go on its own drive. I am then rebalancing data across these files by rebuilding all of the indexes on the tables which is working fine. No problems so far all is good.

I now have an additional problem. If there is a lob or blob column on the table, rebuilding the clustered index and all the non clustered indexes doesn't rebalance the blob or lob data across the disks such as it does with in row data.

I cannot find any articles on rebalancing lob or blob data because all the articles say to move to a new file group. I do not want a new file group, I just want to use the primary file group where the data already resides, and just redistribute it evenly in the same way that I can in row data which is working fine.

One solution I thought about was to BCP data out of the table, truncate the table and then BCP back into the table which I imagine would have the desired effect of distributing the data evenly over the files.

View 2 Replies View Related

Right Way To Retrieve Data From Sql-server

Mar 19, 2007

Last night I face performace problems in web site. I got like 100 000 users and site fails because of sql-connections.
Currently I'm making a new connection for every query, and my code looks like this:Public Shared Function executeQueryReturnDataset(ByVal strsql As String) As DataSet
Dim sqlConnection As New SqlConnection(Connstr)
Dim sqlCommand As New SqlCommand(strsql, sqlConnection)
Dim sqlDataSet As New DataSet

Try
sqlConnection.Open()
Dim myDataAdapter As New SqlDataAdapter
myDataAdapter.SelectCommand = sqlCommand
myDataAdapter.Fill(sqlDataSet)
Catch e As Exception
Console.Write(e.ToString())
Finally
sqlConnection.Close()
End Try
Return sqlDataSet
End Function I was wondering would it be better to save an instance from connection object to memory and use that same connection for all querys?

View 4 Replies View Related

How To Retrieve SqlDbType.Bit In SQL Server?

Sep 29, 2004

Hi,

My checkbox status is saved as SqlDbType.Bit in MS SQl Server database.
When I use the following code to retrieve it, it does not work.

myRow[myColumn].ToString()=="1"

I appreciate your kind assistance.

Thank you.

View 1 Replies View Related

How To Retrieve Data From SQL Server?

Mar 14, 2006

Let us suppose that there are 900 rows in One table, and that table contains neither primary key, nor Identity column.
How can I retrieve all the roows from 201 to 250?(like the middle limits)
In oracle there is a property called RowID, but is there any such item in SQL server?

View 1 Replies View Related

How To Retrieve Userid And Pwd Of Sql Server

Oct 17, 2007

I am writing a VBscript which takes my machine details and put in the SQL database.

But after executing the script , i got the following error: Microsoft OLE DB Provider for SQL Server: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

I dont remember the password I have given during installation.
Please tell me how to get the password of sql server.

View 2 Replies View Related

SQL Server -- How Do I Retrieve Mail?

Aug 17, 2005

So here's my dilemma. I need an automated SQL Server (2000) job thatruns once once per hour.This jobs should:1) Retrieve unread email for the user "Le" from our Exchange Server,and insert it into a table.2) Retrieve unread email for the user "Smith", from from our ExchangeServer, and insert it into a table.3) Retrieve unread email for the user "Carrie", from from our ExchangeServer, and insert it into a table.My initial thought is to create a DTS package to handle emailretrieval, and insert it into the table in the same step.But I have no idea how to get started. How do I get DTS/SqlServer toread mail for these three users?`Le

View 3 Replies View Related

To BLOB Or Not To BLOB

Dec 15, 2005

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
 

View 15 Replies View Related

Regarding BLOB

Nov 11, 2005

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 Related

Using BLOB

May 17, 2006

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

View 3 Replies View Related

BLOB In T-SQL

Jul 21, 2006

Hi,

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.

View 4 Replies View Related

How To Retrieve Data From SQL SERVER 2000 ?

Nov 25, 2005

Hello,
Im using Visual Studio 2005 to code ASP.NET
How to query my MS SQL SERVER 2000 to retrieve data from my DataBase?
Ive tried some tutorials, but i kept giving me errorsCan you put a simple source code to retrieve something like "select userID, Name, Age from Users"
Thank you,
 

View 2 Replies View Related

Retrieve Names Of Users From Sql Server

May 24, 2007

how to retrieve names of the users between start time and end time using stored procedure in sql server??

i have a table which stores user names along with other details.
can anybody tell me how to get the names of the users between start time and end time??

View 8 Replies View Related

Cannot Retrieve Data From DB When Using SQL Server Authentication

Apr 14, 2008

I am developing a commercial solution for scheduling problems, using different databases. Several of our clients don't have a database server installed in their systems networks, so we thought we would simply use SQL Server Express as a no-cost solution. However, I have come across a MAJOR difficulty:
When I try to log on using SQL Server Authentication (for example, name: "sa" pass: "sa") instead of Windows Authentication, I am simply unable to view any data that does not come directly from a table! No data from queries(!), no data from stored procedures(!!), no data from functions(!!!), only from tables!!!!
And it's not like the queries don't return a dataset, because they do, for a user logged on using Windows Authentication!
And this is not 'simply' a problem of remotely connecting to SQL Server Express, this is also true locally!
There is nothing wrong with my own system, this problem occurs on at least four different windows platforms, vista included. And it is not related to our program, I am having the same difficulty with the Management Studio Express.
I have tried to use several different users, each with different permissions and roles, to no avail.
This is truly a frustrating problem.
It can be solved in one of two ways:
1. Get a non-windows-authenticated user to see the data (get more than 0 rows in the dataset).
2. Manage to log on remotely using Windows authentication.

I am unable to do either one of those, and not for lack of trying. I have been at this for the past week without results.

PLEASE, PLEASE, PLEASE, Can anyone HELP?!?!?!?

View 5 Replies View Related

How To Retrieve And Display Data From SQL-server?

Feb 15, 2008

Hello,

I need to retrieve data from a SQL-server table using a stored procedure. I want to retrieve the values from the table and add them to labels on a form. What is the easiest way to do this? Is dataset the solution?

I appreciate any help!


string sCN = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

SqlConnection sqlConnection2 = new SqlConnection(sCN);





SqlCommand cmd = new SqlCommand();

cmd.CommandType = System.Data.CommandType.Text;

cmd.Parameters.AddWithValue("@userID", userID);



Int32 rowsAffected;

cmd.CommandText = "get_user"; //Stored procedure to get user data. Takes UserID as parameter

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = sqlConnection2;



sqlConnection2.Open();

rowsAffected = cmd.ExecuteNonQuery();

sqlConnection2.Close();

View 20 Replies View Related

How To Retrieve Video File From Sql Server Database

Apr 27, 2007

Hi
I uploaded a video file into the sql database converting into binary data.
i have a problem that to  retrieve video file(.wmv) from sql server 2000 database   and which has to play on media player.
Plz help me any one .
Itz urgent for my website.............
Plz plz Plz Help me Urgent...................
Thanks in Advance
Regards Shiva

View 11 Replies View Related

How To Load Image To And Retrieve From Database(sql Server)

Feb 19, 2005

can anyone out there help me to solve this problem???

how to load image to and retrieve from database(sql server)??
thanks alot

cyndie

View 3 Replies View Related

How To Retrieve 100,000 Records From Active Directory Using SQL Server

Nov 24, 2004

Hi,

I am new to Microsoft Technologies, can any one say how to connect/ retrieve data's from Active Directory through SQL SERVER from scratch. I need with some sample codes.....

Thanks in advance

raj d.s

View 1 Replies View Related

How To Retrieve Image Stored In SQL Server From My VB Project

Nov 29, 2001

Hi anyone can help me? i would like to retrieve an image stored in my SQL server from my VB project using ADO... anyone familiarise in it? pls help me... i need it urgently..... tks...

View 1 Replies View Related

How Do I Retrieve Table Properties In MS SQL Server 2000

Nov 29, 2004

I have looked on google and haven't found a query (as of yet) to perform this function.

Essentially I am using VB.NET with Excel and have a mapping between a worksheet and a table in my database. I wrote an import function to pull the data out of excel and put it into SQL Server but I want to try catching errors before i do that.

What is the SQL query to get column sizes from a table. Meaning in a table I have column1 that is allowed a size of int(5). How do i retrieve that information from a query opposed to just looking at it in SQL Server EM??

any help would be appreciated
thanks

View 5 Replies View Related

SQL 2012 :: Retrieve Binary File From Server

Apr 14, 2014

I have been trying to store binary file in a folder from the SQL Server.

Here is the code I am using to do this but, it is not working. It doesn't show any error only shows 1 row(s) affected. The folder remains empty after running this query.

I have already configured server using

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE

SP_CONFIGURE 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO
DECLARE @File VARBINARY(MAX),

[Code] .....

View 3 Replies View Related

Via SQL Server How To Retrieve Text Message That Is In Tab Messages

May 30, 2008

Hi,
To be more clear. My problem is :
1) I launched the stataments behing via sql server studio :
Use AutClust
GO
SET NOCOUNT ON;
Set statistics IO ON
GO
select * FROM table_1;
Go
Set statistics IO OFF
GO

2) I have then obtained in tab message the result displayed in text format in tab messages :
Table 'table_1'. Scan count 1, logical reads 490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The objective is to implement those statements in a stored procedure that will be able to recover in automatic manner the content of tab messages.
This text is a system response connected to the statements above. I think that exist a system variable which contain the result displayed in tab messages.

I tried tu use @@rowcount it deos not work because this system variable return number of row affacted by last statement.

Does it exist another system variable that could contain the text which is in tab messages?

I do need a solution for this problem.
Please bring me your help.

Thanks for your help.

Tietie.

View 2 Replies View Related

Retrieve Data From Web Services Using DTS (SQL Server 2000)

May 2, 2006

The project I'm currently working on involves combining data from one SQL Server 2000 databases and XML returned from a web service into a 3rd SQL Server 2000 database.

This process must be scheduled to happen once a day. If it weren't for the Web Service, I'd say that this is a no-brainer and I'd use DTS. However, I'm not sure if I can even access a Web Service with DTS. Has anyone done this or have any tips?

View 1 Replies View Related

Via SQL Server How To Retrieve Text Message That Is In Tab Messages

May 29, 2008

Hi,
I execute a query via queries editor embedded in sql srver studio.then i have query result in results tab and a text message in tab messages. Does it exist a T-SQL sys global variable as of i could to retrieve the text message that is displayed in tab messages?

To be more clear. My problem is :
1) I launched the stataments behing via sql server studio :
Use AutClust
GO
SET NOCOUNT ON;
Set statistics IO ON
GO
select * FROM table_1;
Go
Set statistics IO OFF
GO

2) I have then obtained in tab message the result displayed in text format in tab messages :
Table 'table_1'. Scan count 1, logical reads 490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The objective is to implement those statements in a stored procedure that will be able to recover in automatic manner the content of tab messages.
This text is a system response connected to the statements above. I think that exist a system variable which contain the result displayed in tab messages.

I do need a solution for this problem.
Please bring me your help.

Thanks for your help.

Tietie.


Thanks in advance.

Tietie

View 3 Replies View Related

SQL Server Setup Failed To Retrieve The SSL Certificate Name

Sep 19, 2005

 When I try to install SQL 2005 Developer September CTP on Windows XP Professional  it returns an error "SQL Server Setup failed to

View 21 Replies View Related

Export BLOB To .JPG

Aug 31, 2007

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 Related







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