Run a DTS Package "in the background" or with low priority?

Configuration:
Windows 2000
Sql Server 2000
Quad CPU
Problem:
I have a DTS update operation that takes many hours to run. The
process is monthly, and for that process speed is not an issue; however,
the database server hosts multiple databases, some with applications
that do interactive queries. The DTS Update step brings down the speed
of interactive queries on the database being updated and all other
databases dramatically.
Is there any way to lower the priority of a DTS package operation so
that it does not impact interactive queries on that databases and other
databases running on the server?
Basically, can I run a DTS package "in the background" or with a very
low priority?
Hi
You may want to read The Guru's Guide to SQL Server Architecture and
Internals by Ken Henderson ISDN 0-201-70047-6 which goes into depth on
thread scheduling. You may get some benefit by setting the MAXDOP query hint
on some of your statements. Highlighting what part of your process is slow
and why it is slow may help you to provide a more efficient solution.
You may also want to review the architecture of this process and either
split it into smaller independent chunks that can be run separately, or if
the data created by this process is read-only to the other processes, then
you may want to look at doing it offline and then swapping it in when the
process is complete e.g. having it all in a separate read-only database.
John
"John Bailo" <jabailo@.texeme.com> wrote in message
news:gsudneQq3ZlEYAzeRVn-uw@.speakeasy.net...
> Configuration:
> Windows 2000
> Sql Server 2000
> Quad CPU
>
> Problem:
> I have a DTS update operation that takes many hours to run. The process
> is monthly, and for that process speed is not an issue; however,
> the database server hosts multiple databases, some with applications that
> do interactive queries. The DTS Update step brings down the speed of
> interactive queries on the database being updated and all other databases
> dramatically.
> Is there any way to lower the priority of a DTS package operation so that
> it does not impact interactive queries on that databases and other
> databases running on the server?
> Basically, can I run a DTS package "in the background" or with a very low
> priority?
>
>

Run a DTS Package "in the background" or with low priority?

Configuration:
Windows 2000
Sql Server 2000
Quad CPU
Problem:
I have a DTS update operation that takes many hours to run. The
process is monthly, and for that process speed is not an issue; however,
the database server hosts multiple databases, some with applications
that do interactive queries. The DTS Update step brings down the speed
of interactive queries on the database being updated and all other
databases dramatically.
Is there any way to lower the priority of a DTS package operation so
that it does not impact interactive queries on that databases and other
databases running on the server?
Basically, can I run a DTS package "in the background" or with a very
low priority?Hi
You may want to read The Guru's Guide to SQL Server Architecture and
Internals by Ken Henderson ISDN 0-201-70047-6 which goes into depth on
thread scheduling. You may get some benefit by setting the MAXDOP query hint
on some of your statements. Highlighting what part of your process is slow
and why it is slow may help you to provide a more efficient solution.
You may also want to review the architecture of this process and either
split it into smaller independent chunks that can be run separately, or if
the data created by this process is read-only to the other processes, then
you may want to look at doing it offline and then swapping it in when the
process is complete e.g. having it all in a separate read-only database.
John
"John Bailo" <jabailo@.texeme.com> wrote in message
news:gsudneQq3ZlEYAzeRVn-uw@.speakeasy.net...
> Configuration:
> Windows 2000
> Sql Server 2000
> Quad CPU
>
> Problem:
> I have a DTS update operation that takes many hours to run. The process
> is monthly, and for that process speed is not an issue; however,
> the database server hosts multiple databases, some with applications that
> do interactive queries. The DTS Update step brings down the speed of
> interactive queries on the database being updated and all other databases
> dramatically.
> Is there any way to lower the priority of a DTS package operation so that
> it does not impact interactive queries on that databases and other
> databases running on the server?
> Basically, can I run a DTS package "in the background" or with a very low
> priority?
>
>

Run a DTS Package "in the background" or with low priority?

Configuration:
Windows 2000
Sql Server 2000
Quad CPU
Problem:
I have a DTS update operation that takes many hours to run. The
process is monthly, and for that process speed is not an issue; however,
the database server hosts multiple databases, some with applications
that do interactive queries. The DTS Update step brings down the speed
of interactive queries on the database being updated and all other
databases dramatically.
Is there any way to lower the priority of a DTS package operation so
that it does not impact interactive queries on that databases and other
databases running on the server?
Basically, can I run a DTS package "in the background" or with a very
low priority?Hi
You may want to read The Guru's Guide to SQL Server Architecture and
Internals by Ken Henderson ISDN 0-201-70047-6 which goes into depth on
thread scheduling. You may get some benefit by setting the MAXDOP query hint
on some of your statements. Highlighting what part of your process is slow
and why it is slow may help you to provide a more efficient solution.
You may also want to review the architecture of this process and either
split it into smaller independent chunks that can be run separately, or if
the data created by this process is read-only to the other processes, then
you may want to look at doing it offline and then swapping it in when the
process is complete e.g. having it all in a separate read-only database.
John
"John Bailo" <jabailo@.texeme.com> wrote in message
news:gsudneQq3ZlEYAzeRVn-uw@.speakeasy.net...
> Configuration:
> Windows 2000
> Sql Server 2000
> Quad CPU
>
> Problem:
> I have a DTS update operation that takes many hours to run. The process
> is monthly, and for that process speed is not an issue; however,
> the database server hosts multiple databases, some with applications that
> do interactive queries. The DTS Update step brings down the speed of
> interactive queries on the database being updated and all other databases
> dramatically.
> Is there any way to lower the priority of a DTS package operation so that
> it does not impact interactive queries on that databases and other
> databases running on the server?
> Basically, can I run a DTS package "in the background" or with a very low
> priority?
>
>

