Job Scheduler

Hello,

I am have problems running a couple of jobs. It makes no sense. I have checked and made sure the agent service is running the same user level permissions as I am. I run the job manually from SMS and it works fine.

User is a Windows Login.

Any suggestions would be greatly appreciated.

Date 6/11/2007 6:00:01 AM
Log Job History (BidBackLog)

Step ID 1
Server TWSQLRPTS
Job Name BidBackLog
Step Name Step1
Duration 00:00:21
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: TWDOMAIN\SQLADMIN. ... 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 6:00:01 AM Error: 2007-06-11 06:00:22.50 Code: 0xC0202009 Source: BidBacklog Connection manager "TWSQLRPTS.HomeBASE" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user 'TWDOMAIN\SQLADMIN'.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot open database "HomeBASE" requested by the login. The login failed.". End Error Error: 2007-06-11 06:00:22.50 Code: 0xC020801C Source: DTSTask_DTSDataPumpTask_1 OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager ... The package execution fa... The step failed.

TrussworksLeo1 wrote:

Hello,

I am have problems running a couple of jobs. It makes no sense. I have checked and made sure the agent service is running the same user level permissions as I am. I run the job manually from SMS and it works fine.

User is a Windows Login.

Any suggestions would be greatly appreciated.

Date 6/11/2007 6:00:01 AM
Log Job History (BidBackLog)

Step ID 1
Server TWSQLRPTS
Job Name BidBackLog
Step Name Step1
Duration 00:00:21
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: TWDOMAIN\SQLADMIN. ... 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 6:00:01 AM Error: 2007-06-11 06:00:22.50 Code: 0xC0202009 Source: BidBacklog Connection manager "TWSQLRPTS.HomeBASE" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user 'TWDOMAIN\SQLADMIN'.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Cannot open database "HomeBASE" requested by the login. The login failed.". End Error Error: 2007-06-11 06:00:22.50 Code: 0xC020801C Source: DTSTask_DTSDataPumpTask_1 OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager ... The package execution fa... The step failed.

The user, TWDOMAIN\SQLADMIN doesn't have access to the databases... You're likely using windows authentication for your database connections, and when Agent runs the job, the user account is that of the SQL Server service account. (In this case ...\SQLADMIN)|||

Phil,

Thanks for the reply.

I have checked the permissions. The user is the owner of the databases that it is writting to, but it reads from a database that has mirroring setup. It will not let me change permisssions on that database. Do you know a way around this?

Thanks, Leo

job execution user change and ownership question

Hi
I am running my sql server 2000 server and agent as a service with domain
admin user.
Is this recommended?
Who should be the job owner? sa or application user?
Can the job be run by any other user other than the user who started the
sqlserver agent.
TKS
MangeshHi
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:907D7E49-4B58-4D9F-B3D4-149D45B9B94C@.microsoft.com...
> Hi
> I am running my sql server 2000 server and agent as a service with domain
> admin user.
> Is this recommended?
No! A domain admin is over privileged, restrict the account to what you need
to do.
Check out the requirements for this account at
http://msdn.microsoft.com/library/d.../>
ew_6k1f.asp
You may also want to read some of the information on:
http://www.sqlsecurity.com/DesktopD...index=0&tabid=1

> Who should be the job owner? sa or application user?
This will depend what the job does e.g if you need to restrict the job to
the privleges of the application user or if the job needs higher
permissions.

> Can the job be run by any other user other than the user who started the
> sqlserver agent.
>
Yes, look at sp_start_job to run the job at a non-scheduled time. Check out
books online for information about the context in which jobs are run.

>
> TKS
> Mangesh
John

job execution user change and ownership question

Hi
I am running my sql server 2000 server and agent as a service with domain
admin user.
Is this recommended?
Who should be the job owner? sa or application user?
Can the job be run by any other user other than the user who started the
sqlserver agent.
TKS
MangeshHi
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:907D7E49-4B58-4D9F-B3D4-149D45B9B94C@.microsoft.com...
> Hi
> I am running my sql server 2000 server and agent as a service with domain
> admin user.
> Is this recommended?
No! A domain admin is over privileged, restrict the account to what you need
to do.
Check out the requirements for this account at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
You may also want to read some of the information on:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabindex=0&tabid=1
> Who should be the job owner? sa or application user?
This will depend what the job does e.g if you need to restrict the job to
the privleges of the application user or if the job needs higher
permissions.
> Can the job be run by any other user other than the user who started the
> sqlserver agent.
>
Yes, look at sp_start_job to run the job at a non-scheduled time. Check out
books online for information about the context in which jobs are run.
>
> TKS
> Mangesh
John

