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


Top 5 Contributors of the Month
momu198
Imran Ghani
Sohaib Khalid
Post New Web Links

Insufficient Permissions to alter table in SSMS Design View, can run ALTER Table successfully

Posted By:      Posted Date: November 16, 2010    Points: 0   Category :Sql Server
 
Hello, are the permissions in the object explorer in management studio different for a login than what can be run in a query window for the same login? I have a developer I gave ddladmin so he can create/modify tables. He can open the table in design view in object explorer (gets a warning about possible insufficient permissions when he opens) but when he tries to add a column it fails with a permissions error. He scripted off the change and ran it in a query window, which worked fine. Any insight would be appreciated. Thanks.


View Complete Post


More Related Resource Links

How to Alter a view to remove the Database Name on the from table in bulk

  

I'm not sure if there's an easy way to do this, but I want to change the "... from [MyDatabase]..[MyTable]" part in my view to remove the database part so it's just "... from [MyTable]".  There are about 100 views that have this.

We use this 3rd party tool to create our database tables and views (no way around this) and in their views they put "... from [MyDatabase]..[MyTable]" .  Then later we need to take a snapshot of that database, so we copy the mdf file and change the database name.  The copy is fine, but the views won't work because of this.  Is there an easy way to alter all the views to remove the datbase name?  There are 100-300 views I have to change, I don't want to write that many alter scripts.

 


dan

SMO classes to generate alter table script to add columns

  
How to generate ALTER TABLE script to add columns using SMO and c#.

Right click script alter table disabled

  
Does anyone know how to enable the "Script Table As" -> "Alter To" option in SQL Server 2008 SSMS? John M. Couch Insight Enterprises Inc. 6820 S. Harl Ave, Tempe, Az 85283 Email: john.couch@insight.com

is Alter table a fully logged operation

  
Hello, A friend of mine has a very large table which has a column of type int. Since this table is growing very fast he wanted to alter the table to change the data type from int to bigint. when he did that, he found that he ran out of disk space on the t-log volume. (600 GB of free space). why did this happen? shouldn't the type change be a meta data change operation? why was the t-log so heavily used on change of type?

Alter Table add column

  
Hi all, ALTER TABLE dbo.tbl_allocation ADD calc_method [decimal](38, 6) ADD allocation_percentage [decimal] (38, 6) when i run this i get the following message: incorrect syntax near decmial. please help

Upgrading timestamp column to rowversion (alter table)

  
Hi, We are upgrading 2005 to 2008 and observed that time stamp data type is deprecated, and should be using rowversion instead. So I started doing alter table for a table with timestamp to rowversion and the alter table failed with the error "cannot alter column 'rv' to be data type timestamp. Can some one please let me know what I am missing here? thanks, Sivakumar

Alter table error

  

continue to get this error and cannot figure out why

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '('

This is the code;  - error refers to the '(' at end of Alter Table line

USE

 

[ControlDB]

GO

/****** Object: Table [dbo].[MarkData] Script Date: 09/22/2010 10:17:06 ******/

SET

 

ANSI_NULLS ON

GO

It is possible to alter multiple columns within a single alter table statement?

  

It is possible to alter multiple columns within a single alter table statement?

I tried & searched not getting it.

Alter table au_de alter column m_user char(9),c_user char(9)

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ','.



 

Alter table au_de alter column m_user char(9),alter column c_user char(9)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Alter table nockeck constr with VAR

  

Hi all,

 

When i run this query:

declare @FK_Name varchar(500)
declare @UT_Name varchar(500)
set @UT_Name = Table_Name
set @FK_Name = FK_Name
alter table @UT_Name nocheck constraint @FK_Name

I get this error msg:

Incorrect syntax near '@UT_Name'.

I guess The problem is in the last dtring of the query.

What should i change in order it works?

 

Thank you

Evgeniya


The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

  

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_Allergy". The conflict occurred in database "MSCPROJECT", table "dbo.Severity", column 'SeverityCode'.


SQL Server 2008 - Alter Table Switch Statement Fails Where Partition Key Defined as a Persisted Comp

  

Here's a very annoying and poorly (or undocumented) issue in SQL Server 2008 Ent. Ed. post-SP1, CU8:

When creating a table with a persisted computed column as the clustered primary and partitioning key (see below), where the computation includes a call to CONVERT, watch out for the representation of this calculation on sys.computed_columns.  The query parser will modify the definition at table creation to insert a default style parameter of "(0)", if no other style is specified.  If the original definition did not include any style parameter and you later attempt to programmatically recreate the table using the stored definition contained on sys.computed_columns, the query optimizer will throw an error when you then attempt to switch partitions from the original table into the new programmatically created table, as follows:

Msg 4966, Level 16, State 1, Line 72

ALTER TABLE SWITCH statement failed. Computed column '%' defined as '%' in table %' is different from the same column in table %' defined as '%'.

This is so, eventhough the two definitions are completely functionally equivalent, considering the default value of the style parameter.  This can cause a lot of headaches, because correcting the problem requires dropping and rebuilding the computed column with a definition that include

Unable to alter table that is beeing replicated

  
Hello

We are using SQL Server 2008 and transaction replication. I have set up the publication so that it is replicating schema changes but today i discovered that I was unable to add a column to one table that also is an article in the publication. The error I get is this:

Altering [dbo].[Group]
Msg 650, Level 16, State 1, Procedure sp_MSreinit_article, Line 66
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

Have anyone any clue of what this can be?

/Henrik

hesta96

how to execute ALTER table for multiple columns

  

I have an SQL table called tbl, im trying to add the columns A, B and C to it.

When i execute the command :

 addcolumns = "ALTER TABLE SqlCreatedTbl ADD A char(50) ;";

......

 cmd = new SqlCommand(addcolumns, conn);

conn.Open(); cmd.ExecuteNonQuery();

The column is added perfectly and i can see it in the table !

However, when i try to add multiple columns, it does NOT work, it gives me an error..

the command im writting for adding multiple columns is the following:

addcolumns = "ALTER TABLE SqlCreatedTbl ADD ( A char(50), B char(50), C char(50) );";

the debugger highlights the line : cmd.ExecuteNonQuery(); and throws the following exception: Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '('.


ANY IDEA ? PLEASE HELP


Ambiguous MSDN help on ALTER TABLE ... SWITCH statement

  
This page on MSDN contains a paragraph that I believe is ridiculously ambiguous:
"Source and target tables must share the same filegroup . The source and the target table of the ALTER TABLE...SWITCH statement must reside in the same filegroup , and their large-value columns must be stored in the same filegroup . Any corresponding indexes, index partitions, or indexed view partitions must also reside in the same filegroup . However, the filegroup can be different from that of the corresponding tables or other corresponding indexes."

What other "corresponding" tables or indexes can there be involved in the ALTER TABLE ... SWITCH statement? Why does this paragraph mention tables and indexes sharing the same filegroup when partitions of each of them can reside on different filegroups?

Can someone please enlighten me on the meaning of this paragraph?


Change grid view from table to other HTML layout

  

Hi,

You'll have to excuse my ignorance, I'm coming at this from a front-end perspective, and don't really know how to deal with data views in ASP.NET.

Basically, I'm trying to change the layout of some repeated data from a straight table, to something more design-heavy.  It's the same data, just re-organised slightly.  The current ASP.NET code looks like this:

		<table>
			<tr>
				<td>
                    <asp:GridView ID="GVMoments" runat="server" AllowPaging="True" AllowSorting="True"
                        AutoGenerateColumns="False" PageSize="20" RowHeaderColumn="FirstName"
                        SkinID="GroupSkin" DataKeyNames="moment_id" 
                        DataSourceID="SqlDataEditMoments">
                        <PagerSettings Mode="NumericFirstLast" PageButtonCount="20" Position="TopAndBottom" />
                        <RowStyle />
                            <Columns>
                                <asp:BoundField DataField="moment_id" HeaderText="moment_id" ReadOnly="True" SortExpression="moment_id"
                                    Visible="False" In

Entity Data Model and database view returning the same columns as there are in a table

  

When adding a stored procedure into the Entity Data Model I can select whether the procedure returns a scalar, a (new) complex type or one of the entity types I already defined. 

How do I do something similar for a view?


I mean assuming I have a view like this

CREATE VIEW FilteredFoos as SELECT Foo.* FROM Foo join ... WHERE ...

(that is a view that implements some involved filtering, but returns all columns from one table) how do I add it to the project so that I can use the entity set, but get the Foo objects, not some new FilteredFoo objects.


var foos = myDB.FilteredFoos.Include("Bar").ToList();

foreach (Foo foo in foos) { ...


Thanks, Jenda


How to create scrolling table for columns in a SharePoint Designer Data View

  

I have a fairly complex SharePoint Designer 2007 Data View they have added two more requirements.

1. Have the columns on the right side be able to scroll left to right as there is a large number of columns, while the first 3 columns on the left side stay locked in place.  This is only one list not two, and because of some other requirements I have it must remain to be just one list.

2. Then they also need to have scrolling up and down of the entire list with the headers locked in place.

The first requirement is more important than the second, but preferrably I need to do both.

I am new to XSL so not familar with the syntax. Is this something I can do on a custom Data View? Does anyone have any examples of how?

I have a drawing that show how it needs to look but I don't see an option here to upload a graphic.

So here is a link to my graphic example uploaded to my blog area: http://lindachapman.blogspot.com/  or just the image here: http://4.bp.blogspot.com/_aB01ue__NvQ/TGL42MOvrGI/AAAAAAAAAA4/c3xP4uCy4BU/s1600/Scrolling.png

I failed to mention that 3/4 of the columns are HTML Calculated fields wh

Categories: 
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