JDBC Driver issue: February 2012

Friday, February 24, 2012

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)

Any help with this is greatly appreciated.

Mark.

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

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

-Curtis

|||

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

Any information that you can provide would be helpful.

Thank You

Mark

|||

hi mark,

I think a sharing violation took place in your access DB.
as we all know access database is not as roboust as sql server,
another user could have get in and use the Access database
locking and preventing sql server to use it.
To prevent this from happening make sure that no ther user is accesing the access mdb.

regards,

joey

|||

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

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

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

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

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

|||

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

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

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

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

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

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

He suggests following these steps:

Seradex wrote:
Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)
Specifically it was to ensure that the following Registry entry exists and is set to ZERO.
HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

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

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

[sys].[sp_MSset_oledb_prop]

At the end, there is the code:

if 1 = @.property_value

begin

declare @.val int

set @.val = @.property_value

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

end

else

begin

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

end

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

The procedure can be found using the following query:

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

|||

Anyone have any suggestions?

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

Closing and re-opening VS2005 does not help.

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

|||

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

|||

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

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

2: put machine name to the trust site in IE

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

Any help with this is greatly appreciated.

Mark.

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

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

-Curtis

|||

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

Any information that you can provide would be helpful.

Thank You

Mark

|||

hi mark,

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

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

another user could have get in and use the Access database

locking and preventing sql server to use it.

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

regards,

joey

|||

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

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

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

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

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

|||

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

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

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

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

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

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

He suggests following these steps:

Seradex wrote:
Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)
Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

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

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

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

[sys].[sp_MSset_oledb_prop]

At the end, there is the code:

if 1 = @.property_value

begin

declare @.val int

set @.val = @.property_value

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

end

else

begin

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

end

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

The procedure can be found using the following query:

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

|||

Anyone have any suggestions?

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

Closing and re-opening VS2005 does not help.

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

|||

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

|||

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

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

2: put machine name to the trust site in IE

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

Any help with this is greatly appreciated.

Mark.

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

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

-Curtis

|||

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

Any information that you can provide would be helpful.

Thank You

Mark

|||

hi mark,

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

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

another user could have get in and use the Access database

locking and preventing sql server to use it.

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

regards,

joey

|||

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

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

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

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

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

|||

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

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

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

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

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

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

He suggests following these steps:

Seradex wrote:
Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)
Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

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

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

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

[sys].[sp_MSset_oledb_prop]

At the end, there is the code:

if 1 = @.property_value

begin

declare @.val int

set @.val = @.property_value

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

end

else

begin

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

end

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

The procedure can be found using the following query:

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

|||

Anyone have any suggestions?

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

Closing and re-opening VS2005 does not help.

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

|||

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

|||

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

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

2: put machine name to the trust site in IE

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

Any help with this is greatly appreciated.

Mark.

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

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

-Curtis

|||

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

Any information that you can provide would be helpful.

Thank You

Mark

|||

hi mark,

I think a sharing violation took place in your access DB.
as we all know access database is not as roboust as sql server,
another user could have get in and use the Access database
locking and preventing sql server to use it.
To prevent this from happening make sure that no ther user is accesing the access mdb.

regards,

joey

|||

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

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

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

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

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

|||

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

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

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

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

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

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

He suggests following these steps:

Seradex wrote:
Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)
Specifically it was to ensure that the following Registry entry exists and is set to ZERO.
HKLM\SOFTWARE\Microsoft\Microsoft Sql Server\<Internal Instance-Name>\Providers\Microsoft.Jet.OLEDB.4.0
DisallowAdHocAccess = 0

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

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

[sys].[sp_MSset_oledb_prop]

At the end, there is the code:

if 1 = @.property_value

begin

declare @.val int

set @.val = @.property_value

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

end

else

begin

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

end

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

The procedure can be found using the following query:

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

|||

Anyone have any suggestions?

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

Closing and re-opening VS2005 does not help.

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

|||

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

|||

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

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

2: put machine name to the trust site in IE

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

Any help with this is greatly appreciated.

Mark.

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

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

-Curtis

|||

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

Any information that you can provide would be helpful.

Thank You

Mark

|||

hi mark,

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

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

another user could have get in and use the Access database

locking and preventing sql server to use it.

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

regards,

joey

|||

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

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

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

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

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

|||

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

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

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

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

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

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

He suggests following these steps:

Seradex wrote:
Does MS have any plans to release a fix for this? (I hope so, especially if it actually fixes the problem.)
Specifically it was to ensure that the following Registry entry exists and is set to ZERO.

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

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

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

[sys].[sp_MSset_oledb_prop]

At the end, there is the code:

if 1 = @.property_value

begin

declare @.val int

set @.val = @.property_value

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

end

else

begin

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

end

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

The procedure can be found using the following query:

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

|||

Anyone have any suggestions?

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

Closing and re-opening VS2005 does not help.

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

|||

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

|||

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

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

2: put machine name to the trust site in IE

jet to sql

I have an access dabase, and need to do the following:

In access I dim a database and a recordset. I then use seek to find if a
record exist.
If it exist I do an edit, if it doesn't exist, I do an addnew. I think any
programmer here gets the idea.

Now, How do I do this in the sql language used with sql server. Above, this
is all done in background, no user interface.
In other words, put in plain english:

Hey database, does TRIP_ID varid exist (seek varid in other words say varid
might be 55147 or whatever here.)
if so
edit
edit record here in code, no user interface.
else
addnew
add record here no user interface. Above isn't the code, but what I'm after
in english. It all works now using DAO.
NOTE: the data is plucked from an open form and stored into a different
table . It needs done like this because another time another table is
used. Trust me, it has to be seperate, not stored in same table open form
is based on. Key billing info is retrieved into a seperate table from more
than one source table. Please don't respond with using one table only.

I saw no seek or find commands in sql.

thanks, a long time programmer who got stumped.[posted and mailed, please reply in news]

JIMMIE WHITAKER (kpsklab@.worldnet.att.net) writes:
> Hey database, does TRIP_ID varid exist (seek varid in other words say
> varid might be 55147 or whatever here.)
> if so
> edit
> edit record here in code, no user interface.
> else
> addnew
> add record here no user interface. Above isn't the code, but what I'm
> after in english. It all works now using DAO.

IF EXISTS (SELECT * FROM tbl WHERE keycol = @.keyval)
BEGIN
UPDATE tbl
SET col1 = @.newval1,
col2 = @.newval2,
...
FROM tbl
WHERE keycol = @.keyval
END
ELSE
BEGIN
INSERT tbl (keycol, col1, col2, ...)
VALUES (@.keyval, @.newval1, @.newval2, ...)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Jet to Ansi SQL converter

I've got a weird question --
Have any of you ever come across software that can convert MS Jet SQL to ANSI standard SQL or other SQL flavors?
Could be a huge help in my next project. Please let me know if you know of anything like this!!
:)I'm not aware of any such software. And I probably wouldn't trust any that I came across. I just think it would be a difficult job writing software to interpret and translate all the crappy code that is out there!|||i do know that you can set the global database properties for microsoft access so that it will write it's queries in MS SQL format (ansi 92).

this would allow you to create simple queries in the query designer and then you could modify them and the designer would parse them but you can also do that in the graphical queries in the SQL Server 2000 Enterprise Manager

{For Access Queries in ANSI 92}
open a database
tools > options > Tables/Queries > SQL Server Compatible Syntax (ansi92)
choose this database or default for all new databases.

{For ANSI 92 Queries Graphically in SQL Server}
Open the SQL Server enterprise manager
expand Servers > [your server name] > Databases > [your database name]
open tables or views
right click one of the tables or views and select Open Table > Query
have at it.

i hope this helps

Jet SQL

Hi,

Can I use Jet SQL also for SQL Server 2000 or is it only for Access??

Greetz,

RoelNope, you can use it only for Access. Virtually every database engine extends the SQL standard in proprietary ways, and Access is no different.

Don

jet oledb from tsql

I have posted following question in sqlprogramming but have
not recieved a response so wondered whether I have posted
it correctly
[url]https://msdn.microsoft.com/newsgroups/managed/default.aspx?&query=OPENROWSET&lang=en&cr=US&guid= &sloc=en-us&dg=microsoft.public.sqlserver.programming&p=1&t id=1d7f7e0b-dfea-4606-ac59-dfac726d711e[/url]
Please help,
since the introduction of SQL2005 the default mode for opening access mdb
databases with Openrowset appears to be in exclusive open mode
I wish to open the database in share mode so the other (non net) ADO threads
can open the database at the same time
I only wish to open the database in read only mode
and so to use the mode param
I have tried the following
SELECT COUNT(*)
FROM
OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'"Database = h:\alarmlogfiles\new folder (3)\alarms 04 feb
2007.mdb; Mode = Share Deny None;"'
, 'SELECT [ID],
[System],
[Group],
[Message],
[Acknowledged],
[AckReq],
[Duplicates],
[Group Name],
[Importance],
[Ms],
[Alarm Date],
[LastDupe],
[Viewers],
[Row_Date],
[Colours],
[Status] From [Alarms 08 Feb 2007] Where 1 = 0' )
but get the following error from sql2005 & sql 2000
Server: Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Could not find installable ISAM.".
[vbcol=seagreen]
SELECT CustomerID, CompanyName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',Customers)
GO
but there is no opportunity here to add a open mode argument?
please help
best wishes nick
""privatenews"" wrote:

> Hello Nick,
> I understand that you 'd like to create Access database and access it from
> SQL Server via TSQL directly. If it is not correct, let me know.
> You may want to create linked server to Access database or use open
> openquery/openrowset or four-part name syntax to query access database.
> Please refer to teh following links for more informaitoin:
> http://msdn2.microsoft.com/en-us/library/ms190479.aspx
> 287414You cannot link tables into a Microsoft Access project by using an
> ODBC Data Source Name (DSN)
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;287414
> As for creating database, it is not feasible to create directly from
> opendatasource etc from SQL Server. You need to use adox/ado to do this as
> you'v done.
> 317881How to create an Access database by using ADOX and Visual C# .NET
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;317881
> If you have further questions, please let's know.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
Hello Nick,
Sorry for the late response and it seems there is sync issue in our
internal application for your account. I'v reported this issue to the
related team.
Per your question, I was able to reproduce the issue on my side. It seems
that we are not able to use provider or connection string in the
"datasource" parameter. It also doesn't work if I use provider string
directly:
SELECT CustomerID, CompanyName from
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Data Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=C:\northwind.mdb;Passord=""',
'select * from customers')
I'v forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Please let's know if you have any further feedback.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

