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

• Shweta Jain-392928

  Old Hand

  Points: 396

  More actions

  February 12, 2020 at 6:35 am

  #3725015

  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)

  • This topic was modified 4 years, 2 months ago by  Shweta Jain-392928.

• Thomas Rushton

  SSC-Insane

  Points: 22648

  More actions

  February 12, 2020 at 9:47 am

  #3725052

  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