How to dynamically pull data for the past month?

I have a query that I want to schedule as a DTS package and have it run on
the first of every month to pull data for the previous month. How can I set
the SQL statement to determine what the last month was and use that for the
query parameters?
Thanks in advance for your help!
Isaac WeathersThe last day of the previous month is
select dateadd(dd, -(datepart(dd,getdate()) ), getdate())
I didn't test this but it gets the current day of the month (say the
12th, ) , then subtracts that many days from the current date, leaving you
at the last day of the prior month...You can then take that date and
subtract the the day -1 from that date, giving you the first day of the
month...
--
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
"Isaac Weathers" <Isaac@.DrivenHosting.com> wrote in message
news:ecUJFsOqFHA.156@.TK2MSFTNGP11.phx.gbl...
>I have a query that I want to schedule as a DTS package and have it run on
> the first of every month to pull data for the previous month. How can I
> set
> the SQL statement to determine what the last month was and use that for
> the
> query parameters?
> Thanks in advance for your help!
> Isaac Weathers
>|||'====yyyymm format
if len(Month(DateAdd("M", -1, Date()))) = 1 then
DateString = DatePart("YYYY", DateAdd("M", -1, Date())) & "0" &
Month(DateAdd("M", -1, Date()))
Else
DateString = DatePart("YYYY", DateAdd("M", -1, Date())) &
Month(DateAdd("M", -1, Date()))
End If
'=====mm/dd/yyy format
DateString = Month(DateAdd("M", -1, Date()) ) & "/01/" &
DatePart("YYYY", DateAdd("M", -1, Date()))|||This will do the trick:
DateAdd("m",-1,
CAST(CONVERT(nvarchar(2), Month(GetDate()))+ '/1/' +
CONVERT(nvarchar(4), Year(GetDate()))
AS SmallDateTime))
AS FirstDayOfLastMonth,
CAST(CONVERT(nvarchar(12), GetDate() - Day(GetDate())) AS SmallDateTime)
AS LastDayOfLastMonth,
GeoSynch
"Isaac Weathers" <Isaac@.DrivenHosting.com> wrote in message
news:ecUJFsOqFHA.156@.TK2MSFTNGP11.phx.gbl...
>I have a query that I want to schedule as a DTS package and have it run on
> the first of every month to pull data for the previous month. How can I set
> the SQL statement to determine what the last month was and use that for the
> query parameters?
> Thanks in advance for your help!
> Isaac Weathers
>

How to drop a full-text that references an invalid path

I have restored an SQL database on a different server and the Full-Text
catalogs are referencing the location and full-text indexes of the previous
server.
When trying to drop I receive the error message 15601: "Full-Text Search is
not enabled for the current database. Use sp_fulltext_database to enable
Full-Text Search."
When trying EXEC sp_fulltext_database 'enable', I receive the error message
7610: "Access is denied to 'S:\the-invalid-path', or the path is invalid.
Full-text
search was not installed properly."
I also tried creating the path on the new server, without success: same
error message. How can I get rid of this index?Hi Monica
This may help you to copy the full text catalogues to the new server.
http://support.microsoft.com/kb/240867/EN-US/
You may want to try and droping the catalogues and re-create them.
John
"Monica Rocha" wrote:
> I have restored an SQL database on a different server and the Full-Text
> catalogs are referencing the location and full-text indexes of the previous
> server.
> When trying to drop I receive the error message 15601: "Full-Text Search is
> not enabled for the current database. Use sp_fulltext_database to enable
> Full-Text Search."
> When trying EXEC sp_fulltext_database 'enable', I receive the error message
> 7610: "Access is denied to 'S:\the-invalid-path', or the path is invalid.
> Full-text
> search was not installed properly."
> I also tried creating the path on the new server, without success: same
> error message. How can I get rid of this index?
>|||drop the full text indexes on the tables, then try to drop the catalog. If
this does not word delete the offending entry from sysfulltextcatalogs. Then
create a new catalog.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Monica Rocha" <Monica Rocha@.discussions.microsoft.com> wrote in message
news:C7A986AA-2228-4567-8B68-4358CB75391A@.microsoft.com...
>I have restored an SQL database on a different server and the Full-Text
> catalogs are referencing the location and full-text indexes of the
> previous
> server.
> When trying to drop I receive the error message 15601: "Full-Text Search
> is
> not enabled for the current database. Use sp_fulltext_database to enable
> Full-Text Search."
> When trying EXEC sp_fulltext_database 'enable', I receive the error
> message
> 7610: "Access is denied to 'S:\the-invalid-path', or the path is invalid.
> Full-text
> search was not installed properly."
> I also tried creating the path on the new server, without success: same
> error message. How can I get rid of this index?
>