By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,474 Members | 1,273 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,474 IT Pros & Developers. It's quick & easy.

Error handling in some of TSQL commands. An example is how to handle errors in the EX

P: 2
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

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE [dbo].[sp_LoadBlankDataToDB] 
  2.     @path varchar(256)
  3. AS
  4. BEGIN
  5.     -- To allow advanced options to be changed.
  6.     EXEC sp_configure 'show advanced options', 1
  7.  
  8.     -- To update the currently configured value for advanced options.
  9.     RECONFIGURE
  10.  
  11.     -- To enable the feature.
  12.     EXEC sp_configure 'xp_cmdshell', 1
  13.  
  14.     -- To update the currently configured value for this feature.
  15.     RECONFIGURE
  16.  
  17.     SET NOCOUNT ON;
  18.     IF (EXISTS (SELECT * 
  19.                  FROM INFORMATION_SCHEMA.TABLES 
  20.                  WHERE TABLE_SCHEMA = 'dbo' 
  21.                  AND  TABLE_NAME = 'BlankData'))
  22.     BEGIN
  23.         print('exist')
  24.          drop table fas.dbo.[BlankData]
  25.     END
  26.  
  27.     create table BlankData(id int identity(1,1),fileName varchar(256),blankFile xml)
  28.  
  29.     declare @cmd varchar(256)
  30.      set @cmd = 'dir /b ' +'"'+ @path+'"'
  31.     create table #filenames(name varchar(256))
  32.  
  33.     insert into #filenames 
  34.     exec xp_cmdshell @cmd
  35.     declare @file nvarchar(256)
  36.  
  37.     declare fileNameCursor CURSOR SCROLL FOR
  38.     select name from #filenames where name like '%.xml'
  39.  
  40.     open fileNameCursor
  41.     fetch next from fileNameCursor
  42.     into @file
  43.  
  44.     WHILE @@FETCH_STATUS = 0
  45.  
  46.     begin
  47.         declare @sql varchar(max)
  48.  
  49.         --insert into fas.dbo.SampleData(fileName) values (@file)
  50.  
  51.         set @sql = 
  52.         'insert into [fas].[dbo].[BlankData]
  53.         select '''+@file+''', * from openrowset (BULK N'''+@path+'\'+@file+''', SINGLE_BLOB) as xmlfile'
  54.  
  55.         exec (@sql)
  56.         FETCH NEXT FROM fileNameCursor
  57.         INTO @file
  58.  
  59.     end
  60.  
  61.     CLOSE fileNameCursor
  62.     DEALLOCATE fileNameCursor
  63.  
  64.     DECLARE @fileCount int
  65.     select @fileCount = COUNT(*) from #filenames
  66.     print ('There are '+ convert(varchar(max),(@fileCount-1)) + ' files under the directory')
  67.     select @fileCount = COUNT(*) from BlankData
  68.     print (convert(varchar(max),@fileCount) +' xml files are imported')
  69.  
  70.     select name as 'File Not Imported'
  71.     from #filenames
  72.     where name not in 
  73.         (select fileName from fas.dbo.BlankData)
  74.  
  75.     select fileName as 'File Imported'
  76.     from BlankData
  77.  
  78. END
  79. GO
Jul 13 '13 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,430
Please use code tags when posting code.

We only allow one question per thread on the forum. So I will answer the first one.

As far as whether or not you should warn the user, that's up to you. It depends on your requirements. For some people, they need to keep the old data, for others, they don't because they're only using it as a staging table or it's just temporary data. Whether or not you want to warn the user depends on which camp you fall into.
Jul 13 '13 #2

P: 2
Thanks for your response.
Jul 14 '13 #3

Post your reply

Sign in to post your reply or Sign up for a free account.