good morning, I am having problems when exporting the results of a query to a TXTSQL
file , the problem for which I cannot export it is because it generates errors due to the casts that are within the query.
This is the query:
SELECT CAST(TSMS_exportar AS NVARCHAR(100)) + '/' +
CLI_apenom + '/' +
CAST(SOL_titulo AS NVARCHAR(100)) + '/' +
REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(100), CAST(PLAN_cuota AS MONEY), 1), ',', '|' ), '.', ', '), '|','.' ) + '/' +
REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(100), CAST(PLAN_nominal AS MONEY), 1), ',', '|' ), '.', ',' ), '|' ,'.' )
FROM Mensaje_Electronico_Detalle
INNER JOIN Mensaje_Electronico_Encabezado
ON Mensaje_Electronico_Detalle.MEE_id = Mensaje_Electronico_Encabezado.MEE_Id
INNER JOIN Solicitud
ON Mensaje_Electronico_Encabezado.SOL_titulo = Solicitud.ANTITSOL
INNER JOIN TempSMS
ON TempSMS.MED_id = Mensaje_Electronico_Detalle.MED_id
WHERE Mensaje_Electronico_Detalle .PLAN_cuota IS NOT NULL
AND Solicitud.ANIDCOB IN ('00010', '00086' ,'00093', '00097')
AND TSMS_exportar IS NOT NULL
AND PER_id IN ((SELECT MAX(IdPeriodo) FROM Periodos WHERE IdProcesoAdmin = 6))
AND PROCESOADMIN_id = 6
And to export the result I do the following:
SET @comando ='bcp "toda la consulta anterior" queryout ' etc
The problem is when using this: '/'
and the signs inside theREPLACE
How is it done correctly? A fact to clarify is that the query within the bcp is not staggered as I show at the beginning, it is all in a single row.
It would be best to convert the query to a view or stored procedure to make the bcp statement as simple as possible due to the limitations of the statement.
But ideally, you should also use the argument to indicate the column separator instead of including it in your query.
You can see more options for row and column terminators here