.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Deleting row with varbinary(max) column from a .Net web app causes a timeout

Posted By:      Posted Date: September 13, 2010    Points: 0   Category :Sql Server
Hi, We've got a .Net web app with a table called CoreFile. The table has a PK called FileID and a varbinary(max) column called FileData, amount other columns. The table holds small files, most of which are below 100kb, but with the odd 1.5-2mb file. I don’t believe there is anything above 10mb. We have a stored procedure that deletes an individual entry: DELETE FROM CoreFile WHERE FileID = @FileID The problem is that when the SP is executed from the web app, the connection times out and the delete is unsuccessful, particularly when the file is larger. The specific error is: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. When we execute the SP from SSMS however, the statement runs almost instantly, regardless of the file size. I checked the connection settings using the SQL Server Profiler, and both SSMS and the web app appear to use the same configuration: -- network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language British set dateformat dmy set datefirst 1 set transaction isolation level read committed I tried replacing the SP with a LINQ statement using the Entity Fram

View Complete Post

More Related Resource Links

ssis sql 2000 image to 2008 varbinary Failed to retrieve long data for column

Hi, I have a task that to migrate the image type column from sql 2000 to varbinary type in sql 2008. The source column having 3812353 max datalength size for the column. The package always failed with following error message. [OLE DB Source [13177]] Error: Failed to retrieve long data for column "attch_file_content_t". [OLE DB Source [13177]] Error: There was an error with output column "attch_file_content_t" (13209) on output "OLE DB Source Output" (13187). The column status returned was: "DBSTATUS_UNAVAILABLE". [OLE DB Source [13177]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "attch_file_content_t" (13209)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "attch_file_content_t" (13209)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Meng Chew

Need to export images from a varbinary column to .jpg files (SQL 2K)



What's an efficient way to export the images out of a table (SQL 2000) which has them stored in a varbinary column -- i.e. export from image data to .jpg file?

Can I do this using an ODBC connection?



Error at Infopath after deleting a listfield - Cannot insert the value NULL into column 'tp_DocId'



after deleting an unused calculated field id_2 at my sharepoint list I get the following error when creating a new item with my Infopath 2010 form (update item still works):
"The form cannot be submitted.
Exception from HRESULT: 0x80131904"

The joblog tells me:
09/28/2010 13:24:46.96  w3wp.exe (0x093C)                        0x0C7C SharePoint Foundation          Logging Correlation Data       xmnv Medium   Name=Request (POST:http://test-project.xx.intranet:80/project/ideas/_layouts/Postback.FormServer.aspx) f8ffa9c3-f81c-429b-91de-3f76739e446a
09/28/2010 13:24:47.03  w3wp.exe (0x093C)                        0x0C7C SharePoint Foundation          Logging Correlation Data       xmnv Medium   Site=/project f8ffa9c3-f81c-429b-91de-3f76739e446a
09/28/2010 13:24:47.19  w3wp.exe (0x093C)               &nbs

Is it possible to save "Collections" to a single varbinary(MAX) field/column in a SQL Server 2008 da


I was thinking about the fastest way to retrieve a "set" of objects related to a specific user. For instance, if I was to create a website similar to Flickr where people upload lots and lots of photos(jpegs) what would be the fastest way to be able to retrieve those photos from the SQL Server database. Scenario 1: You store metadata about each file they upload to the website as a single row in a database table. Then when you want to retrieve a specific users uploaded files you simply scan the database file for every row matching the UserID and store them in memory.

SELECT PictureId, UserId, PathToFile, Caption, (etc.)

FROM Photos

WHERE UserId=@UserId

This, of course, gets all the information about all the photos a single user has uploaded. My concern with this approach is when the user activity goes from a few thousand to being in excess of a million.

This approach seems fine for a small website but does this method work for a million plus user website like Flicker or MySpace? When you have a million users and millions more photos does this approach still perform at acceptable speeds?

My idea was to actually attempt to create a collection in memory of "ALL" the PictureIds' for a single user and store them in-memory, in a collection and then save this collection as a single VARBINARY(MAX) field in the SQL

Tooltip for GridView Column Headers (ASP.Net)

I just wanted to add auto tooltips for my GridView headers. There are couple of ways but I don't want to use javascript and wanted to use simple 'title' attribute. It took couple of hours to get the working code supporting sortable columns. I thought it will be very useful for others. Here's the code and logic behind it.

Multiple Column Dropdownlist for the ASP.NET DataGrid

Based on my previous control "Multiple Column DropDownList for ASP.NET", I received many emails asking for the same control to be used in the DataGrid for web applications. Here we go.. This control can be used as the regular MS DropDownList in the DataGrid and also as a regular dropdownlist. It has all the properties, like DataTextField, DataValueField, DataSource, SelectedIndex etc. The download file contains the samples both in VB.NET and C#. In this sample, I have used the Northwind database of SQL Server.

Adding Client-Side Confirmation When Deleting

The JavaScript confirm(string) function displays its string input parameter as the text inside a modal dialog box that comes equipped with two buttons - OK and Cancel (see Figure 1). The confirm(string) function returns a Boolean value depending on what button is clicked (true, if the user clicks OK, and false if they click Cancel).

Alternating row background for first column of a report


is there a way to make the first column of a report use the an alternating background color? 

I have the background color set for the entire row however it does not work for the first column if the color is set to an expression.  If I just set it to a color i.e. red it works just fine. If I insert a dummy column to the left it works just fine (but the dummy column does not work, presumably because it is first??).

In the file below only the textbox for part number does not show an alternating background.  All others work correctly.

                    <Textbox Name="PartNumber">

Use a column as parameter for a sds in another column; gridview


Hola, I have a gridview which is bound to a sds which returns 7 columns and I added two columns from the GUI.  What I want is to fill up the 9th column with DDLs and these  DDLS will get their data from a different sds which is using a stored procedure with a single parameter.  I created the 9th row, used template field, made the sds for 9th row, the stored_procedure, threw a DDL in Item template and all  I need now is to pass a parameter in the first column to the sds in the 9th column.  I can code this but i'm getting tired of aspx.cs :) and would like to do it from the GUI.

Pass a grid column value to a Modal


Hi, I have a requirement to hide a field called details on a gridview and show it in a jQuery modal dialog when users click on the Customer ID hyperlink. This grid is created programmatically. I will be hiding the details column via the CSS display:none property. Does some one know how can I pass the grid's selected hidden column value to the modal? Here is some of the code I started:

            GridView mygrid = new GridView();
            //.....................................More columns code for the grid

            //Here is where I need to pass the Details column value associated with the customer ID field selected to the Modal and open the Modal dialog.            
            HyperLinkField linkField = new HyperLinkField();
            string[] dataNavigateUrlFields = { "CustomerID" };
            linkField.DataTextField = "CustomerID";

Bug: Does not change column type from nvarchar to ntext


Scenario: If you create a table with a column of type nvarchar, populate it with some content and try to modify it using the WebMatrix editor, from nvarchar to ntext, it does not display any error message or warning that this is not possible. It silently saves and reverts the change, back to nvarchar. Wasted some hours on this issue as I thought my column was ntext, but in fact was still nvarchar.

Expected behavior: It should change to ntext, or if SQL CE doesn't support that, it should notify the user that no change was applied.

Display column from child table. Possible ?



I'am using dnamic entity with EF4. On a list page of a table, I would like to display a column containing information from a child table of the current element. Sample :

Order List :

Order Data | Required Date | Shipped Date | etc... | ... | Customer Name (foreign key with tostring() method override) | Customer Postal Code (Column that I want to add) |

I don't known how to do that. Is it possible ? Maybe I have to to create my own Metadata proxy that will add dynamicly a column on the MetaColumn list of the table.

Does someone have an easier or better idea ?

Thank you for any help.

Rendering data with HTML tags in the DD Gridview for a selected column


I am having trouble finding out where and how to HTML Encode a cell's data on the Dynamic Data (v4.0) gridview of List.aspx.  As a simple case, suppose I have formatted cell data that is A<br/>B in the DB.    Obviously, I want A stacked on B in the cell.

It seems gridView1.HtmlEncode = true has gone away.

So maybe I'll try to catch it on the RowDataBound event:

protected void GridView1_RowDataBound(Object sender, GridViewRowEventArgs e)

            if (e.Row.RowType == DataControlRowType.DataRow)
                // Html Encode the cells


but this event never fires?

Has anyone figured out how to properly render HTML tags data in the List.aspx's GridView1? 

If I figure this out, then I can add a MetaAttribute called something like [EncodeAsHtml(true)] and be on my way.  Thanks!

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is no



 I keep getting the following error. I also added time out parameter in the connection stirng and it still did not help. Has any one faced similar issues.

Thanks in adavance.

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.


Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.]

Filter by Computed Column



After looking for a driver to open legacy dbf drivers to connect, the next level trouble a have is this one:

first, i can retrieve the data but when trying to filter by a parameter (somesc columns) i get an error.

The error says about the database can't determine some blah blah!

The error is a columm tha is formated (  1234  ) about  8 space, fixed. The values in the query appears like that. I resolve this by Trimming Ltrim(ColumnName) and i get this (1234).

Now in SqlExpress, how can i filter by the new Trimmed Expression? (This is a primary key that the DBF use)


SELECT column1, column2, column3


SELECT Ltrim(column1) as NewExp, column2, column3
Where NewExpre = @NewExpres                  -Error-


Unique text value in databse column


Hi Everyone!

I know this is probably a very basic question but how do I keep a column's text values unique in SQL server (ie for a unique username)?  The column is not an integer it's varchar(50), and it's not the primary key but I could make it the primary key if recommended.

The front end could handle it;  where the user would enter text, it's validated, then a query is done to see if that username is available.  If so, then user can decide to choose it and an insert is done, if not they pick a new username and process repeats.  With this approach, the responsibility of keeping the value unique solely rests on the front end, nothing on the database side.

Is this the correct way to handle this? 

Thank you!


SQL 2008 timeout


I am recently getting a timeout exception when executing this method. This table stores images and it might be due to the size of the table. I have no idea! Help!


 Public Sub StoreFile(ByVal fileId As Integer, ByVal upload As Stream, ByVal connection As SqlConnection)
            Dim bufferLen As Integer = 8040
            Dim br As BinaryReader = New BinaryReader(upload)
            Dim chunk As Byte()
            chunk = br.ReadBytes(bufferLen)

            Dim cmd As SqlCommand = _
            New SqlCommand("UPDATE tblContactDocuments SET ContractDocument=@Buffer WHERE AutoID=@FileId", _
            cmd.CommandTimeout = 30
            cmd.Parameters.AddWithValue("@FileId", fileId)
            cmd.Parameters.Add("@Buffer", SqlDbType.VarBinary, bufferLen).Value = chunk

            Dim cmdAppend = New SqlCommand("UPDATE tblContactDocuments SET CONTRACTDOCUMENT.WRITE(@Buffer, NULL, 0) WHERE AutoID=@FileId", _
            cmdAppend.Parameters.AddWithValue("@FileId", fileId)
            cmdAppend.Parameters.Add("@Buffer", SqlDbType.VarBinary, bufferLen)
            chunk = br.ReadBytes(bufferLen)

            While (c
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend