While performing import actions I had a system freeze, when the system returned the sessions had been closed and the database had vanished, with the help of support we recovered the database only to find that the original project ID had a suffix attached ( Original 40/0110, New 40/0110-1 ), when I try to return it to it's original numbering convention it says it has to be a unique number which suggests to me it is not deleted but hiding in the background, can the original be recovered or is it possible to renumber the recovered database, I have searched the whole of the databases and the original is nowhere to be seen.
Basically, I create a database with sql, then I delete it manually(not via sql statment. This is a problem which I realise. In fact, you can't delete the database because the VS 2005 still is using it) I run the same code again, then it says the database still exists, even it is physically destroied.
------Here is the errors: System.Data.SqlClient.SqlException: Database 'riskDatabase' already exists. at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolea n breakConnection)
------The evidence that the database doesn't exist physically: Unhandled Exception: System.Data.SqlClient.SqlException: Cannot open database "riskDatabase" requested by the login. The login failed.
------The code: /* * C# code to programmically create * database and table. It also inserts * data into the table. */
using System; using System.Collections.Generic; using System.Text;
using System.Configuration; using System.Data; using System.Data.SqlClient; using System.IO;
namespace riskWizard { public class RiskWizard { // Sql private string connectionString; private SqlConnection connection; private SqlCommand command;
Can we have an sql server installation where we dont have a master database. Can the complete data dictionary be stored in another database , or put it other way can master database be renamed.
I have a need to assume that there will always be a master database for any SQL server instance. Want to confirm whether this assumption is true or not.
Hi, I'm using SQL Server 2005, and I would like to understand how to create and to drop a database without errors: Infact, if I try to create a database that already exists, SQL Server throws the error "Impossible to create the database because it already exists", and if I try to drop a database that doesn't exist, SQL Server throws the error "Impossible to drop the database because it doesn't esist". Before creating or dropping a database, I should control if it exists or not... Is there a method to do that? I found that such control for a table is the following one (in this case, I drop the table only if it exists):
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table1]
I tried to adapt the statement to the database case, modifying it as follows: if exists (select * from dbo.sysobjects where id = object_id(N'[Database1]') and OBJECTPROPERTY(id, N'IsDatabase') = 1) drop database [Database1]
but it didn't function (it was a blind attempt). Can you suggest me a statement to do that? Thank you very much
What would be the best practice to prevent users who didn't create a record in sql from deleting? When a record is created I have the username who created the record in one of the fields. I was thinking maybe a query?
It is an option to set deletion without getting logged since I have problem to delete two years historical data and would like to keep this year data on my 80MB rows. Actually I create a new table to get copy one-year data and I truncated the old table. I am wondering if there is other better way to do this task.
We are currently setting up out production server to the following requirements:
1. Every month, delete records that haven't been changed in the last 90 days. 2. Replicate insert statements to a backup database which will keep track of all data, and act as an archive/data warehouse.
The first step is easy, as it is just a script that checks the date of the last change on each row. However, the second step is a bit more tricky. We tried setting up replication between two test databases, but we ran into the following problem: Whenever old data has been deleted in the production database, the replication agent deletes it in the data warehouse database too.
Is it possible to override or disable this, so data is only inserted/updated, and not deleted? No applications using the database deletes records, so database integrity should not be a problem.
I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database.
Hi Everyone,I have a table in which their is record which is exactly same.I want to delete all the duplicate keeping ony 1 record in a table.ExampleTable AEmpid currentmonth PreviousmonthSupplimentarydays basic158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00I want to delete 2 rows of above table.How can I achieve that.Any suggestion how can i do that.Thank you in advanceRichard
Ok, so I have an issue, was wondering if anybody else has any suggestions.
I have a table that is pretty large, in all regards. It is a "message" table that holds text messages that users send to each other.
1. Has some data fields, integers, dates, some bit columns, a message subject field (varchar(250)), and a message body field (field type = text) 2. Table contains about 70 million records 3. Table has 6 indexes associated to it 4. Table has 2 views associated to it. 5. Table has 8 foreign keys associated to it.
I need to delete, oh, about 90,000 records out of this 70 million record table. I am able to disable the foreign keys to this table for deletion, but that does not seem to mitigate the problem. I think the issue lies with having to update the indexes as well as the views.
When I execute the select statement to retrieve the records I need to delete, it executes pretty quickly, no problems there that I can see.
The issue comes when I try to delete the records, it takes way too long, and we know it. We let it run for an hour and it didn't really get anywhere. This is in a server environment, some pretty decent hardware, 8gig memory, fast SCSI drives, 8 core processors, i don't know the exact specifics, but they're not bad.
This is from our dev environment which is but a portion of our production db- but I presume our production environment will have similar percentages (not necessarily the pages scanned)
Any suggestions on how to delete records efficiently?
Im using SQL enterprise manager v8, a few days ago I got a report that a user account was deleted. I was wondering what logs would point this out. I've been through the event review and i am not seeing any usefull info.
Obviously to delete all records from DB table is simple, however, I would like to make my whole Live DB pretty much empty. I've copied all my data from my test DB over to my live DB (didn't mean to but I did). I would like to remove all the data and the identity values, resetting them back at their original values. Is there a simple way or do I have to do it the hard way. That being going in and removing Identity, saving and then placing identity back on the DB Table.
i am trying to delete rows where a particular column (hours) has the same value for the same member (primary key) but where the effective dates are different. i want to delete the duplicate(s) rows which have the most recent effective date(s).
--If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted) set @var_db_contract =(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) IF @var_db_contract IS NOT NULL BEGIN SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_fiyr=(SELECT a.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) set @var_chk ="Y" END ELSE BEGIN SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_fiyr=(SELECT b.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) set @var_chk="N" END SET @var_amt_paid=(SELECT a.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_amt_old=(SELECT b.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no ) SET @var_amt_result =ISNULL(@var_amt_paid,0) - ISNULL(@var_amt_old,0)
SET @var_amt_ret = (SELECT a.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no) SET @var_amt_ret_old=(SELECT b.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no) SET @var_amt_ret_result = isnull(@var_amt_ret,0) - isnull(@var_amt_ret_old,0)
SET @var_quant_new = (SELECT a.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no) SET @var_quant_old =(SELECT b.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no) SET @var_quant_result = isnull(@var_quant_new,0) - isnull(@var_quant_old,0) SELECT @item_new = new_item FROM VALID_ITEM WHERE DB_CONTRACT = @var_db_contract AND PC_CODE = @var_PC AND ITEM_NO = @var_ITEM
UPDATE ae_contract set amt_paid_contr = isnull(amt_paid_contr,0) +@var_amt_result, amt_ret_contr = isnull(amt_ret_contr,0) + @var_amt_ret_result where db_contract = @var_db_contract
IF @item_new = 'N' BEGIN update vendor set used_amt = isnull(used_amt,0) + @var_amt_result + @var_amt_ret_result where db_vendor = (select gen_contr from ae_contract where ae_contract.db_contract=@var_db_contract); END UPDATE enc_det set amt_paid_fy = isnull(amt_paid_fy,0) + @var_amt_result, amt_ret_fy = isnull(amt_ret_fy,0) + @var_amt_ret_result where db_contract = @var_db_contract and pc_code = @var_pc and fy = @var_fiyr
UPDATE valid_item set tamt_ret_item = isnull(tamt_ret_item,0) + @var_amt_ret_result, tamt_paid_item = isnull(tamt_paid_item,0) + @var_amt_result, qtd = isnull(qtd,0) + @var_quant_result where db_contract = @var_db_contract and pc_code = @var_pc and item_no = @var_item
Ladys, Gentlement, I have table that grows anywhere from 200,000 to 1,000,000 records perday. Besides that I need to keep at least 6 months historical data from this same table. The transaction log was purged after each batch when testing data monthly. I'm looking for some way of deleting just one day's data if it meets a criteria. It must remain within the 6 months period of historical data. This is what I've come up with so far"
select * FROM dbo.Temp_table WHERE datediff(day, DATE_TIME, getdate()) >= 180
If it meets this criteria I can change the select to a delete? Please Let me know what you think
i am using this statement for deleting a single row in sql table. "DELETE FROM Random WHERE NewID= '" & strwinner & "'"
where "strwinner" is the variable which contains the row to be deleted. the problem is that when i check the table in sql the row which was supposed to be deleted is sitll there.it does not give me any error statement or something. iam executing this statement by using ExecuteNonQuery in my .aspx page. please help
We have an employee table that contains bank details and are experiencingproblems with account numbers being erased and lost. In order to track downwhy this is happening (either due to our application code or SQLreplication) we'd like to be able to prevent certain columns from beingdeleted if they already contain some data.Is it possible to setup a check constraint to prevent our ee_acct_no columnsfrom being set to NULL or blank strings if it contains an account number(i.e a 9 digit number)? We have setup the column to allow NULL's as we don'talways know employees bank details until later, so we do need to put them onour database without bank details initially.Also, if possible, can someone suggest a stored procedure or trigger i couldcreate that would fire a user-defined error message that would email anoperator if a bank account number changed?Many thanksDan Williams.
I was trying to relocate my transaction log to a bigger drive usingsp_movedevice but I made a mistake in the syntax of the second parameterand put only the path, not the path and the file name.Now my database is marked as "suspect" and I get an error message in my logupon database start up saying that the log file cannot be open.Is there a way to have MS SQL 6.5 "forget" all the logs of this database,create new ones and restart the database? The logs contained nothingimportant, I had truncated them an hour or so before I made my mistake. Ijust want to make sure the data are still usable.When I look at the devices with sp_helpdevice, I can see a log that existand is hopefully in pristine condition and the one that doesn't existanymore.I looked in the archives of various newsgroups but couldn't find somethingthat correspond closely to my situation. I saw something similar but withMS SQL 7.0(http://groups.google.com/groups?hl=...om %26rnum%3D4)using sp_attach_db/sp_detach_db. What would be the equivalent with version6.5?Thanks!Charles--Charles-E. Nadeau Ph.Dhttp://radio.weblogs.com/0111823/
One of my customer is asking to delete 95% of the rows in an table which has around 645708 rows in a table.Now my concerns are that , what criteria we need to follow while deleting huge records in table, what are the steps we need to be taken care of?And after deletion, what are the maintenance tasks we need to perform to be up to mark without any issues.Lastly will deletion of 95 % of rows improve performance of a table?
Hi I am not sure if I am at right place, anyhow I hope I am :) Now the question: I am using an ASP.net Application with SQL-Server. I want to make a page so that it set the expiration time (date) for certain record and once that time reaches, it deletes those records, or make any updates to the record (what ever applicable). I also want to control this auto deletion from my application, means that turn this On/Off whenever needed. I am not sure how to start this. I was told by a friend that I need to use triggers from SQL-server but I need some help. Can anyone help me out on this? RegardsMykhan
I have a table...say tb1 of 20 columns which has 2.7 million rows. There is no PK and the only way of identifying a unique row can be done with combination of column1+column2+column3. Can anyone help me how to idetify the duplicate rows and also delete the duplicate rows. And to commit after every 5000 rows. ITS VERY URGENT....Thanks in advance.
I am using a cursor to navigate on data...of a table.... inside the while @@fetch_status = 0 command I want to delete some rows from the table(temporary table) in order to not be processed... The problem is that I want this deletion to affect the rows the cursor has.
I have a table in sql server 2000 which has over 94000 records. I have to delete a record from table ,which record having a language other than english . I need to clean the table by removing all the data which are in other language . My main table has 12 fields .
I have been involved in a project wherein i have to delete old records from two table and the corresponding child records from the interlinked tables. I try to find a SQL Procedure that deletes the child records, but that procedure uses the sysforeignkeys table for recursively deleting the child records. When i looked at my sysoreignkeys table, it was empty(no rows). Request you all if you can provide me the SQL Procedure that can delete record in the table with the child records.
