scheduler brings down the system?

I am hoping this is something simple, a security piece I've messed up.

When I run my dts package manually I get no errors and sucessful completion of each step. So I set my SQL Agent to run thepackage 15 minutes after every hour between 5am and 5pm, these being 15 minutes after the times the new data files are dropped to me.

But instead of running the package it disconnects all the remote desktop users and fails. When remote users can get back on we are faced with a system has recovered from a serious error message. The error message posted to the event logs is empty though.

Any one have any ideas?

We are running MS Server 2003 and SQL Server 2000

Thanks,

Carrie

It is not clear what is happening. When you say remote desktop users are you referring to RDP connections or Terminal Server sessions to your Windows Server. If so then it is not clear how this is a SQL Server issue.

If you are referring to SQL Server users then does the SQL Server service crash when you run the package?

Did you check the SQL Server error logs, windows eventlogs and SQL Server log directory for dump files?

Can you also repro the problem on another SQL Server with your database and package? If it is specific to this server, did you check for database corruptions or other hardware related issues?

Do you have the latest service pack installed on the SQL Server? If not, you should install it on a repro system and test your package again. If the problem doesn't happen then the bug you encountered has been fixed in a later service pack or hotfix.

If you are running the latest service pack and you can repro this problem on other SQL Servers with same package/database then it is a bug in SQL Server. You can file one with the repro details at http://connect.microsoft.com.

Scheduled sql cannot access the network drives?

I have a stored procedure performing several tsql operations with MS
Access and Excel files stored into the network file system, and
therefore using OpenDataSouce with these files in the network,
accessible through mapped network drives.

My user has a profile that maps the necessary network drives at boot.
SQL Server runs on the server with an account created for that. This
accont has also a profile mapping the necessary network drives.

The stored procedure runs fine only if I launch it with SQL Server
Management Studio on the server, logging in directly on the server (at
this point the network drives are mapped correctly).

If I launch the Stored Procedure with SQL Server Management Studio on
my client, it doesn't find the network drives and fails.

The same happens when I schedule the stored procedure on the server
with SQL Server Agent, which runs with an account performing the
mapping in its profile.

Any ideas how to make it work?

A suggestion in common, don′t use mapped network driver, use UNC paths !

If you want to use ressources within the network you will need appropiate permissions either for the service account SQL Server Service is running with or the service account SQL Server Agent is running with (if you do that within a scheduled job). An alternative would be to setup a proxy account for the service [1]

[1] http://www.databasejournal.com/features/mssql/article.php/1580041

In SQL Server 2005 proxy accounts can also be configured through the Configuration interface which you will find in the program collection.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

Scheduled package failed but still shows as running job

i had scheduled to run a package it failed but a part of the package still shows up on the integration system running packages. i tried to stop it or delete it nothing works. when i try toi run the package even in visual studio that part of the package fails.

how do i remove the running package?

Thanks

jas

Have you clicked Refresh in Management Studio? The list of running packages does not auto-refresh.|||

Thanks for the reply.

I tried refreshing nothing worked....but anyway i tried shuting down the sql server and bring it again it worked.

The wierd thing was, when it was hanging it was not even able to execute it in my designer.

Thanks again

jas

Scheduled Jobs

Its not possible to schedule jobs via Express is it? Like I have a process that connects to an Oracle system grabs the data... plays with it some and then inserts it into the MSSQL 2005 server... We need this process to run say every 2 hours... Express cant do that cant it?SQL Express has no SQL Agent job scheduling service. You can execute the appropiate jobs using SQLCMD and the AT or any other scheduling service on your machine.

See the feature comparison here: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

hi,

in addition to Jens answer, you can perhaps have a look at "third party" solutions, both commercial and free, like http://www.valesoftware.com/products-express-agent.php and http://www.codeproject.com/useritems/SQLAgent.asp to implement scheduled features..

regards

|||another possible solution (thats free) is to create a small batch script that calls sqlcmd and does your sql processing. this batch file could then be scheduled using windows scheduling to be called every 2 hours.

Scheduled JobFailed

