OPENROWSET to text file fails with Msg 7399 Could not find installable ISAM

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

ERROR: OLE/DB provider returned message: [Microsoft][ODBC Text Driver] System resource exceeded

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];'
)

PC seems to hang using SSMS? Saving autorecovery information?

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]

Auto-recovery status message

Change this:

Auto Recover Registry Change

NOTE: Failed to notify ‘operator’ via email.

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

Please refer MSDN

To set up SQL Server Agent Mail to use Database Mail

  1. In Object Explorer, expand a server.

  2. Right-click SQL Server Agent, and then click Properties.

  3. Click Alert System.

  4. Select Enable Mail Profile.

  5. In the Mail system list, choose Database Mail.

  6. In the Mail profile list, select a mail profile for Database Mail.

 

SQL Server – What am I running? Configurations.

Here are some useful debugging information queries.

SET nocount ON
            USE 
master
GO
EXEC xp_msver
SELECT
    
'Microsoft SQL Server ' +
            
CONVERT(VARCHARSERVERPROPERTY('ProductVersion') ) + ' ' +
            
CONVERT(VARCHARSERVERPROPERTY('ProductLevel') ) + ' ' +
            
CONVERT(VARCHARSERVERPROPERTY('Edition') ) + ' ' +
            
CONVERT(VARCHARSERVERPROPERTY('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.

SQL Log File Optimization. Check/fix your internal fragmentation

As a DBA you've gotta love Kimberly L. Tripp. Here is what she says.

8) Check/fix your internal fragmentation. OK, so this is the one thatreally helped the customer in Turkey. Often, when transaction logs arenot pre-allocated and/or when there's been a lot of autogrowths, thetransaction log can become internally fragmented. Internally yourtransaction logs are broken down into smaller more granular chunkscalled VLFs (Virtual Log Files). The size and number of VLFs you'llhave depends largely on the size that the chunk is when it's added toyou transaction log. If you add a new chunk to the transaction logwhich is 20MB (through autogrowth or through manual growth) then thenumber of VLFs that are added is 4. If you add a chunk which is greaterthan 64MB but less than or equal to 1GB, you'll add 8 VLFs. If you addmore than 1GB then you'll add 16VLFs. In general, most transaction logswill only have 20 or 30 VLFs – even 50 could be reasonable depending onthe total size of the transaction log. However, in many cases whathappens is that excessive autogrowths can cause an excessive number ofVLFs to be added – sometimes resulting in hundreds of VLFs. Having anexcessive number of VLFs can negatively impact all transaction logrelated activities and you may even see degradation in performance whentransaction log backups occur. To see how many VLFs you have solelylook at the number of rows returned by DBCC LOGINFO. The number of rowsreturned equals the number of VLFs your transaction log file has. Ifyou have more than 50, I would recommend fixing it and adjusting yourautogrowth so that it doesn't occur as fequently. To get rid of all ofthe execessive VLFs, follow these easy steps to shrink off thefragmented chunk and add a new, clean chunk to your transaction log:

1. Wait for an inactive time of day (ideally, it would be best toput the database into single user mode first) and then clear alltransaction log activity through a regular transaction log backup. Ifyou're using the simple recovery model then you don't need to do a logbackup… Instead, just clear the transaction log by running acheckpoint.

 BACKUP LOG databasename TO devicename

2. Shrink the log to as small a size as possible (truncateonly)

DBCC SHRINKFILE(transactionloglogicalfilename, TRUNCATEONLY)

NOTE: if you don't know the logical filename of your transaction log use sp_helpfile to list all of your database files.

3. Alter the database to modify the transaction log file to the appropriate size – in one step

ALTER DATABASE databasename
MODIFY FILE 

      NAME = transactionloglogicalfilename 
    , SIZE = newtotalsize
)

And, that should do it!

–klt

Given that, here is some of the code you might use to optimize your database log file internals. (VLF). I use my own sp__who2 but you just want to see who's in the database. The main feature of this script is it calculates the ALTER DATABASE log file recreate script — as mentioned above — so when you're done all the previous file growths have been beautifully reduced to this one growth. Instead of CHECKPOINT on FULL recovery databases you'll need to run a LOG backup.

SELECT 'USE ' DB_NAME() + ';' CHAR(13)+CHAR(10) +  
   
'EXEC sp__who2 1,null,null,' DB_NAME() + ';' CHAR(13)+CHAR(10) +  
   
'SELECT DATABASEPROPERTYEX(''' DB_NAME() + ''',''Recovery'');' CHAR(13)+CHAR(10) +  
   
'DBCC LOGINFO;' CHAR(13)+CHAR(10) +  
   
'CHECKPOINT;' CHAR(13)+CHAR(10) +  
   
'DBCC SHRINKFILE(' RTRIM(name) + ', TRUNCATEONLY);' CHAR(13)+CHAR(10) + 
   
'ALTER DATABASE ' DB_NAME() + ' MODIFY FILE (NAME = ' RTRIM(name) + 
   
', SIZE = ' CAST(size/1024*AS VARCHAR(25)) + ');'CHAR(13)+CHAR(10)   +
   
'DBCC LOGINFO;' 
FROM   dbo.sysfiles 
WHERE  FILEPROPERTY(name,'IsLogFile'1

 

Test for Linked Server connectivity

See if PING in a command window gives us a return value.
L:\>ping sqldev -n 1

Pinging sqldev.mylan.local [10.01.64.37] with 32 bytes of data:

Reply from 10.01.64.37: bytes=32 time<1ms TTL=128

Ping statistics for 10.01.64.37:
    Packets: Sent = 1, Received = 1, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms

L:\>ECHO %ERRORLEVEL%
0

L:\>ping sqldevx -n 1
Ping request could not find host sqldevx. Please check the name and try again.

L:\>ECHO %ERRORLEVEL%
1

It does -- see if we can utilize that using T-SQL. I'm using some of my helper procedures e.g. sp__PRINT but you can replace it with just PRINT commands for debugging.


SET nocount ON

DECLARE @RC INT,
   
@bVERBOSE bit,
   
@vcCmd VARCHAR(1024),
   
@vcSQL NVARCHAR(1024),
   
@vcServerName VARCHAR(128)

SELECT @bVERBOSE 1,
   
@vcServerName 'SQLDEV',
   
@vcCmd 'ping ' LEFT(@vcServerName CASE CHARINDEX('\',@vcServerName
           
WHEN THEN LEN(@vcServerName)  ELSE CHARINDEX('\',@vcServerName) -END) + ' -n 1',
   
@vcSQL 'IF NOT EXISTS (SELECT srvname FROM master.dbo.sysservers 
WHERE quotename(srvname) = quotename(''' 
@vcServerName ''') ) RAISERROR(''ERROR: no linked server to test'',16,1)'

EXEC @RC master.dbo.xp_cmdshell @vcCmdno_output 
IF @bVERBOSE 1
   
EXEC sp__PRINT @RC

IF @RC <> 0
BEGIN
   RAISERROR
('ERROR: Server %s NOT FOUND on the network.'16,1@vcServerName)
END ELSE
BEGIN
   IF 
@bVERBOSE 1
   
BEGIN
       EXEC 
sp__PRINT 'See if there is a linked server, locally' 
       
PRINT @vcSQL
   
END
   EXEC 
@RC sp_executesql    @vcSQL

       IF @bVERBOSE 1
       
BEGIN
           EXEC 
sp__PRINT  'Run REMOTE QUERY - Test remote linked server connectivity'
           
PRINT @vcSQL
       
END
       EXEC 
@RC sp_executesql    @vcSQL
       
IF @RC <> 0
       
BEGIN
           RAISERROR
('ERROR: connecting to linked server [%s].'16,1,@vcServerName)
       
END
   END ELSE
       SELECT 
srvname FROM master.dbo.sysservers
END    
GO

Here's some output:

IF NOT EXISTS (SELECT srvname FROM master.dbo.sysservers
  WHERE quotename(srvname) = quotename('SQLDEV') ) RAISERROR('ERROR: no linked server to test',16,1)
********************************************************************************
2009-02-11 14:29:00.013 0
********************************************************************************
********************************************************************************
2009-02-11 14:29:00.013 See if there is a linked server, locally
********************************************************************************
IF NOT EXISTS (SELECT srvname FROM master.dbo.sysservers
  WHERE quotename(srvname) = quotename('SQLDEV') ) RAISERROR('ERROR: no linked server to test',16,1)
********************************************************************************
2009-02-11 14:29:00.077 Run REMOTE QUERY – Test remote linked server connectivity
********************************************************************************
IF NOT EXISTS (SELECT srvname FROM [SQLDEV].master.dbo.sysservers
   WHERE quotename(srvname) = quotename('SQLDEV') )  RAISERROR('ERROR: cannot connect to linked server',16,1)

Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42

If you get this error and all else is as it should be, try this.

Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid

Run this and then the TestSQL output to make sure you've really got the correct Profile name.

SELECT [profile_id]
      
,[name] 
      
,[description]
      
,[last_mod_datetime]
      
,[last_mod_user]
   
,'EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''' 
name ''',
@recipients = ''me@test.com'', 
@subject = ''Test'',
@body  = ''Message'',
  @body_format = ''HTML'';' 
AS TestSQL
  
FROM [msdb].[dbo].[sysmail_profile]

 

Create a linked server to a local directory path

Here's what I did to successfully set up a linked server to a directory on a SQL 2000 or SQL 2005 server.


/* Remove any previous references to the linked server */
USE [master]
GO

/****** Object:  LinkedServer [LOCAL_PATH]    Script Date: 01/06/2009 14:04:46 ******/
EXEC master.dbo.sp_dropserver @server=N'LOCAL_PATH', @droplogins='droplogins'
GO

EXEC sp_addlinkedserver 'LOCAL_PATH',
    'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    'E:\MSSQL\Reports', NULL, 'Text'

GO

EXEC sp_tables_ex
   @table_server='LOCAL_PATH'


— If you get this error


–OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LOCAL_PATH" returned message "Unspecified error".
–Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
–Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LOCAL_PATH".

–Simply log onto the server as the same account that the sql services run as.
–Then under my computer properties changes the TEMP and TMP environment variables to something like
    C:\temp
    or
    C:\TempSQL
–which should have security permissions changed to allow local users to have RWC permissions.

 You might also lookup this on the Microsoft site.
    PRB: Error 7399 When You Run a Linked Server Query That Uses the OLE DB Provider for Microsoft Jet