Professional Web Applications Themes

Copying a file with sp_oa stored procedures... - Microsoft SQL / MS SQL Server

I would like to copy\move files around on my servers utilizing the sp_oa stored procedures. I have looked through the doentation and I really did not find anything that would really help me. Does anyone have t- sql script or stored procedure that does what I am looking for? I am running Windows 2000 server and SQL Server 2000. Thank you again... Brett...

  1. #1

    Default Copying a file with sp_oa stored procedures...

    I would like to copy\move files around on my servers
    utilizing the sp_oa stored procedures. I have looked
    through the doentation and I really did not find
    anything that would really help me. Does anyone have t-
    sql script or stored procedure that does what I am looking
    for? I am running Windows 2000 server and SQL Server 2000.

    Thank you again...

    Brett

    Brett Guest

  2. #2

    Default Re: Copying a file with sp_oa stored procedures...

    EXEC master..xp_cmdshell 'COPY C:\File.TXT C:\NEWDIR\File.TXT'

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Brett" <net> wrote in message
    news:0d6301c34af2$478b2bf0$gbl... 


    David Guest

  3. #3

    Default Re: Copying a file with sp_oa stored procedures...

    No David...

    You missed the whole point!!! I do not want to use
    xp_cmdshell... I want to use the sp_oa stored procedures...

     
    C:\NEWDIR\File.TXT' [/ref]
    looking [/ref]
    2000. 
    >
    >
    >.
    >[/ref]
    Brett Guest

  4. #4

    Default Re: Copying a file with sp_oa stored procedures...

    What if I want to use newid as a paameter which shows the folder place?
    like
    declare newid nvarchar(10)
    set newid = '123'
    EXEC master..xp_cmdshell 'COPY C:\' + newid + '\File.TXT
    C:\NEWDIR\File.TXT'

    then it says invalid +............
    Thanks ,
    Pat


    "David Portas" <org> wrote in message
    news:#phx.gbl... 
    >
    >[/ref]


    Patrick Guest

  5. #5

    Default Re: Copying a file with sp_oa stored procedures...

    The EXEC string has to be a constant or a var not a complex expression. Do
    it like this:

    DECLARE cmdstr NVARCHAR(1000), newid NVARCHAR(10)
    SET newid = '123'

    SET cmdstr = 'COPY C:\' + newid + '\File.TXT C:\NEWDIR\File.TXT'
    EXEC master..xp_cmdshell cmdstr

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  6. #6

    Default Re: Copying a file with sp_oa stored procedures...

    I am using:
    DECLARE cmdstr NVARCHAR(1000), newid NVARCHAR(10)
    SET newid = '123'
    SET cmdstr = 'COPY \\server1\mufolder\' + newid + '\File.TXT C:\NEWDIR\'
    + newid + 'File.TXT'
    EXEC master..xp_cmdshell cmdstr
    and files are not getting copied !!!!! <SERVER1> is a server in the same
    workgroup with my server. <mufolder> is a shared folder and asscessable .

    Thanks in advance,
    Pat


    "David Portas" <org> wrote in message
    news:phx.gbl... 


    Patrick Guest

  7. #7

    Default Re: Copying a file with sp_oa stored procedures...

    I would suggest that it is a security issue. Perhaps SQL Server is running
    under an account that does not have access to the share. Instead of running
    under "Local System", you need to set it up with an account that has access
    to the remote server (perhaps a domain account).

    Steve

    "Patrick" <all-in-1.com> wrote in message
    news:e71%phx.gbl... [/ref]
    Do 
    >
    >[/ref]


    Steve Guest

  8. #8

    Default Re: Copying a file with sp_oa stored procedures...

    First one is a copy and the second is move.

    /************************************************** *********************
    *******/
    Print 'sp_FSOCopyFile Start: ' + cast(GetDate() as varchar(20))
    /************************************************** *********************
    *******/
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO
    /****** Object: dbo.sp_FSOCopyFile Script Date: Tuesday, July 15,
    2003 ******/
    if exists (select * from dbo.sysobjects where id =
    object_id(N'[dbo].[sp_FSOCopyFile]') and OBJECTPROPERTY(id,
    N'IsProcedure') = 1)
    drop Procedure [dbo].[sp_FSOCopyFile]
    GO
    CREATE PROCEDURE sp_FSOCopyFile
    FileNameSource varchar(1000) = null,
    FileNameTarget varchar(1000) = null
    AS
    declare iObjectHandle int,
    iReturnValue int,
    cErrorSource varchar(255),
    cErrorDescription varchar(255)
    set nocount on
    if FileNameSource is null or FileNameTarget is null
    return
    -- Create object
    exec iReturnValue = sp_OACreate 'Scripting.FileSystemObject',
    iObjectHandle OUT
    -- If unable to create object
    If iReturnValue <> 0 Goto ErrorTag
    -- Call method in object
    exec iReturnValue = sp_OAMethod iObjectHandle, 'CopyFile', null,
    FileNameSource, FileNameTarget,TRUE
    -- If unable to call method
    If iReturnValue <> 0 Goto ErrorTag
    -- Destroy object
    Exec iReturnValue = sp_OADestroy iObjectHandle
    -- If unable to destroy object
    If iReturnValue <> 0 Goto ErrorTag
    -- Leave
    Return
    ErrorTag:
    Exec sp_OAGetErrorInfo iObjectHandle, cErrorSource OUT,
    cErrorDescription OUT
    Print cErrorSource + ': ' + cErrorDescription
    Return
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO
    GRANT EXECUTE ON [dbo].[sp_FSOCopyFile] TO [PUBLIC]
    GO


    /************************************************** *********************
    *******/
    Print 'sp_FSOMoveFile Start: ' + cast(GetDate() as varchar(20))
    /************************************************** *********************
    *******/
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS OFF
    GO
    /****** Object: dbo.sp_FSOMoveFile Script Date: Tuesday, July 15,
    2003 ******/
    if exists (select * from dbo.sysobjects where id =
    object_id(N'[dbo].[sp_FSOMoveFile]') and OBJECTPROPERTY(id,
    N'IsProcedure') = 1)
    drop Procedure [dbo].[sp_FSOMoveFile]
    GO
    CREATE PROCEDURE sp_FSOMoveFile
    FileNameSource varchar(1000) = null,
    FileNameTarget varchar(1000) = null
    AS
    declare iObjectHandle int,
    iReturnValue int,
    cErrorSource varchar(255),
    cErrorDescription varchar(255)
    set nocount on
    if FileNameSource is null or FileNameTarget is null
    return
    -- Create object
    exec iReturnValue = sp_OACreate 'Scripting.FileSystemObject',
    iObjectHandle OUT
    -- If unable to create object
    If iReturnValue <> 0 Goto ErrorTag
    -- Call method in object
    exec iReturnValue = sp_OAMethod iObjectHandle, 'MoveFile', null,
    FileNameSource, FileNameTarget
    -- If unable to call method
    If iReturnValue <> 0 Goto ErrorTag
    -- Destroy object
    Exec iReturnValue = sp_OADestroy iObjectHandle
    -- If unable to destroy object
    If iReturnValue <> 0 Goto ErrorTag
    -- Leave
    Return
    ErrorTag:
    Exec sp_OAGetErrorInfo iObjectHandle, cErrorSource OUT,
    cErrorDescription OUT
    Print cErrorSource + ': ' + cErrorDescription
    Return
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO
    GRANT EXECUTE ON [dbo].[sp_FSOMoveFile] TO [PUBLIC]
    GO


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Mitchell Guest

Similar Threads

  1. Stored Procedures
    By Anthony M. Davis in forum Coldfusion - Advanced Techniques
    Replies: 28
    Last Post: February 9th, 03:17 PM
  2. dt_ Stored Procedures
    By chopper in forum ASP Database
    Replies: 2
    Last Post: July 20th, 04:06 PM
  3. New to ASP and Stored Procedures
    By John Berman in forum ASP Database
    Replies: 6
    Last Post: February 28th, 01:37 AM
  4. Stored Procedures and 4GL
    By Ahmer Sajjad in forum Informix
    Replies: 1
    Last Post: September 9th, 01:23 PM
  5. Oracle stored procedures vs Running from a flat .sql file
    By computer person in forum Oracle Server
    Replies: 12
    Last Post: January 8th, 10:58 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139