Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
yommy1831
Starting Member
10 Posts |
Posted - 2013-07-13 : 11:23:24
|
All, I need help getting over error checking in the code below:CREATE PROCEDURE [dbo].[sp_LoadBlankDataToDB] @path varchar(256)ASBEGIN -- 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 ENDGO MY QUESTIONSQuestionYou cannot vote on your own post0Hi all,while trying to hone my skils on T-SQL, i came across a procedure thatLoad 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)" command3. -. should option be added for writing messages to a log?4. HOW TO: collapse all sp_Load<documentXX>toDB procedures to a single,-- parameterized procedure5.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... |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-14 : 02:02:08
|
No, you should not drop and create table. A simple TRUNCATE TABLE will do.CREATE PROCEDURE dbo.usp_LoadBlankDataToDB -- Don't prefix procedures with sp_( @Path NVARCHAR(256))AS-- Prevent unwanted resultsets back to clientSET NOCOUNT ON;-- To allow advanced options to be changedEXEC 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;-- Empty the staging tableTRUNCATE TABLE dbo.BlankData;DECLARE @Cmd NVARCHAR(256), @FileCount INT;SET @Cmd = 'dir /b "' + @Path + '"';CREATE TABLE #FileNames ( Name NVARCHAR(256) );INSERT #FileNames ( Name )EXEC xp_cmdshell @Cmd;DELETEFROM #FileNamesWHERE Name = '' OR Name IS NULL;DECLARE curFileName CURSOR FOR SELECT Name FROM #FileNames WHERE Name LIKE '%.xml';OPEN curFileName;FETCH NEXTFROM curFileNameINTO @Cmd;WHILE @@FETCH_STATUS = 0 BEGIN SET @Cmd = 'INSERT dbo.BlankData ([FileName], BlankFile) SELECT ' + QUOTENAME(@Cmd, '''') + ', Data FROM OPENROWSET(BULK N' + QUOTENAME(@Path + '\' + @Cmd, '''') + ', SINGLE_BLOB) AS f(Data)'; EXEC (@Cmd); FETCH NEXT FROM curFileName INTO @Cmd; ENDCLOSE curfileNameDEALLOCATE curFileNameSELECT @FileCount = COUNT(*)FROM #FileNames;PRINT 'There are ' + CAST(@FileCount AS VARCHAR(12)) + ' files under the directory "' + @Path + '".';SELECT @FileCount = COUNT(*)FROM dbo.BlankData;PRINT CAST(@FileCount AS VARCHAR(12)) + ' xml files were imported.';SELECT Name AS [File Not Imported]FROM #FilenamesEXCEPTSELECT [FileName]FROM dbo.BlankData;SELECT [FileName] AS [File Imported]FROM dbo.BlankData;GO N 56°04'39.26"E 12°55'05.63" |
|
|
yommy1831
Starting Member
10 Posts |
Posted - 2013-07-14 : 10:47:28
|
Thanks for the help. It makes a lot of sense now. |
|
|
|
|
|
|
|