0

I have a working Stored Procedure that Creates a file based on a SQL QUERY

When I run the SP this is the result that I have

    NULL
    Starting copy...
    SQLState = S1000, NativeError = 0
    Error = [Microsoft][ODBC Driver 13 for SQL Server]Warning: BCP import                 with a format file will convert empty strings in delimited columns to NULL.
    NULL
    21 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 1      Average : (21000.00 rows per sec.)
    NULL

And the file is created successfuly

Now I wanted to change the SP and the only thing I change is a = sign to a > sign

here From this

     where art.bitInactivo=0  and datediff(day,art.Dtmalteracao, getdate())=5

to this

    where art.bitInactivo=0  and datediff(day,art.Dtmalteracao, getdate())>5    

or even with this

    where art.bitInactivo=0  and datediff(day,art.Dtmalteracao, getdate())<5  

And now it gives me NULL in the output

I came to the conclusion that the less than and greater than sign must be doing something on the BCP command itself.

But I googled And I see noone complaining with this, only escaping single quotes, nothing more.

full line here. The sign is near the end on the where clause

    SET @bcpCommand = 'bcp "select ''192.168.1.60'',1,art.strCodigo,left(replace(replace(replace(art.strDescricao,''\"'',''''),char(147),''''),char(148),''''),40),replace(art.CA_NomeCient,char(0),''''),''0,00'',''0,00'',''0,00'',''0,00'',''0,00'',art.strAbrevMedVnd,''1'',artfam.strCodFamilia,art.strCodBarras,null,art.ca_exportabalanca,0,0,0,0,0,art.ca_etiquetadef,art.CA_numeradorbalanca,0,0,0,''0,000'',''0,00'',replace(isnull(ca_qtddef,0.00),''.'','',''),''0,000'',''0,000'',''0,000'',''0,00'' from Emp_002.dbo.Tbl_Gce_Artigos art with(NOLOCK) left outer JOIN Emp_002.dbo.Tbl_Gce_ArtigosFamilias artfam with(NOLOCK) on artfam.strCodArtigo=art.strCodigo and artfam.strCodTpNivel=''1'' where art.bitInactivo=0  and datediff(day,art.Dtmalteracao, getdate())>5  " queryout '

If you need the full SP here it is

    USE [Emp_002]
    GO
    /****** Object:  StoredProcedure [dbo].[usr_geraficheiro]    Script Date: 14/05/2019 10:08:45 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[usr_geraficheiro]
    AS
    declare @prevAdvancedOptions int
    declare @prevXpCmdshell int

    select @prevAdvancedOptions = cast(value_in_use as int) from sys.configurations where name = 'show advanced options'
    select @prevXpCmdshell = cast(value_in_use as int) from sys.configurations where name = 'xp_cmdshell'

    if (@prevAdvancedOptions = 0)
    begin
        exec sp_configure 'show advanced options', 1
        reconfigure
    end

    if (@prevXpCmdshell = 0)
    begin
        exec sp_configure 'xp_cmdshell', 1
        reconfigure
    end

    /* do work */
    DECLARE @OutputFile NVARCHAR(100) ,    @FilePath NVARCHAR(100) ,    @bcpCommand NVARCHAR(1500)

    SET @bcpCommand = 'bcp "select ''192.168.1.60'',1,art.strCodigo,left(replace(replace(replace(art.strDescricao,''\"'',''''),char(147),''''),char(148),''''),40),replace(art.CA_NomeCient,char(0),''''),''0,00'',''0,00'',''0,00'',''0,00'',''0,        00'',art.strAbrevMedVnd,''1'',artfam.strCodFamilia,art.strCodBarras,null,art.ca_exportabalanca,0,0,0,0,0,art.ca_etiquetadef,art.CA_numeradorbalanca,0,0,0,''0,000'',''0,00'',replace(isnull(ca_qtddef,0.00),''.'','',''),''0,000'',''0,000'',''0        ,000'',''0,00'' from Emp_002.dbo.Tbl_Gce_Artigos art with(NOLOCK) left outer JOIN Emp_002.dbo.Tbl_Gce_ArtigosFamilias artfam with(NOLOCK) on artfam.strCodArtigo=art.strCodigo and artfam.strCodTpNivel=''1'' where art.bitInactivo=0  and         datediff(day,art.Dtmalteracao, getdate())>5  " queryout '
    SET @FilePath = 'C:\compussql\ExportBalanca\'
    SET @OutputFile = 'artigos.txt'
    SET @bcpCommand = @bcpCommand + @FilePath + @OutputFile + ' -c -C 65001 -t; -T -S '+ @@servername
    exec master..xp_cmdshell @bcpCommand

    SET @bcpCommand = 'bcp "Select ''D1'' " queryout '
    SET @FilePath = 'C:\compussql\ExportBalanca\'
    SET @OutputFile = 'BMLink.ctl'
    SET @bcpCommand = @bcpCommand + @FilePath + @OutputFile + ' -c -C 65001 -t; -T -S '+ @@servername
    exec master..xp_cmdshell @bcpCommand

    SET @bcpCommand = 'bcp "Select ''192.168.1.60'', 1, art.CA_numeradorbalanca, null, art.ca_zonacap, null from Emp_002.dbo.Tbl_Gce_Artigos art WITH(NOLOCK) where art.bitInactivo=0 and datediff(DAY,dtmAlteracao, getdate())<5" queryout '
    SET @FilePath = 'C:\compussql\ExportBalanca\'
    SET @OutputFile = 'ingredientes.txt'
    SET @bcpCommand = @bcpCommand + @FilePath + @OutputFile + ' -c -C 65001 -t; -T -S '+ @@servername
    exec master..xp_cmdshell @bcpCommand





    if (@prevXpCmdshell = 0)
    begin
        exec sp_configure 'xp_cmdshell', 0
        reconfigure
    end

    if (@prevAdvancedOptions = 0)
    begin
        exec sp_configure 'show advanced options', 0
        reconfigure
    end
Alexandre Calvario
  • 143
  • 1
  • 1
  • 11
  • Do the queries yield any results on their own (eg when running them separately in SSMS) with the > and < instead of =? – TT. May 14 '19 at 11:09
  • Yes I tested that. The querys run with no issues, I also tested the commands GETDATE() and datediff on their own just to make sure those functions worked in this. – Alexandre Calvario May 14 '19 at 11:39
  • Perhaps they're being interpreted as batch redirection (input, output), though AFAICT that should not happen. Maybe you could test writing the query in a stored procedure, and execute the stored procedure from the bcp command? – TT. May 14 '19 at 11:43
  • print the contents of the variable (@bcpCommand) you pass to xp_cmdshell. Copy and paste the value of the variable into a DOS command window. What is the result? – jamie Jun 05 '19 at 20:21

0 Answers0