|
Once you delete the data and commit the data, the original data is gone. If this is not the behaviour that you want, you need to use a different method to "remove" the data. Normally this is done by setting a flag to mark the record as deleted somehow. This means that all the selects that you do for live data would also need to use this flag.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
thanks a lot for your immidiate reply ....
May you explain me the flag you wrote? or what do u mean by a flag?
Thanks alot
Hamody
|
|
|
|
|
Mohammad Daba`an wrote: May you explain me the flag you wrote? or what do u mean by a flag?
The flag is some attribute in the table that defines whether or not this is a deleted record. For instance:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Rule]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Rule]
GO
CREATE TABLE [dbo].[Rule] (
[ID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Name] [nvarchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Description] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
[Solution] [int] NULL ,
[Deleted] [char] (1) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
The Deleted flag is N for live records, and Y for deleted records.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
well I'm not too sure if u can actually retrieve a deleted data after it's been commited, may be its possible throught some complex kind of operation in the database logs but its not in my knowledge
but I have an idea for this kind of situation... if you can maintain a log for urself by maintaing a table for loging the insert, del,update operations and setting up triggers on that table to insert a new record after every del, insert, update operation so late u can view every single operation a user performed... and do what ever u want with that too
hope it helps,
Rocky
|
|
|
|
|
thanks a lot dear for your good idea, but i don't know, i think that there s a solution for retrieving back any deleted data from any DB table, i think it is allowed for a specific period, i mean that may be you can return back ur deleted data after a specified time since ur delete.
do u know any way we can do this
Thanks alot
Hamody
|
|
|
|
|
You could always use a product like Log Explorer[^].
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
well no dear I dont know, if I knew I would have told u already.
|
|
|
|
|
How to deploy SQL Server 2005 - SQL Server Integration Services into SQL Server
|
|
|
|
|
hi,
just take a look at this article:
http://www.sqlis.com/31.aspx
thanks,
Rahi
If you look at what you do not have in life, you don't have anything,
If you look at what you have in life, you have everything... "
|
|
|
|
|
I Have Created the Windows Form with SqlConnection objects,SqldataAdapters,Command objects ,DataSetObjects everything.I have also displayed the datas from the database into the dataGrid.But the problem is Iam not able to transfer the same datas in to the word document.Please find me the solution
With Regards
<b>sashi:</b>
Thankyou
|
|
|
|
|
hi sashi,
go thru these links:
1.http://www.codeproject.com/office/#Microsoft+Word
2.http://www.google.com/search?hl=en&q=Displaying+the+datas+from+the+database+to+the+word+Document+in+Visual+Studio.Net+2005
3 http://sylk.qarchive.org
thanks,
Rahi
If you look at what you do not have in life, you don't have anything,
If you look at what you have in life, you have everything... "
|
|
|
|
|
Hi,
I need to access SQL Server 2005 data from the browser by just supplying a URL. IS this possible? . I see that it was possible in SQL Server 2000 using the IIS Virtual Directory MAnagement. I have looked into SQL Server Web Services in SQL Server 2005 but, that does not solve our purpose. I'm looking for direct access from the browser using a URL? Please Help.
Thanks and Regards,
Sindhu
Sindhu
|
|
|
|
|
Hi please can any one explain me What are the steps you will take to improve performance of a poor performing stored procedure?
thanks,
Rahi
If you look at what you do not have in life, you don't have anything,
If you look at what you have in life, you have everything... "
|
|
|
|
|
Look at this thread[^]
Wout Louwers
|
|
|
|
|
OK, I have to write a SQL query for someone, for a report. There's financial values involved. There's three tables, essentially. Call them account names, account types, and transactions. I need to return the value of each transaction, the sum of the transactions for the account, and the sum of all account transactions across all account types for the account name. I'm not sure how best to do this, I'm thinking of creating a temporary table at the bottom level ( where the actual data is ) and joining against it to do a SUM on the higher levels. To do that, I seem to need to do group by, which then means I need to specify all my non grouped column names, is that right ?
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
|
|
|
|
|
Christian Graus wrote: To do that, I seem to need to do group by, which then means I need to specify all my non grouped column names, is that right ?
When you do a GROUP BY only the columns specified in the GROUP BY clause may appear in as a column in the SELECT portion. Anything else must be put into an aggregate function. These don't supply a column name but you can add one by using AS [name] for example:
SELECT [Year], SUM(amount) AS [TotalAmount]
FROM SomeTable
GROUP BY [Year]
Does that help?
|
|
|
|
|
It's what I expected, I just felt that was a PITA
Christian Graus - C++ MVP
'Why don't we jump on a fad that hasn't already been widely discredited ?' - Dilbert
|
|
|
|
|
Christian Graus wrote: It's what I expected, I just felt that was a PITA
You are not required to name the results of the aggregate functions. It is optional. If you are accessing the column by its ordinal position it probably isn't worth naming the column.
|
|
|
|
|
I have 2 tables, a galleries table and a photos table...
I'm trying to select all the data from the galleries table and then JUST ONE row from the photos table based on the gallery_id that is the FK.
If I do the following:
<br />
SELECT Galleries.Id, Galleries.Name, Galleries.Description, Photos.Id AS Expr1<br />
FROM Galleries INNER JOIN<br />
Photos ON Galleries.Id = Photos.Gallery_Id<br />
WHERE (Galleries.Visible = @Visible)<br />
I get rows returned with duplicate gallery information... So basically I want to know how i can get a single row of data for a each unique gallery.Id and add a single photo.id that matches that gallery_id...
I've messed around with distinct and still can't get it to work...
(Sql Server 2005 Express)
Thanks
|
|
|
|
|
We have a schema very similar to yours. I don't know what you're trying to accomplish, but I'm going to take a stab that you want to display a thumbnail for the gallery?
Based on my experience it would be best to add a column named ThumbnailID, then have a process (UI or automated doesn't matter) which selects a thumbnail for the gallery and updates the ThumbnailID column. Then you could do the following:
SELECT Galleries.Id, Galleries.Name, Galleries.Description, Photos.Id AS Expr1<br />
FROM Galleries INNER JOIN<br />
Photos ON Galleries.ThumbnailID = Photos.Id<br />
WHERE (Galleries.Visible = @Visible)
I know this isn't actually what you asked for, but I'll give you an alternative as well:
<br />
SELECT Galleries.Id, Galleries.Name, Galleries.Description, <br />
(SELECT TOP 1 Id, FROM Photos WHERE Galleries.Id = Photos.Gallery_Id) Id<br />
FROM Galleries<br />
WHERE (Galleries.Visible = @Visible)<br />
The problem is that over time, this will perform very poorly. For a little bit of extra work, the first option will scale much better.
I hope it wasn't too presumptuous - Good luck!
|
|
|
|
|
Ooops, small correction, with the first option you could actually drop the INNER JOIN and just use Galleryies.ThumbnailID
|
|
|
|
|
You hit it on the head... I'm trying to do exactly what you said, but was hoping to just solve the problem without changing my schema and using sql instead. But if it will perform poorly in the future I'll just change it now...
Thanks
|
|
|
|
|
Assume that I am working with a single database. I am talking about a single database file. Now, both access and sql server. I know the app.config file provides the database connection information. I wonder if I can have an option for a single database connection where user can decide either access or sql. It seems like each of them will require its own data adapter. I want to know if that is possible and how can I do that.
For example, assume that I do have a database name "nwnd", this database depends on the user's choice. For example, if the user wants to choose ms access, the application will use ms access. If the use chooses to use sql, the application will use sql. The problem is, I cannot find a way to choose the database connectivity for a single component. Assume that if the user choose ms access, the ms access table will display on the grid. Also if the user chooses ms sql, the ms sql table will display on the grid. I cannot find a way to do that from the database explorer or wizard. It seems like that can only be done hard coding or manually.
I will appreciate your comment.
|
|
|
|
|
Never trust the wizards. They are at best useful only for elementary cases and learning.
|
|
|
|
|
Can anyone comment on the performance pros/cons of using table-valued functions vs. Stored Procedures in SQLServer? Specifically:
1) are the execution plans for the functions stored as they are in stored procedures?
2) When using table variables inside the function, does the entire result set automatically wind up in memory, or can it be streamed using a reader?
Thanks for any input.
|
|
|
|