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