run a DTS

Hi,

I am trying to run a DTS that export the table to a different server as an excel file.

In the DTS I specified UNC path for the file to be exported.

Now, I login the SQL Server with a network user MyUser and run the package manually and I see the excel file is created in the remote server. I want to do the same thing with a SQL Agent Job task. I created a stored procedure and put my DTSRun all in it as follows;

dtsrun /S MyServer /U MyUSer /P MyPass /N MyDTS . I also create the job and make the owner and “RunAs” MyUser and run the task I get the following error. Apparently since MyUser is a network user, it could not login to SQL Server as I defined in my DTSRun. Is there any way I can get this DTS run under that user without changing SQL Agent user which is running under a system user.

Error:

DTSRun:Loading...

Error:-2147217843 (80040E4D); Provider Error:18456 (4818)

Error string:Login failed for user ‘MyUSer’.

Error source:Microsoft OLE DB Provider for SQL Server

Help file:

Help context:0

(null)

First make a login for your MyUser windows account and assign it to the sysadmin role - make sure that MyUser is a local admin on the server that SQL is running on. Then create a Proxy account for the SQL Server (right click on the agent and select properties, then go the the Job System) use MyUser as the proxy account. Look at SQL Server Agent Properties (Job System Tab) on BOL for more information.

Brad

run a DTS

Hi,

I am trying to run a DTS that export the table to a different server as an excel file.

In the DTS I specified UNC path for the file to be exported.

Now, I login the SQL Server with a network user MyUser and run the package manually and I see the excel file is created in the remote server. I want to do the same thing with a SQL Agent Job task. I created a stored procedure and put my DTSRun all in it as follows;

dtsrun /S MyServer /U MyUSer /P MyPass /N MyDTS . I also create the job and make the owner and “RunAs” MyUser and run the task I get the following error. Apparently since MyUser is a network user, it could not login to SQL Server as I defined in my DTSRun. Is there any way I can get this DTS run under that user without changing SQL Agent user which is running under a system user.

Error:

DTSRun: Loading...

Error: -2147217843 (80040E4D); Provider Error: 18456 (4818)

Error string: Login failed for user ‘MyUSer’.

Error source: Microsoft OLE DB Provider for SQL Server

Help file:

Help context: 0

(null)

No but you can clone your account to run the Agent with the proxy account, try the thread below for details. If it did not run start over it works. Hope this helps.

http://forums.asp.net/thread/1358665.aspx

run a DTS

Hi,

I am trying to run a DTS that export the table to a different server as an excel file.

In the DTS I specified UNC path for the file to be exported.

Now, I login the SQL Server with a network user MyUser and run the package manually and I see the excel file is created in the remote server. I want to do the same thing with a SQL Agent Job task. I created a stored procedure and put my DTSRun all in it as follows;

dtsrun /S MyServer /U MyUSer /P MyPass /N MyDTS . I also create the job and make the owner and “RunAs” MyUser and run the task I get the following error. Apparently since MyUser is a network user, it could not login to SQL Server as I defined in my DTSRun. Is there any way I can get this DTS run under that user without changing SQL Agent user which is running under a system user.

Error:

DTSRun:Loading...

Error:-2147217843 (80040E4D); Provider Error:18456 (4818)

Error string:Login failed for user ‘MyUSer’.

Error source:Microsoft OLE DB Provider for SQL Server

Help file:

Help context:0

(null)

First make a login for your MyUser windows account and assign it to the sysadmin role - make sure that MyUser is a local admin on the server that SQL is running on. Then create a Proxy account for the SQL Server (right click on the agent and select properties, then go the the Job System) use MyUser as the proxy account. Look at SQL Server Agent Properties (Job System Tab) on BOL for more information.

Brad

run a dos command?

Hi all,
I am just wondering is there a way to run a dos command in the stored procedure? Please fill me in on how to go about doing if is there is a way. If this is not the correct forum to post this question, please let me know. Thanks in advance.
Daren

Hi faren,

you can open up a shell with XP_cmdshell and execute commands via passing the command as a paramter:

EXEC MASTER.XP_CMDSHELL 'dir C:\'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de