Stored Procedure to Copy Files

Stored procedure to help you out to copy files see the code bellow

USE [aspnetdb] -- Replace with your DB

GO

/****** Object: StoredProcedure [dbo].[usp_copyfile] Script Date: 04/21/2009 09:22:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create procedure [dbo].[usp_copyfile]

@msg as varchar(200),

@input as varchar(100)

,@retorno as varchar(200) output

as

Begin

declare @drive as varchar(100)

declare @drive_dest as varchar(100)

declare @path as varchar(800)

declare @path_dest as varchar(800)

declare @cmd as varchar(800)

declare @ext as char(3)

set @path_dest='\Dest_Path\'

-- Para Exemplo, não esquecer barra de directorio final

-- Do not forget of blackslash

--

set @path='\PROGRA~1\DATA\'

set @drive='c:'

set @drive_dest='d:'

set @cmd='copy '+@drive+@path+@msg+@ext

set @cmd=@cmd+@drive_dest+@path_dest+ltrim(rtrim(@input))+(select cast(day(getdate()) as varchar(2)))

+(select cast(month(getdate()) as varchar(2)))

+(select cast(year(getdate()) as varchar(4)))

+replace((select convert(varchar(8),getdate(),108)),':','')+@ext

-- Para Testar Caminhos e Visualizar

-- For testing paths and to show

-- select @cmd

exec master..xp_cmdshell @cmd ,no_output

Set @retorno=@drive_dest+@path_dest+ltrim(rtrim(@input))+(select cast(day(getdate()) as varchar(2)))

+(select cast(month(getdate()) as varchar(2)))

+(select cast(year(getdate()) as varchar(4)))

+replace((select convert(varchar(8),getdate(),108)),':','')+@ext

end

Related Posts:

• Stored procedure to copy files

• SQL date manipulation

• Database Triggers

• Create SP with Cursor

Create SP with Cursor

These is my first post in the New Year of 2009, because i had lot's of thing in my professional side, you already notice the change in the logo and in the name of the Blog, i had register the domain www.ITTechBuZ.com e novo logo.
The logo, pretends to give the communication as the major role between Business and IT.
This blog continues to post for Tech people. Today I post one more of the saga of SQL Tip and Tricks about a Store procedure (SP) with a cursor inside to update maintance contracts

--
-- Creates SP com o nome sp_update_onsite
--
create procedure [dbo].[sp_update_onsite]
as Begin


--
-- Inserts ticket in a table when the status is 'Closed'
-- I use an Insert with a Select
--



INSERT INTO [HEAT].[dbo].[Tickets] ([CallId] ,[CallType] ,[CriData] ,[Valor] ,[Saldo] ,[UIDMANUT])


select d.callid,c.calltype,c.closeddate, d.horas_gastas, d.horas_saldo
,d.uidmanut,c.custid
from detail d
inner join calllog c on c.callid=d.callid
inner join subset s on s.callid=c.callid
inner join config cfg on cfg.u_idreg=d.uidmanut
where (c.calltype='Onsite' or c.calltype='Packs') and c.callstatus='Closed' and c.actualiza<>'SIM'
and c.callid not in (select callid from tickets )

--
--
-- Cursor used to update
--

-- Declares the Cursor Cursor_Tickets
-- and Select where it runs the Cursor

DECLARE Cursor_tickets CURSOR for
SELECT t.CallId,c.custid,t.Valor,t.UIDMANUT
from tickets t
inner join calllog c on c.callid=t.callid
where t.[CallType]='Onsite' and c.actualiza <>'SIM'

--
-- Declares the variables to pass througth
--

Declare @Callid varchar(8)
declare @custid varchar(50)
declare @valor decimal(17,2)
declare @uidmanut varchar(25)

set @callid=''
set @custid=''
set @valor=0
set @uidmanut=''


Open Cursor_tickets /* abrir o cursor */

fetch next from Cursor_tickets
into @callid,@custid,@valor,@uidmanut
while @@fetch_Status=0
begin

--- Updates hours spent
--- select @valor,@custid,@uidmanut (for Testing purposes)

update config set horas_gastas=isnull(@valor,0)+(isnull(horas_gastas,0)) ,horas_saldo=isnull(horas_saldo,0)-isnull(@valor,0)
where u_idreg=@uidmanut and custid=@custid

set @callid=''
set @custid=''
set @valor=0
set @uidmanut=''

fetch next from Cursor_Tickets
into @callid,@custid,@valor,@uidmanut
end
close Cursor_tickets
Deallocate Cursor_tickets

--
-- In the end updates the table for not processing agains
--

update calllog set actualiza='SIM' where callid in (select callid from tickets) and actualiza<>'SIM'


end