Trouble with script that dynamically imports flat file. cmdshell
-
I posted this last week. Many people offered assistance; thank you so much. After posting I did fix the initial problem. I posted the new code however answers were still relevant to the problem that was solved. As a result of the thread kind of forking I am reposting with the current code hoping for some help. While I was able to correct the initial problem this one has me completely stumped.
One of the first things I do is read in the directory so I know what files need to be imported. The script generates dynamic sql. I print this to screen and copy / past / execute and I get the desired result. When I run the proc however the same exact code gives an error and inserts the following into the table that is intended to hold a list of files in a folder. (Image attached).
The following is the code for the proc. Can someone please have a look? I have been beating my head against the wall and I know it has to be something simple but I just don't see it.
USE [PointDev]
GO
/****** Object: StoredProcedure [dbo].[ImportFiles] Script Date: 12/13/2017 9:34:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GOALTER PROCEDURE [dbo].[ImportFiles] @FilePath VARCHAR(1000) = 'g:\UnZip\',
@ArchivePath VARCHAR(1000) = 'g:\Imported\',
@FileNameMask VARCHAR(1000) = '*.*',
@MergeProc VARCHAR(128) = 'MergeBCPData'
AS
SET nocount ON-- Consider CALLING this SP from another SP that will pass along the CustomerID. This will allow for a managing
-- SP to determine which clients need to run.
--EXEC sp_configure 'show advanced options', 1
--EXEC sp_configure 'ad hoc distributed queries', 1
--RECONFIGURE
--RECONFIGURE
--GO
--EXEC sp_configure 'xp_cmdshell', 1
--GO
--RECONFIGURE
DECLARE @ImportDate AS DATETIMEDECLARE @CustomerID AS INT = 1
DECLARE @SourceFileDIR AS VARCHAR(4000)
/*
-- Below is for out of band testing. Comment to commit proc and run production
DECLARE @FilePath varchar(1000) = 'g:\UnZip\'
,@ArchivePath varchar(1000) = 'g:\UnZip\Imported\'
,@FileNameMask varchar(1000) = '*.*'
,@MergeProc varchar(128) = 'MergeBCPData'*/
SELECT @ImportDate = Getdate ()
DECLARE @FileName VARCHAR(1000),
@File VARCHAR(8000)DECLARE @cmd VARCHAR(2000)
CREATE TABLE ##import
(
s VARCHAR(8000)
)CREATE TABLE #dir
(
s VARCHAR(300) NULL
)/*****************************************************************/
-- Import file
/*****************************************************************/
SELECT @cmd = 'dir /B ' + @FilePath + @FileNameMaskSELECT @CMD = '''' + @CMD + ''''
-- Print string and manually run with EXEC cmd_shell + string printed below works fine.
PRINT '@cmd: ' + Cast(@cmd AS VARCHAR(2000))PRINT ''
EXEC MASTER..Xp_cmdshell @cmd
DELETE #dir
INSERT #dir
EXEC MASTER..Xp_cmdshell @cmd-- Select to screen to see what we are getting.
SELECT '#DIR', *
FROM #dir-- Test
-- EXEC master..xp_cmdshell 'dir /B g:\UnZip\*.*'DELETE #dir
WHERE s IS NULL
OR s LIKE '%not found%'
OR s IN (SELECT importedfiles
FROM importedfiles)WHILE EXISTS (SELECT *
FROM #dir
WHERE s IS NOT NULL)
BEGIN
SELECT @FileName = Min(s)
FROM #dir
WHERE #dir.s NOT IN (SELECT importedfiles
FROM importedfiles)SELECT @File = @FilePath + @FileName
PRINT '(line 95) @File: ' + Cast(@File AS VARCHAR(1000))
PRINT''
SELECT @cmd = 'bulk insert'
SELECT @cmd = @cmd + ' ##Import'
SELECT @cmd = @cmd + ' from'
SELECT @cmd = @cmd + ' ''' + Replace (@File, '"', '') + ''''
SELECT @cmd = @cmd + ' with (FIELDTERMINATOR=''|'''
SELECT @cmd = @cmd + ',ROWTERMINATOR = ''' + Char (10) + ''')'
PRINT 'Fully Built Command Line: ' + Cast(@cmd AS VARCHAR(1000))
PRINT''
TRUNCATE TABLE ##import
-- import the data
EXEC (@cmd)
PRINT 'Under EXEC (@SMD) Line 125'-- remove filename just imported
DELETE #dir
WHERE s = @FileNameINSERT INTO importedfiles
(customerid,
importedfiles)
VALUES (@CustomerID,
@FileName)EXEC @MergeProc
-- Archive the file
SELECT @cmd = 'move ' + @FilePath + @FileName + ' ' + @ArchivePath + @FileNameEXEC MASTER..Xp_cmdshell @cmd
ENDIF @@ERROR = 0
BEGIN TRANSACTION-- INSERT FIle Delete code here to remove imported files from the hard drive.
-- xp_cmdshell 'del c:\delete-me-file.txt'
COMMIT TRANSACTIONDROP TABLE ##import
DROP TABLE #dir
-- Sample code (NOT TESTED) To Delete files from FIle System after import: xp_cmdshell 'del c:\delete-me-file.txt'
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
-
I just tried a subset of this on my system (SQL 2016 SP1) and got the same result you did. My subset test was:
CREATE TABLE #test (s VARCHAR(8000))
DECLARE @cmd VARCHAR(2000)
SELECT @cmd = 'dir /B'
SELECT @cmd = '''' + @CMD + ''''
PRINT '@cmd: ' + CAST(@cmd AS VARCHAR(2000))
PRINT ''
EXEC [master].[sys].[xp_cmdshell] @cmd
INSERT [#test]
EXEC [master].[sys].[xp_cmdshell] @cmd
SELECT *
FROM #test
DROP TABLE #test
But take out the line "SELECT @cmd = '''' + @CMD + ''''" and it seems to work for me without giving me the output you saw.
Pretty sure you don't need to put quotes around the entire command, but you probably want to put quotes around the file paths. So you will want to put " around both the @FilePath and @ArchivePath to ensure it has the correct paths.Does that help?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. - bmg002 - Wednesday, December 13, 2017 12:42 PM
I got this fixed this morning and I should have reported on it. In the top section and further down where I am building a string that string needs to be in quotes. I was wrapping in single quotes using '''' + whatever + '''' So I changed to wrap in double quotes, using singles to encapsulate ( ' " ' + whatever + ' " ' ) and it works perfectly. In my opinion I should not have had to do this but it works and I am burried so.....
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
- Jeffery Williams - Wednesday, December 13, 2017 12:48 PM
I've gotten into the habit of encapsulating all file paths in double quotes (not 2 single quotes) because you never know when someone is going to create a directory or file name with spaces or other "nuances" in it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply