how to schedule DTS package in sql 2005

Hello,

I am trying to to schedule DTS Package but this message appear .

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (1). and this

Message
Executed as user: Computer name\SYSTEM. The package execution failed. The step failed

so how I can schedule DTS Package in sql 2005 .

Do you have logging enabled for this package? if enabled check the log file it should have more detailed information on the error.

The error you have posted is a very generic error from the job agent, and it does not give any information.

Thanks

|||

Sounds like you might have some issues with the account settings. See the following thread for tips on setting up proxies / credentials / jobs etc.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

|||

Thanks, all

But still the same problem even I did all thing to solve but still can't schedule DTS, but when I see th proparites of the

SQL Server Agent-->Connection and found the sql server connection grayed out and can't select or change it . so may be this is the problem but how to run the agent under sa account .

|||

Assuming you are talking about the connection infromation on the left hand side of the job properties screen, that connection information is how you are currently connected to the sql server. You would need to look at the steps tab and click edit on the step associated with the package you are trying to run. At the top of this screen there will be a place for the step name (i.e. run package x), the type (sql server integration services package) and the run as. You would select the correct proxy name from the run as drop down that ties to your sa account...

|||

Thanks very much for you reply but I still can't schedule the DTS even though when I execute the dts it's work fine and I did all thing what said here http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

|||

I am trying to schedule working DTS in sql server 2005 sp 2 . but I it failed and got this message . I searched at Internet and found this

http://support.microsoft.com/kb/904796

but I can’t understand how to solve it, any one can help please .

Message

Executed as user: ComputrName\Administrator. ...00.3042.00 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.Started:10:16:11 ?Error: 2007-08-17 22:16:12.95Code: 0x00000000Source: Copy Data from ROOM toDBNamedboER_ROOMTaskDescription: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:14.75Code: 0x00000000Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:15.14Code: 0x00000000The package execution fa...The step failed.

|||

how to Reinstall the SQL Server 2000 tools? where to find it .

Still no solution can solve this issue .

how to schedule DTS package in sql 2005

Hello,

I am trying to to schedule DTS Package but this message appear .

Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (1). and this

Message
Executed as user: Computer name\SYSTEM. The package execution failed. The step failed

so how I can schedule DTS Package in sql 2005 .

Do you have logging enabled for this package? if enabled check the log file it should have more detailed information on the error.

The error you have posted is a very generic error from the job agent, and it does not give any information.

Thanks

|||

Sounds like you might have some issues with the account settings. See the following thread for tips on setting up proxies / credentials / jobs etc.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

|||

Thanks, all

But still the same problem even I did all thing to solve but still can't schedule DTS, but when I see th proparites of the

SQL Server Agent-->Connection and found the sql server connection grayed out and can't select or change it . so may be this is the problem but how to run the agent under sa account .

|||

Assuming you are talking about the connection infromation on the left hand side of the job properties screen, that connection information is how you are currently connected to the sql server. You would need to look at the steps tab and click edit on the step associated with the package you are trying to run. At the top of this screen there will be a place for the step name (i.e. run package x), the type (sql server integration services package) and the run as. You would select the correct proxy name from the run as drop down that ties to your sa account...

|||

Thanks very much for you reply but I still can't schedule the DTS even though when I execute the dts it's work fine and I did all thing what said here http://www.codeproject.com/useritems/Schedule__Run__SSIS__DTS.asp

|||

I am trying to schedule working DTS in sql server 2005 sp 2 . but I it failed and got this message . I searched at Internet and found this

http://support.microsoft.com/kb/904796

but I can’t understand how to solve it, any one can help please .

Message

Executed as user: ComputrName\Administrator. ...00.3042.00 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.Started:10:16:11 ?Error: 2007-08-17 22:16:12.95Code: 0x00000000Source: Copy Data from ROOM toDBNamedboER_ROOMTaskDescription: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:14.75Code: 0x00000000Description: System.Runtime.InteropServices.COMException (0x80040427): Execution was canceled by user.at DTS.PackageClass.Execute()at Microsoft.SqlServer.Dts.Tasks.Exec80PackageTask.Exec80PackageTask.ExecuteThread()End ErrorError: 2007-08-17 22:16:15.14Code: 0x00000000The package execution fa...The step failed.

