Welcome to Sign in | Join | Help
in Search

cum creez un folder in T-SQL (SQLServer 2000)

Last post 06-21-2007, 11:34 PM by ggciubuc. 26 replies.
Page 2 of 2 (27 items)   < Previous 1 2
Sort Posts: Previous Next
  •  06-07-2007, 11:22 AM 2045 in reply to 2044

    Re: cum creez un folder in T-SQL (SQLServer 2000)

    si mai am si problema ca nu pot folosi o cale mai lunga...care sa contina foldere a caror denumire este compusa dn spatii :)

    gen: SET @LocalBackUPPath = 'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP' + @DBName

    in locul SET @LocalBackUPPath = 'D:\' + @DBName

    imi zice ca:

    'C:\Program' is not recognized as an internal or external command,
    operable program or batch file.

    asa arata procedura acum:

    ALTER       proc _BackUp_DBName_WinRAR
     @DBName nvarchar(50),
     @param bit Out

    as
    set @param=0

    declare
     @now    nvarchar(100),
     @luna    nvarchar(10),
     @LogicalBackup   nvarchar(300),
     @CaleBackup0   nvarchar(300),
     @CaleBackup   nvarchar(300),
     @strSQL   nvarchar(4000)

    declare
     @dataRef datetime,
     @month int,
     @year int

    set @dataRef=getdate()
    set @month = datepart(mm,@dataRef)
    set @year = datepart(yyyy,@dataRef)


    select @luna=cast(month(GETDATE()) as nvarchar(50))


    if len(@luna)=1 set @luna='0'+cast(@luna as nvarchar(50))

    set  @strSQL='mkdir "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @DBName + '"'
    EXEC master..xp_cmdshell @strSQL

    -- Create a logical backup device for the full database backup
    set @now=cast(day(GETDATE()) as nvarchar(50))+''+@luna+''+cast(year(GETDATE()) as nvarchar(50))+'_'+cast(DATEPART(hour, GETDATE()) as nvarchar(50))+''+cast(DATEPART(minute, GETDATE()) as nvarchar(50))+''+cast(DATEPART(second, GETDATE()) as nvarchar(50))
    set @CaleBackup0='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @DBName + '\'+ @DBName + '_'+ @now
    set @CaleBackup=@CaleBackup0+'.BAK'
    set @LogicalBackup= @DBName + '_'+ @now

    print 'EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+''''
    exec('EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+'''')
    -- Back up the full database
    exec('BACKUP DATABASE ' + @DBName + ' TO ' + @LogicalBackup)

    DECLARE  @LocalBackUPPath   nvarchar(255),
      @LocalBackUPDrive  nvarchar(128), 
      @TemplateFileName  nvarchar(255),
      @TemplateCharSeparator  nvarchar(128),
      @tmpSQL    varchar(8000),
      @tmpServer   varchar(8000),
      @PackerFileName   nvarchar(255),
      @PackFileName   nvarchar(255),
      @SetPasswordAutomatic  bit,
      @SetPasswordTo    nvarchar(64),
      @PackOptions   nvarchar(255),
      @FileExtention   nvarchar(32)

    SET @PackerFileName = '"C:\Program Files\WinRAR\WinRAR.exe"'
    SET @PackOptions = 'a -df -ep -s -d4096 -rr -m5 -ri1'
    SET @LocalBackupDrive = 'D:'
    SET @LocalBackUPPath = '"' + 'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @DBName
    SET @PackFileName=@LogicalBackup + '.RAR"' --+ @FileExtention

    SET @tmpServer='master.dbo.xp_cmdshell'
    SET @tmpSQL=@PackerFileName + ' ' + @PackOptions + ' ' +  @LocalBackUPPath + '\' + @PackFileName + ' ' + @LocalBackUPPath + '\' + @LogicalBackup + '"'
    EXEC master..xp_cmdshell @tmpSQL

    set @param=1

    return @param
    GO

  •  06-07-2007, 1:51 PM 2046 in reply to 2045

    Re: cum creez un folder in T-SQL (SQLServer 2000)

    Alex,

    Katalyn ţi-a mai spus că trebuie să foloseşti ghilimele (dacă numele fişierului/directorului conţine spaţii). Încearcă să foloseşti comanda dorită direct într-un Command Prompt şi după ce vezi că merge, gândeşte-te unde trebuie să pui ghilimelele în cod.

    Hint: în Command Prompt poţi folosi Tab pentru a completa denumirea unui fişier/director după ce scrii primele litere din denumirea lui.

    Răzvan 

  •  06-07-2007, 2:31 PM 2047 in reply to 2046

    Re: cum creez un folder in T-SQL (SQLServer 2000)

    am iincercat in comand prompt si merge super......dar cu sql-ul nu-mi dau seama de ce nu merge ca-mi formeaza stringul respectiv asa cum trebuie ....cu ghilimele cu tot ce trebuie (sau cel putin asa creed eu)....mai jos pun procedura daca vrei s-o testezi si tu.....nu trebuie decat rulata daca calea din procedura se potriveste si bineinteles database sa nu fie prea mare.....dar stiti voi mai bine ca mine ;)

     create proc _BackUp_DBName_WinRAR


     @DBName nvarchar(50),
     @param bit Out

    as
    set @param=0

    declare
     @now    nvarchar(100),
     @luna    nvarchar(10),
     @LogicalBackup   nvarchar(300),
     @CaleBackup0   nvarchar(300),
     @CaleBackup   nvarchar(300),
     @strSQL   nvarchar(4000)

    declare
     @dataRef datetime,
     @month int,
     @year int

    set @dataRef=getdate()
    set @month = datepart(mm,@dataRef)
    set @year = datepart(yyyy,@dataRef)


    select @luna=cast(month(GETDATE()) as nvarchar(50))


    if len(@luna)=1 set @luna='0'+cast(@luna as nvarchar(50))

    set  @strSQL='mkdir "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @DBName + '"'
    EXEC master..xp_cmdshell @strSQL

    -- Create a logical backup device for the full database backup
    set @now=cast(day(GETDATE()) as nvarchar(50))+''+@luna+''+cast(year(GETDATE()) as nvarchar(50))+'_'+cast(DATEPART(hour, GETDATE()) as nvarchar(50))+''+cast(DATEPART(minute, GETDATE()) as nvarchar(50))+''+cast(DATEPART(second, GETDATE()) as nvarchar(50))
    set @CaleBackup0='D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @DBName + '\'+ @DBName + '_'+ @now
    set @CaleBackup=@CaleBackup0+'.BAK'
    set @LogicalBackup= @DBName + '_'+ @now

    print 'EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+''''
    exec('EXEC master..sp_addumpdevice ''disk'', '''+@LogicalBackup+''', '''+@CaleBackup+'''')
    -- Back up the full database
    exec('BACKUP DATABASE ' + @DBName + ' TO ' + @LogicalBackup)

    DECLARE  @LocalBackUPPath   nvarchar(255),
      @LocalBackUPDrive  nvarchar(128), 
      @TemplateFileName  nvarchar(255),
      @TemplateCharSeparator  nvarchar(128),
      @tmpSQL    varchar(8000),
      @tmpServer   varchar(8000),
      @PackerFileName   nvarchar(255),
      @PackFileName   nvarchar(255),
      @SetPasswordAutomatic  bit,
      @SetPasswordTo    nvarchar(64),
      @PackOptions   nvarchar(255),
      @FileExtention   nvarchar(32)

    SET @PackerFileName = '"C:\Program Files\WinRAR\WinRAR.exe"'
    SET @PackOptions = 'a -df -ep -s -d4096 -rr -m5 -ri1'
    SET @LocalBackupDrive = 'D:'
    SET @LocalBackUPPath = '"' + 'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' + @DBName
    SET @PackFileName=@LogicalBackup + '.RAR"' --+ @FileExtention

    SET @tmpServer='master.dbo.xp_cmdshell'
    SET @tmpSQL=@PackerFileName + ' ' + @PackOptions + ' ' +  @LocalBackUPPath + '\' + @PackFileName + ' ' + @LocalBackUPPath + '\' + @LogicalBackup + + '.BAK' + '"'
    EXEC master..xp_cmdshell @tmpSQL

    set @param=1

    return @param

    GO

    P.S. super hintul ;)....multumesc

  •  06-07-2007, 2:58 PM 2048 in reply to 2047

    Re: cum creez un folder in T-SQL (SQLServer 2000)

    Se pare că e vorba de o limitare a xp_cmdshell-ului (pe care nici eu nu o ştiam). Books Online zice aşa: "command_string cannot contain more than one set of double quotation marks [...] If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround."

    Drept urmare, încearcă:

    SET @PackerFileName = 'c:\progra~1\winrar\rar.exe'

    E recomandabil să foloseşti rar.exe în loc de winrar.exe, deoarece acesta din urmă are GUI care nu are unde să se afişeze (mai ales dacă dă vreo eroare).

    Din alt punct de vedere, ai putea să nu mai faci logical device-uri şi să foloseşti direct BACKUP TO DISK='nume fisier.bak'

    Răzvan

  •  06-07-2007, 3:12 PM 2049 in reply to 2048

    Re: cum creez un folder in T-SQL (SQLServer 2000)

    super tare....merge.....multumesc pt a nu stiu cata oara :)

    alex.

  •  06-21-2007, 11:19 AM 2133 in reply to 2048

    Re: cum creez un folder in T-SQL (SQLServer 2000)

    revin cu inca o rugaminte la aceasta procedura

    as vrea s-o continui si anume....dupa ce face arhiva

    1. sa faca o mapare catre un folder din retea  -- (NET USE)

    2. sa copieze arhiva in acest folder mapat din retea -- (COPY)

    3. sa stearga arhiva daca mutarea s-a facut cu succes -- (DEL)

    multumesc mult.

    alex.

     

  •  06-21-2007, 1:01 PM 2136 in reply to 2133

    Re: cum creez un folder in T-SQL (SQLServer 2000)

    Acum chiar ca trebuie design de package DTS !

    Gheorghe Ciubuc,SQL Server Influencer, MCP(SQL 2000), MCTS (SQL Server 2005) , OCA(Oracle 9i), Sybase(Brainbench)
  •  06-21-2007, 1:52 PM 2137 in reply to 2136

    Re: cum creez un folder in T-SQL (SQLServer 2000)

    eu am incercat sa fac ceva inspirat dintr-un alt exemplu....ceva de genul

    -- pentru serverele din lan se va folosi mapare la copiere

     SET @tmpServer='master..xp_cmdshell'

     SET @tmpSQL='NET USE x: \\' + @SysServerName + '\' + @ParentBackupDrive --+ SUBSTRING(@ParentBackupDrive,1,1) + '$'
     EXEC @tmpServer @tmpSQL

     SET @tmpSQL='COPY x:\' + ' ' + @PathToMove + + @PackFileName -- + @ParentBackupPath + '\' + @PackFileName
     EXEC @tmpServer @tmpSQL

    if @@error = 0
    begin
     SET @tmpSQL='DEL x:\' + @ParentBackupPath + '\' + @PackFileName
     EXEC @tmpServer @tmpSQL 
     EXEC @tmpServer 'NET USE x: /DELETE'
    end
    else
    begin
     EXEC @tmpServer 'NET USE x: /DELETE'
    end

    doar ca se impotmoleste la copy x:\

    :(

    alex.

    P.S. sunt sigur ca merge facut tot din procedura doar ca nu-mi dau seama ce nu fac bine.........

  •  06-21-2007, 2:17 PM 2138 in reply to 2137

    Re: cum creez un folder in T-SQL (SQLServer 2000)

    totul functioneaza super ok.....face si maparea respectivului folder (net use) dar cand incearca sa execute

    SET @tmpSQL='COPY x:\' + ' ' + '"' + @PathToMove + @PackFileName -- + '"' -- + @ParentBackupPath + '\' + @PackFileName
     EXEC @tmpServer @tmpSQL

    primesc eroarea:

    x:\*
    The system cannot find the file specified.
            0 file(s) copied.

    nu inteleg cum trebuie sa execute acest COPY.....syntaxa

    el formeaza acel @tmpSQL astfel:

    'COPY x:\ "D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TEST_20070621_141258.RAR"'

    care vad ca nici din comand prompt nu functioneaza?!?!!.....cum ar trebui sa creeze acest COPY?

    alex.

     

  •  06-21-2007, 2:44 PM 2139 in reply to 2136

    Re: cum creez un folder in T-SQL (SQLServer 2000)

    ggciubuc:
    Acum chiar ca trebuie design de package DTS !

    imi poti explica mai mult legat de cum trebuie sa arate acest DTS, daca e o varianta mai simpla....ma gandesc ca dupa ce se executa jobul care apeleaza procedura care face backupul si pe urma arhiva....sa se execute DTS-ul care sa mute respectivul file de pe diskul meu local pe un altul din retea (cu toate ca nu vad cum imi poate recunoaste arhiva respectiva daca in denumirae ei intra zi luna an ora minute secunde(acestea referinduse la ora la care se face backupul)......am ametit de tot:))) )

    alex.

  •  06-21-2007, 4:06 PM 2140 in reply to 2138

    Re: cum creez un folder in T-SQL (SQLServer 2000)

    merge :)

    SET @tmpServer='master..xp_cmdshell'

     SET @tmpSQL='NET USE X: \\' + @SysServerName + '\' + @ParentBackupPath
     EXEC @tmpServer @tmpSQL

     SET @tmpSQL='COPY ' +  @LocalBackUPPath + '\' + @PackFileName + ' X:\'
     EXEC @tmpServer @tmpSQL
     SET @tmpSQL='DEL ' +  @LocalBackUPPath + '\' + @PackFileName
     EXEC @tmpServer @tmpSQL 
     EXEC @tmpServer 'NET USE X: /DELETE'

    problema era una minora ca de obicei :)

    era "copy FisierDeCopiat CaleaUndeSeDOresteAfiCopiat"....nu invers :)

    alex

  •  06-21-2007, 11:34 PM 2141 in reply to 2139

    Re: cum creez un folder in T-SQL (SQLServer 2000)

    Nu, nu este simplu insa este mai sigur si ai la dispozitie instrumentar de sistem de operare Windows

    pentru operatiunile de add device / backup se foloseste "Execute SQL Task"
    iar pentru verificari/creari foldere , lansare rar, ActiveX Script Task in care se poate baga WMI

    Iata ce se poate scrie intr-un script VBScript

    ' Initialize variables.
    Dim cn As New ADODB.Connection

    Dim ServerName As String, DatabaseName As String, _
       UserName As String, Password As String

    ' Put text box values into connection variables.
    ServerName = "(local)"
    DatabaseName = "Northwind"
    UserName = sa
    Password = ""

    ' Specify the OLE DB provider.
    cn.Provider = "sqloledb"

    ' Set SQLOLEDB connection properties.
    cn.Properties("Data Source").Value = ServerName
    cn.Properties("Initial Catalog").Value = DatabaseName

    ' Decision code for login authorization type:
    ' Windows NT or SQL Server authentication.
    'If optWinNTAuth.Value = True Then
        cn.Properties("Integrated Security").Value = "SSPI"
    'Else
     '   cn.Properties("User ID").Value = UserName
     '   cn.Properties("Password").Value = Password
    'End If

    ' Open the database.
    cn.Open

    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset

    'cn.Open "Provider=sqloledb;Data Source=MyServerName;" & _
    '   "Initial Catalog=northwind;User Id=sa;Password=;"

    'Prepare the user-defined function statement and execute the command.
    cmd.ActiveConnection = cn
    cmd.CommandText = "select * from region"
    Set rs = cmd.Execute
    MsgBox rs!RegionDescription
    rs.Close

    cn.Close
     

    dupa ce se face package-ul cu taskurile respective se pune intr-un job comanda urmatoare

    dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password

    Exista o baza de date de scripturi specifice prin care se poate monitoriza tot

    http://www.microsoft.com/technet/scriptcenter/scripts/default.mspx?mfr=true

    (ar fi o sursa de inspiratie pt scripturile ce ar trebui bagate in taskul ActiveX)


    Gheorghe Ciubuc,SQL Server Influencer, MCP(SQL 2000), MCTS (SQL Server 2005) , OCA(Oracle 9i), Sybase(Brainbench)
Page 2 of 2 (27 items)   < Previous 1 2
View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems