What's the proper way to delete an existing database through native c++ code?
When I try DeleteFile(filename), I get an error about another process using that file. I hadn't created a connection to that file in my own program, so I assume that lock is from the sql engine.
Thanks folks!
hi guys,
just a question regarding database design
i have a table with an auto-generated primary key but the problem is this:
say i have 4 records,so logically they'll be numbered 1 to 4.so the problem is whenever i delete all records and add new ones,the numbering will start from 5 and not 1 again.
how do i remedy this?
thanx
I have 50 MSDE SQL2k servers, each server has around 10 customer databases.
There are 5 stored procecures need to update to 50*10 = 500 databases.
These 5 stored procedures each has many 'Go' keywords and 4 of 5 with more than 8000 characters.
What might be the best way to loop execute them automatically, instead of
isql/w to each database connection to run the script?
I had bumpped by 'Go' keywords error and limitation of max varchar of 8000 error.
thanks for the help
David
Hi gang,
We have a couple of databases on separate servers that have exactly the same schema, and we would like to set up merge replication between them.
Is that possible? The few times we've experimented on test databases, the subscriber database has been trashed and rebuilt using data from the publisher, rather than preserving data from both databases.
I'm really new to replication in general, so any kind of advice would be helpful.
Cheers,
Matt
I have a package which loads data from a flat file (csv) to 4 tables in a database.
Now, the load is incremental.
I want to clear the data of all 4 tables(in the database) before loading the data from flat file everytime.How can i do this?
Iam using 4 Oledb Destinations, 1 multicast, 1 source component to do this.
Also can it happen like a transaction? because if it deletes the existing data and couldnt load new data there will be a problem!.how to avoid this?
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 went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
View 1 Replies View RelatedHi All,
I would like to restore database using RESTORE DATABASE ... REPLACE command.
If database exists already and has any open connections this command will fail.
I would like to close all existing connections to specific database before running RESTORE DATABASE ... REPLACE command.
I can do closing from Management Studio using checkbox "Close Existing Connection" when deleting database. Actually I need to do the same but from script.
Please advice me how to do it.
Thanks in advance,
Roman
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
Thanks
Prashanth
Please 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.
hi from France !!!
i would like how to duplicate a database to another server with all datas, constraints, keys, indexes...
should i use sp_attach_db, dts, backup/restore, sql scripts... ???
thanks to all, nico
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.
here 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!!!
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?
View 3 Replies View RelatedHi, 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
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.
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
Hi,
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
how do i delete an entire database when it keeps saying that the database is currently in use?
View 3 Replies View Relatedhi,
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
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.
Thanks,
Ken
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
View 2 Replies View RelatedI 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
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 would appreciate your help.
As always you guys are the greatest.
Dianne
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?
Hey 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
I am joining two databases and the result has many duplications in it. Using SQL, how do I delete the duplications.
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?
View 10 Replies View RelatedI 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