Optimizations & Integrity Check Jobs failing?

Thanks in advance for any info.
What would cause Optimization & Integrity Check jobs to fail? The agent info
shows SQL State = 42000, which, according to BOL is "Syntax error,
permission violation, or other nonspecific error. " Hmm... where to go from
here?
Thanks,
MorganI think I have found out what it is...
After further investigation, the server is running SQL Server personal
edition and was exceeding the 5 user limit, which I am guessing is causing
the jobs to not run and reporting back a generic error message.
"Morgan" <abcd@.senditon.com> wrote in message
news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Thanks in advance for any info.
> What would cause Optimization & Integrity Check jobs to fail? The agent
info
> shows SQL State = 42000, which, according to BOL is "Syntax error,
> permission violation, or other nonspecific error. " Hmm... where to go
from
> here?
>
> Thanks,
> Morgan
>
>|||The "5 user limit" shouldn't cause this. That is only a throttling mechanism
(very misunderstood, though). Are you using the maint wizard? If so, specify
a report file in the maint plan and check for the real error message in that
report file.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Morgan" <abcd@.senditon.com> wrote in message
news:OPPPEXM7DHA.2300@.TK2MSFTNGP10.phx.gbl...
> I think I have found out what it is...
> After further investigation, the server is running SQL Server personal
> edition and was exceeding the 5 user limit, which I am guessing is causing
> the jobs to not run and reporting back a generic error message.
> "Morgan" <abcd@.senditon.com> wrote in message
> news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > Thanks in advance for any info.
> >
> > What would cause Optimization & Integrity Check jobs to fail? The agent
> info
> > shows SQL State = 42000, which, according to BOL is "Syntax error,
> > permission violation, or other nonspecific error. " Hmm... where to go
> from
> > here?
> >
> >
> > Thanks,
> >
> > Morgan
> >
> >
> >
>|||Thanks, Tibor. Unfortunately we had to burn the SQL Server install and
install EE on the box. If it starts happening again, we'll hit the logs for
more detail.
Thanks again.
Morgan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfAd4CO7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> The "5 user limit" shouldn't cause this. That is only a throttling
mechanism
> (very misunderstood, though). Are you using the maint wizard? If so,
specify
> a report file in the maint plan and check for the real error message in
that
> report file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Morgan" <abcd@.senditon.com> wrote in message
> news:OPPPEXM7DHA.2300@.TK2MSFTNGP10.phx.gbl...
> > I think I have found out what it is...
> > After further investigation, the server is running SQL Server personal
> > edition and was exceeding the 5 user limit, which I am guessing is
causing
> > the jobs to not run and reporting back a generic error message.
> >
> > "Morgan" <abcd@.senditon.com> wrote in message
> > news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > > Thanks in advance for any info.
> > >
> > > What would cause Optimization & Integrity Check jobs to fail? The
agent
> > info
> > > shows SQL State = 42000, which, according to BOL is "Syntax error,
> > > permission violation, or other nonspecific error. " Hmm... where to go
> > from
> > > here?
> > >
> > >
> > > Thanks,
> > >
> > > Morgan
> > >
> > >
> > >
> >
> >
>

Optimizations & Integrity Check Jobs failing?