Hi,
I scheduled to run a SP which uses XP_SMTP_SENDMAIL. I
have both MSSQLSERVER and MS SQL Agent running on system
account. The server is in a different domain and the mail
server is in different domain.
When I run the SP in QA, it runs without any error. But on
scheduling, it gives the following error:
"The Job failed. The job was invoked by User sa. The last
step to run was step 1 (mail task)."
Was it a problem of startup account?
TIA,
HariYou need to check out the output file for the jobstep (and first define such, of course). Here you
can fine the error message created. "It failed" doesn't tell us anything...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:05c201c39c78$09491960$a601280a@.phx.gbl...
> Hi,
> I scheduled to run a SP which uses XP_SMTP_SENDMAIL. I
> have both MSSQLSERVER and MS SQL Agent running on system
> account. The server is in a different domain and the mail
> server is in different domain.
> When I run the SP in QA, it runs without any error. But on
> scheduling, it gives the following error:
> "The Job failed. The job was invoked by User sa. The last
> step to run was step 1 (mail task)."
> Was it a problem of startup account?
> TIA,
> Hari
>|||Thanks.
I had used RAISERROR inside SP which uses
XP_SMTP_SENDMAIL. When I removed that code, the job
completed successfully.
>--Original Message--
>You need to check out the output file for the jobstep
(and first define such, of course). Here you
>can fine the error message created. "It failed" doesn't
tell us anything...
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Hari" <anonymous@.discussions.microsoft.com> wrote in
message
>news:05c201c39c78$09491960$a601280a@.phx.gbl...
>> Hi,
>> I scheduled to run a SP which uses XP_SMTP_SENDMAIL. I
>> have both MSSQLSERVER and MS SQL Agent running on system
>> account. The server is in a different domain and the
mail
>> server is in different domain.
>> When I run the SP in QA, it runs without any error. But
on
>> scheduling, it gives the following error:
>> "The Job failed. The job was invoked by User sa. The
last
>> step to run was step 1 (mail task)."
>> Was it a problem of startup account?
>> TIA,
>> Hari
>>
>
>.
>|||Yes, an error with severity level > 10 makes the job fail. If you only want to write to the
eventlog, you can use xp_logevent.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:063f01c39c7f$14d81130$a501280a@.phx.gbl...
> Thanks.
> I had used RAISERROR inside SP which uses
> XP_SMTP_SENDMAIL. When I removed that code, the job
> completed successfully.
>
> >--Original Message--
> >You need to check out the output file for the jobstep
> (and first define such, of course). Here you
> >can fine the error message created. "It failed" doesn't
> tell us anything...
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at: http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"Hari" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:05c201c39c78$09491960$a601280a@.phx.gbl...
> >> Hi,
> >>
> >> I scheduled to run a SP which uses XP_SMTP_SENDMAIL. I
> >> have both MSSQLSERVER and MS SQL Agent running on system
> >> account. The server is in a different domain and the
> mail
> >> server is in different domain.
> >>
> >> When I run the SP in QA, it runs without any error. But
> on
> >> scheduling, it gives the following error:
> >>
> >> "The Job failed. The job was invoked by User sa. The
> last
> >> step to run was step 1 (mail task)."
> >>
> >> Was it a problem of startup account?
> >>
> >> TIA,
> >> Hari
> >>
> >>
> >
> >
> >.
> >

schedule stored procedure

How to schedule a stored procedure to run at a certain system time in a regular basis?Use SQL Server Agent. You could configure SQL Server Agent jobs in Enterprise Manager or SQL Server Management Studio. Or by sp: sp_add_job, sp_add_jobstep, sp_add_jobschedule|||If you do not have SQL Agent available (you're using SQL 2005 Express), you can combine the use of SQLCmd.exe, Windows Scheduler, and Stored Procedures.

SCHEDULE DTS PACKAGE ERROR

I am having trouble scheduling a DTS package.
The error I receive is:
Executed as user: MAGIC-SQL-02\SYSTEM. DTSRun: Loading...
Error: -2147467259 (80004005); Provider Error: 17 (11) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0. Process Exit Code 1. The step failed.
Any idea why
TIA
Grant
Check the SQL Server Agent startup account. It appears the system account
is being used. NOt sure which SQL Server is causing the SQL Server does not
exist or access denied error, but if it is a remote SQL Server then the
system account will not have access to it. Change teh SQL Server Agent
startup account to a domain account that has access to all SQL Servers
accessed by the package.
Rand
This posting is provided "as is" with no warranties and confers no rights.

SCHEDULE DTS PACKAGE ERROR

I am having trouble scheduling a DTS package.
The error I receive is:
Executed as user: MAGIC-SQL-02\SYSTEM. DTSRun: Loading...
Error: -2147467259 (80004005); Provider Error: 17 (11) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not
exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0. Process Exit Code 1. The step failed.
Any idea why
TIA
GrantCheck the SQL Server Agent startup account. It appears the system account
is being used. NOt sure which SQL Server is causing the SQL Server does not
exist or access denied error, but if it is a remote SQL Server then the
system account will not have access to it. Change teh SQL Server Agent
startup account to a domain account that has access to all SQL Servers
accessed by the package.
Rand
This posting is provided "as is" with no warranties and confers no rights.

SCHEDULE DTS PACKAGE ERROR

I am having trouble scheduling a DTS package.
The error I receive is:
Executed as user: MAGIC-SQL-02\SYSTEM. DTSRun: Loading...
Error: -2147467259 (80004005); Provider Error: 17 (11) Error
string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied. Error source: Microsoft OLE DB Provider for SQL Server
Help file: Help context: 0. Process Exit Code 1. The step failed.
Any idea why
TIA
GrantCheck the SQL Server Agent startup account. It appears the system account
is being used. NOt sure which SQL Server is causing the SQL Server does not
exist or access denied error, but if it is a remote SQL Server then the
system account will not have access to it. Change teh SQL Server Agent
startup account to a domain account that has access to all SQL Servers
accessed by the package.
Rand
This posting is provided "as is" with no warranties and confers no rights.