Link Server Error: [SQLSTATE 42000] (Error 7399)

I have created a link server between SQL Server 2000 and
MS Access. If I run the job manually, the job runs fine.
But, if I schedule the job, it fails with the follwoing
error message.
Executed as user: raj. OLE DB
provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error
7312) OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
[SQLSTATE 01000] (Error 7300). The step failed.
I have also created a DTS package to update another Access
database, if I execute the package, it runs. If I schedule
the job, the job fails.
I have checked the permissions for the user Raj and it has
all the privilges.
Any help will be greatly appreciated!
Please suggest, how to fix this issue.
Thanks,
Raj
With what account do you use to start the sqlagent with? Does that have
necessary permissions?
thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||HRIS and that acount has read write privilges on the
folder where Access database is residing.
Thanks,
Raj

>--Original Message--
>With what account do you use to start the sqlagent with?
Does that have
>necessary permissions?
>thanks,
>Vikram Jayaram
>Microsoft, SQL Server
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Subscribe to MSDN & use
http://msdn.microsoft.com/newsgroups.
>
>.
>
|||I was experiencing the exact same issue.
I solved it by using absolute paths in place of drive letters when defining the linked server
So in place of z:\folder\file.mdb I wrote \\servername\share\folder\file.mdb
this solved the issue for me

Quote:

Originally Posted by Raj

HRIS and that acount has read write privilges on the
folder where Access database is residing.
Thanks,
Raj

>--Original Message--
>With what account do you use to start the sqlagent with?
Does that have
>necessary permissions?
>thanks,
>Vikram Jayaram
>Microsoft, SQL Server
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Subscribe to MSDN & use
http://msdn.microsoft.com/newsgroups.
>
>.
>

Link Server Error: [SQLSTATE 42000] (Error 7399)

I have created a link server between SQL Server 2000 and
MS Access. If I run the job manually, the job runs fine.
But, if I schedule the job, it fails with the follwoing
error message.
Executed as user: raj. OLE DB
provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error
7312) OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
[SQLSTATE 01000] (Error 7300). The step failed.
I have also created a DTS package to update another Access
database, if I execute the package, it runs. If I schedule
the job, the job fails.
I have checked the permissions for the user Raj and it has
all the privilges.
Any help will be greatly appreciated!
Please suggest, how to fix this issue.
Thanks,
RajWith what account do you use to start the sqlagent with? Does that have
necessary permissions?
thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||HRIS and that acount has read write privilges on the
folder where Access database is residing.
Thanks,
Raj

>--Original Message--
>With what account do you use to start the sqlagent with?
Does that have
>necessary permissions?
>thanks,
>Vikram Jayaram
>Microsoft, SQL Server
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Subscribe to MSDN & use
http://msdn.microsoft.com/newsgroups.
>
>.
>

Link Server Error: [SQLSTATE 42000] (Error 7399)

I have created a link server between SQL Server 2000 and
MS Access. If I run the job manually, the job runs fine.
But, if I schedule the job, it fails with the follwoing
error message.
Executed as user: raj. OLE DB
provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error
7312) OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
[SQLSTATE 01000] (Error 7300). The step failed.
I have also created a DTS package to update another Access
database, if I execute the package, it runs. If I schedule
the job, the job fails.
I have checked the permissions for the user Raj and it has
all the privilges.
Any help will be greatly appreciated!
Please suggest, how to fix this issue.
Thanks,
RajWith what account do you use to start the sqlagent with? Does that have
necessary permissions?
thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||HRIS and that acount has read write privilges on the
folder where Access database is residing.
Thanks,
Raj
>--Original Message--
>With what account do you use to start the sqlagent with?
Does that have
>necessary permissions?
>thanks,
>Vikram Jayaram
>Microsoft, SQL Server
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Subscribe to MSDN & use
http://msdn.microsoft.com/newsgroups.
>
>.
>sql

Lines in a job cmdexec step

