DTS and DBCC

Question 1:

When I setup DTS package, what kind of privilege I need to get.??
Only SA can setup or there is other server privilage can be add.

Question 2:

How do I setup a database maintain job like ( backup, DBCC reindex, update statistics ) , using DTS or JOBs ? it there any suggest scripts?

Thanks in advance.

GY1) In general DTS package contains one or more steps that are executed sequentially or in parallel when the package is run. When executed, the package connects to the correct data sources, copies data and database objects, transforms data, and notifies other users or processes of events. Packages can be edited, password protected, scheduled for execution, and retrieved by version. So normal user privileges are enough to accomplish the task and it depends purely on level of privilege for that user on the database.

Then coming to DTS security, if you set an owner password, the package user needs the password to edit or run the package. If you set a user password, you also must set an owner password.

2) You can setup Maintenance Jobs to perform backup, DBCC checks and optimization tasks, from Enterprise Manager goto Management and there you can find DB Maint.plan to setup.

By all means refer to BOOKS ONLINE for more information.|||Thanks Satya.

That means any use can create DTS package only if he or her has the privilege for the database objects ( tables, views , etc). SA is not the only id we can create DTS

GY|||1. Why not try it and see. Any user can create a package - remember dts is a client utility which can connect to sql server.
It's usually easier to run as sa though.

2. You can schedule a scheduled job with t-sql tasks and put any statement(s) in it you wish. Usually better to put individual operations as separate steps or call an SP to do them.
I'm not a fan of maintenance plans - would rather code the steps myself.|||True, whatever referred by Nigel (Thanks).
It works as my developers and support staff can do it.
With DTS you can do it... :)|||Thanks nigelrivett and satya

DTCXact transaction in DBCC openTran

I keep seeing this return from running a DBCC OpenTran:

Transaction information for database 'Live_App'.

Oldest active transaction:
SPID (server process ID) : 92
UID (user ID) : 1
Name : DTCXact
LSN : (12837:1924:1)
Start time : Oct 4 2004 8:54:03:570AM
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

I don't see anywhere in code that begins a transaction with the name
DTCXact explicitly. Is this a generic name for any transaction that is
opened without an explicit name? The problem I am having with this is
that sometime it will start and may not get commited or rolledback for
quite some time. I have seen it remain for over 1 1/2 hours before.
Would that be caused by the application not cleaning it up?
Your help in explaining the source of this will be appreciated. I did
find an entry on Microsoft.com that used the word DTCXact. It was
talking about Transaction Propagation from Resource Manager To
Application. I'm not sure if this applies to what I am seeing here or
not.

Thank you.
Kalvin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Kalvin" <ktXXDelMeXXuel@.streck.com> wrote in message
news:4161599e$0$26142$c397aba@.news.newsgroups.ws.. .
> I keep seeing this return from running a DBCC OpenTran:
> Transaction information for database 'Live_App'.
> Oldest active transaction:
> SPID (server process ID) : 92
> UID (user ID) : 1
> Name : DTCXact
> LSN : (12837:1924:1)
> Start time : Oct 4 2004 8:54:03:570AM
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> I don't see anywhere in code that begins a transaction with the name
> DTCXact explicitly. Is this a generic name for any transaction that is
> opened without an explicit name? The problem I am having with this is
> that sometime it will start and may not get commited or rolledback for
> quite some time. I have seen it remain for over 1 1/2 hours before.
> Would that be caused by the application not cleaning it up?
> Your help in explaining the source of this will be appreciated. I did
> find an entry on Microsoft.com that used the word DTCXact. It was
> talking about Transaction Propagation from Resource Manager To
> Application. I'm not sure if this applies to what I am seeing here or
> not.
> Thank you.
> Kalvin
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

It looks like a distributed query, ie. a query or stored procedure being
executed on another server via a linked server (DTC is the Distributed
Transaction Coordinator service). You can use DBCC INPUTBUFFER,
fn_get_sql(), or Profiler to see exactly what the SPID is doing.

Simon