martes, 29 de noviembre de 2011

[Solucionado] Crear carpetas utilizando SQL Server (TSQL) comando xp_cmdshell

En SQL Server (cualquier versión), es posible programas trabajos (Jobs) que se ejecuten a una hora determinada, por ejemplo si queremos hacer algún respaldo de una base de datos. Si queremos ser muy organizados podemos crear una carpeta con la fecha y guardar dentro todos los respaldos.

Si tratamos de hacer un respaldo dentro de una carpeta que no existe aparecerá el siguiente error en SQL:

Date: 11/28/2011 11:00:00 PM
Log: Job History (prueba)
Step ID: 1
Server: NOMBRE_SERVIDOR\INSTANCIASQL
Job Name: prueba_yolosolucionoasi.blogspot.com
Step Name: prueba
Duration: 00:00:00
Sql Severity: 16
Sql Message ID: 3013
Operator Emailed
Retries Attempted: 0
Message: Executed as user: NT AUTHORITY\SYSTEM. Cannot open backup device 'C:\Respaldos\prueba\basededatos_20111128'. Operating system error 3(The system cannot find the path specified.). [SQLSTATE 42000] (Error 3201)  BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

Para solucionar esto debemos crear un Trabajo (Job), ejecutando un comando de DOS para crear la carpeta con el nombre deseado, por ejemplo:


declare @MD varchar(100), @wk_no varchar(100)
set @wk_no = 'C:\Respaldos\' + Convert(VarChar(4),DatePart(Year,GetDate()))+Right('0'+Convert(VarChar(2),DatePart(Month,GetDate())),2)+Right('0'+Convert(VarChar(2),DatePart(Day,GetDate( ))),2)
SET @MD = ' mkdir ' + @wk_no
EXEC xp_cmdshell @MD, no_output


Esto crearía una carpeta con la fecha de la siguiente forma: c:\respaldos\yyyymmdd

Luego de crear el trabajo antes de comenzar a ejecutarlo se debe autorizar al servidor para que pueda ejecutar comandos 'xp_cmdshell', de lo contrario aparecería el siguiente error:
Date: 11/29/2011 3:14:19 PM
Log:  Job History (Respaldos)
Step ID: 1
Server: NOMBRE_SERVIDOR\INSTANCIASQL
Job Name: Respaldos
Step Name: Crear carpeta para respaldo
Duration: 00:00:00
Sql Severity: 16
Sql Message ID: 15281
Operator Emailed
Retries Attempted: 0
Message: Executed as user: NT AUTHORITY\SYSTEM. SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online. [SQLSTATE 42000] (Error 15281).  The step failed.

Para permitir ejecutar xp_shell:
EXEC sp_configure 'xp_cmdshell', 1



Más información:
[Solucionado] Crear un Trabajo de Respaldo (backup Job) en SQL Server y guarde en una carpeta con el nombre de la fecha del respaldo
http://yolosolucionoasi.blogspot.com/2011/11/solucionado-crear-un-trabajo-de.html

1 comentario:

  1. Mira lo tengo así, pero no sé por qué no me agarra el BCP me tira error al procesarlo y también al leer en la linea del bcpcommand

    DECLARE @FileName varchar(100),@bcpCommand varchar(2000)
    SET @FileName = REPLACE ('C:\Reportes dfarias\Reporte '+right(CONVERT(CHAR(10),DATEADD(MONTH,-1,GETDATE()),105),9)+'\Entrada '+ CONVERT(char(10),GETDATE(),105)+'.csv','/','-')
    SET @bcpCommand= 'bcp "SELECT ''Area Funcional'',''Apellido Paterno'',''Apellido Materno'',''Nombre'',''Hora ingreso'',''Fecha'' FROM tempdb.dbo.tablatemporal UNION ALL SELECT * FROM tempdb.dbo.Hora_entrada" queryout "'
    SET @bcpCommand = ' mkdir ' + @bcpCommand + @FileName-- + '" -S ***\SQLEXPRESS -c -T /t ";"'

    ResponderEliminar