|
I want to delete some postings(records)automatically when it expiry date comes ,those are in the database table.How can i schedule this.I am using sqlserver 2000 and Asp.Net with C#.
Please help me
Thanks in advance.
Krishna Prasad
|
|
|
|
|
Create a DTS task in SQL Server that deletes these records based on the expiry date. Then, you create a job that wraps the task and schedule it to run every day. I don't have SQL Server 2000 installed anymore so I can't give you the exact steps, but it's easy enough to figure out with the aid of Books Online.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
You can create a trgger on insert if this table is not large or heavy used
CREATE TRIGGER trg_DelOldRec
AS
DELETE FROM [tbl name] WHERE DateCreated < DATEADD(mm, -3,GETDATE())
|
|
|
|
|
I have an database application and I want to add a button to Back Up the data, and another button to revert to saved databases(Load Archives). Im using VS Studio 2005, ADO.
I need an explanation of how the procedure of adding this functionality to an application goes.If you use bindingsources, or tablesAdapter and also the SQL statements that achieve this.
Thank you in advance
Is this chair taken
|
|
|
|
|
You want to program using SMO. There is a sample app located in the samples that come with SQL 2005 C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\SMO\BackRest
|
|
|
|
|
Hi
Can any one out there help me? I have got a database table which contains sessions that are in progress. Each session has a session balance and this session balance is meant to change after every month. The session balance should be deducted by the going rate at the end of every month.
What am I suppose to use and how can I go about this?
Any help will be most appreciated.
|
|
|
|
|
I am using below sql
SELECT CategoryId,CategoryDesc
FROM TB_CategoryMaster AS Category
WHERE CategoryActive=0
ORDER BY CategoryDisplayOrder
FOR XML AUTO, ELEMENTS, TYPE
and output comes like ...
Buy * Sell instead of Buy & Sell... where * stands for amp
How i can handle the same at sql end before sending to client end,,,,
Thanks!!
|
|
|
|
|
Hi,
Can u tell me to download an SQL server 2000 ebook/pdf from the net free?
Can u?
Senthil S
Software Engineer
|
|
|
|
|
I would not ask for that at this website. Many of our contributors have written books. When you download a free book, it is basically stealing.
_____________________________________________
Flea Market! It's just like...it's just like...A MINI-MALL!
|
|
|
|
|
leckey wrote: When you download a free book, it is basically stealing.
Unless the author allows a free watered down version of the book to be available. I don't know of very many who do.
"Try asking what you want to know, rather than asking a question whose answer you know." - Christian Graus
|
|
|
|
|
|
I don't know why, but I get the feeling that website is putting up these books illegally. These are not free books.
|
|
|
|
|
You have to become member in that site then after any search for any book it provides links to download.
I Love SQL
|
|
|
|
|
Blue_Boy wrote: You have to become member in that site then after any search for any book it provides links to download.
And that makes it legal, does it?
|
|
|
|
|
Colin Angus Mackay wrote: And that makes it legal, does it?
Don't know, but it provides links for free e-books (and I can say leatest e-books).
I Love SQL
|
|
|
|
|
SQLServer comes with a rich BooksOnline both whilst installation and at MSDN Online. What more are you looking at?
|
|
|
|
|
I want to restrict user for not adding the new record if table already contains active record. (means Active=0)
CREATE TRIGGER TR_TB_QuizMaster ON TB_QuizMaster
FOR INSERT
AS
DECLARE @iCount int
BEGIN
SELECT @iCount=COUNT(*) FROM TB_QuizMaster WHERE Active=0
Print @iCount
IF (@iCount>=1)
BEGIN
Print 'Active record already exists. Only one active record can exisits'
Rollback
END
ELSE
print 'Inserted'
END
But this does not allow me to enter a new record unless i change it to IF (@iCount>=2). This is because it fires after insert.....Is there any way by which we can fire trigger BEFORE actual DML operation....
Please suggest
Thanks!!
-- modified at 6:49 Friday 21st September, 2007
|
|
|
|
|
There is - it's called an instead of trigger and works by defining a trigger on a view which matches the table structure. What happens is you insert into the view which runs the trigger and then depending on your result, inserts into the appropriate table. From memory, it would look something like this:
create trigger MyTableInsertCheck
on MyView
instead of insert
as
begin
select @count = count(1) from mytable m inner join inserted i on m.keyfield = i.keyfield
if @count = 0
begin
insert name, keyfield into mytable
select name, keyfield from myview
end
end
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks and if i don't want to create views....Is there any Before trigger in SQL 2005.....
|
|
|
|
|
Amit Kumar G wrote: Is there any Before trigger in SQL 2005.....
No (except as in the example above).
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
thanks!! I just read trigger syntax and would really appreciate if you could help me to understand difference between FOR|After clause....
|
|
|
|
|
I want to list dates with a simple select-statement like this.
2007-01-01
2007-01-02
2007-01-03
2007-01-04
2007-01-05
2007-01-06
2007-01-07
2007-01-08
2007-01-09
Any tip how to do that?
_____________________________
...and justice for all
APe
|
|
|
|
|
Do you want to return data that contains this date? If so, use a SELECT ... FROM ... WHERE DATE BETWEEN @startDate AND @endDate.
Alternatively, if you want to just retrieve the dates and are using SQL Server 2005 you could always use a Common Table Expression (CTE) as in:.
WITH DateIncrement (PreviousDate) AS
(
SELECT CAST('2007-09-01' AS DATETIME)
UNION ALL
SELECT DATEADD(d, 1, PreviousDate)
FROM DateIncrement
WHERE PreviousDate < '2007-10-01'
)
SELECT * FROM DateIncrement p As you can see, the CTE passes the start date in to the first part of the query and then does a union to add the incrementing date.
Finally, you could always write your own function to do this, but I prefer the CTE approach.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Perfect!!
WITH DateIncrement (PreviousDate) AS( <br />
SELECT CAST('2007-09-01' AS DATETIME) <br />
UNION ALL <br />
SELECT DATEADD(d, 1, PreviousDate) <br />
FROM DateIncrement <br />
WHERE PreviousDate < '2007-10-01')<br />
<br />
SELECT * FROM DateIncrement p
...was exactly what I wanted!
_____________________________
...and justice for all
APe
|
|
|
|
|
I'm glad it helped. CTEs are really useful for all sorts of hierarchical/recursive searches and people just don't have enough information about them.
Deja View - the feeling that you've seen this post before.
|
|
|
|