Automating deployment of maintenance plans

Hello,

I have created a Maintenance Plan on our development SQL Server 2005 Standard using the designer in SQL Server Management Studio. The plan backs up databases and transaction logs to a hard disk and does some cleanup too. It is scheduled to run nightly. This plan needs to be deployed to 13 production sites by someone else not familiar with SQL Server.

Can I use some combination of a SQL script, an export of the maintenance plan, and/or a batch file to automate the deployment of this plan and it's schedule to servers at several different sites? The deployment team will have admin remote desktop access to the production SQL Servers, which also have SQL Management Studio installed but we cannot expect the team to recreate the plan manually on each site.

I haven't been able to find much documentation on doing this automatically. Any help will be appreciated.

Thank you,

- Jason

Create a SSIS package to perform this maintenance plan task and use DTUTIL to deploy on multiple servers.

http://www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx#ERGAE fyi.

sql

Automating a time-sensitive query

My company uses MS Retail Management System, which is SQL-based. Without
going into a lot of detail about RMS, here's what I need to do: schedule a
query that will insert a new row into an existing table. Part of the data
that needs to be inserted is date/time. For example, here's the query I
need to run:
INSERT INTO Worksheet (Style, EffectiveDate, Status, Notes, Title, FromDate)
VALUES (250,Today 6:30PM,2,'Auto 250','Automated Daily Item Update',NOW)
What I don't know how to do:
Generate the date fields "Today 6:30PM" and "NOW"
Schedule the query.
I've got SQL 2000 (as part of SBS Premium), and I am certain that I have the
capability to do this. I think I have figured out the scheduling part, but
I'm hesitant to try it out on a live database. In fact, I think I'll try a
simpler query on a sample database before I ever go live with it.
Just to be perfectly clear, I'm a whole lot closer to being an SQL newbie
than an SQL expert.
Thanks for any advice you might offer,
Tom
--
Stop Fishing For e-MailThe function getdate() returns the current date/time, and jobs are the
standard method of scheduling a query, stored procedure, or package to
execute at a specific time or at intervals.
"Terrible Tom" <tomg@.gofish.robysfurniture.com> wrote in message
news:eHO$g55QGHA.1772@.TK2MSFTNGP14.phx.gbl...
> My company uses MS Retail Management System, which is SQL-based. Without
> going into a lot of detail about RMS, here's what I need to do: schedule
> a query that will insert a new row into an existing table. Part of the
> data that needs to be inserted is date/time. For example, here's the
> query I need to run:
> INSERT INTO Worksheet (Style, EffectiveDate, Status, Notes, Title,
> FromDate)
> VALUES (250,Today 6:30PM,2,'Auto 250','Automated Daily Item Update',NOW)
> What I don't know how to do:
> Generate the date fields "Today 6:30PM" and "NOW"
> Schedule the query.
> I've got SQL 2000 (as part of SBS Premium), and I am certain that I have
> the capability to do this. I think I have figured out the scheduling
> part, but I'm hesitant to try it out on a live database. In fact, I think
> I'll try a simpler query on a sample database before I ever go live with
> it.
> Just to be perfectly clear, I'm a whole lot closer to being an SQL newbie
> than an SQL expert.
> Thanks for any advice you might offer,
> Tom
> --
> Stop Fishing For e-Mail
>|||> INSERT INTO Worksheet (Style, EffectiveDate, Status, Notes, Title,
> FromDate)
> VALUES (250,Today 6:30PM,2,'Auto 250','Automated Daily Item Update',NOW)
> What I don't know how to do:
> Generate the date fields "Today 6:30PM" and "NOW"
> Schedule the query.
DECLARE @.eff SMALLDATETIME, @.from SMALLDATETIME;
SET @.from = CURRENT_TIMESTAMP;
SET @.eff = DATEADD(MINUTE, 30, DATEADD(HOUR, 18, DATEADD(DAY, 0,
DATEDIFF(DAY, 0, @.from))));
INSERT WorkSheet
(
Style,
EffectiveDate,
Status,
Notes,
Title,
FromDate
)
SELECT
250,
@.eff,
2,
'Auto 250',
'Automated Daily Item Update',
@.from;

> I'm hesitant to try it out on a live database. In fact, I think I'll try
> a simpler query on a sample database before I ever go live with it.
Never a bad idea. We test our code on three non-essential environments
before live clients ever hear about it.
A|||Use the ANSI/ISO Standard CURRENT_TIMESTAMP instead of NOW or the old
proprietary getdate().
.|||Why is it called a time "stamp" ?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141927581.261444.223900@.v46g2000cwv.googlegroups.com...
> Use the ANSI/ISO Standard CURRENT_TIMESTAMP instead of NOW or the old
> proprietary getdate().
> .
>

automatically expand identity specification node?

subject says it all -- is it possible to automatically expand the identity specification node in table properties for the Management Studio or VS2005 diagram mode? when creating a DB, it seems ridiculous that for EVERY table I have to add an extra click to get to just one more clickable item that ought to be exposed by default.Yeah, that's kind of a pain. If you have a lot of design to do at once, it's easier to use T-SQL than the GUI tools. I don't know of a method to auto-expand that.|||

thx for the reply ... even if it's a year later

sure, T-SQL would be easier for the identity aspect ... but when I'm modelling a DB, it's too organic of a process to do in script. One of SQL Server's strengths has always been the DB modeller. I don't need to create an ERD and work off of that because the SQL Server diagram IS my initial ERD. Implementing ideas visually at the conceptual stage of DB design is critical IMO. Making the identiy attribute more accessible, whether by default, by choice, or by rearranging that portion of the GUI, would be a big value add, again, IMO ...

|||Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.|||

Buck Woody - MSFT wrote:

Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.

Where at? Connections?

|||That's right - here is the link:

http://connect.microsoft.com/SQLServer

|||thanks for the link, hadn't used Connect in a while.

automatically expand identity specification node?

subject says it all -- is it possible to automatically expand the identity specification node in table properties for the Management Studio or VS2005 diagram mode? when creating a DB, it seems ridiculous that for EVERY table I have to add an extra click to get to just one more clickable item that ought to be exposed by default.Yeah, that's kind of a pain. If you have a lot of design to do at once, it's easier to use T-SQL than the GUI tools. I don't know of a method to auto-expand that.|||

thx for the reply ... even if it's a year later

sure, T-SQL would be easier for the identity aspect ... but when I'm modelling a DB, it's too organic of a process to do in script. One of SQL Server's strengths has always been the DB modeller. I don't need to create an ERD and work off of that because the SQL Server diagram IS my initial ERD. Implementing ideas visually at the conceptual stage of DB design is critical IMO. Making the identiy attribute more accessible, whether by default, by choice, or by rearranging that portion of the GUI, would be a big value add, again, IMO ...

|||Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.|||

Buck Woody - MSFT wrote:

Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.

Where at? Connections?

|||That's right - here is the link:

http://connect.microsoft.com/SQLServer

|||thanks for the link, hadn't used Connect in a while.sql

automatically expand identity specification node?

subject says it all -- is it possible to automatically expand the identity specification node in table properties for the Management Studio or VS2005 diagram mode? when creating a DB, it seems ridiculous that for EVERY table I have to add an extra click to get to just one more clickable item that ought to be exposed by default.Yeah, that's kind of a pain. If you have a lot of design to do at once, it's easier to use T-SQL than the GUI tools. I don't know of a method to auto-expand that.|||

thx for the reply ... even if it's a year later

sure, T-SQL would be easier for the identity aspect ... but when I'm modelling a DB, it's too organic of a process to do in script. One of SQL Server's strengths has always been the DB modeller. I don't need to create an ERD and work off of that because the SQL Server diagram IS my initial ERD. Implementing ideas visually at the conceptual stage of DB design is critical IMO. Making the identiy attribute more accessible, whether by default, by choice, or by rearranging that portion of the GUI, would be a big value add, again, IMO ...

|||Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.|||

Buck Woody - MSFT wrote:

Have you gone to the Microsoft site to make that suggestion? They have a place where they rank them.

Where at? Connections?

|||That's right - here is the link:

http://connect.microsoft.com/SQLServer

|||thanks for the link, hadn't used Connect in a while.

Automatic Range Management - I give up

I have searched through this NG as well as searched BOL concerning this
process called Automatic Range Management, which is suggested as an easy way
to handle reseeding of the identity values. But I have no idea how to set up
ARM. I looked through the properties of my publications and subscriptions
and couldn't find anything for ARM. Nor do the wizards reveal any setting
for ARM.
So, howya do it?
Roger,
in the publication properties, on the articles tab, click on the elipsis
button which gives access to the article properties. There is a tab for
identities, and a checkbox at the top for automatic range management.
Assuming you haven't yet subscribed, it won't be greyed out and you can then
set the size of the range etc.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||THANK YOU!!!!!!
"Paul Ibison" wrote:

> Roger,
> in the publication properties, on the articles tab, click on the elipsis
> button which gives access to the article properties. There is a tab for
> identities, and a checkbox at the top for automatic range management.
> Assuming you haven't yet subscribed, it won't be greyed out and you can then
> set the size of the range etc.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Automatic Identity Range Management

