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


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

error when I try and drop a primary key on a table

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :ASP.Net
 

I have a table which is populated with data. It has a primary key defined on one of the columns. I need to first
drop the primary key constraint, and then add it back on the next line, with another column, so that their will be two
primary keys for the table instead of one. (Otherwise known as a Composite primary key)

The problem is that I cannot drop the primary key using sql. My drop code is on line 1 below.

Here is my sql

1    ALTER TABLE [dbo].[ShipTest] DROP CONSTRAINT TruckId_PK

ERROR: line 1: TruckId_PK is not a constraint


2    ALTER TABLE [dbo].[ShipTest] ADD PRIMARY KEY ([TruckId],[VehicleId])

Why am I getting this error message? I can't find an answer or hint on google either?

 

USE [shippingDev]
GO
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].[ShipTest] (
 [TruckId] [int] NOT NULL,
 [VehicleId] [int] NOT NULL,
 [VehicleName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[TruckId] ASC,
[VehicleId] ASC
) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]




View Complete Post


More Related Resource Links

Error Message: The referenced table must have a primary or candidate key. [ FK Name = FK_tblA_tblB ]

  
An Entity Data Model is used to generate the script to create  a SQL CE database.  It creates all of the 10+ tables and their primary keys without any problem. A lot of foreign key creations generate errors like the following: SQL Execution Error. Executed SQL statement ALTER TABLE [tblA] ADD CONSTRAINT [FK_tblA_tblB] FOREIGN KEY([A], [B], [C]) REFERENCES [tblB]([A], [BBB], [C]) Error Source: SQL Server Compact ADO.NET Data Provider Error Message: The referenced table must have a primary or candidate key. [ FK Name = FK_tblA_tblB ] The primary keys are there and correct. Could anyone offer some tips on how to figure out what is wrong?hz

SQL Server Replication Error Cannot drop the table

  

Can any one help me on the below error.

I have a transactional replication (pull). All of a shudden it failed to transfer the data. The error message is

The concurrent snapshot for publication 'Woolworths Petrol - Filtered' is not available because it has not been fully generated or the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Snapshot A

I tried to reinitialize the subscriber and it pops up the below message.

Cannot drop the table 'table_name' because it is being used for replication. (Source: MSSQLServer, Error number: 3724)

I don't want to drop the table.

Please help.

CHIN


Ajax Cascading drop down method error 500!

  

Sorry to trouble you all with something that has already been posted a million times but I have tried everything I can and stuck with for about 10 days now. Please help. I have created 2 tables Categories, Subcategories.

