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:   ].

Sometimes I want to start with clean information. I can always add back in the owner and identifiers.

DECLARE @vcTableName varchar(128)
SET @vcTableName = 'dbo.test table'
SET @vcTableName = 'mytable.test'
SET @vcTableName = QUOTENAME('mytable.test table')
IF CHARINDEX('[', @vcTableName) > 0
BEGIN
SET @vcTableName = REPLACE(REPLACE(@vcTableName, '[', ''), ']', '')
END
IF CHARINDEX('.', @vcTableName) > 0
BEGIN
SET @vcTableName = SUBSTRING(@vcTableName, CHARINDEX('.', @vcTableName) + 1, LEN(@vcTableName) - CHARINDEX('.', @vcTableName))
END
PRINT '***' + @vcTableName + '***'
PRINT 'dbo.' + QUOTENAME(@vcTableName)

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:

Here's a trusted connection example.

SELECT *
FROM 	OPENDATASOURCE(
'SQLOLEDB',
'Data Source=SQLDEV;Integrated Security=SSPI'
).[pubs].dbo.[authors]

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

Here are some useful debugging information queries.

SET nocount ON
            USE master
GO
EXEC xp_msver
SELECT
    'Microsoft SQL Server ' +
            CONVERT(VARCHAR, SERVERPROPERTY('ProductVersion') ) + ' ' +
            CONVERT(VARCHAR, SERVERPROPERTY('ProductLevel') ) + ' ' +
            CONVERT(VARCHAR, SERVERPROPERTY('Edition') ) + ' ' +
            CONVERT(VARCHAR, SERVERPROPERTY('EngineEdition') ),
    @@VERSION
GO
            PRINT '** Database master compatibility level:'
EXEC sp_dbcmptlevel master
GO
            

Here is a good resource to figure out what version of SQL you are running using the version number.

Here' my helper function which I put in my master_admin database.

CREATE  FUNCTION fn_GetCurrentSQL (
   @sql_handle binary(20),
   @stmt_start INT,
   @stmt_end INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
   RETURN (

   SELECT 
       SUBSTRING(s.TEXT,
           COALESCE(NULLIF((@stmt_start/2)+1, 0), 1),
           CASE @stmt_end 
               WHEN -1 
                   THEN DATALENGTH(s.TEXT) 
               ELSE 
                   (@stmt_end - @stmt_start) 
               END
           ) 
   FROM    ::fn_get_sql(@sql_handle) s
)
END
GO

Here's my sp_who2 replacement script. 

CREATE      PROCEDURE dbo.sp__who2 (
        @nSortFlag INT = 0,
        @nMinAgo INT = NULL,
        @nSPID INT = NULL,
        @vcDBName VARCHAR(128) = NULL,
        @bAll bit = 0
    )
            AS
            /*
** Procedure

** Description

** Parameters

** Requirements

** Usage
EXEC dbo.sp_who2 0, 0
EXEC dbo.sp__who2 0, 0, 84
EXEC dbo.sp__who2 0, NULL, 84
EXEC dbo.sp__who2 NULL, NULL, NULL
EXEC dbo.sp__who2 0, NULL, NULL
EXEC dbo.sp__who2 1, NULL, NULL
EXEC dbo.sp__who2 2, NULL, NULL
EXEC dbo.sp__who2 NULL, 11, NULL, 'Optima'
EXEC dbo.sp__who2 0, 2, NULL
EXEC dbo.sp__who2 1, 2, NULL
EXEC dbo.sp__who2 1, 2, NULL, 'nhpri'

  

** Changes
  2008-02-19   John Perry  Removed system processes restriction. OR @nMinAgo IS NULL 
  2008-08-04           Added memusage.
  2008-09-25           Further defined waittype and added active flag and moved it.
   
*/
DECLARE @bootjobtime datetime
SELECT @bootjobtime = MIN(last_batch)
    FROM    master.dbo.sysprocesses   (nolock)
    WHERE spid < 20
SELECT
            CAST('A' AS VARCHAR(50)) SortC ,
            CAST(0 AS INT) SortN ,
            IDENTITY(INT, 1,1) AS ID_Num,
            CAST(DB_NAME(dbid) AS VARCHAR(35)) DatabaseName,
            spid,
            ecid ,
            BlkBy = CASE CONVERT(CHAR(5),ISNULL(blocked,'0'))
                WHEN '0' THEN '  .'
    ELSE CONVERT(CHAR(5),ISNULL(blocked,'0'))
    END,
            open_tran AS openx,
            physical_io AS io,
            cpu ,
            memusage,
            waittime AS waitMS ,
            CAST(ISNULL(master_admin.dbo.fn_GetCurrentSQL (sql_handle, stmt_start, stmt_end), cmd) AS VARCHAR(128)) cmd,
            hostname,
            program_name
            ,CONVERT(sysname, RTRIM(loginame))AS loginname
            ,CONVERT(VARCHAR,last_batch,121) AS last_date
            ,    CASE
                WHEN waittype = 0x0000  THEN lastwaittype
    ELSE '*' + lastwaittype
    END WaitType, waitresource
            INTO #tmpWHO
    FROM    master.dbo.sysprocesses   (nolock)
    WHERE    (ISNULL(blocked,'0') > 0
            OR
                open_tran > 0
    )
        AND (@nSortFlag IS NOT NULL)
    ORDER BY
            spid,
            ecid
INSERT #tmpWHO (SortC,SortN,
                DatabaseName, spid, ecid, BlkBy, openx, io, cpu,
                memusage, waitMS, cmd,
                hostname, program_name, loginname, last_date,     WaitType,
                waitresource)
    SELECT    CASE
                WHEN 1=@nSortFlag THEN 'B' + CONVERT(VARCHAR(35),DB_NAME(dbid) )
                WHEN 2=@nSortFlag THEN 'B' + CONVERT(VARCHAR(35),last_batch,121)
    ELSE 'B'
    END
            SortC ,
    CASE
                WHEN 0=0 THEN  spid
    ELSE 99
    END SortN ,
            CAST(DB_NAME(dbid) AS VARCHAR(35)) DatabaseName,
            spid,
            ecid ,
            BlkBy = CASE CONVERT(CHAR(5),ISNULL(blocked,'0'))
                WHEN '0' THEN '  .'
    ELSE CONVERT(CHAR(5),ISNULL(blocked,'0'))
    END,
            open_tran AS openx,
            physical_io AS io,
            cpu ,
            memusage,
            waittime AS waitMS ,
            cmd,
            hostname,
            program_name
            ,CONVERT(sysname, RTRIM(loginame))AS loginname
            ,CONVERT(VARCHAR,last_batch,121) AS last_date
            ,    CASE
                WHEN waittype = 0x0000  THEN lastwaittype
    ELSE '*' + lastwaittype    -- (active)
    END WaitType, waitresource
    FROM    master.dbo.sysprocesses   (nolock)
    WHERE    (
        (@nSortFlag IS NULL)
            OR
        (ISNULL(blocked,'0') = 0
                AND
                    open_tran = 0)
    )
        AND
    (@nSPID > 0 
            OR @nMinAgo IS NULL 
            OR last_batch >= DATEADD(minute, -@nMinAgo, GETDATE()))
    -- ORDER BY
    --  SortCol,
    --  ecid 
SELECT    DatabaseName, spid, ecid, BlkBy, openx, io, cpu,
            memusage, waitMS, WaitType,
            cmd,
            hostname, program_name, loginname, last_date,
            waitresource
    --, SortC,SortN,id_NUM
    FROM    #tmpWHO
    WHERE
    (@bAll = 1 
            OR last_date > @bootjobtime 
            OR @nMinAgo IS NULL )
        AND (@vcDBName IS NULL 
            OR DatabaseName LIKE '%' + @vcDBName + '%')
        AND (@nSPID IS NULL 
            OR @nSPID = 0 
            OR spid = @nSPID)
    ORDER BY
            SortC,SortN,
            spid,
            ecid
DROP TABLE #tmpWHO
GO