SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Issues in checking errors in TSQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yommy1831
Starting Member

USA
10 Posts

Posted - 07/13/2013 :  11:23:24  Show Profile  Reply with Quote
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

Sweden
30241 Posts

Posted - 07/14/2013 :  02:02:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
10 Posts

Posted - 07/14/2013 :  10:47:28  Show Profile  Reply with Quote
Thanks for the help. It makes a lot of sense now.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000