Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
30421 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  
 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.14 seconds. Powered By: Snitz Forums 2000