Thanks in advance for any info.
What would cause Optimization & Integrity Check jobs to fail? The agent info
shows SQL State = 42000, which, according to BOL is "Syntax error,
permission violation, or other nonspecific error. " Hmm... where to go from
here?
Thanks,
MorganI think I have found out what it is...
After further investigation, the server is running SQL Server personal
edition and was exceeding the 5 user limit, which I am guessing is causing
the jobs to not run and reporting back a generic error message.
"Morgan" <abcd@.senditon.com> wrote in message
news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Thanks in advance for any info.
> What would cause Optimization & Integrity Check jobs to fail? The agent
info
> shows SQL State = 42000, which, according to BOL is "Syntax error,
> permission violation, or other nonspecific error. " Hmm... where to go
from
> here?
>
> Thanks,
> Morgan
>
>|||The "5 user limit" shouldn't cause this. That is only a throttling mechanism
(very misunderstood, though). Are you using the maint wizard? If so, specify
a report file in the maint plan and check for the real error message in that
report file.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Morgan" <abcd@.senditon.com> wrote in message
news:OPPPEXM7DHA.2300@.TK2MSFTNGP10.phx.gbl...
> I think I have found out what it is...
> After further investigation, the server is running SQL Server personal
> edition and was exceeding the 5 user limit, which I am guessing is causing
> the jobs to not run and reporting back a generic error message.
> "Morgan" <abcd@.senditon.com> wrote in message
> news:ercGkiL7DHA.1428@.TK2MSFTNGP12.phx.gbl...
> info
> from
>|||Thanks, Tibor. Unfortunately we had to burn the SQL Server install and
install EE on the box. If it starts happening again, we'll hit the logs for
more detail.
Thanks again.
Morgan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OfAd4CO7DHA.1596@.TK2MSFTNGP10.phx.gbl...
> The "5 user limit" shouldn't cause this. That is only a throttling
mechanism
> (very misunderstood, though). Are you using the maint wizard? If so,
specify
> a report file in the maint plan and check for the real error message in
that
> report file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Morgan" <abcd@.senditon.com> wrote in message
> news:OPPPEXM7DHA.2300@.TK2MSFTNGP10.phx.gbl...
causing
agent
>

Optimization job failing

I have scheduled the optimization job to run everyday and sometimes it fails.
I get this:
Executed as user: test\test. ConnectionCheckForData (CheckforData()).
{SQLSTATE 01000} (Message 10054) General network error. Check your network
documentation. {SQLSTATE 08S01} (Error 11). The step failed.
It does not fail every night. I had it scheduled to run once a week, but it
was taken a long time to run so I thought I would run it more often to reduce
the time.
Also, I notice that when the optimization job is running the transaction log
backup fails.
Any help would be greatly appreciated.
Thank you!
Are you having network problems sporadically? Run ping -t all night several
nights in a row (dump to a text file) and see if you have any 'blips" in
connectivity.
"Orn" wrote:

> I have scheduled the optimization job to run everyday and sometimes it fails.
> I get this:
> Executed as user: test\test. ConnectionCheckForData (CheckforData()).
> {SQLSTATE 01000} (Message 10054) General network error. Check your network
> documentation. {SQLSTATE 08S01} (Error 11). The step failed.
> It does not fail every night. I had it scheduled to run once a week, but it
> was taken a long time to run so I thought I would run it more often to reduce
> the time.
> Also, I notice that when the optimization job is running the transaction log
> backup fails.
> Any help would be greatly appreciated.
> Thank you!
|||ok, i will try that.
Can you tell me what the network would have to do with an optimization job
running locally on the SQL server?
Thank you.
"Donna Lambert" wrote:
[vbcol=seagreen]
> Are you having network problems sporadically? Run ping -t all night several
> nights in a row (dump to a text file) and see if you have any 'blips" in
> connectivity.
>
> "Orn" wrote:
sql

Optimization job failing

I have scheduled the optimization job to run everyday and sometimes it fails
.
I get this:
Executed as user: test\test. ConnectionCheckForData (CheckforData()).
{SQLSTATE 01000} (Message 10054) General network error. Check your netw
ork
documentation. {SQLSTATE 08S01} (Error 11). The step failed.
It does not fail every night. I had it scheduled to run once a week, but it
was taken a long time to run so I thought I would run it more often to reduc
e
the time.
Also, I notice that when the optimization job is running the transaction log
backup fails.
Any help would be greatly appreciated.
Thank you!Are you having network problems sporadically? Run ping -t all night several
nights in a row (dump to a text file) and see if you have any 'blips" in
connectivity.
"Orn" wrote:

> I have scheduled the optimization job to run everyday and sometimes it fai
ls.
> I get this:
> Executed as user: test\test. ConnectionCheckForData (CheckforData()).
> {SQLSTATE 01000} (Message 10054) General network error. Check your ne
twork
> documentation. {SQLSTATE 08S01} (Error 11). The step failed.
> It does not fail every night. I had it scheduled to run once a week, but
it
> was taken a long time to run so I thought I would run it more often to red
uce
> the time.
> Also, I notice that when the optimization job is running the transaction l
og
> backup fails.
> Any help would be greatly appreciated.
> Thank you!|||ok, i will try that.
Can you tell me what the network would have to do with an optimization job
running locally on the SQL server?
Thank you.
"Donna Lambert" wrote:
[vbcol=seagreen]
> Are you having network problems sporadically? Run ping -t all night sever
al
> nights in a row (dump to a text file) and see if you have any 'blips" in
> connectivity.
>
> "Orn" wrote:
>

