A client is switching the way they deliver files to us, from emailing them, to a secure FTP. So, I wrote this and set it up as a SQL Agent job to run hourly on Monday, which is the day the sample shows up. The goal is to take the text file and move it to the folder I process these files from, and send an email to a few people letting us know the name of the file and when it moved.

I'm using a Schema.ini file because when I load these files without one, the data becomes garbled. A file with a few dozen columns becomes two columns with a bunch of odd question mark delimiters.

It's running as I want it to, but it fails as a SQL Agent job because I either need to change the owner or my user needs the appropriate privs. My sysadmin is looking into that part.

Here's how my SP looks now, with some anonymity edits:

USE [Sample]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[filemove]

--EXECUTE filemove 'X:\Service\'

@filespec varchar(512)

as

declare @filetbl table (fileinfo varchar(2000))

declare @fileinfoemail varchar(8000)

declare @commandline varchar(8000)

declare @filename varchar(128)

declare @droppath varchar(512)

set @droppath = '\\server\path'

declare @sql varchar(8000)

select @commandline = ''

select @commandline = left('dir "' + @filespec + '" /A-D /B /O-D', 4000)

insert into @filetbl (fileinfo)

exec xp_cmdshell @commandline

--print (@commandline)

delete from @filetbl where fileinfo is null or fileinfo='file not found'

set @filename = (SELECT top 1 fileinfo FROM @filetbl)

if @filename is null

begin

select 'NOPE' as [NOPE]

end

else if @filename is not null

begin

set @sql = 'EXEC master.sys.xp_cmdshell ' + '''MOVE /Y "' + @filespec + @filename + '" "' + @droppath + '\"'''

exec(@sql)

--print (@sql)

set @sql = 'exec master..xp_cmdshell ' + '''echo [' + @filename + '] > ' + @droppath + '\' + 'Schema.ini'''

exec(@sql)

set @sql = 'exec master..xp_cmdshell ' + '''echo Format=TabDelimited >> ' + @droppath + '\' +'Schema.ini'''

exec(@sql)

set @sql = 'exec master..xp_cmdshell ' + '''echo MaxScanRows = 0 >> ' + @droppath + '\' +'Schema.ini'''

exec(@sql)

set @sql = 'exec master..xp_cmdshell ' + '''echo CharacterSet = ANSI >> ' + @droppath + '\' +'Schema.ini'''

exec(@sql)

select @commandline = ''

select @commandline = 'dir "' + @filespec + '" /A-D'

insert into @filetbl (fileinfo)

execute xp_cmdshell @commandline

delete from @filetbl where fileinfo is null or fileinfo = 'file not found' or fileinfo = 'Schema.ini'

set @fileinfoemail = (select top 1 fileinfo from @filetbl where fileinfo like '%txt%')

exec msdb.dbo.sp_send_dbmail

@profile_name = 'Erik',

@recipients = 'erik@email',

@copy_recipients = 'distrogroup@email',

@subject='Service Quality file moved',

@body = @fileinfoemail

end