Generate .TXT file with Stored Procedure

Question

Generate .TXT file with Stored Procedure

Navigation

#1 by (1 votes)

2

I have the following SP and I want the results saved in a .TXT file for I also have a JOB which executes the Stored Procedure, which I configured from the SQL Server Management Studio.

This is an example of how a result of a query is sent to a TXT, and from this example, implement it in a Stored Procedure.

DECLARE @archivo varchar(50),

    @comando varchar(2000)


SET @archivo = 'E:\archivo.txt'
SET @comando = 'bcp "SELECT * FROM bdd.dbo.tblEjemplo" queryout "'
SET @comando = @comando + @archivo + '" -U sa -P 1234567 -c'


EXEC master..xp_cmdshell @comando

''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''

Implementation in the Stored Procedure

CREATE PROCEDURE consulta_hoy
@Hoy DATE

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT @Hoy = GETDATE()

-- Insert statements for procedure here
DECLARE @archivo varchar(50),@comando varchar(2000)
SET @archivo = 'E:\archivo.txt'

SET @comando = 'bcp"
SELECT c.CodeCompany, 
    @Hoy AS fechaActual,
    a.idClave, 
    c.nombre +' '+c.apellidos AS Nombre, --Me marca un error por el uso de +' '+ 
    d.Descripcion AS Depto,
    t.HorasAlDia AS HorasDia,
    convert(time(0), fechaEntrada) AS Entrada,
    convert(time(0), fechaSalida) AS Salida,

DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24 AS horasAlDia

FROM tblasistencia a

    JOIN tblpersonal c ON a.idClave=c.idClave
    JOIN tblTurno    t ON t.idTurno=c.fkTurno
    JOIN tblDepto    d ON c.fkDepto=d.idDepto

WHERE fechaEntrada BETWEEN @Hoy AND DATEADD(DAY, 1, @Hoy) and fkStatus !=1 " queryout "'
SET @comando = @comando + @archivo + '" -U sa -P 123457 -c'

EXEC master..xp_cmdshell @comando

END

Execution SP:

exec consulta_hoy '';

When I command to execute the Stored Procedure, it shows the following:

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors]            [-f formatfile]          [-e errfile]
[-F firstrow]             [-L lastrow]             [-b batchsize]
[-n native type]          [-c character type]      [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier]  [-t field terminator]    [-r row terminator]
[-i inputfile]            [-o outfile]             [-a packetsize]
[-S server name]          [-U username]            [-P password]
[-T trusted connection]   [-v version]             [-R regional enable]
[-k keep null values]     [-E keep identity values]
[-h "load hints"]         [-x generate xml format file]
[-d database name]
NULL

Send to print the variable @comando

Show me:

bcp"
SELECT c.CodeCompany, 
    @Hoy AS fechaActual,
    a.idClave, 
    c.nombre  c.apellidos AS Nombre, 
    d.Descripcion AS Depto,
    t.HorasAlDia AS HorasDia,
    convert(time(0), fechaEntrada) AS Entrada,
    convert(time(0), fechaSalida) AS Salida,

DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24 AS horasAlDia

FROM tblasistencia a

    JOIN tblpersonal c ON a.idClave=c.idClave
    JOIN tblTurno    t ON t.idTurno=c.fkTurno
    JOIN tblDepto    d ON c.fkDepto=d.idDepto

WHERE fechaEntrada BETWEEN @Hoy AND DATEADD(DAY, 1, @Hoy) and fkStatus !=1 " queryout "E:\archivo.txt" -U sa -P 123457 -c

sql sql-server sql-server-2008

asked by Noel L 09.01.2018 в 21:39

source

1 answer

Help with data type in MySQL Access objects created in another class, in what way?

score 1 · Answer 1

The problem is this:

SET @comando = 'bcp"
SELECT c.CodeCompany,

You are adding line breaks to the statements that you are going to execute, they break the call. I rewrote your code a bit:

Some corrections and indications

DECLARE @comando VARCHAR(2000)
DECLARE @query   VARCHAR(2000)
DECLARE @archivo VARCHAR(50)

SET @archivo = 'E:\archivo.txt'
SET @query   = 'SELECT c.CodeCompany, @Hoy AS fechaActual, a.idClave, C.nombre +'' ''+c.apellidos AS Nombre, d.Descripcion AS Depto, t.HorasAlDia AS HorasDia, convert(time(0), fechaEntrada) AS Entrada, convert(time(0), fechaSalida) AS Salida, DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24 AS horasAlDia FROM tblasistencia a JOIN tblpersonal c ON a.idClave=c.idClave JOIN tblTurno t ON t.idTurno=c.fkTurno JOIN tblDepto d ON c.fkDepto=d.idDepto WHERE fechaEntrada BETWEEN @Hoy AND DATEADD(DAY, 1, @Hoy) and fkStatus !=1'

SET @comando = 'bcp queryout "'+ REPLACE(@query + '" ' + @archivo + ' -U sa -P 123457 -c'

If you have to indicate a single quote in a dynamic string you should write it as follows ''
Remember that the user that starts the SQL Server service must have write permissions in order to save the file.
Check the jumps and / or tabs.

Another option is to remove the line breaks from the statement, as follows:

SET @query = REPLACE(REPLACE(@query, CHAR(10), ''),CHAR(13), '')

Double REPLACE nested to remove characters 13 and 10, the usual Windows line break.