Categories table has Category_ID(Primary Key) and "Category" fields. Subcat table has SubcatID(Primary Key, Sub_cat and Category_ID(Foreign Key). Here's the code of the asmx file.

Service methods used in ASPX file ServiceMethod="GetVendors" and ServiceMethod="GetContactsForVendor". Please help me find the source of the method erro 500 and I will be very greatful.



<%@ WebService Language="VB" Class="CascadingDropdown1" %>


Imports System.Web.Script.Services

Imports AjaxControlToolkit

Imports System.Web

Imports System.Web.Services

Imports System.Web.Services.Protocols

Imports System.Collections.Generic

Imports System.Collections.Specialized

Imports System.Data.SqlClient

<ScriptService()> _
Public Class CascadingDropdown1
Inherits System.Web.Services.WebService

<WebMethod()> _
Public Function GetVendors(ByVal knownCategoryValues As String, ByVal category As String) As Cascadi

Using table with no primary key in CTP4

  

Hi,


Does anyone knows if CTP 4.0 supports table with no primary key. I know Entity framework does.



Can't perform Create, Update or Delete operations on 'Table(outbox)' because it has no primary ke

  

 Hi,

I'm a beginner in asp.net and I have some problem inserting data into sql server 2005 with linq.

 

my code is : 

 

        Dim Result As New SMS.SendResult
        If Result.status = 0 Then
            Label1.Text = "Your Message Sent Successfuly"
           
            Dim db As New IGSSDataContext
            Dim out As New outbox With {.ClientID = intRandomNumber, .From = User.Identity.Name, .TO = TextBox1.TabIndex, _
            .Time = Now, .Message = Message}
            out.ID = (ID)
            db.outboxes.InsertOnSubmit(out)
            db.SubmitChanges()
           
            TextBox1.Text = ""
   &nb

Excel 2007 Pivot table OLAP- SSAS 2008 Error

  
Hi, I do have a strange issue with Excel 2007 pivot table report; the data source is SSAS 2008 cube. When refresh the excel 2007 report data using the 'Refresh All' or 'Refresh' button available under the Data menu, the below error is thrown. "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." After some analysis, the report works well on the below workaround, 1. Remove all the calculated measures from report (3 measures in this scenario) 2. Remove one row label (dimension attribute) from report But the measures, attributes are valid one from cube and it is required fields in the report. So, understand that removing few items is not at all a solution. The below workaround also works. 1. Check the 'Defer Layout Update' option from the Pivot Table Field List window. 2. Click on the Update button After that Unchecked the 'Defer Layout Update' option and apply the filter, etc in the report. The report works very well. But every time, we need to use the above work around after open the excel report. I have NO clue on this issue. Please help to resolve this issue. Thanks, Jey

Invalid object error on temp table on a particular environment

  
Hi, I am using a temp table in my sp. I have written the following tsql before and after using the temp table. IF OBJECT_ID('tempdb..##TEMP') IS NOT NULL DROP TABLE tempdb..##TEMP; Data will be inserted to the temp table consecutively using a cursor. I use SELECT * INTO tempdb..##TEMP FROM EXEC('query') to fill this temp table for the first time and INSERT INTO tempdb..##TEMP(<fields>) EXEC('query') for the remaining times. The SP is working fine in my laptop, but I am getting an Invalid Object error on this temp table name in the production server. Could anyone tell me the possible causes please? Thanks Sree

Analysis Service Process Dimension Cause : OLE DB error: OLE DB or ODBC error: ORA-00942: table or v

  
Dear Gurus, I'd face a "STRANGE" problem. I'm using SQL2008 connect to Oracle Data Source (OracleOldDB.1) When I process a snow-flake dimension . There is a ORA-00942 problem. But if I past the SQL Statement to a Oracle Tool . It worked without any problem. Does any body ever face this "STRANGE" problem ? And I'd try to remove this table from data-source-view and re-join it . Still same problem !!  Wilson

Excel Drop Table question

  
Can anyone tell me if the drop table syntax should delete only the data in a worksheet or clear the formatting as well. If not is there a way to clear all the formatting. Thanks

Two Primary key for table???

  
i HAVE FOLLOWING SCRIPT TO CREATE TABLE. create table Grade_report (STUDENT_NUMBER SMALLINT NOT NULL, SECTION_ID SMALLINT NOT NULL, GRADE CHAR(1), CONSTRAINT stno_secid PRIMARY KEY (STUDENT_NUMBER, SECTION_ID)) tHE BOLD line shows that it has two primary key. would that be possible to create two primary key for a single table??  If not then what does it for?? may be unique key??? anyone can explain me please.     Thanks,      

Adding Oracle table into SSIS project --Oracle error occurred, but error message could not be retrie

  
Hi all I am new to design SSIS packages,to day I stated creating package SSIS package and adding the Tables,while adding tables,one of the table giving an below error . I verified the table structure it has one "CLOB" datatype. can some body help how to add this table( whole idea of the package is I have Dump those tables with structure and Data to SQL Server 2008 Database)  =================================== Oracle error occurred, but error message could not be retrieved from Oracle. Data type is not supported. (Microsoft Visual Studio) ------------------------------ Program Location: at System.Data.OleDb.OleDbCommand.ProcessResults(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.PrepareCommandText(Int32 expectedExecutionCount) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType) at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV() Thanks in Advance.   SNIVAS

adding Primary key to a data table

  
Hi All, I have a data table this table saves values from a form's add button and binds to a gridview on the final submit button adds to the data base. See my Data table below    DataTable dt = new DataTable();                   if (ViewState["CompDetails"] == null)            {                 dt.Columns.Add("CourseCode");                dt.Columns.Add("CourseName");                dt.Columns.Add("Fees");            }            else                dt = (DataTable)ViewState["CompDetails"];            DataRow dr = dt.NewRow();                                &n

Joins on non-indexed fields and table primary keys.

  

I have two tables (a,b) each with a primary key (which are clustered indexes). When I issue the t-sql statement

delete XX from t1 XX inner join t2 on (xx.A2 = t2

How can I avoid error a Insert Store proc if he don't find any coloum in a table

  

I have created one Insert Proc for a table which has three Colourm. 

Id int, [Name] varchar (30), Phone varchar (15) I want to use this proc for another Table which has two coloum which is semiler with first table lie   Id int, [Name] varchar (30) I want to use one Insert Store Proc For two Table. I can avoid null value of parameter but but can't avoid if the coloum is not exist. 

 

 


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

Performance Drop when switching from a #temp table to a @temp table variable

  

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim


Performance Drop when switching from a #temp table to a @temp table variable

  

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim


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