Question: How do I fix these unexplained intermittent errors when using CREATE SERVER CLASS 'DIRECTORY' to get a list of the file names in a folder?
- From a SELECT statement even though the CREATE EXISTING TABLE works OK:
SQLCODE = -141, SQLSTATE = 42W33, ERRORMSG() = Table '...' not found
- From a CREATE EXISTING TABLE statement even though the CREATE EXTERNLOGIN is correct:
SQLCODE = -656, SQLSTATE = HY000, ERRORMSG() = Unable to connect to server '...': DirectoryAccess servers require an externlogin
Answer: Switch to using
sp_list_directory(); here's how:
- Set the max_depth parameter to 1 in the sp_list_directory() call if you are using SUBDIRS=0 in the CREATE SERVER statement.
- Use SELECT TOP 1 and sa_split_list() to get the file name out of the file_path column returned by sp_list_directory().
- Use LATERAL to join the sp_list_directory() and sa_split_list() calls.
By Example
After several years of intermittent errors, the SERVER CLASS 'DIRECTORY' method was recently judged
too fragile for further use in Foxhound. Here is the before-and-after code for the four sections that were changed:
Example 1 BEFORE: SERVER CLASS 'DIRECTORY'
CREATE SERVER local_folder CLASS 'DIRECTORY' USING 'ROOT={@foxhound_folder};SUBDIRS=0';
CREATE EXTERNLOGIN DBA TO local_folder;
CREATE EXISTING TABLE local_file AT 'local_folder;;;.';
SELECT TOP 1
local_file.size,
local_file.file_name
INTO @size,
@file_name
FROM local_file
WHERE local_file.file_name LIKE @patch_file_name_like_pattern
AND NOT EXISTS ( SELECT *
FROM rroad_applied_patch
WHERE rroad_applied_patch.file_name = local_file.file_name )
ORDER BY local_file.file_name ASC;
|
Example 1 AFTER: sp_list_directory()
SELECT TOP 1
sp_list_directory.file_size AS size,
local_file.file_name AS file_name
INTO @size,
@file_name
FROM sp_list_directory ( @foxhound_folder, 1 ),
LATERAL ( SELECT TOP 1
sa_split_list.row_value AS file_name
FROM sa_split_list ( sp_list_directory.file_path, '\\' )
ORDER BY sa_split_list.line_num DESC
) AS local_file
WHERE sp_list_directory.file_type = 'F'
AND local_file.file_name LIKE @patch_file_name_like_pattern
AND NOT EXISTS ( SELECT *
FROM rroad_applied_patch
WHERE rroad_applied_patch.file_name = local_file.file_name )
ORDER BY local_file.file_name ASC;
|
Example 2 BEFORE: SERVER CLASS 'DIRECTORY'
CREATE SERVER dynamic_source_help_folder
CLASS 'DIRECTORY' USING 'ROOT=C:/projects/foxhound5/help/dynamic_source;SUBDIRS=0';
CREATE EXTERNLOGIN DBA TO dynamic_source_help_folder;
CREATE EXISTING TABLE dynamic_source_help_folder AT 'dynamic_source_help_folder;;;.';
INSERT temp_help
SELECT dynamic_source_help_folder.file_name ,
dbo.xp_read_file ( STRING ( 'C:/projects/foxhound5/help/dynamic_source/', dynamic_source_help_folder.file_name ) )
FROM dynamic_source_help_folder
WHERE dynamic_source_help_folder.file_name LIKE '%.html'
ORDER BY dynamic_source_help_folder.file_name;
|
Example 2 AFTER: sp_list_directory()
INSERT temp_help
SELECT dynamic_source_help_folder.file_name ,
dbo.xp_read_file ( STRING ( 'C:/projects/foxhound5/help/dynamic_source/', dynamic_source_help_folder.file_name ) )
FROM sp_list_directory ( 'C:/projects/foxhound5/help/dynamic_source', 1 ),
LATERAL ( SELECT TOP 1
sa_split_list.row_value AS file_name
FROM sa_split_list ( sp_list_directory.file_path, '\\' )
ORDER BY sa_split_list.line_num DESC
) AS dynamic_source_help_folder
WHERE sp_list_directory.file_type = 'F'
AND dynamic_source_help_folder.file_name LIKE '%.html'
ORDER BY dynamic_source_help_folder.file_name;
|
Example 3 BEFORE: SERVER CLASS 'DIRECTORY'
CREATE SERVER backup_folder CLASS 'DIRECTORY' USING 'ROOT={@backup_folder};SUBDIRS=0';
CREATE EXTERNLOGIN DBA_activation TO backup_folder;
CREATE EXISTING TABLE backup_file AT 'backup_folder;;;.';
INSERT backup_file_drop_down_list_entry
SELECT ROW_NUMBER() OVER ( ORDER BY backup_file.file_name DESC ) AS row_number,
backup_file.file_name AS file_name
FROM backup_file
WHERE backup_file.file_name LIKE STRING ( 'foxhound5-', @build_number, '-mini-backup-%.txt' )
ORDER BY backup_file.file_name DESC;
|
Example 3 AFTER: sp_list_directory()
INSERT backup_file_drop_down_list_entry
SELECT ROW_NUMBER() OVER ( ORDER BY file_name.file_name DESC ) AS row_number,
file_name.file_name AS file_name
FROM sp_list_directory ( @backup_folder, 1 ),
LATERAL ( SELECT TOP 1
sa_split_list.row_value AS file_name
FROM sa_split_list ( sp_list_directory.file_path, '\\' )
ORDER BY sa_split_list.line_num DESC
) AS file_name
WHERE sp_list_directory.file_type = 'F'
AND file_name.file_name LIKE STRING ( 'foxhound5-', @build_number, '-mini-backup-%.txt' )
ORDER BY file_name.file_name DESC;
|
Example 4 BEFORE: SERVER CLASS 'DIRECTORY'
CREATE SERVER backup_folder CLASS 'DIRECTORY' USING 'ROOT={@backup_folder};SUBDIRS=0';
CREATE EXTERNLOGIN DBA TO backup_folder;
CREATE EXISTING TABLE backup_file AT 'backup_folder;;;.';
FOR f_backup_file AS c_backup_file INSENSITIVE CURSOR FOR
SELECT TOP ALL START AT ( @mini_backup_retention_count + 1 )
backup_file.file_name AS @file_name
FROM backup_file
WHERE backup_file.file_name LIKE STRING ( 'foxhound5-', @build_number, '-mini-backup-%.txt' )
ORDER BY backup_file.file_name DESC
FOR UPDATE
DO
|
Example 4 AFTER: sp_list_directory()
FOR f_backup_file AS c_backup_file INSENSITIVE CURSOR FOR
SELECT TOP ALL START AT ( @mini_backup_retention_count + 1 )
backup_file.file_name AS @file_name
FROM sp_list_directory ( @backup_folder, 1 ),
LATERAL ( SELECT TOP 1
sa_split_list.row_value AS file_name
FROM sa_split_list ( sp_list_directory.file_path, '\\' )
ORDER BY sa_split_list.line_num DESC
) AS backup_file
WHERE sp_list_directory.file_type = 'F'
AND backup_file.file_name LIKE STRING ( 'foxhound5-', @build_number, '-mini-backup-%.txt' )
ORDER BY backup_file.file_name DESC
FOR UPDATE
DO
|
No comments:
Post a Comment