JOB TIME (twice a day)..?

Hi,
In our database a job is scheduled to run at 6:30 PM.
I need to run that job twice a day ie., at 12:30 PM & 6:30 PM
Is it possible?
Is there any alternative for this without creating a new job?
Thanks,
Soura
Yes, right click on the agent, select agent properties and have it run every
6 hours, starting at 12:30 pm. ending at 7:00 pm
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:65EADF5F-4D28-4835-A54F-F209FD3422F7@.microsoft.com...
> Hi,
> In our database a job is scheduled to run at 6:30 PM.
> I need to run that job twice a day ie., at 12:30 PM & 6:30 PM
> Is it possible?
> Is there any alternative for this without creating a new job?
> Thanks,
> Soura
|||Thank you Cotter
It's working fine.
Soura.
"Hilary Cotter" wrote:

> Yes, right click on the agent, select agent properties and have it run every
> 6 hours, starting at 12:30 pm. ending at 7:00 pm
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:65EADF5F-4D28-4835-A54F-F209FD3422F7@.microsoft.com...
>
>

Job that runs past the next scheduled start time

Hello group
I have a problem that I'm considering if the SQL Server Agent and Job
scheduling can help me solve.
I need to check some status very often (approximately every 30. sec.). I
have learned that you can get the job sceduling to start this often, so that
should not be a problem.
Normaly the job will just check this status and then stop.
Once in a while the status will signal that further processing should take
place. This processing may last more than 30 seconds, and therefore last
beyond the next start time of the job.
Is this a problem?
Will I have the same job running multiple times? That is not good.
When will the next job then start? Will it start 30 sec. after the long
running job completed, or will jobs queue op while the long running job is
processing so that I will get multiple starts when the first job completes?
I hope that you can understand my issue.
Regards
Anders> Will I have the same job running multiple times?
No. Agent is simple in this regard. If the job is already running when it is supposed to start, then
Agent will *not* start another instance of that job.
> When will the next job then start? Will it start 30 sec. after the long running job completed, or
> will jobs queue op while the long running job is processing so that I will get multiple starts
> when the first job completes?
Neither. It will start when it is supposed to. Say for simplicity of this example that you want to
start it every minute. Now, you have one occasion where it starts at 13:00:00 and it runs for 110
seconds. The job will not start at 13:01:00 since it is already running. As I said, this occasion
takes 110 seconds so it finishes at 13:01:50. Next time it is supposed to start is 13:02:00, and now
it isn't running so Agent will start it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Anders K. Olsen" <akol_dk@.hotmail.com> wrote in message
news:OM4PQuc%23HHA.700@.TK2MSFTNGP05.phx.gbl...
> Hello group
> I have a problem that I'm considering if the SQL Server Agent and Job scheduling can help me
> solve.
> I need to check some status very often (approximately every 30. sec.). I have learned that you can
> get the job sceduling to start this often, so that should not be a problem.
> Normaly the job will just check this status and then stop.
> Once in a while the status will signal that further processing should take place. This processing
> may last more than 30 seconds, and therefore last beyond the next start time of the job.
> Is this a problem?
> Will I have the same job running multiple times? That is not good.
> When will the next job then start? Will it start 30 sec. after the long running job completed, or
> will jobs queue op while the long running job is processing so that I will get multiple starts
> when the first job completes?
> I hope that you can understand my issue.
> Regards
> Anders
>|||Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23GdwCgd%23HHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Will I have the same job running multiple times?
> No. Agent is simple in this regard. If the job is already running when it
> is supposed to start, then Agent will *not* start another instance of that
> job.
Sometimes simple is good :-)
>> When will the next job then start? Will it start 30 sec. after the long
>> running job completed, or will jobs queue op while the long running job
>> is processing so that I will get multiple starts when the first job
>> completes?
> Neither. It will start when it is supposed to. Say for simplicity of this
> example that you want to start it every minute. Now, you have one occasion
> where it starts at 13:00:00 and it runs for 110 seconds. The job will not
> start at 13:01:00 since it is already running. As I said, this occasion
> takes 110 seconds so it finishes at 13:01:50. Next time it is supposed to
> start is 13:02:00, and now it isn't running so Agent will start it.
This is fine for our purposes, so we will continue along this line.
Thank you for your help.
Regards
Anderssql

Job Scheduler

Hi all
When I run a stored procedure using the job scheduler in
SQL 2000 it takes more time to complete comparing when I
run it in the Query analyser.
Is there any reason behind this?
Example
I am running a stored Procedure for generating a report
every one minute using a job scheduler.
It takes 13 seconds to complete.
Whereas when I run the same SP in Query Analyser it takes
only 2 sec or 3 seconds.
Please clarify.
Thanks in advance
AnandThis could be the problem:
http://support.microsoft.com/defaul...730&Product=sql
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Anand" <gurusanand@.yahoo.com> wrote in message
news:024101c3db49$baff3990$a001280a@.phx.gbl...
Hi all
When I run a stored procedure using the job scheduler in
SQL 2000 it takes more time to complete comparing when I
run it in the Query analyser.
Is there any reason behind this?
Example
I am running a stored Procedure for generating a report
every one minute using a job scheduler.
It takes 13 seconds to complete.
Whereas when I run the same SP in Query Analyser it takes
only 2 sec or 3 seconds.
Please clarify.
Thanks in advance
Anand

Job Scheduler

Hi all
When I run a stored procedure using the job scheduler in
SQL 2000 it takes more time to complete comparing when I
run it in the Query analyser.
Is there any reason behind this?
Example
I am running a stored Procedure for generating a report
every one minute using a job scheduler.
It takes 13 seconds to complete.
Whereas when I run the same SP in Query Analyser it takes
only 2 sec or 3 seconds.
Please clarify.
Thanks in advance
AnandThis could be the problem:
http://support.microsoft.com/default.aspx?scid=kb;en-us;249730&Product=sql
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Anand" <gurusanand@.yahoo.com> wrote in message
news:024101c3db49$baff3990$a001280a@.phx.gbl...
Hi all
When I run a stored procedure using the job scheduler in
SQL 2000 it takes more time to complete comparing when I
run it in the Query analyser.
Is there any reason behind this?
Example
I am running a stored Procedure for generating a report
every one minute using a job scheduler.
It takes 13 seconds to complete.
Whereas when I run the same SP in Query Analyser it takes
only 2 sec or 3 seconds.
Please clarify.
Thanks in advance
Anand|||Do you SET NOCOUNT ON at the beginning of your stored
procedure? Sometimes this can help with this type of
problem.
Matthew Bando
BandoM@.CSCTGI(remove this).com
>--Original Message--
>Hi all
>When I run a stored procedure using the job scheduler in
>SQL 2000 it takes more time to complete comparing when I
>run it in the Query analyser.
>Is there any reason behind this?
>Example
>I am running a stored Procedure for generating a report
>every one minute using a job scheduler.
>It takes 13 seconds to complete.
>Whereas when I run the same SP in Query Analyser it
takes
>only 2 sec or 3 seconds.
>Please clarify.
>Thanks in advance
>Anand
>.
>

Job Schedule.

Hi to all,
does anyone knows how to schedule a job to run in seconds time interval ?
I have found the sysobschedule table in msdb but even if i change the
freq_subday_type to 2 (seconds) the job doesn't run in seconds.
The only solution i imagine is to run once the Stored Procedure i want
and add a WAITFOR DELAY '00:00.05' command,
so this will run it constantly (and for ever...)
Is there any other way to accomplish the same thing,
or are there any side effects to have an sp to run forever
with the WAITFOR statement '
Thanks a lot for any help!
Anthony BoudouvasGert Drapers posted an undocumented way around this
limitation awhile back- it sounds like you had the right
idea on how to go about it, maybe something just got missed
in how you implemented it. Check the following example:
http://tinyurl.com/q0vv
-Sue
On Tue, 7 Oct 2003 13:08:59 +0200, "Anthony Boudouvas"
<anthonyb@.mediatel.gr> wrote:
>Hi to all,
>does anyone knows how to schedule a job to run in seconds time interval ?
>I have found the sysobschedule table in msdb but even if i change the
>freq_subday_type to 2 (seconds) the job doesn't run in seconds.
>The only solution i imagine is to run once the Stored Procedure i want
>and add a WAITFOR DELAY '00:00.05' command,
>so this will run it constantly (and for ever...)
>Is there any other way to accomplish the same thing,
>or are there any side effects to have an sp to run forever
>with the WAITFOR statement '
>
>Thanks a lot for any help!
>Anthony Boudouvas
>|||Ok, it did work that way.
Thanks a lot!
anthonyb
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:2aj5ov8bk7klsmgf79hgn70285g8fk112i@.4ax.com...
> Gert Drapers posted an undocumented way around this
> limitation awhile back- it sounds like you had the right
> idea on how to go about it, maybe something just got missed
> in how you implemented it. Check the following example:
> http://tinyurl.com/q0vv
> -Sue
> On Tue, 7 Oct 2003 13:08:59 +0200, "Anthony Boudouvas"
> <anthonyb@.mediatel.gr> wrote:
> >Hi to all,
> >
> >does anyone knows how to schedule a job to run in seconds time interval ?
> >I have found the sysobschedule table in msdb but even if i change the
> >freq_subday_type to 2 (seconds) the job doesn't run in seconds.
> >
> >The only solution i imagine is to run once the Stored Procedure i want
> >and add a WAITFOR DELAY '00:00.05' command,
> >so this will run it constantly (and for ever...)
> >
> >Is there any other way to accomplish the same thing,
> >or are there any side effects to have an sp to run forever
> >with the WAITFOR statement '
> >
> >
> >Thanks a lot for any help!
> >
> >Anthony Boudouvas
> >
>

