bcp doesnot run properly and create the csv file at that location

  • SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET NOCOUNT ON;

    declare @tempNum int,@debtorID varchar(100),@ExpectedDeliveryDate varchar(100),@ItemNo varchar(100),@ProductDesc varchar(600),@ItemQty varchar(100)

    , @csvFileName varchar(100),@sql varchar(7000),@ShopifyOrder varchar(50), @processedFolderLocation varchar(6000),@Source varchar(1000),

    @cmdFromDriveMapping varchar(1000),@ItemPrice varchar(40), @LineNote varchar(max),@tempTable varchar(350),@SQLQuery nvarchar(max),

    @XMLFileName varchar(300),@SQLQurey2 varchar(max), @ItemDueDate varchar(20),@ExpectedDeliveryDate1 varchar(100)

    set @tempTable = '##csvtemp' + replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')

    --IF OBJECT_ID('tempdb..##csvtemp'+) IS NOT NULL

    -- BEGIN

    -- DROP TABLE ##csvtemp

    -- END

    set @XMLFileName = '100008_RST100201007.xml'

    set @sqlquery = 'create table ' + @tempTable +'(id int identity(1,1) primary key,col1 varchar(100) null,col2 varchar(100) null,

    col3 varchar(100) null,col4 varchar(100) null,

    col5 varchar(100) null,col6 varchar(100) null,

    col7 varchar(100) null,col8 varchar(100) null,

    col9 varchar(100) null,col10 varchar(100) null,

    col11 varchar(100) null,col12 varchar(100) null,

    col13 varchar(100) null,col14 varchar(100) null,

    col15 varchar(100) null,col16 varchar(100) null,

    col17 varchar(100) null,col18 varchar(100)null,

    col19 varchar(100) null,col20 varchar(100) null,

    col21 varchar(100) null,col22 varchar(100) null)'

    print @sqlquery

    exec (@SQLQuery)

    set @tempNum = 0 ;

    select @debtorID = CustomerNumber,@Source = Source,@ExpectedDeliveryDate = ExpectedDeliveryDate,@ShopifyOrder = ShopifyOrderNumber from ValidateTestxmlEDIHeader where xmlFileName = @XMLFileName

    if(lower(@source)='vending')

    begin

    set @sqlquery =

    'insert into '+@tempTable +

    ' select

    ''H'',''P'',nullif(CustomerNumber,''''),nullif(CustomerOrderNumber,''''),nullif(convert(varchar(20),LoadedDateTime,103),''''),

    nullif(OrderContactEmail,''''),nullif(ExpectedDeliveryDate,''''),nullif(CustomerOrderReference,''''),

    nullif(DeliveryAddressLine1,''''),

    nullif(DeliveryAddressLine2,''''),nullif(DeliverySuburb,''''),

    Case When lower(DeliveryState) = ''queensland'' then ''QLD''

    WHEN lower(DeliveryState) = ''western australia'' then ''WA''

    WHEN lower(DeliveryState) = ''victoria'' then ''VIC''

    WHEN lower(DeliveryState) = ''new south wales'' then ''NSW''

    WHEN lower(DeliveryState) = ''tasmania'' then ''TAS''

    WHEN lower(DeliveryState) = ''south australia'' then ''SA''

    WHEN lower(DeliveryState) = ''northern territory'' then ''NT''

    WHEN lower(DeliveryState) = ''australian capital territory'' then ''ACT''

    else nullif(UPPER(DeliveryState),'''')

    END as col12,

    nullif(DeliveryPostCode,'''') as col13,nullif(CustomerOrderNumber,'''') as col14,

    nullif(OrderContactName,'''') as col15,nullif(ExpectedDeliveryDate,'''') as col16,null as col17,null as col18,null as col19,

    nullif(ID,'''') as col20,nullif(WarehouseNo,'''') as col21,nullif(OrderSalesman,'''') as col22

    from ValidateTestxmlEDIHeader where xmlFileName ='''+ @XMLFileName +''''

    print @sqlquery

    exec (@SQLQuery)

    end

    if(lower(@source)='shopify')

    begin

    set @sqlquery =

    'insert into '+@tempTable +

    ' select

    ''H'',''P'',nullif(DebtorId,''''),nullif(CustomerOrderNumber,''''),nullif(convert(varchar(20),LoadedDateTime,103),''''),

    nullif(OrderContactEmail,''''),nullif(ExpectedDeliveryDate,''''),nullif(CustomerOrderReference,''''),

    nullif(DeliveryAddressLine1,''''),

    nullif(DeliveryAddressLine2,''''),nullif(DeliverySuburb,''''),

    Case When lower(DeliveryState) = ''queensland'' then ''QLD''

    WHEN lower(DeliveryState) = ''western australia'' then ''WA''

    WHEN lower(DeliveryState) = ''victoria'' then ''VIC''

    WHEN lower(DeliveryState) = ''new south wales'' then ''NSW''

    WHEN lower(DeliveryState) = ''tasmania'' then ''TAS''

    WHEN lower(DeliveryState) = ''south australia'' then ''SA''

    WHEN lower(DeliveryState) = ''northern territory'' then ''NT''

    WHEN lower(DeliveryState) = ''australian capital territory'' then ''ACT''

    else nullif(UPPER(DeliveryState),'''')

    END as col12,

    nullif(DeliveryPostCode,''''),nullif(ShopifyOrderNumber,''''),

    nullif(OrderContactName,''''),nullif(ExpectedDeliveryDate,''''),null,null,null,nullif(ID,''''),nullif(WarehouseNo,''''),

    nullif(OrderSalesman,'''')

    from ValidateTestxmlEDIHeader where xmlFileName ='''+ @XMLFileName +''''

    print @sqlquery

    exec (@SQLQuery)

    end

    if(lower(@source)='edi')

    begin

    set @sqlquery =

    'insert into '+@tempTable +

    ' select

    ''H'',''P'',nullif(DebtorId,''''),nullif(CustomerOrderNumber,''''),nullif(convert(varchar(20),LoadedDateTime,103),''''),

    nullif(OrderContactEmail,''''),nullif(ExpectedDeliveryDate,''''),nullif(CustomerOrderReference,''''),

    nullif(DeliveryAddressLine1,''''),

    nullif(DeliveryAddressLine2,''''),nullif(DeliverySuburb,''''),

    Case When lower(DeliveryState) = ''queensland'' then ''QLD''

    WHEN lower(DeliveryState) = ''western australia'' then ''WA''

    WHEN lower(DeliveryState) = ''victoria'' then ''VIC''

    WHEN lower(DeliveryState) = ''new south wales'' then ''NSW''

    WHEN lower(DeliveryState) = ''tasmania'' then ''TAS''

    WHEN lower(DeliveryState) = ''south australia'' then ''SA''

    WHEN lower(DeliveryState) = ''northern territory'' then ''NT''

    WHEN lower(DeliveryState) = ''australian capital territory'' then ''ACT''

    else nullif(UPPER(DeliveryState),'''')

    END as col12,

    nullif(DeliveryPostCode,''''),nullif(ShopifyOrderNumber,''''),

    nullif(OrderContactName,''''),nullif(ExpectedDeliveryDate,''''),null,null,null,nullif(ID,''''),nullif(WarehouseNo,''''),

    nullif(OrderSalesman,'''')

    from ValidateTestxmlEDIHeader where xmlFileName ='''+ @XMLFileName +''''

    print @sqlquery

    exec (@SQLQuery)

    end

    DECLARE curReadOrderLine CURSOR

    FOR

    SELECT LineNote,ItemNo,ProductDesc,ItemQty,ItemPrice,ItemDueDate

    FROM ValidateTestxmlEDILines where xmlFileName = @XMLFileName

    OPEN curReadOrderLine

    FETCH NEXT FROM curReadOrderLine INTO @LineNote,@ItemNo,@ProductDesc,@ItemQty,@ItemPrice,@ItemDueDate

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @ExpectedDeliveryDate1 = isnull(@ExpectedDeliveryDate,'')

    print @ExpectedDeliveryDate1

    set @tempNum = @tempNum + 10

    set @SQLQurey2 =

    'insert into '+@tempTable + ' values(

    ''I'',

    nullif('''+cast(@debtorID as varchar)+''',''''),'''

    +cast(@tempNum as varchar)+''','

    + 'nullif('''+isnull(@ItemNo,'') +''',''''),

    case

    when lower('''+cast(@Source as varchar)+''') = ''vending'' then nullif('''+isnull(@LineNote ,'''')+''','''')

    else nullif('''+isnull(@ProductDesc,'')+''','''')

    end,

    nullif('''+isnull(@ItemQty,'')+''',''''),'+

    'nullif('''+isnull(@ExpectedDeliveryDate,'')+''',''''),'+

    'nullif('''+isnull(@ItemPrice,'')+''',''''),'+

    'null,

    null,

    null,

    null,

    null,

    null,

    null,

    null,

    null,

    null,

    null,

    null,

    null,

    null)'

    print @SQLQurey2

    exec (@SQLQurey2)

    if(lower(@source)<>'vending')

    begin

    set @tempNum = @tempNum + 10

    set @sqlquery =

    'insert into '+@tempTable +

    ' select ''T'','''+@LineNote+''',cast('+@tempNum+' as varchar),null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null from ValidateTestxmlEDIHeader

    where xmlFileName ='''+ @XMLFileName +''''

    exec (@SQLQUERY)

    end

    FETCH NEXT FROM curReadOrderLine INTO @LineNote,@ItemNo,@ProductDesc,@ItemQty,@ItemPrice,@ItemDueDate

    END

    CLOSE curReadOrderLine

    DEALLOCATE curReadOrderLine

    EXEC xp_cmdshell 'net use Y: /delete /Y'

    select @processedFolderLocation = AttributeValue from [dbo].[sysAttribute] where [AttributeCode] = 'POMicronetCSVLocation';

    select @processedFolderLocation

    set @cmdFromDriveMapping = 'net use Y: ' + @processedFolderLocation + ' /user:atom\sql_backup $upplyCha1n /persistent:no'

    EXEC sp_configure 'xp_cmdshell', 1

    EXEC xp_cmdshell @cmdFromDriveMapping

    set @csvFileName = left(@XMLFileName, charindex('.', @XMLFileName) - 1)+'.csv'

    select @csvFileName

    set @sqlquery= 'select * from '+@tempTable

    exec (@SQLQuery)

    set @sql = 'bcp "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22 FROM '+@tempTable+'" queryout '+@processedFolderLocation+'/"'+@csvFileName+'" -c -d ' + DB_NAME() + ' -t, -S '+ @@servername+ ' -U ****** -P *******-T'

    print @sql

    --set @sql = 'bcp "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22 FROM ##csvtemp" queryout Y:/"'+@csvFileName+'" -c -d ' + DB_NAME() + ' -t, -S '+ @@servername+ ' -U ******  -P ******* -T'

    exec master..xp_cmdshell @sql

    EXEC xp_cmdshell 'net use Y: /delete /Y'

    set @sqlquery= 'select * from '+@tempTable

    exec (@SQLQuery)

    --drop table ##csvtemp;

    set @sqlquery= 'drop table '+@tempTable

    exec (@SQLQuery)

  • Where is the BCP file created?

    I note that you are running the sp_configure xpcmdshell statement after you've attempted to run xp_cmdshell for the first time; I also note that your paths in teh BCP statement are built using / rather than

     

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply