.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

How to add FILESTREAM attribute to an existing column?

Posted By:      Posted Date: October 10, 2010    Points: 0   Category :Sql Server
I have a table that uses the FILESTREAM attribute for SQL Server 2008. If I edit it in the 2008 SSMS table designer, the FILESTREAM attribute is lost. Most folks say to use ALTER to put it back. Problem is that every example I see shows creating a brand new table or brand new column. I can drop and re-add the column but then I lose my data. What happens if I have a table in production with existing filestream data and need to put the FILESTREAM attribute back on the column without losing the data in the column?

I tried and get this:

Cannot alter column 'Data' in table 'accnt_attachment' to add or remove the FILESTREAM column attribute.

Obviously I do not have the correct syntax. An example would be greatly appreciated.


View Complete Post

More Related Resource Links

New column to existing content types

HI, How can I add a new column to existing content type. The field shoule be reflected to new lists created using the content type as well to old lists created using the content type. None of functionalities associated with old lists should get affected. Thanks,kesari suresh

Filestream column

Hello, On a Visual Studio 2010 database project I created the following table: CREATE TABLE [dbo].[Resources] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Content] VARBINARY (MAX) FILESTREAM NULL, [Description] NVARCHAR (800) NULL, [Key] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL, [Locked] BIT NOT NULL, [Name] NVARCHAR (100) NOT NULL ); I keep having the error: SQL04141: A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column. But I do have such a column: Key What am I doing wrong? Thanks, Miguel  

Dimension design: Key column of non-key Dimension Attribute

Assume I have a product dimension where key dimension attribute is product code - this is a very large dimension with more than 1 million members, the key column for this attribute is productID (integer). There will be other attributes in this dimension related to key attribute.   My question is about the key columns to be defined for these other attributes. As it is related to key attribute, it has to include ProductID as part of key and hence forming a composite key - e.g. Product inception date - the key column for this has to be ProductID + date as many products can have same date. This design will violate the best practice recommendation from MS to have only numeric key columns for very large dimension attributes. But I can't find a way around this and assume this will be case for all non-key attributes in very large dimension. Is my understanding right? I assume this will be an issue for everyone? Any standard way to get around this? Or best to leave it as such? Thanks in advance.

Adding New Column to XSD File Without Remapping existing Transformations

Hello, I am loading XML file using SQL 2005 SSIS and using XSD file for XML Schema defination. Now I have a requirement where I should add couple of new columns in XSD as the XML (MAY) have extra columns for few of its tables. Is it safe to add new columns to XSD and the Map only new columns in Data Flow and all will work? or after I add new columns I need to redesign all mappings as Column order will change. (I think SSIS refere a numeric value for each column, if Yes how can I add new column with less impact)? Thanks, Prabhat Nath

update stored procedure for existing values of column(salperyr)

tbl_salary salary           salperyr   hike20      hike20yr10000.0000    NULL      12000.00    144000.0012000.0000    NULL      14400.00    172800.0014000.0000    NULL      16800.00    201600.0015000.0000    NULL      18000.00    216000.0018000.0000    NULL      21600.00    259200.0020000.0000    NULL      24000.00    288000.0022000.0000    NULL      26400.00    316800.00in above table salaryper yr (salperyr) has to be modified after caliculation my null values has to be removed and place (salary*12)in one shot.so plz suggest me update stored procedure for this.

update stored procedure for existing values of column(

tbl_salary salary            salperyr    hike20       hike20yr 10000.0000    NULL      12000.00    144000.00 12000.0000    NULL      14400.00    172800.00 14000.0000    NULL      16800.00    201600.00 15000.0000    NULL      18000.00    216000.00 18000.0000    NULL      21600.00    259200.00 20000.0000    NULL      24000.00    288000.00 22000.0000    NULL      26400.00    316800.00 in above table salaryper yr (salperyr ) has to be modified after caliculation my null values has to be removed and place (salary*12 )in one shot.   so plz suggest me update stored procedure for this

Errors in the OLAP storage engine: The attribute key cannot be found: Table: table_name, Column: col


I get this error on occasion while processing (full) the OLAP database, and I know that the value 822518 is part of my dimension table refered by the fact table.  I usually just re-process (full) the OLAP database and don't get the error anymore.  Is this a problem with the order in which the objects are processed?  If so, how can I change that?



SQL2008 Filestream Column Issue

I've got a table that uses the SQL 2008 FILESTREAM feature for storing binary data within a filegroup.
This feature is not supported by Management Studio so any designer modification will remove the feature from the column.

My question is, how would one recover from this? Reattaching the attribute and the binary data link to the filegroup? It doesn’t seem possible?
With thousands of records this could mean massive data issues should someone modify the table with the GUI.


Get SSAS Dimension Attribute SQL Column Name using C#



I am trying to determine what the real SQL Server Column name is for a Dimension Attribute using ADOMD.net, however all I am able to find is the the TABLE_NAME and COLUMN_NAME Columns using the GetSchemaDataSet("DBSCHEMA_COLUMNS", null) function.

Does anyone have any suggestions?



the column in table tbl_table1 do not match an existing primary key or UNIQUE constaint


ID primarykey (autoinc)
UserId uniqueidentifier
IdNumber primarykey int

 I'd like to have a relationship between two tables.

Where is the problem?


Changing existing column types


Is there a proper way to change an OOTB column's data type?

Microsoft have created columns using nearly all the common column names but several of them are not the right type for our organisation. It will bequite a nuisence if we have to create new ones prefixed with something to make them unique.

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.

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.

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