How can I delete tables in a SQLServer Database automatically (at the moment I just do it using Micorosft SQl Management Studio Express manually)
But its important that I can do that automatically, the best thing would be if I can do that from a .NET programm.
thanks in advance,
mulata
I need to setup an automated process to delete all the files older than a month in a particular directory on a network drive, how is that possible? Any help is greatly appreciated.
thanks.
Sheila.
Hello Friends,
I am working on DTS package. I got a wonderful article of Davide Mauri.
http://www.sqlteam.com/article/how-to-asynchronously-execute-a-dts-package-from-asp-or-aspnet
I am using DTS package to update a table. I have done all the things that are stated in that article. Also there is a point
on how to delete the jobs automatically when the job execution is done so that we dont have to manually delete the jobs later
on. The @delete_level does the trick. I thought it would work but after executing the DTS for a variable number of times I
got this error...
The specified @name ('dts1') already exists. Supply either @job_id or @job_name to identify the job. Supply either @job_id or
@job_name to identify the job. Supply either @job_id or @job_name to identify the job.
Now i am not getting what went wrong? I have implemented all steps given in that article. Please see on how to delete the job
immediately upon successful execution. I dont want to create a 'n' number of job names every time.
Pl help on it.
Thank you,
Prashant...
I have one stored procedure for insert, update and delete operations, I need automatically logged the insert,update and delete operations.
How can I set auto logged mechanism and how can I access the logs in c#?
Thanks
I would like to perform autodeletion operation from my table using stored procedure....
Here is my table structure:
Emp.id | emp_name | dept | desig | time_out | date | emp_sign |
if the user inserts new employee record that record should delete automatically from original after
30 minutes..I don't want to keep that newly inserted record after
30 minutes...I don't know how to achieve this...
this is my Delete Query NO 1
alter table ZT_Master disable trigger All
Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
alter table ZT_Master enable trigger All
I have troble in Delete Query No 2
here is a select statemnt , I need to delete them
select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
I tried modified it as below
delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
but this doesn't works..
can you please help?
and can I combine these 2 SQL Query into one Sql Query? thank you
Is there a way to automatically delete tables in a database? I want to delete tables with a specific prefix in their names (that I assign to them) based on their age. Is there a way to do this automatically while keeping the tables that I don't want to touch?
joe
I was wondering if there is a way to schedule the deletion of temporary tables that are over a certain age. Right now, temporary tables are being created, but unless I delete them manually, they end up building up and just taking up a lot of space.
View 5 Replies View RelatedHi,
I need help in looking at sample script to perform automatic creation for weekly partitioned tables and also update the partitioned view accordingly.
Any helps are very much appreciated!
Thanks,
G
I have created a table with the following columns...Date(datetime),Actual (Int),Planned (Int)I need to insert weekending dates starting from 23/04/04 loopingthru'for the next 52weeks automatically into the date column.Then in the actual and planned colums, I need to insert a count ofsome records in the table.I will appreciate help on a SQL query to achieve this!
View 5 Replies View RelatedWhen creating a new row or record in the Master table (by an Adapter update), how do I then populate new records in 15 different Child tables?
These Child tables are attributes of the Master table. I am sure there is more than one way to do this...
Thanks
From Newbie to Newbie,
Add reference to:
'Microsoft ActiveX Data Objects 2.8 Library
'Microsoft ADO Ext.2.8 for DDL and Security
'Microsoft Jet and Replication Objects 2.6 Library
--------------------------------------------------------
Imports System.IO
Imports System.IO.File
Code Snippet
'BACKUP DATABASE
Public Shared Sub Restart()
End Sub
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click
Dim addtimestamp As String
Dim f As String
Dim z As String
Dim g As String
Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm")
z = "C:Program FilesVSoftAppMissNewAppDB.mdb"
g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB
f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.")
If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then
End If
End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click
Dim Filename As String
Dim Restart1 As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String
With OpenFileDialog1
.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
Filename = .FileName
'Strips restored database from the timestamp
xi = "C:Program FilesVSoftAppMissNewAppDB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With
'Notify user
MsgBox("Data restored successfully")
Restart()
If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim Restart2 As New NewDBRestart
If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then
If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table
'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column
Dim col7 As ADOX.Column = New ADOX.Column
Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table
'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog
Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "MA"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Gender"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Phone1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col7.Name = "Phone2"
col7.Type = ADOX.DataTypeEnum.adVarWChar
col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col8.Name = "Notes"
col8.Type = ADOX.DataTypeEnum.adVarWChar
col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")
'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)
Tablename.Columns.Append(col7)
Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)
'User notification )
MsgBox("A new empty database was created successfully")
'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing
'Restart application
If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name.
JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted)
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme.
Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification
MsgBox("The database was compacted successfully")
End Sub
End Class
I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?
View 5 Replies View RelatedI have an Integration Services package that loads new data into tables that are dimension tables wi my cube. The same situation exists for my fact table. If I perform an "Analysis Services Processing Task" for the dimensions ,cube and measures, will that move the new data into my cube or do I need to perform the "Dimension Processing Destination" data flow task prior to this? Is the initial processing task good enough?
thx,
-Marilyn
These are my selcet and delete commands, however I am trying to delete from two tables both Assets and AssetAttribute. The both have related colums AssetTypeId, AssetAttributeId. I need to slect them first then delete them. I need the one action delete to delete int eh same information from both tables. Help. I only have the one written for Asst table, I need to include the Assetattribute table. Please help me. SelectCommand="SELECT AssetId, AssetTypeId, Description, AssetAttributeId, SKU, Barcode, GovBarcode, WarehouseId, IsVehicle, DeploymentStatus FROM Asset, AssetAttribute WHERE AssetId = @AssetId" DeleteCommand="DELETE FROM Asset, AssetAttribute WHERE AssetId = @AssetId"
View 3 Replies View RelatedHi folk, whats the SQL syntax of deleting all user tables of a specific database on a Microsoft SQL server?thanks in advance,mulata
View 3 Replies View RelatedI have 2 tables "Orders" and "OrderProducts"
In my application, there are moments when I clean up these tables.
There is a query that looks for some flag in the "Orders" table, and deletes the records.
But there are related (PK-FK) records in the "OrderProducts" table.
How can I delete also these records in the same query?
I've set up a grid control on my other page and trying to delete items via a check box. I got it to work with deleting from one table but I need to delete from two tables at the same time.
How do I code using a stored procedure called Delete Resource instead of the "Delete from Titles where ...(code is below)
Now if I can add my other table called resources to the sql query that's fine as they both will use titleID to delete the info.Any help would be appreciated. Thanks!
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim gvIDs As String = ""
Dim chkBox As Boolean = False
'Navigate through each row in the GridView for checkbox itemsFor Each gv As GridViewRow In GridView1.Rows
Dim deleteChkBxItem As CheckBox = CType(gv.FindControl("deleteRec"), CheckBox)
If deleteChkBxItem.Checked Then
chkBox = True
gvIDs += CType(gv.FindControl("TitleID"), Label).Text.ToString + ","
End If
NextDim cn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(SqlDataSource1.ConnectionString)
If chkBox Then
TryDim deleteSQL As String = _
"DELETE from Titles WHERE TitleID IN (" + _
gvIDs.Substring(0, gvIDs.LastIndexOf(",")) + ")"Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(deleteSQL, cn)
cn.Open()
cmd.ExecuteNonQuery()
GridView1.DataBind()Catch Err As Data.SqlClient.SqlException
Response.Write(Err.Message.ToString)
Finally
cn.Close()
End Try
End IfProtected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim gvIDs As String = ""
Dim chkBox As Boolean = False
'Navigate through each row in the GridView for checkbox itemsFor Each gv As GridViewRow In GridView1.Rows
Dim deleteChkBxItem As CheckBox = CType(gv.FindControl("deleteRec"), CheckBox)
If deleteChkBxItem.Checked Then
chkBox = True
gvIDs += CType(gv.FindControl("TitleID"), Label).Text.ToString + ","
End If
NextDim cn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(SqlDataSource1.ConnectionString)
If chkBox Then
TryDim deleteSQL As String = _
"DELETE from Titles WHERE TitleID IN (" + _
gvIDs.Substring(0, gvIDs.LastIndexOf(",")) + ")"Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(deleteSQL, cn)
cn.Open()
cmd.ExecuteNonQuery()
GridView1.DataBind()Catch Err As Data.SqlClient.SqlException
Response.Write(Err.Message.ToString)
Finally
cn.Close()
End Try
End IfEnd Sub
End Class
I have 3 tables have the same column ID.I want to delete records in one query like "delete from orders as o,ordersdetail as od,membersdns as m where o.ID = 821" but I get the error "incorrect syntax near as" .is this possible to delete records like this?
View 6 Replies View RelatedThese are my selcet and delete commands, however I am trying to delete from two tables both Assets and AssetAttribute. The both have related colums AssetTypeId, AssetAttributeId. I need to slect them first then delete them. I need the one action delete to delete int eh same information from both tables. Help. I only have the one written for Asst table, I need to include the Assetattribute table. Please help me.
SelectCommand="SELECT AssetId, AssetTypeId, Description, AssetAttributeId, SKU, Barcode, GovBarcode, WarehouseId, IsVehicle, DeploymentStatus FROM Asset, AssetAttribute WHERE AssetId = @AssetId"
DeleteCommand="DELETE FROM Asset, AssetAttribute WHERE AssetId = @AssetId"
Hi All,
How can i delete all the tables in a DATABASE with a single shot!!
Thanx in advance
I have 5 tables in the same database named the following: do, name, olm, image, person, scr
They are all link together by and have the following field in each: FCN
I would like to delete the corresponding record in each table where the value of the field 'image_path' in the 'image' table is '999999.jpg'
What would the syntax be?
delete from do, name, olm, image, person, scr
where image.image_path = '999999.jpg'
innerjoin something????
Thanks
Hello,
To delete all tables from my database I use:
SELECT 'DROP table ' + table_NAME
FROM INFORMATION_SCHEMA.tables
To get the list of the tables. Then I run that list:
DROP table Folders
DROP table Files
...
Because of relationships I need to run it various times until all the tables disappear. Is there a way to everything in one command?
Thank You,
Miguel
Hi,
How can i delete/drop all tables in a database using a script.
Thanks.
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.
View 1 Replies View RelatedHi,
I am working on IBUYSTORE's db. I have added a backoffice/admin to the site.
I have a page where I retrieve a customer's details based on the customerid such as the following:
Dim Ssql As String = "SELECT O.CUSTOMERID, C.CUSTOMERID, C.PASSWORD, C.FULLNAME, C.EMAILADDRESS, O.ORDERID, OD.ORDERID , OD.PRODUCTID FROM customers AS C , ORDERS AS O , ORDERDETAILS AS OD WHERE C.customerID=O.CUSTOMERID AND OD.ORDERID=OD.ORDERID AND C.customerID = @cust_ID"
CMD As New SqlCommand(Ssql, oConn)
CMD.Parameters.Add("@cust_ID", id)
I am also using a sqldatareader.
I have a delete button. If the site manager clicks on that button i want to delete all that customer's details including all his orders from both orders and orderdetails tables.
I am not sure how to accomplish it.
The tables are as follows:
Customers: Orders: OrderDetails
customerid customerid orderid
orderid
I tried the following delete:
strDelete = "DELETE FROM CUSTOMERS where CustomerID=@CUST_id; DELETE FROM ORDERS WHERE CUSTOMERID=@CUST_id; DELETE FROM ORDERDETAILS WHERE ORDERID=@orderID "
Dim objCMD As New SqlCommand(strDelete, oConn)
objCMD.Parameters.Add("@CUST_id", id)
objCMD.Parameters.Add("@orderID", LBLORDER.Text)
oConn.Open()
objCMD.ExecuteNonQuery()
oConn.Close()
I get the following error:
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Orders_Customers'. The conflict occurred in database 'Store', table 'Orders', column 'CustomerID'. DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_OrderDetails_Orders'. The conflict occurred in database 'Store', table 'OrderDetails', column 'OrderID'. The statement has been terminated. The statement has been terminated
I have 2 tables that are joined together by a primary key (Order Number). Can I use one SQL query to delete from both of the tables. One table contains the order information from a client (Order Number, Customer Name etc). The other table has order information (Order Number, Item Number, Quantity Ordered etc.)
I need one statement that will allow me to remove the items from both tables. Can this be done.
Thanks in Advance
Wes
I want to reset my application and delete all the data in all tables.
But I have question for this.
1. I do not know how to loop over all the tables and delete data.
2. the database have database diagram so threre are dependency with tables so the delete order is hard to decide.
Please give me a idea.
Thanks
Mark
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.
In my DB there some tables that I would like to delete. Is there a way to do it NOT one by one?
View 4 Replies View RelatedI have two tables PROFILES & ROLE
CREATE TABLE PROFILES(
ID varchar(20) UNIQUE NOT NULL,
Name varchar(40) NULL,
Address varchar(25) NULL
)
[Code] ...
This query joins both the tables and gets displayed in a grid.
select P.ID, Name, Address, Role, Applications
from PROFILES P
Inner Join ROLE R
ON P.ID= R.ID
I need to write a delete query which deletes data from both the table in a single query.
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?
View 4 Replies View Related
Hello,
I have the following problem:
2 tables: both have the same pk values.
one table must be deleted based on a filter (I mean the table is not delete completely but only some records), I would like to delete same records in the second table.
for ex:
table 1: pk: 1,2,3,4,5
table 2: pk: 1,2,3,4,5
table 1: deleting 1,2, 3 thus also in table 2 pk: 1,2,3 must be deleted.
At the and of process Table1 and Table2 must have the same records (always also in the case of failure, errors and so on ).
The target is avoid using triggers.
OUTPUT is not useful because it writes what is deleted (or may be useful but how to use it?).
How can I do?
Thank