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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Issues in checking errors in TSQL

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)
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
Question
You cannot vote on your own post
0
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...

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 client
SET NOCOUNT ON;

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

-- Empty the staging table
TRUNCATE 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;

DELETE
FROM #FileNames
WHERE Name = ''
OR Name IS NULL;

DECLARE curFileName CURSOR FOR SELECT Name
FROM #FileNames
WHERE Name LIKE '%.xml';

OPEN curFileName;

FETCH NEXT
FROM curFileName
INTO @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;
END

CLOSE curfileName
DEALLOCATE curFileName

SELECT @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 #Filenames

EXCEPT

SELECT [FileName]
FROM dbo.BlankData;

SELECT [FileName] AS [File Imported]
FROM dbo.BlankData;
GO



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

yommy1831
Starting Member

10 Posts

Posted - 2013-07-14 : 10:47:28
Thanks for the help. It makes a lot of sense now.
Go to Top of Page
   

- Advertisement -