Another way to save files without xp_cmdshell SqlServer 2008

Question

Another way to save files without xp_cmdshell SqlServer 2008

Navigation

#1 by (1 votes)

0

Good morning classmates, I need your help, I have a storedprocedure where a select is executed and the information obtained from that query is printed and saved as a .txt file in a specific route by means of the command xp_cmdshell What I would like to do is to perform the same action of saving the information in a file but avoiding the xp_cmdshell command since I do not have to use security issues, I leave parts of the script:

SET @output ='\SERVERPRO\PaymentsReport\REPORTE_TRX__MYREPORT.txt'

SET @cmd = 'SELECT Pagos FROM dbo.Pagos'

SET @cmd = 'sqlcmd -S MYSERVER -E -Q ' + '"' + @cmd + '"' + ' -o' + @output + ''  
EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

If you can give me an example, I would thank you in advance. Thanks and regards.

sql sql-server

asked by BDOM 02.08.2018 в 18:55

source

1 answer

Fill comboBox with result of lineEdit Select a single "checkbox" and then do a calculation

score 1 · Accepted Answer

The only thing that occurs to me that you can try to do from an SP only with TSQL is to take advantage of access to the Ole Automation Procedures (provided that the server has configured that possibility) and instantiate a Scripting.FileSystemObject object to take advantage of the% function WriteLine .

In the first place, it would be necessary to verify and eventually reconfigure the server to allow this type of procedure:

-- Para ver la configuración (1=On)
EXEC sp_configure 'Ole Automation Procedures';  

-- Si nos devuelve un error es por que no está habilitado el acceso a las
-- configuraciones avanzadas. Para habilitarlo:
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

-- Para configurar los Ole Automation Procedures
EXEC sp_configure 'Ole Automation Procedures', 1;

Having correctly configured the server, the following is a conceptual example of the solution: we want to export the result of a simple query: SELECT id,name FROM SYSOBJECTS to a file, what we will do is:

Use a cursor that points to a query
The query will return a single column that will correspond to each line of the file, you have to convert the missing data to VARCHAR and format everything as we want it to go to the file.
We move the cursor and invoke the WriteLine method to write each line

.

DECLARE @FS         int 
DECLARE @OLEResult  int 
DECLARE @FileID     int 
DECLARE @FileName   VARCHAR(255)
DECLARE @Registro   VARCHAR(MAX)

-- Es compatible con UNC ej \server\carpeta\archivo.txt
SELECT  @Filename = 'c:\Salida.txt'

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT 
EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, True 

DECLARE Registros CURSOR LOCAL FORWARD_ONLY
FOR SELECT CONVERT(VARCHAR,id) + ',' + name
    FROM SYSOBJECTS

OPEN Registros
FETCH NEXT FROM Registros 
INTO @Registro

WHILE @@FETCH_STATUS = 0
BEGIN  
    EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Registro
    FETCH NEXT FROM Registros 
    INTO @Registro
END 

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

CLOSE Registros
DEALLOCATE Registros

If you want to avoid using the CURSOR, you should save everything in a single variable and save this:

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT 
EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, True 

SELECT @Registro = ''   
SELECT @Registro = @Registro + CONVERT(VARCHAR,id) + ',' + name + CHAR(10) + CHAR(13)
    FROM SYSOBJECTS

EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Registro
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

Important:

Keep in mind that this is a safety hole a little less dangerous than the one of xp_cmdshell , but it is still a gap, so it is possible that if you are not allowed to use one, do not let yourself be used either the other.