Unless you are an 'sa' you probably will get this error.

Fix it by adding this setting to the registry.

Value Data
DisallowAdHocAccess 0

--
-- Quickly check your registry settings running this on SQL 2005+ servers

EXEC master..xp_regenumvalues 
                        'HKEY_LOCAL_MACHINE',
                        'SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\Providers\MSDASQL'

-- or on SQL 2000

EXEC master..xp_regenumvalues 
                        'HKEY_LOCAL_MACHINE',
                        'SOFTWARE\Microsoft\MSSQLServer\Providers\MSDASQL\'

1.In Add/Remove programs
2.Click the 'Uninstall/Change' button for "SQL Server 2008 R2"
3.In the coming dialog, click 'Remove'
4.Click 'OK'
5.In the 'Select Instance' dialog,  select the appropriate instance of the SQL Server (!) and then click 'Next'
6.In the 'Select Features' dialog, please only select 'Reporting Services'
7.Then click 'Next' and 'Next' till the uninstalling process starting

Step 6

Step 7

Up until SQL Server 2008, I didn't need PowerShell to manage my dozen servers and know exactly what was going on. Now I do.

And the benefits and capabilities are plentiful! You can start to be convinced by reading this article, "Why This SQL Server DBA is Learning Powershell", brilliantly conceived.

Next, use this site as a resource and examples.

Here is a great set of examples by MAK.

USE MyDatabase
GO

-- Windows 2000 / XP
-- Replace "{servername}" with your SQL Server name

EXEC sp_grantlogin [{servername}\ASPNET] 
EXEC sp_grantdbaccess [{servername}\ASPNET], [NETAPP]

-- GRANT EXECUTE ON [ProcedureName] TO [NETAPP]
grant exec on [dbo].[usp_My_Procedure] TO NETAPP 
GO

-- Windows Server 2003
-- Info.

EXEC sp_grantlogin [NT AUTHORITY\NETWORK SERVICE]
EXEC sp_grantdbaccess [NT AUTHORITY\NETWORK SERVICE]

GRANT EXECUTE ON [ProcedureName] TO [NT AUTHORITY\NETWORK SERVICE]
GO

1. http://en.wikipedia.org/wiki/RAID
2. http://www.raidarray.com/04_01_50.html

SELECT *   
FROM  OPENROWSET (
  'MSDASQL','Driver=Microsoft Visual FoxPro Driver;SourceDB=E:\DATA;SourceType=DBF;',
  'SELECT  * FROM MyFreeTable;'
)

Once the correct drivers were installed we could run the query, above, and create ODBC connections. Then, we were getting this error, but only when not running directly on the server itself.

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Visual FoxPro Driver]File 'hprofile.dbf' does not exist.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

So, our OPENROWSET queries and the linked server queries could be run locally to the server. There must still be some proxy permissions or security something we still need to set. 

Still, another error we're getting is this: 
Cannot get the schema rowset 'DBSCHEMA_CATALOGS' for OLE DB provider "SQL Server" for linked server "(null)" -- huh?

This problem occurs occasionally after working for a period of time.

The only way I've found to resolve this is to restart the SQL Server and Agent processes.

I've tried refreshing the registry entry (See MS Article ID: 209805) for the file Mstext40.dll / HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text, without change.

Here's an example of the T-SQL query.

SELECT 
*
FROM   OPENROWSET('MSDASQL',  
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=E:\MSSQL\;', 
'SELECT * FROM [Myfile#txt];'
)

Here's another version of the error.

Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'MSDASQL' reported an error.  
[OLE/DB provider returned message: 
[Microsoft][ODBC Text Driver] System resource exceeded.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' 
IColumnsInfo::GetColumnsInfo returned 0x80004005:   ].

PROBLEM Error message:

[OLE/DB provider returned message: [Microsoft][ODBC Text Driver] System resource exceeded.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:   ]

SOLUTION:

I got this error running an import of a text file using a bcp format file and BULK INSERT. The solution was to use a different server. I'd run the command on a SQL Server 2000, Windows 2000 server. I seem to remember something about an issue about some DLL mismatch with the OS or another server. Or maybe even after we installed SSMS for SQL 2005.

Trying the same command on a Windows Server 2003 with the SQL 2000 instance (SQL 2005 instance was also on that server) worked like a charm.

SELECT 
*
FROM   OPENROWSET('MSDASQL',  
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=E:\Data\;', 
'SELECT * FROM [ImportFile#txt];'
)

Find your connection seems to hang?

 The problem presents itself using SSMS, and as it turns out it is running something like
    saving auto recovery information

Change this registry setting to 0. This location was on the server.
    HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM\General\AutoRecover

On my PC it was
    [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\90\Tools\Shell\General\AutoRecover]

Change this:

On SQL 2005 you have to tell SQL Server Agent / Properties / Alert System, which profile that you'd like to enable. Here's what it does.

e.g. and don't forget to restart SQL Agent.

USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite 
   N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
   N'DatabaseMailProfile', 
   N'REG_SZ', 
   N'MyDatabaseProfile'
GO