It is working but when i click the delete button.. this appears
Must declare the scalar variable "@UserId".
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: Must declare the scalar variable "@UserId".
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
Hi i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table. regards vinod.t.v
/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months: -- Purge records in small portions to avoid locking production tables -- for a long time. The process takes longer, but can co-exist with -- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45 Incorrect syntax near 'Failed:'.
I need a query to DELETE the first few records in a table. For example: something like .....delete top 1000 from tablename. I figured that TOP dosent work with the DELETE. It only works with SELECT and UPDATE statments.
I cannot hardcode any values in the WHERE clause due to certain constraints in my application.
I have edited the aspnet_Users_CreateUser stored procedure so that the UserId that is created when a new user is created is copied to a UserId field in another table. However, when I use the website administration tool to delete users that have been created, it gives me an error saying the delete statement conflicted with the reference constraint. I then added the following code in the aspnet_Users_DeleteUser procedure.... IF ((@TablesToDeleteFrom & 16) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N'vw_tt') AND (type = 'V')))) BEGIN DELETE FROM dbo.userclassset WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 hdhd END This code was then added to the function at the end which deletes the data from the aspnet_Users table when everything else has been removed (@TablesToDeleteFrom & 16) <> 0 AND Now when I delete a user in the website admin tool, it "deletes" (with no error) the user from the list but doesnt actually physically delete it from the database. Any ideas?
I have a data source which I created a custom statement for which I need to delete from. I created the folowing selecct statement SELECT [CompId], [Description], [CompName], [OS], [UserName], [DriverEntryId] FROM [SrcComputer] WHERE ([UserName] = @UserName) I wrote the following DELETE statement DELETE FROM SrcComputer WHERE (CompId = @CompId) I have tested it in SQL server management and it does what I want it to do, but I don't know how to use it in my code. I put it inside of the DELETE tab inside of my custome statement and added a delete button inside of the gridview which utializes my data source, but it's not working. I don't know how to set the CompId variable? I want the statement to delete the row that the user clicks on. Can anyone give me some advice?
I have three tables 1. membership table(aspnet_membership table) 2. User Contacts table 3. Address table These three table have relation ship with one another through a UserId field. How will you set up cascaded delete on these tables, Like if I delete a user in the membership table I want the related records in the other tables to be deleted as well. Cascaded delete is it something done through code, or is it definde when the tables are created. Please advice.
Hey Guys,I have Performance Monitor running and storing the network usage to my MsSQL database, and this is done a few times a minute. I have a page that then shows show much of my bandwidth is being used. As you can gather, the database quickly starts filling up with hundrreds of records so I could do with a script that delete these records. I cant simply delete all records because that would cause my webpage to fail so I need a way to delete all records apart from the latest one. Wondering if anyone would know how I could do this?
I am new to SQL backups but what I have done is created a script sp_BackupProcessor that is used to determine which backup type is run (Full,Differential,Transaction). I am backing up to the local drive and then our tape backup system backs up the files on our local system to tape. I only want to keep 3 days worth of backups on our local machine since the same information is stored on tape. What is the best way to handle this? Any help or ideas would be appreciated. I have included my script for reference.
How can you delete a log file. Say I have backed up a transaction log at some drive say G:. Now I want to delete this log file and want to define it in a job. What is the syntax - delete G:Test_log.bak ???? What is a proper syntax?? Thanks!
Does anyone know why it takes a long time (Approx 10 minutes) to delete a DB. I have 2 identical environments for test and prod. I can delete DBs from my test environment quickly but it takes forever to delete a DB from the production environment. I am running SQL2000 SP3a. There are about 25 DBs in the environment with their sized ranging from 100meg to 2 gig. The only differnece in the environments is all test DBs are simple recovery mode and modt prod DBs are full.
I need to delete duplicate records from a SQL table where only the key field is not a duplicate. I'm not having any luck with my script. Does anyone have a scrip that might work? Thanks
I have two identical databases. I need to delete the even customer numbers from the first database and the odd numbers from the other database. I know I can do this writing a delete statement for each table. However there are a total of 54 tables.
Does anyone know a faster way to write the script without writing 54 scripts?
i have a table that i think is bad - how do i delete the table? also i want to copy this same table from another database - can i use enterprise manager to copy the same table in place of the deleted table?
I have a table with 100,000 plus records in it, and some are duplicates. Is there any way to delete one of them and not the other. For instance, if I duplicate the table I could run this query. <cfquery name="query1" datasource="datasource"> DELETE DISTINCT FROM tablename WHERE FirstName in ( SELECT FirstName from tablename1 where tablename1.FirstName = tablename.FIRST_NAME AND tablename1.LastName = tablename.LAST_NAME AND tablename1.State = tablename.STATE) </cfquery>
However, it doesn't work. I know the distinct is not correct. But does anyone know how to achieve this, I have looked all over, and everything I try deletes both records. I was thinking of using some kindof count statement, but it still deletes both of them. Please help. Thanks
I must admit I dont know all that much about SQL, which is why I hope someone can show me the light. I have a script almost finished, however I have no idea how to have it trim database entries that are older than, say, 90 days. Any ideas?
I have a table with approx 5 million rows and 36 columns. It takes approx 4 minutes to delete 1 row. The table has 3 indexes in addition to it's primary key and has twelve foreign key constraints. We are still using sequel 7. There is a backup run every night as part of the nightly maintenence that reorg/reindexes and checks the database integrity. Any thoughts?
I have a table with a load of orphaned records (I know... poor design) I'm trying to get rid of them, but I'm having a brain cramp.
I need to delete all the records from the table "Floor_Stock" that would be returned by this select statement:
SELECT FLOOR_STOCK.PRODUCT, FLOOR_STOCK.SITE FROM PRODUCT_MASTER INNER JOIN FLOOR_STOCK ON PRODUCT_MASTER.PRODUCT = FLOOR_STOCK.PRODUCT LEFT OUTER JOIN BOD_HEADER ON FLOOR_STOCK.PRODUCT = BOD_HEADER.PRODUCT AND FLOOR_STOCK.SITE = BOD_HEADER.SITE WHERE (BOD_HEADER.BOD_INDEX IS NULL) AND (PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's'))
I was thinking along the lines of:
DELETE FROM FLOOR_STOCK INNER JOIN (SELECT FLOOR_STOCK. PRODUCT, FLOOR_STOCK.SITE FROM PRODUCT_MASTER INNER JOIN FLOOR_STOCK ON PRODUCT_MASTER. PRODUCT = FLOOR_STOCK.PRODUCT LEFT OUTER JOIN BOD_HEADER ON FLOOR_STOCK. PRODUCT = BOD_HEADER. PRODUCT AND FLOOR_STOCK.SITE = BOD_HEADER.SITE WHERE (BOD_HEADER.BOD_INDEX IS NULL) AND (PRODUCT_MASTER.PROD_TYPE IN ('f', 'n', 'k', 'b', 'l', 's'))) F ON FLOOR_STOCK. PRODUCT = F. PRODUCT AND FLOOR_STOCK.SITE = F.SITE
... but Sql Server just laughs at me: "Incorrect Syntax near the keyword INNER"
I have a database and I see that I have a lot of TRN files behind it taking up more than 82GB disk space.I have a TRN file from Jan until today. I plan to delete every one of them until April to recover 59 GB of disk space. Would that be OK?
Yes, It can be a stupid question but I was trying to understand if it is really impossible! I have a big table (around 200 millions of records) and I have to delete the old record. I read about the hints and ROWLOCK seems to be perfect. I don't want to have pages locked at all.
However, when I am deleting this table using rowlock I got the following locks mode - key , type U (Update) and mode - tab, type X (Exclusive).
I have tried a SELECT on new records but got to wait.
Is there a chance to lock only the records to be deleted?
I have a msde back end for my application. I also have an Access data file with linked tables to the SQL one. My app accesses the data through the Access one through ODBC.
I have a data control and when try to delete a record as so: DataControl.Recordset.Delete I get a 'ODBC Call Failed' error.
Any ideas why? The recordset's updateable property is true.
I have a table which consist of 6,185 rows and from this table I want to delete 427 rows. I have placed the 427 rows in a separate table. So how do I delete these 427 from the original table(6185 rows).
I should also mention that the table does not have a primary key so I was thinking about something like this but it didn't work
where exists (select h.CardNumber ,h.ref_no ,h.tran_date ,h.tran_val from test427 h where( h.ref_no=o.ref_no and h.CardNumber = o.CardNumber and h.tran_date =o.tran_date and h.tran_val =o.tran_val ))
How do I delete from multi tables? There are no issues with keys.
Will this work? DELETE MC.Document FROM dbo.dw_MasterClaim MC, dbo.dw_MasterClaim_Checks CHK, dbo.dw_MasterClaim_ChgDate,dbo.dw_MasterClaim_Diagnosis, dbo.dw_MasterClaim_InsNo, dbo.dw_MasterClaim_Pay, dbo.dw_MasterClaim_ProcLine, dbo.dw_MasterClaim_ProcLine2, dbo.dw_MasterClaim_ProcLine3, dbo.dw_MasterClaim_RiskPool, dbo.dw_MasterClaim_SuspHist WHERE MC.Document IN (Select Document FROM fl1stDwImport.dbo.raw_MasterClaim WHERE Document = MC.Document)
Here is the scenario. I'm working with two tables:
Contact1 Conthist
Contact1 contains basic contact information and conthist contains history records for those contacts. Conthist can hold many records related to a single contact1 record.
The link between the two tables is a column called accountno.
I'm trying to delete any records in conthist that have an accountno that does not exist in contact1. The queries that I've tried keep returning conthist records that do actually have a matching accountno.
