Sending Large Binary To SQL Server Via ADO.NET

Feb 27, 2007

Hi,
I am having some trouble with my ASP page sending a file to a SQL Server 2005 database running on another machine. An exception is generated as follows:
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) 
 It seems to work ok with smaller files, but when I attempt to upload a 11MB file, it gives me the above exception. I upped the httpRuntime maxRequestLength in the web.config to 2048.


 Any Ideas?
 
Here is my code:1 if (FileUploader.HasFile)
2 {
3
4 // call the stored proc
5 SqlConnection conn = new SqlConnection();
6 conn.ConnectionString = "Password=password;Persist Security Info=True;User ID=MyUser;Initial Catalog=MyDb;Data Source=MySqlServerMachine;Connect Timeout=300";
7 conn.Open();
8
9 SqlCommand cmd = new SqlCommand();
10 cmd.Connection = conn;
11 cmd.CommandType = CommandType.StoredProcedure;
12 cmd.CommandText = "spAddTestBinary";
13
14 SqlParameter param = new SqlParameter();
15 param.ParameterName = "@binaryParam";
16 param.SqlDbType = SqlDbType.VarBinary;
17 param.Direction = ParameterDirection.Input;
18 param.Value = FileUploader.FileBytes;
19 cmd.Parameters.Add(param);
20
21
22 cmd.ExecuteNonQuery();
23
24
25 conn.Close();
26 }
 
 
Here is my table:

1 BinaryTable(
2 [id] [int] IDENTITY(1,1) NOT NULL,
3 [myBinary] [varbinary](max) NULL,
4 CONSTRAINT [PK_BinaryTable] PRIMARY KEY CLUSTERED
5 (
6 [id] ASC
7 )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
8 ) ON [PRIMARY]

My stored proc:1 ALTER PROCEDURE [dbo].[spAddTestBinary]
2 -- Add the parameters for the stored procedure here
3 @binaryParam varbinary(MAX)
4 AS
5 BEGIN
6 -- SET NOCOUNT ON added to prevent extra result sets from
7 -- interfering with SELECT statements.
8 SET NOCOUNT ON;
9
10 -- Insert statements for procedure here
11 Insert into BinaryTable
12 (myBinary)
13 Values
14 (
15 @binaryParam
16 )
17 END
 

View 3 Replies


ADVERTISEMENT

Read Large Binary Data From Sql Server 2005

Jul 14, 2007

Hi I've followed a tutorial on how to write and read varbinary(max) data to and from a database. But when i try to read the data i get the error that the data would be truncated, but only when the varbinary(max)  is greater then 8kB. I've used a system stored procedure (sp_tableoption) to set the table that holds the data to store data outside rows. To select the data i'm using a stored procedure:               SELECT imageData , MIMEType FROM Pictures WHERE (imageTitle = @imageTitle)        And then using an .aspx page to Response.Write the data:Using conn As New sql.SqlConnection            conn.ConnectionString = ConfigurationManager.ConnectionStrings("myConnectionString").ToString            Dim getLogoCommand As New sql.SqlCommand            getLogoCommand.CommandType = Data.CommandType.StoredProcedure            getLogoCommand.CommandText = "GetPicture"            getLogoCommand.Connection = conn            Dim imageTitleParameter As New sql.SqlParameter("@imageTitle", Data.SqlDbType.NVarChar, 200)            imageTitleParameter.Value = Request("imageTitle")            imageTitleParameter.Direction = Data.ParameterDirection.Input            getLogoCommand.Parameters.Add(imageTitleParameter)            conn.Open()            Using logoReader As sql.SqlDataReader = getLogoCommand.ExecuteReader                logoReader.Read()                If logoReader.HasRows = True Then                    Response.Clear()                    Response.ContentType = logoReader("MIMEtype").ToString()                    Response.BinaryWrite(logoReader("imageData"))                End If            End Using            conn.Close()        End Using  Can anyone please help me with this?!

View 2 Replies View Related

SQL Server 2008 :: Large Binary Dataset - Database Or File System?

Jun 2, 2015

I have a well-structured but also very large binary data-set that is generated by a C++ application every five minutes. The data needs to be accessed by SQL applications. Since data is generated every five minutes, performance is key, both for write and read. The data set is about 500MB.If data is written to the file system, the write performance doesn't involve SQL server. For reading it, I have a CLR to read the portions of the data that I need based on offset and length. That works and is very fast. The problem is that data is stored in the file system, so it is not self-contained within the database.

A second option that I haven't explored yet, is to write the data into a table as VARBINARY(MAX). I would read the data using SUBSTRING with appropriate offset and length. Performance of SQL write/read of binary data of this size, and whether there is a third option I haven't thought off. I'm using SQL Server 2014.

View 5 Replies View Related

Large Binary Writes Slower Than Expected

Jul 23, 2005

Hello, we are investigating the use of SQL Server as a backend to ourscientific imaging application. We have found that when we write alarge image (60 Megabytes) the performance is quite a bit slower thanwriting 60 single megabyte images. The tests were performed runningSQL Server 2000 on Windows 2003 Enterprise on a single machine toeliminate the network's contribution. Perhaps there is a configurationoption that will allow us to tune SQL Server to better handle largewrites?TIA

View 1 Replies View Related

VERY Large Binary Import/export Headache

Oct 13, 2006

Hi,

I am currently importing (and exporting) binary flat files to and from Db fields using the TEXTPTR and UPDATETEXT (or READTEXT for export) functions. This allows me to fetch/send the data in manageable packet sizes without the need to load complete files into RAM first.

Given that some files can be up to 1Gb in size I am keen to find out a new way of doing this since the announcement that TEXTPTR, READTEXT and UPDATETEXT are going to be removed from T-SQL.

I had a quick foray into SSIS but couldn't find anything suitable which brings me back to T-SQL. If anyone knows a nice elegant way of doing this and is prepared to share, that would be grand.

Thanks for your time,
Paul

View 9 Replies View Related

How To Store Large Chunks For Binary Data Into The DB?

Sep 7, 2006

From what I can see, the 'varbinary(max)' data type is not supported, and the 'image' data type is supposed to go away. Is there some other way to store large chunks (10MB to 100MB) of data into an SSEv DB?

If I have to use the 'image' data type to so this, does anyone have a code sample that would let me push an array() of numbers into an 'image' field, and unload an 'image' field into an array()?

TIA

Pat

View 7 Replies View Related

Problem In Converting MS Access OLE Object[Image] Column To BLOB (binary Large Object Bitmap)

May 27, 2008

Hi All,
i have a table in MS Access with CandidateId and Image column. Image column is in OLE object  format. i need to move this to SQL server 2005 with CandidateId column with integer and candidate Image column to Image datatype.
its very udgent, i need any tool to move this to SQL server 2005 or i need a code to move this table from MS Access to SQL server 2005 in C#.
please do the needfull ASAP. waiting for your reply
with regards
 
 
 

View 1 Replies View Related

Concatenate All Binary Columns Into Single Binary Column?

May 22, 2014

Server is SQL 2000

I have a table with 10 rows with a varbinary column

I wish to concatenate all the binary column into a single binary column and then write that to another table within the database. This application splits a binary file (Word or PDF document) into multiple segments (this is Column2 as below)

example as follows

TableA

Column1 Column2 Column3
aaa 001 <some binary value>
aaa 002 <some binary value>
aaa 003 <some binary value>
aaa 004 <some binary value>
aaa 005 <some binary value>

desired results in TableB

Column1 Column2
aaa <concatenated value of above binary columns>

View 9 Replies View Related

T-SQL (SS2K8) :: Store Binary Data Rather Than Int Or Binary?

May 7, 2015

I'm using a bit-wise comparison to effectively store multiple values in one column. However once the number of values increases it starts to become too big for a int data type.you also cannot perform a bitwise & on two binary datatypes. Is there a better way to store the binary data rather than int or binary?

View 9 Replies View Related

Sending Mail Through SQL Server

May 9, 2001

I need help regarding how to send mails through SQL server. Let me give you some more details: I am having a table where in all the e-mail id's are stored so i need to write a stored procedure that will pick up the email id from the table and it has to send the mail once you run the stored procedure.
Any help in this regard will really help...pls help me out...

View 1 Replies View Related

Sending Email From SQL Server

Nov 29, 2000

Does anyone know how to send emails from within a trigger without the use of a mail server. I know that xp_sendmail can be used if a mail server, such as MS Exchange Server is available and SQL Server is set up as an e-mail client. However is there a way to send mail to the outside world without the use of a mail server? (Perhaps there is a third party product or extended stored procedure that can give me this ability?)

Many thanks for any response.

View 1 Replies View Related

Sending SMS From Sql Server 2005

Jun 18, 2008

Hi

How can i send sms to mobile phone from sql server 2005.
Is it possible!

Regards
Js.Reddy

View 1 Replies View Related

Sending Mail From Sql Server?

Jun 11, 2007

Hi,

i want to send Mail from sql server.which means that when i execute an query a mail will send to mentioned e-mail.is it any predefined function is there?.if,yes,can u explain Detail.

Regards
Umapathy

View 2 Replies View Related

Sending Email From Sql Server

Jul 20, 2005

HiTrying to send email from sql server seems a very hard task, can iteven be done without using exchange? all examples I can find relies onexchange but I would rather send it to any SMTP server since we do notuse exchange.rgdsMatt

View 2 Replies View Related

Sending An E-mail Via SMTP In SQL Server

Jul 20, 2005

Hello,Does anyone know how I can set SQL Server up so that after it hascompleted a backup sucessfully it will send the completion report toan e-mail address via SMTP.Unfortunatly we cannot install MS OUTLOOK Express or similar.Is this possible?Many thanks in advance,Allan Martin

View 2 Replies View Related

Sending Mail Using SQL Server 2000

Dec 19, 2007

Hello Friends


I want to send mail using Stored Procedure using SQL Server 2000 using windows smtp component. I don't want third party component.

can any body send Stored Procedure.

View 1 Replies View Related

Sending Message With Sql Server 2005

May 3, 2006

I have a need to asynchronously send a short message to an application over


tcp/ip after the user makes a selection from a web browser.





We are developing a reservation system where the availability to users in


close physical proximity is indicated by lights at a kiosk. We want the user


to be able to make a reservation from a web browser over the internet, (which


checks the sql server db for availibility), and when the reservation is made,


the appropriate lights at the kiosk indicate it is unavailable.








I can think of three choices, but am unsure which is best:





1. Poll the server to look for the event. I don't like this since the


application will be connected over a dial up internet connection, and I want


to keep traffic to a minimum, although this seems the easiest to build.





2. Have the users web browser be redirected to the application's ip address


directly. Here I am not sure which message protocol to use. The kiosk


application will be a .net 2.0 windows form app, but not running on a server


per se, but windows xp pro.





3. Have the database relay a message to the kiosk. I like this way the best,


since the user does not need to know the ip address of the kiosk, and the


kiosk does not need to be open from all ip addresses for security reasons. I


am not which way to do this either. I have read a little about hosting web


services using http.sys, but we probably want a shared hosting plan due to


costs at this point, and they are restrictive about turning on the clr inside


the db. Is there a different way to send a short message?





I apologize in advance if this is the wrong forum.

View 1 Replies View Related

Sending Mails Through SQL Server 2005

Aug 1, 2007



Hi,
I have a need to shoot and automated mail at regular intervals to clients. I got the following code for sending email from SQL Server but get an error.

ALTER PROCEDURE [dbo].[sp_SMTPMail]

@SenderName varchar(100),

@SenderAddress varchar(100),

@RecipientName varchar(100),

@RecipientAddress varchar(100),

@Subject varchar(200),

@Body varchar(8000),

@MailServer varchar(100) = 'localhost'

AS



SET nocount on



declare @oMail int --Object reference

declare @resultcode int



EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT



if @resultcode = 0

BEGIN

EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver

EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName

EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress



EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress



EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject

EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body





EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL



EXEC sp_OADestroy @oMail

END



SET nocount off


The error is

SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.


Can any one help me please

View 1 Replies View Related

Help With Sending File To Server Using Ftp Task

Aug 28, 2006



Doe anyone know how to do this. I keep getting an error remote path missing "/" but it doesn't contain a "/".



Mike

View 6 Replies View Related

Sending Out Calendaring Event From SQL Server

Jan 21, 2008

Hello, I have a table in SQL Server that is used to track appointments. Is
there some way to take this data and send an email to user with the
appointment, when the user clicks on the email it would create the event in
the user or public calendar.

Thanks

View 3 Replies View Related

Binary Data And Sql Server

Sep 20, 2007

Here is my task  I am storing pdf's in sql server. I would like to retrieve the binary data from sql server and write the pdf content into an existing aspx page to the appropriate pageview section.  What is the best way to handle this.  The code works below but it loads a new browser with the content.  I need it to appear in it's tabbed section in the original aspx file.  Any assistance you can give me would be greatly appreciated.
 Thanks Jerry
oSQLConn.Open()Dim myreader As SqlDataReader
myreader = myCommand.ExecuteReader
Response.Expires = 0
Response.Buffer = True
Response.Clear()
Do While (myreader.Read())
Response.ContentType = ("application/pdf")Response.BinaryWrite(myreader.Item("img_content"))
Loop

View 2 Replies View Related

Error In Sending Out-of-band Data To SQL Server.

May 17, 2008



First, yes I know that DB Library is depreciated. Unfortunately it isn't an option for us to make a huge change like that right now.


Now, on to the problem. We have a few different C applications that access SQL Server via db-lib. On SQL 6.5, and SQL2000 we had no issues to speak of. Since upgrading to SQL2005 (Enterprise Edition, 32 bit), we have had a horrible time with receiving an error that says "Error msg: Possible network error: Error in sending out-of-band data to SQL Server. General network error.". It appears to happen at somewhat random times during the applications run.

Search for information on "Error msg: Possible network error: Error in sending out-of-band data to SQL Server. General network error." has provided next to nothing, so I turn to you. Any ideas?

Thanks,
Matt

View 1 Replies View Related

Sql Server 2000 Retrieving Binary

May 9, 2007

I am storing data as binary in my sql server 2000 database, and now I wanted to retrieve the data- before I move on I just wanted to check whether the data is restored correctly.
Is there an easy way for that? A tool for sql 2000 or any other simple options?

View 2 Replies View Related

Length Of A Binary File In Sql Server

Jun 16, 2004

hello!
I am curious to know how I can find the length of a binary in Sql Server. The length doesn't work on binaries.
ie
select length(binary)
from Binarytable

View 2 Replies View Related

Binary Field Usage In SQL Server

Sep 19, 2005

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.

View 1 Replies View Related

Example Of Binary Data Type In SQL Server

Jan 29, 2006

There are two datatypes for storing binary data type in the SQL Server:1. binary - for fixed length binary data2. varbinary - for variable length dataMy question is: how is data inserted into them? Do they have anydelimiters that go into the insert statement like strings and datetimeshave? What format (hex/decimal?) do they accept data in? Can you pleasegive me an insert statement example?

View 9 Replies View Related

Passing A Binary Value To Sql Server 2005

Jun 27, 2007

I have been pulling my hair out on this.



I am trying to pass a binary value from an ASP.NET app to a VERY simple stored proc, and I can NOT get it to work!



Here is some of my code:






Code Snippet

ALTER PROCEDURE [dbo].[sp_SelectAppointments] @PID VarChar(50)



AS

BEGIN

--set @PID = 0x00000000000000B1

--PLEASE NOTE HERE THAT THE ABOVE VALUE(WHEN UNCOMMENTED) RETURNS RECORDS

--WHEN THE VALUE IS IN QUOTES (AS IT WOULD BE) IT DOES NOT WORK

SET NOCOUNT ON;

SELECT Appt_Date, Appt_Description, Rn_Appointments_Id

FROM Rn_Appointments

WHERE project = @PID --Project is a binary field.

END



I created a Dataset in VS2005, and here is the code that passes the value to the Dataset:




Code Snippet

Dim PID As String = Request.QueryString("pid")

PID = "0x00000000000000B1"

Dim da2 As New sp_SelectAppointmentsTableAdapter

GridView1.DataSource = da2.GetData2(PID)

GridView1.DataBind()



What am I missing?? Can anybody please help me out here?



Thank you.



Steve

View 1 Replies View Related

Read Binary Data From SQL Server

Oct 19, 2007

hello.
i have a _CommandPtr that has the type CommandTypeEnum::adCmdTex, and the CommandText a query("Select * from Table_1") and this select returns one row that has a binary data in it.




Code Block

_CommandPtr pCommand;
//Create the C++ ADO Command Object

pCommand.CreateInstance(__uuidof(Command));

pCommand->ActiveConnection = this->pConnection;

//Make the ADO C++ command object to accept stored procedure

pCommand->CommandType = CommandTypeEnum::adCmdText;

//Tell the name of the Stored Procedure to the command object

pCommand->CommandText = _bstr_t("select * from Table_1");

//get recordset
pRecordset = pCommand->Execute(NULL,NULL,CommandTypeEnum::adCmdText);

BYTE* buffer = NULL;
if(pRecordset != NULL)
{

while (!pRecordset->GetEndOfFile())
{

VARIANT var = pRecordset->Fields->GetItem("test")->Value;

void * pData = NULL;
SafeArrayAccessData(var.parray, &pData);
long size = GetArraySize(var, NULL);
buffer = new BYTE[size];
memcpy(buffer, pData, size);
SafeArrayUnaccessData(var.parray);
pRecordset->MoveNext();
}
pRecordset->Close();
}

where:



