hi,
i learned that BLOB data types are out-row data by default, i think it means it will not store in the row of table, because the max-length of a col is 8KB.
but will the system store those BLOB data? and is there any way to specify where should it be stored, like tha Partition Schema.
i am using SQL Express, so i hope it works for express.(partition does not work for express)
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 RelatedHi,
My package dumps the errors into a table. The problem is, it couldnt dump Error Output column to a varchar field. I have added an script component in between to transform to string but no success.
I tried ErrorOutput.GetBlobData(0, ErrorOutput.Length)
but when I query the database, it says "System.Byte[]'
I will appreciate the responses to this post.
Thankyou,
Fahad
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
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 RelatedDoes 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 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 RelatedHi, I have to migrate an oracle database over to SQL Server 2005. One of the tables has PDF files stored as BLOB. All the ORACLE providers in SSIS do not support BLOB column. What can I use or how can I get the PDF files into my SQL table? The SQL column can be either IMAGE or VARBINARY(MAX).
Thanks,
Les
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.
Hi,
I have a problem with writing BLOB data into type of 'image' field.
If the data size > ~280 MByte (< 2 GB) the operation failed.
It is appear on SQL Server 2000 and 2005 (MSDE and express also).
Detailes :
column type : image
Server : SQL Server 2005, version - 9.0.3042
technology : OLEDB, from C++ -> CDynamicAccessor
protocol for communication : TCP/IP
Size of the DB : ~3 MByte.
As I see the provider read partially (~267 943 936 byte if all is 280 MB) of data from my ISequentialStream.
Error of OLEDB:
DB_E_ERRORSOCCURED : 0x80040e21
[DBNETLIB][ConnectionWrite(send()).]
General network error.
SQL Server error log entries :
10/05/2007 16:52:53,Server,Unknown,A fatal error occurred while reading the
input stream from the network. The session will be terminated.
10/05/2007 16:52:53,Server,Unknown,Error: 4014<c/> Severity: 20<c/> State: 1.
Entries of SQL profiler :
Error: 4002, Severity: 16, State: 4
The incoming tabular data stream (TDS) protocol stream is incorrect. The stream ended unexpectedly.
I used to set up the bug with contents of a ~350 MB .zip or .mdf file. Everyting is ok when the data <= ~250 MB.
I had ran MS Network Monitor on the server side and not found partially transmitted frames (but I use it on first time).
We try it on another machines, on another LAN with other BLOB (and the bug arised).
I had try 'named pipes' protocol for communication (and the bug arised).
I will be glad if somebody maintain that how can I found the origin of the bug (tools, log options, protokol settings etc.) or
I can present my simple test program (VC 2005 project) and its database (MS SQL 2005).
Best Regards,
Imre
How do I use the GetBlobdata to get the string representation?
I have tried numerous version of Encoders thinking that
System.Text.Encoding.GetEncoding(Row.TextData.ColumnInfo.CodePage).GetString(b) (where b is the byte array returned by GetBlobData)
But this doesn't work. I have had to resort to converting the field in SQL, so I don't have to deal with blobdata.
Hi all,
I am trying to store binary datafiles into a SQl Express table. However I can not find the BLOB data type when I am designing the table. Are we limited to using that kind of data type in the Express edition? Thanks.
Ke
Hi experts,
I have a textbox and a upload file function in my asp.net page.User can either copy/paste their resume in text or upload their resume file and submit the application.The uploaded file will be saved into a BLOB column, but do you know if text in textbox can be saved into BLOB column?
I received error message on the code:'save Applicant resume to a BLOB-image datatype column objComd.Parameters.Add(New SqlParameter("@AppResume", SqlDbType.NText))
error:Exception has been thrown by the target of an invocation.Operand type clash:ntext is incompatible with image
Hello,
I have SQL Server 2000 and I'm trying to import data from a paradox v5 database. Everything has moved over fine but the data from the blob fields. When I use the import tool and try connect to a paradox blob field, the import wizard does not "see" the paradox blob field. here is and example.
If I have a table with the following fields.
id char(20)
description char(120)
image blob
the ms-sql import wizard sees
id char(20)
description char(20)
I find this very odd, but I do need a solution. Any Ideas?
TY
I need some assistence here.I have two servers. One with MS SQL 2000 Standard edition and MSDE(MSdesktop engine).I need to sync my databases nightly, as a backup.Is there a cheap software that will do this? I don't want to justbackup the entire databases files b/c they are large and I'mtransfering them over DSL.I was thinking transaction logging, but I'm not sure. Is there a easyway to do this?I don't know how to accomplish this task.PLEASE HELP.
View 9 Replies View RelatedHow to access the XML, CLOB and BLOB Oracle data through linked server from SQL Server 2012?
I want to retrieve XML, CLOB and BLOB data from SQL Server 2012 through Oracle Linked server .
I am using SqlServer 2000 and Jdbc connectivity in my application on windows.So, when i execute a query on Sqlserver database as :
Select * from DBO.RS_TABLELOB where Rep_sync_id > 15 and Rep_server_name != 'replicator' order by Rep_sync_id;
This problem is only with the BLOB data types in table RS_TABLELOB.
Then i got an error as:
[Microsoft][SQLServer 2000 Driver for JDBC]ResultSet can not re-read row data for column
1.'.'.at com.microsoft.jdbc.base.BaseExceptions.createExcep tion(Unknown Source)at
com.microsoft.jdbc.base.BaseExceptions.getExceptio n(Unknown Source)at
com.microsoft.jdbc.base.BaseResultSet.validateColu mnIndex(Unknown Source)at
com.microsoft.jdbc.base.BaseResultSet.getObject(Un known Source)at
com.microsoft.jdbc.base.BaseResultSet.getObject(Un known Source)
Please help me out, if you have any idea!!!
Thanks in advance
John Berry
Hi! I tried to save some image data, but it get truncated at 8000 (the table column is defined as Image). I then wrote a converter to try ntext-datatype instead, but it gets truncated at 4000.
Error message:
System.Data.SqlServerCe: @3 : String truncation: max=4000, len=4168
The code uses only ADO entity framework for database access. Is there a way to store binary data larger than 8000 bytes? I am running SQL Compact 3.5 sp 1 BETA.
Henning
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?
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.
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:
I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?
hi my friends;i have got a problem.i thing you can help mea='x80x02}qx00(Kx02Kx03Kx04Kx06Kx05Kx07u. 'i want to store database but i don't know which encoding and dataytpepls help mesorry for my bed english...thanks all
View 3 Replies View RelatedHow to purge data in transaction table or we can delete some data and store in separate table in data warehouse?
View 7 Replies View Relatedwe need to decide an architecture for Performance on a web site Search! I wanna use text service of SQL 2005 .But I am worried about the performance .... How should I design the system if I want the best perfomance and scalability ?1.Should I build a seperate coloumn in my every table and merge all the information into one coloumn and full text index that column.2.Put a full text index in all column in the table and use OR clause and reverse rank it for AND clause,using CONTAINSTABLE function.3.Make a different table and put _ID,_TYPE and _VALUE fields and search in that table with less coloumns.4.Seperate the full text database and search in a seperate db so that I can scale better?did anybody have a similiar problem ? Any books on full text search ?
View 4 Replies View RelatedHi
I have this stored procedure using Table Variable.
-------------------------------------------------------------------------------------------------------------------------------
create procedure sp_matching @pid Int
ASdeclare @tbl table (MAID int, image varchar(100))
insert @tblselect MAID, image from productswhere pid in (select top 10 mid from matching1 where pid=@pid order by newid())
select * from @tbl
GO
---------------------------------------------------------------------------------------------------------------------------------
When I run this sp, i have table with 10 rows. But what I really need to do is to store all data in one row like:
PID, MAID1, Image1, MAID2, Image2, ..... MAID9, Image9, MAID10, Image10
How can I do this? Please help me out for this.
Thanks.
Hi friends I want to add HTML to the SQL server as data please can anyone help me. which data type . & how to store the data. Thanks for all your support Thanks & regardsASPFreak
View 3 Replies View RelatedHi all,
please, show me How to data is store on the server ?
thanks for reading.
Greetings All, I was wondering if any of you would share some of yourexperiences regarding the task of loading a data store from an Oltpsource. We are using Analysis Services in a BI product that requiresdata to be pulled from one of our products, an OLTP database. Thedesign is to first run an ETL process from the OLTP source into anoperational data store, from here Analysis Services will pull its datato do its thing. Now, for small OLTP databases (< 1Gb) the storedprocs I have written to do the extraction works well, it is relativelyfast and efficient. However, we have a few databases that are 10Gb'sand the load could end up taking several hours. During this long loadthe OLTP source may be in use and I want to avoid write blocks, or if Iwere to use "select ... NOLOCK" I could get dirty data brought over. Icould used BCP for some of the big tables or Bulk Copy but I wanted tosee if anyone has dealt with this issue and what their specificresolution was for their specific problem. It is my hope that byseeing how others have dealt with this I will be able to architect asolution for my specific problemRegards, TFD.
View 1 Replies View RelatedHi,
This is more store procedure:ALTER PROCEDURE dbo.InsertSpecialOrders
(@OrderID int,
@DepotName nvarchar(50),@CustomerName nvarchar(50),
@OrderDate nvarchar(50),@TelephoneNumber nvarchar(50),
@ObtainedFrom nvarchar(50),@CustomerCanCollect nvarchar(50),
@DepositPaid nvarchar(50),@PriceQuoted nvarchar(50),
@OrderTakenBy nvarchar(50),@BalancePaid nvarchar(50),
@Status nvarchar(50),@TransferedBy nvarchar(50),@CarriagePrice nvarchar(50)
)
AS
Declare @LatestID Int
INSERT INTO Specials
(
OrderID,
DepotName,
CustomerName,
OrderDate,
TelephoneNumber,
ObtainedFrom,
CustomerCanCollect,
DepositPaid,
PriceQuoted,
OrderTakenBy,
BalancePaid,
Status,
TransferedBy,
CarriagePrice
)
VALUES
(
@OrderID,
@DepotName,
@CustomerName,
@OrderDate,
@TelephoneNumber,
@ObtainedFrom,
@CustomerCanCollect,
@DepositPaid,
@PriceQuoted,
@OrderTakenBy,
@BalancePaid,
@Status,
@TransferedBy,
@CarriagePrice
)
SELECT @LatestID = Scope_Identity()
INSERT INTO SpecialParts
(
OrderID,
PartNo,
Quantity
)
VALUES
(
@LatestID,
@DepotName,
@CustomerName
)
RETURN
Now I want to insert data and create the tables:
SqlDataSource ordersDataSource = new SqlDataSource();ordersDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["SpecialsConnectionString1"].ToString();
ordersDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
So to insert data for the first field I need:
ordersDataSource.InsertParameters.Add("@CustomerName", CustomerName.Text) Is this right? Any help is appreciated.
HI,I anm geting error when i want to store some text which contens single quote like this Hi I am 'santosh'.as i am using text editor which genetates XML data (not pure) so i have used varchar(max) to store the data but it gives error.
Is thier any way to store text with single quote........
urgent plz.
how to use class iDataReader's function getbytes(...............) to read binary data?
using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;using System.Data.Common;using System.IO;
public partial class uploadFileToDatabase : System.Web.UI.Page{ string strUser; protected void Page_Load(object sender, EventArgs e) { strUser=Request.QueryString["download"]; if(strUser!=null) { string strConn="Data Source=hao-pc;Initial Catalog=simpleBBS;Persist Security Info=True;User ID=sa;Password=123;Pooling=False"; SqlDataAdapter sda=new SqlDataAdapter(); DbProviderFactory dbProviderFactory=DbProviderFactories.GetFactory("System.Data.SqlClient"); DbConnection dbConn=dbProviderFactory.CreateConnection(); dbConn.ConnectionString=strConn; dbConn.Open(); DbCommand dbComm=dbProviderFactory.CreateCommand(); dbComm.Connection=dbConn; dbComm.CommandText="select * from userFiles2 where fileisn='"+strUser+"'"; IDataReader myReader=dbComm.ExecuteReader(); int strFileLen=0; if(myReader.Read()) { strFileLen=Convert.ToInt32(myReader.GetString(3)); FileStream afs; string fPath; fPath = Server.MapPath(Request.ApplicationPath) + "\upLoadFiles\" + myReader.GetString(1); afs = new FileStream(fPath, FileMode.OpenOrCreate, FileAccess.Write); Byte[] filedata = new Byte[strFileLen]; afs.Write(filedata, 0, strFileLen); BinaryWriter abw; int iBufferSize=1000; Byte[] outbyte=new Byte[iBufferSize-1]; long retval; long starIndex=0; abw=new BinaryWriter(afs); starIndex=0; int itemNum=4; retval=myReader.GetBytes(itemNum,starIndex,outbyte,0,iBufferSize); //when i store large data into a file it display an error such as "Buffer offset '0' plus the bytes '82452' is greater than the length of the passed in buffer. abw.Write(outbyte); starIndex+=iBufferSize; retval=myReader.GetBytes(itemNum,starIndex,outbyte,0,iBufferSize); } abw.Write(outbyte); abw.Flush(); abw.Close(); afs.Close(); myReader.Close(); dbComm.Dispose(); } } } protected void Button1_Click(object sender, EventArgs e) { string strFileName; string strFileType; int strFileLen; Random ran=new Random(); string strFileISN=DateTime.Now.ToString("yyyyMMddhhmmss")+ran.Next(0,999); if(FileUpload1.PostedFile.FileName!=null) { strFileName=FileUpload1.PostedFile.FileName; strFileType=strFileName.Substring(strFileName.LastIndexOf(".")+1); strFileName=strFileName.Substring(strFileName.LastIndexOf("\")+1); strFileLen=FileUpload1.PostedFile.ContentLength; string strConn="Data Source=hao-pc;Initial Catalog=simpleBBS;Persist Security Info=True;User ID=sa;Password=123;Pooling=False"; SqlDataAdapter sda=new SqlDataAdapter(); DbProviderFactory dbProviderFactory=DbProviderFactories.GetFactory("System.Data.SqlClient"); DbConnection dbConn=dbProviderFactory.CreateConnection(); dbConn.ConnectionString=strConn; dbConn.Open(); DbCommand dbComm=dbProviderFactory.CreateCommand(); dbComm.Connection=dbConn; string dateTime=DateTime.Now.ToString(); Stream fs=FileUpload1.PostedFile.InputStream; Byte[] filedata=new Byte[strFileLen]; fs.Read(filedata,0,strFileLen); dbComm.CommandText="insert into userFiles2 (fileisn,filename,filetype,filesize,filecontent,uploadTime) values (@fileisn,@filename,@filetype,@filesize,@filecontent,@uploadTime)"; dbComm.Parameters.Add(new SqlParameter("@fileisn", (object)strFileISN)); dbComm.Parameters.Add(new SqlParameter("@filename",(object)strFileName)); dbComm.Parameters.Add(new SqlParameter("@filetype",(object)strFileType)); dbComm.Parameters.Add(new SqlParameter("@filesize",(object)strFileLen.ToString())); dbComm.Parameters.Add(new SqlParameter("@filecontent",(object)filedata)); dbComm.Parameters.Add(new SqlParameter("@uploadTime",(object)dateTime)); dbComm.ExecuteNonQuery(); fs.Close(); dbConn.Close(); dbComm.Dispose(); dbConn.Close(); } } }
I have a recursive query, using common table expressions, like this:
WITH TaskHierarchy (GUID, ParentGUID, Title, Complete, HierarchyLevel)
AS
(
SELECT GUID, ParentGUID, Title, Complete, 1 HierarchyLevel
FROM Task
WHERE ParentGUID = @GUID
UNION ALL
SELECT t.GUID, t.ParentGUID, t.Title, t.Complete, th.HierarchyLevel + 1 HierarchyLevel
FROM Task t
INNER JOIN TaskHierarchy th
ON t.ParentGUID = th.GUID
)
SELECT (COUNT(*) - SUM(CAST(Complete AS INT))) Outstanding FROM TaskHierarchy
The result is a number. I need access to this number. Ideally, I would like to store it in a variable, but anything would work as long as I can access it after the query.Anyone know of a way?