Job runs for a long time in SQL Server Agent 2005

I have an exe in C# in .Net 1.1. This accesses an Oracle database and an SQL Server 2000 database. It is scheduled in SQL Server Agent. It runs for normally 30 seconds and stops

Recently we migrated the database to SQL Server 2005. We created this job manually in SQL Server Agent. Even after running for 3 minutes it never stops (abnormal)

However this exe runs normally in the Windows 2003 scheduler and the command prompt.

Am stumped for an answer.

Any help

Thanks and regards

jarathna

Do have relevant oracle client drivers installed on this SQL 2005 instance?

Ibelieve this is something related to the Operating system related process, if this is scheduled via SQLagent job then ensure SQLserver serivces do have relevant privileges on that C# & Oracle based components share.

Job Run Time Notifications

Is there a way to send an email alert if a dts job runs
for more than a specific time. For example, I want to
send an alert if a job runs for more than 2 minutes.As the first step of your DTS job you could start another job that simply
waits 2 minutes (waitfor delay '00:02:00') and then checks the execution
status of the DTS job (you could use sp_help_job). It could then alert if
the job was still running (assuming the DTS job wouldn't start again before
the monitor finished). I'm sure there's a slicker way but that would be
relatively simple and robust
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Tim Savery" <Tim.Savery@.mbna.com> wrote in message
news:05a701c3a2df$b7115900$a001280a@.phx.gbl...
Is there a way to send an email alert if a dts job runs
for more than a specific time. For example, I want to
send an alert if a job runs for more than 2 minutes.|||To be sure whether the job is still in the current execution, you can
execute msdb..sp_help_job before the wait, and then check execute
sp_help_job and check last_run_date and last_run_time. If the job has been
restarted, these two values would have been changed.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:eiBPqZxoDHA.360@.TK2MSFTNGP12.phx.gbl...
> As the first step of your DTS job you could start another job that simply
> waits 2 minutes (waitfor delay '00:02:00') and then checks the execution
> status of the DTS job (you could use sp_help_job). It could then alert if
> the job was still running (assuming the DTS job wouldn't start again
before
> the monitor finished). I'm sure there's a slicker way but that would be
> relatively simple and robust
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Tim Savery" <Tim.Savery@.mbna.com> wrote in message
> news:05a701c3a2df$b7115900$a001280a@.phx.gbl...
> Is there a way to send an email alert if a dts job runs
> for more than a specific time. For example, I want to
> send an alert if a job runs for more than 2 minutes.
>

Job History is not showing

Using Sql 2005, I have a job that will not display job history when it runs
at the schedule time. It will show job history if you manually run the job.
Any thoughts?
Are you sure that the history settings are not such that it gets purged by
the time you view it?
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
> Using Sql 2005, I have a job that will not display job history when it
> runs
> at the schedule time. It will show job history if you manually run the
> job.
> Any thoughts?
|||The Limit Size of Job History Log is checked with Max Log size of 1000 and
Max Job history is 105. When I looked at the job history for all it showed
only 212 entries.
This particular server is the distributor. The only jobs on the server that
do show history deal with the replication.
"Andrew J. Kelly" wrote:

> Are you sure that the history settings are not such that it gets purged by
> the time you view it?
> --
> Andrew J. Kelly SQL MVP
> "Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
> news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
>
>
|||OK my guess is that the history limit is beeing exceeded by one of the jobs
that runs say every minute or so. You can try bumping up the history limits
but it depends on how often the other jobs run.
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:A1B7DE7A-B3CD-4C05-B971-46A00161624B@.microsoft.com...[vbcol=seagreen]
> The Limit Size of Job History Log is checked with Max Log size of 1000 and
> Max Job history is 105. When I looked at the job history for all it
> showed
> only 212 entries.
> This particular server is the distributor. The only jobs on the server
> that
> do show history deal with the replication.
> "Andrew J. Kelly" wrote:

Job History is not showing

Using Sql 2005, I have a job that will not display job history when it runs
at the schedule time. It will show job history if you manually run the job.
Any thoughts?Are you sure that the history settings are not such that it gets purged by
the time you view it?
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
> Using Sql 2005, I have a job that will not display job history when it
> runs
> at the schedule time. It will show job history if you manually run the
> job.
> Any thoughts?|||The Limit Size of Job History Log is checked with Max Log size of 1000 and
Max Job history is 105. When I looked at the job history for all it showed
only 212 entries.
This particular server is the distributor. The only jobs on the server that
do show history deal with the replication.
"Andrew J. Kelly" wrote:

> Are you sure that the history settings are not such that it gets purged by
> the time you view it?
> --
> Andrew J. Kelly SQL MVP
> "Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
> news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
>
>|||OK my guess is that the history limit is beeing exceeded by one of the jobs
that runs say every minute or so. You can try bumping up the history limits
but it depends on how often the other jobs run.
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:A1B7DE7A-B3CD-4C05-B971-46A00161624B@.microsoft.com...[vbcol=seagreen]
> The Limit Size of Job History Log is checked with Max Log size of 1000 and
> Max Job history is 105. When I looked at the job history for all it
> showed
> only 212 entries.
> This particular server is the distributor. The only jobs on the server
> that
> do show history deal with the replication.
> "Andrew J. Kelly" wrote:
>

Job History is not showing

Using Sql 2005, I have a job that will not display job history when it runs
at the schedule time. It will show job history if you manually run the job.
Any thoughts?Are you sure that the history settings are not such that it gets purged by
the time you view it?
--
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
> Using Sql 2005, I have a job that will not display job history when it
> runs
> at the schedule time. It will show job history if you manually run the
> job.
> Any thoughts?|||The Limit Size of Job History Log is checked with Max Log size of 1000 and
Max Job history is 105. When I looked at the job history for all it showed
only 212 entries.
This particular server is the distributor. The only jobs on the server that
do show history deal with the replication.
"Andrew J. Kelly" wrote:
> Are you sure that the history settings are not such that it gets purged by
> the time you view it?
> --
> Andrew J. Kelly SQL MVP
> "Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
> news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
> > Using Sql 2005, I have a job that will not display job history when it
> > runs
> > at the schedule time. It will show job history if you manually run the
> > job.
> >
> > Any thoughts?
>
>|||OK my guess is that the history limit is beeing exceeded by one of the jobs
that runs say every minute or so. You can try bumping up the history limits
but it depends on how often the other jobs run.
--
Andrew J. Kelly SQL MVP
"Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
news:A1B7DE7A-B3CD-4C05-B971-46A00161624B@.microsoft.com...
> The Limit Size of Job History Log is checked with Max Log size of 1000 and
> Max Job history is 105. When I looked at the job history for all it
> showed
> only 212 entries.
> This particular server is the distributor. The only jobs on the server
> that
> do show history deal with the replication.
> "Andrew J. Kelly" wrote:
>> Are you sure that the history settings are not such that it gets purged
>> by
>> the time you view it?
>> --
>> Andrew J. Kelly SQL MVP
>> "Cindy B" <CindyB@.discussions.microsoft.com> wrote in message
>> news:EE87736D-8DC0-4646-B67F-FF2477DF8ABA@.microsoft.com...
>> > Using Sql 2005, I have a job that will not display job history when it
>> > runs
>> > at the schedule time. It will show job history if you manually run the
>> > job.
>> >
>> > Any thoughts?
>>sql

job execution Time

i observed a strange problem in my production setup. i have a job which updates usage metrics (for reporting) which is scheduled to run once in a day. (the job invokes an sp to do this. the sp refers two tables to retrieve/update information, say TableA and TableB).

the job normally takes an average of 25 seconds to complete. all of a sudden the job execution time increased to 6 minutes and 52 seconds. now, the average job execution time is 8 minutes. there is no table/sp change in the DB

the only thing i observed is that one of the tables referred by the sp has 30,000 records added to it, on the day from which the job execution time increaed to 6 minutes.

i have updated the statistics on the Table, but the execution time remains unchanged. can any one suggest any possible causes for such a scenario.

