Deleting Datbase
Apr 23, 2001how do i delete an entire database when it keeps saying that the database is currently in use?
View 3 Replies
ADVERTISEMENT
Datbase Migration
Jan 21, 2008HI Guys
I have a vary large NS-Access database that needs to be migrated with its data to SQL Server, are there any good tools available to do this ?
How would one proceed doing this ?
Datbase Design Help
Jan 25, 2008Four Entities-
Consultant
Client
Project
Time Sheet
Relationships:
A consultant can work for many Clients at a time
A client can have many projects at a time
A Consultant may wok on many projects for many Clients
Time sheet is the time a consultant worked for a particular client on a particular project for a week.
I would appreciate if someone can help me design this database
Recover Datbase
Mar 28, 2007how can i recover sqlserver 6.5 datbase from tape.
pls help me?
Build Datbase From Xsd/xml ??
Sep 13, 2006Hello all. I have an .xsd file and a .xml file that goes with it. Are there any tools available that will build a database structure in SQL Server from either of these two files? I can't see the .xml file being of much use for building the structure, but how about the .xsd file?
View 5 Replies View RelatedDatbase Model Issue
Sep 28, 2004I need input in how to define a proper model for a certain issue.
I've got a table, testcase. Each row in table testcase relates to 1 or more statistics tables. (the statistics differ a lot , thats way I can't place them in the very samt table) Now, there are like 50 different statistics tables. And each row may only store statistics in some of them. Whats a proper model for this?
Ex: create foregin keys in testcase table (for all statistics thats like 50 foreign keys) that has a primary key in each statistics table. I then must have a dummy value in each statistics table to ensure that we do not violate the referencial integrety.
Ex: create a relation between prim.key of table testcase that has a foreign key in each statistics table.
ex: many-to-many, but this violates the referential integrety...since we relates to multiple tables on one side.
suggestions?
/Martin
Datbase Mirroring Issues
Jan 12, 2008I am going to do database mirroring for. I need to configure what needs to be installed for mirror server and witness server with OS perspective. Does Mirror server have to have same hardware and software config as Principle server?
Thanks
How To: Move A Datbase From Express To Server?
Mar 10, 2006Hello,
Thanks for reviewing my question. I would like to know how do you bring a database from SQL Express into a SQL 2005 server? I didn't find a way to import the SQLExpress (.mdf) file.
Many Thanks
Peter
Question On Automatic Copying Table From One Datbase To Another On Same SQL Server
Apr 6, 2004Currently have two databases. A live database and a history database. Also have the ability to purge data from the live to the history database. What I'm looking for is the ability, if the table doesn't exist in history, to automatically create the table in the history database using the format of the table within the live database. Any help would be greatly appreciated. Thanks
View 9 Replies View RelatedDeleting The Master Table Withour Deleting The Child Tables
Aug 9, 2007Hi
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
T-SQL (SS2K8) :: Deleting Only 1 Row At A Time Instead Of Using Condition And Deleting Many Rows?
Jul 18, 2014/****** 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:'.
Need Help On Deleting
Dec 12, 2001Please HELP!!
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.
KINDLY HELP, THIS IS URGENT.
Deleting Data From DB
Nov 23, 2006I 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?
Deleting From Sql Question.
Jan 2, 2007I 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?
Deleting A Record
Oct 9, 2007I 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.
Deleting Problems..
Apr 2, 2008here i a again.. because im really having problems in deleting rows from a gridview... here is my code
SelectCommand="SELECT UserId, First, Last, Email, CreateDate, LastLoginDate, IsApproved, IsLockedOut FROM aspnet_Membership"
DeleteCommand="DELETE FROM aspnet_Membership WHERE [UserId] = @UserId"
ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" >
<DeleteParameters>
<asp:Parameter Name="UserId" Type="Int32"/>
</DeleteParameters>
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:
[SqlException (0x80131904): Must declare the scalar variable "@UserId".]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3430
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +271
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +367
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +492
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +919
System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +176
System.Web.UI.WebControls.GridView.HandleDelete(GridViewRow row, Int32 rowIndex) +911
System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +1067
System.Web.UI.WebControls.GridView.RaisePostBackEvent(String eventArgument) +214
System.Web.UI.WebControls.GridView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +242
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3839
need help asap.. pls THANK YOU VERY MUCH!!!
Deleting All But Top Record.
Jun 23, 2005Hey 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?
View 3 Replies View RelatedDeleting From 2 Tables?
Jul 21, 2005Hi, I am pretty new to SQL and know that I am probably going around this the wrong way.
I want to make a stored proc that deletes rows from table 1 and delete rows from table 2 where the common link is the id.
Any help would be greatly appreciated!
Many thanks
moop
Deleting Old Backups
Apr 17, 2001I 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.
Thanks,
Tim
CREATE PROCEDURE sp_BackupProcessor
@Help BIT = 0,
@Database VARCHAR(30) = NULL,
@BackupName VARCHAR(50) = NULL,
@BackupType VARCHAR(4) = NULL,
@Filename VARCHAR(255) = NULL,
@Description VARCHAR(255) = NULL
AS
Parameters:
@Database = ''master''
-- Default = ''master''
-- Specifies the database to backup.
@BackupName = ''masterLOG''
-- Default = ''masterLOG''
-- Specifies a name for the backup.
@BackupType = [''FULL'' | ''DIFF'' | ''LOG''],
-- Default = ''LOG''
-- ''FULL'' = Full database backup
-- ''DIFF'' = Differential database backup
-- ''LOG'' = Transaction Log Backup
@Filename = ''C:empmasterLOG.TRN''
-- Defualt = ''C:empmasterLOG.TRN''
-- Specifies the file to use for the backup.
-- Provide full path and filename.
@Description = ''255 cahr max''
-- Default = ''
-- Specifies a description to attach to the bakup.
Usage:
sp_BackupProcessor
@Database = ''Database'',
@BackupName = ''BackupName'',
@BackupType = [''FULL'' | ''DIFF'' | ''LOG''],
@Filename = ''FullPathLocalFile'',
@Description = ''Backup Description (255 max)''
'
RETURN 1
END
DECLARE @BackupStmt VARCHAR(500)
SELECT @BackupStmt =
CASE @BackupType
WHEN 'FULL' THEN
'BACKUP DATABASE ' + @Database + ' ' +
'TO DISK = ''' + @Filename + ''' ' +
'WITH RETAINDAYS = 2,' +
'INIT' + ', ' +
'NAME = ''' + @BackupName + ''', ' +
'NOSKIP' + ', ' +
'DESCRIPTION = ''' + @Description + ''', ' +
'NOFORMAT'
WHEN 'DIFF' THEN
'BACKUP DATABASE ' + @Database + ' ' +
'TO DISK = ''' + @Filename + ''' ' +
'WITH DIFFERENTIAL' + ', ' +
'NAME = ''' + @BackupName + ''', ' +
'NOSKIP' + ', ' +
'DESCRIPTION = ''' + @Description + ''', ' +
'NOFORMAT'
WHEN 'LOG' THEN
'BACKUP LOG ' + @Database + ' ' +
'TO DISK = ''' + @Filename + ''' ' +
'WITH NOINIT' + ', ' +
'NAME = ''' + @BackupName + ''', ' +
'NOSKIP' + ', ' +
'DESCRIPTION = ''' + @Description + ''', ' +
'NOFORMAT'
END
EXECUTE(@BackupStmt)
GO
Deleting Duplicates
Apr 17, 2001Hi,
does anyone out there know how to delete dulicate records in a table? I've looked in BOL and I can't seem to find it. Please advise.
thanks, mark
Deleting Tables
Feb 21, 2001hi,
How can I delete two tables with a WHERE condition.
That is I can't do this -What can be an alternate of this statement -
Delete from Tab1,Tab2,Tab3
where
Tab1.name = Tab2.Name and
Tab2.name = Tab3.name
TIA
Deleting A Log File
Mar 5, 2001How 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!
Deleting Tables
Aug 24, 2005Does 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.
Thanks,
Ken
Deleting Dups
Jul 3, 2002I 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
View 2 Replies View RelatedDeleting A Device
Dec 1, 1998I am trying to drop a device that as no associated file in Windows NT Explorer(.dat) that shows up in my Server Manager Windows
How can I delete them?
Thanks
Deleting Odd And Even Numbers
Aug 8, 2002I 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 would appreciate your help.
As always you guys are the greatest.
Dianne
Deleting A Table
Nov 9, 1998i 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?
Deleting Duplicates
Sep 21, 2004Hey all.
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
Deleting Duplications Of A Db
Aug 13, 2004I am joining two databases and the result has many duplications in it. Using SQL, how do I delete the duplications.
Thanks
Deleting Records That Get Too Old
Jul 12, 2004I 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?
View 10 Replies View RelatedDeleting Rows
Nov 26, 2004I 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?
Thanks
Deleting Records
Feb 25, 2005I 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"
Deleting TRN Files
May 26, 2006I 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?
View 4 Replies View Related