Basic Query: Alternatives to Group By for nText column

I am having some difficulty writing a relatively basic query. The objective is to retrieve the new stories (headlines) for the past 3 days from the database. Since each headline can be assigned multiple categories (topics) the query returns a row for every headline assignment. I can't use the 'Group By' expression because one of the columns is nText.

So basically if there is an article written yesterday, "I Love Cats" that gets assigned both topics 'CATS' and 'PETS' I only it returned with the first topic assigned... 'CATS'. Here is a little image of the three tables being called:

http://64.225.154.232/temp_dbDiagram.gif

I don't think that this query is too difficult, but I'm just getting my feet wet with writing queries that are more than select * from whatever. Any insight or recommendations are greatly appreciated.

SELECT headline.HEADLINE_ID, headline.HEADLINE_TITLE, headline.HEADLINE_DATE, headline.HEADLINE_THUMBNAIL,
topic.TOPIC_NAME, topic.TOPIC_URL
FROM tbl_CCF_Headlines headline INNER JOIN
tbl_CCF_Headlines_Topics ON headline.HEADLINE_ID = tbl_CCF_Headlines_Topics.HEADLINE_ID INNER JOIN
tbl_CCF_Topics topic ON tbl_CCF_Headlines_Topics.TOPIC_ID = topic.TOPIC_ID
WHERE (headline.HEADLINE_DATE IN
(SELECT TOP 3 HEADLINE_DATE
FROM tbl_CCF_HEADLINES
GROUP BY HEADLINE_DATE
ORDER BY HEADLINE_DATE DESC))
ORDER BY headline.HEADLINE_DATE DESCTry to cast youe text column to varchar(1000).|||What if the nText-field contains more than 1000 characters? What if it contains more than 4000 characters which I believe is the limit for nVarChar? I have this very same situation and am yet to find a solution...

-Tuukka

Bad characters in strings

Hi
We have a problem whereby a particular application is writing certain ascii
characters to our table which is causing an issue in another application.
Obviously the solution is to fix the application, but it's taking some time.
In the meantime I'm trying to develop a trigger to fix the data. I have a
table which contains a row for every ascii character which we do not want to
allow, I'm trying to develop a very efficient trigger to remove these bad
ascii characters (on around 7 string columns in 1 table)
Any hints on how to do this?
ThanksUse an INSTEAD OF trigger rather than an AFTER (the default), the INSTEAD OF
will do it before the insert/update occurrs thereby making it more
efficient, if you did it in the AFTER trigger (the default) then you'll be
doing an insert and then a further delete.
Use nested REPLACE to remove the characters, do you have a list of the
characters - are there a lot?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"..." <...@.nowhere.com> wrote in message
news:O2Joviu5FHA.472@.TK2MSFTNGP15.phx.gbl...
> Hi
> We have a problem whereby a particular application is writing certain
> ascii characters to our table which is causing an issue in another
> application.
> Obviously the solution is to fix the application, but it's taking some
> time. In the meantime I'm trying to develop a trigger to fix the data. I
> have a table which contains a row for every ascii character which we do
> not want to allow, I'm trying to develop a very efficient trigger to
> remove these bad ascii characters (on around 7 string columns in 1 table)
> Any hints on how to do this?
> Thanks
>
>

Backup.ExpirationDate in Sql Server SMO

I'm writing a console app in Visual Studio 2005 to backup databases using SMO and I have run into a minor snag.

It appears that when I have the Initialize property set to false to allow backups to append onto existing backups, the backups that are past their expiration dates do not get overwritten. I know the RetainDays property requires the Intialize property to be set to true but I haven't seen anything that suggests that the ExpirationDate property does as well.

Does anyone have any suggestions?

I don't think you have run into a minor snag, you have run into desired and expected behavior because Backup is the best thing you have to recreate a SQL Server database including the an actual copy of the database. The reason is when backup is copied to tape, then the .bak file can be deleted manually. That said I think you can find some backup code in the BOL (books online) but try the link below to get started. Hope this helps.

http://www.sqldbatips.com/showarticle.asp?ID=37

|||

Thanks for the response Caddre.

I have the backup code written and the application works fine. I'm just afraid that if the backups go unmonitored (and in our case that is a possibility) the backup file will get extremely large.

Every time I run the console app it appends to the end of an existing backup .bak file, thus doubling it in size. I thought if you established an expiration date, "expired" backups would be overwriiten within the .bak file. That way the .bak file would never be more than twice its size; however, my testing shows that it just keeps getting bigger and bigger.

Thanks again

|||That is what I am trying to explain to you Backup files cannot be allowed to be overwritten because someone could make the mistake of overwriting needed .bak files. You could write code to delete expired .bak files or you could do it manually weekly.