i expect a few hints with which i can explore my production DB and find out the causes for the increased execution time for the sp.

Pl discuss...

Thanks in advancethe only thing i observed is that one of the tables referred by the sp has 30,000 records added to it, on the day from which the job execution time increaed to 6 minutes.


I think you may have answered your own question! If the columns that your stored procedures are referencing are not indexed, they will likely have to perform table scans. You mentioned that 30,000 rowd were added...how many did you start off with.

Have you tried generating an execution plan using Query Analyzer? That will give you a good indicaton of where the problem lies.

Job Does Not Execute Correctly

Hi,
As you say u execute job every 1 minute, What does the job
do and what is the expected time period of the job
completion.
Regards
Thirumal
www.thirumal.com

>--Original Message--
>Hi everyone,
>I have a management job which runs every minute
automatically. However recently I discovered that the
result was not executed correctly even the job history
tells me it runs successfully. On the other hand, the
result was executed correctly (i.e. what I expect) when I
try to start the job manually.
>Any idea what issue does it related to? I doubt if it is
related to any security context but cannot prove it yet.
Thanks for any idea.
>.
>
Thirumal,
Thanks for your reply. The job executes within just few seconds, wont' be too long.
The job retreives several records from tables and then insert records into another table based on a if-else condition.
The code is like this:
DECLARE @.thisMin varchar(25)
DECLARE @.table_no varchar(2), @.table_tmp varchar(2)
SET @.thisMin = GETDATE()
SET @.table_no = DATEDIFF(dd, ''01/01/01'', @.thisMin) % 36
SET @.table_tmp = DATEDIFF(dd, ''01/01/01'', DATEADD(dd, -1, @.thisMin)) % 36
EXEC(''
DECLARE temp_cur CURSOR FOR
SELECT DISTINCT alertId FROM z_EventHistoryD''+@.table_no+'' WHERE
DATEDIFF(mi, endTime, ''''''+@.thisMin+'''''') = 0 OR
DATEDIFF(mi, endTime, ''''''+@.thisMin+'''''') = 1
GROUP BY alertId, severitySortOrder
HAVING COUNT(alertId) = 1
DECLARE @.alertId int, @.thisSSO int, @.lastSSO int, @.auditTime datetime
OPEN temp_cur
FETCH NEXT FROM temp_cur INTO @.alertId
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.thisSSO = NULL
SET @.lastSSO = NULL
SET @.auditTime = NULL
IF DATEPART(hh, ''''''+@.thisMin+'''''')=0 AND DATEPART(mi, ''''''+@.thisMin+'''''')=0
SELECT @.lastSSO = severitySortOrder, @.auditTime = endTime
FROM z_EventHistoryD''+@.table_tmp+'' WHERE alertId = @.alertId
AND DATEDIFF(mi, endTime, ''''''+@.thisMin+'''''') = 1
ELSE
SELECT @.lastSSO = severitySortOrder, @.auditTime = endTime
FROM z_EventHistoryD''+@.table_no+'' WHERE alertId = @.alertId
AND DATEDIFF(mi, endTime, ''''''+@.thisMin+'''''') = 1
SELECT @.thisSSO = severitySortOrder, @.auditTime = endTime
FROM z_EventHistoryD''+@.table_no+'' WHERE alertId = @.alertId
AND DATEDIFF(mi, endTime, ''''''+@.thisMin+'''''') = 0
IF (@.lastSSO IS NULL)
--PRINT ''''-- NEW EVENT''''
INSERT INTO alertAudit VALUES (@.alertId, NULL, @.thisSSO, 1, @.auditTime)
ELSE
IF (@.thisSSO IS NULL)
--PRINT ''''-- ENDING EVENT''''
INSERT INTO alertAudit
SELECT alertId, @.lastSSO, NULL, -1, clearTime FROM alertHistory WHERE alertId = @.alertId
ELSE
IF (@.thisSSO <> @.lastSSO)
--PRINT ''''-- CHANGE SEVERITY EVENT''''
INSERT INTO alertAudit VALUES (@.alertId, @.lastSSO, @.thisSSO, 2, @.auditTime)
ELSE
IF (@.thisSSO = @.lastSSO)
PRINT ''''-- UNCHANGED SEVERITY''''
ELSE
PRINT ''''-- UNKNOWN ERROR''''
/*--DEBUG
PRINT @.alertId
PRINT @.auditTime
PRINT @.lastSSO
PRINT @.thisSSO
--DEBUG*/
FETCH NEXT FROM temp_cur INTO @.alertId
END
CLOSE temp_cur
DEALLOCATE temp_cur
'')
Thanks again
"Thirumal" wrote:

> Hi,
> As you say u execute job every 1 minute, What does the job
> do and what is the expected time period of the job
> completion.
> Regards
> Thirumal
> www.thirumal.com
> automatically. However recently I discovered that the
> result was not executed correctly even the job history
> tells me it runs successfully. On the other hand, the
> result was executed correctly (i.e. what I expect) when I
> try to start the job manually.
> related to any security context but cannot prove it yet.
> Thanks for any idea.
>

Job Does Not Execute Correctly

Hi,
As you say u execute job every 1 minute, What does the job
do and what is the expected time period of the job
completion.
Regards
Thirumal
www.thirumal.com

>--Original Message--
>Hi everyone,
>I have a management job which runs every minute
automatically. However recently I discovered that the
result was not executed correctly even the job history
tells me it runs successfully. On the other hand, the
result was executed correctly (i.e. what I expect) when I
try to start the job manually.
>Any idea what issue does it related to? I doubt if it is
related to any security context but cannot prove it yet.
Thanks for any idea.
>.
>Thirumal,
Thanks for your reply. The job executes within just few seconds, wont' be t
oo long.
The job retreives several records from tables and then insert records into a
nother table based on a if-else condition.
The code is like this:
DECLARE @.thisMin varchar(25)
DECLARE @.table_no varchar(2), @.table_tmp varchar(2)
SET @.thisMin = GETDATE()
SET @.table_no = DATEDIFF(dd, ''01/01/01'', @.thisMin) % 36
SET @.table_tmp = DATEDIFF(dd, ''01/01/01'', DATEADD(dd, -1, @.thisMin)) % 36
EXEC(''
DECLARE temp_cur CURSOR FOR
SELECT DISTINCT alertId FROM z_EventHistoryD''+@.table_no+'' WHERE
DATEDIFF(mi, endTime, '''+@.thisMin+''') = 0 OR
DATEDIFF(mi, endTime, '''+@.thisMin+''') = 1
GROUP BY alertId, severitySortOrder
HAVING COUNT(alertId) = 1
DECLARE @.alertId int, @.thisSSO int, @.lastSSO int, @.auditTime datetime
OPEN temp_cur
FETCH NEXT FROM temp_cur INTO @.alertId
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.thisSSO = NULL
SET @.lastSSO = NULL
SET @.auditTime = NULL
IF DATEPART(hh, '''+@.thisMin+''')=0 AND DATEPART(mi, '''+@.thisMin+'
'')=0
SELECT @.lastSSO = severitySortOrder, @.auditTime = endTime
FROM z_EventHistoryD''+@.table_tmp+'' WHERE alertId = @.alertId
AND DATEDIFF(mi, endTime, '''+@.thisMin+''') = 1
ELSE
SELECT @.lastSSO = severitySortOrder, @.auditTime = endTime
FROM z_EventHistoryD''+@.table_no+'' WHERE alertId = @.alertId
AND DATEDIFF(mi, endTime, '''+@.thisMin+''') = 1
SELECT @.thisSSO = severitySortOrder, @.auditTime = endTime
FROM z_EventHistoryD''+@.table_no+'' WHERE alertId = @.alertId
AND DATEDIFF(mi, endTime, '''+@.thisMin+''') = 0
IF (@.lastSSO IS NULL)
--PRINT ''''-- NEW EVENT''''
INSERT INTO alertAudit VALUES (@.alertId, NULL, @.thisSSO, 1, @.auditTime)
ELSE
IF (@.thisSSO IS NULL)
--PRINT ''''-- ENDING EVENT''''
INSERT INTO alertAudit
SELECT alertId, @.lastSSO, NULL, -1, clearTime FROM alertHistory WHERE alertI
d = @.alertId
ELSE
IF (@.thisSSO <> @.lastSSO)
--PRINT ''''-- CHANGE SEVERITY EVENT''''
INSERT INTO alertAudit VALUES (@.alertId, @.lastSSO, @.thisSSO, 2, @.auditTime)
ELSE
IF (@.thisSSO = @.lastSSO)
PRINT ''''-- UNCHANGED SEVERITY''''
ELSE
PRINT ''''-- UNKNOWN ERROR''''
/*--DEBUG
PRINT @.alertId
PRINT @.auditTime
PRINT @.lastSSO
PRINT @.thisSSO
--DEBUG*/
FETCH NEXT FROM temp_cur INTO @.alertId
END
CLOSE temp_cur
DEALLOCATE temp_cur
'')
Thanks again
"Thirumal" wrote:

> Hi,
> As you say u execute job every 1 minute, What does the job
> do and what is the expected time period of the job
> completion.
> Regards
> Thirumal
> www.thirumal.com
>
> automatically. However recently I discovered that the
> result was not executed correctly even the job history
> tells me it runs successfully. On the other hand, the
> result was executed correctly (i.e. what I expect) when I
> try to start the job manually.
> related to any security context but cannot prove it yet.
> Thanks for any idea.
>

Jet.OLEDB.4.0 and Linked Servers (SQL standard 2005 on Win2003 SP1) - solution

I have been trying to figure out for some time why Linked Servers do not continue to run. I start up my SQL Server Database service, and then using SQL Server Management Studio I create one or more linked servers to Microsoft Access Databases using the Jet Provider. I then run some queries to that access those linked servers without any problems. (One currently is called 'THE')

After some time has passed, possibly minutes, hours or days, I can no longer access any of the linked servers. No data, queries, tables, or anything is accessible to me anymore. I use this command "EXEC sp_testlinkedserver N'THE' " to test the linked server and get the following error msg.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE".

The only solution I have to fix this currently is to restart the SQL Server Database service. I have tried deleting and recreating the Linked server before doing this to no avail. I have also used the FileMon and RegMon tools on the server to try to determine the problem but have no no success. After restarting the SQL service I can run the "EXEC sp_testlinkedserver N'THE' " command successfully "Command(s) completed successfully." and can access the linked servers again without any problems.

Any help with this is greatly appreciated.

Mark.

P.S. I have tried changing the security options on the linked server from "Be made without a context." to using the "Admin" username and leaving the password blank, to several other possible variations and options, as well as setting up the system.mdw database in the registry to no avail.

This issue has been reported fixed with Sql Server 2005 SP1.

You can install SP1 from http://www.microsoft.com/downloads/details.aspx?FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&DisplayLang=en.

-Curtis

|||

I was unable to find any documentation that said this problem was fixed. Can you please direct me to KB article that describes this fix as our company documentation requires this information.

Any information that you can provide would be helpful.

Thank You

Mark

|||

hi mark,

I think a sharing violation took place in your access DB.

as we all know access database is not as roboust as sql server,

another user could have get in and use the Access database

locking and preventing sql server to use it.

To prevent this from happening make sure that no ther user is accesing the access mdb.

regards,

joey

|||

I had thought I had Service Pack 1 installed, however, I discovered it had not yet been applied. It did appear to be fixed after applying SP1, however it has shown up again.

Now it is entirely possible that another user has accessed the Access DB, but I can guarantee you that no-one was accessing it while I tried to use the linked server, and definitely no-one else has used that linked server.

Also just to note, it gives me the exact same error I had gotten before the Service Pack was applied. This sounds to me like a very bad sign for anyone who uses linked servers.

|||Does anyone know or have an idea what causes this. I never had this problem when I used SQL Server 2000.|||

Seradex wrote:

Does anyone know or have an idea what causes this. I never had this problem when I used SQL Server 2000.

I have the same problem with a linked server on a file, but for me with "SQL authentication" (sa) is ok but not with "Windows authentication".

|||

My 2005 server has just done this for the first time! I have found it happens about every other month between Sql 2000 servers. Every sp that has "promised" to fix it has not. I have also found that on the server you loose one, you loose them all. I have multiple connections to different types of data sources and they all stop simultaneously.

The comment about record locking is along the right track I think. I have found that OLE DB connectivity can fail at network level and can cause it.

|||This is a bug using the Sql Management Studio.
I've found a workaround, described at
http://www.sql-und-xml.de/sql-praxis/legacy-daten-import-in-ein-zwei-server-system.html
at the end.|||

Thank you for the idea. I will try this ASAP.

Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)

Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

He suggests following these steps:

Check Disallow AdHoc Access, this will create the right key with the wrong value (1 instead of the correct 0). Unchecking this option will remove the key and the value, this is interpreted as 'value = 1'. This 'Removing' in combination with the standard-value 1 (if no key exists) is the bug.|||

Seradex wrote:

Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)

Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

Now I've found the 'bug' as explicit code.

The master database contains a stored procedure called from the Sql-Server-Management-Studio

[sys].[sp_MSset_oledb_prop]

At the end, there is the code:

-

if 1 = @.property_value

begin

declare @.val int

set @.val = @.property_value

exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', @.regpath, @.property_name, REG_DWORD, @.val

end

else

begin

exec xp_instance_regdeletevalue N'HKEY_LOCAL_MACHINE', @.regpath, @.property_name

end

-

@.property_value is called with 0 (I used the Sql-Profiler). So the key is deleted.

The procedure can be found using the following query:

Select name from master.dbo.sysobjects a inner join master.dbo.syscomments b
on a.id = b.id where b.text like '%DisallowAdHocAccess%'

|||

I am getting this error when using VS2005 to "run" a modification to a stored procedure that references an Access database via a Linked Server. I tried the fix above but it still will not run. I can run a query against the Linked server using MS SS Management Studio without problem.

Anyone have any suggestions?

BTW: I have SP1 installed and have connection be made without a security context checked.

Closing and re-opening VS2005 does not help.

I copied the stored procedure source code to a window in MS SS Management Studio and executed it. It ran ok there.

|||

I tried applying this fix as described and it does not seem to resolve the problem.

The Linked Servers on our SQL 2005 Server still stop working after a short amount of time. I applied the fix a while back, but was unable to test until recently. I first thought I had forgotten to restart the SQL service, so I restarted the service. After that it only worked for a short time like before. Because of that I thought that perhaps I had applied the fix incorrectly so I tried again, and again it stopped working after a short period of time.

This is not good. Are any Microsoft techs looking at this issue, because it needs to be fixed soon?
It is not good that the SQL Server service needs to be restarted each time this problem occurs.

|||

I met the same problem, at last I have it done:

1: don't use mapped drive, use network path like: //machine/sharefolder/filename

2: put machine name to the trust site in IE

|||I first started trying to use an Access database as a linked server from SQL Server 2005 Express. It works fine from SSMSEE whether I have the MDB file open with Access or one of our legacy VB applications. From a C# application, it works if nothing else has the database open. If the MDB file is open, the C# app gets the "Cannot initialize data source object" message. Closing the database is enough to make it start working from the C# application. I don't have to restart SQL Server. I tried reverting back to MSDE and the linked server works from the C# app even if Access has the MDB file open. Apparently SSMSEE talks to the database in a different way than a .NET database object. It would be nice to know what options to set (registry, server settings, C# parameters, etc.) to make a C# app work with SS2K5 when it's not the only thing that wants to talk to the database. If SSMSEE can do it, why shouldn't we be able to ?

Jet.OLEDB.4.0 and Linked Servers (SQL standard 2005 on Win2003 SP1)

I have been trying to figure out for some time why Linked Servers do not continue to run. I start up my SQL Server Database service, and then using SQL Server Management Studio I create one or more linked servers to Microsoft Access Databases using the Jet Provider. I then run some queries to that access those linked servers without any problems. (One currently is called 'THE')

After some time has passed, possibly minutes, hours or days, I can no longer access any of the linked servers. No data, queries, tables, or anything is accessible to me anymore. I use this command "EXEC sp_testlinkedserver N'THE' " to test the linked server and get the following error msg.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE".

The only solution I have to fix this currently is to restart the SQL Server Database service. I have tried deleting and recreating the Linked server before doing this to no avail. I have also used the FileMon and RegMon tools on the server to try to determine the problem but have no no success. After restarting the SQL service I can run the "EXEC sp_testlinkedserver N'THE' " command successfully "Command(s) completed successfully." and can access the linked servers again without any problems.

Any help with this is greatly appreciated.

Mark.

P.S. I have tried changing the security options on the linked server from "Be made without a context." to using the "Admin" username and leaving the password blank, to several other possible variations and options, as well as setting up the system.mdw database in the registry to no avail.

This issue has been reported fixed with Sql Server 2005 SP1.

You can install SP1 from http://www.microsoft.com/downloads/details.aspx?FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&DisplayLang=en.

-Curtis

|||

I was unable to find any documentation that said this problem was fixed. Can you please direct me to KB article that describes this fix as our company documentation requires this information.

Any information that you can provide would be helpful.

Thank You

Mark

|||

hi mark,

I think a sharing violation took place in your access DB.

as we all know access database is not as roboust as sql server,

another user could have get in and use the Access database

locking and preventing sql server to use it.

To prevent this from happening make sure that no ther user is accesing the access mdb.

regards,

joey

|||

I had thought I had Service Pack 1 installed, however, I discovered it had not yet been applied. It did appear to be fixed after applying SP1, however it has shown up again.

Now it is entirely possible that another user has accessed the Access DB, but I can guarantee you that no-one was accessing it while I tried to use the linked server, and definitely no-one else has used that linked server.

Also just to note, it gives me the exact same error I had gotten before the Service Pack was applied. This sounds to me like a very bad sign for anyone who uses linked servers.

|||Does anyone know or have an idea what causes this. I never had this problem when I used SQL Server 2000.|||

Seradex wrote:

Does anyone know or have an idea what causes this. I never had this problem when I used SQL Server 2000.

I have the same problem with a linked server on a file, but for me with "SQL authentication" (sa) is ok but not with "Windows authentication".

|||

My 2005 server has just done this for the first time! I have found it happens about every other month between Sql 2000 servers. Every sp that has "promised" to fix it has not. I have also found that on the server you loose one, you loose them all. I have multiple connections to different types of data sources and they all stop simultaneously.

The comment about record locking is along the right track I think. I have found that OLE DB connectivity can fail at network level and can cause it.

|||This is a bug using the Sql Management Studio.
I've found a workaround, described at
http://www.sql-und-xml.de/sql-praxis/legacy-daten-import-in-ein-zwei-server-system.html
at the end.|||

Thank you for the idea. I will try this ASAP.

Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)

Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

He suggests following these steps:

Check Disallow AdHoc Access, this will create the right key with the wrong value (1 instead of the correct 0). Unchecking this option will remove the key and the value, this is interpreted as 'value = 1'. This 'Removing' in combination with the standard-value 1 (if no key exists) is the bug.|||

Seradex wrote:

Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)

Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

Now I've found the 'bug' as explicit code.

The master database contains a stored procedure called from the Sql-Server-Management-Studio

[sys].[sp_MSset_oledb_prop]

At the end, there is the code:

-

if 1 = @.property_value

begin

declare @.val int

set @.val = @.property_value

exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', @.regpath, @.property_name, REG_DWORD, @.val

end

else

begin

exec xp_instance_regdeletevalue N'HKEY_LOCAL_MACHINE', @.regpath, @.property_name

end

-

@.property_value is called with 0 (I used the Sql-Profiler). So the key is deleted.

The procedure can be found using the following query:

Select name from master.dbo.sysobjects a inner join master.dbo.syscomments b
on a.id = b.id where b.text like '%DisallowAdHocAccess%'

|||

I am getting this error when using VS2005 to "run" a modification to a stored procedure that references an Access database via a Linked Server. I tried the fix above but it still will not run. I can run a query against the Linked server using MS SS Management Studio without problem.

Anyone have any suggestions?

BTW: I have SP1 installed and have connection be made without a security context checked.

Closing and re-opening VS2005 does not help.

I copied the stored procedure source code to a window in MS SS Management Studio and executed it. It ran ok there.

|||

I tried applying this fix as described and it does not seem to resolve the problem.

The Linked Servers on our SQL 2005 Server still stop working after a short amount of time. I applied the fix a while back, but was unable to test until recently. I first thought I had forgotten to restart the SQL service, so I restarted the service. After that it only worked for a short time like before. Because of that I thought that perhaps I had applied the fix incorrectly so I tried again, and again it stopped working after a short period of time.

This is not good. Are any Microsoft techs looking at this issue, because it needs to be fixed soon?
It is not good that the SQL Server service needs to be restarted each time this problem occurs.

|||

I met the same problem, at last I have it done:

1: don't use mapped drive, use network path like: //machine/sharefolder/filename

2: put machine name to the trust site in IE

|||I first started trying to use an Access database as a linked server from SQL Server 2005 Express. It works fine from SSMSEE whether I have the MDB file open with Access or one of our legacy VB applications. From a C# application, it works if nothing else has the database open. If the MDB file is open, the C# app gets the "Cannot initialize data source object" message. Closing the database is enough to make it start working from the C# application. I don't have to restart SQL Server. I tried reverting back to MSDE and the linked server works from the C# app even if Access has the MDB file open. Apparently SSMSEE talks to the database in a different way than a .NET database object. It would be nice to know what options to set (registry, server settings, C# parameters, etc.) to make a C# app work with SS2K5 when it's not the only thing that wants to talk to the database. If SSMSEE can do it, why shouldn't we be able to ?

Jet.OLEDB.4.0 and Linked Servers (SQL standard 2005 on Win2003 SP1)

I have been trying to figure out for some time why Linked Servers do not continue to run. I start up my SQL Server Database service, and then using SQL Server Management Studio I create one or more linked servers to Microsoft Access Databases using the Jet Provider. I then run some queries to that access those linked servers without any problems. (One currently is called 'THE')

After some time has passed, possibly minutes, hours or days, I can no longer access any of the linked servers. No data, queries, tables, or anything is accessible to me anymore. I use this command "EXEC sp_testlinkedserver N'THE' " to test the linked server and get the following error msg.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE".

The only solution I have to fix this currently is to restart the SQL Server Database service. I have tried deleting and recreating the Linked server before doing this to no avail. I have also used the FileMon and RegMon tools on the server to try to determine the problem but have no no success. After restarting the SQL service I can run the "EXEC sp_testlinkedserver N'THE' " command successfully "Command(s) completed successfully." and can access the linked servers again without any problems.

Any help with this is greatly appreciated.

Mark.

P.S. I have tried changing the security options on the linked server from "Be made without a context." to using the "Admin" username and leaving the password blank, to several other possible variations and options, as well as setting up the system.mdw database in the registry to no avail.

This issue has been reported fixed with Sql Server 2005 SP1.

You can install SP1 from http://www.microsoft.com/downloads/details.aspx?FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&DisplayLang=en.

-Curtis

|||

I was unable to find any documentation that said this problem was fixed. Can you please direct me to KB article that describes this fix as our company documentation requires this information.

Any information that you can provide would be helpful.

Thank You

Mark

|||

hi mark,

I think a sharing violation took place in your access DB.

as we all know access database is not as roboust as sql server,

another user could have get in and use the Access database

locking and preventing sql server to use it.

To prevent this from happening make sure that no ther user is accesing the access mdb.

regards,

joey

|||

I had thought I had Service Pack 1 installed, however, I discovered it had not yet been applied. It did appear to be fixed after applying SP1, however it has shown up again.

Now it is entirely possible that another user has accessed the Access DB, but I can guarantee you that no-one was accessing it while I tried to use the linked server, and definitely no-one else has used that linked server.

Also just to note, it gives me the exact same error I had gotten before the Service Pack was applied. This sounds to me like a very bad sign for anyone who uses linked servers.

|||Does anyone know or have an idea what causes this. I never had this problem when I used SQL Server 2000.|||

Seradex wrote:

Does anyone know or have an idea what causes this. I never had this problem when I used SQL Server 2000.

I have the same problem with a linked server on a file, but for me with "SQL authentication" (sa) is ok but not with "Windows authentication".

|||

My 2005 server has just done this for the first time! I have found it happens about every other month between Sql 2000 servers. Every sp that has "promised" to fix it has not. I have also found that on the server you loose one, you loose them all. I have multiple connections to different types of data sources and they all stop simultaneously.

The comment about record locking is along the right track I think. I have found that OLE DB connectivity can fail at network level and can cause it.

|||This is a bug using the Sql Management Studio.
I've found a workaround, described at
http://www.sql-und-xml.de/sql-praxis/legacy-daten-import-in-ein-zwei-server-system.html
at the end.|||

Thank you for the idea. I will try this ASAP.

Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)

Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

He suggests following these steps:

Check Disallow AdHoc Access, this will create the right key with the wrong value (1 instead of the correct 0). Unchecking this option will remove the key and the value, this is interpreted as 'value = 1'. This 'Removing' in combination with the standard-value 1 (if no key exists) is the bug.|||

Seradex wrote:

Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)

Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

Now I've found the 'bug' as explicit code.

The master database contains a stored procedure called from the Sql-Server-Management-Studio

[sys].[sp_MSset_oledb_prop]

At the end, there is the code:

-

if 1 = @.property_value

begin

declare @.val int

set @.val = @.property_value

exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', @.regpath, @.property_name, REG_DWORD, @.val

end

else

begin

exec xp_instance_regdeletevalue N'HKEY_LOCAL_MACHINE', @.regpath, @.property_name

end

-

@.property_value is called with 0 (I used the Sql-Profiler). So the key is deleted.

The procedure can be found using the following query:

Select name from master.dbo.sysobjects a inner join master.dbo.syscomments b
on a.id = b.id where b.text like '%DisallowAdHocAccess%'

|||

I am getting this error when using VS2005 to "run" a modification to a stored procedure that references an Access database via a Linked Server. I tried the fix above but it still will not run. I can run a query against the Linked server using MS SS Management Studio without problem.

Anyone have any suggestions?

BTW: I have SP1 installed and have connection be made without a security context checked.

Closing and re-opening VS2005 does not help.

I copied the stored procedure source code to a window in MS SS Management Studio and executed it. It ran ok there.

|||

I tried applying this fix as described and it does not seem to resolve the problem.

The Linked Servers on our SQL 2005 Server still stop working after a short amount of time. I applied the fix a while back, but was unable to test until recently. I first thought I had forgotten to restart the SQL service, so I restarted the service. After that it only worked for a short time like before. Because of that I thought that perhaps I had applied the fix incorrectly so I tried again, and again it stopped working after a short period of time.

This is not good. Are any Microsoft techs looking at this issue, because it needs to be fixed soon?
It is not good that the SQL Server service needs to be restarted each time this problem occurs.

|||

I met the same problem, at last I have it done:

1: don't use mapped drive, use network path like: //machine/sharefolder/filename

2: put machine name to the trust site in IE

|||I first started trying to use an Access database as a linked server from SQL Server 2005 Express. It works fine from SSMSEE whether I have the MDB file open with Access or one of our legacy VB applications. From a C# application, it works if nothing else has the database open. If the MDB file is open, the C# app gets the "Cannot initialize data source object" message. Closing the database is enough to make it start working from the C# application. I don't have to restart SQL Server. I tried reverting back to MSDE and the linked server works from the C# app even if Access has the MDB file open. Apparently SSMSEE talks to the database in a different way than a .NET database object. It would be nice to know what options to set (registry, server settings, C# parameters, etc.) to make a C# app work with SS2K5 when it's not the only thing that wants to talk to the database. If SSMSEE can do it, why shouldn't we be able to ?

Jet.OLEDB.4.0 and Linked Servers (SQL standard 2005 on Win2003 SP1)

I have been trying to figure out for some time why Linked Servers do not continue to run. I start up my SQL Server Database service, and then using SQL Server Management Studio I create one or more linked servers to Microsoft Access Databases using the Jet Provider. I then run some queries to that access those linked servers without any problems. (One currently is called 'THE')

After some time has passed, possibly minutes, hours or days, I can no longer access any of the linked servers. No data, queries, tables, or anything is accessible to me anymore. I use this command "EXEC sp_testlinkedserver N'THE' " to test the linked server and get the following error msg.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE".

The only solution I have to fix this currently is to restart the SQL Server Database service. I have tried deleting and recreating the Linked server before doing this to no avail. I have also used the FileMon and RegMon tools on the server to try to determine the problem but have no no success. After restarting the SQL service I can run the "EXEC sp_testlinkedserver N'THE' " command successfully "Command(s) completed successfully." and can access the linked servers again without any problems.

Any help with this is greatly appreciated.

Mark.

P.S. I have tried changing the security options on the linked server from "Be made without a context." to using the "Admin" username and leaving the password blank, to several other possible variations and options, as well as setting up the system.mdw database in the registry to no avail.

This issue has been reported fixed with Sql Server 2005 SP1.

You can install SP1 from http://www.microsoft.com/downloads/details.aspx?FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&DisplayLang=en.

-Curtis

|||

I was unable to find any documentation that said this problem was fixed. Can you please direct me to KB article that describes this fix as our company documentation requires this information.

Any information that you can provide would be helpful.

Thank You

Mark

|||

hi mark,

I think a sharing violation took place in your access DB.

as we all know access database is not as roboust as sql server,

another user could have get in and use the Access database

locking and preventing sql server to use it.

To prevent this from happening make sure that no ther user is accesing the access mdb.

regards,

joey

|||

I had thought I had Service Pack 1 installed, however, I discovered it had not yet been applied. It did appear to be fixed after applying SP1, however it has shown up again.

Now it is entirely possible that another user has accessed the Access DB, but I can guarantee you that no-one was accessing it while I tried to use the linked server, and definitely no-one else has used that linked server.

Also just to note, it gives me the exact same error I had gotten before the Service Pack was applied. This sounds to me like a very bad sign for anyone who uses linked servers.

|||Does anyone know or have an idea what causes this. I never had this problem when I used SQL Server 2000.|||

Seradex wrote:

Does anyone know or have an idea what causes this. I never had this problem when I used SQL Server 2000.

I have the same problem with a linked server on a file, but for me with "SQL authentication" (sa) is ok but not with "Windows authentication".

|||

My 2005 server has just done this for the first time! I have found it happens about every other month between Sql 2000 servers. Every sp that has "promised" to fix it has not. I have also found that on the server you loose one, you loose them all. I have multiple connections to different types of data sources and they all stop simultaneously.

The comment about record locking is along the right track I think. I have found that OLE DB connectivity can fail at network level and can cause it.

|||This is a bug using the Sql Management Studio.
I've found a workaround, described at
http://www.sql-und-xml.de/sql-praxis/legacy-daten-import-in-ein-zwei-server-system.html
at the end.|||

Thank you for the idea. I will try this ASAP.

Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)

Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

He suggests following these steps:

Check Disallow AdHoc Access, this will create the right key with the wrong value (1 instead of the correct 0). Unchecking this option will remove the key and the value, this is interpreted as 'value = 1'. This 'Removing' in combination with the standard-value 1 (if no key exists) is the bug.|||

Seradex wrote:

Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)

Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

Now I've found the 'bug' as explicit code.

The master database contains a stored procedure called from the Sql-Server-Management-Studio

[sys].[sp_MSset_oledb_prop]

At the end, there is the code:

-

if 1 = @.property_value

begin

declare @.val int

set @.val = @.property_value

exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', @.regpath, @.property_name, REG_DWORD, @.val

end

else

begin

exec xp_instance_regdeletevalue N'HKEY_LOCAL_MACHINE', @.regpath, @.property_name

end

-

@.property_value is called with 0 (I used the Sql-Profiler). So the key is deleted.

The procedure can be found using the following query:

Select name from master.dbo.sysobjects a inner join master.dbo.syscomments b
on a.id = b.id where b.text like '%DisallowAdHocAccess%'

|||

I am getting this error when using VS2005 to "run" a modification to a stored procedure that references an Access database via a Linked Server. I tried the fix above but it still will not run. I can run a query against the Linked server using MS SS Management Studio without problem.

Anyone have any suggestions?

BTW: I have SP1 installed and have connection be made without a security context checked.

Closing and re-opening VS2005 does not help.

I copied the stored procedure source code to a window in MS SS Management Studio and executed it. It ran ok there.

|||

I tried applying this fix as described and it does not seem to resolve the problem.

The Linked Servers on our SQL 2005 Server still stop working after a short amount of time. I applied the fix a while back, but was unable to test until recently. I first thought I had forgotten to restart the SQL service, so I restarted the service. After that it only worked for a short time like before. Because of that I thought that perhaps I had applied the fix incorrectly so I tried again, and again it stopped working after a short period of time.

This is not good. Are any Microsoft techs looking at this issue, because it needs to be fixed soon?
It is not good that the SQL Server service needs to be restarted each time this problem occurs.

|||

I met the same problem, at last I have it done:

1: don't use mapped drive, use network path like: //machine/sharefolder/filename

2: put machine name to the trust site in IE

|||I first started trying to use an Access database as a linked server from SQL Server 2005 Express. It works fine from SSMSEE whether I have the MDB file open with Access or one of our legacy VB applications. From a C# application, it works if nothing else has the database open. If the MDB file is open, the C# app gets the "Cannot initialize data source object" message. Closing the database is enough to make it start working from the C# application. I don't have to restart SQL Server. I tried reverting back to MSDE and the linked server works from the C# app even if Access has the MDB file open. Apparently SSMSEE talks to the database in a different way than a .NET database object. It would be nice to know what options to set (registry, server settings, C# parameters, etc.) to make a C# app work with SS2K5 when it's not the only thing that wants to talk to the database. If SSMSEE can do it, why shouldn't we be able to ?

Jet.OLEDB.4.0 and Linked Servers (SQL standard 2005 on Win2003 SP1)

I have been trying to figure out for some time why Linked Servers do not continue to run. I start up my SQL Server Database service, and then using SQL Server Management Studio I create one or more linked servers to Microsoft Access Databases using the Jet Provider. I then run some queries to that access those linked servers without any problems. (One currently is called 'THE')

After some time has passed, possibly minutes, hours or days, I can no longer access any of the linked servers. No data, queries, tables, or anything is accessible to me anymore. I use this command "EXEC sp_testlinkedserver N'THE' " to test the linked server and get the following error msg.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE".

The only solution I have to fix this currently is to restart the SQL Server Database service. I have tried deleting and recreating the Linked server before doing this to no avail. I have also used the FileMon and RegMon tools on the server to try to determine the problem but have no no success. After restarting the SQL service I can run the "EXEC sp_testlinkedserver N'THE' " command successfully "Command(s) completed successfully." and can access the linked servers again without any problems.

Any help with this is greatly appreciated.

Mark.

P.S. I have tried changing the security options on the linked server from "Be made without a context." to using the "Admin" username and leaving the password blank, to several other possible variations and options, as well as setting up the system.mdw database in the registry to no avail.

This issue has been reported fixed with Sql Server 2005 SP1.

You can install SP1 from http://www.microsoft.com/downloads/details.aspx?FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&DisplayLang=en.

-Curtis

|||

I was unable to find any documentation that said this problem was fixed. Can you please direct me to KB article that describes this fix as our company documentation requires this information.

Any information that you can provide would be helpful.

Thank You

Mark

|||

hi mark,

I think a sharing violation took place in your access DB.

as we all know access database is not as roboust as sql server,

another user could have get in and use the Access database

locking and preventing sql server to use it.

To prevent this from happening make sure that no ther user is accesing the access mdb.

regards,

joey

|||

I had thought I had Service Pack 1 installed, however, I discovered it had not yet been applied. It did appear to be fixed after applying SP1, however it has shown up again.

Now it is entirely possible that another user has accessed the Access DB, but I can guarantee you that no-one was accessing it while I tried to use the linked server, and definitely no-one else has used that linked server.

Also just to note, it gives me the exact same error I had gotten before the Service Pack was applied. This sounds to me like a very bad sign for anyone who uses linked servers.

|||Does anyone know or have an idea what causes this. I never had this problem when I used SQL Server 2000.|||

Seradex wrote:

Does anyone know or have an idea what causes this. I never had this problem when I used SQL Server 2000.

I have the same problem with a linked server on a file, but for me with "SQL authentication" (sa) is ok but not with "Windows authentication".

|||

My 2005 server has just done this for the first time! I have found it happens about every other month between Sql 2000 servers. Every sp that has "promised" to fix it has not. I have also found that on the server you loose one, you loose them all. I have multiple connections to different types of data sources and they all stop simultaneously.

The comment about record locking is along the right track I think. I have found that OLE DB connectivity can fail at network level and can cause it.

|||This is a bug using the Sql Management Studio.
I've found a workaround, described at
http://www.sql-und-xml.de/sql-praxis/legacy-daten-import-in-ein-zwei-server-system.html
at the end.|||

Thank you for the idea. I will try this ASAP.

Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)

Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

He suggests following these steps:

Check Disallow AdHoc Access, this will create the right key with the wrong value (1 instead of the correct 0). Unchecking this option will remove the key and the value, this is interpreted as 'value = 1'. This 'Removing' in combination with the standard-value 1 (if no key exists) is the bug.|||

Seradex wrote:

Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)

Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

Now I've found the 'bug' as explicit code.

The master database contains a stored procedure called from the Sql-Server-Management-Studio

[sys].[sp_MSset_oledb_prop]

At the end, there is the code:

-

if 1 = @.property_value

begin

declare @.val int

set @.val = @.property_value

exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', @.regpath, @.property_name, REG_DWORD, @.val

end

else

begin

exec xp_instance_regdeletevalue N'HKEY_LOCAL_MACHINE', @.regpath, @.property_name

end

-

@.property_value is called with 0 (I used the Sql-Profiler). So the key is deleted.

The procedure can be found using the following query:

Select name from master.dbo.sysobjects a inner join master.dbo.syscomments b
on a.id = b.id where b.text like '%DisallowAdHocAccess%'

|||

I am getting this error when using VS2005 to "run" a modification to a stored procedure that references an Access database via a Linked Server. I tried the fix above but it still will not run. I can run a query against the Linked server using MS SS Management Studio without problem.

Anyone have any suggestions?

BTW: I have SP1 installed and have connection be made without a security context checked.

Closing and re-opening VS2005 does not help.

I copied the stored procedure source code to a window in MS SS Management Studio and executed it. It ran ok there.

|||

I tried applying this fix as described and it does not seem to resolve the problem.

The Linked Servers on our SQL 2005 Server still stop working after a short amount of time. I applied the fix a while back, but was unable to test until recently. I first thought I had forgotten to restart the SQL service, so I restarted the service. After that it only worked for a short time like before. Because of that I thought that perhaps I had applied the fix incorrectly so I tried again, and again it stopped working after a short period of time.

This is not good. Are any Microsoft techs looking at this issue, because it needs to be fixed soon?
It is not good that the SQL Server service needs to be restarted each time this problem occurs.

|||

I met the same problem, at last I have it done:

1: don't use mapped drive, use network path like: //machine/sharefolder/filename

2: put machine name to the trust site in IE

|||I first started trying to use an Access database as a linked server from SQL Server 2005 Express. It works fine from SSMSEE whether I have the MDB file open with Access or one of our legacy VB applications. From a C# application, it works if nothing else has the database open. If the MDB file is open, the C# app gets the "Cannot initialize data source object" message. Closing the database is enough to make it start working from the C# application. I don't have to restart SQL Server. I tried reverting back to MSDE and the linked server works from the C# app even if Access has the MDB file open. Apparently SSMSEE talks to the database in a different way than a .NET database object. It would be nice to know what options to set (registry, server settings, C# parameters, etc.) to make a C# app work with SS2K5 when it's not the only thing that wants to talk to the database. If SSMSEE can do it, why shouldn't we be able to ?

Jet.OLEDB.4.0 and Linked Servers (SQL standard 2005 on Win2003 SP1)

I have been trying to figure out for some time why Linked Servers do not continue to run. I start up my SQL Server Database service, and then using SQL Server Management Studio I create one or more linked servers to Microsoft Access Databases using the Jet Provider. I then run some queries to that access those linked servers without any problems. (One currently is called 'THE')

After some time has passed, possibly minutes, hours or days, I can no longer access any of the linked servers. No data, queries, tables, or anything is accessible to me anymore. I use this command "EXEC sp_testlinkedserver N'THE' " to test the linked server and get the following error msg.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE".

The only solution I have to fix this currently is to restart the SQL Server Database service. I have tried deleting and recreating the Linked server before doing this to no avail. I have also used the FileMon and RegMon tools on the server to try to determine the problem but have no no success. After restarting the SQL service I can run the "EXEC sp_testlinkedserver N'THE' " command successfully "Command(s) completed successfully." and can access the linked servers again without any problems.

Any help with this is greatly appreciated.

Mark.

P.S. I have tried changing the security options on the linked server from "Be made without a context." to using the "Admin" username and leaving the password blank, to several other possible variations and options, as well as setting up the system.mdw database in the registry to no avail.

This issue has been reported fixed with Sql Server 2005 SP1.

You can install SP1 from http://www.microsoft.com/downloads/details.aspx?FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&DisplayLang=en.

-Curtis

|||

I was unable to find any documentation that said this problem was fixed. Can you please direct me to KB article that describes this fix as our company documentation requires this information.

Any information that you can provide would be helpful.

Thank You

Mark

|||

hi mark,

I think a sharing violation took place in your access DB.

as we all know access database is not as roboust as sql server,

another user could have get in and use the Access database

locking and preventing sql server to use it.

To prevent this from happening make sure that no ther user is accesing the access mdb.

regards,

joey

|||

I had thought I had Service Pack 1 installed, however, I discovered it had not yet been applied. It did appear to be fixed after applying SP1, however it has shown up again.

Now it is entirely possible that another user has accessed the Access DB, but I can guarantee you that no-one was accessing it while I tried to use the linked server, and definitely no-one else has used that linked server.

Also just to note, it gives me the exact same error I had gotten before the Service Pack was applied. This sounds to me like a very bad sign for anyone who uses linked servers.

|||Does anyone know or have an idea what causes this. I never had this problem when I used SQL Server 2000.|||

Seradex wrote:

Does anyone know or have an idea what causes this. I never had this problem when I used SQL Server 2000.

I have the same problem with a linked server on a file, but for me with "SQL authentication" (sa) is ok but not with "Windows authentication".

|||

My 2005 server has just done this for the first time! I have found it happens about every other month between Sql 2000 servers. Every sp that has "promised" to fix it has not. I have also found that on the server you loose one, you loose them all. I have multiple connections to different types of data sources and they all stop simultaneously.

The comment about record locking is along the right track I think. I have found that OLE DB connectivity can fail at network level and can cause it.

|||This is a bug using the Sql Management Studio.
I've found a workaround, described at
http://www.sql-und-xml.de/sql-praxis/legacy-daten-import-in-ein-zwei-server-system.html
at the end.|||

Thank you for the idea. I will try this ASAP.

Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)

Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

He suggests following these steps:

Check Disallow AdHoc Access, this will create the right key with the wrong value (1 instead of the correct 0). Unchecking this option will remove the key and the value, this is interpreted as 'value = 1'. This 'Removing' in combination with the standard-value 1 (if no key exists) is the bug.|||

Seradex wrote:

Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)

Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

Now I've found the 'bug' as explicit code.

The master database contains a stored procedure called from the Sql-Server-Management-Studio

[sys].[sp_MSset_oledb_prop]

At the end, there is the code:

-

if 1 = @.property_value

begin

declare @.val int

set @.val = @.property_value

exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', @.regpath, @.property_name, REG_DWORD, @.val

end

else

begin

exec xp_instance_regdeletevalue N'HKEY_LOCAL_MACHINE', @.regpath, @.property_name

end

-

@.property_value is called with 0 (I used the Sql-Profiler). So the key is deleted.

The procedure can be found using the following query:

Select name from master.dbo.sysobjects a inner join master.dbo.syscomments b
on a.id = b.id where b.text like '%DisallowAdHocAccess%'

|||

I am getting this error when using VS2005 to "run" a modification to a stored procedure that references an Access database via a Linked Server. I tried the fix above but it still will not run. I can run a query against the Linked server using MS SS Management Studio without problem.

Anyone have any suggestions?

BTW: I have SP1 installed and have connection be made without a security context checked.

Closing and re-opening VS2005 does not help.

I copied the stored procedure source code to a window in MS SS Management Studio and executed it. It ran ok there.

|||

I tried applying this fix as described and it does not seem to resolve the problem.

The Linked Servers on our SQL 2005 Server still stop working after a short amount of time. I applied the fix a while back, but was unable to test until recently. I first thought I had forgotten to restart the SQL service, so I restarted the service. After that it only worked for a short time like before. Because of that I thought that perhaps I had applied the fix incorrectly so I tried again, and again it stopped working after a short period of time.

This is not good. Are any Microsoft techs looking at this issue, because it needs to be fixed soon?
It is not good that the SQL Server service needs to be restarted each time this problem occurs.

|||

I met the same problem, at last I have it done:

1: don't use mapped drive, use network path like: //machine/sharefolder/filename

2: put machine name to the trust site in IE

|||I first started trying to use an Access database as a linked server from SQL Server 2005 Express. It works fine from SSMSEE whether I have the MDB file open with Access or one of our legacy VB applications. From a C# application, it works if nothing else has the database open. If the MDB file is open, the C# app gets the "Cannot initialize data source object" message. Closing the database is enough to make it start working from the C# application. I don't have to restart SQL Server. I tried reverting back to MSDE and the linked server works from the C# app even if Access has the MDB file open. Apparently SSMSEE talks to the database in a different way than a .NET database object. It would be nice to know what options to set (registry, server settings, C# parameters, etc.) to make a C# app work with SS2K5 when it's not the only thing that wants to talk to the database. If SSMSEE can do it, why shouldn't we be able to ?

Jet.OLEDB.4.0 and Linked Servers (SQL standard 2005 on Win2003 SP1)

I have been trying to figure out for some time why Linked Servers do not continue to run. I start up my SQL Server Database service, and then using SQL Server Management Studio I create one or more linked servers to Microsoft Access Databases using the Jet Provider. I then run some queries to that access those linked servers without any problems. (One currently is called 'THE')

After some time has passed, possibly minutes, hours or days, I can no longer access any of the linked servers. No data, queries, tables, or anything is accessible to me anymore. I use this command "EXEC sp_testlinkedserver N'THE' " to test the linked server and get the following error msg.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "THE".

The only solution I have to fix this currently is to restart the SQL Server Database service. I have tried deleting and recreating the Linked server before doing this to no avail. I have also used the FileMon and RegMon tools on the server to try to determine the problem but have no no success. After restarting the SQL service I can run the "EXEC sp_testlinkedserver N'THE' " command successfully "Command(s) completed successfully." and can access the linked servers again without any problems.

Any help with this is greatly appreciated.

Mark.

P.S. I have tried changing the security options on the linked server from "Be made without a context." to using the "Admin" username and leaving the password blank, to several other possible variations and options, as well as setting up the system.mdw database in the registry to no avail.

This issue has been reported fixed with Sql Server 2005 SP1.

You can install SP1 from http://www.microsoft.com/downloads/details.aspx?FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&DisplayLang=en.

-Curtis

|||

I was unable to find any documentation that said this problem was fixed. Can you please direct me to KB article that describes this fix as our company documentation requires this information.

Any information that you can provide would be helpful.

Thank You

Mark

|||

hi mark,

I think a sharing violation took place in your access DB.

as we all know access database is not as roboust as sql server,

another user could have get in and use the Access database

locking and preventing sql server to use it.

To prevent this from happening make sure that no ther user is accesing the access mdb.

regards,

joey

|||

I had thought I had Service Pack 1 installed, however, I discovered it had not yet been applied. It did appear to be fixed after applying SP1, however it has shown up again.

Now it is entirely possible that another user has accessed the Access DB, but I can guarantee you that no-one was accessing it while I tried to use the linked server, and definitely no-one else has used that linked server.

Also just to note, it gives me the exact same error I had gotten before the Service Pack was applied. This sounds to me like a very bad sign for anyone who uses linked servers.

|||Does anyone know or have an idea what causes this. I never had this problem when I used SQL Server 2000.|||

Seradex wrote:

Does anyone know or have an idea what causes this. I never had this problem when I used SQL Server 2000.

I have the same problem with a linked server on a file, but for me with "SQL authentication" (sa) is ok but not with "Windows authentication".

|||

My 2005 server has just done this for the first time! I have found it happens about every other month between Sql 2000 servers. Every sp that has "promised" to fix it has not. I have also found that on the server you loose one, you loose them all. I have multiple connections to different types of data sources and they all stop simultaneously.

The comment about record locking is along the right track I think. I have found that OLE DB connectivity can fail at network level and can cause it.

|||This is a bug using the Sql Management Studio.
I've found a workaround, described at
http://www.sql-und-xml.de/sql-praxis/legacy-daten-import-in-ein-zwei-server-system.html
at the end.|||

Thank you for the idea. I will try this ASAP.

Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)

Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

He suggests following these steps:

Check Disallow AdHoc Access, this will create the right key with the wrong value (1 instead of the correct 0). Unchecking this option will remove the key and the value, this is interpreted as 'value = 1'. This 'Removing' in combination with the standard-value 1 (if no key exists) is the bug.|||

Seradex wrote:

Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)

Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

Now I've found the 'bug' as explicit code.

The master database contains a stored procedure called from the Sql-Server-Management-Studio

[sys].[sp_MSset_oledb_prop]

At the end, there is the code:

-

if 1 = @.property_value

begin

declare @.val int

set @.val = @.property_value

exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', @.regpath, @.property_name, REG_DWORD, @.val

end

else

begin

exec xp_instance_regdeletevalue N'HKEY_LOCAL_MACHINE', @.regpath, @.property_name

end

-

@.property_value is called with 0 (I used the Sql-Profiler). So the key is deleted.

The procedure can be found using the following query:

Select name from master.dbo.sysobjects a inner join master.dbo.syscomments b
on a.id = b.id where b.text like '%DisallowAdHocAccess%'

|||

I am getting this error when using VS2005 to "run" a modification to a stored procedure that references an Access database via a Linked Server. I tried the fix above but it still will not run. I can run a query against the Linked server using MS SS Management Studio without problem.

Anyone have any suggestions?

BTW: I have SP1 installed and have connection be made without a security context checked.

Closing and re-opening VS2005 does not help.

I copied the stored procedure source code to a window in MS SS Management Studio and executed it. It ran ok there.

|||

I tried applying this fix as described and it does not seem to resolve the problem.

The Linked Servers on our SQL 2005 Server still stop working after a short amount of time. I applied the fix a while back, but was unable to test until recently. I first thought I had forgotten to restart the SQL service, so I restarted the service. After that it only worked for a short time like before. Because of that I thought that perhaps I had applied the fix incorrectly so I tried again, and again it stopped working after a short period of time.

This is not good. Are any Microsoft techs looking at this issue, because it needs to be fixed soon?
It is not good that the SQL Server service needs to be restarted each time this problem occurs.

|||

I met the same problem, at last I have it done:

1: don't use mapped drive, use network path like: //machine/sharefolder/filename

2: put machine name to the trust site in IE

|||I first started trying to use an Access database as a linked server from SQL Server 2005 Express. It works fine from SSMSEE whether I have the MDB file open with Access or one of our legacy VB applications. From a C# application, it works if nothing else has the database open. If the MDB file is open, the C# app gets the "Cannot initialize data source object" message. Closing the database is enough to make it start working from the C# application. I don't have to restart SQL Server. I tried reverting back to MSDE and the linked server works from the C# app even if Access has the MDB file open. Apparently SSMSEE talks to the database in a different way than a .NET database object. It would be nice to know what options to set (registry, server settings, C# parameters, etc.) to make a C# app work with SS2K5 when it's not the only thing that wants to talk to the database. If SSMSEE can do it, why shouldn't we be able to ?