JDBC Driver issue: linked

Showing posts with label linked. Show all posts

Wednesday, March 28, 2012

Job step + linked server help

I have a job step that executes a lot of stored procs against linked servers
such as
exec server1.db.dbo.abc
exec server2.db.dbo.abc
exec server3.db.dbo.abc
exec server4.db.dbo.abc
I do not wish to create multiple job steps but would like to know
programatically how i can run all those statements even if say linked server
server2 is unavailable. Currently if server2 is unavailable, it just exits
out. I would like for it to proceed and execute the last 2 stored procs for
server3 and server4TSQL jobsteps will exit if you get errors. You would have to talk to the person who wrote agent and
get a new compile to change that.
One alternative is to use a CMDExec jobstep and OSQL.EXE instead.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:%23WcbEJhkDHA.2328@.TK2MSFTNGP10.phx.gbl...
> I have a job step that executes a lot of stored procs against linked servers
> such as
> exec server1.db.dbo.abc
> exec server2.db.dbo.abc
> exec server3.db.dbo.abc
> exec server4.db.dbo.abc
> I do not wish to create multiple job steps but would like to know
> programatically how i can run all those statements even if say linked server
> server2 is unavailable. Currently if server2 is unavailable, it just exits
> out. I would like for it to proceed and execute the last 2 stored procs for
> server3 and server4
>
>|||Hassan
Check for PING to the server.
set nocount on
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + 00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
SELECT * FROM #t_ip
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
..................
END
DROP TABLE #t_ip
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#WcbEJhkDHA.2328@.TK2MSFTNGP10.phx.gbl...
> I have a job step that executes a lot of stored procs against linked
servers
> such as
> exec server1.db.dbo.abc
> exec server2.db.dbo.abc
> exec server3.db.dbo.abc
> exec server4.db.dbo.abc
> I do not wish to create multiple job steps but would like to know
> programatically how i can run all those statements even if say linked
server
> server2 is unavailable. Currently if server2 is unavailable, it just exits
> out. I would like for it to proceed and execute the last 2 stored procs
for
> server3 and server4
>
>

Monday, March 12, 2012

Job Fails on Linked Server [solution]

I've got a job running on a SQL2005 box (server07) that modifies data on a SQL2000 box (server05). The databases on the 2005 were imported from an older box running SQL2000. The step that is failing is:

update server05.xtender.sysop.ae_rf4
set field3 = sname
from server05.xtender.sysop.ae_rf4 x inner join allscholarships..studentsindb_table s
on x.field1 = s.ssn
where x.field3 is null or len(field3) = 0

The history shows this for the failed step:

Message
Executed as user: Domain\Administrator. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274)Unable to open Step output file. The step failed.

Domain\Administrator is a sysadmin on both boxes. This job had been running some time ago, but the SQL2000 box (under a different dba) has somehow changed. Now I need to get the thing running again.

Hi,

The error said its untrusted connection ... have you tried re mapping accounts using sp_addlinkedsrvlogin!!!?

refer this thread http://www.webservertalk.com/archive137-2006-4-1477449.html

Hemantgiri S. Goswami

|||That discussion is the only thing that pops up when I google "Error 15274", I've read the very original and he hasn't gotten an answer yet.

The best I can tell is that the SQL Agent is supplying the security credentials to the linked server and I'm not able to specify which security context for the job to use.

In the Job Step Properties pages, under each step's General page in the middle of the window is a dropdown - Run As. There is nothing in the dropdown list and no way to enter a value. On the Advanced page of each step is a Run As User with a select user button, I've entered every conceivable value on this Advanced page with no success.
|||Check services - which account is used for startup of the SQL agent. System account is no good - only local resources are available. Use "This account" with administrative rights on the Windows & SQL server.|||Make sure Domain\Administrator has got required privilges on SQL Server (incl. SQLAgent) in order to complete the job process.|||Also check the linked server setup - security. Check "Be made using the login's current security context".|||

a very weird issue. Was able to resolve it using a new NT Account with Admin rights. Just created it at both servers, then added to both SQL Servers with rights I needed. And also set this account to be used for SQL Agent service.

Oh, also have used the imporsonate setting for linked servers mapping (at both sides)

and just leave "run as user" blank for step settings

|||check the connectivity of your linked server using sp_testlinkedserver

Job Fails on Linked Server

The history shows this for the failed step:

Domain\Administrator is a sysadmin on both boxes. This job had been running some time ago, but the SQL2000 box (under a different dba) has somehow changed. Now I need to get the thing running again.

Hi,

The error said its untrusted connection ... have you tried re mapping accounts using sp_addlinkedsrvlogin!!!?

refer this thread http://www.webservertalk.com/archive137-2006-4-1477449.html

Hemantgiri S. Goswami

|||That discussion is the only thing that pops up when I google "Error 15274", I've read the very original and he hasn't gotten an answer yet.

The best I can tell is that the SQL Agent is supplying the security credentials to the linked server and I'm not able to specify which security context for the job to use.

Oh, also have used the imporsonate setting for linked servers mapping (at both sides)

and just leave "run as user" blank for step settings

|||check the connectivity of your linked server using sp_testlinkedserver

Job Fails on Linked Server

The history shows this for the failed step:

Domain\Administrator is a sysadmin on both boxes. This job had been running some time ago, but the SQL2000 box (under a different dba) has somehow changed. Now I need to get the thing running again.

Hi,

The error said its untrusted connection ... have you tried re mapping accounts using sp_addlinkedsrvlogin!!!?

refer this thread http://www.webservertalk.com/archive137-2006-4-1477449.html

Hemantgiri S. Goswami

|||That discussion is the only thing that pops up when I google "Error 15274", I've read the very original and he hasn't gotten an answer yet.

The best I can tell is that the SQL Agent is supplying the security credentials to the linked server and I'm not able to specify which security context for the job to use.

In the Job Step Properties pages, under each step's General page in the middle of the window is a dropdown - Run As. There is nothing in the dropdown list and no way to enter a value. On the Advanced page of each step is a Run As User with a select user button, I've entered every conceivable value on this Advanced page with no success.|||Check services - which account is used for startup of the SQL agent. System account is no good - only local resources are available. Use "This account" with administrative rights on the Windows & SQL server.|||Make sure Domain\Administrator has got required privilges on SQL Server (incl. SQLAgent) in order to complete the job process.|||Also check the linked server setup - security. Check "Be made using the login's current security context".|||

Oh, also have used the imporsonate setting for linked servers mapping (at both sides)

and just leave "run as user" blank for step settings

|||check the connectivity of your linked server using sp_testlinkedserver

Friday, March 9, 2012

job failed on resource limit

Hi,
I have a job that pulls data from a linked server into
this server's database. I am working on sql server 2000.
It generally runs fine, but twice it failed on this error
message: 'OLE DB provider 'SQLOLEDB' reported an error.
Execution terminated by the provider because a resource
limit was reached. [SQLSTATE 42000] (Error 7399)
[SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB
Provider 'SQLOLEDB' ICommandText::Execute returned
0x80040e31: Execution terminated by the provider because
a resource limit was reached.]. [SQLSTATE 01000] (Error
7300). The step failed.'
When it says resource limit was reached, was it
complaining the number of connections at the linked server
has reached the limit?
Many thanks.
JJIt generally means a timeout has been exceeded (that's what 0x80040e31
means). You can increase the timeout to try and prevent this issue.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"JJ Wang" <jwang@.leapwireless.com> wrote in message
news:09b401c371e7$6f7330b0$a101280a@.phx.gbl...
Hi,
I have a job that pulls data from a linked server into
this server's database. I am working on sql server 2000.
It generally runs fine, but twice it failed on this error
message: 'OLE DB provider 'SQLOLEDB' reported an error.
Execution terminated by the provider because a resource
limit was reached. [SQLSTATE 42000] (Error 7399)
[SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB
Provider 'SQLOLEDB' ICommandText::Execute returned
0x80040e31: Execution terminated by the provider because
a resource limit was reached.]. [SQLSTATE 01000] (Error
7300). The step failed.'
When it says resource limit was reached, was it
complaining the number of connections at the linked server
has reached the limit?
Many thanks.
JJ|||thanks, Jasper. Where should I go to adjust the time-out?
JJ
>--Original Message--
>It generally means a timeout has been exceeded (that's
what 0x80040e31
>means). You can increase the timeout to try and prevent
this issue.
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>"JJ Wang" <jwang@.leapwireless.com> wrote in message
>news:09b401c371e7$6f7330b0$a101280a@.phx.gbl...
>Hi,
>I have a job that pulls data from a linked server into
>this server's database. I am working on sql server 2000.
>It generally runs fine, but twice it failed on this error
>message: 'OLE DB provider 'SQLOLEDB' reported an error.
>Execution terminated by the provider because a resource
>limit was reached. [SQLSTATE 42000] (Error 7399)
>[SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB
>Provider 'SQLOLEDB' ICommandText::Execute returned
>0x80040e31: Execution terminated by the provider because
>a resource limit was reached.]. [SQLSTATE 01000] (Error
>7300). The step failed.'
>When it says resource limit was reached, was it
>complaining the number of connections at the linked server
>has reached the limit?
>Many thanks.
>JJ
>
>.
>

Wednesday, March 7, 2012

Job Agent - Syntax Errors

Hi All,
I'm having a problem with a job on an SQL 2000 Server that is linked to pull
data from an SQL 2005 server. When I enter my infromation into the Job Step
properties and parse the SQL, the system tells me it is fine. When I run
the code through query analyzer, it also works fine as well. However, when
the job is run, I am getting a syntax error on the file name because (I
think) it is in double quotes.
Here is the SQL I am trying to parse:
insert into starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging"
(vin,productionDateDT,plantcode,UnitReferenceNo,ProductionLotNo,
KDLotNo,LotPosition,MTOC,ModelGrade,Returned,Completed,GhostBody)
select a.vin, (CONVERT(varchar(10), a.pdate ,101) + ' '
+ CONVERT(varchar(10), a.ptime ,108)), a.plantcode, a.lrnum, a.prod_lot,
a.kd_lot, a.lot_position, a.mtoc, 'N/A', 'N/A', 'N/A','N/A'
from bos_data a
where vin COLLATE DATABASE_DEFAULT not in
(select vin from starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging")
Again, it fails with a syntax error on the "SPS 4_0$AFON DTS Import Staging"
in both areas of the SQL.
Any ideas would be most appreciated.
Thanks!
Brian.My guess is that Agent is executing your code with below setting:
SET QUOTED_IDENTIFIER OFF
Above will interpret double-quotes as string delimiters instead of identifier delimiters. IMO, the
setting should be ON which is ANSI SQL compliant, what QA etc has and apparently even the "parse"
functionality seem to have it set differently.
Anyhow, you can try to use [square brackets] instead of double-quotes. Or add below in beginning of
your TSQL code:
SET QUOTED_IDENTIFIER ON
GO
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian Piotrowski" <n0spam-bpiotrowski@.simcoeparts.com> wrote in message
news:A83BACC9-B41F-470F-8F29-8B2B9A6BAB2B@.microsoft.com...
> Hi All,
> I'm having a problem with a job on an SQL 2000 Server that is linked to pull
> data from an SQL 2005 server. When I enter my infromation into the Job Step
> properties and parse the SQL, the system tells me it is fine. When I run
> the code through query analyzer, it also works fine as well. However, when
> the job is run, I am getting a syntax error on the file name because (I
> think) it is in double quotes.
> Here is the SQL I am trying to parse:
> insert into starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging"
> (vin,productionDateDT,plantcode,UnitReferenceNo,ProductionLotNo,
> KDLotNo,LotPosition,MTOC,ModelGrade,Returned,Completed,GhostBody)
> select a.vin, (CONVERT(varchar(10), a.pdate ,101) + ' '
> + CONVERT(varchar(10), a.ptime ,108)), a.plantcode, a.lrnum, a.prod_lot,
> a.kd_lot, a.lot_position, a.mtoc, 'N/A', 'N/A', 'N/A','N/A'
> from bos_data a
> where vin COLLATE DATABASE_DEFAULT not in
> (select vin from starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging")
> Again, it fails with a syntax error on the "SPS 4_0$AFON DTS Import Staging"
> in both areas of the SQL.
> Any ideas would be most appreciated.
> Thanks!
> Brian.|||That worked.
Thank you very much, Tibor!
/b;
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OoRcLb8nIHA.1036@.TK2MSFTNGP03.phx.gbl...
> My guess is that Agent is executing your code with below setting:
> SET QUOTED_IDENTIFIER OFF
> Above will interpret double-quotes as string delimiters instead of
> identifier delimiters. IMO, the setting should be ON which is ANSI SQL
> compliant, what QA etc has and apparently even the "parse" functionality
> seem to have it set differently.
> Anyhow, you can try to use [square brackets] instead of double-quotes. Or
> add below in beginning of your TSQL code:
> SET QUOTED_IDENTIFIER ON
> GO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Brian Piotrowski" <n0spam-bpiotrowski@.simcoeparts.com> wrote in message
> news:A83BACC9-B41F-470F-8F29-8B2B9A6BAB2B@.microsoft.com...
>> Hi All,
>> I'm having a problem with a job on an SQL 2000 Server that is linked to
>> pull
>> data from an SQL 2005 server. When I enter my infromation into the Job
>> Step
>> properties and parse the SQL, the system tells me it is fine. When I run
>> the code through query analyzer, it also works fine as well. However,
>> when
>> the job is run, I am getting a syntax error on the file name because (I
>> think) it is in double quotes.
>> Here is the SQL I am trying to parse:
>> insert into starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging"
>> (vin,productionDateDT,plantcode,UnitReferenceNo,ProductionLotNo,
>> KDLotNo,LotPosition,MTOC,ModelGrade,Returned,Completed,GhostBody)
>> select a.vin, (CONVERT(varchar(10), a.pdate ,101) + ' '
>> + CONVERT(varchar(10), a.ptime ,108)), a.plantcode, a.lrnum, a.prod_lot,
>> a.kd_lot, a.lot_position, a.mtoc, 'N/A', 'N/A', 'N/A','N/A'
>> from bos_data a
>> where vin COLLATE DATABASE_DEFAULT not in
>> (select vin from starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import
>> Staging")
>> Again, it fails with a syntax error on the "SPS 4_0$AFON DTS Import
>> Staging"
>> in both areas of the SQL.
>> Any ideas would be most appreciated.
>> Thanks!
>> Brian.
>

Job Agent - Syntax Errors

Hi All,
I'm having a problem with a job on an SQL 2000 Server that is linked to pull
data from an SQL 2005 server. When I enter my infromation into the Job Step
properties and parse the SQL, the system tells me it is fine. When I run
the code through query analyzer, it also works fine as well. However, when
the job is run, I am getting a syntax error on the file name because (I
think) it is in double quotes.
Here is the SQL I am trying to parse:
insert into starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging"
(vin,productionDateDT,plantcode,UnitReferenceNo,Pr oductionLotNo,
KDLotNo,LotPosition,MTOC,ModelGrade,Returned,Compl eted,GhostBody)
select a.vin, (CONVERT(varchar(10), a.pdate ,101) + ' '
+ CONVERT(varchar(10), a.ptime ,108)), a.plantcode, a.lrnum, a.prod_lot,
a.kd_lot, a.lot_position, a.mtoc, 'N/A', 'N/A', 'N/A','N/A'
from bos_data a
where vin COLLATE DATABASE_DEFAULT not in
(select vin from starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging")
Again, it fails with a syntax error on the "SPS 4_0$AFON DTS Import Staging"
in both areas of the SQL.
Any ideas would be most appreciated.
Thanks!
Brian.
My guess is that Agent is executing your code with below setting:
SET QUOTED_IDENTIFIER OFF
Above will interpret double-quotes as string delimiters instead of identifier delimiters. IMO, the
setting should be ON which is ANSI SQL compliant, what QA etc has and apparently even the "parse"
functionality seem to have it set differently.
Anyhow, you can try to use [square brackets] instead of double-quotes. Or add below in beginning of
your TSQL code:
SET QUOTED_IDENTIFIER ON
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian Piotrowski" <n0spam-bpiotrowski@.simcoeparts.com> wrote in message
news:A83BACC9-B41F-470F-8F29-8B2B9A6BAB2B@.microsoft.com...
> Hi All,
> I'm having a problem with a job on an SQL 2000 Server that is linked to pull
> data from an SQL 2005 server. When I enter my infromation into the Job Step
> properties and parse the SQL, the system tells me it is fine. When I run
> the code through query analyzer, it also works fine as well. However, when
> the job is run, I am getting a syntax error on the file name because (I
> think) it is in double quotes.
> Here is the SQL I am trying to parse:
> insert into starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging"
> (vin,productionDateDT,plantcode,UnitReferenceNo,Pr oductionLotNo,
> KDLotNo,LotPosition,MTOC,ModelGrade,Returned,Compl eted,GhostBody)
> select a.vin, (CONVERT(varchar(10), a.pdate ,101) + ' '
> + CONVERT(varchar(10), a.ptime ,108)), a.plantcode, a.lrnum, a.prod_lot,
> a.kd_lot, a.lot_position, a.mtoc, 'N/A', 'N/A', 'N/A','N/A'
> from bos_data a
> where vin COLLATE DATABASE_DEFAULT not in
> (select vin from starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging")
> Again, it fails with a syntax error on the "SPS 4_0$AFON DTS Import Staging"
> in both areas of the SQL.
> Any ideas would be most appreciated.
> Thanks!
> Brian.
|||That worked.
Thank you very much, Tibor!
/b;
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OoRcLb8nIHA.1036@.TK2MSFTNGP03.phx.gbl...
> My guess is that Agent is executing your code with below setting:
> SET QUOTED_IDENTIFIER OFF
> Above will interpret double-quotes as string delimiters instead of
> identifier delimiters. IMO, the setting should be ON which is ANSI SQL
> compliant, what QA etc has and apparently even the "parse" functionality
> seem to have it set differently.
> Anyhow, you can try to use [square brackets] instead of double-quotes. Or
> add below in beginning of your TSQL code:
> SET QUOTED_IDENTIFIER ON
> GO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Brian Piotrowski" <n0spam-bpiotrowski@.simcoeparts.com> wrote in message
> news:A83BACC9-B41F-470F-8F29-8B2B9A6BAB2B@.microsoft.com...
>

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)

Any help with this is greatly appreciated.

Mark.

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.

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".

|||

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:

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%'

|||

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.

|||

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

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

Any help with this is greatly appreciated.

Mark.

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.

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".

|||

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:

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%'

|||

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.

|||

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

Friday, February 24, 2012

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

Any help with this is greatly appreciated.

Mark.

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.

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".

|||

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:

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%'

|||

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.

|||

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

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

Any help with this is greatly appreciated.

Mark.

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.

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".

|||

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:

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%'

|||

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.

|||

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

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

Any help with this is greatly appreciated.

Mark.

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.

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".

|||

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:

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%'

|||

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.

|||

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

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

Any help with this is greatly appreciated.

Mark.

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.

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".

|||

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:

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%'

|||

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.

|||

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

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

Any help with this is greatly appreciated.

Mark.

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.

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".

|||

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:

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%'

|||

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.

|||

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

Wednesday, March 28, 2012

Job step + linked server help

Monday, March 12, 2012

Job Fails on Linked Server [solution]

Job Fails on Linked Server

Job Fails on Linked Server

Friday, March 9, 2012

job failed on resource limit

Wednesday, March 7, 2012

Job Agent - Syntax Errors

Job Agent - Syntax Errors

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

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

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

Friday, February 24, 2012

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

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

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

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

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

JDBC Driver issue

Blog Archive

About Me