Does it only execute the first line, or is there a way to have it
execute multiple lines? Sure seems like it just does one line. Seems
fine if that line executes a .cmd file with multiple lines.
But for some reasons I *thought* you could put multiple lines in the
little text window in the job editor and it would work, but it's not.
I'm trying to set a couple of environment variables before calling a
dts package:
--
set foo=123
dtsrun mypackage
--
Thanks.
Josh> Does it only execute the first line, or is there a way to have it
> execute multiple lines?
For cmdExec job steps, only the first line will be executed. Is using a bat file an option?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jxstern" <jxstern@.wherever.com> wrote in message news:q13v529cuemj7e79o2is9cfds5vncn4v8s@.4ax.com...
> Does it only execute the first line, or is there a way to have it
> execute multiple lines? Sure seems like it just does one line. Seems
> fine if that line executes a .cmd file with multiple lines.
> But for some reasons I *thought* you could put multiple lines in the
> little text window in the job editor and it would work, but it's not.
> I'm trying to set a couple of environment variables before calling a
> dts package:
> --
> set foo=123
> dtsrun mypackage
> --
>
> Thanks.
> Josh
>|||On Tue, 9 May 2006 15:11:47 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>> Does it only execute the first line, or is there a way to have it
>> execute multiple lines?
>For cmdExec job steps, only the first line will be executed. Is using a bat file an option?
Guess so!
Already had them written, actually, for testing purposes.
Now have to run them through the deployment process ...
Thanks.
Josh

Lines in a job cmdexec step

Does it only execute the first line, or is there a way to have it
execute multiple lines? Sure seems like it just does one line. Seems
fine if that line executes a .cmd file with multiple lines.
But for some reasons I *thought* you could put multiple lines in the
little text window in the job editor and it would work, but it's not.
I'm trying to set a couple of environment variables before calling a
dts package:
set foo=123
dtsrun mypackage
--
Thanks.
Josh> Does it only execute the first line, or is there a way to have it
> execute multiple lines?
For cmdExec job steps, only the first line will be executed. Is using a bat
file an option?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jxstern" <jxstern@.wherever.com> wrote in message news:q13v529cuemj7e79o2is9cfds5vncn4v8s@.
4a
x.com...
> Does it only execute the first line, or is there a way to have it
> execute multiple lines? Sure seems like it just does one line. Seems
> fine if that line executes a .cmd file with multiple lines.
> But for some reasons I *thought* you could put multiple lines in the
> little text window in the job editor and it would work, but it's not.
> I'm trying to set a couple of environment variables before calling a
> dts package:
> --
> set foo=123
> dtsrun mypackage
> --
>
> Thanks.
> Josh
>|||On Tue, 9 May 2006 15:11:47 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>For cmdExec job steps, only the first line will be executed. Is using a bat file an
option?
Guess so!
Already had them written, actually, for testing purposes.
Now have to run them through the deployment process ...
Thanks.
Josh

Limiting job history in SQL2005

Hi,
Under SQL Agent properties/History there are 2 limiting columns for Limit
size of job history log.
We have Maximum job history set to 12000 and maximum job history rows per
job at 150 but we only seem to be able to keep 2 or 3 days worth of
jobhistory even though looking in sysjobhistory I can only return 7300 rows.
I thought that it would keep a maximum of 150 per job to a total of 12000.
Is something else limiting the data in sysjobhistory?
Thanks
ChrisHi,
We found the problem. If you run a maintenance plan that has a History
Cleanup Task and its run by an admin level account then the
sp_purge_jobhistory does not include a @.job_name parameter. This combination
means that all job history gets cleaned up rather than just the job that has
the History Cleanup Task problem raised on Connect as bug 322320.
Chris
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Under SQL Agent properties/History there are 2 limiting columns for Limit
> size of job history log.
> We have Maximum job history set to 12000 and maximum job history rows per
> job at 150 but we only seem to be able to keep 2 or 3 days worth of
> jobhistory even though looking in sysjobhistory I can only return 7300
> rows.
> I thought that it would keep a maximum of 150 per job to a total of 12000.
> Is something else limiting the data in sysjobhistory?
> Thanks
> Chris
>|||Very interesting Chris. Thanks for the update.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We found the problem. If you run a maintenance plan that has a History
> Cleanup Task and its run by an admin level account then the
> sp_purge_jobhistory does not include a @.job_name parameter. This
> combination means that all job history gets cleaned up rather than just
> the job that has the History Cleanup Task problem raised on Connect as bug
> 322320.
> Chris
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for Limit
>> size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows per
>> job at 150 but we only seem to be able to keep 2 or 3 days worth of
>> jobhistory even though looking in sysjobhistory I can only return 7300
>> rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>|||Unfortunately Andrew I have not seen any reposnse to my Connect post.
Thanks
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
> Very interesting Chris. Thanks for the update.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than just
>> the job that has the History Cleanup Task problem raised on Connect as
>> bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth of
>> jobhistory even though looking in sysjobhistory I can only return 7300
>> rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>|||Actually I just had a look at the sp_purge_jobhistory sp and the bottom line
is that if you don't specify a job ID or name it will delete all history
beyond the date specified. It makes no difference how many rows you have set
in SQL Agent as it does not even check for that. So if you want to keep x
many days worth you need to ensure that the date specified in the Maint Plan
for the history cleanup is far enough out so the history is maintained.
There is no link between the two. The Agent behavior was that way long
before the maint plan had the hist cleanup task and they simply didn't
factor in agents settings when they wrote the task for the maint plans. They
should have have had you specify a minimum # of rows deform they deleted
them by date only.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Unfortunately Andrew I have not seen any reposnse to my Connect post.
> Thanks
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than just
>> the job that has the History Cleanup Task problem raised on Connect as
>> bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth of
>> jobhistory even though looking in sysjobhistory I can only return 7300
>> rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>>
>|||Andrew,
That's part of the problem. BOL states that if you are an admin then a
straight sp_purge_jobhistory, without the @.job_name will purge ALL
jobhistory. This is my problem. The maintenance plan does not include the
@.job_name parameter otherwise this might have worked fine. We had to look at
all our jobs that do a cleanup and are run by an admin account to make sure
that we have made the cleanup period long enough.
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
> line is that if you don't specify a job ID or name it will delete all
> history beyond the date specified. It makes no difference how many rows
> you have set in SQL Agent as it does not even check for that. So if you
> want to keep x many days worth you need to ensure that the date specified
> in the Maint Plan for the history cleanup is far enough out so the history
> is maintained. There is no link between the two. The Agent behavior was
> that way long before the maint plan had the hist cleanup task and they
> simply didn't factor in agents settings when they wrote the task for the
> maint plans. They should have have had you specify a minimum # of rows
> deform they deleted them by date only.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than just
>> the job that has the History Cleanup Task problem raised on Connect as
>> bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth
>> of jobhistory even though looking in sysjobhistory I can only return
>> 7300 rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>>
>>
>|||Chris,
That only means that you need it to be in the sa or SQLAgentOperatorRole in
order to modify any jobs that are not you own. Since you don't specify a job
ID it has to assume you need to be in one of those two roles. If not it
throws an exception and does nothing. So if you want to keep a certain
amount of history for a particular job I suggest you create your won
scheduled job that calls sp_purge_jobhistory with the proper Job names and
dates for each. You may need to set up a table with any jobs that need a
date other than the default you pass. It's basically do it for every job by
date or one job at a time by date and job id or name. Unfortunately there is
no in between.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
> Andrew,
> That's part of the problem. BOL states that if you are an admin then a
> straight sp_purge_jobhistory, without the @.job_name will purge ALL
> jobhistory. This is my problem. The maintenance plan does not include the
> @.job_name parameter otherwise this might have worked fine. We had to look
> at all our jobs that do a cleanup and are run by an admin account to make
> sure that we have made the cleanup period long enough.
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
>> line is that if you don't specify a job ID or name it will delete all
>> history beyond the date specified. It makes no difference how many rows
>> you have set in SQL Agent as it does not even check for that. So if you
>> want to keep x many days worth you need to ensure that the date specified
>> in the Maint Plan for the history cleanup is far enough out so the
>> history is maintained. There is no link between the two. The Agent
>> behavior was that way long before the maint plan had the hist cleanup
>> task and they simply didn't factor in agents settings when they wrote the
>> task for the maint plans. They should have have had you specify a minimum
>> # of rows deform they deleted them by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than
>> just the job that has the History Cleanup Task problem raised on
>> Connect as bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth
>> of jobhistory even though looking in sysjobhistory I can only return
>> 7300 rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>>
>>
>|||Thanks again Andrew. MS could always fix the bug and include the @.job_name
in the script they produce.
I don't see us changing our backups as you have to be an admin to run the
xp_delete_file that comes from the Maintenance Cleanup Task (See bug 247463
in Connect).
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJzpwveYIHA.4272@.TK2MSFTNGP05.phx.gbl...
> Chris,
> That only means that you need it to be in the sa or SQLAgentOperatorRole
> in order to modify any jobs that are not you own. Since you don't specify
> a job ID it has to assume you need to be in one of those two roles. If not
> it throws an exception and does nothing. So if you want to keep a certain
> amount of history for a particular job I suggest you create your won
> scheduled job that calls sp_purge_jobhistory with the proper Job names and
> dates for each. You may need to set up a table with any jobs that need a
> date other than the default you pass. It's basically do it for every job
> by date or one job at a time by date and job id or name. Unfortunately
> there is no in between.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Andrew,
>> That's part of the problem. BOL states that if you are an admin then a
>> straight sp_purge_jobhistory, without the @.job_name will purge ALL
>> jobhistory. This is my problem. The maintenance plan does not include the
>> @.job_name parameter otherwise this might have worked fine. We had to look
>> at all our jobs that do a cleanup and are run by an admin account to make
>> sure that we have made the cleanup period long enough.
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
>> line is that if you don't specify a job ID or name it will delete all
>> history beyond the date specified. It makes no difference how many rows
>> you have set in SQL Agent as it does not even check for that. So if you
>> want to keep x many days worth you need to ensure that the date
>> specified in the Maint Plan for the history cleanup is far enough out so
>> the history is maintained. There is no link between the two. The Agent
>> behavior was that way long before the maint plan had the hist cleanup
>> task and they simply didn't factor in agents settings when they wrote
>> the task for the maint plans. They should have have had you specify a
>> minimum # of rows deform they deleted them by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a
>> History Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than
>> just the job that has the History Cleanup Task problem raised on
>> Connect as bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Under SQL Agent properties/History there are 2 limiting columns for
>>> Limit size of job history log.
>>> We have Maximum job history set to 12000 and maximum job history
>>> rows per job at 150 but we only seem to be able to keep 2 or 3 days
>>> worth of jobhistory even though looking in sysjobhistory I can only
>>> return 7300 rows.
>>>
>>> I thought that it would keep a maximum of 150 per job to a total of
>>> 12000.
>>>
>>> Is something else limiting the data in sysjobhistory?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>
>>
>>
>>
>|||> Thanks again Andrew. MS could always fix the bug and include the @.job_name
> in the script they produce.
I think you are missing my point. The task in the Maint Plan does not have a
way to specify the job only a date. This isn't a bug it is simply how they
created the task. How do you expect them to specify the job name? Which job
name are they supposed to use? Like I said the task is not in any way shape
or form tied to SQL Agent. It simply deletes rows from MSDB that happen to
be the Agent history. But it knows nothing of the jobs themselves. If it
looped thru each job one by one and specified the name it would still delete
all the rows for each job that were after the date you specified. You end up
with the same result. So this isn't a bug and I am sure they will not fix it
as such. If you want anything other than that behavior you must create it
your self. The Maint Plans are extremely limiting in what they can do and
how they do it. And it is not always clear on what it actually does unless
you trace the plans actions. One of the main reasons why I almost never
recommend using Maint Plans in the first place.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:um0fh8eYIHA.5132@.TK2MSFTNGP02.phx.gbl...
> Thanks again Andrew. MS could always fix the bug and include the @.job_name
> in the script they produce.
> I don't see us changing our backups as you have to be an admin to run the
> xp_delete_file that comes from the Maintenance Cleanup Task (See bug
> 247463 in Connect).
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJzpwveYIHA.4272@.TK2MSFTNGP05.phx.gbl...
>> Chris,
>> That only means that you need it to be in the sa or SQLAgentOperatorRole
>> in order to modify any jobs that are not you own. Since you don't specify
>> a job ID it has to assume you need to be in one of those two roles. If
>> not it throws an exception and does nothing. So if you want to keep a
>> certain amount of history for a particular job I suggest you create your
>> won scheduled job that calls sp_purge_jobhistory with the proper Job
>> names and dates for each. You may need to set up a table with any jobs
>> that need a date other than the default you pass. It's basically do it
>> for every job by date or one job at a time by date and job id or name.
>> Unfortunately there is no in between.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Andrew,
>> That's part of the problem. BOL states that if you are an admin then a
>> straight sp_purge_jobhistory, without the @.job_name will purge ALL
>> jobhistory. This is my problem. The maintenance plan does not include
>> the @.job_name parameter otherwise this might have worked fine. We had to
>> look at all our jobs that do a cleanup and are run by an admin account
>> to make sure that we have made the cleanup period long enough.
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
>> line is that if you don't specify a job ID or name it will delete all
>> history beyond the date specified. It makes no difference how many rows
>> you have set in SQL Agent as it does not even check for that. So if
>> you want to keep x many days worth you need to ensure that the date
>> specified in the Maint Plan for the history cleanup is far enough out
>> so the history is maintained. There is no link between the two. The
>> Agent behavior was that way long before the maint plan had the hist
>> cleanup task and they simply didn't factor in agents settings when they
>> wrote the task for the maint plans. They should have have had you
>> specify a minimum # of rows deform they deleted them by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>>> Hi,
>>>
>>> We found the problem. If you run a maintenance plan that has a
>>> History Cleanup Task and its run by an admin level account then the
>>> sp_purge_jobhistory does not include a @.job_name parameter. This
>>> combination means that all job history gets cleaned up rather than
>>> just the job that has the History Cleanup Task problem raised on
>>> Connect as bug 322320.
>>>
>>> Chris
>>>
>>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Under SQL Agent properties/History there are 2 limiting columns for
>>> Limit size of job history log.
>>> We have Maximum job history set to 12000 and maximum job history
>>> rows per job at 150 but we only seem to be able to keep 2 or 3 days
>>> worth of jobhistory even though looking in sysjobhistory I can only
>>> return 7300 rows.
>>>
>>> I thought that it would keep a maximum of 150 per job to a total of
>>> 12000.
>>>
>>> Is something else limiting the data in sysjobhistory?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>>
>>>
>>
>>
>>
>|||I see your point Andrew. I hadn't thought about how the Maint Plan would use
the Maintenance Cleanup Task. I am not a Visual Studio user so I don't
always see how the toolset gets used in Management Studio.
It definitely sounds as though taking this task out of the maintenance plans
and creating a nightly job could be the best option.
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23I2X1bfYIHA.4172@.TK2MSFTNGP02.phx.gbl...
>> Thanks again Andrew. MS could always fix the bug and include the
>> @.job_name in the script they produce.
> I think you are missing my point. The task in the Maint Plan does not have
> a way to specify the job only a date. This isn't a bug it is simply how
> they created the task. How do you expect them to specify the job name?
> Which job name are they supposed to use? Like I said the task is not in
> any way shape or form tied to SQL Agent. It simply deletes rows from MSDB
> that happen to be the Agent history. But it knows nothing of the jobs
> themselves. If it looped thru each job one by one and specified the name
> it would still delete all the rows for each job that were after the date
> you specified. You end up with the same result. So this isn't a bug and I
> am sure they will not fix it as such. If you want anything other than that
> behavior you must create it your self. The Maint Plans are extremely
> limiting in what they can do and how they do it. And it is not always
> clear on what it actually does unless you trace the plans actions. One of
> the main reasons why I almost never recommend using Maint Plans in the
> first place.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:um0fh8eYIHA.5132@.TK2MSFTNGP02.phx.gbl...
>> Thanks again Andrew. MS could always fix the bug and include the
>> @.job_name in the script they produce.
>> I don't see us changing our backups as you have to be an admin to run the
>> xp_delete_file that comes from the Maintenance Cleanup Task (See bug
>> 247463 in Connect).
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJzpwveYIHA.4272@.TK2MSFTNGP05.phx.gbl...
>> Chris,
>> That only means that you need it to be in the sa or SQLAgentOperatorRole
>> in order to modify any jobs that are not you own. Since you don't
>> specify a job ID it has to assume you need to be in one of those two
>> roles. If not it throws an exception and does nothing. So if you want to
>> keep a certain amount of history for a particular job I suggest you
>> create your won scheduled job that calls sp_purge_jobhistory with the
>> proper Job names and dates for each. You may need to set up a table with
>> any jobs that need a date other than the default you pass. It's
>> basically do it for every job by date or one job at a time by date and
>> job id or name. Unfortunately there is no in between.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Andrew,
>> That's part of the problem. BOL states that if you are an admin then a
>> straight sp_purge_jobhistory, without the @.job_name will purge ALL
>> jobhistory. This is my problem. The maintenance plan does not include
>> the @.job_name parameter otherwise this might have worked fine. We had
>> to look at all our jobs that do a cleanup and are run by an admin
>> account to make sure that we have made the cleanup period long enough.
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the
>> bottom line is that if you don't specify a job ID or name it will
>> delete all history beyond the date specified. It makes no difference
>> how many rows you have set in SQL Agent as it does not even check for
>> that. So if you want to keep x many days worth you need to ensure
>> that the date specified in the Maint Plan for the history cleanup is
>> far enough out so the history is maintained. There is no link between
>> the two. The Agent behavior was that way long before the maint plan
>> had the hist cleanup task and they simply didn't factor in agents
>> settings when they wrote the task for the maint plans. They should
>> have have had you specify a minimum # of rows deform they deleted them
>> by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>>> Very interesting Chris. Thanks for the update.
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>>
>>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>>> Hi,
>>>
>>> We found the problem. If you run a maintenance plan that has a
>>> History Cleanup Task and its run by an admin level account then the
>>> sp_purge_jobhistory does not include a @.job_name parameter. This
>>> combination means that all job history gets cleaned up rather than
>>> just the job that has the History Cleanup Task problem raised on
>>> Connect as bug 322320.
>>>
>>> Chris
>>>
>>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Under SQL Agent properties/History there are 2 limiting columns
>>> for Limit size of job history log.
>>> We have Maximum job history set to 12000 and maximum job history
>>> rows per job at 150 but we only seem to be able to keep 2 or 3
>>> days worth of jobhistory even though looking in sysjobhistory I
>>> can only return 7300 rows.
>>>
>>> I thought that it would keep a maximum of 150 per job to a total
>>> of 12000.
>>>
>>> Is something else limiting the data in sysjobhistory?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>

limit the resources of a job

hi
we develop a stored procedure that run a dll, the dll consumes all the
processor, and take a long of time (2 days) processing data.
its posible limit the amount of cpu uses by a single stored procedure?
i know that using the windows system resource manager its posible limit
the amount of cpu uses by all sql server, but it's possible for only a
stored procedure?
thanksSQL Server can't control what your external process does. You said it
is the DLL and not the proc that consumes your resources.
Two days is an extremely long time to execute a proc. Why not invoke
your code from outside SQL Server using .NET or VB or something else?
That way you may be able to add some code to monitor and control what
happens during processing.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Alternatively, you may try to relax the grip on the processor (e.g. by not
using tight loops) in the code of the DLL if that doesn't lead to
unacceptable performance.
Linchi
"hongo32" wrote:

> hi
> we develop a stored procedure that run a dll, the dll consumes all the
> processor, and take a long of time (2 days) processing data.
> its posible limit the amount of cpu uses by a single stored procedure?
> i know that using the windows system resource manager its posible limit
> the amount of cpu uses by all sql server, but it's possible for only a
> stored procedure?
> thanks
>

limit the resources of a job

hi
we develop a stored procedure that run a dll, the dll consumes all the
processor, and take a long of time (2 days) processing data.
its posible limit the amount of cpu uses by a single stored procedure?
i know that using the windows system resource manager its posible limit
the amount of cpu uses by all sql server, but it's possible for only a
stored procedure?
thanks
SQL Server can't control what your external process does. You said it
is the DLL and not the proc that consumes your resources.
Two days is an extremely long time to execute a proc. Why not invoke
your code from outside SQL Server using .NET or VB or something else?
That way you may be able to add some code to monitor and control what
happens during processing.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Alternatively, you may try to relax the grip on the processor (e.g. by not
using tight loops) in the code of the DLL if that doesn't lead to
unacceptable performance.
Linchi
"hongo32" wrote:

