T-SQL

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

ggciubuc — Thu, 21 Jun 2007 20:34:25 GMT

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)

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

Alex — Thu, 21 Jun 2007 13:06:54 GMT

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

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

Alex — Thu, 21 Jun 2007 11:44:44 GMT

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.

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

Alex — Thu, 21 Jun 2007 11:17:22 GMT

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.

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

Alex — Thu, 21 Jun 2007 10:52:48 GMT

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

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

ggciubuc — Thu, 21 Jun 2007 10:01:26 GMT

Acum chiar ca trebuie design de package DTS !

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

Alex — Thu, 21 Jun 2007 08:19:10 GMT

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.

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

Alex — Thu, 07 Jun 2007 12:12:22 GMT

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

alex.

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

rsocol — Thu, 07 Jun 2007 11:58:43 GMT

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

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

Alex — Thu, 07 Jun 2007 11:31:08 GMT

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

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

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

rsocol — Thu, 07 Jun 2007 10:51:01 GMT

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

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

Alex — Thu, 07 Jun 2007 08:22:28 GMT

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

set @param=1

return @param
GO

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

Alex — Thu, 07 Jun 2007 08:05:33 GMT

am reusit sa arhivez fisierul....singura problema e ca nu-mi pune extensia .RAR.....il arhiveaza fara extensie si nu pot sa-l dezarhivez....dar oricum am inaintat ceva :)....asta e procedura corecta...cat de cat :)

create proc _BackUp_DBName_WinRAR_Test
@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:\' + @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:\'+ @DBName + '\' + @DBName + '_'+ @now

set @CaleBackup=@CaleBackup0+'.BAK'
set @LogicalBackup= @DBName + '_'+ @now

SET @PackerFileName = '"C:\Program Files\WinRAR\WinRAR.exe"'
SET @PackOptions = 'a -df -ep -s -d4096 -rr -m5 -ri1'
SET @LocalBackupDrive = 'D:'

SET @LocalBackUPPath = 'D:\' + @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

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

Alex — Thu, 07 Jun 2007 06:37:44 GMT

da...intradevar chiar nu sunt sigur de parametrii pe care-i dau....dar nici nu inteleg cum as putea sa-i verific.....parametrii pe care-i dau acum i-am luat si eu dintr-o alta procedura care facea ceva asemanator......nu am inteles principiul deci nici nu stiu care sunt cei corecti :(....o iau si eu pe incercate....daca are cineva vreo idee sunt mai mult decat fericit sa o primesc.....

multumesc

P.S. daca vrea cineva sa incerce sa-i dea de cap...mai jos am pus procedura asa cum arata ea acum......

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\TEST\'+@DBName + '_'+ @now
set @CaleBackup=@CaleBackup0+'.BAK'
set @LogicalBackup= @DBName + '_'+ @now

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

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

set @param=1

return @param

asa arata acel @tmpSQL care se formeaza si care se pare ca nu e ok.....

'"C:\Program Files\WinRAR\WinRAR.exe" a -s D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TEST_7062007_94649.BAK D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\TEST_7062007_94649'

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

MrSmersh — Wed, 06 Jun 2007 15:37:04 GMT

Il recunoaste pe rar dar ii dai un parametru la winrar care nu e bun ...
Incearca-le inainte intr-un bat sau ceva, sau apeleaza direct bat cu parametru calea...
A atentie ca bat sa se inchida ca daca nu ramine in memorie pina la reset, cred ca echo off si @cls la sfirsit daca mergi pe calea bat caut si iti zic sigur.