Delete Constraint Without Knowing Its Name ??
Oct 4, 2007
In SQL Server 2005, I hava a client where I do not have access to their SQL Server. I update the database structure by giving them scripts which they run. As I update the structures I occasionally need to delete a constraint, then typically recreate it later. Usually I use this type of snippet:
IF EXISTS (SELECT * FROM sys.default_constraints
WHERE object_id = OBJECT_ID(N'[dbo].[ConstraintName]')
AND parent_object_id = OBJECT_ID(N'[dbo].[tablename]'))
ALTER TABLE [dbo].[tablename] DROP CONSTRAINT [ConstraintName]
This assumes I know the constraint name. A lot of the older constraints have random type names which I assume are different on their server than mine; they were not explicitly given names when created, so they would heve been given names by SQL Server.
Is there a way to delete a constraint without knowing its name? For example, delete all constraints associated with a field, or all constraints ssociated with a table?
Management Studio is an option, but for my client that involves a higher degree of paperwork and permission seeking than just running a script.
Many thanks,
Mike Thomas
View 3 Replies
ADVERTISEMENT
Jul 20, 2005
I have the need to remove a constraint on a table since I'm trying to alterthe datatype of one of the columns. I know I can drop the constraint giventhe name, but since the name is auto generated (something likeDF__WHRPT_ITV__Expor__45F365D3)I need a way to find this constraint name so that I can programmaticallyremove it.I can get the name using sp_helpconstraint on the table, but can'tseem to locate where the actual constraint_name is stored.This is on SQL 2000.Any help is appreciated.Thanks,-Gary
View 2 Replies
View Related
Jan 3, 2005
how can i implement delete constraint? i mean i don't want the rows of the primary key table to be deleted if they are used as foreign key in some other table. so i want to check if that PK is used as foreign key in other tables before deleting.
View 10 Replies
View Related
Dec 7, 2003
Can somebody tell me about on delete constraint and where should it be used - table having foriegn key?
coz want i want is- the moment i delete the data from the table whose primary key is been referenced as foreign key , The Data in all the tables where its primary key is beeen used as forein key should be deleted.
:confused:
View 2 Replies
View Related
May 5, 2015
I have a table named [New Item] and created a default constraint on a column, and i wanted to change the data type of the column using the query
alter table [new item]
alter column pcs_qty decimal(15,2) not null
but the name of the default constraint is 'DF__New Item__Pcs_Qt__2D12A970' and i am not able to delete the constraint because it contains a space in between.Is there any work around for this.
I tried to delete the constraint by using the query
alter table [new item]
drop constraint 'DF__New Item__Pcs_Qt__2D12A970'
but I am getting the exception,
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'DF__New Item__Pcs_Qt__2D12A970'.
View 2 Replies
View Related
Nov 22, 2007
Hello, I try to delete a user from the Membership table but I receive this error. "The DELETE statement conflicted with the REFERENCE constraint "FK__aspnet_Me__UserI__15502E78". The conflict occurred in database "E:INETPUBWEBSITE4APP_DATAASPNETDB.MDF", table "dbo.aspnet_Membership", column 'UserId'.The statement has been terminated."...<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" DataKeyNames="UserId" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display."> <Columns> <asp:CommandField ShowDeleteButton="True" /> <asp:BoundField DataField="UserId" HeaderText="UserId" SortExpression="UserId" /> <asp:BoundField DataField="UserName" HeaderText="UserName" SortExpression="UserName" /> <asp:BoundField DataField="LastActivityDate" HeaderText="LastActivityDate" SortExpression="LastActivityDate" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>" DeleteCommand="DELETE FROM [aspnet_Users] WHERE [UserId] = @UserId" InsertCommand="INSERT INTO [aspnet_Users] ([ApplicationId], [UserId], [UserName], [LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate]) VALUES (@ApplicationId, @UserId, @UserName, @LoweredUserName, @MobileAlias, @IsAnonymous, @LastActivityDate)" ProviderName="<%$ ConnectionStrings:ASPNETDBConnectionString2.ProviderName %>" SelectCommand="SELECT [ApplicationId], [UserId], [UserName], [LoweredUserName], [MobileAlias], [IsAnonymous], [LastActivityDate] FROM [aspnet_Users]" UpdateCommand="UPDATE [aspnet_Users] SET [ApplicationId] = @ApplicationId, [UserName] = @UserName, [LoweredUserName] = @LoweredUserName, [MobileAlias] = @MobileAlias, [IsAnonymous] = @IsAnonymous, [LastActivityDate] = @LastActivityDate WHERE [UserId] = @UserId"> <InsertParameters> <asp:Parameter Name="ApplicationId" Type="Object" /> <asp:Parameter Name="UserId" Type="Object" /> <asp:Parameter Name="UserName" Type="String" /> <asp:Parameter Name="LoweredUserName" Type="String" /> <asp:Parameter Name="MobileAlias" Type="String" /> <asp:Parameter Name="IsAnonymous" Type="Boolean" /> <asp:Parameter Name="LastActivityDate" Type="DateTime" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="ApplicationId" Type="Object" /> <asp:Parameter Name="UserName" Type="String" /> <asp:Parameter Name="LoweredUserName" Type="String" /> <asp:Parameter Name="MobileAlias" Type="String" /> <asp:Parameter Name="IsAnonymous" Type="Boolean" /> <asp:Parameter Name="LastActivityDate" Type="DateTime" /> <asp:Parameter Name="UserId" Type="Object" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="UserId" Type="Object" /> </DeleteParameters> </asp:SqlDataSource></Content> ... cheers,imperialx
View 1 Replies
View Related
May 14, 2008
Hello All, i have 2 files in excel which i am uploading them to a folder which is located in the root directory. then i am importing these two files into a sqldatabase. i needed some help in importing them and i got it from my previous post at http://forums.asp.net/t/1261155.aspx but now i have a problem. the import works perfectly first time but when i do it the second time i am getting an error on of the file (header). to explain clearly, what i am doing is , every time i upload a new file , i am deleting the data from the tables, so the new data can be inserted ( basically trying to achieve overwirting the existing data). now this technique works fine with the Detail table but not with the Header table. i think the reason is the header table has a primary key on OrderID and a relationship does exists between the Header and Detail. now how would i overcome this error. can some one please guide me. I really appreciate it.here is my code: // connection for header file
protected OleDbCommand headExcelConnection() { // Connect to the Excel Spreadsheet
string headConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/imports/headerorder.xls") + ";" + "Extended Properties=Excel 8.0;"; // create your excel connection object using the connection string
OleDbConnection headXConn = new OleDbConnection(headConnStr); headXConn.Open(); // use a SQL Select command to retrieve the data from the Excel Spreadsheet // the "table name" is the name of the worksheet within the spreadsheet // in this case, the worksheet name is "Sheet1" and is expressed as: [Sheet1$]
OleDbCommand headCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", headXConn); return headCommand; } // importing header information
protected void BtnImpHeader_Click(object sender, EventArgs e) { PanelUpload.Visible = false; PanelView.Visible = false; PanelImport.Visible = true; LabelImport.Text = ""; // reset to blank // Create a new Adapter
OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(); // retrieve the Select command for the Spreadsheet
objDataAdapter.SelectCommand = headExcelConnection();
// Create a DataSet
DataSet objDataSet = new DataSet(); // Populate the DataSet with the spreadsheet worksheet data
objDataAdapter.Fill(objDataSet);
// deleting the exisitng table before copy
SqlConnection mycon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); SqlCommand SqlCmd = null; mycon.Open(); SqlCmd = mycon.CreateCommand(); SqlCmd.CommandText = "DELETE FROM Header"; ---- showing error over on second time SqlCmd.ExecuteNonQuery(); mycon.Close(); // entering the newer header information
SqlConnection mysqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); mysqlcon.Open(); foreach (DataRow dr in objDataSet.Tables[0].Rows) { String sqlinsert = "insert into Header values(@param1,@param2,@param3,@param4,@param5,@param6,@param7,@param8,@param9,@param10,@param11,@param12,@param13,@param14,@param15,@param16,@param17,@param18,@param19,@param20,@param21,@param22,@param23,@param24,@param25,@param26,@param27,@param28,@param29,@param30,@param31,@param32)"; SqlCommand cmd = new SqlCommand(sqlinsert, mysqlcon); cmd.Parameters.AddWithValue("@param1", dr[0].ToString()); cmd.Parameters.AddWithValue("@param2", dr[1].ToString()); cmd.Parameters.AddWithValue("@param3", dr[2].ToString()); cmd.Parameters.AddWithValue("@param4", dr[3].ToString()); cmd.Parameters.AddWithValue("@param5", dr[4].ToString()); cmd.Parameters.AddWithValue("@param6", dr[5].ToString()); cmd.Parameters.AddWithValue("@param7", dr[6].ToString()); cmd.Parameters.AddWithValue("@param8", dr[7].ToString()); cmd.Parameters.AddWithValue("@param9", dr[8].ToString()); cmd.Parameters.AddWithValue("@param10", dr[9].ToString()); cmd.Parameters.AddWithValue("@param11", dr[10].ToString()); cmd.Parameters.AddWithValue("@param12", dr[11].ToString()); cmd.Parameters.AddWithValue("@param13", dr[12].ToString()); cmd.Parameters.AddWithValue("@param14", dr[13].ToString()); cmd.Parameters.AddWithValue("@param15", dr[14].ToString()); cmd.Parameters.AddWithValue("@param16", dr[15].ToString()); cmd.Parameters.AddWithValue("@param17", dr[16].ToString()); cmd.Parameters.AddWithValue("@param18", dr[17].ToString()); cmd.Parameters.AddWithValue("@param19", dr[18].ToString()); cmd.Parameters.AddWithValue("@param20", dr[19].ToString()); cmd.Parameters.AddWithValue("@param21", dr[20].ToString()); cmd.Parameters.AddWithValue("@param22", dr[21].ToString()); cmd.Parameters.AddWithValue("@param23", dr[22].ToString()); cmd.Parameters.AddWithValue("@param24", dr[23].ToString()); cmd.Parameters.AddWithValue("@param25", dr[24].ToString()); cmd.Parameters.AddWithValue("@param26", dr[25].ToString()); cmd.Parameters.AddWithValue("@param27", Convert.ToDecimal(dr[26].ToString())); cmd.Parameters.AddWithValue("@param28", Convert.ToDecimal(dr[27].ToString())); cmd.Parameters.AddWithValue("@param29", Convert.ToDecimal(dr[28].ToString())); cmd.Parameters.AddWithValue("@param30", Convert.ToDecimal(dr[29].ToString())); cmd.Parameters.AddWithValue("@param31", Convert.ToDecimal(dr[30].ToString())); cmd.Parameters.AddWithValue("@param32", dr[31].ToString()); cmd.ExecuteNonQuery(); // new SqlCommand (stmt, mysqlcon).ExecuteNonQuery();
LabelImport.Text = "Rows Inserted"; } mysqlcon.Close(); }// connection for detail file
protected OleDbCommand detExcelConnection() { // Connect to the Excel Spreadsheet
string detConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/imports/detailorder.xls") + ";" + "Extended Properties=Excel 8.0;"; // create your excel connection object using the connection string
OleDbConnection detXConn = new OleDbConnection(detConnStr); detXConn.Open(); // create your excel connection object using the connection string // use a SQL Select command to retrieve the data from the Excel Spreadsheet // the "table name" is the name of the worksheet within the spreadsheet // in this case, the worksheet name is "Sheet1" and is expressed as: [Sheet1$]
OleDbCommand detCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", detXConn); return detCommand; }// importing detail information
protected void ButtonImport_Click(object sender, EventArgs e) { PanelUpload.Visible = false; PanelView.Visible = false; PanelImport.Visible = true; LabelImport.Text = ""; // reset to blank // Create a new Adapter
OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(); // retrieve the Select command for the Spreadsheet
objDataAdapter.SelectCommand = detExcelConnection();
// Create a DataSet
DataSet objDataSet = new DataSet(); // Populate the DataSet with the spreadsheet worksheet data
objDataAdapter.Fill(objDataSet);
// deleting the exisitng table before copy
SqlConnection mycon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); SqlCommand SqlCmd = null; mycon.Open(); SqlCmd = mycon.CreateCommand(); SqlCmd.CommandText = "DELETE FROM Detail"; SqlCmd.ExecuteNonQuery(); mycon.Close(); // entering newer detail information
SqlConnection mysqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); mysqlcon.Open(); foreach (DataRow dr1 in objDataSet.Tables[0].Rows) { String sqlinsert = "insert into Detail values(@param1,@param2,@param3,@param4,@param5,@param6,@param7,@param8,@param9,@param10,@param11,@param12,@param13)"; SqlCommand cmd = new SqlCommand(sqlinsert, mysqlcon); cmd.Parameters.AddWithValue("@param1", dr1[0].ToString()); cmd.Parameters.AddWithValue("@param2", dr1[1].ToString()); cmd.Parameters.AddWithValue("@param3", dr1[2].ToString()); cmd.Parameters.AddWithValue("@param4", dr1[3].ToString()); cmd.Parameters.AddWithValue("@param5", dr1[4].ToString()); cmd.Parameters.AddWithValue("@param6", dr1[5].ToString()); cmd.Parameters.AddWithValue("@param7", dr1[6].ToString()); cmd.Parameters.AddWithValue("@param8", Convert.ToDecimal(dr1[7].ToString())); cmd.Parameters.AddWithValue("@param9", Convert.ToDecimal(dr1[8].ToString())); cmd.Parameters.AddWithValue("@param10", dr1[9].ToString()); cmd.Parameters.AddWithValue("@param11", dr1[10].ToString()); cmd.Parameters.AddWithValue("@param12", dr1[11].ToString()); cmd.Parameters.AddWithValue("@param13", dr1[12].ToString()); cmd.ExecuteNonQuery(); LabelImport.Text = "Rows Inserted"; } mysqlcon.Close(); } the error is as follows:
Server Error in '/WebSite6' Application.
The DELETE statement conflicted with the REFERENCE constraint
"FK_Detail_Header". The conflict occurred in database "C:DOCUMENTS AND
SETTINGSMEMY DOCUMENTSVISUAL STUDIO
2005WEBSITESWEBSITE6APP_DATADATABASE.MDF", table "dbo.Detail", column
'OrderID'.The statement has been terminated. 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: The DELETE statement conflicted with the
REFERENCE constraint "FK_Detail_Header". The conflict occurred in database
"C:DOCUMENTS AND SETTINGSMEMY DOCUMENTSVISUAL STUDIO
2005WEBSITESWEBSITE6APP_DATADATABASE.MDF", table "dbo.Detail", column
'OrderID'.The statement has been terminated.Source Error:
Line 176: SqlCmd = mycon.CreateCommand();Line 177: SqlCmd.CommandText = "DELETE FROM Header";Line 178: SqlCmd.ExecuteNonQuery();Line 179: mycon.Close();Line 180: again i really appreciate.Thanks
View 1 Replies
View Related
Oct 26, 2004
Hi,
On my aspx Web page, I want to delete a member from database table 'tblMember', but if this MemberID is used as FK in another table, I want to display a user friendlier message like "You cannot delete this member, ....." I am using Try, Catch blocks in my Web Page.
Currently it display this message:
"DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_..._....' The conflict occurred in database '...', table 'tblMembers', column 'MemberID'. The statement has been terminated. "
So how should I precisely trap this error? Does anybody know what Exception is it? or what error number in SQL server?
Thanks
View 2 Replies
View Related
Jan 22, 2004
I'm trying to change the <NULL> fields of my table, but I don't know how to tell the query to look for <NULL>
For example:
select * from MyTable
where fieldx = <NULL>
This doesn't work.
How should it be?
View 1 Replies
View Related
Sep 16, 2006
Thank you for your help.
I run a website which uses SQL 2000 and VB ASP. I would like to add a section to the site which posts the most popular data being queried from my SQL server. I'm sure this is possible, but I don't know where to start. Please let me know if any of you need specifics regarding my data and set up.
Thanks again for the help! :)
JAC
View 6 Replies
View Related
Apr 16, 2005
Hi all...
I'm connected to a network with 3 Servers ( 3 sql servers on 3 different machines )
How can i know the name of the servers using SQL Code?
Thanks
View 1 Replies
View Related
Feb 1, 2006
How can I know how many connections are open at a given point of time while I am testing an ASP.Net application? The application uses SQL Server 2000 as its database.
View 1 Replies
View Related
Oct 10, 2007
I use SCD to extract and send as output further in the data flow only modified and new records. Before I write to DB and after SCD does its work, I have to execute different controls on both modified and new records. So, I send both SCD outputs (changing attribute updates output and new output ) to a Union All transformation, I execute the needed controls and then I want to insert/update the records in DB.
Is there a way to know which records SCD identified as new/modified after I unified them with a union all transformation? I can create a derivate column for one of the scd outputs and use it in a conditional split before writing to DB but I'd like to know if SCD sends any flags down the dataflow?
Hope I made the problem clear.
Thanx
Sara
View 17 Replies
View Related
Mar 31, 2006
I've called a resultset from SQL Server
using an SQL Selection. I need to iterate over that entire result set
(200+ columns/fields) and all I need are the random numbers contained
in any of the rows/columns. I don't want to have to name each
field/column and then use an if > 0 statement.Isn't there
some way to generically loop through the column's by index or something
instead of their field name so I can just use an integer loop to walk
the dataset? I know there is I've done it about 5 years ago. The
question is how do you do it in C#?SqlConnection thisConn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLQuery"].ConnectionString); SqlCommand thisCmd = new SqlCommand("Command String", thisConn); thisCmd.CommandText = "Select * from SelectionsByCountry where [" + DropDownList1.SelectedItem.ToString() + "] > '0'"; thisConn.Open(); SqlDataReader thisReader = null; thisReader = thisCmd.ExecuteReader(CommandBehavior.CloseConnection); while (thisReader.Read()) { DropDownList2.Items.Add(thisReader["System"].ToString().Trim());/*** There are 200+ columns left I want to walk over using a loop structure of some sort. How do I do that?*/ }- Rex
View 2 Replies
View Related
Jul 23, 2005
I'm working on a VB.NET project where we are intending to use MSDE as ourback-end database. The actual number of users is expected to be low and Idon't have any concerns as to whether MSDE will be up to the job (most ofthe time). I'm aware that if we end up with lots of users connecting to oursite at once (which may happen as certain times in the year when people needinformation for deadlines) then the performance governor in MSDE will kickin and slow everything down.What I don't know is how do I find out whether the performance governor haskicked in?I would like to know how to monitor this so we can make an educated decisionas to when we need to migrate to a full version of SQL Server (if at all).Can anyone point me in the right direction for finding this out?thanks,Brian Cryer.
View 2 Replies
View Related
Feb 22, 2008
Hi Folks,
Always sorry to have to ask what is most likely such a simple question. However I'm in no way a programmer, I'm just patching something together using scripts I've found all over the shop.
I'm reading in an excel source using a dataflow script component. I build up a SQL String in Vb.Net using the output column names and query the spreadsheet via microsoft.Jet.OLEDB.4.0 then processing it in code. I don't want to use the Excel Source task btw, more for the fact I want to learn from this as well as other less important reasons.
This all works fine. Adding new columns to the output means the query string dynamically changes without the script ever having those columns defined in code. Easy stuff so far.
The issue I have is writing back into the outputbuffer the results without explicitly mapping the result to the output column names.
An easy example to see is..
Excel Spreadsheet Looks like..
ServerName
Server1
Server2
Server3
Output Column Names..
ServerName
Code Snippet..
While Reader.Read
With OutputBuffer
.AddRow()
.ServerName = Reader.GetString(0)
End With
End While
Instead of defining what the column name (servername) is, I want to map back by matching the source column header to the output column name. By doing this I won't ever need to change any code when I add or remove output columns.
Could someone lend a hand with this, and I'll buy you a virtual beer or two. I've already spent more time searching for an answer than I have writing the code thus far, and I think my heads getting more and more muddled by it.
Many thanks,
Jode
View 1 Replies
View Related
Jun 23, 2006
I need to import few tables from MS Access to MS SQL but the table structure in Access is always different, as I would like the destination table in SQL to be.
Therefore I would like that a table would be created in SQL at runtime, according to the structure the Access table accessed has.
View 6 Replies
View Related
Jul 20, 2005
I have two tables in my database called CartItems and OrderItems. Istore all of a session's shopping cart items in the CartItems tableusing the sessionID as the identifier (called cartID in my DB). Afteran order is placed and is approved, I would like to copy all of theitems in the CartItems table for that given cartID to the OrderItemstable given a new orderID.I will know the cartID and orderID ahead of time and would like tosend them both into a stored procedure and have the transfer takeplace.Example:take this data...CartItems (table)--------------------------------------cartID | itemID | quantity | price--------------------------------------12345 2 1 12.9512345 7 2 17.95and make it this data...OrderItems (table)--------------------------------------orderID | itemID | quantity | price--------------------------------------00001 2 1 12.9500001 7 2 17.95via some stored procedure that I send (@cartID,@orderID)Any help would be greatly appreciated!!
View 5 Replies
View Related
May 13, 2008
Hi, all.
I am trying to create table with following SQL script:
Code Snippet
create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) constraint NN_Prj_Name not null,
Creator nvarchar (255),
CreateDate datetime
);
When I execute this script I get following error message:
Error source: SQL Server Compact ADO.NET Data Provider
Error message: Named Constraint is not supported for this type of constraint. [ Constraint Name = NN_Prj_Name ]
I looked in the SQL Server Books Online and saw following:
CREATE TABLE (SQL Server Compact)
...
< column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ] | REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]
As I understand according to documentation named constraints should be supported, however error message says opposite. I can rephrase SQL script by removing named constraint.
Code Snippet
create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) not null,
Creator nvarchar (255),
CreateDate datetime
);
This script executes correctly, however I want named constraints and this does not satisfy me.
View 1 Replies
View Related
May 13, 2008
We are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get the following error:
A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle
But the only PK on this table is RegTitleID.
The table structure is:
[RegTitleID] [int] IDENTITY(1,1) NOT NULL,
[RegTitleNumber] [int] NOT NULL,
[RegTitleDescription] [varchar](200) NOT NULL,
[FacilityTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL,
The problem occurs when a Title Number is inserted and a record with that number already exists. There is no unique constraint on Title Number.
Has anyone else experienced this?
View 3 Replies
View Related
Apr 27, 2007
I just have done the SSIS example in the tutorial document included when install SQL 2005 ENT. I have a problem that whenever I test to run, the service load all data from source with out noticing about the data (I mean it load all the data to the destination), I do it several time and it continue to load all without checking. That mean the data is dublicated when the schedule run???
I think there should be a paramete or something like that to help the engine just load the new data to the destination. Could you help please?
Thank
View 3 Replies
View Related
Sep 5, 2015
I was querying to find the first non null address value using the COALESCE function.And I got the correct result.But then I jumped into another question and i.e what if I need to find the column name without knowing the column nameand just by using the column value.What I mean is this...My query was.....
SELECT COALESCE(AddressLine1,AddressLine2) AS [Addresss] FROM Person.Address
This is what I got.
Address#500-75 O'Connor Street#9900 2700 Production Way00, rue Saint-Lazare02, place de Fontenoy035, boulevard du Montparnasse081, boulevard du Montparnasse081, boulevard du Montparnasse084, boulevard du Montparnasse1 Corporate Center Drive1 Mt. Dell Drive
But then what if I just know the address of that person i.e #500-75 O'Connor Street..How am I suppose to retrieve that without knowing the column name.
View 2 Replies
View Related
Sep 11, 2015
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
View 0 Replies
View Related
May 19, 2012
I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
View 18 Replies
View Related
Oct 23, 2004
Hello:
Need some serious help with this one...
Background:
Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...
Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.
The Problem:
There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.
This appears possible in SqlServer too --...as long as there are no CASCADE operations.
Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.
So -- please correct me if I am wrong here -- it appears that the operations would be
along the lines of:
a) Remove the Foreign Key references
b) Copy the table structure, and make a new temp table, adding the column
c) Copy the data over
d) Add the FK relations, that used to be in the first table, to the new table
e) Delete the original
f) Done?
The questions are:
a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'.
b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant?
c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.
Thanks!
Sky
View 1 Replies
View Related
Nov 13, 2006
I am having great difficulty with cascading deletes, delete triggers and referential integrity.
The database is in First Normal Form.
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:
Table A
/
Table B Table C
/
Table D
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
This is an example of my delete trigger:
CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
Hope this makes sense...
Thanks,
Josh
View 6 Replies
View Related
Nov 17, 2006
I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
DELETE FROM Broker_Rates
WHERE (Broker_Rates.BID <> Broker.BID)
Thanks
View 6 Replies
View Related
Nov 26, 2007
this is my Delete Query NO 1
alter table ZT_Master disable trigger All
Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
alter table ZT_Master enable trigger All
I have troble in Delete Query No 2
here is a select statemnt , I need to delete them
select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
I tried modified it as below
delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
but this doesn't works..
can you please help?
and can I combine these 2 SQL Query into one Sql Query? thank you
View 1 Replies
View Related
Feb 16, 2008
I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question)
View 2 Replies
View Related
Sep 16, 2013
The requirement is: I should allow single row delete from a table but not bulk delete. An audit table should get updated if there is any single delete or single update. So I wrote the triggers as follows: for single and bulk delete
ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
[code]...
When I try to run the website, the database error I am getting is:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
View 3 Replies
View Related
Aug 20, 2007
I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:
DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)
The status message (and @@ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.
I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.
Does anyone have suggestions on what might cause this sort of behavior?
View 3 Replies
View Related
Nov 20, 2007
Hi,
I have a problem with one report on my server. A user has requested that I exclude him from receiving a timed email subscription to several reports. I was able to amend all the subscriptions except one. When I try to remove his email address from the subscription I receive this error:
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Online no help couldn't offer any advice at all, so I thought I'd just delete the subscription and recreate it again, but I receive the same message. "Okay, no problem, I'll just delete the report and redeploy it and set up the subscription so all the other users aren't affected", says I. "Oh, no!", says the report server, and then it give me this message:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---
What's even weirder is that I'm the owner and creator of the report and I'm a system admin and content manager on the report server and I set up the subscription when the report was initially deployed. Surely I should have sufficient rights to fart around with this subscription/report as I see fit?
I have rebooted the server, redeployed the report, checked credentials on the data source and tried amending and deleting from both the report manager and management studio but still I am prevented from doing so.
Any help would be much appreciated.
Thanks in advance,
Paul
View 3 Replies
View Related
Feb 23, 2006
First, this is not my code.
This one is weird and I am missing something fundamental on this one. A developer was getting a timeout with this...
CREATE PROCEDURE p_CM_DeleteBatch
(
@SubmitterTranID VARCHAR(50)
)
AS
DECLARE
@COUNT INT,
@COMMIT INT
SET @COUNT = 0
SET @COMMIT = 1 --DO NOT CHANGE THIS. The Operation will be commited only when this value is 1
select @COUNT = COUNT(*) from claimsreceived
where (claimstatus NOT IN ('Keyed', 'Imported')) AND
SubmitterTranID = @SubmitterTranID
IF (@COUNT = 0) --This means that that Claims under this Batch have not been adjudicated & it is safe to delete
BEGIN
BEGIN TRANSACTION
DELETE FROM INVOICECLAIMMAPPING WHERE CLMRECDID IN (SELECT DISTINCT CLMRECDID FROM CLAIMSRECEIVED WHERE SUBMITTERTRANID = @SUBMITTERTRANID)
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM ClaimsPayment WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM ClaimsPaymentServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM ClaimsreceivedPayorServices where ClmRecdPyID in (SELECT ClmRecdPyID
FROM ClaimsReceivedPayors
WHERE SubmitterTranID = @SubmitterTranID)
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM ClaimsReceivedPayors WHERE ClmRecdid in (SELECT ClmRecdID
FROM ClaimsReceived
WHERE SubmitterTranID = @SubmitterTranID)
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM ClaimsReceivedServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM ClaimsReceived WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM BATCHLOGCLAIMS WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
IF (@COMMIT = 1)
BEGIN
--ROLLBACK TRANSACTION --For Testing Purpose ONLY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN (-1)
END
END
ELSE
BEGIN
RaisError ('This Batch cannot be deleted. It has claim(s) which has been Adjudicated', 16, 1)
END
GO
I applied a couple of indices and got ride of the uncorrelated subqueries
CREATE PROCEDURE p_CM_DeleteBatch
(
@SubmitterTranID VARCHAR(50)
)
AS
DECLARE
@COUNT INT,
@COMMIT INT
SET @COUNT = 0
SET @COMMIT = 1 --DO NOT CHANGE THIS. The Operation will be commited only when this value is 1
select @COUNT = COUNT(*) from claimsreceived
where (claimstatus NOT IN ('Keyed', 'Imported')) AND
SubmitterTranID = @SubmitterTranID
IF (@COUNT = 0) --This means that that Claims under this Batch have not been adjudicated & it is safe to delete
BEGIN
BEGIN TRANSACTION
DELETE INVOICECLAIMMAPPING
FROM INVOICECLAIMMAPPING
JOIN CLAIMSRECEIVED
ON INVOICECLAIMMAPPING.CLMRECDID = CLAIMSRECEIVED.CLMRECDID
WHERE CLAIMSRECEIVED.SUBMITTERTRANID = @SUBMITTERTRANID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM ClaimsPayment
WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM ClaimsPaymentServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE ClaimsreceivedPayorServices
FROM ClaimsreceivedPayorServices
JOIN ClaimsReceivedPayors
ON ClaimsreceivedPayorServices.ClmRecdPyID = ClaimsReceivedPayors.ClmRecPyID
WHERE ClaimsReceivedPayors.SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE ClaimsReceivedPayors
FROM ClaimsReceivedPayors
JOIN ClaimsReceived
ON ClaimsReceivedPayors.ClmRecdid = ClaimsReceived.ClmRecdid
WHERE ClaimsReceived.SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM ClaimsReceivedServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM ClaimsReceived WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM BATCHLOGCLAIMS WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
IF (@COMMIT = 1)
BEGIN
--ROLLBACK TRANSACTION --For Testing Purpose ONLY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN (-1)
END
END
ELSE
BEGIN
RaisError ('This Batch cannot be deleted. It has claim(s) which has been Adjudicated', 16, 1)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Suddenly this constraint was being violated with the change
ALTER TABLE [dbo].[ClaimsReceivedPayorServices] ADD CONSTRAINT [FK_ClaimsReceivedPayorServices_CLAIMSRECEIVEDPAYOR S] FOREIGN KEY
(
[ClmRecdPyID]
) REFERENCES [CLAIMSRECEIVEDPAYORS] (
[CLMRECPYID]
)
Is the delete on ClaimsReceivedPayors starting before the delete on ClaimsreceivedPayorServices finishes? If so why would it matter between the join and subquery? This one is making me depressed because I can not explain it.
View 2 Replies
View Related
