June 14, 2013 at 9:27 am
Hi,
The point of this is to check for a file on a remote folder, move it to the processing folder, and then write a Schema.ini file with the new file name in it. I used a fair amount of code from here:( https://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/ )
I don't have a ton of experience writing these, so any advice or corrections is appreciated.
create procedure dbo.filemove
--EXECUTE filemove 'Z:\folder\'
@filespec nvarchar(max),
@order nvarchar (80) = '/O-D'
AS
declare @myfiles table (fullpath varchar(2000))
declare @commandline varchar(4000)
declare @filename nvarchar(max)
declare @droppath nvarchar(max)
set @droppath = '\\server\folder\folder\'
declare @sql nvarchar(max)
exec xp_cmdshell 'net use Z: /delete'
exec xp_cmdshell 'net use Z: \\Server\Folder\ password /USER:username'
if @order is not null
begin
select @commandline =left('dir "' + @filespec + '" /A-D /B '+@order,4000)
insert into @myfiles (fullpath)
execute xp_cmdshell @commandline
delete from @myfiles where fullpath is null or fullpath='file not found'
end
set @filename = (SELECT top 1 fullpath FROM @MyFiles)
set @sql = 'EXEC master.sys.xp_cmdshell ' + '''COPY ' + @filespec + @filename + ' ' + @droppath + @filename + ' /Y /B''';
exec(@sql)
exec master..xp_cmdshell 'echo [' + @filename + '] > ' + @droppath+ 'Schema.ini'
exec master..xp_cmdshell 'echo Format=TabDelimited >> ' + @droppath+ 'Schema.ini'
exec master..xp_cmdshell 'echo MaxScanRows = 0 >> ' + @droppath+ 'Schema.ini'
exec master..xp_cmdshell 'echo CharacterSet = ANSI >> ' + @droppath+ 'Schema.ini'
June 14, 2013 at 6:11 pm
Are you getting an error or just looking for a "peer review"?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2013 at 6:19 pm
Hi Jeff,
Actually, after a day of on and off tinkering, the SP has changed a bit. I had to make a separate procedure on MSDB that the SP I posted calls to send the email. What I'm stuck on now is getting db sendmail to execute the query I want correctly. This could potentially be solved by creating an actual table that my results from the original query get selected into, but here:
use msdb
go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure aw_mail
as
declare @sql nvarchar (max)
set @sql = 'exec sp_send_dbmail @profile_name = ''Erik'',
@recipients = ''erik@email.com'',
@subject=''Service file moved'',
@body=''File moved to \\server\path\'',
@query = ''exec cp_cmdshell dir \\server\path\ /B /A-D /O-D'''
exec(@sql)
Results in this error:
Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: Msg 15404, Level 16, State 19, Server P42, Line 1
Could not obtain information about Windows NT group/user 'Erik', error code 0x6e.
June 15, 2013 at 8:42 am
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
June 22, 2013 at 4:29 pm
Writing a PowerShell script and kicking it off using powershell.exe from a SQL Agent CmdExec Job Step would be another option here. The backflips you're having to perform using xp_cmdshell to get directory listings into a table and then shell out using dynamic SQL and xp_cmdshell again all go away when you're in a PowerShell session. There is a little learning curve to getting started with PowerShell but it offers a much more robust bridge between the OS and SQL Server for these kinds of tasks than T-SQL does or ever will.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 24, 2013 at 10:09 pm
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
I have to ask, what is the ultimate goal here? It seems that you're moving just one file at a time and then reporting on that one file. Why not move all of the files and report on them in a single email?
Also, I understand that you're making a Schema.Ini file for each file you've found, but why? What is it that you're doing with the file once it reaches its destination that you think you need a Schema.Ini file?
Perhaps if we knew such things, we might be able to greatly simply this effort and the effort not yet revealed once the file reaches its destination.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 25, 2013 at 9:00 am
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
June 25, 2013 at 9:22 am
Gosh, that seems like a lot of code to move the newest file from one place to another. Here is how you would do the same in PowerShell:
$SourceFileSpec = "H:\"
$DestinationFileSpec = "J:\"
$NewestFile = Get-ChildItem -Path $SourceFileSpec | Where {-not $_.PSIsContainer} | Sort-Object -Property LastWriteTime -Descending | Select-Object -Property FullName -First 1
$NewestFile
Move-Item -Path $NewestFile -Destination $DestinationFileSpec
#send an email that the file was moved
You could save this to a .ps1 file and then call it from a SQL Agent CmdExec job step using powershell.exe.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 25, 2013 at 9:47 am
opc.three (6/25/2013)
Gosh, that seems like a lot of code to move the newest file from one place to another. Here is how you would do the same in PowerShell:$SourceFileSpec = "H:\"
$DestinationFileSpec = "J:\"
$NewestFile = Get-ChildItem -Path $SourceFileSpec | Where {-not $_.PSIsContainer} | Sort-Object -Property LastWriteTime -Descending | Select-Object -Property FullName -First 1
$NewestFile
Move-Item -Path $NewestFile -Destination $DestinationFileSpec
#send an email that the file was moved
You could save this to a .ps1 file and then call it from a SQL Agent CmdExec job step using powershell.exe.
PowerShell is on my long list of things to learn. I've read some on it, just not enough to comfortably use it, never mind integrate its use with SQL.
Thanks
June 25, 2013 at 10:25 am
erikd (6/25/2013)
opc.three (6/25/2013)
Gosh, that seems like a lot of code to move the newest file from one place to another. Here is how you would do the same in PowerShell:$SourceFileSpec = "H:\"
$DestinationFileSpec = "J:\"
$NewestFile = Get-ChildItem -Path $SourceFileSpec | Where {-not $_.PSIsContainer} | Sort-Object -Property LastWriteTime -Descending | Select-Object -Property FullName -First 1
$NewestFile
Move-Item -Path $NewestFile -Destination $DestinationFileSpec
#send an email that the file was moved
You could save this to a .ps1 file and then call it from a SQL Agent CmdExec job step using powershell.exe.
PowerShell is on my long list of things to learn. I've read some on it, just not enough to comfortably use it, never mind integrate its use with SQL.
Thanks
I hear you. It's hard to branch out, but think about how much time you're spending trying to contort T-SQL to do what is in essence a set of file system management tasks. Consider what you're trying to accomplish, what SQL stands for and what SQL Server actually does best. Learning some basic PowerShell (this is basic stuff PowerShell does with ease) might be a worthwhile investment of your time to broaden your skills and eventually know that you're applying the right tool for the job at hand.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 25, 2013 at 10:39 am
For the simple file move, I agree with you. But I'm doing a bit more than that with the file name as a variable. I'm not sure I could easily do that with PS.
So like, where I insert the file name into the Schema.ini file, or email the file name... Yeah. Not sure about those things with your method.
June 25, 2013 at 11:12 am
erikd (6/25/2013)
For the simple file move, I agree with you. But I'm doing a bit more than that with the file name as a variable. I'm not sure I could easily do that with PS.So like, where I insert the file name into the Schema.ini file, or email the file name... Yeah. Not sure about those things with your method.
Use Out-File to write a new file with PowerShell.
One of PowerShell's strengths is its help system. If you learn to use the help, you can learn how to script in PowerShell in a hurry. At a PowerShell prompt type
help Out-File
or
help Out-File -Examples
to see how to use Out-File.
To send an email with PowerShell, if you like the audit trail Database Mail offers you can connect to your database and execute sp_send_dbmail using Invoke-SqlCmd. Or you can skip the database dependency completely and add a function to your script to send an email directly out of PowerShell, like this: http://blogs.msdn.com/b/rkramesh/archive/2012/03/16/sending-email-using-powershell-script.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 25, 2013 at 11:59 am
I'll see if I can figure it out when I have some extra time. I'm most likely going to leave this SP as-is (at least for now), since it's working, and try to integrate PS into something a bit simpler when starting fresh.
The way things usually go with me at work is: I use T-SQL because it's all I have any practical experience using to do things programmatically (and everything I need to do involves a file needing to end up in a table, or something in a table that needs to be manipulated).
I know other things like SSIS and PS exist as solutions, but I'd have no idea where to start with them. And since it's a constant issue of providing the fastest solution, I don't necessarily have the bandwidth to figure something totally foreign out. Or to even know when something else would be a better solution than SQL. Does that make sense?
Thanks
June 25, 2013 at 12:17 pm
erikd (6/25/2013)
I'll see if I can figure it out when I have some extra time. I'm most likely going to leave this SP as-is (at least for now), since it's working, and try to integrate PS into something a bit simpler when starting fresh.The way things usually go with me at work is: I use T-SQL because it's all I have any practical experience using to do things programmatically (and everything I need to do involves a file needing to end up in a table, or something in a table that needs to be manipulated).
I know other things like SSIS and PS exist as solutions, but I'd have no idea where to start with them. And since it's a constant issue of providing the fastest solution, I don't necessarily have the bandwidth to figure something totally foreign out. Or to even know when something else would be a better solution than SQL. Does that make sense?
Thanks
It definitely makes sense. I deal with that dilemma as well. Regarding where to start or knowing which tool might be better suited to do something than another, that's what is great about these forums, and the community in general, is we can share ideas when a problem is presented for comment. You knowing what is possible using PowerShell and knowing that it is better suited to handle the problem you're trying to solve than is T-SQL is a large part of the barrier to you trying to use it. I am not saying to tell your folks that the project is going to take longer because you have to learn PowerShell first. I am saying to give it a look, and try to work it into your day. I am confident you will find that not all problems are best solved using T-SQL, in fact most problems that involve interacting with the file system are not best solved using T-SQL. I won't get into the reasons, but suffice it to say that if you find yourself using xp_cmdshell then you are probably in need of a design rethink, and in need of looking to something other than T-SQL to solve the problem at hand.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 25, 2013 at 12:40 pm
I'm using a Schema.ini file because when I load these files without one, the data becomes garbled.
"Load these files" how and where? I'm asking because one of the biggest problems you're having seems to be with the creation of the Schema.Ini file for each file. If you're using something like BULK INSERT, the Schema.Ini file isn't necessary. You can just change to a "raw" type file setting.
Also, you didn't answer my question. Why can't we move "all" the files at once and send a single email with the list of files moved?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply