I need to create maintenance plans on SQL 2005 without using the wizard

I have several hundred SQl installations I need to change or create a maintenance plan and i don't want to touch every box. I need the step by step process to load the tables myself.

If you want to run the maintenance from a single seat, you could create a set of maintenance tasks in Integration Services, and create multiple connections for them. If you want to have the same plan on multiple servers run from each server, I'd create an Integration Services package again and ship it to the other servers. Use (local) as the connection string, assuming the same account can access each server.

Buck Woody

http://www.buckwoody.com

|||

It sounds like you're already familiar with maintenance plans in SQL 2K5, but just in case you're not:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=33&rl=1

Again, if you're a pro on this, my apologies.

Buck Woody

|||Thanks! Thought about the distribution of a services package but that doesn't initilize the maintenance folder in th SQL Enterprise tree. I know that sounds trivial but I got to follow standards.|||If this answers the question, you can mark it "answered". If you need anything else, just let us know!

Buck Woody|||Just in case you're interested in the process, you can import and export you maintenance plans. You can right click on one of your maintenance plans and then select import or export. Export from one, import to the other.

You can also "pull" your maintenance plan to another server. To do this, on the target server connect to Integration Services on the server where you created your original maintenance plan. Then go to Stored Packages - MSDB - Maintenance Plans. Right click on Maintenance Plans and then select Import.

Buck
|||Thanks

I need advanced help please.

Here's one for one of you SQL genuises out there. I really appreciate any help that I can get. There are several steps to this.
Step 1:
I have a database that holds timesheet records for users. These records are by week. I need a query that will return the username of the people that have entered 0 time for all of their timesheets in the database.
Step 2:
I need a query that will delete and timesheets that have 0 time entered after the last timesheet with time entered. If they entered time in week 3 but have 0 time for week 4 and 5 and then are withdrawn at week 5 I need to remove timesheets for week 4 and 5. Note, they could have entered 0 for week 2 as long as week three has time entered.
Step 3: - THIS IS THE MONSTER-
Since the timesheets are by week, I need to find the date that the last time was entered. The timesheet has a start-date then suntime, montime, tuetime, wedtime etc. Sunday would equal the week start-date. So, if time was last entered on Friday, the date that I'm looking for is the start-date + 5. My problem is, How do I go about doing this? I've been trying to do it all in a query to no avail.
I really appreciate any assistance that anyone can offer.
Thank you,
Still learning......
-ScottAll of the below is assuming that Username is unique, and that the"time" columns contain a 0 when there is no time entered. (Ifthey contain a NULL then the ISNULL function will need to beused.) This should hopefully be enough to get you going; Ihaven't tested the code so there may be some syntax errors.

spfeiffer13 wrote:

Step 1:
I have a database that holdstimesheet records for users. These records are by week. Ineed a query that will return the username of the people that haveentered 0 time for all of their timesheets in the database.

SELECT DISTINCT
Username
FROM
yourTable
INNER JOIN
(SELECT UserName, COUNT(*) AS TotalTimeSheetsPerUsername FROMyourTable GROUP BY UserName) AS Totals ON yourTable.Username =Totals.Username
WHERE
suntime+montime+tuetime+wedtime+thutime+fritime+sattime = 0
GROUP BY
yourTable.Username
HAVING
COUNT(*) = Totals.TotalTimeSheetsPerUsername

spfeiffer13 wrote:

Step 2:
I need a query that willdelete and timesheets that have 0 time entered after the last timesheetwith time entered. If they entered time in week 3 but have 0 timefor week 4 and 5 and then are withdrawn at week 5 I need to removetimesheets for week 4 and 5. Note, they could have entered 0 forweek 2 as long as week three has time entered.

DELETE
yourTable
FROM
yourTable
INNER JOIN
(
SELECT
Username,
MAX(StartDate) AS StartDate
FROM
yourTable
WHERE
suntime+montime+tuetime+wedtime+thutime+fritime+sattime = 0
GROUP BY
Username
) AS LastBlankTimesheet ON yourTable.Username =LastBlankTimesheet.Username AND yourTable.StartDate >=LastBlankTimesheet.StartDate

spfeiffer13 wrote:

Step 3: - THIS IS THE MONSTER-
Sincethe timesheets are by week, I need to find the date that the last timewas entered. The timesheet has a start-date then suntime,montime, tuetime, wedtime etc. Sunday would equal the weekstart-date. So, if time was last entered on Friday, the date thatI'm looking for is the start-date + 5. My problem is, How do I goabout doing this? I've been trying to do it all in a query to noavail.

This is what came to mind; maybe someone else will have something more elegant:
SELECT
UserName
MAX(TimesheetDate)
FROM
(
SELECT
UserName,
StartDate AS TimesheetDate,
SunTime AS TimeEntered
FROM
yourTable
UNION ALL
SELECT
UserName,
DATEADD(d,1,StartDate) AS TimesheetDate,
MonTime AS TimeEntered
FROM
yourTable
UNION ALL
SELECT
UserName,
DATEADD(d,2,StartDate) AS TimesheetDate,
TueTime AS TimeEntered
FROM
yourTable
UNION ALL
SELECT
UserName,
DATEADD(d,3,StartDate) AS TimesheetDate,
WedTime AS TimeEntered
FROM
yourTable
UNION ALL
SELECT
UserName,
DATEADD(d,4,StartDate) AS TimesheetDate,
ThuTime AS TimeEntered
FROM
yourTable
UNION ALL
SELECT
UserName,
DATEADD(d,5,StartDate) AS TimesheetDate,
FriTime AS TimeEntered
FROM
yourTable
UNION ALL
SELECT
UserName,
DATEADD(d,6,StartDate) AS TimesheetDate,
SatTime AS TimeEntered
FROM
yourTable
) AS TimesheetDates
WHERE
TimeEntered <> 0
GROUP BY
UserName

|||Thanks for the help. It all worked great except for one thing on Step 3. I keep getting this error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ATTSTARTDT'.
I know that it is probably somehting stupid that I'm doing. I have the code below. Thanks in advance for any help that anyone can offer.
SELECT
schlstuid,
MAX(ATTSTARTDT)
FROM
(
SELECT
schlstuid,
ATTSTARTDT AS TimesheetDate,
sunmns AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
ATTSTARTDT AS TimesheetDate,
sunhrs AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,1,ATTSTARTDT) AS TimesheetDate,
monmns AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,1,ATTSTARTDT) AS TimesheetDate,
monhrs AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,2,ATTSTARTDT) AS TimesheetDate,
tuemns AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,2,ATTSTARTDT) AS TimesheetDate,
tuehrs AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,3,ATTSTARTDT) AS TimesheetDate,
wedmns AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,3,ATTSTARTDT) AS TimesheetDate,
wedhrs AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,4,ATTSTARTDT) AS TimesheetDate,
Thrmns AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,4,ATTSTARTDT) AS TimesheetDate,
Thrhrs AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,5,ATTSTARTDT) AS TimesheetDate,
Frimns AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,5,ATTSTARTDT) AS TimesheetDate,
Frihrs AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,6,ATTSTARTDT) AS TimesheetDate,
Satmns AS TimeEntered
FROM
TimeSheetDailyAttendance
UNION ALL
SELECT
schlstuid,
DATEADD(d,6,ATTSTARTDT) AS TimesheetDate,
Sathrs AS TimeEntered
FROM
TimeSheetDailyAttendance
) AS TimesheetDates
WHERE
TimeEntered <> 0
GROUP BY
schlstuid
--------
I know that this is one long query. Thanks again for any help.
Still learning....
-Scott|||Use MAX(TimesheetDate) instead. When we added days to ATTSTARTDT we used TimesheetDate as the column name.
|||Worked like a charm. Thank you!!!!!!