Hello I need to be able to regularly, update or delete data from my parent table and subsequent child tables from A to Z, each table contains data. However, I have having problems. I have already created the tables with primary keys on each table and foreign keys linking each table to the next. I tried to delete a row from the parent table and was given this error: DELETE FROM [dbo].[DomNam]WHERE [DomNam]=N' football ' Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_DomNam'. The conflict occurred in database 'DomDB', table 'Dom_CatA', column 'DomNam'.
I tried to insert an alter table query: ALTER TABLE dbo.DomNamADD CONSTRAINT FK_Dom_ID REFERENCES dbo.Dom_CatA (Dom_ID) ON DELETE CASCADE ON UPDATE CASCADE But on Execute I saw this error: Error] Incorrect syntax for definition of the 'TABLE' constraint What is wrong with the above syntax? Or would it be better if I used a trigger instead because I already have foreign keys set within the tables?If so please give an example of the syntax for the trigger I would need to update and cascade data from all tables. I would be grateful for any advice. Thanks.
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
I have a table with the following columns,NAME, TYPE, TAGAnd there may be 'duplicates' on name and type.How can I delete them??I want to delete all with duplicate NAME and TYPE
ID LastContact 1a 4/12/2003 2d 5/12/2004 1a 9/12/2006 3c 6/12/2005
...and so on
I need to clean up this table so that I have one unique ID, with the latest 'Last Contact' date....so for this example, after the delete query I would only have '1a' with the '9/12/2006' LastContact colum..and the other two rows would be unaffected, since they didnt have a duplicate id..so after the delete query, my table would look like this;
ID LastContact 2d 5/12/2004 1a 9/12/2006 3c 6/12/2005
How would I write such a query...some sort of grouping ?
I have the following SELECT query, the results of which I would deletefrom the table they're pulled from:SELECT A.* FROM SalesOrderPartPrices AWHERE EXISTS(SELECT 'Exists' FROM SalesOrderPartPrices BWHERE(A.SalesOrderNo = B.SalesOrderNo) AND(A.PartNo = B.PartNo) AND(A.UnitPrice = B.UnitPrice) AND(A.RowID > B.RowID))However, when I try to run the following query, I get an error ("Msg170, Level 15, State 1, Line 1: Incorrect syntax near 'A'."):DELETE FROM SalesOrderPartPrices AWHERE EXISTS(SELECT 'Exists' FROM SalesOrderPartPrices BWHERE(A.SalesOrderNo = B.SalesOrderNo) AND(A.PartNo = B.PartNo) AND(A.UnitPrice = B.UnitPrice) AND(A.RowID > B.RowID))What am I doing wrong, and how do I resolve the issue? Any help isgreatly appreciated....... thanks in advance!-=Tek Boy=-
I am hoping this is a quick easy question for someone! :)I am trying (struggling) with moving data from Sql Server to a LotusNotes table.I am using SQL Server 2000, I have a Lotus Notes linked server (usingNotesSQL), and I wasnt to clear the table (delete all records) and thenreload it from my data on SQL Server.What is the syntax to delete the records?My select statement would be like this:select * from openquery([LinkedServer], 'select * from NotesTable')where lastName='Smith'My delete statement ??? -- cant quite figure out the syntax of thisone....select * from openquery([LinkedServer], 'delete from NotesTable') wherelastName='Smith'(this doesnt work)Thanks!
I have some data that I'd like to order by a certain attribute.. but if there is a tie, then it should order by a secondary attribute.. and if there's still a tie.. then a 3rd attribute.
Currently the query looks like this:
SELECT * FROM Players WHERE ORDER BY Points
But I want it to look something like this (I know this doesn't work.. but it's just to give an idea):
SELECT * FROM Players WHERE ORDER BY (Points desc AND Games asc AND Goals desc)
Does anyone know the proper syntax for a multiple ORDER BY query like I described above?
What is the syntax when creating a SQL statement and using a column that has a multiple word name?
I know that in query analyzer it should be delimited? by double quotation marks like such: SELECT Item, "Item Description" FROM ComponentList WHERE Item = 'CPU' The above works great in Query Analyzer on the device and in the device emulator.
but I'm attempting to build a SQL string in code...VB.Net using CF and PocketPC SDK, SQLCE on the PocketPC device....
Here's the snippet that does not work: sql = "SELECT Item, 'Item Description' FROM ComponentList WHERE Item = '" & selectedItem & "' "
While drItems.Read() .....More code here not copied
I'm trying to fill a listview control and when I use the above syntax one of the columns is filled with the text Item Description instead of pulling the actual Item Description from the table...So what is the correct way to construct the SQL string?
I am converting all the inline sql in my ASP.NET app to stored procs and I am experiencing some difficultly with my shortest and least complex stored proc. I am new to both ASP.NET and SQL especially using stored procedures so this may have some extremely obvious flaw in it. I am trying to delete a entry that has parts in two tables. I created a stored procedure along the lines of: CREATE PROCEDURE DeleteEntry(Â Â Â Â Â @EntryIDINT)ASDELETE FROM firstTable WHERE entry_id = @EntryIDDELETE FROM secondTable WHERE entry_id = @EntryIDI have a remove button on a asp page that executes the stored procedure but it only removes the entry from the second table the first time I click it and then when I click it a second time it removes the entry from the first table.
The method I wrote to delete records is working if there's one record, but not for more than one. This method takes a string, and I've examined what is passed in and everything looks ok, but then no error occurs where there's more than one record, but no delete occurs either. Here's what I see in my Trace.Warn statement: Delete From Photo_TBL where PhotoID IN ('223,224') So the sql looks fine but I can't figure out why it's not working. Here's my method for deleting. Can you see what might be wrong? Thanks public void PerformDeletion(string photoID) { //Response.Write(photoID);
// Set up SqlCommand, connection to db, sql statement, etc. SqlCommand DeleteCommand = new SqlCommand(); DeleteCommand.Connection = DBConnectionClass.myConnection; DeleteCommand.CommandType = CommandType.Text;
// Store Primary Key photoID passed here from DeleteRows_Click // in a parameter for DeleteCommand SqlParameter DeletePrimaryKeyParam = new SqlParameter(); DeletePrimaryKeyParam.ParameterName = "@PhotoID"; DeletePrimaryKeyParam.Value = photoID.ToString();
// Insert new parameter into command object DeleteCommand.Parameters.Add(DeletePrimaryKeyParam);
// Delete row, open connection, execute, close connection DeleteCommand.CommandText = ("Delete From Photo_TBL where PhotoID IN ('" + photoID + "')"); Trace.Warn(DeleteCommand.CommandText); // DeleteCommand.Connection.Close(); DeleteCommand.Connection.Open(); DeleteCommand.ExecuteNonQuery(); DeleteCommand.Connection.Close();
// Call BindData so GridView is binded and most recent changes appear BindData(); }
OK, this is a big problem, with multiple tables, but here goes. Here's my schema:
-------------------------------------------------- Events -------------------------------------------------- ID | E_Title --------------------------------------------------
-------------------------------------------------- EventOptionGroups -------------------------------------------------- ID | EOG_EventID | EOG_OptionGroupID --------------------------------------------------
-------------------------------------------------- OptionGroups -------------------------------------------------- ID | OG_Title --------------------------------------------------
-------------------------------------------------- Options -------------------------------------------------- ID | O_OptionGroupID --------------------------------------------------
-------------------------------------------------- EventRegistration -------------------------------------------------- ID | ER_EventID --------------------------------------------------
-------------------------------------------------- RegistrantOptions -------------------------------------------------- ID | RO_EventRegistrationID | RO_OptionGroupID --------------------------------------------------
OK, what I'm trying to do is, when I delete an event, I need to delete all the data associated with that event. So here's the thought process.
Delete Event based on ID Delete all EventRegistration where ER_EventID = Event.ID Delete all RegistrantOptions where RO_EventRegistrationID = EventRegistration.ID Delete all EventOptionGroups where EOG_EventID = Event.ID Delete all OptionGroups where OptionGroups.ID = EOG_OptionGroupID Delete all Options where O_OptionGroupID = OptionGroups.ID
Sorry that it's so complicated, by I need help. There are foreign key constraints on the tables as well, so you have to work from the bottom back up.
I have 30 or 40 tables in a database called Temp with names like PV_ and i am trying to figure out how to loop through and delete them. I've searched all over the web and forums looking for an answer but can't seem to find it. Does anyone know how to delete tables with the keyword like?
Hello All, I have a table: idSurrogate int identity id1 int id2 int
id1 + id2 is a unique index
I need to delete multiple rows from the table given a list of id1 and a list of id2 In other words @id1List = '10,20,30' @id2List = '1,3,5' I need to delete these 3 rows from the table 1) @id1=10 and @id2=1 2) @id1=20 and @id2=3 3) @id1=30 and @id2=5
I am a bit lazy today - can anyone help out with a delete sql stmt
I'm trying to run a SELECT on 3 tables: Class, Enrolled, Waiting. I want to select the name of the class, the count of the students enrolled, and the count of the students waiting to enroll.
My current query...
SELECT Class.Name, COUNT(Enrolled.StudentID) AS EnrolledCount, COUNT(Waiting.StudentID) AS WaitingCount FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID GROUP BY Class.Name
...results in identical counts for enrolled and waiting, which I know to be incorrect. Furthermore, it appears that the counts are being multiplied together (in one instance, enrolled should be 14, waiting should be 2, but both numbers come back as 28).
If I run this query without one of the joined tables, the counts are accurate. The problem only occurs when I try to pull counts from both the tables.
Can anyone find the problem with my query? Should I be using something other than a LEFT OUTER JOIN?
Hi: I have 3 tables namely: 1 Category(CategoryID(int), CategoryName(varchar), 2 SubCategory( CategoryID(int),SubcategoryID(int),SubcategoryName) 3 Productlist (ProductID(int),ProductName(varchar),CategoryID(int), CategoryName(varchar),SubcategoryID(int),SubcategoryName(varchar)) how to delete correspoding subcategories of category from SubCategory,Productlist tables using triggers Ex: Category :TV Subcategory:ColorTV,Plasma,LCD...Plz Send me the query.... Thanks
I have a table with a large number of records that I need to delete, before attempt to perform the delete I also archived the records to another table.
So I need to delete all of these selected records stored in the archive table from the main table. I can now reference all the records that qualify for the delete in the main table by performing a join on the archive table like so:
select * from my_main_table a join my_archive_table b on a.distinct_id=b.distinct_id and a.surrogate_key=b.surrogate_key and a.identifier=b.indentifier
So all the records check out to be the ones I'd like to perform a delete on but I just can't figure out how to perform a delete of the records with little or no change to the existing query.
Obviously something like this won't work:
delete from my_main_table a join my_archive_table b on a.distinct_id=b.distinct_id and a.surrogate_key=b.surrogate_key and a.identifier=b.indentifier
Though it would be nice if it did.:D
So my question is how would I use the existing query with some modification to delete only the records that this query returns. I've tried selection of records in the main table based on the existing records in the archive table but it can return a higher number of records than what I know is expected. I actually need the join specified to be in place to do it.
I have an SQL 2000 server. I have multiple tables in the db that have a row with a time stamp of '10-23-2007'. What I am trying to do is delete these specific rows because they don't belong. So I need to query the db for table names that are like 'elect_Sub%' and then execute a query on those tables that would delete the row with the time_stamp '10-23-2007'. I know that I have to use the db schema to get the table names, but I need help in writing the sql script that will automatically scroll through the tables.
Seasons greetings to everyone,A simple question. Could someone show me the syntax to produce multiple (2 or 3) result sets in a stored proc and how you access those sets from a c# program (ASP.NET)..Couldn't find a reference on Google, maybe I was asking the wrong question! Thanks for any help regardsDavej
SELECT @idAgency = idAgency FROM tblAgencies WHERE strAgencyId = @strAgencyId;
SELECT strName, strAddress1, strAddress2, strCounty, strCountry, strPostcode, strTelephone, strFax, bitActive, bitHeadOffice, bitFinanceBranch FROM tblBranches WHERE fk_idAgency = @idAgency
SELECT strFirstName, strSurname, strUsername, bitActive FROM tblUsers WHERE fk_idAgency = @idAgency
GO
It basically first declare's and sets @idAgency using the first small select statment, then uses that parameter to run two more selects queries which I want sending to a dataset. Now within the Query Analyzer this works fine. But in my asp.net page it trows up this error:
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'spAdminAgencyActivate_Select'
Now the code im using in the asp.net page is as follows:
Dim objSqlConnection as New SqlConnection(ConfigurationSettings.AppSettings("strCon")) Dim objSqlCommand_Select as New SqlCommand("spAdminAgencyActivate_Select", objSqlConnection)
Can anyone help? I believe the error is in the stored procedure somewhere, because if I change the stored procedure so no parameters are being passed to it then it starts working. This is what I comment out and change for it to to get it working, but obviously this is not satisfactory as a final result because the parameter is hard coded in the stored procedure. Im just showing this to see if it gives anyone a clue!!
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Hi, all I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code. First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]"> </asp:SqlDataSource> And the dropdown list's code:<asp:DropDownList ID="ddlPayrollCompany" runat="server" AutoPostBack="True" DataSourceID="sqlDataSourcePayrollCompany" DataTextField="DisplayName" DataValueField="PayrollCompanyID"> </asp:DropDownList> Then, I use the selectedindexchanged event to bind the data to the datagrid. Here's that code: 1 Sub BindData() 2 3 Dim ds As New DataSet 4 Dim sda As SqlClient.SqlDataAdapter 5 Dim strSQL As String 6 Dim strCon As String 7 8 strSQL = "SELECT [SocialSecurityNumber], [Prefix], [FirstName], [LastName], [HireDate], [PayrollCostPercent], " & _ 9 "[Phone], [BadgeNumber], [IsSupervisor], [SupervisorID], [IsUser], [IsScout] FROM [rsrc_Personnel] " & _ 10 "WHERE ([PayrollCompanyID] = @PayrollCompanyID)" 11 12 strCon = "Data Source=DATASOURCE;Initial Catalog=DATABASE;User ID=USERID;Password=PASSWORD" 13 14 sda = New SqlClient.SqlDataAdapter(strSQL, strCon) 15 16 sda.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@PayrollCompanyID", Me.ddlPayrollCompany.SelectedItem.ToString())) 17 18 sda.Fill(ds, "rsrc_Personnel") 19 20 dgPersonnel.DataSource = ds.Tables("rsrc_Personnel") 21 dgPersonnel.DataBind() 22 23 End Sub 24
I'm assuming my problem lies in line 16 of the above code. I've tried SelectedItemIndex, SelectedItemValue too and get errors for those, as well. What am I missing? Thanks for anyone's help! Cappela07
i am using asp.net vb, i have 2 table show as below if i want to delete the forumid 1 row,then how would i delete the topic table who belong to the forumid 1. how would 1 do it if i am using gridview Forum table Forumid | Forumname 1 | hi 2 | me Topic table Topicid | Forumid | Topicname 1 | 1 | yo 2 | 1 | everyone 3 | 1 | google
How can I run through an array of ID's and delete the fields associated to it in a single table? Â I have a lot of bad data that needs to be removed. Â BTW, I'm using the SQL Server Management Studio to do this.
I'm currently doing by hand by using the following.
USE database; DECLARE @id integer SET @id = XXXXX DELETE FROM table WHERE id = @id;
I would like to use the same code but with a slight variation to support an array.
Hi, I'm having an SSIS package which gives the following error when executed :
Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server
The package has a single Execute SQL task with the properties listed below :
General Properties Result Set : None
ConnectionType : OLEDB Connection : Connected to a Local Database (DB1) SQLSourceType : Direct Input SQL Statement : exec(?) IsQueryStorePro : False BypassPrepare : False
Parameter Mapping Properties
variableName Direction DataType ParameterName
User::AddLinkSql Input Varchar 0
'AddLinkSql' is a global variable of package scope of type string with the value Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'
When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"
I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough it gives the same parse error message.
I would appreciate if anybody can help me out of this issue by suggeting where the problem is.
Hi All, can someone help me, i've created a stored procedure to make a report by calling it from a website. I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me. The code i use is:
CREATE proc CP_Cashbox @mID varchar,@startdate datetime,@enddate datetime as set dateformat dmy go declare @startdate as varchar declare @enddate as varchar
--print "query aan het uitvoeren"
select sum(moneyout) / sum(moneyin)*100 as cashbox from dbo.total where machineID = '@mID' and njdate between '@startdate' and '@enddate' GO
ALTER TABLE [dbo].[CalCalendar] ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED
is answered with:
Server: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'PRIMARY'.
which I consider to be interesting. Anyone has an idea why? I checked documentation but I do not see an error.
Note that:
ALTER TABLE [dbo].[CalCalendar] ALTER COLUMN [OID] uniqueidentifier NOT NULL
DOES get executed, and
ALTER TABLE [dbo].[CalCalendar] ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY
produces the same error.
Now, in my understanding this has nothing to do with an index may already exist etc. - the eror indicates a SYNTAX error, before any checking. Makes no sense to me, though, reading the documentation.
