Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Need some eyes on a SP I'm working on Expand / Collapse
Author
Message
Posted Friday, June 14, 2013 9:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 388, Visits: 1,532
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'

Post #1463645
Posted Friday, June 14, 2013 6:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:58 PM
Points: 36,794, Visits: 31,253
Are you getting an error or just looking for a "peer review"?

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1463800
Posted Friday, June 14, 2013 6:19 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 388, Visits: 1,532
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.

Post #1463801
Posted Saturday, June 15, 2013 8:42 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 388, Visits: 1,532
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

Post #1463856
Posted Saturday, June 22, 2013 4:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:45 PM
Points: 7,094, Visits: 12,582
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
Post #1466492
Posted Monday, June 24, 2013 10:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:58 PM
Points: 36,794, Visits: 31,253
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1466969
Posted Tuesday, June 25, 2013 9:00 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 388, Visits: 1,532
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

Post #1467216
Posted Tuesday, June 25, 2013 9:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:45 PM
Points: 7,094, Visits: 12,582
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
Post #1467231
Posted Tuesday, June 25, 2013 9:47 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 388, Visits: 1,532
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
Post #1467251
Posted Tuesday, June 25, 2013 10:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:45 PM
Points: 7,094, Visits: 12,582
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
Post #1467272
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse