locked
Error Checking in TSQL RRS feed

  • Question

  • User-1922804372 posted

    Hi all, 

    I ran into  several issues when going through this code:

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

    Any ideas will be appreciated

    Saturday, July 13, 2013 11:56 AM

All replies

  • User260886948 posted

    Hi,

    Could please edit your code? It is hard to find the code.

    Best Regards.

    Monday, July 22, 2013 5:00 AM