|||

how to Reinstall the SQL Server 2000 tools? where to find it .

Still no solution can solve this issue .

How to run the snapshot agent using the -PublisherLogin ?

I am Merge replicating from MS SQL Server 2000 SP4 to a MS SQL Server 2000
SP3 box.
Get the following error while running the Merge Agent:
Error Message : The process could not log conflict information.
Error Details : The process could not log conflict information. (Source:
Merge Replication Provider (Agent); Error number: -2147200992)
Could not find stored procedure ''.
(Source: <publishing servername> (Data source); Error number: 2812)
On the Microsoft support website
http://support.microsoft.com/default...b;en-us;308743
it says this happens when 'If Merge Replication is configured so that the
Snapshot Agents connect to the Publisher by using a login that is defined as
a member of db_owner (and not a system administrator) in a database that is
merge published, if conflicts are detected during the Merge Process the Merge
Agent fails with this error message'
They offer a workaround which is :
To work around this behavior, run the Snapshot Agent with the
-PublisherLogin parameter and specify a login. For example, use the sa login,
which is a system administrator on the Publisher to generate a new snapshot.
After the snapshot completes, SQL Server creates the conflict stored
procedure. Then, you can rerun the Merge Agent so that SQL Server can log the
conflicts. Because you are not reinitializing the subscription, SQL Server
does not apply the new snapshot to the subscribers.
How do I run the Snapshot Agent with the -PublisherLogin parameter ?
On your publisher server there's a job which creates the snapshot. The
name of the job varies but you can recognize it by the category
REPL-Snapshot.
When you open the properties of the job, go to to Steps tab and edit
the "run agent" step by adding the parameter.
M

How to run a package using T-SQL?

The package is stored on file system with the default property 'EncryptSensitiveWithUserKey'. It is strange that it returns an error message when I use this: xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx" '. I ran it under the account which created the package.

So I wonder if there's any other ways to run the package using SQL?

Why why why, still no answer?|||

From Books Online: "The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. "

Unless your SQL Server Service Account and your user account are the same, SaveSensitiveWithUserKey will not work without some additional steps. You can either set up a proxy account (see "xp_cmdshell" in Books Online), or use a different ProtectionLevel.

|||

Thank you, jwelch.

Is "Windows Authentication" a SQL Server Account? I just choose "Windows Authentication" every time I log in SQL Server.

|||No, Windows Authentication means you are logging in with your network credentials (the user name and password you log in with).|||So that means I cannot run a package unless I create and run it under a SQL Server account?|||No, you should take a look at the ProtectionLevel for your package. Try using SaveSensitiveWithPassword, or use Don'tSaveSensitive and set it with a package configuration.|||Thanks again jwelch, I just dont understand why SaveSensitiveWithUserKey doesn't work when using windows authentication|||

As I said above, xp_cmdshell doesn't run under your network login, it runs under the identity of the service account for SQL Server. SaveSensitiveWithUserKey encrypts passwords, etc using the identity of the network user who created the package. Since those are not the same user, the sensitive data cannot be decrypted.

If you absolutely have to use SaveSensitiveWithUserKey (and the general recommendation on the forum is to use one of the other ProtectionLevels, by the way), you need to set up a proxy for xp_cmdshell that runs under your network login. Unfortunately, that's not an area where I have a lot of experience, so I'll have to refer you to Books Online for additional help on it.

|||Thanks for your suggestion, thanks a lot! Problem resolved!

How to run a package using T-SQL?

The package is stored on file system with the default property 'EncryptSensitiveWithUserKey'. It is strange that it returns an error message when I use this: xp_cmdshell 'dtexec /f "C:\UpsertData.dtsx" '. I ran it under the account which created the package.

So I wonder if there's any other ways to run the package using SQL?

Why why why, still no answer?|||

From Books Online: "The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. "