jet oledb from tsql

I wish to create a new access database each week whos name includes the
curent date, then to create 7 tables in the database eg '27 Jul 2006.mdb' on
e
for each day
I then wish to insert rows into these tables,
currently I use managed adox & ado to do this, is it possible to use tsql to
get
sql server write the files from its oledb drivers, and would there be any
problems
doing this
in particular I do not know what arguments to pass to opendatasource to
create a new database.Hello Nick,
I understand that you 'd like to create Access database and access it from
SQL Server via TSQL directly. If it is not correct, let me know.
You may want to create linked server to Access database or use open
openquery/openrowset or four-part name syntax to query access database.
Please refer to teh following links for more informaitoin:
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
287414 You cannot link tables into a Microsoft Access project by using an
ODBC Data Source Name (DSN)
http://support.microsoft.com/defaul...kb;EN-US;287414
As for creating database, it is not feasible to create directly from
opendatasource etc from SQL Server. You need to use adox/ado to do this as
you'v done.
317881 How to create an Access database by using ADOX and Visual C# .NET
http://support.microsoft.com/defaul...kb;EN-US;317881
If you have further questions, please let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Nick,
I understand that you 'd like to create Access database and access it from
SQL Server via TSQL directly. If it is not correct, let me know.
You may want to create linked server to Access database or use open
openquery/openrowset or four-part name syntax to query access database.
Please refer to teh following links for more informaitoin:
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
287414 You cannot link tables into a Microsoft Access project by using an
ODBC Data Source Name (DSN)
http://support.microsoft.com/defaul...kb;EN-US;287414
As for creating database, it is not feasible to create directly from
opendatasource etc from SQL Server. You need to use adox/ado to do this as
you'v done.
317881 How to create an Access database by using ADOX and Visual C# .NET
http://support.microsoft.com/defaul...kb;EN-US;317881
If you have further questions, please let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||I have posted following question in sqlprogramming but have
not recieved a response so wondered whether I have posted
it correctly
https://msdn.microsoft.com/newsgrou...59-dfac726d711e
Please help,
since the introduction of SQL2005 the default mode for opening access mdb
databases with Openrowset appears to be in exclusive open mode
I wish to open the database in share mode so the other (non net) ADO threads
can open the database at the same time
I only wish to open the database in read only mode
and so to use the mode param
I have tried the following
SELECT COUNT(*)
FROM
OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'"Database = h:\alarmlogfiles\new folder (3)\alarms 04 feb
2007.mdb; Mode = Share Deny None;"'
, 'SELECT [ID],
[System],
[Group],
[Message],
[Acknowledged],
[AckReq],
[Duplicates],
[Group Name],
[Importance],
[Ms],
[Alarm Date],
[LastDupe],
[Viewers],
[Row_Date],
[Colours],
[Status] From [Alarms 08 Feb 2007] Where 1 = 0' )
but get the following error from sql2005 & sql 2000
Server: Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Could not find installable ISAM.".

SELECT CustomerID, CompanyName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',Customers)
GO
but there is no opportunity here to add a open mode argument?
please help
best wishes nick
""privatenews"" wrote:
[vbcol=seagreen]
> Hello Nick,
> I understand that you 'd like to create Access database and access it from
> SQL Server via TSQL directly. If it is not correct, let me know.
> You may want to create linked server to Access database or use open
> openquery/openrowset or four-part name syntax to query access database.
> Please refer to teh following links for more informaitoin:
> http://msdn2.microsoft.com/en-us/library/ms190479.aspx
> 287414 You cannot link tables into a Microsoft Access project by using an
> ODBC Data Source Name (DSN)
> http://support.microsoft.com/defaul...kb;EN-US;287414
> As for creating database, it is not feasible to create directly from
> opendatasource etc from SQL Server. You need to use adox/ado to do this as
> you'v done.
> 317881 How to create an Access database by using ADOX and Visual C# .NET
> http://support.microsoft.com/defaul...kb;EN-US;317881
> If you have further questions, please let's know.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||Hello Nick,
Sorry for the late response and it seems there is sync issue in our
internal application for your account. I'v reported this issue to the
related team.
Per your question, I was able to reproduce the issue on my side. It seems
that we are not able to use provider or connection string in the
"datasource" parameter. It also doesn't work if I use provider string
directly:
SELECT CustomerID, CompanyName from
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Data Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=C:\northwind.mdb;Passord=""',
'select * from customers')
I'v forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Please let's know if you have any further feedback.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Jet OLDB Error on 1/2 of A/A Cluster

Evening Folk ...
Have an Active/Active cluster of SS2K (SP3) on Windows 2003 server.
I have an SP that runs great on one node of the cluster BUT FAILS on the
other node of the cluster.
So in all the DBs (over 50) on NodeA the SP runs and returns the result set.
On NodeB , in all the DBs (over 40), the EXACT SAME SP failes with the
following error:
Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
Where should I start to look?
Where do I find where the 'Microsoft.Jet.OLEDB.4.0 is loaded?
Thanks ALL!
Thanks ...
J. KuschOdd that a Microsoft.Jet related error would be coming from a stored
procedure. I guess you have a remote query or are linking to a MS Access,
Excel, etc. file?
Anyhow, yes, I would suspect this failure is the result of MDAC not being
installed correctly on NodeB. However, this error seems to be generic and it
could be the result of an inproperly configured DNS, missing default
database, etc.
http://support.microsoft.com/?id=280102
There is a tool available for download called the MDAC Component Checker
that will determine what version of MDAC is installed and helps diagnose any
installation issues. Also, there is a download for MDAC (currently the
latest version is 2.8). Both are available for download from this location:
http://msdn.microsoft.com/data/mdac...ds/default.aspx
"Jay Kusch" <JayKusch@.discussions.microsoft.com> wrote in message
news:750B0A66-5EF9-4023-BC3E-21ACFD9C497A@.microsoft.com...
> Evening Folk ...
> Have an Active/Active cluster of SS2K (SP3) on Windows 2003 server.
> I have an SP that runs great on one node of the cluster BUT FAILS on the
> other node of the cluster.
> So in all the DBs (over 50) on NodeA the SP runs and returns the result
> set.
> On NodeB , in all the DBs (over 40), the EXACT SAME SP failes with the
> following error:
> Server: Msg 7399, Level 16, State 1, Line 2
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: Unspecified error]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
> Where should I start to look?
> Where do I find where the 'Microsoft.Jet.OLEDB.4.0 is loaded?
> Thanks ALL!
>
> --
> Thanks ...
> J. Kusch

Jet ODBC Driver for Text files - Permissions Issue

Hi,
I have setup a linked server to a text file uing the MS Jet ODBC driver.
Users that are not sysadmins and local admins on the server that the file
resides get an Jet Initialize error message.
What are the permissions required for SQL Server users to gain access to
this file?
Thanks.
Arun,
What happens if you run OPENDATASOURCE (See Books Online) against that
provider for the non-admin user account in Query Analyzer? You could also
enable trace flag 7300.
In Query Analyzer you would type something like:
DBCC TRACEON(7300)
go
SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',...
Please provide exact error message.
Regards,
James

Jet ODBC Driver for Text files - Permissions Issue

Hi,
I have setup a linked server to a text file uing the MS Jet ODBC driver.
Users that are not sysadmins and local admins on the server that the file
resides get an Jet Initialize error message.
What are the permissions required for SQL Server users to gain access to
this file?
Thanks.Arun,
What happens if you run OPENDATASOURCE (See Books Online) against that
provider for the non-admin user account in Query Analyzer? You could also
enable trace flag 7300.
In Query Analyzer you would type something like:
DBCC TRACEON(7300)
go
SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',...
Please provide exact error message.
Regards,
James

JET DB initilization problem help!!!

Hi I have set up a access database on a doc library and a infopath form based on this db published to a nearby folder. When I go to fill out the form, and it opens, I hit submit. But then it gives me a JET DB initilization form with login of admin and a blank pass. When I hit ok, it gives me a error saying

infopath The Microsoft Jet database engine cannot open the file '\\207.245.47.37\sites\Safety_and_Training\Experimental\db1.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

For some reason, once or twice its randomly been able to submit succcessfully but I havnt been able to reproduce the situation. Are there files that i am missing? any permissions that I need to check? The access database only has a admin and no password account. PLEASE HELP ME ive been working on this for ages! If you know a solid workaround with little programming let me know.

Hi,

This is a SQL Server forum, i suggest you direct your question to an Access specific forum where you should find someone able to help.

Cheers,

Leigh

Jet datasource on x64

Running SSRS on a 64bit machine. Have a legacy MS Access database that
we need to use as a datasource for select reports. Appears that Jet
was not ported to x64. Is my only recourse for connecting from SSRS to
Jet to uninstall SSRS x64 and install 32bit version?On Aug 22, 9:30 am, Jim <emailje...@.yahoo.com> wrote:
> Running SSRS on a 64bit machine. Have a legacy MS Access database that
> we need to use as a datasource for select reports. Appears that Jet
> was not ported to x64. Is my only recourse for connecting from SSRS to
> Jet to uninstall SSRS x64 and install 32bit version?
Just a quick followup that I decided against the 32bit version of SSRS
and instead installed an instance of SQL Express which runs 32 bit
inside WOW. SQL Express can use Jet as a linked server. and x64 SQL
Server can link to 32bit express. Not ideal, but gets me the data I
need untill we can do away with MS Access/Jet.
Cheers

JET Database Engine error

I've seen other postings for this error, but I wanted to see if anyone had something a little more solid on how they resolved the issue. When trying to execute a dts package I'm receiving the error:

the number of failing rows exceeds the maximum specified. (Microsoft Jet Database Engine (80004005). Could not update; currently locked. Does anyone have any suggestions? Thanks in advance!yeah, you've probably have data that is corrupt...

Can you modify so it goes in to a table with no contraints?

Where is the file coming from (where is it going)?

It's probably going to be dup primary key, validation rule exception, fk problem, or some other constraint...

just a guess...

JET Anonymous Merge: Can It Be Done?

Is it possible to set up
Publisher: SQL Server 2000
Subscriber: Access 97
Merge replication
Anonymous pull subscriptions
Over the Internet via VPN
I've read the BOL and other on-line artices, but don't quite get it.
Links to specific examples?
Thanks
Nope, you might be able to do a push if you could map a drive. The problem
is the linked server definition to enable access as a database you need to
use if you are replicating to a access database. Review the sample chapter
download in the nwsu link for my book for more info on how to replicate to
access.
The problem is in your term anonymous pull. For a pull to work you must have
SQL Server or MSDE installed on the remote machine.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<richerwin@.ubs-europe.org> wrote in message
news:1107726821.588526.272280@.l41g2000cwc.googlegr oups.com...
> Is it possible to set up
> Publisher: SQL Server 2000
> Subscriber: Access 97
> Merge replication
> Anonymous pull subscriptions
> Over the Internet via VPN
> I've read the BOL and other on-line artices, but don't quite get it.
> Links to specific examples?
> Thanks
>

Jesus said, "I am the way, the truth and the life: no one can

Mr. Mark wrote:
> Jesus said, "Blessed are those who don't spam the news groups making
> otherwise happy people curse my name."
Beatitudes like that will get you nowhere :)Harry Thompson wrote:

> They will get you now here.
_____________________
/| /| | |
||__|| | Please do not |
/ O O\__ | feed the |
/ \ | Trolls |
/ \ \|_____________________|
/ _ \ \ ||
/ |\____\ \ ||
/ | | | |\____/ ||
/ \|_|_|/ | _||
/ / \ |____| ||
/ | | | --|
| | | |____ --|
* _ | |_|_|_| | \-/
*-- _--\ _ \ | ||
/ _ \\ | / `'
* / \_ /- | | |
* ___ c_c_c_C/ \C_c_c_c____________
_____________________
/| /| | |
||__|| | Please do not |
/ O O\__ | feed the |
/ \ | Trolls |
/ \ \|_____________________|
/ _ \ \ ||
/ |\____\ \ ||
/ | | | |\____/ ||
/ \|_|_|/ | _||
/ / \ |____| ||
/ | | | --|
| | | |____ --|
* _ | |_|_|_| | \-/
*-- _--\ _ \ | ||
/ _ \\ | / `'
* / \_ /- | | |
* ___ c_c_c_C/ \C_c_c_c____________
_____________________
/| /| | |
||__|| | Please do not |
/ O O\__ | feed the |
/ \ | Trolls |
/ \ \|_____________________|
/ _ \ \ ||
/ |\____\ \ ||
/ | | | |\____/ ||
/ \|_|_|/ | _||
/ / \ |____| ||
/ | | | --|
| | | |____ --|
* _ | |_|_|_| | \-/
*-- _--\ _ \ | ||
/ _ \\ | / `'
* / \_ /- | | |
* ___ c_c_c_C/ \C_c_c_c____________

jeff

SQL .mdf file import.
How can i import a .mdf file(291MB in size) into a txt
file?
a step by step guide - as i know nothign about SQL 2000
thanks / Regards,
Jeff
Hi,
You can't import/export from a physical SQL server data file to a text file
or viceversa.
To do this first attach the MDF file as a database inside SQL server. After
that use the SQL server utilities BCP OUT or DTS to export the data from
tables to a Text file.
Thanks
Hari
MCDBA
"antz" <anonymous@.discussions.microsoft.com> wrote in message
news:58b501c42d31$6064e950$a001280a@.phx.gbl...
> SQL .mdf file import.
> How can i import a .mdf file(291MB in size) into a txt
> file?
> a step by step guide - as i know nothign about SQL 2000
> thanks / Regards,
> Jeff
|||thanks -
how do i go about attaching the mdf file?
to export it?
thanks
Jeff
>--Original Message--
>Hi,
>You can't import/export from a physical SQL server data
file to a text file
>or viceversa.
>To do this first attach the MDF file as a database
inside SQL server. After
>that use the SQL server utilities BCP OUT or DTS to
export the data from
>tables to a Text file.
>Thanks
>Hari
>MCDBA
>
>"antz" <anonymous@.discussions.microsoft.com> wrote in
message
>news:58b501c42d31$6064e950$a001280a@.phx.gbl...
>
>.
>
|||Hi Jeff,
Login to SQl server using the Query analyzer and try attaching the MDF file
as a database.
sp_attach_single_file_db
'dbname','physical_file_name_of_mdf_file_with_exac t_path'
Note:
Normally each database consists of 1 MDF and 1 LDF (Transactionlog) file. In
your case it seems you do not have the LDF file. So the above command may
file due to below reasons:-
1. If the database is not detached earlier
2. If the original database had more than 1 LDF files.
If your case does not fall in above categories then you will be able to
attach and then you can export contents to a text file using DTS or BCP.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:5a4a01c42d48$588653b0$a101280a@.phx.gbl...[vbcol=seagreen]
> thanks -
> how do i go about attaching the mdf file?
> to export it?
> thanks
> Jeff
> file to a text file
> inside SQL server. After
> export the data from
> message

jdbc-sql 2000 error

Hi,
I'm having trouble connecting to Sql Server 2000 via Java 2 (5.0).
I'm on Windows Server 2003 and trying to connect locally from the
server. I've downloaded the jar files (mssqlserver.jar,msutil.jar,
msbase.jar) containing the jdbc driver from microsoft and placed them in
the same directory as my java file, below is a snippet of my code to
test the connection:
public void openTest() throws Exception
{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver" );
Connection m_Conn = DriverManager.getConnection
("jdbc:microsoft:sqlserver://local:1433", "", "");
}
I compile using the following command there are no errors:
javac -classpath ".;./mssqlserver.jar;./msbase.jar;./msutil.jar"
Test.java
At runtime i get the following error,
Exception in thread "main" java.lang.ClassNotFoundException:
com.microsoft.jdbc.
sqlserver.SQLServerDriver
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at Hello.openTest(HelloPrint.java:30)
at HelloPrint.main(HelloPrint.java:11)
Can anyone help? Many thanks in advance.
InderHi
It's been a while since I used the JDBC driver!
microsoft.public.sqlserver.jdbcdriver may be a better newsgroup to post to.
You may want to make sure the .jar files are on the classpath and you may
want to re-run the setup just in case there is some corruption.
John
"inder" wrote:

> Hi,
> I'm having trouble connecting to Sql Server 2000 via Java 2 (5.0).
> I'm on Windows Server 2003 and trying to connect locally from the
> server. I've downloaded the jar files (mssqlserver.jar,msutil.jar,
> msbase.jar) containing the jdbc driver from microsoft and placed them in
> the same directory as my java file, below is a snippet of my code to
> test the connection:
> public void openTest() throws Exception
> {
> Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver" );
> Connection m_Conn = DriverManager.getConnection
> ("jdbc:microsoft:sqlserver://local:1433", "", "");
> }
>
> I compile using the following command there are no errors:
> javac -classpath ".;./mssqlserver.jar;./msbase.jar;./msutil.jar"
> Test.java
> At runtime i get the following error,
>
> Exception in thread "main" java.lang.ClassNotFoundException:
> com.microsoft.jdbc.
> sqlserver.SQLServerDriver
> at java.net.URLClassLoader$1.run(Unknown Source)
> at java.security.AccessController.doPrivileged(Native Method)
> at java.net.URLClassLoader.findClass(Unknown Source)
> at java.lang.ClassLoader.loadClass(Unknown Source)
> at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
> at java.lang.ClassLoader.loadClass(Unknown Source)
> at java.lang.ClassLoader.loadClassInternal(Unknown Source)
> at java.lang.Class.forName0(Native Method)
> at java.lang.Class.forName(Unknown Source)
> at Hello.openTest(HelloPrint.java:30)
> at HelloPrint.main(HelloPrint.java:11)
>
> Can anyone help? Many thanks in advance.
> Inder
>|||Hi John,
I've got that error fixed; you were right, it was a classpath
problem. Now i have another error,
[Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket.
when i run the following,
Connection m_Conn =
DriverManager.getConnection("jdbc:microsoft:sqlserver://servername:1433;
DatabaseName=Northwind","","");
The problem is that my 'servername' is the same as my windows server
2003 domain and when i setup my sql server 2000 i set it to use the
windows authentication, so now i'm not sure what to put for the username
and password in the above string.
Many thanks again,
Inderjit
*** Sent via Developersdex http://www.examnotes.net ***|||Hi
This may help a bit.
http://support.microsoft.com/defaul...kb;en-us;313100
AFIK the JDBC driver does not support Windows Authentication as it is
not a type 4 driver. You may want to look at
http://www.jnetdirect.com/products.php?op=jsqlconnect
John

JDBC: Retrieving stored procedures column information returns "-9" data type for nvarc

Hello,

I'm using the latest JDBC driver for SQL server 2005 (1.0.809.102).

Retrieving stored procedures column information returns "-9" data type for nvarchar:

public class driver {
public static void main(String[] args) throws java.lang.Throwable {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();

java.util.Properties properties = new java.util.Properties ();
properties.put("username", "sa");
properties.put("password", "sa");
properties.put("database", "GHTDB");

Connection con = DriverManager.getConnection("jdbc:sqlserver://POWERGH", properties);

ResultSet rs = con.getMetaData().getProcedureColumns("GHTDB", null, "SalesByCategory", null);

while (rs.next()) {
int i = rs.getInt("DATA_TYPE");
String s = rs.getString("TYPE_NAME");

System.out.println(i); // prints -9 for nvarchar
System.out.println(s);
}

}
}

Is this behavior documented anywhere and is by design?

Any help will be appreciated,
Regards,
Kosta

Kosta:

java.sql.Types has no value for any of the unicode text types so we return the SQL Server value for nvarchar (-9) rather than mis-reporting the type as a non-unicode varchar (12).

Both options here are bad, although, returning 12 in this case has the advantage of being wrong in the company of most other JDBC drivers, including our SQL Server 2000 JDBC driver.

If you think we should fix this, please file a bug at the product feedback center (http://lab.msdn.microsoft.com/productfeedback/) and we'll revisit the issue and make a change or doc it.

-shelby

Shelby Goerlitz

SQL Server Data Programmability -- JDBC

JDBC: DatabaseMetaData.getURL() returns jdbc:sqljdbc://

Hi,

I'm using:

Microsoft SQL Server 9.00.1399
Microsoft SQL Server 2005 JDBC Driver 1.0.809.102

Executing the DatabaseMetaData.getURL() method when database is connected returns "jdbc:sqljdbc://". The documentation for getURL() specifies that this URL is returned only when accessing pre SQL Server 2005 versions while when connected to 2005 it should return the correct URL
(ex: jdbc:sqlserver://192.168.1.105:1223;databaseName=test)

My question is why I don't get the correct URL as I connect to a 2005 database?

(Another related question is why a proper URL is not returned even when connected to a pre 2005 database?)

Regards

Roger

Minq Software
Author of DbVisualizer (http://www.dbvis.com/products/dbvis)

I don't know where did you get the documentation saying that getURL() will return more than "jdbc:sqljdbc://" for SQL Server 2005. According to our spec, it is the correct behavior.

Thanks

Bei

|||I read the following:

http://msdn2.microsoft.com/en-us/library/ms379009.aspx

But you're right and I was wrong that this behavior includes SQL Server 2005 as well.

Big question then is why it returns "jdbc:sqljdbc://"? Why does it return jdbc:sqljdbc to start with and secondly why doesn't it return the complete URL as specified at connect? (Beta versions of the driver returned the URL correctly (as I see it) but using the latest driver it works as above).

Regards

Roger
|||

It is for security reason. We don't want to leak sensitive information.

Thanks
Bei

|||Bei,

> It is for security reason. We don't want to leak sensitive information.

Can you please explain what security reasons there might be? We've tried JDBC drivers from 20+ vendors for all major databases and all of them returns the URL as it was specified during connect.

Second question is why jdbc:sqljdbc:// was choosed over the more intuitive jdbc:sqlserver://?

Regards

Roger

|||

Roger,

The fact that it is returning "jdbc:sqljdbc://" is obviously a bug, I have filed this internally but if you want to you can file it directly under http://lab.msdn.microsoft.com/productfeedback/default.aspx

The reasoning behind returning something other than the URL that the user passed in boils down to two issues.

1) We can't return password information with this method. This is considered a security bug and would not pass internal Microsoft security reviews. This may sound strange but there are good reasons behind it and it is consistent with all the drivers microsoft has shipped recently.

2) Here are some of the ways in which you can create a connection:

DriverManager.getConnection(url)
DriverManager.getConnection(url, info)
DriverManager.getConnection(url, user, password)
DataSource.getConnection()
DataSource.getConnection(username, password)

What would you expect getUrl to return in each of these cases? How usefull will what you are returning be without password information?

When we asked ourselves this questions it became clear that doing the work to return all values minus the password did not make a lot of sense.

If you have a scenario where having this information is important I would be happy to work with you to get this integrated with our next driver release. Sorry there is nothing I can do about the password information.

Hope this makes sense,

Angel

|||Angel,

Thanks for getting back to me.

Good to hear that you agree that the jdbc:sqljdbc:// is a bug.

When it comes to the URL in general it is supposed to return the URL, not URL + userid/password, just only the URL. If user or password is passed as arguments in the URL these should be removed. I strongly recommend that you check drivers from other vendors.

Thanks!

Regards

Roger

JDBC: calling a stored procedure with multiple return values.

Using JDBC, is there a way to call a stored procedure with multiple
return values? Thanks.
randy.p.ho@.gmail.com wrote:

> Using JDBC, is there a way to call a stored procedure with multiple
> return values? Thanks.

Absolutely. What do you mean by 'multiple return values'? Multiple output
parameters? Multiple result sets and/or update counts? Multiple mixes of
result sets and update counts?
If you will show the procedure signature and maybe even the text? Tell
us what the body of the procedure returns.

Joe Weinstein at BEA|||Thanks for the reply. I meant "multiple output parameters".

Here is how I execute the stored procedure:

declare @.ErrorID int
declare @.ErrorStr varchar(255)
exec procName
@.customerId = '1234567890',
@.customerName = 'some name',
@.error_code = @.ErrorID,
@.error_state = @.ErrorStr

Here is the procedure:

create procedure uxt1.procName
@.customerId char(15) output,
@.customerName char(64) output,
@.error_code int output,
@.error_state varchar(255) output
... ...
/* all the business logic */
... ...
return (@.error_state)
GO

Here's what SQL server gives me if I do a "Script object as Execute":
DECLARE @.RC int
DECLARE @.customerId char(15)
DECLARE @.customerName char(64)
DECLARE @.error_code int
DECLARE @.error_state varchar(255)
EXEC @.RC = [uxt1].[procName] @.customerId, @.customerName, @.error_code
OUTPUT , @.error_state OUTPUT

The following is what I've tried in a Java program:
...
CallableStatement cs = conn.prepareCall(" {? = call
uxt1.procName(?,?,?,?)}" );
cs.registerOutParameter(1,java.sql.Types.INTEGER);
cs.setString(2,"some ID");
cs.setString(3,"some Name");
cs.registerOutParameter(4,java.sql.Types.INTEGER);
cs.registerOutParameter(5,java.sql.Types.VARCHAR);
ResultSet rs = cs.executeQuery();
...

My code doesn't throw any exception; but the procedure was not executed
correctly (i.e. it's not doing what it's supposed to do, which is to
simply insert some values into a table).
Any help is appreciated. Thanks in advance.|||
randy.p.ho@.gmail.com wrote:

> Thanks for the reply. I meant "multiple output parameters".
> Here is how I execute the stored procedure:
> declare @.ErrorID int
> declare @.ErrorStr varchar(255)
> exec procName
> @.customerId = '1234567890',
> @.customerName = 'some name',
> @.error_code = @.ErrorID,
> @.error_state = @.ErrorStr
>
> Here is the procedure:
> create procedure uxt1.procName
> @.customerId char(15) output,
> @.customerName char(64) output,
> @.error_code int output,
> @.error_state varchar(255) output
> ... ...
> /* all the business logic */
> ... ...
> return (@.error_state)
> GO
>
> Here's what SQL server gives me if I do a "Script object as Execute":
> DECLARE @.RC int
> DECLARE @.customerId char(15)
> DECLARE @.customerName char(64)
> DECLARE @.error_code int
> DECLARE @.error_state varchar(255)
> EXEC @.RC = [uxt1].[procName] @.customerId, @.customerName, @.error_code
> OUTPUT , @.error_state OUTPUT
>
> The following is what I've tried in a Java program:
> ...
> CallableStatement cs = conn.prepareCall(" {? = call
> uxt1.procName(?,?,?,?)}" );
> cs.registerOutParameter(1,java.sql.Types.INTEGER);
> cs.setString(2,"some ID");
> cs.setString(3,"some Name");
> cs.registerOutParameter(4,java.sql.Types.INTEGER);
> cs.registerOutParameter(5,java.sql.Types.VARCHAR);
> ResultSet rs = cs.executeQuery();
> ...
> My code doesn't throw any exception; but the procedure was not executed
> correctly (i.e. it's not doing what it's supposed to do, which is to
> simply insert some values into a table).
> Any help is appreciated. Thanks in advance.

Is the procedure executing at all? You shouldn't be calling executeQuery()
unless the first thing the procedure does is a select. Use execute() and
then loop:

cs.execute();
while (true)
{
int update_count = ps.getUpdateCount();
ResultSet rs = ps.getResultSet();
if ((rs == null && (update_count == -1)) break; // done

if (rs != null) process rs;
ps.getMoreResults();
}
// after processing inline results, call ps.getXXX() to get output parameters.

Whatever jdbc driver you're suing is pretty flakey if it
returns a result set from executeQuery() and the
procedure didn't do a select for data to go to the caller...

Joe Weinstein at BEA|||I have closely the same problem, excepting that my stored procs returns a "TABLE".

In fact, I have a SQL function that must return a TABLE and get 2 parameters. Its signature is as follow:

FUNCTION myFunction (@.codett varchar(40), @.codet varchar(40) )
RETURNS @.Tab TABLE([cp] [varchar] (40), [pourcentage] [int])

I'd like to call it from my DAO (using JDBC). having that 'cs' is a CallableStatement, I tried:

cs = jdbcConnection.prepareCall("{? = call myFunction(?, ?)}");
cs.registerOutParameter( 1, Types.OTHER );
cs.setString(2, codett);//codett contains a String
cs.setString(3, codet);//codet contains a String
ResultSet rset = cs.executeQuery();

However, when a get to the "executeQuery()" method, I get an SQLException saying that "myFunction" is a function Object.

have you met such a problem ?

Friday, February 24, 2012

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

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

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

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

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

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

jet to sql

Jet to Ansi SQL converter

Jet SQL

jet oledb from tsql

jet oledb from tsql

Jet OLDB Error on 1/2 of A/A Cluster

Jet ODBC Driver for Text files - Permissions Issue

Jet ODBC Driver for Text files - Permissions Issue

JET DB initilization problem help!!!

Jet datasource on x64

JET Database Engine error

JET Anonymous Merge: Can It Be Done?

Jesus said, "I am the way, the truth and the life: no one can

jeff

jdbc-sql 2000 error

JDBC: Retrieving stored procedures column information returns "-9" data type for nvarc

JDBC: DatabaseMetaData.getURL() returns jdbc:sqljdbc://

JDBC: calling a stored procedure with multiple return values.

JDBC Driver issue

Blog Archive

About Me