DECLARE @msg VARCHAR(100);

SET @msg = ' TIME: ' + CONVERT(varchar(8),GETDATE(),114) 
RAISERROR (@msg, 0, 1) WITH NOWAIT

ALTER AUTHORIZATION ON DATABASE::dbname TO sa

/*
DECLARE @strQuery varchar(max) = 'SELECT getDate(),getDate()'	--Query a ejecutar
DECLARE @strHeaders varchar(max) = 'FECHA_1,FECHA_2'			--Headers separados por coma y sin comilla sencilla
DECLARE @strName varchar(100) = 'NOMBRE_ARCHIVO'				--Nombre del archivo sin sufijo y sin extencion
DECLARE @pathFile varchar(200) = 'C:\pruebas\'					--Path donde se guardara
DECLARE @sufijo BIT = 1											-- 0.- NOMBRE_ARCHIVO.csv, 1.- NOMBRE_ARCHIVO_yyyyMMdd_HHmmss.csv
DECLARE @printHeaders BIT = 1									--Imprime los headers en el archivo
DECLARE @fileNameOut varchar(200)								--Variable con el archivo completo generado PATH+NOMBRE(C:\pruebas\NOMBRE_ARCHIVO_20150609_143449.csv)    

EXEC sp_SaveToFileHeadersFile @strQuery ,@strHeaders ,@strName ,@pathFile ,@sufijo ,@printHeaders ,@fileNameOut 
SELECT @fileNameOut
*/

ALTER PROCEDURE [dbo].[sp_SaveToFileHeadersFile]
@strQuery varchar(max),
@strHeaders varchar(max),
@strName varchar(100),
@pathFile varchar(200),
@sufijo BIT,
@printHeaders BIT,
@fileNameOut varchar(200) OUTPUT    

AS 
    DECLARE @bcpString varchar(1000);
    DECLARE @fileDataName varchar(200);
    DECLARE @fileHeadersName varchar(200);
    DECLARE @fileResultName varchar(200);
    DECLARE @fecha varchar(100);
    DECLARE @Existe int
BEGIN

    SET @fecha = '_' + convert(varchar, getdate(), 112) + '_' +  REPLACE(CONVERT(varchar(8),getdate(),114),':','');
    
    IF @sufijo IS NULL OR @sufijo = 0 BEGIN
		SET @fecha = '';
    END

    SELECT @fileHeadersName =  @pathFile + @strName + '_Headers' + @fecha + '.csv';
    SELECT @fileDataName =  @pathFile + @strName + '_Data' + @fecha + '.csv';
    SELECT @fileResultName =  @pathFile + @strName + @fecha + '.csv';
    SET @fileNameOut = @fileResultName;
    
    
    SET @strHeaders = ' SELECT ' + CHAR(39) + @strHeaders + CHAR(39)
    
    EXEC master.dbo.xp_fileexist @pathFile, @Existe OUT;
    
    IF @Existe = 0 BEGIN
		EXEC master.dbo.xp_create_subdir @pathFile;     
    END
    
    IF @printHeaders = 1 BEGIN
		
		SET @bcpString = 'bcp " ' + @strHeaders + ' " queryout "' + @fileHeadersName + '"  -c -t, -T -S ' + @@servername
		print @bcpString
		Exec master..xp_cmdShell @bcpString
				
		SET @bcpString = 'bcp " ' + @strQuery + ' " queryout "' + @fileDataName + '"  -c -t, -T -S ' + @@servername
		print @bcpString
		Exec master..xp_cmdShell @bcpString
	   
		SET @bcpString = 'type "' + @fileHeadersName + '" >> "' + @fileResultName +'"'
		print @bcpString
		Exec master..xp_cmdShell @bcpString
	   
		SET @bcpString = 'type "' + @fileDataName + '" >> "' + @fileResultName +'"'
		print @bcpString
		Exec master..xp_cmdShell @bcpString
		
		SET @bcpString = 'DEL "' + @fileHeadersName + '"'
		print @bcpString
		Exec master..xp_cmdShell @bcpString

		SET @bcpString = 'DEL "' + @fileDataName + '"'
		print @bcpString
		Exec master..xp_cmdShell @bcpString
    END ELSE BEGIN
		SET @bcpString = 'bcp " ' + @strQuery + ' " queryout "' + @fileResultName + '"  -c -t, -T -S ' + @@servername
		print @bcpString
		Exec master..xp_cmdShell @bcpString
    END
        
    RETURN;
	
END

IF OBJECT_ID('tempdb..') IS NOT NULL
	DROP TABLE 

use master
go
declare @kill_spid varchar(20)
-- Find spid of user connection to database test
select @kill_spid= max(spid) from master.dbo.sysprocesses
where dbid in ( select dbid from sysdatabases where name = 'TOYSRUS_CaseManagement' )

select [Connection to Kill] = @kill_spid

-- Kill connection to db test
exec ('kill '+@kill_spid )
go
-- set DB test ofline
alter database TOYSRUS_CaseManagement set offline with rollback immediate
go
-- Bring DB test online in multi user mode
alter database TOYSRUS_CaseManagement set online, multi_user with rollback immediate
go
use TOYSRUS_CaseManagement
select Current_DB = db_name()
go
use master