Hello,
I've got a question dealing with automatic identity range management.
Currently we have a database setup for publication in which the subscribers
are running SQL Server CE on mobile devices. We've got automatic identity
range management turned on for a few of our tables and we're having some
issues with the publisher identity ranges not getting automatically
reassigned after they are exhausted. I found this statement in the SQL Server
manuals:
"If the Publisher exhausts its identity range after an insert, it can
automatically assign a new range if the insert was performed by a member of
the db_owner fixed database role."
The user that is inserting the rows into the publisher database is a member
of the db_owner fixed database role but when a row is inserted which exhausts
the currenty identity range they are not automatically reassigned. I can
manually adjust the identity ranges using sp_adjustpublisheridentityrange
logged in with the same user, but they are not automatically adjusted after
the insert as the documentation suggests.
What else should we check for?
Thanks in advance,
mike...
You have to size the ranges for the max amount of inserts that would occur
between syncs. Otherwise the range will not be adjusted.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Mike..." <Mike@.discussions.microsoft.com> wrote in message
news:5D34048F-05C2-4502-B295-C10AB89AF4BE@.microsoft.com...
> Hello,
> I've got a question dealing with automatic identity range management.
> Currently we have a database setup for publication in which the
> subscribers
> are running SQL Server CE on mobile devices. We've got automatic identity
> range management turned on for a few of our tables and we're having some
> issues with the publisher identity ranges not getting automatically
> reassigned after they are exhausted. I found this statement in the SQL
> Server
> manuals:
> "If the Publisher exhausts its identity range after an insert, it can
> automatically assign a new range if the insert was performed by a member
> of
> the db_owner fixed database role."
> The user that is inserting the rows into the publisher database is a
> member
> of the db_owner fixed database role but when a row is inserted which
> exhausts
> the currenty identity range they are not automatically reassigned. I can
> manually adjust the identity ranges using sp_adjustpublisheridentityrange
> logged in with the same user, but they are not automatically adjusted
> after
> the insert as the documentation suggests.
> What else should we check for?
> Thanks in advance,
> mike...
>
|||Hilary, Thanks for the reply - the problem we've run into is that the user
accounts (Windows Auth) sync'ing the devices are not members of the db_owner
role so the merge agents do not refresh the identity ranges (which is another
issues we've been attempting to work around). From our experiences (and from
reading the SQL Server manuals) the publisher identity ranges should refresh
themselves if the inserts are done at the publisher by users in the db_owner
fixed database role. This isn't occuring at one of our sites and I'm
wondering what other security arrangements need to be made for this to occur.
Any ideas?
Thanks again,
mike...
"Hilary Cotter" wrote:

> You have to size the ranges for the max amount of inserts that would occur
> between syncs. Otherwise the range will not be adjusted.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Mike..." <Mike@.discussions.microsoft.com> wrote in message
> news:5D34048F-05C2-4502-B295-C10AB89AF4BE@.microsoft.com...
>
>

Auto-Increasement field?

Hi!

I'm using Microsoft SQL Server Management Studio to design a table with two fields:

id (int)

file (text)

I set 'id' to be primary. I try to add a row to this table but it asks me for a custom value for 'id'. I want it simply to auto-assign a uniqe value for it. How to do this please?

In the table designer, set the Identity Specification to Is_Identity = Yes.

Also, I recommend NOT using [ID] as the column name. A good standard is to use the TableName and ID, so a table named MyTable would have it's IDENTITY column named MyTableID.

Auto reminder emails?

I don't know much about broker service so I have question. We have a content management system the we developed locally, and what we have are catagories and subcatagries. When people choose a topic we send them an email about the topic they are interested in. We are now selling webinars and white papers and articles on demand. We would like to be able to send a reminder email to anyone who signed up for a webinar or special event. Is this possible with broker service?While you could use SSB to build your fan-out and scheduling for the mails, it will not be capable of actually delivering the mails to the users, since it can only send messages between two SQL Server instances. The Database mail functionality in SQL can achieve that though (see http://msdn2.microsoft.com/en-us/library/ms175887.aspx)|||So I can use service broker to send the email to the dbmail engine and than to the customers right?|||Yes, but db mail engine already does that (sp_senddbmail uses Service Broker to communicate with the mailing engine)

Auto generated CRUD in Sql 2005 issue

Here is our problem. If you right click on a table in Management studio it gives you the option of creating The Delete, insert, select and Update stored procedures for any table. The problem is that the auto generation script includes the database name in the stored procedures. So if we have a database called DB_DEV and we move the stored procedures over to database DB_QA these stored procedures are now trying to access the wrong database. Is there a way to make sure that the database name is not included?

If I am reproducing your steps correctly, I see that SSMS will 'auto-magically' write a query for one of the CRUD actions -but it's not a stored procedure.

You may wish to combine that action with using a Stored Procedure template -but as far as I can determine, you'll have to manually remove the dbname.

|||

Could you please post the script you see, and the version of SQL Server you are using?

You can try it on a simple table and not necessary your primary one.

When I try to reproduce your problem, the CRUD script created has a "use [<dbname>]" at the beginning of it. If this is the case for you, you can simply remove this line from the script and it will be applicable to any database.

|||Why not use Edit / Find and Replace after you generate the script?