Optimization job failing

I have scheduled the optimization job to run everyday and sometimes it fails.
I get this:
Executed as user: test\test. ConnectionCheckForData (CheckforData()).
{SQLSTATE 01000} (Message 10054) General network error. Check your network
documentation. {SQLSTATE 08S01} (Error 11). The step failed.
It does not fail every night. I had it scheduled to run once a week, but it
was taken a long time to run so I thought I would run it more often to reduce
the time.
Also, I notice that when the optimization job is running the transaction log
backup fails.
Any help would be greatly appreciated.
Thank you!Are you having network problems sporadically? Run ping -t all night several
nights in a row (dump to a text file) and see if you have any 'blips" in
connectivity.
"Orn" wrote:
> I have scheduled the optimization job to run everyday and sometimes it fails.
> I get this:
> Executed as user: test\test. ConnectionCheckForData (CheckforData()).
> {SQLSTATE 01000} (Message 10054) General network error. Check your network
> documentation. {SQLSTATE 08S01} (Error 11). The step failed.
> It does not fail every night. I had it scheduled to run once a week, but it
> was taken a long time to run so I thought I would run it more often to reduce
> the time.
> Also, I notice that when the optimization job is running the transaction log
> backup fails.
> Any help would be greatly appreciated.
> Thank you!|||ok, i will try that.
Can you tell me what the network would have to do with an optimization job
running locally on the SQL server?
Thank you.
"Donna Lambert" wrote:
> Are you having network problems sporadically? Run ping -t all night several
> nights in a row (dump to a text file) and see if you have any 'blips" in
> connectivity.
>
> "Orn" wrote:
> > I have scheduled the optimization job to run everyday and sometimes it fails.
> > I get this:
> >
> > Executed as user: test\test. ConnectionCheckForData (CheckforData()).
> > {SQLSTATE 01000} (Message 10054) General network error. Check your network
> > documentation. {SQLSTATE 08S01} (Error 11). The step failed.
> >
> > It does not fail every night. I had it scheduled to run once a week, but it
> > was taken a long time to run so I thought I would run it more often to reduce
> > the time.
> >
> > Also, I notice that when the optimization job is running the transaction log
> > backup fails.
> >
> > Any help would be greatly appreciated.
> >
> > Thank you!

Operator failing

sql2k sp3
SQLMail and the Mail Profile are configured and
successfully test when hitting the "test" button in the
SQL Mail Configuration Properties box.
But when I try to do a test for an operator in the
Operator Properties it says "Error 22022: SQLServerAgent
mail session not running; check mail profile and/ or
SQLServerAgent service startup account in SQLServerAgent
properites dialog box".
The Agent and the Mail Profile(same account) are in the
Local Admins Group. I have already rebooted.
Any ideas?
TIA, ChrisR
Hi Chris
You have already asked this question once and got a reply...:-).
Have you tried to check the properties of the SQLSERVERAgent in EM. Here
you have to set a mailprofile for the mail session.
Regards
Steen
"ChrtisR" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:2901301c464fa$6779e5a0$a501280a@.phx.gbl...
> sql2k sp3
> SQLMail and the Mail Profile are configured and
> successfully test when hitting the "test" button in the
> SQL Mail Configuration Properties box.
> But when I try to do a test for an operator in the
> Operator Properties it says "Error 22022: SQLServerAgent
> mail session not running; check mail profile and/ or
> SQLServerAgent service startup account in SQLServerAgent
> properites dialog box".
> The Agent and the Mail Profile(same account) are in the
> Local Admins Group. I have already rebooted.
> Any ideas?
> TIA, ChrisR
>

Operator failing