Code Block
long GetArraySize(const VARIANT& var, long * nElems)
{

if ( !(var.vt & VT_ARRAY) ) return -1;

long size = 0;
long dims = SafeArrayGetDim(var.parray);
long elemSize = SafeArrayGetElemsize(var.parray);
long elems = 1;
for ( long i=1; i <= dims; i++ )
{

long lbound, ubound;
SafeArrayGetLBound(var.parray, i, &lbound);
SafeArrayGetUBound(var.parray, i, &ubound);
elems *= (ubound - lbound + 1);
}
if ( nElems ) *nElems = elems;
size = elems*elemSize;
return size;
}






I enter in VARIANT var = pRecordset..... and if gives me a value... but when I put it in Memory explorer in VS, i only see this data " fe ee fe ee fe ee fe ee fe ee fe ee fe ee ...fe ee" and of course it brakes at
SafeArrayGetLBound(var.parray, i, &lbound);

Can someone tell me where I am doing a very bad thing?

P.S. I was able to read the binary data from the server using C#.NET 2.0.

View 1 Replies View Related

Sending Emails Throught DTS Packages In Sql Server 2005

Jan 23, 2007

Hai,
I am working on the DTS Package in SQL Server 2005 , actaully the DTA Packages Use OutLook to Send the email to the Mailing List , now that Out Look Stuff is not working Fine , soo I came to know that we can use Sp_send_dbMail stored Procedure insteds of Outlook , i am trying to do that my Email contains the attaachment to the Text file from the shared Folder.
I am not Getting the Correct Help when i am trying .
So can some one Helpe Providind Some examples and suggetions .

Thanks

View 1 Replies View Related

Sending Email On Some Specific Event (was Sql Server 2005)

Apr 19, 2007

Hi,
I need a help from you all on Sending Email on some specific event from SQL SERVER 2005. Any Idea how Do I proceed?


Any help will be appreciated.....



Thanks,
Rahul Jha

View 2 Replies View Related

SQL Server 2012 :: Sending Email Through Store Procedure?

Jan 28, 2015

I have below code to send email in HTML table format with store procedure. from my

Database = "CreditControl"
Table = "Testing$"

and code as below

DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @recipients nvarchar(max)
DECLARE @profile_name nvarchar(max)

[code]....

View 6 Replies View Related

SQL Server 2014 :: Trigger A Report By Sending Email

Oct 18, 2015

I have a report that gets a Customer_Number parameter and sends a mail with that customer's data.I want my users to be able to get this report's results by sending an email to a certain email address with a customer number in the topic.

View 1 Replies View Related

Extracting Binary Data From SQL DB To A Location On The Web Server

Mar 15, 2007

Hey all, WE have a document management system where by Adminstrators can upload documents, once the document is uploaded the binary data is stored on in a folder on the web server.  We used to stored the documents in the actaul db table, but we found that there were to many documents and it was using alot of space on db server. So my boss has decided we are now going to upload the binary data onto the web server.  Currently we are donig this with new documents which have been added or documents which are gettinguploaded when reloading, but there are many documents in the db table which have not been updated and are still embedded in the db table.  So i need to figure out how to go about copying the data storewd in the db table and storing it in web servers folder location. I've tried various things for a enitre day but im going round in circiles.                             MemoryStream mStream = new MemoryStream((Byte[])dtrResults["file"]);                            BinaryReader bReader = new BinaryReader(mStream);                            int intFileSize = (int)mStream.Length;                            Byte[] byteFile = (Byte[])dtrResults["file"]; i can get to this state but then how do i create a folder on the BinaryREader to then store the binary data of the file to the location.                             BinaryReader bReader2 = new BinaryReader(File.Open(strDocFolder + strSavedFileName, FileMode.Create));                            int count2 = bReader2.Read(byteFile, 0, intFileSize);                            bReader2.Close();i've also tried this but when the file gets created in the folder there is no content.  i do know that the file does contain content as ive tried this and downlaoding the file from that page acctually works                            string strContentTpe = WValue.WStr(dtrResults["contenttype"]);                            int intFileSize = VValue.VInt(dtrResults["filesize"]);                       /    Byte[] byteFile = ((Byte[])dtrResults["file"]);                            //Downloads the data correctly                           Response.ClearContent();                          Response.ClearHeaders();                           Response.AddHeader("Content-Disposition", "attachment; filename="" + WValue.WStr(dtrResults["docfilename"]) + """);                           Response.AddHeader("Content-Length", WValue.WStr(intFileSize));                          Response.ContentType = strContentTpe;                           Response.BinaryWrite(byteFile); I hope ive made some snese andthat someone can hlep me. Have a nice dayZal     

View 2 Replies View Related







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