Is this possible without using Cursors?

Hello!
I have a stored proc that checks the file existance in one location and
copy them to another location. The stored proc reads the record one by
one and builds the DOS COPY command. In the end, it exccutes the DOS
command using xp_cmdshell. See below the code.
The stored proc is working great but I had to use the CURSOR for reading
the records. I was wondering if I can avoid using it? Is this possible?
how? Thanks in advance for your help!
CREATE TABLE [dbo].[FILE_PATH] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Source] [varchar] (150) NULL ,
[Destination] [char] (150) NULL ,
[Environment] [char] (25) NULL ,
[Filename] [varchar] (50) NULL
) ON [PRIMARY]
GO
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverA\folder1','\\serverD\folder1
','Development',
'file1')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file3')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverA\folder1','\\serverD\folder1
','Development',
'file2')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file4')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
CREATE proc CopyFiles
as
set nocount on
declare @.source varchar(150)
declare @.destination varchar(150)
declare @.DOScmd varchar(300)
declare @.source_dir varchar(200)
declare @.filename varchar(30)
declare @.environment varchar(200)
declare @.environementTmp varchar(200)
declare @.errortext varchar(200)
select
@.environementTmp =
case @.@.servername
when 'server A' then 'Development'
when 'server B' then 'UAT'
when 'server C' then 'Production'
end
create table #files(filename sysname NULL)
declare filecursor cursor for
select source, destination, environment, [filename]
from file_path
open filecursor
fetch next from filecursor into @.source, @.destination, @.environment,
@.filename
while (@.@.fetch_status = 0)
begin
if @.environementTmp = @.environment
begin
set @.source_dir = 'dir ' + '"'+rtrim(@.source) + rtrim(@.filename)+ '"'
+ ' /b'
insert #files exec master..xp_cmdshell @.source_dir
if (select filename from #files where filename is not null) = @.filename
begin
set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) + rtrim(@.filename) +'"'+ '
' +'"'+ rtrim(@.destination)+'"'
exec master.dbo.xp_cmdshell @.DOScmd
end
else
begin
set @.errortext = 'The File ' + rtrim(@.filename) + ' does not
exist!!!'
exec master.dbo.xp_sendmail
@.recipients = 'abc123',
@.copy_recipients = 'abc123'
@.subject = @.errortext,
@.message = @.errortext
end
end
truncate table #files
fetch next from filecursor into @.source, @.destination, @.environment,
@.filename
end
close filecursor
deallocate filecursor
GO
*** Sent via Developersdex http://www.examnotes.net ***Test Test wrote:
> Hello!
> I have a stored proc that checks the file existance in one location
> and copy them to another location. The stored proc reads the record
> one by one and builds the DOS COPY command. In the end, it exccutes
> the DOS command using xp_cmdshell. See below the code.
> The stored proc is working great but I had to use the CURSOR for
> reading the records. I was wondering if I can avoid using it? Is this
> possible? how? Thanks in advance for your help!
> CREATE TABLE [dbo].[FILE_PATH] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Source] [varchar] (150) NULL ,
> [Destination] [char] (150) NULL ,
> [Environment] [char] (25) NULL ,
> [Filename] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO
>
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file1')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file3')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production',
> 'file5') go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file2')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file4')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production',
> 'file5')
>
> CREATE proc CopyFiles
> as
>
> set nocount on
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> declare @.source_dir varchar(200)
> declare @.filename varchar(30)
> declare @.environment varchar(200)
> declare @.environementTmp varchar(200)
> declare @.errortext varchar(200)
>
> select
> @.environementTmp =
> case @.@.servername
> when 'server A' then 'Development'
> when 'server B' then 'UAT'
> when 'server C' then 'Production'
> end
> create table #files(filename sysname NULL)
> declare filecursor cursor for
> select source, destination, environment, [filename]
> from file_path
> open filecursor
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> while (@.@.fetch_status = 0)
> begin
> if @.environementTmp = @.environment
> begin
> set @.source_dir = 'dir ' + '"'+rtrim(@.source) + rtrim(@.filename)+ '"'
> + ' /b'
> insert #files exec master..xp_cmdshell @.source_dir
> if (select filename from #files where filename is not null) =
> @.filename begin
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) + rtrim(@.filename) +'"'+ '
> ' +'"'+ rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> end
> else
> begin
> set @.errortext = 'The File ' + rtrim(@.filename) + ' does not
> exist!!!'
> exec master.dbo.xp_sendmail
> @.recipients = 'abc123',
> @.copy_recipients = 'abc123'
> @.subject = @.errortext,
> @.message = @.errortext
> end
> end
> truncate table #files
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> end
> close filecursor
> deallocate filecursor
> GO
>
Something tells me more time is spent executing xp_cmdshell than the
time used for the cursor, so you're probably fine. Cursors are good for
this type of operation IMO since it makes the code relatively clear. You
should always use local, read-only forward-only cursors, for this type
of read-only processing so update your declare statement accordingly.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||We had a similar problem - we solved this by using COM objects.
We created our own custom set of COM objects, but WSH.FileSystemObject does
the same thing.
Essentially:-
Call master.dbo.sp_OACreate to create the com object (returns @.lpObject)
Then create a UDF to do the file exists check
Call master.dbo.sp_OAMethod for file exists passing filename returning file
exists
Then destroy the object using master.dbo.sp_OADestory
Then you'd do
UPDATE
tableWithFileNamesIn
SET
FileExists = dbo.fnFileExists( @.lpObject , FullPathToFile ) AS
FileExists
Assuming: tableWithFileNamesIn( FullPathToFile VARCHAR(260) , FileExists
BIT )
http://msdn.microsoft.com/library/d.../>
sotutor.asp
If the process isn't going to run as system admin, create a role in master
called COMCreator, and add all the sp_OA... (except for OAStop) to that
role and add the process user account to COMCreator.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:uB58v9A0FHA.268@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have a stored proc that checks the file existance in one location and
> copy them to another location. The stored proc reads the record one by
> one and builds the DOS COPY command. In the end, it exccutes the DOS
> command using xp_cmdshell. See below the code.
> The stored proc is working great but I had to use the CURSOR for reading
> the records. I was wondering if I can avoid using it? Is this possible?
> how? Thanks in advance for your help!
> CREATE TABLE [dbo].[FILE_PATH] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Source] [varchar] (150) NULL ,
> [Destination] [char] (150) NULL ,
> [Environment] [char] (25) NULL ,
> [Filename] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO
>
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file1')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file3')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file2')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file4')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
>
> CREATE proc CopyFiles
> as
>
> set nocount on
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> declare @.source_dir varchar(200)
> declare @.filename varchar(30)
> declare @.environment varchar(200)
> declare @.environementTmp varchar(200)
> declare @.errortext varchar(200)
>
> select
> @.environementTmp =
> case @.@.servername
> when 'server A' then 'Development'
> when 'server B' then 'UAT'
> when 'server C' then 'Production'
> end
> create table #files(filename sysname NULL)
> declare filecursor cursor for
> select source, destination, environment, [filename]
> from file_path
> open filecursor
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> while (@.@.fetch_status = 0)
> begin
> if @.environementTmp = @.environment
> begin
> set @.source_dir = 'dir ' + '"'+rtrim(@.source) + rtrim(@.filename)+ '"'
> + ' /b'
> insert #files exec master..xp_cmdshell @.source_dir
> if (select filename from #files where filename is not null) = @.filename
> begin
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) + rtrim(@.filename) +'"'+ '
> ' +'"'+ rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> end
> else
> begin
> set @.errortext = 'The File ' + rtrim(@.filename) + ' does not
> exist!!!'
> exec master.dbo.xp_sendmail
> @.recipients = 'abc123',
> @.copy_recipients = 'abc123'
> @.subject = @.errortext,
> @.message = @.errortext
> end
> end
> truncate table #files
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> end
> close filecursor
> deallocate filecursor
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***