sql2k sp3
SQLMail and the Mail Profile are configured and
successfully test when hitting the "test" button in the
SQL Mail Configuration Properties box.
But when I try to do a test for an operator in the
Operator Properties it says "Error 22022: SQLServerAgent
mail session not running; check mail profile and/ or
SQLServerAgent service startup account in SQLServerAgent
properites dialog box".
The Agent and the Mail Profile(same account) are in the
Local Admins Group. I have already rebooted.
Any ideas?
TIA, ChrisRHi Chris
You have already asked this question once and got a reply...:-).
Have you tried to check the properties of the SQLSERVERAgent in EM. Here
you have to set a mailprofile for the mail session.
Regards
Steen
"ChrtisR" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:2901301c464fa$6779e5a0$a501280a@.phx
.gbl...
> sql2k sp3
> SQLMail and the Mail Profile are configured and
> successfully test when hitting the "test" button in the
> SQL Mail Configuration Properties box.
> But when I try to do a test for an operator in the
> Operator Properties it says "Error 22022: SQLServerAgent
> mail session not running; check mail profile and/ or
> SQLServerAgent service startup account in SQLServerAgent
> properites dialog box".
> The Agent and the Mail Profile(same account) are in the
> Local Admins Group. I have already rebooted.
> Any ideas?
> TIA, ChrisR
>

Operator failing

sql2k sp3
SQLMail and the Mail Profile are configured and
successfully test when hitting the "test" button in the
SQL Mail Configuration Properties box.
But when I try to do a test for an operator in the
Operator Properties it says "Error 22022: SQLServerAgent
mail session not running; check mail profile and/ or
SQLServerAgent service startup account in SQLServerAgent
properites dialog box".
The Agent and the Mail Profile(same account) are in the
Local Admins Group. I have already rebooted.
Any ideas?
TIA, ChrisRHi Chris
You have already asked this question once and got a reply...:-).
Have you tried to check the properties of the SQLSERVERAgent in EM. Here
you have to set a mailprofile for the mail session.
Regards
Steen
"ChrtisR" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:2901301c464fa$6779e5a0$a501280a@.phx.gbl...
> sql2k sp3
> SQLMail and the Mail Profile are configured and
> successfully test when hitting the "test" button in the
> SQL Mail Configuration Properties box.
> But when I try to do a test for an operator in the
> Operator Properties it says "Error 22022: SQLServerAgent
> mail session not running; check mail profile and/ or
> SQLServerAgent service startup account in SQLServerAgent
> properites dialog box".
> The Agent and the Mail Profile(same account) are in the
> Local Admins Group. I have already rebooted.
> Any ideas?
> TIA, ChrisR
>

Operationg System Command (CmdExec) job failing

I have sql 2000 job that has been setup as a "Operating System Command (CmdExec) job. I am logged into the SQL Server as DomainName\SQLAdmin this domain account is part of the Administrator group on the SQL Server that the job in running on. This account has SysAdmin rights and is also starting the MSSQLSERVER Service and SQLSERVERAGENT Service on the same machine.

The job just copies files from one directory to another, here's the code.

D:
cd \MSSQL\BACKUP\AP\AP_Primary
xcopy *.* D:\MSSQL\BACKUP\AP\ /s/y/d

When run as a job, with the owner of the job being DomainName\SQLAdmin the job fails with the following error message: Executed as user: DomainName\SQLAdmin. The process could not be created for step 1 of job 0x822E9AD29DCAAF4196369A46C7FE212A (reason: Access is denied). The step failed.

Here's the weird part if I open a command window on the sql server and run the batch it works fine.

I even tried executing the commands via xp_cmdshell but that didn't work either, I recieved the message: (1 row(s) affected) with the output being NULL and the file was never copied.

Anyone have an idea whats going on?It sounds like there could be multiple reasons for the access denied issue, but an odd one which I just found out about follows:

I was just trying to restore a db and I was receiving an Access denied error. I ended up finding out that some of the parent folders were in 'read only' mode. After I disabled it, everything worked fine.

Good luck,
Hope this helped|||what account is listed as the job owner?|||The account that owns the job, DomainName\SQLAdmin, is also the account that belongs to the Administrator group on the server and Stops and Starts the MSSQLSERVER/SQLSERVERAGENT services. As far as I can tell this account has all the permissions possible.

Also checked permissions on all parent folders none are marked read-only.|||I found a work around but it doesn't explain why my original job failed. What I did was create a batch file with the following code.

D:
cd \MSSQL\BACKUP\AP\AP_Primary
xcopy *.* D:\MSSQL\BACKUP\AP\ /s/y/d

I then stored it on the C: drive and called the batch from within the job and this worked. So anyone have an idea what's going on? I'm sure it's a permissions thing but I can't explain it.