none
How to check TSQL's EXECUTE command for error

    Question

  • Hello,

    I am TOTALLY new with TSQL/SQL. consider the code below:

    EXECUTE sp_executesql @sql, N'@xmldataOUT xml OUTPUT', @xmldataOUT=@instance OUTPUT;

    CREATE XML SCHEMA COLLECTION FASSchema AS @instance

    How can:
    1. Add a check to the EXECUTE command to see if it fails
    2. what is a suitable way to display an error message in case the EXECUTE command fails
    3. Add a check to the CREATE command to see if it fails

    Your help will be highly appreciated. I need people to jumpstart me
    • Moved by Kalman Toth Saturday, July 20, 2013 4:20 PM better error control
    Friday, July 12, 2013 12:25 AM

Answers

  • 1. Add a check to the EXECUTE command to see if it fails
    2. what is a suitable way to display an error message in case the EXECUTE command fails
    3. Add a check to the CREATE command to see if it fails

    If your objective is to simply make sure the script does not continue after an error, you can use TRY/CATCH as Hua Min suggested:

    BEGIN TRY
        EXECUTE sp_executesql @sql, N'@xmldataOUT xml OUTPUT', @xmldataOUT=@instance OUTPUT;
    	CREATE XML SCHEMA COLLECTION FASSchema AS @instance;
    END TRY
    BEGIN CATCH
    	DECLARE
    		@ErrorNumber int
    		,@ErrorMessage nvarchar(2048)
    		,@ErrorSeverity int
    		,@ErrorState int
    		,@ErrorLine int;
    
    	SELECT
    		@ErrorNumber =ERROR_NUMBER()
    		,@ErrorMessage =ERROR_MESSAGE()
    		,@ErrorSeverity = ERROR_SEVERITY()
    		,@ErrorState =ERROR_STATE()
    		,@ErrorLine =ERROR_LINE();
    
    	RAISERROR('Error %d caught at line %d: %s'
    		,@ErrorSeverity
    		,@ErrorState
    		,@ErrorNumber
    		,@ErrorLine
    		,@ErrorMessage);
    END CATCH;

    In SQL 2012 and later, you can simply use THROW in the CATCH block to simplify code:

    BEGIN TRY
        EXECUTE sp_executesql @sql, N'@xmldataOUT xml OUTPUT', @xmldataOUT=@instance OUTPUT;
    	CREATE XML SCHEMA COLLECTION FASSchema AS @instance;
    END TRY
    BEGIN CATCH
    	THROW;
    END CATCH;

    Alternatively, you can turn on XACT_ABORT so the batch stops after an error is encountered:

    SET XACT_ABORT ON;
    EXECUTE sp_executesql @sql, N'@xmldataOUT xml OUTPUT', @xmldataOUT=@instance OUTPUT;
    CREATE XML SCHEMA COLLECTION FASSchema AS @instance;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Friday, July 12, 2013 2:07 AM spelling
    • Marked as answer by slim1831 Monday, July 29, 2013 8:31 PM
    Friday, July 12, 2013 2:06 AM
  • Thanks Chen and Dan... your suggestions are great. however, where in the code will it be most appropriate to send my own ' customized ' error message and how best can I do that?

    You can specify your own custom error message in the RAISERROR or THOW statement of the CATCH block as desired.  But you should ask yourself why you need to do that since that might obfuscate useful information about the underlying cause of the error.

    If the purpose is to provide a user-friendly error message for unexpected errors, it may be better to do that in the application code.  The code can log the actual T-SQL error details and any other contextual information useful for troubleshooting and then display a message to the user that something went wrong.  For example, if the xml is passed from a client app describes a product type which is transformed into an xml schema, you could display a message like 'unable to create product definition' if the operation fails and record the T-SQL error message and xml parameter value to the event log. 

    CATCH can be used to handle certain error conditions where corrective action can be taken automatically (such as deadlock retries).  But, IMHO, the main benefit of T-SQL error handling is to simply stop subsequent code from executing after an error and cleaning up open transactions.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by slim1831 Monday, July 29, 2013 8:31 PM
    Friday, July 12, 2013 12:30 PM
  • First, you should not use 'sp_' as a prefix for user stored procedures.  This is reserved for system procs.

    I see that this proc must be invoked by a sysadmin since it is executing sp_configure.  I assume this proc will be run in SSMS by an application DBA that is a sysadmin role member. 

    Is it possible for multiple users execute proc at same time?  That will be problematic if the same target table is used.

    1.  should procedure warn if table already present before deleting BlankData?

    If there is some other process that must occur against the imported data before it is overwritten, I think this should be an error condition.  The table should be dropped after the other process is done to ensure data are not lost.

    But of the onus is on the invoking user to make sure the previous data is no longer needed, perhaps an informational message is more appropriate.

     

    2. HOW DO I :

    --      -.  add checks for the following conditions, with suitable messages

    --          -.  failed "exec xp_cmdshell @cmd" command

    catch and throw in outer TRY/CATCH

    --          -.  @path's referencing a directory that's devoid of .xml files

    catch and throw in outer TRY/CATCH

    --          -.  failed attempts to read .xml files

    catch, report error and continue in inner TRY/CATCH

    --          -.  failed "select name from #filenames where name like '%.xml'" command

    catch and throw in outer TRY/CATCH

    --          -.  failed "exec (@sql)" command

    catch and throw in outer TRY/CATCH

    3.    -.  should option be added for writing messages to a log?

    If you need historical reporting, yes.

    4. HOW TO:

        collapse all sp_Load<documentXX>toDB procedures to a single,

    --          parameterized procedure

    So you have multiple stored procedures like this?  Do they differ other than the target table name?

    5.HOW DO I SPECIFY A SECOND PARAMAMETER TO
    --          -.  specifies qualifier (e.g., 201308) for table from which to load documents
    --          -.  defaults to value given by a new "current epoch" function
    --          concern here is supporting multi-schema operation by allowing for extraction of different schema versions' of
    --          blank documents from different tables in a set of related tables:  

    --          e.g.., BlankData_201308, BlankData_201309...

    I assume this is related to #4 and the import processes are identical other than the source path and target table.  I'm not sure I understand the multi-schema requirement.  
     

    Below is an example that builds the table name dynamically and includes the error handling described above (assuming SQL Server 2012).

    CREATE PROCEDURE [dbo].[usp_LoadBlankDataToDB] 
        @path varchar(256)
    	,@table_qualifier nvarchar(100) = NULL
    AS
    
    SET NOCOUNT ON;
    
    DECLARE 
    	@file nvarchar(256)
        ,@fileCount int
    	,@message varchar(2047)
    	,@cmd varchar(8000)
    	,@target_table_name nvarchar(392);
    
    BEGIN TRY
    
    	IF IS_SRVROLEMEMBER('sysadmin') = 0
    	BEGIN
    		RAISERROR('You must be a sysadmin role member to execute this stored procedure', 16, 0);
    	END;
    
    	-- To allow advanced options to be changed.
    	EXEC sp_configure 'show advanced options', 1;
    	-- To update the currently configured value for advanced options.
    	RECONFIGURE;
    
    	-- To enable the feature.
    	EXEC sp_configure 'xp_cmdshell', 1;
    	-- To update the currently configured value for this feature.
    	RECONFIGURE;
    
    	IF @table_qualifier IS NULL
    	BEGIN
    		SET @target_table_name = N'dbo.' + QUOTENAME(N'BlankTable_' + dbo.YourEpochFunction());
    	END
    	ELSE
    	BEGIN
    		SET @target_table_name = N'dbo.' + QUOTENAME(N'BlankTable_' + @table_qualifier);
    	END;
    
    	IF OBJECT_ID(@target_table_name, 'U') IS NOT NULL
    	BEGIN
    		PRINT 'exist';
    		EXEC (N'DROP TABLE ' + @target_table_name);
    	END
    
    	EXEC(N'CREATE TABLE ' + @target_table_name + N'(
    		id int identity(1,1)
    		,fileName varchar(256)
    		,blankFile xml
    		);');
    
    	SET @cmd = 'dir /b ' + '"' + @path + '"'
    	CREATE TABLE #filenames(name varchar(256));
    
    	INSERT INTO #filenames 
    		EXEC xp_cmdshell @cmd;
    
        DECLARE fileNameCursor CURSOR STATIC FOR
    		SELECT name 
    		FROM #filenames
    		WHERE name LIKE '%.xml';
    
        OPEN fileNameCursor;
    
        FETCH NEXT FROM fileNameCursor INTO @file;
    
    	IF @@FETCH_STATUS = -1
    	BEGIN
    		SET @message = 'path %s is devoid of xml files';
    		RAISERROR(@message, 16, 1, @path);
    		RETURN -1;
    	END
    
        WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE
    			@sql nvarchar(MAX);
    
            SET @sql = 
    			N'INSERT INTO ' + @target_table_name + '(fileName, blankFile)
    			SELECT ' + QUOTENAME(@file, '''') + ', * FROM OPENROWSET(BULK N' + QUOTENAME(@path + '\' + @file, '''') + ', SINGLE_BLOB) AS xmlfile';
    
    		BEGIN TRY
    			EXEC (@sql);
    		END TRY
    		BEGIN CATCH
    			SET @message = 'Warning: Import of file ' + @path + '\' + @file + ' into table ' + @target_table_name + ' failed';
    			RAISERROR(@message, 10, 0);
    			--continue after single file import error
    		END CATCH;
    
            FETCH NEXT FROM fileNameCursor INTO @file;
    
        END;
    
        CLOSE fileNameCursor;
        DEALLOCATE fileNameCursor;
    
        SELECT @fileCount = COUNT(*) FROM #filenames;
    
    	SET @fileCount = @fileCount - 1; 
    	RAISERROR('There are %d files under the directory', 0, 0, @fileCount);
    
        SET @sql = N'SELECT @fileCount = COUNT(*) FROM ' + @target_table_name + N';';
    	EXEC sp_executesql	
    		@sql
    		,N'@fileCount int OUTPUT'
    		,@fileCount = @fileCount OUTPUT;
    
    	RAISERROR('%d xml files are imported', 0, 0, @fileCount);
    
        SET @sql = N'SELECT name AS [File Not Imported]
        FROM #filenames
        WHERE
    		name IS NOT NULL
    		AND name NOT IN 
            (
    		SELECT fileName
    		FROM ' + @target_table_name + 
    		');';
    	EXEC (@sql);
    
        SET @sql = N'SELECT fileName AS [File Imported] FROM ' + @target_table_name + ';';
    	EXEC (@sql);
    
    END TRY
    BEGIN CATCH
    	THROW;
    END CATCH;
    
    RETURN 0;
    
    GO


     


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by slim1831 Monday, July 29, 2013 8:32 PM
    Saturday, July 13, 2013 5:25 PM
  • Thnaks Dan. The explanation makes a lot of sense. I still have similar issues concerning EXEC () that I will be posting shortly.

    Appreciatively,

    • Marked as answer by slim1831 Monday, July 29, 2013 8:31 PM
    Friday, July 12, 2013 4:53 PM

All replies

  • Hello,

    I am TOTALLY new with TSQL/SQL. consider the code below:

    EXECUTE sp_executesql @sql, N'@xmldataOUT xml OUTPUT', @xmldataOUT=@instance OUTPUT;

    CREATE XML SCHEMA COLLECTION FASSchema AS @instance

    How can:
    1. Add a check to the EXECUTE command to see if it fails
    2. what is a suitable way to display an error message in case the EXECUTE command fails
    3. Add a check to the CREATE command to see if it fails

    Your help will be highly appreciated. I need people to jumpstart me
    Friday, July 12, 2013 12:28 AM
  • You can use try catch block. So that error will be caught.
    Friday, July 12, 2013 1:29 AM
  • Hi,

    How about you directly run "CREATE XML SCHEMA COLLECTION" instead? Read:

    http://msdn.microsoft.com/en-us/library/ms176009.aspx


    Many Thanks & Best Regards, Hua Min

    Friday, July 12, 2013 1:49 AM
  • 1. Add a check to the EXECUTE command to see if it fails
    2. what is a suitable way to display an error message in case the EXECUTE command fails
    3. Add a check to the CREATE command to see if it fails

    If your objective is to simply make sure the script does not continue after an error, you can use TRY/CATCH as Hua Min suggested:

    BEGIN TRY
        EXECUTE sp_executesql @sql, N'@xmldataOUT xml OUTPUT', @xmldataOUT=@instance OUTPUT;
    	CREATE XML SCHEMA COLLECTION FASSchema AS @instance;
    END TRY
    BEGIN CATCH
    	DECLARE
    		@ErrorNumber int
    		,@ErrorMessage nvarchar(2048)
    		,@ErrorSeverity int
    		,@ErrorState int
    		,@ErrorLine int;
    
    	SELECT
    		@ErrorNumber =ERROR_NUMBER()
    		,@ErrorMessage =ERROR_MESSAGE()
    		,@ErrorSeverity = ERROR_SEVERITY()
    		,@ErrorState =ERROR_STATE()
    		,@ErrorLine =ERROR_LINE();
    
    	RAISERROR('Error %d caught at line %d: %s'
    		,@ErrorSeverity
    		,@ErrorState
    		,@ErrorNumber
    		,@ErrorLine
    		,@ErrorMessage);
    END CATCH;

    In SQL 2012 and later, you can simply use THROW in the CATCH block to simplify code:

    BEGIN TRY
        EXECUTE sp_executesql @sql, N'@xmldataOUT xml OUTPUT', @xmldataOUT=@instance OUTPUT;
    	CREATE XML SCHEMA COLLECTION FASSchema AS @instance;
    END TRY
    BEGIN CATCH
    	THROW;
    END CATCH;

    Alternatively, you can turn on XACT_ABORT so the batch stops after an error is encountered:

    SET XACT_ABORT ON;
    EXECUTE sp_executesql @sql, N'@xmldataOUT xml OUTPUT', @xmldataOUT=@instance OUTPUT;
    CREATE XML SCHEMA COLLECTION FASSchema AS @instance;


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Friday, July 12, 2013 2:07 AM spelling
    • Marked as answer by slim1831 Monday, July 29, 2013 8:31 PM
    Friday, July 12, 2013 2:06 AM
  • Thanks Chen and Dan... your suggestions are great. however, where in the code will it be most appropriate to send my own ' customized ' error message and how best can I do that?

    Thanks

    Friday, July 12, 2013 11:07 AM
  • Thanks Chen and Dan... your suggestions are great. however, where in the code will it be most appropriate to send my own ' customized ' error message and how best can I do that?

    You can specify your own custom error message in the RAISERROR or THOW statement of the CATCH block as desired.  But you should ask yourself why you need to do that since that might obfuscate useful information about the underlying cause of the error.

    If the purpose is to provide a user-friendly error message for unexpected errors, it may be better to do that in the application code.  The code can log the actual T-SQL error details and any other contextual information useful for troubleshooting and then display a message to the user that something went wrong.  For example, if the xml is passed from a client app describes a product type which is transformed into an xml schema, you could display a message like 'unable to create product definition' if the operation fails and record the T-SQL error message and xml parameter value to the event log. 

    CATCH can be used to handle certain error conditions where corrective action can be taken automatically (such as deadlock retries).  But, IMHO, the main benefit of T-SQL error handling is to simply stop subsequent code from executing after an error and cleaning up open transactions.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by slim1831 Monday, July 29, 2013 8:31 PM
    Friday, July 12, 2013 12:30 PM
  • Thnaks Dan. The explanation makes a lot of sense. I still have similar issues concerning EXEC () that I will be posting shortly.

    Appreciatively,

    • Marked as answer by slim1831 Monday, July 29, 2013 8:31 PM
    Friday, July 12, 2013 4:53 PM
  • Hi all,

    while trying to hone my skils on T-SQL, i came across a procedure that

    Load blank per-activity data files for multi-instance activities to a DB's BlankData table, then it should be 

    --     creating this table if BlankData isn't in the  DB and recreating it otherwise.  

    THE PROCEDURE IS AS BELOW)

    --     Directory containing files to load specified as a @path argument to this procedure.

    --     Directory containing files to load specified as a @path argument to this procedure.

    MY QUESTIONS:

    1.  should procedure warn if table already present before deleting BlankData?

    2. HOW DO I :

    --      -.  add checks for the following conditions, with suitable messages
    --          -.  failed "exec xp_cmdshell @cmd" command
    --          -.  @path's referencing a directory that's devoid of .xml files
    --          -.  failed attempts to read .xml files
    --          -.  failed "select name from #filenames where name like '%.xml'" command

    --          -.  failed "exec (@sql)" command

    3.    -.  should option be added for writing messages to a log?

    4. HOW TO:

        collapse all sp_Load<documentXX>toDB procedures to a single,

    --          parameterized procedure

    5.HOW DO I SPECIFY A SECOND PARAMAMETER TO
    --          -.  specifies qualifier (e.g., 201308) for table from which to load documents
    --          -.  defaults to value given by a new "current epoch" function
    --          concern here is supporting multi-schema operation by allowing for extraction of different schema versions' of
    --          blank documents from different tables in a set of related tables:  

    --          e.g.., BlankData_201308, BlankData_201309...

    THE PROCEDURE

    CREATE PROCEDURE [dbo].[sp_LoadBlankDataToDB] 
        @path varchar(256)
    AS
    BEGIN
        -- To allow advanced options to be changed.
        EXEC sp_configure 'show advanced options', 1
        
        -- To update the currently configured value for advanced options.
        RECONFIGURE
        
        -- To enable the feature.
        EXEC sp_configure 'xp_cmdshell', 1
        
        -- To update the currently configured value for this feature.
        RECONFIGURE
        
        SET NOCOUNT ON;
        IF (EXISTS (SELECT * 
                     FROM INFORMATION_SCHEMA.TABLES 
                     WHERE TABLE_SCHEMA = 'dbo' 
                     AND  TABLE_NAME = 'BlankData'))
        BEGIN
            print('exist')
             drop table fas.dbo.[BlankData]
        END
        
        create table BlankData(id int identity(1,1),fileName varchar(256),blankFile xml)
        
        declare @cmd varchar(256)
         set @cmd = 'dir /b ' +'"'+ @path+'"'
        create table #filenames(name varchar(256))
        
        insert into #filenames 
        exec xp_cmdshell @cmd
        declare @file nvarchar(256)
        
        declare fileNameCursor CURSOR SCROLL FOR
        select name from #filenames where name like '%.xml'
        
        open fileNameCursor
        fetch next from fileNameCursor
        into @file
        
        WHILE @@FETCH_STATUS = 0
        
        begin
            declare @sql varchar(max)
            
            --insert into fas.dbo.SampleData(fileName) values (@file)
            
            set @sql = 
            'insert into [fas].[dbo].[BlankData]
            select '''+@file+''', * from openrowset (BULK N'''+@path+'\'+@file+''', SINGLE_BLOB) as xmlfile'
            
            exec (@sql)
            FETCH NEXT FROM fileNameCursor
            INTO @file
            
        end
        
        CLOSE fileNameCursor
        DEALLOCATE fileNameCursor
        
        DECLARE @fileCount int
        select @fileCount = COUNT(*) from #filenames
        print ('There are '+ convert(varchar(max),(@fileCount-1)) + ' files under the directory')
        select @fileCount = COUNT(*) from BlankData
        print (convert(varchar(max),@fileCount) +' xml files are imported')
        
        select name as 'File Not Imported'
        from #filenames
        where name not in 
            (select fileName from fas.dbo.BlankData)
            
        select fileName as 'File Imported'
        from BlankData
        
    END

    GO

    Saturday, July 13, 2013 3:44 PM
  • Hi all,

    while trying to hone my skils on T-SQL, i came across a procedure that

    Load blank per-activity data files for multi-instance activities to a DB's BlankData table, then it should be 

    --     creating this table if BlankData isn't in the  DB and recreating it otherwise.  

    THE PROCEDURE IS AS BELOW)

    --     Directory containing files to load specified as a @path argument to this procedure.

    --     Directory containing files to load specified as a @path argument to this procedure.

    MY QUESTIONS:

    1.  should procedure warn if table already present before deleting BlankData?

    2. HOW DO I :

    --      -.  add checks for the following conditions, with suitable messages
    --          -.  failed "exec xp_cmdshell @cmd" command
    --          -.  @path's referencing a directory that's devoid of .xml files
    --          -.  failed attempts to read .xml files
    --          -.  failed "select name from #filenames where name like '%.xml'" command

    --          -.  failed "exec (@sql)" command

    3.    -.  should option be added for writing messages to a log?

    4. HOW TO:

        collapse all sp_Load<documentXX>toDB procedures to a single,

    --          parameterized procedure

    5.HOW DO I SPECIFY A SECOND PARAMAMETER TO
    --          -.  specifies qualifier (e.g., 201308) for table from which to load documents
    --          -.  defaults to value given by a new "current epoch" function
    --          concern here is supporting multi-schema operation by allowing for extraction of different schema versions' of
    --          blank documents from different tables in a set of related tables:  

    --          e.g.., BlankData_201308, BlankData_201309...

    THE PROCEDURE

    CREATE PROCEDURE [dbo].[sp_LoadBlankDataToDB] 
        @path varchar(256)
    AS
    BEGIN
        -- To allow advanced options to be changed.
        EXEC sp_configure 'show advanced options', 1
        
        -- To update the currently configured value for advanced options.
        RECONFIGURE
        
        -- To enable the feature.
        EXEC sp_configure 'xp_cmdshell', 1
        
        -- To update the currently configured value for this feature.
        RECONFIGURE
        
        SET NOCOUNT ON;
        IF (EXISTS (SELECT * 
                     FROM INFORMATION_SCHEMA.TABLES 
                     WHERE TABLE_SCHEMA = 'dbo' 
                     AND  TABLE_NAME = 'BlankData'))
        BEGIN
            print('exist')
             drop table fas.dbo.[BlankData]
        END
        
        create table BlankData(id int identity(1,1),fileName varchar(256),blankFile xml)
        
        declare @cmd varchar(256)
         set @cmd = 'dir /b ' +'"'+ @path+'"'
        create table #filenames(name varchar(256))
        
        insert into #filenames 
        exec xp_cmdshell @cmd
        declare @file nvarchar(256)
        
        declare fileNameCursor CURSOR SCROLL FOR
        select name from #filenames where name like '%.xml'
        
        open fileNameCursor
        fetch next from fileNameCursor
        into @file
        
        WHILE @@FETCH_STATUS = 0
        
        begin
            declare @sql varchar(max)
            
            --insert into fas.dbo.SampleData(fileName) values (@file)
            
            set @sql = 
            'insert into [fas].[dbo].[BlankData]
            select '''+@file+''', * from openrowset (BULK N'''+@path+'\'+@file+''', SINGLE_BLOB) as xmlfile'
            
            exec (@sql)
            FETCH NEXT FROM fileNameCursor
            INTO @file
            
        end
        
        CLOSE fileNameCursor
        DEALLOCATE fileNameCursor
        
        DECLARE @fileCount int
        select @fileCount = COUNT(*) from #filenames
        print ('There are '+ convert(varchar(max),(@fileCount-1)) + ' files under the directory')
        select @fileCount = COUNT(*) from BlankData
        print (convert(varchar(max),@fileCount) +' xml files are imported')
        
        select name as 'File Not Imported'
        from #filenames
        where name not in 
            (select fileName from fas.dbo.BlankData)
            
        select fileName as 'File Imported'
        from BlankData
        
    END

    GO

    Saturday, July 13, 2013 3:44 PM
  • First, you should not use 'sp_' as a prefix for user stored procedures.  This is reserved for system procs.

    I see that this proc must be invoked by a sysadmin since it is executing sp_configure.  I assume this proc will be run in SSMS by an application DBA that is a sysadmin role member. 

    Is it possible for multiple users execute proc at same time?  That will be problematic if the same target table is used.

    1.  should procedure warn if table already present before deleting BlankData?

    If there is some other process that must occur against the imported data before it is overwritten, I think this should be an error condition.  The table should be dropped after the other process is done to ensure data are not lost.

    But of the onus is on the invoking user to make sure the previous data is no longer needed, perhaps an informational message is more appropriate.

     

    2. HOW DO I :

    --      -.  add checks for the following conditions, with suitable messages

    --          -.  failed "exec xp_cmdshell @cmd" command

    catch and throw in outer TRY/CATCH

    --          -.  @path's referencing a directory that's devoid of .xml files

    catch and throw in outer TRY/CATCH

    --          -.  failed attempts to read .xml files

    catch, report error and continue in inner TRY/CATCH

    --          -.  failed "select name from #filenames where name like '%.xml'" command

    catch and throw in outer TRY/CATCH

    --          -.  failed "exec (@sql)" command

    catch and throw in outer TRY/CATCH

    3.    -.  should option be added for writing messages to a log?

    If you need historical reporting, yes.

    4. HOW TO:

        collapse all sp_Load<documentXX>toDB procedures to a single,

    --          parameterized procedure

    So you have multiple stored procedures like this?  Do they differ other than the target table name?

    5.HOW DO I SPECIFY A SECOND PARAMAMETER TO
    --          -.  specifies qualifier (e.g., 201308) for table from which to load documents
    --          -.  defaults to value given by a new "current epoch" function
    --          concern here is supporting multi-schema operation by allowing for extraction of different schema versions' of
    --          blank documents from different tables in a set of related tables:  

    --          e.g.., BlankData_201308, BlankData_201309...

    I assume this is related to #4 and the import processes are identical other than the source path and target table.  I'm not sure I understand the multi-schema requirement.  
     

    Below is an example that builds the table name dynamically and includes the error handling described above (assuming SQL Server 2012).

    CREATE PROCEDURE [dbo].[usp_LoadBlankDataToDB] 
        @path varchar(256)
    	,@table_qualifier nvarchar(100) = NULL
    AS
    
    SET NOCOUNT ON;
    
    DECLARE 
    	@file nvarchar(256)
        ,@fileCount int
    	,@message varchar(2047)
    	,@cmd varchar(8000)
    	,@target_table_name nvarchar(392);
    
    BEGIN TRY
    
    	IF IS_SRVROLEMEMBER('sysadmin') = 0
    	BEGIN
    		RAISERROR('You must be a sysadmin role member to execute this stored procedure', 16, 0);
    	END;
    
    	-- To allow advanced options to be changed.
    	EXEC sp_configure 'show advanced options', 1;
    	-- To update the currently configured value for advanced options.
    	RECONFIGURE;
    
    	-- To enable the feature.
    	EXEC sp_configure 'xp_cmdshell', 1;
    	-- To update the currently configured value for this feature.
    	RECONFIGURE;
    
    	IF @table_qualifier IS NULL
    	BEGIN
    		SET @target_table_name = N'dbo.' + QUOTENAME(N'BlankTable_' + dbo.YourEpochFunction());
    	END
    	ELSE
    	BEGIN
    		SET @target_table_name = N'dbo.' + QUOTENAME(N'BlankTable_' + @table_qualifier);
    	END;
    
    	IF OBJECT_ID(@target_table_name, 'U') IS NOT NULL
    	BEGIN
    		PRINT 'exist';
    		EXEC (N'DROP TABLE ' + @target_table_name);
    	END
    
    	EXEC(N'CREATE TABLE ' + @target_table_name + N'(
    		id int identity(1,1)
    		,fileName varchar(256)
    		,blankFile xml
    		);');
    
    	SET @cmd = 'dir /b ' + '"' + @path + '"'
    	CREATE TABLE #filenames(name varchar(256));
    
    	INSERT INTO #filenames 
    		EXEC xp_cmdshell @cmd;
    
        DECLARE fileNameCursor CURSOR STATIC FOR
    		SELECT name 
    		FROM #filenames
    		WHERE name LIKE '%.xml';
    
        OPEN fileNameCursor;
    
        FETCH NEXT FROM fileNameCursor INTO @file;
    
    	IF @@FETCH_STATUS = -1
    	BEGIN
    		SET @message = 'path %s is devoid of xml files';
    		RAISERROR(@message, 16, 1, @path);
    		RETURN -1;
    	END
    
        WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE
    			@sql nvarchar(MAX);
    
            SET @sql = 
    			N'INSERT INTO ' + @target_table_name + '(fileName, blankFile)
    			SELECT ' + QUOTENAME(@file, '''') + ', * FROM OPENROWSET(BULK N' + QUOTENAME(@path + '\' + @file, '''') + ', SINGLE_BLOB) AS xmlfile';
    
    		BEGIN TRY
    			EXEC (@sql);
    		END TRY
    		BEGIN CATCH
    			SET @message = 'Warning: Import of file ' + @path + '\' + @file + ' into table ' + @target_table_name + ' failed';
    			RAISERROR(@message, 10, 0);
    			--continue after single file import error
    		END CATCH;
    
            FETCH NEXT FROM fileNameCursor INTO @file;
    
        END;
    
        CLOSE fileNameCursor;
        DEALLOCATE fileNameCursor;
    
        SELECT @fileCount = COUNT(*) FROM #filenames;
    
    	SET @fileCount = @fileCount - 1; 
    	RAISERROR('There are %d files under the directory', 0, 0, @fileCount);
    
        SET @sql = N'SELECT @fileCount = COUNT(*) FROM ' + @target_table_name + N';';
    	EXEC sp_executesql	
    		@sql
    		,N'@fileCount int OUTPUT'
    		,@fileCount = @fileCount OUTPUT;
    
    	RAISERROR('%d xml files are imported', 0, 0, @fileCount);
    
        SET @sql = N'SELECT name AS [File Not Imported]
        FROM #filenames
        WHERE
    		name IS NOT NULL
    		AND name NOT IN 
            (
    		SELECT fileName
    		FROM ' + @target_table_name + 
    		');';
    	EXEC (@sql);
    
        SET @sql = N'SELECT fileName AS [File Imported] FROM ' + @target_table_name + ';';
    	EXEC (@sql);
    
    END TRY
    BEGIN CATCH
    	THROW;
    END CATCH;
    
    RETURN 0;
    
    GO


     


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by slim1831 Monday, July 29, 2013 8:32 PM
    Saturday, July 13, 2013 5:25 PM
  • Thanks Dan, you help is higly appreciated. Keep up the god work. To answer your question, " can multiple users execute proc at same time?" The answer is YES. I will work on your suggestions and give you a feed back.

    Thankfully

    Saturday, July 13, 2013 6:03 PM
  • Good morning Dan. Thanks for being helpful. i have a similar procedure to the one i dealt with, however, this is rather complex ( moreso, for a newbie like me)

    This procudure:

     drops and recreates table BlankData if table exists; creates it anew otherwise.
    --.                 -.  loads all xml documents in directory specified by @path into BlankData.

    Now I want the proc to:

    -.  add checks for the following conditions, with suitable messages
    1         -.  failed "exec xp_cmdshell @cmd" command
    2      -.  @path's referencing a directory that's devoid of .xml files
    3       -.  failed attempts to read .xml files
    4       -.  failed "select name from #filenames where name like '%.xml'" command
    5     -.  failed "exec (@sql)" command6           - collapse all sp_Load<documentXX>toDB procedures to a single   parameterized procedure

     7             - add a second parameter that:
                   specifies qualifying name of table from which to load documents
                  defaults to value given by a new "current epoch" function


    The  concern here is supporting multi-schema operation by allowing for extraction of different versions of control    supporting choice documents (for different schema versions) from different tables in a set of related tables:  The Proc

    CREATE PROCEDURE [dbo].[LoadComplexChoiceValuesToDB] 
        @path varchar(256)
    AS
    BEGIN

        -- To allow advanced options to be changed.
        EXEC sp_configure 'show advanced options', 1
        
        -- To update the currently configured value for advanced options.
        RECONFIGURE
        
        -- To enable the feature.
        EXEC sp_configure 'xp_cmdshell', 1
        
        -- To update the currently configured value for this feature.
        RECONFIGURE
        

        SET NOCOUNT ON;
        IF (EXISTS (SELECT * 
                     FROM INFORMATION_SCHEMA.TABLES 
                     WHERE TABLE_SCHEMA = 'dbo' 
                     AND  TABLE_NAME = 'ComplexChoiceValues'))
        BEGIN
            print('exist')
             drop table fas.dbo.[ComplexChoiceValues]
        END
        
        
        create table ComplexChoiceValues(id int identity(1,1),fileName varchar(256),complexChoiceFile xml)
        
        declare @cmd varchar(256)
         set @cmd = 'dir /b ' +'"'+ @path+'"'
        create table #filenames(name varchar(256))
        
        insert into #filenames 
        exec xp_cmdshell @cmd

        declare @file nvarchar(256)
        
        declare fileNameCursor CURSOR SCROLL FOR
        select name from #filenames where name like '%.xml'
        
        open fileNameCursor
        fetch next from fileNameCursor
        into @file
        
        WHILE @@FETCH_STATUS = 0
        
        begin
            declare @sql varchar(max)
            
            --insert into fas.dbo.SampleData(fileName) values (@file)
            
            set @sql = 
            'insert into [fas].[dbo].[ComplexChoiceValues]
            select '''+@file+''', * from openrowset (BULK N'''+@path+'\'+@file+''', SINGLE_BLOB) as xmlfile'
            
            
            exec (@sql)
            

            FETCH NEXT FROM fileNameCursor
            INTO @file
            
        end
        
        CLOSE fileNameCursor
        DEALLOCATE fileNameCursor
        
        DECLARE @fileCount int
        select @fileCount = COUNT(*) from #filenames
        print ('There are '+ convert(varchar(max),(@fileCount-1)) + ' files under the directory')
        select @fileCount = COUNT(*) from BlankData
        print (convert(varchar(max),@fileCount) +' xml files are imported')
        
        select name as 'File Not Imported'
        from #filenames
        where name not in 
            (select fileName from fas.dbo.BlankData)
            
        select fileName as 'File Imported'
        from BlankData
        
    END
    GO

    Monday, July 15, 2013 4:40 PM
  • I think you can use the same pattern here as in the previous example I provided. Use an outer TRY/CATCH to prevent the code from continuing after a fatal error and an inner TRY/CATCH to continue if a single xml file import fails.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, July 20, 2013 1:20 PM
  • You should build an SSIS package. Your code is pushing the feeble error control limits of T-SQL.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: SQL Server 2012 Administration


    Saturday, July 20, 2013 4:19 PM
  • Thanks for the contribution. I did a little research on SSIS and the "xp_cmdshell" S.Proc. I am still confused about how the SSIS will work? Will it only work for the xp_cmdshell S.Proc? or will it work for all the commands I am checking thier failed conditions?

    How can I implement/integrate SSIS to my code above?

    Thanks


    slim

    Saturday, July 20, 2013 4:34 PM
  • Hi all,

    Thaks for the contribution

    Given the SP below, 

    guid -  it is a string that identifies an XML document to be extracted from DB Schema

    create PROCEDURE [dbo].[sp_ExtractBlankData] 
    @guid        VARCHAR(MAX)
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @xpath VARCHAR(MAX)
        DECLARE @xml   XML

    -- check for null guid  IF (@guid IS NULL)
        BEGIN
            RAISERROR('Invalid parameter: @guid cannot be NULL.', 18, 0) 
            RETURN
        END
        
        SELECT  @xml  = xpaths.query('declare default element namespace "http://www"; //GUID[. eq sql:variable("@guid")]/..//path/text()')
        FROM    fas.dbo.[Directory]
        SET     @xpath = CONVERT(VARCHAR(MAX), @xml)
            
        SET @xpath = REPLACE(@xpath,'/faculty-activities-taxonomy/','')
        SET @xpath = @xpath+'.xml'
        SET @xpath = REPLACE(@xpath,'/','.')
        
        select blankFile from fas.dbo.BlankData where fileName = @xpath
    END
    GO

    My Questions

    1. Should attempts to use GUIDs that correspond to no known database document be logged?

    3. How do I add Add  a second parameter  that specifies qualifier  for table from which to load documents and
    --       defaults to value given by a new "current epoch" function

    Note

    The  issue here is supporting multi-schema operation by allowing for extraction of different schema versions' of
          blank documents from different tables in a set of related tables


    slim


    • Edited by slim1831 Monday, July 22, 2013 11:53 AM imporved clarity
    Saturday, July 20, 2013 4:57 PM