job execution user change and ownership question

Hi
I am running my sql server 2000 server and agent as a service with domain
admin user.
Is this recommended?
Who should be the job owner? sa or application user?
Can the job be run by any other user other than the user who started the
sqlserver agent.
TKS
Mangesh
Hi
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:907D7E49-4B58-4D9F-B3D4-149D45B9B94C@.microsoft.com...
> Hi
> I am running my sql server 2000 server and agent as a service with domain
> admin user.
> Is this recommended?
No! A domain admin is over privileged, restrict the account to what you need
to do.
Check out the requirements for this account at
http://msdn.microsoft.com/library/de...rview_6k1f.asp
You may also want to read some of the information on:
http://www.sqlsecurity.com/DesktopDe...ndex=0&tabid=1

> Who should be the job owner? sa or application user?
This will depend what the job does e.g if you need to restrict the job to
the privleges of the application user or if the job needs higher
permissions.

> Can the job be run by any other user other than the user who started the
> sqlserver agent.
>
Yes, look at sp_start_job to run the job at a non-scheduled time. Check out
books online for information about the context in which jobs are run.

>
> TKS
> Mangesh
John

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 ?

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 ?

JDBC for SQL 2000 Service Pack 4?

Am I knuts, or does SQL2k SP4 break the latest set of JDBC drivers? Can
anyone confirm?
oleitch-AT-locustcreek-DOT-com
And by "break" you mean?
Alin.
|||"Fail to work with"
JDBC will not connect (gives the 'End of stream was detected on a read'
error). Was working fine before SP4 (previously SP3).
|||Works fine for me. What is the exact error? Can you connect using OSQL.EXE?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"end-user" <end-user@.discussions.microsoft.com> wrote in message
news:A01EF57B-01C3-4B84-A9CB-1D3E22E6BE42@.microsoft.com...
> "Fail to work with"
> JDBC will not connect (gives the 'End of stream was detected on a read'
> error). Was working fine before SP4 (previously SP3).
|||The exact error is " java.sql.SQLException: [Microsoft][SQLServer 2000 Driver
for JDBC]End of stream was detected on a read."
On the SQL server, I get a "Connection opened but invalid login packet(s)
sent. Connection closed".
I can't run osql.exe as I'm connecting from a linux box.
|||Can someone confirm that the JDBC drivers (for sp3) can successfully connect
to SQL Server w/ SP4 *when the "force protocol encryption" option is enabled*?
|||end-user wrote:
> Can someone confirm that the JDBC drivers (for sp3) can successfully connect
> to SQL Server w/ SP4 *when the "force protocol encryption" option is enabled*?
Anyone?
|||Anyone what?
|||Alin Sinpalean wrote:
> Anyone what?
>
Can someone confirm that the JDBC drivers (for sp3) can successfully
connect to SQL Server w/ SP4 *when the "force protocol encryption"
option is enabled*?
|||"Force protocol encryption" enabled? The MS driver never did support
SSL encryption. Use jTDS ( http://jtds.sourceforge.net ) or one of the
commercial drivers for that.
Disclaimer: I'm a jTDS developer.
Alin.

JDBC Driver for Windows 2003 Server

I have a java application in Win2000 Server connecting to
MS SQL Server 2000 via the JDBC Driver Service Pack 2. I
upgraded my OS to Windows 2003 Server, but when i try to
connect to MS SQL Server via the same JDBC Driver, it
fails, i get error "Microsoft SQL Server 2000 Driver for
JDBC: Error establishing to socket".
What driver should i use to correct this? I dont see any
updated drivers available in microsoft.com.
thanks a lot!
tk wrote:

> I have a java application in Win2000 Server connecting to
> MS SQL Server 2000 via the JDBC Driver Service Pack 2. I
> upgraded my OS to Windows 2003 Server, but when i try to
> connect to MS SQL Server via the same JDBC Driver, it
> fails, i get error "Microsoft SQL Server 2000 Driver for
> JDBC: Error establishing to socket".
> What driver should i use to correct this? I dont see any
> updated drivers available in microsoft.com.
> thanks a lot!
Hi. It's not the driver. It's probably the DBMS not yet being configured
to listen in mixed-mode (listen for TCPIP connections). Got to setup and
check that.
Joe Weinstein at BEA
|||Hi,
I have a application in java using JDBC driver, but the connection is
correct and i have access to information.
In some cases a process of a user blocks the others, and until it is not
eliminated, they cannot continue the others. I would like to know as the
mixed-mode is configurated.
Thanks.
LuisJaimeG.
"Joe Weinstein" <joeNOSPAM@.bea.com> escribi en el mensaje
news:uzpgY9%23REHA.2876@.TK2MSFTNGP09.phx.gbl...
>
> tk wrote:
>
> Hi. It's not the driver. It's probably the DBMS not yet being configured
> to listen in mixed-mode (listen for TCPIP connections). Got to setup and
> check that.
> Joe Weinstein at BEA
>
|||solution please
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||| From: "Luis J." <ljgutierrez@.avansoft.com>
| References: <166b701c447c7$0cff79f0$a101280a@.phx.gbl>
<uzpgY9#REHA.2876@.TK2MSFTNGP09.phx.gbl>
| Subject: Re: JDBC Driver for Windows 2003 Server
| Date: Mon, 14 Jun 2004 23:48:57 -0500
| Lines: 40
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <#fP4VPpUEHA.2844@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: 200.124.170.10
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGP12
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6102
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi,
|
| I have a application in java using JDBC driver, but the connection is
| correct and i have access to information.
|
| In some cases a process of a user blocks the others, and until it is not
| eliminated, they cannot continue the others. I would like to know as the
| mixed-mode is configurated.
|
| Thanks.
|
| LuisJaimeG.
Hi Luis,
Mixed mode authentication only matters when you are making your connection
to SQL Server. Once you're authenticated and connected, it no longer
applies. You can troubleshoot the blocking problem using the SQL Server
blocking script in conjunction with SQL Profiler.
271509 INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
If you already know the SPID that is causing the blocking, you can simply
run "DBCC INPUTBUFFER(<spid>)" in Query Analyzer, where <spid> is your SPID
number. This will give you the SQL statement that is running at the time.
You can run "sp_lock" in Query Analyzer to see the specific locks that are
held by the blocking SPID. Once you identify which query is causing the
blocking, you can take steps to optimize the query (adding indexes,
recoding the query, etc). The faster the query executes, the less blocking
you will see. If the query is part of a transaction, you want to minimize
the size of your transactions to improve their speed and ultimately reduce
the blocking time.
Coding Efficient Transactions
http://msdn.microsoft.com/library/de...us/acdata/ac_8
_md_06_3eba.asp
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||tk wrote:
> I have a java application in Win2000 Server connecting to
> MS SQL Server 2000 via the JDBC Driver Service Pack 2. I
> upgraded my OS to Windows 2003 Server, but when i try to
> connect to MS SQL Server via the same JDBC Driver, it
> fails, i get error "Microsoft SQL Server 2000 Driver for
> JDBC: Error establishing to socket".
> What driver should i use to correct this? I dont see any
> updated drivers available in microsoft.com.
> thanks a lot!
Use netstat on the server to determine wether anyone is listening on the
SQL Server port.
You propably did not install SQL Server Service Pack 3.
On Windows 2003 Server TCP/IP is disabled for SQL Server without SP 3
due to security issues. Look at the eventlog, you will propably find a
message stating something in that direction.
Install SP 3, reboot and you should be fine.
Daniel Hagen
|||tk wrote:
> I have a java application in Win2000 Server connecting to
> MS SQL Server 2000 via the JDBC Driver Service Pack 2. I
> upgraded my OS to Windows 2003 Server, but when i try to
> connect to MS SQL Server via the same JDBC Driver, it
> fails, i get error "Microsoft SQL Server 2000 Driver for
> JDBC: Error establishing to socket".
> What driver should i use to correct this? I dont see any
> updated drivers available in microsoft.com.
> thanks a lot!
Use netstat on the server to determine wether anyone is listening on the
SQL Server port.
You propably did not install SQL Server Service Pack 3.
On Windows 2003 Server TCP/IP is disabled for SQL Server without SP 3
due to security issues. Look at the eventlog, you will propably find a
message stating something in that direction.
Install SP 3, reboot and you should be fine.
Daniel Hagen
|||I also upgraded from 2000 sever to 2003 server. My application was working fine with sql server until the upgrade. As a last resort I downloaded the Microsoft SQL Server 2000 Service Pack 3a and it did the trick. No more Error establishing socket connecti
on errors!
you can get the Service pack at:
http://www.microsoft.com/downloads/d...displaylang=en
I only installed the sql2ksp3.exe file.
Good Luck!
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.