Unless your SQL Server Service Account and your user account are the same, SaveSensitiveWithUserKey will not work without some additional steps. You can either set up a proxy account (see "xp_cmdshell" in Books Online), or use a different ProtectionLevel.

|||

Thank you, jwelch.

Is "Windows Authentication" a SQL Server Account? I just choose "Windows Authentication" every time I log in SQL Server.

|||No, Windows Authentication means you are logging in with your network credentials (the user name and password you log in with).|||So that means I cannot run a package unless I create and run it under a SQL Server account?|||No, you should take a look at the ProtectionLevel for your package. Try using SaveSensitiveWithPassword, or use Don'tSaveSensitive and set it with a package configuration.|||Thanks again jwelch, I just dont understand why SaveSensitiveWithUserKey doesn't work when using windows authentication|||

As I said above, xp_cmdshell doesn't run under your network login, it runs under the identity of the service account for SQL Server. SaveSensitiveWithUserKey encrypts passwords, etc using the identity of the network user who created the package. Since those are not the same user, the sensitive data cannot be decrypted.

If you absolutely have to use SaveSensitiveWithUserKey (and the general recommendation on the forum is to use one of the other ProtectionLevels, by the way), you need to set up a proxy for xp_cmdshell that runs under your network login. Unfortunately, that's not an area where I have a lot of experience, so I'll have to refer you to Books Online for additional help on it.

|||Thanks for your suggestion, thanks a lot! Problem resolved!

How to Retry Failed Messages with Database Mail

I am using Database Mail on SQL 2005 SP 2. I can query to find message that
were not able to be sent with the query below.
USE msdb
SELECT *
FROM sysmail_faileditems
How can I retry sending these messages?
Hi Jeremy,
I understand that you would like to resend those unsent emails which are
existed in the view of sysmail_faileditems.
If I have misunderstood, please let me know.
Unfortunately SQL Server 2005 does not provide the feature to resend the
failed messages. You need to send a new mail with the same content of the
previous message. Thinking about Outlook, if a messsage was undelivered,
you can receive a response and then if you want to retry sending the
message, you need to compose a new message. Those failed messages might be
thrown or put into another queue of the mail server and they are not
exposed to the clients.
Appreciate your understanding that this is by design and please feel free
to let us know if you have any other questions or concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

How to Retry Failed Messages with Database Mail

I am using Database Mail on SQL 2005 SP 2. I can query to find message that
were not able to be sent with the query below.
USE msdb
SELECT *
FROM sysmail_faileditems
How can I retry sending these messages?Hi Jeremy,
I understand that you would like to resend those unsent emails which are
existed in the view of sysmail_faileditems.
If I have misunderstood, please let me know.
Unfortunately SQL Server 2005 does not provide the feature to resend the
failed messages. You need to send a new mail with the same content of the
previous message. Thinking about Outlook, if a messsage was undelivered,
you can receive a response and then if you want to retry sending the
message, you need to compose a new message. Those failed messages might be
thrown or put into another queue of the mail server and they are not
exposed to the clients.
Appreciate your understanding that this is by design and please feel free
to let us know if you have any other questions or concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

How to Retry Failed Messages with Database Mail

I am using Database Mail on SQL 2005 SP 2. I can query to find message that
were not able to be sent with the query below.
USE msdb
SELECT *
FROM sysmail_faileditems
How can I retry sending these messages?Hi Jeremy,
I understand that you would like to resend those unsent emails which are
existed in the view of sysmail_faileditems.
If I have misunderstood, please let me know.
Unfortunately SQL Server 2005 does not provide the feature to resend the
failed messages. You need to send a new mail with the same content of the
previous message. Thinking about Outlook, if a messsage was undelivered,
you can receive a response and then if you want to retry sending the
message, you need to compose a new message. Those failed messages might be
thrown or put into another queue of the mail server and they are not
exposed to the clients.
Appreciate your understanding that this is by design and please feel free
to let us know if you have any other questions or concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

How to restrict access to database to only IUSR_<machinename>

