Archive Data Instead Of Deleting It To Prevent 4GB Data Limit
We are running SQL Server 2005 express on Windows 2003. The database server gets significant amounts of data.
Because of the 4GB data limit we have a daily cron task which goes through and deletes data older then 90 days.
We would like a way to archive this data instead of deleting it. Is there any way to take data and compress it and store it in a different way, so that if needed, customers can query directly out from the compressed data? Cleary querying from compressed would be slower but that is ok.
Any other solutions that would allow us to archive data instead of deleting it? Thanks.
View Complete Forum Thread with Replies
Related Forum Messages:
Data Archive
Hello, everyone: What does "data archive" mean in SQL Server? Is it same thing as archiving in Oracle? Thanks. ZYT
View Replies !
Archive Data Suggestion
I have a table contains huge rows of data. Performance issue raised. I amthinking archive some data so that the table will not be that big. The mostconvience way is move it to another table. The problem is: will this solvemy performance problem? or I need to move it to another database to reducethe database size?Regards,TrueNo
View Replies !
NOT Replicate Data Archive
Hi Folks, We have transactional replication setup to replicate data from production across to a reporting server. We want to ARCHIVE production, but don't want the ARCHIVE duplicated on the reporting server. Does anyone know of a way that the reporting server can be stopped from replicating these changes, and continue to hold the FULL history of the database? Cheers, David
View Replies !
NOT Replicate Data Archive
Hi Folks, We have transactional replication setup to replicate data from production across to a reporting server. We want to ARCHIVE production, but don't want the ARCHIVE duplicated on the reporting server. Does anyone know of a way that the reporting server can be stopped from replicating these changes, and continue to hold the FULL history of the database? Cheers, David
View Replies !
Under 6.5, Best Way To Archive Data Out Of Large Table?
Hello: The purchased-application mssql 6.5, sp 4 that I am working on has one large table has 13m illion. It the largest table considering thenextlatgest table is only1.75 million rows. Thew vnedor has made a change to this largest table in recommending changing a data type -- char to varchar. To make this change easier to do, I want to "archive" older data not necessary for the current year or current processing to another table. What is the best way to do this archiving? Any information you can provide will be greatly appreciated. Thanks. David Spaisman
View Replies !
Prevent User From Deleting Tables
Hi, How can I prevent a colleage to delete tables in a specific database. Yes he has access to Enterprise Manager. We would like to allow him read only to the live databases. Is this possible?
View Replies !
Move Data To Archive Table. Need Suggestions.
I have two tables say A and Archive. After a certain period of time some records are to be sent to archive table.To copy records to archive table I am using SqlBulkCopy operations.Now I have to delete the records from A Table. I was thinking of sending a Comma seperated id's of rows that are to be deleted to a stored procedure.Are there any better techniques to move data to archive table and to remove data from main table.?Thanks.
View Replies !
Deleting Existing Data Before Loading New Data
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?
View Replies !
Update Check For Actual Data Change And Archive
I'm working on an update trigger for a table that I want to archive the changes for. I'm importing a database to this database. It will add new records and recopy prior imported records the same way. So basically every field/column is updated even if the actual data doesn't change. I need to be able to figure out which fields had data which actually did change, and there are a lot of fields (like 100). I got it to work on a small scale, but I didn't take into account the fact I need to check all (100 or so) fields and I need to be able to check for multiple records in the inserted/deleted tables. I was advised to use a cursor to compare the inserted/deleted tables with a cursor and dump into a temp table. Then check for the dirty field (no idea what that is) in the temp table to copy the actual updated records into the archive table. I'm a novice (one database class) and this is my first SQL related issue on my new job. Any help you could give to me would be greatly appreciated.
View Replies !
Archive Data By Data
I need a script that can be schedule for a SQL7 server to archive a table-space's data for exery day except current, then pull the last 7 days worth of data back into the table. Any one have anything like this? My dbase is a load totaling dbase and the size is getting out of control.
View Replies !
Prevent Data Being Inserted Twice
I have a table with 3 columns: ID, Status, DateTime. I created a stored procedure to insert a staus value for each ID. This will run every hour. The DateTime stores the time, date when the Status was inserted. If the procedure was to be run a second time in hour window I do not want any Status to be inserted. Note: that I cannot rely on the procedure being run at exactly the right time - if it was scheduled to run on the hour (i.e at 1:00, 2:00, 3 :00 etc) but didn't run until 1:20 it sould still be able to run at 2:00. Does anyone know if there is anyway I can gaurd against this?
View Replies !
How Can I Prevent From Inserting Duplicate Data?
I have a table storing only 2 FKs, let's say PID, MID Is there any way that I can check distinct data before row is added to this table? For example, current data is PID MID------------100 2001100 2005101 3002102 1009102 7523102 2449 If my query is about to insert PID 100, MID 2001, since it's existing data, i don't want to add it. Can I use trigger to solve this issue? Thanks.
View Replies !
How To Prevent DBA From Getting Confidential Data Stored In SQL Server?
Suppose I have a database storing some confidential information, such as legal information, medical or financial records, etc., and a Web site with a membership system so that only authorized users can view the information. I understand I can encrypt the information, and the user's passwords, so that if the database is compromised it still shouldn't be possible for an outsider to view the confidential information. However, what about people who have legitimate access to the database, such as the DBA, Web developer, etc., but who should not be able to view the confidential information? For example, even though the user's password was encrypted, what would stop the DBA from replacing the user's password with his own (encrypted) password, then logging in and viewing the user's info, then copying back the original encrypted password? Or, adding a new user for himself with whatever permissions he chooses?
View Replies !
Does Db_denydatawriter Prevent Procs From Updating Data?
If a user is a member of a role which would allow him to execute a proc which updates a table, and he is then granted db_denydatawriter , can he still update the table through the proc? SS2000 SP4. Thanks, michael Since posting this I tested the scenario and YES! The user was still able to execute an update proc even though the user excuting the proc was granted db_denydatawriter.
View Replies !
Stored Procs: How To Prevent Return Of Uneccesary Data?
Hi, I have a stored procedure that looks like this:...WHILE @@FETCH_STATUS = 0BEGINDECLARE @MyCount int ; EXEC spLoanQuestionnaireCriteria @AuditSelectedLoanID, @Criteria, @MyCount OUTPUTEND ...SELECT * FROM #Categories... Executing this stored procedure will return me 1 table for each time the EXEC statement is called that only has on column (MyCount)I really don't need this data to be returned, it is only used for some internal calculations in the stored procedureThe stored procedure should only return the results from SELECT * FROM #Categories in 1 table.Is there a Keyword I can use to exclude the EXEC results being returned to the dataset? Thanks in advance,Andre
View Replies !
How To Prevent System Administrator To View And Edit A Database Structure And Data
I represent a software development house and we have developed a client server system based on SQL Server. Most of our customers have already purchased Enterprise License of SQL Server, therefore they own the SA Login and Password. We are bound to attach our Database with their Server on their machine. My question is how can we stop a System Administrator of SQL Server to view our Database Structure, Queries, Data installed on their SQL Server on their machine. Our database structure is a trade secret and we cant reveal the structure to the client. please answer this question by email to me at farhandotcom@gmail.com Thanks & Regards Farhan
View Replies !
Limit Data
I have the fields in my table: ID, Title, Description, Price, ImageData, Active I only want it to be possible to have two records at any time with Active=Yes. Active is a Bit, Yes/No, field. Any help is appreciated. Chuck
View Replies !
Deleting Data From DB
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?
View Replies !
Deleting All Data
Is there a way to delete all data from a database - all tables and alltables excluding system tables?sqlserver 2000?Thanks,Tmuld
View Replies !
How To Limit Size Of A Data Regions
I am working on a report is divided into 4 data regions. The layout wll look something like this (r1, r2, r3, r4 are table data regions) - <Header region> r1 r2 r3 r4 <Footer region> I want the height of each region to remain static irrespective of the number of rows returned by the data set. I can limit the maximum nbr of records to show on each table to 4, but if the dataset returns only 1 record the height of the report changes. How do I go about implementing this, please advice. Thanks
View Replies !
Deleting Data From Database
Edited by SomeNewKid. Please post code between <code> and </code> tags. I have added a delete button to my datagrid, and put in what I think is the code to delete a member from the database at their Member ID, however when I have called a members details and the delete button is pressed, nothing happens!!?? Any ideas? Here is the code: <%@ Page Language="VB" %> <script runat="server"> ' Insert page code here ' Function GetMember(ByVal iD As Integer) As System.Data.SqlClient.SqlDataReader Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='adp1SQL'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "SELECT [oga].* FROM [oga] WHERE ([oga].[ID] = @ID)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection) sqlCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = iD sqlConnection.Open Dim dataReader As System.Data.SqlClient.SqlDataReader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection) Return dataReader End Function Sub dgMember_Delete(sender as Object, e as DataGridCommandEventArgs) dgMember.EditItemIndex = -1 dgMember.DataSource = GetMember(memberID.Text) dgMember.DataBind() End Sub Sub btnView_Click(sender As Object, e As EventArgs) dgMember.DataSource = GetMember(memberID.Text) dgMember.DataBind() End Sub Sub dgMember_SelectedIndexChanged(sender As Object, e As EventArgs) End Sub Function DeleteMember(ByVal iD As Integer) As Integer Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='adp1SQL'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "DELETE FROM [oga] WHERE ([oga].[ID] = @ID)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection) sqlCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = iD Dim rowsAffected As Integer = 0 sqlConnection.Open Try rowsAffected = sqlCommand.ExecuteNonQuery Finally sqlConnection.Close End Try Return rowsAffected End Function </script> <html> <head> </head> <body id="dgMemberInfo"> <font face="arial"> <h1><img src="thlogotop.gif" align="left" /> <img src="thlogotop.gif" align="right" /> <br /> <center><font color="red">T</font>albot <font color="red">H</font>eath <font color="red">O</font>ld <font color="red">G</font>irls <font color="red">A</font>ssociation </center> </h1> <h2 align="center">Delete a Member </h2> <asp:HyperLink id="HyperLink1" runat="server" Width="94px" NavigateUrl="Default.aspx">Main Menu</asp:HyperLink> <hr /> <br /> <br /> <br /> <p align="center"> </p> <form runat="server"> <br /> <div align="center">Please Enter Member ID : <asp:TextBox id="memberID" runat="server"></asp:TextBox> </div> <br /> <br /> <br /> <br /> <div align="center"> <asp:Button id="btnView" onclick="btnView_Click" runat="server" Width="204px" Text="View Member Details"></asp:Button> </div> <br /> <br /> <p align="center"> <asp:DataGrid id="dgMember" runat="server" BorderColor="Black" OnSelectedIndexChanged="dgMember_SelectedIndexChanged" AutoGenerateColumns="False" OnDeleteCommand="dgMember_Delete"> <Columns> <asp:ButtonColumn Text="Delete" ButtonType="PushButton" CommandName="Delete"></asp:ButtonColumn> <asp:BoundColumn DataField="ID" HeaderText="ID"> <HeaderStyle font-bold="True" horizontalalign="Center" verticalalign="Middle"></HeaderStyle> </asp:BoundColumn> <asp:BoundColumn DataField="Member No" HeaderText="Member No"> <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle> </asp:BoundColumn> <asp:BoundColumn DataField="Surname" HeaderText="Surname"> <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle> </asp:BoundColumn> <asp:BoundColumn DataField="Forenames" HeaderText="Forenames"> <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle> </asp:BoundColumn> <asp:BoundColumn DataField="Known as" HeaderText="Known As"> <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle> </asp:BoundColumn> <asp:BoundColumn DataField="Title" HeaderText="Title"> <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle> </asp:BoundColumn> <asp:BoundColumn DataField="Address" HeaderText="Address"> <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle> </asp:BoundColumn> <asp:BoundColumn DataField="Home Tel" HeaderText="Home Tel"> <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle> </asp:BoundColumn> <asp:BoundColumn DataField="Email" HeaderText="Email"> <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle> </asp:BoundColumn> <asp:BoundColumn DataField="DOB" HeaderText="DOB"> <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle> </asp:BoundColumn> <asp:BoundColumn DataField="StartDate" HeaderText="StartDate"> <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle> </asp:BoundColumn> <asp:BoundColumn DataField="LeaveDate" HeaderText="LeaveDate"> <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle> </asp:BoundColumn> <asp:BoundColumn DataField="clubsoc" HeaderText="Club/Society"> <HeaderStyle font-bold="True" horizontalalign="Center"></HeaderStyle> </asp:BoundColumn> </Columns> </asp:DataGrid> </p> <p align="center"> </p> <p align="left"> <br /> <br /> </p> <!-- Insert content here --> </form> </font> </body> </html>
View Replies !
Deleting Data After Replication.
I am looking for an opinion on the best way to delete all data from a table after it has been replicated. The simple use case would be: Data is replicated from production server to archive server. Data is deleted from production serverThanks in advance...
View Replies !
I Have A Problem For Deleting Data
my row in my data base consists of 12 coulmns, i have a duplicate values in 6 coulmns of it , i use select distinct for all it doesnt work for it it doesnot remove dupliacte , i cant make primary key or any constraint on this 6 coulmns together cause it contains duplicate values even if i choose ignore duplicate it doesnot work example id name adress telephone job gender deprtment 1 john dd 123 doctor m 1st deprtment 1 john dd 123 doctor m 2nd deprtment so there is duplicate on id, name,telephone, job , gender and different in department so how i make a primary key on id , name , adress , telephone , job , gender together also how to delete duplicate from it although i used distinct function and it doesnit remove duplicate thanks in advance
View Replies !
How Do You Limit The Data That A Certain User Can See When Viewing Reports?
Hi I'm new to SQL Reporting Services and I've been asked to create an online environment for a client where their customers can log on and view a report. The client wants to make sure that each customer can only see data that is relevant to them, and that other customer's information is hidden from whoever is logged on. Can this be done through a username/password setup or is the solution more complex? Thanks very much!
View Replies !
Deleting Data By Comparing To Another Table
I have an entry form allowing customers to enter up to 15 skus (productid) at a time, so they can make a multiple order, instead of enteringone sku, then submitting it, then returing to the form to submit thesecond one, and so forth.From time to time, the sku they enter will be wrong, or discontiued, soit will not submit an order.Therefore, when they are done submitting their 15 skus through the orderform, I want a list showing them all of those skus that came back blank,or were not found in the database.I'm doing this by creating two tables. A shopping cart, which holds allthe skus that were returned, and a holding table, that holds all theskus that were submitted. I want to then delete all the skus in theholding page that match the skus in teh cart (because they are goodskus) which will then leave the unmatched skus in the holding table.I'll then scroll out the contents of the holding table, to show them theskus that were not found in the database.(confused yet?)So what I want to do is have some sql that will delete from the holdingtable where the sku = the sku in the cart. I've tried writing this, butit dosn't work.I tiried this delete from holding_table where sku = cart.skuI was hoping this would work, but it dosn't. Is there a way for me to dothis?Thanks!Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View Replies !
SQL Db Size Will Not Decrease After Deleting Data
Hi I installed " Web Wiz Forum ASP SQL 2000 DB " it work fine but when i added some data in the forum for example my db size is 1.45 MB after i delete those data the db size will not decrease is there any code that i must enter on the sql server setup file Excuse me i asked this question in the web wix forum site but they don`t answer me if know what i must to do plz tell me Thanks
View Replies !
Trouble Deleting Data In Table
I have a table where I want to delete some data from but I get this error. You might have a record that has a foreign key value related to it, or you might have violated a check constraint. What to do????
View Replies !
Problem Deleting Duplicate Data
I have a table that contains more than 10,000 rows of duplicate data. The script below copies the data to a temp table then deletes from the original table. My problem is that after it runs, I now have 122 rows of triplicate data (but dups are gone). If I rerun the script, it doesn't see the triplicate data and returns 0 rows. I've use three different versions of delete dup row scripts with the same result. There are no triggers or constraints on the table, not even a primary key. What am I missing?------------------------------------------------------------------- /********************************************** Delete Duplicate Data **********************************************/ --Create temp table to hold duplicate data CREATE TABLE #tempduplicatedata ( [student_test_uniq] [bigint] NULL, [test_uniq] [int] NULL, [concept_id] [smallint] NULL, [test_id] [varchar](12) NULL, [questions_correct] [smallint] NULL, [questions_count] [smallint] NULL, [percentage_correct] [decimal](6, 3) NULL, [concept_response_count] [smallint] NULL ) --Identify and save dup data into temp table INSERT INTO #tempduplicatedata SELECT * FROM crt_concept_score GROUP BY student_test_uniq, test_uniq, concept_id, test_id, questions_correct, questions_count, percentage_correct, concept_response_count HAVING COUNT(*) > 1 --Confirm number of dup rows SELECT @@ROWCOUNT AS 'Number of Duplicate Rows' --Delete dup from original table DELETE FROM crt_concept_score FROM crt_concept_score INNER JOIN #tempduplicatedata ON crt_concept_score.student_test_uniq = #tempduplicatedata.student_test_uniq AND crt_concept_score.test_uniq = #tempduplicatedata.test_uniq AND crt_concept_score.concept_id = #tempduplicatedata.concept_id AND crt_concept_score.test_id = #tempduplicatedata.test_id AND crt_concept_score.questions_correct = #tempduplicatedata.questions_correct AND crt_concept_score.questions_count = #tempduplicatedata.questions_count AND crt_concept_score.percentage_correct = #tempduplicatedata.percentage_correct AND crt_concept_score.concept_response_count = #tempduplicatedata.concept_response_count --Insert the delete data back INSERT INTO crt_concept_score SELECT * FROM #tempduplicatedata --Check for dup data. SELECT * FROM crt_concept_score GROUP BY student_test_uniq, test_uniq, concept_id, test_id, questions_correct, questions_count, percentage_correct, concept_response_count HAVING COUNT(*) > 1 --Check table -- SELECT * FROM crt_concept_score --Drop temp table DROP TABLE #tempduplicatedata GO
View Replies !
Deleting Data Using Table Prefix
I can run a select to retrieve data using a prefix 'a' for the specific table involved. However when I try to run a delete using the same criteria it fails telling me Msg 102, Level 15, State 1,.......Line 1 Incorrect syntax near 'a' The Select statement looks like: select count(*) from schema.table a where a.customer_id=1234 The Delete looks like: delete from schema.table a where a.customer_id=1234 What am I doing wrong here? and how can I prefix the table, because the command I want to run is much more complicated than the example above and it needs the prefix
View Replies !
Deleting Data From Production Sever
Hi All, I have to delete 135 Million records from our production server, keeping only last three months data & the table doesnot have any index. Can you please suggest the best possible approach to perform this activity. Approach i am thinking :- 1)I will rename the existing table & create a new table with the same name (By this my application wont be interrupted) 2)then i am planning to create a nonclustered index on the date column sort order desc so that i can get the last three months data. 3)Once the index is created i can transfer the required records to the new table created in step 1. Please sugeest your valuable feedback or suggestions to perform the task ASAP. Thanks
View Replies !
Deleting Data Bloats Log File
When I delete substantial amounts of data using the SQL DELETE command, the database size apparently remains the same (702 mb) and the log file goes from about 17 mb to over 1 gig. I was expecting for the overall size to decrease drastically, but got just the opposite. Is this typical? Can I do something to slim it down? As I am just trying to decrease the overall size to make it easier to work with when creating my application in VB, I am not worried about restoring the db (I have secure copies).
View Replies !
Data From The Table Deleting Automaticaly.
Hi gurus, The data is automaticaly deleting from one perticular table at every night from last week onwords. I have created a delete trigger to find it out. But Nothing was recorded. There is no jobs except maintainance plans. Nothing in event viewer too. The database recovery model is simple. How can i solve this problem Please advise me to solve this problem Thanks Krishna.
View Replies !
Deleting Orphaned Data - Maintenance
Hi, I really don't know how frowned upon my approach is here, but it was the only way I have been able I've been able to do it. On my application, when users delete their account, it sometimes brings the db server to a COMPLETE crawl. The reason is some users who delete have many years of related data, and when the data deletes with them, its very slow. To avoid this I've taken off many contstraints, and I do have some sprocs that deleted orphaned data at night. thoughts on this approach ?
View Replies !
In SQL 2000 In TEXT Data Type How Much Long Is The Limit?
Hai Every one i am facing a werid problem it is related to storing a long text data in SQL 2000 the text data is some thing like the following "dshjfsjlksdjakdjlksadjfeidkadflkdsajfieawirfjalkdfjsakdfjaiekdvnmckaumnmmmmmmmmmmmmmmmoadifdjsakdjfauiereoweiiiiiiiiiiiiiiiiiiiiiiiiiiidalfkjdsa,mlfdsdflvmsaldifsdjfskladfakdfjakladkalfkfadkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkadlkfjaidfajfkamcmmmmmmmmmmmmmmmmmmmmmmmmmmmmmiadlmfalierfmaerjeaiaelelllllllllllllllllllll..." in short it is really long so i opted to go with TEXT data type as it states that it can store more than 8 kb but when i try to insert this data it gives me error stating that Text data type cannot be of length more than 128...........? What am i doing wrong........if Text is not the proper datatype to store such a data then can anyone suggest some thing better............... Thanks in advance Austin
View Replies !
Deleting Using SqlDataAdapter Via A Data Access Layer
I've a management module (managing Products) currently being displayed on the aspx page using ObjectDataSource and GridView control.The datasource is taken from a class residing in my Data Access layer with custom methods such as getProducts() and deleteProduct(int productID)I'm currently using SqlDataAdapter as well as Datasets to manipulate the data and have no big problems so far.However, my issue is this, each time i delete a product using the deleteProduct method, I would need to refill the dataset to fill the dataset before i can proceed to delete the product. But since I already filled the dataset using the getProducts() method, is it possible to just use that dataset again so that I dont have to make it refill again? I need to know this cos my data might be alot in the future and refilling the dataset might slow down the performance. 1 public int deleteCompany(Object companyId) 2 { 3 SqlCommand deleteCommand = new SqlCommand("DELETE FROM pg_Company WHERE CompanyId = @companyId", getSqlConnection()); 4 5 SqlParameter p1 = new SqlParameter("@companyId", SqlDbType.UniqueIdentifier); 6 p1.Value = (Guid)companyId; 7 p1.Direction = ParameterDirection.Input; 8 9 deleteCommand.Parameters.Add(p1); 10 dataAdapter.DeleteCommand = deleteCommand; 11 12 companyDS = getCompanies(); // <--- I need to refill this before I can delete, I would be iterating an empty ds. 13 14 try 15 { 16 foreach (DataRow row in companyDS.Tables["pg_Company"].Select(@"companyId = '" + companyId + "'")) 17 { 18 row.Delete(); 19 } 20 return dataAdapter.Update(companyDS.Tables["pg_Company"]); 21 } 22 catch 23 { 24 return 0; 25 } 26 finally { } 27 } I thank you in advance for any help here.
View Replies !
Deleting Data By Calling The Stored Procedure In The .NET
Hi, does anyone know how to delete data from the SQL database by calling the stored procedure in the Visual Basic.NET? Because I did the Delete hyperlink bounded inside a datagrid. I have already displayed the appointment date, time in the datagrid so I do not have to input any values inside it. These are my stored procedures code for deleting: ALTER PROCEDURE spCancelReservation(@AppDate DATETIME, @AppTime CHAR(4), @MemNRIC CHAR(9)) AS BEGIN IF NOT EXISTS (SELECT MemNRIC, AppDate, AppTime FROM DasAppointment WHERE (MemNRIC = @MemNRIC) AND (AppDate = @AppDate) AND (AppTime = @AppTime)) RETURN -400 ELSE IF EXISTS (SELECT MemNRIC FROM DasAppointment WHERE (DATEDIFF(DAY, GETDATE(), @AppDate) < 10)) RETURN -401 ELSE DELETE FROM DasAppointment WHERE MemNRIC = @MemNRIC END IF @@ERROR <> 0 RETURN @@ERROR RETURN DECLARE @status int EXEC @status = spCancelReservation '2005-08-16', '1900', 'S1256755J' SELECT 'Status' = @status Can someone pls help? Thanks!
View Replies !
Reduce Table Size Without Deleting Data
We are using SQL Server 2000 Standard ed, sp4 on Server 2000 Advanced.We have one table that is three times as large as the rest of the database.Most of the data is static after approximately 3-6 months, but we arerequired to keep it for 8 years. I would like to archive this table (A), butthere are complications.1. the only way to access the data is through the application (they areimages produced by the application-built on Power-Builder)2. there are multiple tables refrencing this table and vise-versa3. we restore the entire db to two other servers for testing and trainingregularly4. there might be more complications that have not been thought ofCurrently, our only plan is to setup a seperate server with a copy of this dbon it and the application. Leave only the tables necessary to access the data,and if this 'archive' works, remove from production the data from the table Aand all references to the table A from rows on the other tables.I mentioned #3 because someone mentioned a third party tool that may be ableto pull the data from the table, archive it elsewhere, and at the same time,place a 'pointer' in the table to the new storage location. The tool theymentioned only works on Oracle and we have not explored beyond that yet.I am ready to explore ideas and suggestions; I am still new to the DBA world,I am out of ideas.Thank you!--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200607/1
View Replies !
Deleting Extra Tempdb Log And Data Files
We had someone create an extra data file and log file for tempdb. Sowe currently have two data files and two log files. Is it possible todelete the newly created data and log files? If I just delete thephysical files, I assume they'll get created as soon as SQL Servergets started back up. Any help would be great, since a single dataand log file for tempdb is my goal.Thanks much.sean
View Replies !
Urgent! Please Help! Deleting Data From A Huge Table
I have a huge table with 4 primary keys on it. I need to delete the data from this table ( approx. 5.6 millions records to be deleted). It takes a hell lot of time to delete it by normal query. Can someone please suggest me a better way? Any help will be appreciated.
View Replies !
Deleting Large Amount Of Data From The Table......
I need to delete data from a particular table which has more than half a million records. The data needs to be deleted is more than 200,000 records from the table. What is the best way to delete the data from the table other than importing into a temporary table and performing the same operation? Let me know if the strategy to be followed is okay. 1. Drop all the triggers 2. Drop all the indexes 3. Write a procedure with a loop setting ROWCOUNT to 1000 and delete the records. ( since if I try to delete all the rows it will give timeout error ) The above procedure will delete 1000 records for each batch inside the loop till it wipes out all the data for the specified condition. 4. Recreate Indexes and Triggers. Please let me know if there are any other optimal solution. Thanx, Zombie
View Replies !
|
TRACKER
