Filter double results from query

We are working on an application which aggregates news articles from various sources. The problem is, when querying the database, is that there are a lot of double results because some of the sources get their news from the same press agency. To clarify things a bit here's a link to an example:

http://84.244.146.27/Zoekresultaten.aspx?SearchQuery=Amsterdam

As you can see, there are a lot of the same results. What we want is to filter out the double results irrespective of the source. In other words; when the query encounters the first result, display that and leave out the other same results.

I've tried filtering them out by using DISTINCT on the 'news header' / results, but that does not seem to work. Any ideas on how to get this done?

Any help is greatly appreciated!

Providing the aggregator select is an infrequent action, you can use a CURSOR within it to select just the first record to a buffer table which the page will read.

|||

Thanks Tatworth for your answer! I'm afraid that it sounds a bit like Chinese to me though...

The aggregator is run every couple of minutes and of course it would be best to filter the results in that step, then filtering on querying results. There's a slight problem though with the aggregator script. It's in PHP :( and PHP and MSSQL don't play along that nicely with eachother as far as I have experienced.

Is there a way to filter within the select query?

|||

Yes, as an example:

14-11-2007

10:52

Marokkaanse vaders in actie in Amsterdam-West

BNdestem

|||

If your answer is 2005 or Express, and you want the earliest record, this syntax will do it for you:

SELECT *

FROM (

SELECT {the fields from your original query},row_number() OVER (PARTITION BY {the list of key fields} ORDER BY {Your datetime field} ASC) AS TheRank

{the rest of your original query}

) t1

WHERE TheRank=1

Example:

SELECT *FROM (SELECT NewsDate,NewsTitle,NewsLink,row_number()OVER (PARTITIONBY NewsTitleORDER BY NewsDateASC)AS TheRankFROM NewsTable) t1WHERE TheRank=1

|||

Thanks very much Motley! It works like a charm!

Filter by months & days without years

I have an application that holds BirthDates.
Is there a way to query for all birthdates between two dates without
considering the year ?
Like: Between "April 1" and Dec 10". I know who to query between two dates,
but it considers the year.
CASystemsUse
Select *
From <TableName>
Where DateValue Between
Stuff(convert(VarChar(8), DateColumn, 112), 5, 4, '0401') And
Stuff(convert(VarChar(8), DateColumn, 112), 5, 4, '1210')
"CASystems" wrote:

> I have an application that holds BirthDates.
> Is there a way to query for all birthdates between two dates without
> considering the year ?
> Like: Between "April 1" and Dec 10". I know who to query between two dates
,
> but it considers the year.
> --
> CASystemssql

Fill() problem in VS .NET 2003 in Windows application C# with SQL Server

I starting to use Visual Studio .NET 2003 creating C# Windows application
with SQL Server and I get problem with method Fill() for which when
running ends with System Error even with the most simple cases which I
copied from a tutorial. everything else works right for ex Preview Data
except for example sqlDataAdapter1.Fill(ds1).
Is it a bug or some other kind of error. What should I do?
Dalius
Fill() problem in VS .NET 2003 in Windows application C# with SQL ServerI get :
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in system.data.dll
Additional information: System error.
"Auto" <dalius@.autocentras.com> wrote in message
news:uvWhT6X2DHA.1704@.tk2msftngp13.phx.gbl...
> I starting to use Visual Studio .NET 2003 creating C# Windows application
> with SQL Server and I get problem with method Fill() for which when
> running ends with System Error even with the most simple cases which I
> copied from a tutorial. everything else works right for ex Preview Data
> except for example sqlDataAdapter1.Fill(ds1).
> Is it a bug or some other kind of error. What should I do?
> Dalius
> Fill() problem in VS .NET 2003 in Windows application C# with SQL Server
>
>|||Post your code... The fill method does work, so I suspect you are doing
something funny... It might be a better place to post it in the dot net
groups, but Either place will do.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Auto" <dalius@.autocentras.com> wrote in message
news:uciFeVa2DHA.3224@.tk2msftngp13.phx.gbl...
> I get :
> An unhandled exception of type 'System.Data.SqlClient.SqlException'
occurred
> in system.data.dll
> Additional information: System error.
>
> "Auto" <dalius@.autocentras.com> wrote in message
> news:uvWhT6X2DHA.1704@.tk2msftngp13.phx.gbl...
> > I starting to use Visual Studio .NET 2003 creating C# Windows
application
> > with SQL Server and I get problem with method Fill() for which
when
> > running ends with System Error even with the most simple cases which I
> > copied from a tutorial. everything else works right for ex Preview
Data
> > except for example sqlDataAdapter1.Fill(ds1).
> >
> > Is it a bug or some other kind of error. What should I do?
> >
> > Dalius
> >
> > Fill() problem in VS .NET 2003 in Windows application C# with SQL Server
> >
> >
> >
>|||Try executing the statement in a try/catch block so that you can get more
detailed info on the error:
try
{
Fill();
}
catch ( SqlException ex )
{
MessageBox.Show(ex.ToString());
}
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Auto" <dalius@.autocentras.com> wrote in message
news:uciFeVa2DHA.3224@.tk2msftngp13.phx.gbl...
> I get :
> An unhandled exception of type 'System.Data.SqlClient.SqlException'
occurred
> in system.data.dll
> Additional information: System error.
>
> "Auto" <dalius@.autocentras.com> wrote in message
> news:uvWhT6X2DHA.1704@.tk2msftngp13.phx.gbl...
> > I starting to use Visual Studio .NET 2003 creating C# Windows
application
> > with SQL Server and I get problem with method Fill() for which
when
> > running ends with System Error even with the most simple cases which I
> > copied from a tutorial. everything else works right for ex Preview
Data
> > except for example sqlDataAdapter1.Fill(ds1).
> >
> > Is it a bug or some other kind of error. What should I do?
> >
> > Dalius
> >
> > Fill() problem in VS .NET 2003 in Windows application C# with SQL Server
> >
> >
> >
>sql

Fill() problem in VS .NET 2003 in Windows application C# with SQL Server

I starting to use Visual Studio .NET 2003 creating C# Windows application
with SQL Server and I get problem with method Fill() for which when
running ends with System Error even with the most simple cases which I
copied from a tutorial. everything else works right for ex Preview Data
except for example sqlDataAdapter1.Fill(ds1).
Is it a bug or some other kind of error. What should I do?
Dalius
Fill() problem in VS .NET 2003 in Windows application C# with SQL ServerI get :
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in system.data.dll
Additional information: System error.
"Auto" <dalius@.autocentras.com> wrote in message
news:uvWhT6X2DHA.1704@.tk2msftngp13.phx.gbl...

quote:

---

> I starting to use Visual Studio .NET 2003 creating C# Windows application
> with SQL Server and I get problem with method Fill() for which when
> running ends with System Error even with the most simple cases which I
> copied from a tutorial. everything else works right for ex Preview Data
> except for example sqlDataAdapter1.Fill(ds1).
> Is it a bug or some other kind of error. What should I do?
> Dalius
> Fill() problem in VS .NET 2003 in Windows application C# with SQL Server
>
>

---

|||Post your code... The fill method does work, so I suspect you are doing
something funny... It might be a better place to post it in the dot net
groups, but Either place will do.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Auto" <dalius@.autocentras.com> wrote in message
news:uciFeVa2DHA.3224@.tk2msftngp13.phx.gbl...

quote:

---

> I get :
> An unhandled exception of type 'System.Data.SqlClient.SqlException'

---

occurred

quote:

---

> in system.data.dll
> Additional information: System error.
>
> "Auto" <dalius@.autocentras.com> wrote in message
> news:uvWhT6X2DHA.1704@.tk2msftngp13.phx.gbl...
application[QUOTE]
when[QUOTE]
Data[QUOTE]
>

---

|||Try executing the statement in a try/catch block so that you can get more
detailed info on the error:
try
{
Fill();
}
catch ( SqlException ex )
{
MessageBox.Show(ex.ToString());
}
Hope this helps.
Dan Guzman
SQL Server MVP
"Auto" <dalius@.autocentras.com> wrote in message
news:uciFeVa2DHA.3224@.tk2msftngp13.phx.gbl...

quote:

---

> I get :
> An unhandled exception of type 'System.Data.SqlClient.SqlException'

---

occurred

quote:

---

> in system.data.dll
> Additional information: System error.
>
> "Auto" <dalius@.autocentras.com> wrote in message
> news:uvWhT6X2DHA.1704@.tk2msftngp13.phx.gbl...
application[QUOTE]
when[QUOTE]
Data[QUOTE]
>

---

Filegroup backups/restore design problems

At the design stage of an application using SQL server 7 I assumed the
following could be done:
- Separate monthly buckets of data into separate filegroups.
- Flag current filegroups as readonly when the new month rollsover.
- design was proposed in this way to minimise backups of the entire
database
of which most will be read-only. Was I completely wrong ?
Backup:
I assumed we could backup the primary, active (current) filegroups and
the previous filegroup.
I assumed a complete db could be rebuilt using the primary and active
filegroup backup.
Is this possible ?
Is there an option on FULL DB BACKUPS to ignore read-only filegroups
and therefore facilitate restoration of a smaller db with only the
primary and active filegroups?
Archiving:
When using this approach - and imagining 2 years of filegroups -
Suppose I would drop one of the tables ?
Could you restore this archived filegroup to the current db ?
thanks
bill
bill
If you don't place the table on a separated physical disks you won't get a
perfomance benefit.
Did you consider to use a full backup database a week period and backup log
file a on an hour period?
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.c om...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
>
> thanks
> bill
|||see inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.c om...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
IF you are talking about 1 filegroup per month, you need to be aware that
the max # of filegroups per database is 256. Additionally, you would have to
name each of the partition tables differently, a single table can not span
multiple filegroups. Each filegroup can be marked as read-only... The idea
you have is correct however, rolling historical data into another filegroup
which doesn't need to be backed up as frequently is one of the primary
usages for filegroups... Maybe you roll everything up into annual
filegroups instead of monthly...
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
You may restore ANY filegroup individually... What you might do , is put
the historical filegroups on a separate raid array from the current
information. Then back the historical stuff up monthly when it changes, and
keep the backups for (maybe) 3 months.

> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
No there isn't... Your full database backup will have to specify the
filegroups you wish to backup... I would still do a REAL full database
backup occasionally.

> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
You may restore any filegroup independently, but everthing in the filegroup
will be restored... You may also do a partial restore to another database,
then pull only the tables you need back into the production database..

>
> thanks
> bill
Your thinking is sound... But before you go production make sure you do
testing to ensure your plan works as you intend...
|||Wayne,
I appreciate your comments.
As for the 1 table per filegroup - yes I am doing that and am aware of
the 256 limit. And yes my table are named differently and they do not
span filegroups - they are created dynamically in their own filegroup
and with a yyyymm extension on each.
Could I please ask you to clarify further my understanding.
You mention that you can do a partial restore but from my
reading/understanding this can only be done on a full db backup.
To recreate my db as proposed I understood that I would need to
1. restore primary filegroup
2. restore active filegroup
3. restore read-only filegroups
4. restore ALL transaction log backups since the last read-only
filegroup backup.
Is this correct ?
And can I do the following on a completely new server:
1. restore primary filegroup
2. restore active filegroup
3. restore ALL transaction log backups since the last primary/active
filegroup backup.
thanks
bill
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<uD0$2vIeEHA.592@.TK2MSFTNGP11.phx.gbl>...
> see inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "bill k" <bkatelis@.yahoo.com> wrote in message
> news:b11556a0.0408020050.63e48688@.posting.google.c om...
> IF you are talking about 1 filegroup per month, you need to be aware that
> the max # of filegroups per database is 256. Additionally, you would have to
> name each of the partition tables differently, a single table can not span
> multiple filegroups. Each filegroup can be marked as read-only... The idea
> you have is correct however, rolling historical data into another filegroup
> which doesn't need to be backed up as frequently is one of the primary
> usages for filegroups... Maybe you roll everything up into annual
> filegroups instead of monthly...
> You may restore ANY filegroup individually... What you might do , is put
> the historical filegroups on a separate raid array from the current
> information. Then back the historical stuff up monthly when it changes, and
> keep the backups for (maybe) 3 months.
>
> No there isn't... Your full database backup will have to specify the
> filegroups you wish to backup... I would still do a REAL full database
> backup occasionally.
> You may restore any filegroup independently, but everthing in the filegroup
> will be restored... You may also do a partial restore to another database,
> then pull only the tables you need back into the production database..
>
> Your thinking is sound... But before you go production make sure you do
> testing to ensure your plan works as you intend...

Filegroup backups/restore design problems

At the design stage of an application using SQL server 7 I assumed the
following could be done:
- Separate monthly buckets of data into separate filegroups.
- Flag current filegroups as readonly when the new month rollsover.
- design was proposed in this way to minimise backups of the entire
database
of which most will be read-only. Was I completely wrong ?
Backup:
I assumed we could backup the primary, active (current) filegroups and
the previous filegroup.
I assumed a complete db could be rebuilt using the primary and active
filegroup backup.
Is this possible ?
Is there an option on FULL DB BACKUPS to ignore read-only filegroups
and therefore facilitate restoration of a smaller db with only the
primary and active filegroups?
Archiving:
When using this approach - and imagining 2 years of filegroups -
Suppose I would drop one of the tables ?
Could you restore this archived filegroup to the current db ?
thanks
billbill
If you don't place the table on a separated physical disks you won't get a
perfomance benefit.
Did you consider to use a full backup database a week period and backup log
file a on an hour period?
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.com...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
>
> thanks
> bill|||see inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.com...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
IF you are talking about 1 filegroup per month, you need to be aware that
the max # of filegroups per database is 256. Additionally, you would have to
name each of the partition tables differently, a single table can not span
multiple filegroups. Each filegroup can be marked as read-only... The idea
you have is correct however, rolling historical data into another filegroup
which doesn't need to be backed up as frequently is one of the primary
usages for filegroups... Maybe you roll everything up into annual
filegroups instead of monthly...
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
You may restore ANY filegroup individually... What you might do , is put
the historical filegroups on a separate raid array from the current
information. Then back the historical stuff up monthly when it changes, and
keep the backups for (maybe) 3 months.
> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
No there isn't... Your full database backup will have to specify the
filegroups you wish to backup... I would still do a REAL full database
backup occasionally.
> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
You may restore any filegroup independently, but everthing in the filegroup
will be restored... You may also do a partial restore to another database,
then pull only the tables you need back into the production database..
>
> thanks
> bill
Your thinking is sound... But before you go production make sure you do
testing to ensure your plan works as you intend...|||Wayne,
I appreciate your comments.
As for the 1 table per filegroup - yes I am doing that and am aware of
the 256 limit. And yes my table are named differently and they do not
span filegroups - they are created dynamically in their own filegroup
and with a yyyymm extension on each.
Could I please ask you to clarify further my understanding.
You mention that you can do a partial restore but from my
reading/understanding this can only be done on a full db backup.
To recreate my db as proposed I understood that I would need to
1. restore primary filegroup
2. restore active filegroup
3. restore read-only filegroups
4. restore ALL transaction log backups since the last read-only
filegroup backup.
Is this correct ?
And can I do the following on a completely new server:
1. restore primary filegroup
2. restore active filegroup
3. restore ALL transaction log backups since the last primary/active
filegroup backup.
thanks
bill
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<uD0$2vIeEHA.592@.TK2MSFTNGP11.phx.gbl>...
> see inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "bill k" <bkatelis@.yahoo.com> wrote in message
> news:b11556a0.0408020050.63e48688@.posting.google.com...
> > At the design stage of an application using SQL server 7 I assumed the
> > following could be done:
> > - Separate monthly buckets of data into separate filegroups.
> > - Flag current filegroups as readonly when the new month rollsover.
> > - design was proposed in this way to minimise backups of the entire
> > database
> > of which most will be read-only. Was I completely wrong ?
> IF you are talking about 1 filegroup per month, you need to be aware that
> the max # of filegroups per database is 256. Additionally, you would have to
> name each of the partition tables differently, a single table can not span
> multiple filegroups. Each filegroup can be marked as read-only... The idea
> you have is correct however, rolling historical data into another filegroup
> which doesn't need to be backed up as frequently is one of the primary
> usages for filegroups... Maybe you roll everything up into annual
> filegroups instead of monthly...
> >
> > Backup:
> > I assumed we could backup the primary, active (current) filegroups and
> > the previous filegroup.
> > I assumed a complete db could be rebuilt using the primary and active
> > filegroup backup.
> > Is this possible ?
> You may restore ANY filegroup individually... What you might do , is put
> the historical filegroups on a separate raid array from the current
> information. Then back the historical stuff up monthly when it changes, and
> keep the backups for (maybe) 3 months.
>
> > Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> > and therefore facilitate restoration of a smaller db with only the
> > primary and active filegroups?
> No there isn't... Your full database backup will have to specify the
> filegroups you wish to backup... I would still do a REAL full database
> backup occasionally.
> >
> > Archiving:
> > When using this approach - and imagining 2 years of filegroups -
> > Suppose I would drop one of the tables ?
> > Could you restore this archived filegroup to the current db ?
> You may restore any filegroup independently, but everthing in the filegroup
> will be restored... You may also do a partial restore to another database,
> then pull only the tables you need back into the production database..
> >
> >
> > thanks
> > bill
> Your thinking is sound... But before you go production make sure you do
> testing to ensure your plan works as you intend...

Filegroup backups/restore design problems

At the design stage of an application using SQL server 7 I assumed the
following could be done:
- Separate monthly buckets of data into separate filegroups.
- Flag current filegroups as readonly when the new month rollsover.
- design was proposed in this way to minimise backups of the entire
database
of which most will be read-only. Was I completely wrong ?
Backup:
I assumed we could backup the primary, active (current) filegroups and
the previous filegroup.
I assumed a complete db could be rebuilt using the primary and active
filegroup backup.
Is this possible ?
Is there an option on FULL DB BACKUPS to ignore read-only filegroups
and therefore facilitate restoration of a smaller db with only the
primary and active filegroups?
Archiving:
When using this approach - and imagining 2 years of filegroups -
Suppose I would drop one of the tables ?
Could you restore this archived filegroup to the current db ?
thanks
billbill
If you don't place the table on a separated physical disks you won't get a
perfomance benefit.
Did you consider to use a full backup database a week period and backup log
file a on an hour period?
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.com...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
>
> thanks
> bill|||see inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"bill k" <bkatelis@.yahoo.com> wrote in message
news:b11556a0.0408020050.63e48688@.posting.google.com...
> At the design stage of an application using SQL server 7 I assumed the
> following could be done:
> - Separate monthly buckets of data into separate filegroups.
> - Flag current filegroups as readonly when the new month rollsover.
> - design was proposed in this way to minimise backups of the entire
> database
> of which most will be read-only. Was I completely wrong ?
IF you are talking about 1 filegroup per month, you need to be aware that
the max # of filegroups per database is 256. Additionally, you would have to
name each of the partition tables differently, a single table can not span
multiple filegroups. Each filegroup can be marked as read-only... The idea
you have is correct however, rolling historical data into another filegroup
which doesn't need to be backed up as frequently is one of the primary
usages for filegroups... Maybe you roll everything up into annual
filegroups instead of monthly...
> Backup:
> I assumed we could backup the primary, active (current) filegroups and
> the previous filegroup.
> I assumed a complete db could be rebuilt using the primary and active
> filegroup backup.
> Is this possible ?
You may restore ANY filegroup individually... What you might do , is put
the historical filegroups on a separate raid array from the current
information. Then back the historical stuff up monthly when it changes, and
keep the backups for (maybe) 3 months.

> Is there an option on FULL DB BACKUPS to ignore read-only filegroups
> and therefore facilitate restoration of a smaller db with only the
> primary and active filegroups?
No there isn't... Your full database backup will have to specify the
filegroups you wish to backup... I would still do a REAL full database
backup occasionally.

> Archiving:
> When using this approach - and imagining 2 years of filegroups -
> Suppose I would drop one of the tables ?
> Could you restore this archived filegroup to the current db ?
You may restore any filegroup independently, but everthing in the filegroup
will be restored... You may also do a partial restore to another database,
then pull only the tables you need back into the production database..

>
> thanks
> bill
Your thinking is sound... But before you go production make sure you do
testing to ensure your plan works as you intend...|||Wayne,
I appreciate your comments.
As for the 1 table per filegroup - yes I am doing that and am aware of
the 256 limit. And yes my table are named differently and they do not
span filegroups - they are created dynamically in their own filegroup
and with a yyyymm extension on each.
Could I please ask you to clarify further my understanding.
You mention that you can do a partial restore but from my
reading/understanding this can only be done on a full db backup.
To recreate my db as proposed I understood that I would need to
1. restore primary filegroup
2. restore active filegroup
3. restore read-only filegroups
4. restore ALL transaction log backups since the last read-only
filegroup backup.
Is this correct ?
And can I do the following on a completely new server:
1. restore primary filegroup
2. restore active filegroup
3. restore ALL transaction log backups since the last primary/active
filegroup backup.
thanks
bill
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message news:<uD0$2vIeEHA.592@.
TK2MSFTNGP11.phx.gbl>...
> see inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "bill k" <bkatelis@.yahoo.com> wrote in message
> news:b11556a0.0408020050.63e48688@.posting.google.com...
> IF you are talking about 1 filegroup per month, you need to be aware that
> the max # of filegroups per database is 256. Additionally, you would have
to
> name each of the partition tables differently, a single table can not span
> multiple filegroups. Each filegroup can be marked as read-only... The ide
a
> you have is correct however, rolling historical data into another filegrou
p
> which doesn't need to be backed up as frequently is one of the primary
> usages for filegroups... Maybe you roll everything up into annual
> filegroups instead of monthly...
> You may restore ANY filegroup individually... What you might do , is put
> the historical filegroups on a separate raid array from the current
> information. Then back the historical stuff up monthly when it changes, an
d
> keep the backups for (maybe) 3 months.
>
> No there isn't... Your full database backup will have to specify the
> filegroups you wish to backup... I would still do a REAL full database
> backup occasionally.
>
> You may restore any filegroup independently, but everthing in the filegrou
p
> will be restored... You may also do a partial restore to another database,
> then pull only the tables you need back into the production database..
>
> Your thinking is sound... But before you go production make sure you do
> testing to ensure your plan works as you intend...

File Shrinking

I have a data file that was created from an ISA web Proxy application that
is (being used by our networking department) 50 gig in size, and I would
like it to be around 5 gig if possible. The problem is that it was
initially creatred at 38 gig, so I cannnot shrink it below this amount. It
has no free space allocated!!! I thought i could even delete some hundred
thousand records from the one and only table in the database, but i can't
even do that without it timing out, or telling me the transaction log is
full. I'm using simple recovery, so i thought the log file would not grow
upon this huge deletion transaction but it did grow enormously. I then
disabled automatic file growth on the log, but it tells me the log file is
full and it won't perform the transaction. I have shrunk the transaction
log down to 500K, but still nothing. I'm using sql server 2005, with
windows xp.
Overall, how can i make this data file smaller? I have tried everything I
know with the transaction log, and manually deleting , but nothing seems to
work.
Error messages:
The transaction log for database 'ISAWebProxyLog' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases (this column says NOTHING)
or
A problem occurred attempting to delete row 1. Error source:
Microsoft.visualstudio.datatools. Error Message: The row values updated or
deleted either do not make the row unique or they alter multiple rows.
Correct the errors and attempt to delete the row again.That is why you should never create the files that large to begin with.
Create them relatively small then manually grow them to the size you need
for the next year or so. But in any case you can delete the rows in small
batches so that the tran log can be truncated in-between batches.
SET ROWCOUNT 50000
WHILE 1 = 1
BEGIN
DELETE FROM Table WHERE xxx
IF @.@.ROWCOUNT = 0
EXIT
END
SET ROWCOUNT 0
Another alternative is to export the data you wish to keep. Drop the DB and
create a new one with a smaller size. Then import the data back in.
Andrew J. Kelly SQL MVP
"Matt Fritz" <mafritz@.state.pa.us> wrote in message
news:eoSDUbHpHHA.5092@.TK2MSFTNGP04.phx.gbl...
>I have a data file that was created from an ISA web Proxy application that
> is (being used by our networking department) 50 gig in size, and I would
> like it to be around 5 gig if possible. The problem is that it was
> initially creatred at 38 gig, so I cannnot shrink it below this amount.
> It
> has no free space allocated!!! I thought i could even delete some hundred
> thousand records from the one and only table in the database, but i can't
> even do that without it timing out, or telling me the transaction log is
> full. I'm using simple recovery, so i thought the log file would not grow
> upon this huge deletion transaction but it did grow enormously. I then
> disabled automatic file growth on the log, but it tells me the log file is
> full and it won't perform the transaction. I have shrunk the transaction
> log down to 500K, but still nothing. I'm using sql server 2005, with
> windows xp.
> Overall, how can i make this data file smaller? I have tried everything I
> know with the transaction log, and manually deleting , but nothing seems
> to
> work.
> Error messages:
> The transaction log for database 'ISAWebProxyLog' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases (this column says NOTHING)
> or
> A problem occurred attempting to delete row 1. Error source:
> Microsoft.visualstudio.datatools. Error Message: The row values updated
> or
> deleted either do not make the row unique or they alter multiple rows.
> Correct the errors and attempt to delete the row again.
>|||The second error message you indicate lets me think that your table is an
Heap (no indexes nor primary key). In some cases this situation causes this
kind of error
Gilberto Zampatti
"Andrew J. Kelly" wrote:

> That is why you should never create the files that large to begin with.
> Create them relatively small then manually grow them to the size you need
> for the next year or so. But in any case you can delete the rows in small
> batches so that the tran log can be truncated in-between batches.
> SET ROWCOUNT 50000
> WHILE 1 = 1
> BEGIN
> DELETE FROM Table WHERE xxx
> IF @.@.ROWCOUNT = 0
> EXIT
> END
> SET ROWCOUNT 0
>
> Another alternative is to export the data you wish to keep. Drop the DB a
nd
> create a new one with a smaller size. Then import the data back in.
> --
> Andrew J. Kelly SQL MVP
> "Matt Fritz" <mafritz@.state.pa.us> wrote in message
> news:eoSDUbHpHHA.5092@.TK2MSFTNGP04.phx.gbl...
>
>

File Shrinking

I have a data file that was created from an ISA web Proxy application that
is (being used by our networking department) 50 gig in size, and I would
like it to be around 5 gig if possible. The problem is that it was
initially creatred at 38 gig, so I cannnot shrink it below this amount. It
has no free space allocated!!! I thought i could even delete some hundred
thousand records from the one and only table in the database, but i can't
even do that without it timing out, or telling me the transaction log is
full. I'm using simple recovery, so i thought the log file would not grow
upon this huge deletion transaction but it did grow enormously. I then
disabled automatic file growth on the log, but it tells me the log file is
full and it won't perform the transaction. I have shrunk the transaction
log down to 500K, but still nothing. I'm using sql server 2005, with
windows xp.
Overall, how can i make this data file smaller? I have tried everything I
know with the transaction log, and manually deleting , but nothing seems to
work.
Error messages:
The transaction log for database 'ISAWebProxyLog' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases (this column says NOTHING)
or
A problem occurred attempting to delete row 1. Error source:
Microsoft.visualstudio.datatools. Error Message: The row values updated or
deleted either do not make the row unique or they alter multiple rows.
Correct the errors and attempt to delete the row again.That is why you should never create the files that large to begin with.
Create them relatively small then manually grow them to the size you need
for the next year or so. But in any case you can delete the rows in small
batches so that the tran log can be truncated in-between batches.
SET ROWCOUNT 50000
WHILE 1 = 1
BEGIN
DELETE FROM Table WHERE xxx
IF @.@.ROWCOUNT = 0
EXIT
END
SET ROWCOUNT 0
Another alternative is to export the data you wish to keep. Drop the DB and
create a new one with a smaller size. Then import the data back in.
--
Andrew J. Kelly SQL MVP
"Matt Fritz" <mafritz@.state.pa.us> wrote in message
news:eoSDUbHpHHA.5092@.TK2MSFTNGP04.phx.gbl...
>I have a data file that was created from an ISA web Proxy application that
> is (being used by our networking department) 50 gig in size, and I would
> like it to be around 5 gig if possible. The problem is that it was
> initially creatred at 38 gig, so I cannnot shrink it below this amount.
> It
> has no free space allocated!!! I thought i could even delete some hundred
> thousand records from the one and only table in the database, but i can't
> even do that without it timing out, or telling me the transaction log is
> full. I'm using simple recovery, so i thought the log file would not grow
> upon this huge deletion transaction but it did grow enormously. I then
> disabled automatic file growth on the log, but it tells me the log file is
> full and it won't perform the transaction. I have shrunk the transaction
> log down to 500K, but still nothing. I'm using sql server 2005, with
> windows xp.
> Overall, how can i make this data file smaller? I have tried everything I
> know with the transaction log, and manually deleting , but nothing seems
> to
> work.
> Error messages:
> The transaction log for database 'ISAWebProxyLog' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases (this column says NOTHING)
> or
> A problem occurred attempting to delete row 1. Error source:
> Microsoft.visualstudio.datatools. Error Message: The row values updated
> or
> deleted either do not make the row unique or they alter multiple rows.
> Correct the errors and attempt to delete the row again.
>|||The second error message you indicate lets me think that your table is an
Heap (no indexes nor primary key). In some cases this situation causes this
kind of error
Gilberto Zampatti
"Andrew J. Kelly" wrote:
> That is why you should never create the files that large to begin with.
> Create them relatively small then manually grow them to the size you need
> for the next year or so. But in any case you can delete the rows in small
> batches so that the tran log can be truncated in-between batches.
> SET ROWCOUNT 50000
> WHILE 1 = 1
> BEGIN
> DELETE FROM Table WHERE xxx
> IF @.@.ROWCOUNT = 0
> EXIT
> END
> SET ROWCOUNT 0
>
> Another alternative is to export the data you wish to keep. Drop the DB and
> create a new one with a smaller size. Then import the data back in.
> --
> Andrew J. Kelly SQL MVP
> "Matt Fritz" <mafritz@.state.pa.us> wrote in message
> news:eoSDUbHpHHA.5092@.TK2MSFTNGP04.phx.gbl...
> >I have a data file that was created from an ISA web Proxy application that
> > is (being used by our networking department) 50 gig in size, and I would
> > like it to be around 5 gig if possible. The problem is that it was
> > initially creatred at 38 gig, so I cannnot shrink it below this amount.
> > It
> > has no free space allocated!!! I thought i could even delete some hundred
> > thousand records from the one and only table in the database, but i can't
> > even do that without it timing out, or telling me the transaction log is
> > full. I'm using simple recovery, so i thought the log file would not grow
> > upon this huge deletion transaction but it did grow enormously. I then
> > disabled automatic file growth on the log, but it tells me the log file is
> > full and it won't perform the transaction. I have shrunk the transaction
> > log down to 500K, but still nothing. I'm using sql server 2005, with
> > windows xp.
> >
> > Overall, how can i make this data file smaller? I have tried everything I
> > know with the transaction log, and manually deleting , but nothing seems
> > to
> > work.
> >
> > Error messages:
> > The transaction log for database 'ISAWebProxyLog' is full. To find out why
> > space in the log cannot be reused, see the log_reuse_wait_desc column in
> > sys.databases (this column says NOTHING)
> >
> > or
> >
> > A problem occurred attempting to delete row 1. Error source:
> > Microsoft.visualstudio.datatools. Error Message: The row values updated
> > or
> > deleted either do not make the row unique or they alter multiple rows.
> > Correct the errors and attempt to delete the row again.
> >
> >
>
>

File Shrinking

I have a data file that was created from an ISA web Proxy application that
is (being used by our networking department) 50 gig in size, and I would
like it to be around 5 gig if possible. The problem is that it was
initially creatred at 38 gig, so I cannnot shrink it below this amount. It
has no free space allocated!!! I thought i could even delete some hundred
thousand records from the one and only table in the database, but i can't
even do that without it timing out, or telling me the transaction log is
full. I'm using simple recovery, so i thought the log file would not grow
upon this huge deletion transaction but it did grow enormously. I then
disabled automatic file growth on the log, but it tells me the log file is
full and it won't perform the transaction. I have shrunk the transaction
log down to 500K, but still nothing. I'm using sql server 2005, with
windows xp.
Overall, how can i make this data file smaller? I have tried everything I
know with the transaction log, and manually deleting , but nothing seems to
work.
Error messages:
The transaction log for database 'ISAWebProxyLog' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases (this column says NOTHING)
or
A problem occurred attempting to delete row 1. Error source:
Microsoft.visualstudio.datatools. Error Message: The row values updated or
deleted either do not make the row unique or they alter multiple rows.
Correct the errors and attempt to delete the row again.
That is why you should never create the files that large to begin with.
Create them relatively small then manually grow them to the size you need
for the next year or so. But in any case you can delete the rows in small
batches so that the tran log can be truncated in-between batches.
SET ROWCOUNT 50000
WHILE 1 = 1
BEGIN
DELETE FROM Table WHERE xxx
IF @.@.ROWCOUNT = 0
EXIT
END
SET ROWCOUNT 0
Another alternative is to export the data you wish to keep. Drop the DB and
create a new one with a smaller size. Then import the data back in.
Andrew J. Kelly SQL MVP
"Matt Fritz" <mafritz@.state.pa.us> wrote in message
news:eoSDUbHpHHA.5092@.TK2MSFTNGP04.phx.gbl...
>I have a data file that was created from an ISA web Proxy application that
> is (being used by our networking department) 50 gig in size, and I would
> like it to be around 5 gig if possible. The problem is that it was
> initially creatred at 38 gig, so I cannnot shrink it below this amount.
> It
> has no free space allocated!!! I thought i could even delete some hundred
> thousand records from the one and only table in the database, but i can't
> even do that without it timing out, or telling me the transaction log is
> full. I'm using simple recovery, so i thought the log file would not grow
> upon this huge deletion transaction but it did grow enormously. I then
> disabled automatic file growth on the log, but it tells me the log file is
> full and it won't perform the transaction. I have shrunk the transaction
> log down to 500K, but still nothing. I'm using sql server 2005, with
> windows xp.
> Overall, how can i make this data file smaller? I have tried everything I
> know with the transaction log, and manually deleting , but nothing seems
> to
> work.
> Error messages:
> The transaction log for database 'ISAWebProxyLog' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases (this column says NOTHING)
> or
> A problem occurred attempting to delete row 1. Error source:
> Microsoft.visualstudio.datatools. Error Message: The row values updated
> or
> deleted either do not make the row unique or they alter multiple rows.
> Correct the errors and attempt to delete the row again.
>
|||The second error message you indicate lets me think that your table is an
Heap (no indexes nor primary key). In some cases this situation causes this
kind of error
Gilberto Zampatti
"Andrew J. Kelly" wrote:

> That is why you should never create the files that large to begin with.
> Create them relatively small then manually grow them to the size you need
> for the next year or so. But in any case you can delete the rows in small
> batches so that the tran log can be truncated in-between batches.
> SET ROWCOUNT 50000
> WHILE 1 = 1
> BEGIN
> DELETE FROM Table WHERE xxx
> IF @.@.ROWCOUNT = 0
> EXIT
> END
> SET ROWCOUNT 0
>
> Another alternative is to export the data you wish to keep. Drop the DB and
> create a new one with a smaller size. Then import the data back in.
> --
> Andrew J. Kelly SQL MVP
> "Matt Fritz" <mafritz@.state.pa.us> wrote in message
> news:eoSDUbHpHHA.5092@.TK2MSFTNGP04.phx.gbl...
>
>

File Protection

Hi -
I'm building a VB.NET application using an MSDE database. I'm working on an
install program for the app, and I'm running into an error with the MSDE
install.
I'm installing MSDE by executing the MSDE setup:
setup.exe INSTANCENAME="test601" BLANKSAPWD=1
DATADIR="d:\data\test601\Data\" TARGETDIR="d:\data\test601\"
DISABLENETWORKPROTOCOLS=0 SECURITYMODE=SQL /L*v
"d:\data\test601\MSDE601.log"
The install works without error on my WinXP Pro development machine. But I
get a Windows File Protection error on a 'fresh' WinXP Home computer.
I create the WinXP Home environment by installing WinXP Home from the MSDN
DVDs and then applying all of the Windows Updates from the MS site (except
for SP2; I'll test that one separately). I then install MSDE by running the
above setup, and I get the following Windows File Protection error:
"Files that are required for Windows to run properly have been replaced by
unrecognized versions. To maintain system stability, Windows must restore
the original versions of these files. Insert your Windows XP Home Edition
CD-ROM now."
How can I prevent this error message from appearing? (MSDE appears to have
installed fine, other than displaying this error.) Or, perhaps more
importantly, how can I install MSDE in a way that does not threaten "system
stability?"
Thanks for your help.
- Jeff
A have the some problem.
Your sistem is hacked by a virus.
"Jeff" <jeff_nospam@.eNetPortals.com> wrote in message news:uNrgd.13498$ta5.4810@.newsread3.news.atl.earth link.net...
Hi -
I'm building a VB.NET application using an MSDE database. I'm working on an
install program for the app, and I'm running into an error with the MSDE
install.
I'm installing MSDE by executing the MSDE setup:
setup.exe INSTANCENAME="test601" BLANKSAPWD=1
DATADIR="d:\data\test601\Data\" TARGETDIR="d:\data\test601\"
DISABLENETWORKPROTOCOLS=0 SECURITYMODE=SQL /L*v
"d:\data\test601\MSDE601.log"
The install works without error on my WinXP Pro development machine. But I
get a Windows File Protection error on a 'fresh' WinXP Home computer.
I create the WinXP Home environment by installing WinXP Home from the MSDN
DVDs and then applying all of the Windows Updates from the MS site (except
for SP2; I'll test that one separately). I then install MSDE by running the
above setup, and I get the following Windows File Protection error:
"Files that are required for Windows to run properly have been replaced by
unrecognized versions. To maintain system stability, Windows must restore
the original versions of these files. Insert your Windows XP Home Edition
CD-ROM now."
How can I prevent this error message from appearing? (MSDE appears to have
installed fine, other than displaying this error.) Or, perhaps more
importantly, how can I install MSDE in a way that does not threaten "system
stability?"
Thanks for your help.
- Jeff
|||Thanks for your response -
While it may be true that a virus was at work here, I was able to get a
different cause ("known issue") from MS (see below). Just curious: what
virus did your computer have?
- Jeff
Response from MS:
I found that it turned out to be a known issue in MSDE setup or SP3a Setup
that File Protection will pop-up . The root cause is the protected system
file sqlunirl.dll was not restored to its original, valid version because
the Windows File Protection restoration process was cancelled by user
interaction. However on computers with Windows XP SP1 if the dllcache
folder is missing or the contents of the dllcache are empty, then you might
still get the pop-ups even with sp3a.
First of all, upgrade your Windows XP to the latest MDAC 2.8
Microsoft Data Access Components (MDAC) 2.8
http://www.microsoft.com/downloads/d...fe3-c795-4b7d-
b037-185d0506396c&DisplayLang=en
Secondly, if above doesn't work, the workaround for this issue is
1. First manually run the dahotfix.exe, which will install/copy the correct
copy of SQLUNIRL.DLL (2000.80.728.0) into the dllcache. (I got the
dahotfix.exe from the sqlredis.exe that ships with SQL2KSP3 in the
x86\other folder. Just right click on this sqlredis.exe and extract the exe
to a local folder. This will give you mdac_qfe.exe. Then again, right click
on mdac_qfe.exe and extract the contents of this exe into another local sub
folder. This should give you a dahotfix.exe)
2. Then run the setup, which should complete successfully with no errors.
"news.microsoft.com" <x> wrote in message
news:uqpxmFdzEHA.2716@.TK2MSFTNGP14.phx.gbl...
A have the some problem.
Your sistem is hacked by a virus.
"Jeff" <jeff_nospam@.eNetPortals.com> wrote in message
news:uNrgd.13498$ta5.4810@.newsread3.news.atl.earth link.net...
Hi -
I'm building a VB.NET application using an MSDE database. I'm working on an
install program for the app, and I'm running into an error with the MSDE
install.
I'm installing MSDE by executing the MSDE setup:
setup.exe INSTANCENAME="test601" BLANKSAPWD=1
DATADIR="d:\data\test601\Data\" TARGETDIR="d:\data\test601\"
DISABLENETWORKPROTOCOLS=0 SECURITYMODE=SQL /L*v
"d:\data\test601\MSDE601.log"
The install works without error on my WinXP Pro development machine. But I
get a Windows File Protection error on a 'fresh' WinXP Home computer.
I create the WinXP Home environment by installing WinXP Home from the MSDN
DVDs and then applying all of the Windows Updates from the MS site (except
for SP2; I'll test that one separately). I then install MSDE by running the
above setup, and I get the following Windows File Protection error:
"Files that are required for Windows to run properly have been replaced by
unrecognized versions. To maintain system stability, Windows must restore
the original versions of these files. Insert your Windows XP Home Edition
CD-ROM now."
How can I prevent this error message from appearing? (MSDE appears to have
installed fine, other than displaying this error.) Or, perhaps more
importantly, how can I install MSDE in a way that does not threaten "system
stability?"
Thanks for your help.
- Jeff

File Operations SQL 2000

I have a directory on our SQL server that contains certain temporay
files that have been generated from another application.
I want to periodically clean this directory up by running an SQL job.
How would I go about this?
Essentially, I just want to delete everything from C:\\application_temp
\ *.*
TIAHi
xp_cmdshel + DEL commanad . Do you rememeber DOS commands?
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184843844.787726.12240@.k79g2000hse.googlegroups.com...
>I have a directory on our SQL server that contains certain temporay
> files that have been generated from another application.
> I want to periodically clean this directory up by running an SQL job.
> How would I go about this?
> Essentially, I just want to delete everything from C:\\application_temp
> \ *.*
> TIA
>|||use xp_cmdshell
eg. exec master.dbo.xp_cmdshell 'dir c:\temp\*.sql'|||xp_cmd_shell gives you the ability to perform any os command, such as DEL.
xp_cmd_shell is turned on by default in SQL 2000 but is turned off by
default in SQL 2005 so this might / might not be easy in your case.
xp_dir_tree also lets you traverse a directory to get a list of files (much
better than using xp_cmd_shell + DIR)
In SQL 2005, you can also write a CLR procedure with external access that
can do something similar.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184843844.787726.12240@.k79g2000hse.googlegroups.com...
>I have a directory on our SQL server that contains certain temporay
> files that have been generated from another application.
> I want to periodically clean this directory up by running an SQL job.
> How would I go about this?
> Essentially, I just want to delete everything from C:\\application_temp
> \ *.*
> TIA
>|||Hi Greg
Did you mean xp_cmdshell ? I also missed 'l' in my post
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%23JKwwjfyHHA.4640@.TK2MSFTNGP03.phx.gbl...
> xp_cmd_shell gives you the ability to perform any os command, such as DEL.
> xp_cmd_shell is turned on by default in SQL 2000 but is turned off by
> default in SQL 2005 so this might / might not be easy in your case.
> xp_dir_tree also lets you traverse a directory to get a list of files
> (much better than using xp_cmd_shell + DIR)
> In SQL 2005, you can also write a CLR procedure with external access that
> can do something similar.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184843844.787726.12240@.k79g2000hse.googlegroups.com...
>|||Hi Uri
No - I actually meant xp_dirtree (i had it as xp_dir_tree previously which
wasn't quite correct)..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zg%23uoDzHHA.3772@.TK2MSFTNGP06.phx.gbl...
> Hi Greg
> Did you mean xp_cmdshell ? I also missed 'l' in my post
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:%23JKwwjfyHHA.4640@.TK2MSFTNGP03.phx.gbl...
>

File Operations SQL 2000

I have a directory on our SQL server that contains certain temporay
files that have been generated from another application.
I want to periodically clean this directory up by running an SQL job.
How would I go about this?
Essentially, I just want to delete everything from C:\\application_temp
\ *.*
TIA
Hi
xp_cmdshel + DEL commanad . Do you rememeber DOS commands?
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184843844.787726.12240@.k79g2000hse.googlegro ups.com...
>I have a directory on our SQL server that contains certain temporay
> files that have been generated from another application.
> I want to periodically clean this directory up by running an SQL job.
> How would I go about this?
> Essentially, I just want to delete everything from C:\\application_temp
> \ *.*
> TIA
>
|||use xp_cmdshell
eg. exec master.dbo.xp_cmdshell 'dir c:\temp\*.sql'
|||Hi Greg
Did you mean xp_cmdshell ? I also missed 'l' in my post
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%23JKwwjfyHHA.4640@.TK2MSFTNGP03.phx.gbl...
> xp_cmd_shell gives you the ability to perform any os command, such as DEL.
> xp_cmd_shell is turned on by default in SQL 2000 but is turned off by
> default in SQL 2005 so this might / might not be easy in your case.
> xp_dir_tree also lets you traverse a directory to get a list of files
> (much better than using xp_cmd_shell + DIR)
> In SQL 2005, you can also write a CLR procedure with external access that
> can do something similar.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184843844.787726.12240@.k79g2000hse.googlegro ups.com...
>

File Operations SQL 2000

I have a directory on our SQL server that contains certain temporay
files that have been generated from another application.
I want to periodically clean this directory up by running an SQL job.
How would I go about this?
Essentially, I just want to delete everything from C:\\application_temp
\ *.*
TIAHi
xp_cmdshel + DEL commanad . Do you rememeber DOS commands?
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184843844.787726.12240@.k79g2000hse.googlegroups.com...
>I have a directory on our SQL server that contains certain temporay
> files that have been generated from another application.
> I want to periodically clean this directory up by running an SQL job.
> How would I go about this?
> Essentially, I just want to delete everything from C:\\application_temp
> \ *.*
> TIA
>|||use xp_cmdshell
eg. exec master.dbo.xp_cmdshell 'dir c:\temp\*.sql'|||xp_cmd_shell gives you the ability to perform any os command, such as DEL.
xp_cmd_shell is turned on by default in SQL 2000 but is turned off by
default in SQL 2005 so this might / might not be easy in your case.
xp_dir_tree also lets you traverse a directory to get a list of files (much
better than using xp_cmd_shell + DIR)
In SQL 2005, you can also write a CLR procedure with external access that
can do something similar.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"pinhead" <dlynes2005@.gmail.com> wrote in message
news:1184843844.787726.12240@.k79g2000hse.googlegroups.com...
>I have a directory on our SQL server that contains certain temporay
> files that have been generated from another application.
> I want to periodically clean this directory up by running an SQL job.
> How would I go about this?
> Essentially, I just want to delete everything from C:\\application_temp
> \ *.*
> TIA
>|||Hi Greg
Did you mean xp_cmdshell ? I also missed 'l' in my post
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%23JKwwjfyHHA.4640@.TK2MSFTNGP03.phx.gbl...
> xp_cmd_shell gives you the ability to perform any os command, such as DEL.
> xp_cmd_shell is turned on by default in SQL 2000 but is turned off by
> default in SQL 2005 so this might / might not be easy in your case.
> xp_dir_tree also lets you traverse a directory to get a list of files
> (much better than using xp_cmd_shell + DIR)
> In SQL 2005, you can also write a CLR procedure with external access that
> can do something similar.
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> Benchmark your query performance
> http://www.SQLBenchmarkPro.com
> "pinhead" <dlynes2005@.gmail.com> wrote in message
> news:1184843844.787726.12240@.k79g2000hse.googlegroups.com...
>>I have a directory on our SQL server that contains certain temporay
>> files that have been generated from another application.
>> I want to periodically clean this directory up by running an SQL job.
>> How would I go about this?
>> Essentially, I just want to delete everything from C:\\application_temp
>> \ *.*
>> TIA
>|||Hi Uri
No - I actually meant xp_dirtree (i had it as xp_dir_tree previously which
wasn't quite correct)..
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zg%23uoDzHHA.3772@.TK2MSFTNGP06.phx.gbl...
> Hi Greg
> Did you mean xp_cmdshell ? I also missed 'l' in my post
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:%23JKwwjfyHHA.4640@.TK2MSFTNGP03.phx.gbl...
>> xp_cmd_shell gives you the ability to perform any os command, such as
>> DEL. xp_cmd_shell is turned on by default in SQL 2000 but is turned off
>> by default in SQL 2005 so this might / might not be easy in your case.
>> xp_dir_tree also lets you traverse a directory to get a list of files
>> (much better than using xp_cmd_shell + DIR)
>> In SQL 2005, you can also write a CLR procedure with external access that
>> can do something similar.
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> Benchmark your query performance
>> http://www.SQLBenchmarkPro.com
>> "pinhead" <dlynes2005@.gmail.com> wrote in message
>> news:1184843844.787726.12240@.k79g2000hse.googlegroups.com...
>>I have a directory on our SQL server that contains certain temporay
>> files that have been generated from another application.
>> I want to periodically clean this directory up by running an SQL job.
>> How would I go about this?
>> Essentially, I just want to delete everything from C:\\application_temp
>> \ *.*
>> TIA
>>
>

File Manipulation from MS SQL Server

I am working on an application which uses DTS to move data into temporary tables. I would like to be able to rename/relocate the source file in order to maintain a historical reference. The process which creates the source file is not flexible at all. Is there a way to manipulate the file's name and/or relocate the file by using SQL Server.

Thanks in advance!

Daniel
Austin, TexasTry looking into xp_cmdshell stored proc in BOL. This will allow you to use DOS commands from a TSQL script. We had to use this stored proc to rename delimited files we created from a DTS to be exported to a marketing company. Works pretty good.

HTH

DMW|||Hi DMW,

Thank you very much! I will give this a try.

Sincerely,

Daniel
Austin, Texas|||USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(RowNum int IDENTITY(1,1), Data varchar(8000))
GO

INSERT INTO myTable99(Data) EXEC master..xp_cmdshell 'Dir C:\*.*'

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||I use sp_oaxxx with FileSystemObject, because xp_cmdshell will require for a user to have privileges on the target file system.