erikd (6/15/2013)

---

I'm replying to this mostly because the error I posted was such a quirky little thing that THIS post was coming up (on the only page of results) when I was searching for a solution.

I couldn't find one, so I sort of worked around it by storing the one result I wanted from my select in a variable, and setting @body to that variable. I couldn't figure out a way to get a larger select to work in @query whether it was written out or stored in a variable. I always got the same errors.

I haven't finished with this SP yet. I will probably go back to using a declared variable table instead of a real one, since the reason for my switching is no longer an issue.

USE [Sample]

GO

/****** Object: StoredProcedure [dbo].[filemove] Script Date: 06/15/2013 10:32:53 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE procedure [dbo].[filemove]

--EXECUTE filemove 'Z:\Service\'

@filespec nvarchar(max),

@order nvarchar (80) = '/O-D'

as

declare @filetbl table (fileinfo varchar(2000))

declare @fileinfoemail varchar(8000)

declare @commandline varchar(4000)

declare @filename nvarchar(max)

declare @droppath nvarchar(max)

set @droppath = '\\server\path'

declare @sql nvarchar(max)

--exec xp_cmdshell 'net use Y: /delete'

--exec xp_cmdshell 'net use Z: /delete'

--exec xp_cmdshell 'net use Y: \\server\path pass /USER:erik'

--exec xp_cmdshell 'net use Z: \\server\path pass /USER:erik'

set @sql = 'delete from sample.dbo.filelist'

exec (@sql)

if @order is not null

begin

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

insert into sample.dbo.filelist (fullpath)

execute xp_cmdshell @commandline

delete from sample.dbo.filelist where fullpath is null or fullpath='file not found'

end

set @filename = (SELECT top 1 fullpath FROM sample.dbo.filelist)

set @sql = 'EXEC master.sys.xp_cmdshell ' + '''COPY ' + @filespec + '\' + @filename + ' ' + @droppath + '\' + @filename + ' /Y /B''';

exec(@sql)

--Change to this one when finalized

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

--exec(@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 = '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.com',

@subject='Service file moved',

@body = @fileinfoemail