The Posts Should Not Be Older Then 2 Months In Database
Jan 28, 2008
Is it possible to send a email after a certain date has pass by. And 2 days after the email has been sent the post should be removed if the member doesn't answer the message.
example: The posts should not be older then 2 months. When they get older than 2 months the SQL-Server detects this post and sends a messegae to the member if he wants to renew it. If he ignors it for 2 days after the email the post will be removed from the database. (Some posts have images also, They should also be removed from the harddrive)
Hi ! i am developing feature like blogs where a user posts but what i am looking for a stored procedure which could count that how many post were made in a particular month like
I am in the process of moving a database to a new location. My problem is that I do not have the most recent full backup file. I have a previous full backup file and I also have all of the transaction log backups from the time of the old backup to the present time.
My question is, do I need the most recent backup to restore the database, or can I do it with the older backup plus the transaction log backups?
I need only the count of databases that last fullbackup was older then 24 hours or null. and status is online. I have tried
SELECT Count(DISTINCT msdb.dbo.backupset.database_name) From msdb.dbo.backupset where datediff(day,backup_finish_date,GETDATE()) > 1 -- or is null and Database_Name not in ('tempdb','ReportServerTempDB','AdventureWorksDW','AdventureWorks') --online also group by Database_name, backup_finish_date
Tried using where max(backup_finish_date) < datediff(day,backup_finish_date,GETDATE()) .But get the aggregate in where clause error. get a count of databases with backups older than 24 hours not including the samples, report service, and tempdb. I would also want to put status is online but havent gotten the above to work so havent tried to add that yet.
Hi guys, Could anyone help me to write sql query to select only three months record from database if the cur month is march then i need jan, feb and mar data , if it is jan, then i need nov , dec and jan data...how can i write sql query for this, i need this for creating reports and also how cal i write queries based on days...mean to say that i need to select all records of particular month that falls on monday or tuesday... Help me out. Thanks in advance
is something wrong with this webite or is it moderated?
i have tried 5 times to post and it never appears. i just get a brief message flash after posting that says 'if xxxx has chosen to moderate.... you will be taken to that thread' then i go to that thread. no post.
if i resubmit, i get '...duplicate post in last 5 minutes' - if it's duplicate, where is the post? this forum is becoming too frustrating for me to use.
select Max(EntryID),MemberID from Blogs where RoleID = 2
with FilteredLastPost ( MemberID,EntryID,PublishedDate)
as
(
select MemberID,EntryID,PublishedDate from Blogs where IsPublished = 1 and MemberID in( (select MemberID from Members where RoleID = 2)) order by EntryID desc, MemberID asc
)
select MemberID from FilteredLastPost
select distinct Blogs.MemberID,EntryID,PublishedDate from Blogs
right join Members on Members.MemberID = Blogs.MemberID
where not PublishedDate ='' and IsPublished = 1 and Members.RoleID = 2
order by EntryID desc, PublishedDate desc
select DISTINCT MemberID, EntryID, PublishedDate DISTINCT IsPulished from Blogs where IsPublished = 1 and not PublishedDate ='' and MemberID in ( select MemberID from Members where RoleID = 2 )
I'd like to select from a table, let say with 2 variables. One with a currency and one with integer. The integer value is a number between 0 and 255. The integer value represents a value of what kind of BIT is turned on in one BYTE.
Down to business.
I am looking for all BYTES with the fifth BIT = 1 as in example 00001000.This represents the value of 16. This value is an integer in my table. But as you see, the BYTE has a number of different values with the fifth=1 as the rest of the BITS can be 1 as in example 01011000 = 26.
Down to code. Tablename = myMoney Column Name = mCurrency; Data Type = decimal(18,2) Column Name = bit5; Data Type = int
Code Block
SELECT sum(mCurrency) FROM myMoney WHERE somefunction(bit5) = 1
I have made the code in VB some time ago but this time I need to do the aclculation in the SQL server.
So I have simple forum. At main page I have gridView which display topics. When I go to topic #5 (for example) I use repeater to display posts and authors. Here is a code: SELECT aspnet_Users.UserName, forum_posts.post_id,forum_posts.post_content, forum_posts.topic_id, forum_posts.post_date FROM aspnet_Users INNER JOIN forum_posts ON aspnet_Users.uID = forum_posts.user_id WHERE (forum_posts.topic_id = @topic_id) Now I have a problem - how can I display with each user his number of posts - eventually how can I display for example if he has 25 posts - one star, 50 posts - two stars, or maybe display "Starter" range, etc. How to make this, cause I don't have any idea.
Let's say a user profile page on a website contains a description about a car that the user wants to sell. The user could try to be wise and write something very short like "buy my car" or the user could write a very lengthy description of his car to convince the buyer to make the purchase. Or somebody creates a thread in a forum or replies to a topic (much like what we do here). In these instances, what is the most efficient manner of storing the high-character content in these examples with SQL Server 2005? Keep in mind that a user could make a 2-character post like "OK" or could write a long reply or create a huge profile. So the data type has to be efficient and flexible. Judging from the list of available types in my Express 2005, I would guess the varchar(MAX) or the text data types could be used. Or would I use some other data type? TIA.
I use Widows Vista Business, have uninstalled all previous versions of SQL before installing SQL 2005 Express.
At first, I faced "Receiving WMI configuration error. SQL Server System Configuration cannot be executed due to to WMI configuration on this computer Error:2147749896 (0x80041008)."
Then, I tried this cmd someone posted as:
FIXWMI.CMD ------------------------
@echo on cd /d c: emp if not exist %windir%system32wbem goto TryInstall cd /d %windir%system32wbem net stop winmgmt winmgmt /kill if exist Rep_bak rd Rep_bak /s /q rename Repository Rep_bak for %%i in (*.dll) do RegSvr32 -s %%i for %%i in (*.exe) do call :FixSrv %%i for %%i in (*.mof,*.mfl) do Mofcomp %%i net start winmgmt goto End
:FixSrv if /I (%1) == (wbemcntl.exe) goto SkipSrv if /I (%1) == (wbemtest.exe) goto SkipSrv if /I (%1) == (mofcomp.exe) goto SkipSrv %1 /RegServer
kipSrv goto End
:TryInstall if not exist wmicore.exe goto End wmicore /s net start winmgmt :End
I tried to install SQL2005 Express again, but then it gives:
Error 1603 installing Microsoft SQL Native Client.
I have an old Compaq Presario walmart pc. The stats on it are: 750MHz Duron, 512MB RAM, currently have 20GB and 30GB hard drives and planning on upgrading HD (100GB+), onboard nvidia vanta (8mb tnt2). If I get a larger hard drive, XP Pro, and put SQL Server Express on this computer, would I be able to get things done, or will this be extremely slowed down?
Hi All Can anyone help me to keep track of older view or stored Procedure that has been altered so that I can compare the two and make out the changes that have been made.At present I 'm able to get just the altered view or storedProcedure but how to know that what has been changed. This will be somewhat similar to Instead if and after in DML trigger but DDL triggers doesn't supports it.If anyone is aware of something then please help THANKS in advance Anisha
Hey Guys,I have been trying to work out how I would delete a record that was created more then 10 minutes ago.I can use this to delete records older then a day.DELETE FROM DownloadQueue WHERE Downloading = '0' AND QueuePos = '0' AND DateTime < GETDATE() - 1Just need something now that will do it for just 10 minutes.Cheers.
I am little confused in writing the exact query i.e filling the correct details in the query . To simplify let me explain....
I am using the query as
delete MYTABLE where datediff(dd,loaddate,getdate())>5
I have a table now with loadate column which gets the default date and time . The loadate shows correct date and time when the data was imported in the table .
Now suppose if i want to delete previous 5 days records from today ( for e.g today is 20/08/2002 3:40:00 PM ) ideally it should delete all records which are 5 days older from today . i.e from 20/08/2002 3:40:00 PM to 15/08/2002 3:40:00 PM ) But when i execute the datediff command , it deletes the records previous than 15/08/2002 till 15/08/2002. The records from 15/08/2002 to 20/08/2002 remain intact .
I am getting some different results .
Am i missing something in the query or i am confused about the calculation of the dates the datediff command performs .
Is the logic correct or i am missing someting important ?
Ok in SQL 2000 this is within the maintaince plan, where as i have to create a seprate one for sql 2005 being i assume the 'clean up history' I choose the backup andrestore option choose 2 weeks and then run the job once created it runs sucssessfully but yet it dosnt delete a thing. I have backups going back almost 2 months now. What on earth am i not doing?
I have a scheduled job which does an text file import in my database . The data gets appended in my table every day from this import job .
Since my table is growing every day , i want to truncate the table after the data has been collected for three months i.e 90 days . The table will be empty and the new data will flow in through the import .
Any thoughts how to do it through query and schedule it ???
So I've been monitoring long-running transactions on a SQL Server that hosts a couple of vendor-supplied databases that look after our factory.Today I noticed a pair that have confused my Excel spreadsheet (that I've been using to analyze these transactions).So here's the weird thing that I spotted. Given this query:
SELECT p.spid, p.login_time, at.transaction_begin_time, datediff(second, p.login_time, at.transaction_begin_time) as [difference] FROM sys.sysprocesses AS p INNER JOIN sys.dm_tran_session_transactions AS st ON st.session_id = p.spid INNER JOIN sys.dm_tran_active_transactions AS at ON st.transaction_id = at.transaction_id
[code]....
I had a look in the event log on the server, which had just been rebooted at around that time. It seems that the clock got changed on boot-up, with the size of it quite surprising. This meant that these processes were able to start their transactions *before* they logged on. Hopefully this doesn't cause any other weird problems.So I've requested an investigation about time synchronization on our virtualization hosts... and in the mean time, have set the SQL Server services to 'delayed start'.
When running the following SQL statements, I get the same results.Though I need to count only -30 days. Both statements below alsoconsider the time of the day as well, which is not desiredDELETE FROM MNT_RWHERE MNT_R.TIMESTAMP < GETDATE()- 30DELETE FROM MNT_RWHERE MNT_R.TIMESTAMP < DATEADD(d, -30, GETDATE())Here is the format of the values in columnMNT_R.TIMESTAMP2005-08-09 06:06:44.5772005-08-09 06:06:46.8102005-08-09 06:06:49.060So, since data are inserted into the MNT_R table every few seconds, mydelete statement will delete different number of rows, according to thetime of the day it runs.Can you please post a SQL query that will not give me this headache?thanx a lot all
I am using the backup task and backing up a database but want to delete all backup files older than 5 days old. I am using the file task for this and have built the path in a variable but am trying to use a wildcard for the time. I am getting illegal character in path. How can I go about this.
I currently have E:MSSQL.1MSSQLBackupdatabasename_backup_20070309*.bak in my input variable and am trying to delete the file databasename_backup_200703091532.bak
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO
[code]....
I am trying to display all records older then 5 Mondays span..so far I got this and it is working fine but it is excluding the records with Id 2 and 4 (Sundays) I would like to include these records as well.
I want to delete all files in a given folder that are older than 3 days from today's date.
I tried using a "Foreach Loop container" with a "File System" task inside it but found I couldn't access any file properties such as the file creation date. Am I using the wrong task for this job?
TIA,
Barkingdog
P.S. History of the problem:
I used the sql 2005 Database maintenance program to setup our database backup jobs. One day I noticed that the free space on the drive where we keep the dumps had grown small. I found that we had at least 4 weeks of dumps in there, not the 3 days I wanted to keep! I looked but could not find the "delete file" option in the SSIS package generated by the Database Maintenance Wizard. No wonder the files were piling up.
Setup log shipping from an Australian SQL2008R2 server to a UK SQL2012 server. With the time involved to copy the large backup file across a WAN link, we end up with around 2 days of log backups since the full backup.
- The Full Backup is restored to the UK Server to create a new database in norecovery mode. - The first log backup file is manually copied to the UK server and restored. - Start the Log Ship copy SQL Agent job - starts copying files prior the full backup on the Australian server. Copies 1 Log backup file and then fails. - Manually copy the next Log backup file to the UL server. - Start the Log Ship Restore SQL Agent job and runs successfully and restores the Log file. - Start the Log Ship copy SQL Agent job - AGAIN it starts trying to copy files prior to the Full Backup. IS this correct behaviour? I thought it should only copy Log files since the full backup.
