Error: "Ole Db Provider "Msdasql" for Linked Server "(Null)" Returned Message "[Microsoft][Odbc Driver Manager] Data Source Name Not Found ..."

Error: OLE DB provider MSDASQL for linked server (null) returned message [Microsoft][ODBC Driver Manager] Data source name not found ...

I just tried it on x64 Win7 and made it work. I think there are a couple problems.

1. I believe you have to add a space
   between *.txt; and *.csv
2. Don't include the path with the file name

This worked:

select * from OpenRowset('MSDASQL', 
         'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:\;', 
         'select top 10 * from x.csv')

The OLE DB provider Microsoft.ACE.OLEDB.12.0 for linked server (null)

http://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx

This solves the issue.
For some reason SQL Server does not like the default MSSQLSERVER account. Switching it to a local user account resolves the issue.

The operation could not be performed because OLE DB provider SQLNCLI11 for linked server was unable to begin a distributed transaction

If after configuring your MS Distributed Transaction Coordinator (MSDTC) on the two SQL server's according to the OP's original post, you still get "no transaction active", you should check that each host is reachable via the IP (assuming that's what you've used) registered in the linked server.

For example; on a recent setup, two SQL servers were reachable through a network in the 192.168.200.x range (same subnet), but each server was also indirectly connected through an IP in the 10.x.x.x range. On one SQL Server, the DNS server it used kept resolving the target SQL server to it's 10.x.x.x IP (which was firewalled) even though the linked server entry used the IP in the 192.168.200.x of the target server.

It appears that MSDTC uses the hostname of the server, while SQL server connects over any linked connection using the IP or hostname defined in the linked server entry, leading to this confusing behaviour of apparent connectivity when checking the target linked server within SQL Management Studio, but inability to execute remote procedures on the target.

The solution was to add entries in the host file's (%windir%\system32\drivers\etc\hosts) to explicitly force each SQL server to resolve the other to the IP address on the 192.168.200.x network.

On host 1 (IP 192.168.200.15):

# TARGET SERVER
192.168.200.20    targetserverhostname.and.any.domain.suffix  targetserverhostname

On host 2 (IP 192.168.200.20)

# SOURCE SERVER
192.168.200.15    sourceserverhostname.and.any.domain.suffix sourceserverhostname

Don't forget to ensure MSDTC has been configured according to the OP's screenshot above allowing network access and (if required) No Authentication.

Simba Mongo ODBC driver: returned data that does not match expected data length

Linked Server is very picky about metadata and the data that is returned, in general you're more likely to encounter problems if your defined metadata doesn't match exactly what is expected when using it vs. using other applications.

What's happening in this case is that you're retrieving data with a string column defined. The data in the string column has a length of 290, but the driver is reporting a length of 255. This is because MongoDB doesn't return metadata about the length of any specific field as it is a schema-less data source. The driver instead uses a default for reporting lengths of string columns, which by default is set to 255. You can change this by opening the Configuration Dialog for the DSN, going to the Advanced Options, and changing the Standard string column length from 255 to something larger, like 512. This should allow Linked Server to behave properly unless your data exceeds 512 bytes, in which case you should simply adjust this to a larger appropriate value.

Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null

I have MS Sql server 2012, and Office 2013. This seems to be very finicky, so you may have to adjust to your particular versions.

1. Download the Microsoft.ACE.OLEDB.12.0 for Windows, 64 bit version found here: https://www.microsoft.com/en-us/download/details.aspx?id=13255
2. Install it on your server.
3. Check the user running SQL Server and make sure that user has access to the temp directory C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp if it's a local service account or C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp if it's a network service account.
4. Configure 'Ad Hoc Distributed Queries' and enable the Microsoft.ACE.OLEDB files like this:

Here's the SP_CONFIGURE commands:

SP_CONFIGURE 'show advanced options', 1; 
GO 
RECONFIGURE; 
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1; 
GO 
RECONFIGURE; 
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1   
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParam', 1

On newer SQL Server 2014 You had use 'DynamicParameters' instead of 'DynamicParam'

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

Make sure you register msexcl40.dll like this:

regsvr32 C:\Windows\SysWOW64\msexcl40.dll

General error Unable to open registry key Temporary (volatile) ... from Access ODBC

Causes

General error Unable to open registry key Temporary (volatile) Ace DSN for process ...

This is the top-level error message produced by the Access Database Engine (a.k.a. "ACE") ODBC driver when the current process is unable to open the Access database file for one of the following reasons:

1. Some other process has opened the database "exclusively".

2. Some other process originally opened the database file in Access as "shared" and
   has some pending structural modification that requires "exclusive" access to the file. Examples of such pending modifications are edits to Module code that have not yet been saved, or having a Form or Report open in Design View.

3. The account under which the current process is running does not have sufficient filesystem permissions to open the database file or the folder in which it resides.

4. The account under which the current process is running does not have sufficient registry permissions to access the values under the HKLM\SOFTWARE\ODBC key.

5. The database file simply does not exist.

6. The database you are trying to open requires a newer version of Microsoft Access.

When only the top-level error message is reported, the earlier "Jet" ODBC driver produced somewhat more intuitive error messages. When the other process had done an "Open Exclusive" on the file the error message was

Could not use '(unknown)'; file already in use.

and when the file had pending design changes the error message said

The database has been placed in a state by an unknown user that prevents it from being opened or locked.

However, when we only see the top-level message produced by the ACE ODBC driver all we see is

General error Unable to open registry key Temporary (volatile) Ace DSN for process ...

That is because both drivers return multiple error messages, but they return them in a different order. The Jet messages are ...

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The database has been placed in a state by an unknown user that prevents it from being opened or locked.

ERROR [01000] [Microsoft][ODBC Microsoft Access Driver]General Warning Unable to open registry key 'Temporary (volatile) Jet DSN for process 0xed4 Thread 0x1204 DBC 0xab004 Jet'.

ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR [01000] [Microsoft][ODBC Microsoft Access Driver]General Warning Unable to open registry key 'Temporary (volatile) Jet DSN for process 0xed4 Thread 0x1204 DBC 0xab004 Jet'.

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The database has been placed in a state by an unknown user that prevents it from being opened or locked.

... while the ACE messages are:

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0xf6c Thread 0x1568 DBC 0x6347fec Jet'.

ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0xf6c Thread 0x1568 DBC 0x6347fec Jet'.

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The database has been placed in a state by an unknown user that prevents it from being opened or locked.

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0xf6c Thread 0x1568 DBC 0x6347fec Jet'.

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key Temporary (volatile) Ace DSN for process 0xf6c Thread 0x1568 DBC 0x6347fec Jet'.

ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] The database has been placed in a state by an unknown user that prevents it from being opened or locked.

Solutions

Scenarios 1 and 2 (another process has an "exclusive" lock on the file):

Ensure that the database file is not opened by any other process – often that means just closing the Access application itself – and then re-try the ODBC operation from your external application. Rebooting your machine is one way to ensure that any other such processes have been terminated and have relinquished their claim to the database file.

Scenario 3 (insufficient file/folder permissions):

Either adjust the permissions or move the file to a location where the user can open the file. Placing the database file in the root folder of a drive is a common cause of this type of problem.

Scenario 4 (insufficient registry permissions):

Adjust the registry permissions to allow the account to access the HKLM\SOFTWARE\ODBC key. The most common cause in this case is that an IIS process is running under an account that does not have normal "user" privileges. In that case remember that you should not be using Access databases with IIS anyway.

Scenario 5 (database file does not exist):

Fix the connection string so that it contains a valid path to an existing file.

Scenario 6 (database file requires a newer version of Access):

Upgrade Microsoft Access or the Access Database Engine Redistributable (if available).

---

Related Topics