Hello Mark,
Thanks for your message.
What do you mean " I removed TCP/IP and Named Pipes from the SQL Server
Registration from within Enterprise Manager and rebooted the machine."? Do
you mean you have performed the following steps?
1. Right-click 'MYSERVER\SERVER' in SQL Enterprise Manager(SEM), click
Properties.
2. Click General tab in the Properties window, click Network Configuration.
3. Remove TCP/IP and Named Pipes from the "SQL Server Network Utility."
Please let me know what steps you performed.
If SQL Server service didn't start, SQL Server Agent service will not be
started. Please make sure the SQL Server service has been started. You can
follow the steps below to start SQL Server service:
Click Start > All Programs > SQL Server > Service Manager > start SQL
Server service in Service Manager > start SQL Server Agent service in
Service Manager
If you still are unable to start the SQL Server Agent service, please
follow the steps below to attempt to start the SQL Server Agent Services
from a command line, then send the error log files to me.
1. Open a command line window.
2. Run the following command in the command line window:
"C:\Program Files\Microsoft SQL Server\<instance name>\Binn\sqlagent" -c -v
Note: Above command is just a sample. You need to replace the directory
"C:\Program Files\Microsoft SQL Server\<instance name>\ with the real
directory which you installed the SQL Server.
3. Compress all the log files under the directory "C:\Program
Files\Microsoft SQL Server\<instance name>\LOG" and send it to me for
research.
In addition, you can open "SQL Server Network Utility." by clicking Start >
All Programs > SQL server > SQL Server Network Utility, then you can
re-enable TCP/IP and Named Pipes in the SQL Server Network Utility.
If anything is unclear, get in touch.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.>>Do you mean you have performed the following steps?
Yes, that is correct. When I did that, and rebooted the machine, the
SQLServerAgent would not start. (It would start, then immediately stop).
Therefore I could not open my database using Enterprise Manager.
NOTE: I had also set the 'Hide Server' switch, and suspect this may have
been part of the problem as well.
SOLUTION: I went into the registry under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\MIRACLECAT\MSSQLServer\SuperSocke
tNetLib\Tcp and set the TcpHideFlag
back to zero and then rebooted and all worked again.
I think I may just depend on Windows Server 2003 blocking post 1433 (SQL
Server Port) and let it go at that since temporarily losing my SQL databases
put quite a scare into me
Thanks for all your help however.
Mark
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:HCsqCPf%23EHA.3360@.cpmsftngxa10.phx.gbl...
> Hello Mark,
> Thanks for your message.
> What do you mean " I removed TCP/IP and Named Pipes from the SQL Server
> Registration from within Enterprise Manager and rebooted the machine."?
> Do
> you mean you have performed the following steps?
> 1. Right-click 'MYSERVER\SERVER' in SQL Enterprise Manager(SEM), click
> Properties.
> 2. Click General tab in the Properties window, click Network
> Configuration.
> 3. Remove TCP/IP and Named Pipes from the "SQL Server Network Utility."
> Please let me know what steps you performed.
> If SQL Server service didn't start, SQL Server Agent service will not be
> started. Please make sure the SQL Server service has been started. You can
> follow the steps below to start SQL Server service:
> Click Start > All Programs > SQL Server > Service Manager > start SQL
> Server service in Service Manager > start SQL Server Agent service in
> Service Manager
> If you still are unable to start the SQL Server Agent service, please
> follow the steps below to attempt to start the SQL Server Agent Services
> from a command line, then send the error log files to me.
> 1. Open a command line window.
> 2. Run the following command in the command line window:
> "C:\Program Files\Microsoft SQL Server\<instance
> name>\Binn\sqlagent" -c -v
> Note: Above command is just a sample. You need to replace the directory
> "C:\Program Files\Microsoft SQL Server\<instance name>\ with the real
> directory which you installed the SQL Server.
> 3. Compress all the log files under the directory "C:\Program
> Files\Microsoft SQL Server\<instance name>\LOG" and send it to me for
> research.
>
> In addition, you can open "SQL Server Network Utility." by clicking Start
> All Programs > SQL server > SQL Server Network Utility, then you can
> re-enable TCP/IP and Named Pipes in the SQL Server Network Utility.
> If anything is unclear, get in touch.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>