> hi
> we develop a stored procedure that run a dll, the dll consumes all the
> processor, and take a long of time (2 days) processing data.
> its posible limit the amount of cpu uses by a single stored procedure?
> i know that using the windows system resource manager its posible limit
> the amount of cpu uses by all sql server, but it's possible for only a
> stored procedure?
> thanks
>

limit the resources of a job

hi
we develop a stored procedure that run a dll, the dll consumes all the
processor, and take a long of time (2 days) processing data.
its posible limit the amount of cpu uses by a single stored procedure?
i know that using the windows system resource manager its posible limit
the amount of cpu uses by all sql server, but it's possible for only a
stored procedure?
thanksSQL Server can't control what your external process does. You said it
is the DLL and not the proc that consumes your resources.
Two days is an extremely long time to execute a proc. Why not invoke
your code from outside SQL Server using .NET or VB or something else?
That way you may be able to add some code to monitor and control what
happens during processing.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Alternatively, you may try to relax the grip on the processor (e.g. by not
using tight loops) in the code of the DLL if that doesn't lead to
unacceptable performance.
Linchi
"hongo32" wrote:
> hi
> we develop a stored procedure that run a dll, the dll consumes all the
> processor, and take a long of time (2 days) processing data.
> its posible limit the amount of cpu uses by a single stored procedure?
> i know that using the windows system resource manager its posible limit
> the amount of cpu uses by all sql server, but it's possible for only a
> stored procedure?
> thanks
>

Limit the Amount of Time for each job

Is there away to place at the beginning of a SQL Server job step the maximum
of amount you would like this step to execute. If the job steps executes
greater than this amount kill the step and go to the next step.
Please help me with this issue.
Thanks,maximum amount of what?
"Joe K." wrote:

> Is there away to place at the beginning of a SQL Server job step the maxim
um
> of amount you would like this step to execute. If the job steps executes
> greater than this amount kill the step and go to the next step.
> Please help me with this issue.
> Thanks,
>|||Fix the step
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:6C8530C7-174C-4E05-88D0-90CA2AD4EF52@.microsoft.com...
> Is there away to place at the beginning of a SQL Server job step the
maximum
> of amount you would like this step to execute. If the job steps executes
> greater than this amount kill the step and go to the next step.
> Please help me with this issue.
> Thanks,
>

Limit the Amount of Time for each job

Is there away to place at the beginning of a SQL Server job step the maximum
of amount you would like this step to execute. If the job steps executes
greater than this amount kill the step and go to the next step.
Please help me with this issue.
Thanks,
maximum amount of what?
"Joe K." wrote:

> Is there away to place at the beginning of a SQL Server job step the maximum
> of amount you would like this step to execute. If the job steps executes
> greater than this amount kill the step and go to the next step.
> Please help me with this issue.
> Thanks,
>
|||Fix the step
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:6C8530C7-174C-4E05-88D0-90CA2AD4EF52@.microsoft.com...
> Is there away to place at the beginning of a SQL Server job step the
maximum
> of amount you would like this step to execute. If the job steps executes
> greater than this amount kill the step and go to the next step.
> Please help me with this issue.
> Thanks,
>

Limit the Amount of Time for each job

Is there away to place at the beginning of a SQL Server job step the maximum
of amount you would like this step to execute. If the job steps executes
greater than this amount kill the step and go to the next step.
Please help me with this issue.
Thanks,maximum amount of what?
"Joe K." wrote:
> Is there away to place at the beginning of a SQL Server job step the maximum
> of amount you would like this step to execute. If the job steps executes
> greater than this amount kill the step and go to the next step.
> Please help me with this issue.
> Thanks,
>|||Fix the step
"Joe K." <JoeK@.discussions.microsoft.com> wrote in message
news:6C8530C7-174C-4E05-88D0-90CA2AD4EF52@.microsoft.com...
> Is there away to place at the beginning of a SQL Server job step the
maximum
> of amount you would like this step to execute. If the job steps executes
> greater than this amount kill the step and go to the next step.
> Please help me with this issue.
> Thanks,
>