BCP Error !! Trying to copy Data in Excel format but errors !!

• nitinuniyal

  SSC Eights!

  Points: 997

  More actions

  September 11, 2012 at 12:49 am

  #261715

  Hi Experts,

  I am trying to fetch the data to excel format and getting the below errors. I think my SQL is working fine but when i am trying to use BCP i am getting the below given errors. Could you please let me know how can i solve these issue and make my query perfect.

  I am using the same query in another server and its working fine, so i am just wondering as well that these erros can be related to some SQL settings.

  (20 row(s) affected)

  Msg 102, Level 15, State 1, Line 1

  Incorrect syntax near ','.

  Msg 102, Level 15, State 1, Line 1

  Incorrect syntax near ')'.

  Msg 102, Level 15, State 1, Line 1

  Incorrect syntax near ')'.

  (6 row(s) affected)

  Msg 3701, Level 11, State 5, Procedure spExportData_n, Line 74

  Cannot drop the table '##TempExportData2', because it does not exist or you do not have permission.

  (1 row(s) affected)

  I am using the following code in SQL 2005.

  USE [assyst]

  GO

  /****** Object: StoredProcedure [dbo].[spExportData_n] Script Date: 09/10/2012 15:42:42 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  ALTER proc [dbo].[spExportData_n]

  (

  @dbName varchar(100) = 'assyst',

  @sql varchar(8000) = 'SELECT incident.incident_ref, incident.inc_status AS Status, incident.date_logged AS [Logged_Date], incident.aff_usr_name AS [Affected_User_Name],

  incident.inc_desc1 AS Description, assyst_usr.assyst_usr_n AS [Logged_by], act_type.act_type_n AS [Last_Action],

  act_reg.date_actioned AS [Date_Actioned], act_reg.act_rmk AS Remark, assyst_usr_1.assyst_usr_n AS [Last_Action_By],

  inc_data.last_action_svd AS [Last_Action_Dept.], serv_dept.serv_dept_sc AS [Under_Dept.]

  FROM inc_data INNER JOIN

  incident ON inc_data.incident_id = incident.incident_id INNER JOIN

  assyst_usr ON incident.assyst_usr_id = assyst_usr.assyst_usr_id INNER JOIN

  act_reg ON incident.incident_id = act_reg.incident_id INNER JOIN

  act_type ON act_reg.act_type_id = act_type.act_type_id INNER JOIN

  sectn_dept ON incident.sectn_dept_id = sectn_dept.sectn_dept_id INNER JOIN

  assyst_usr AS assyst_usr_1 ON act_reg.assyst_usr_id = assyst_usr_1.assyst_usr_id INNER JOIN

  serv_dept ON incident.ass_svd_id = serv_dept.serv_dept_id

  where incident_ref = 1

  ORDER BY incident.date_logged',

  @fullFileName varchar(100) = 'P:\CallCenter_Audit_Report.xls'

  )

  as

  if @sql = '' or @fullFileName = ''

  begin

  select 0 as ReturnValue -- failure

  return

  end

  -- if DB isn't passed in set it to master

  select @dbName = 'use ' + @dbName + ';'

  if object_id('##TempExportData') is not null

  drop table ##TempExportData

  if object_id('##TempExportData2') is not null

  drop table ##TempExportData2

  -- insert data into a global temp table

  declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)

  select @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' +

  substring(@sql, charindex('from', @sql)-1, len(@sql))

  exec(@dbName + @tempSQL)

  if @@error > 0

  begin

  select 0 as ReturnValue -- failure

  return

  end

  -- build 2 lists

  -- 1. column names

  -- 2. columns converted to nvarchar

  SELECT @columnNames = COALESCE( @columnNames + ',', '') + column_name,

  @columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),'

  + column_name + case when data_type in ('datetime', 'smalldatetime') then ',121'

  when data_type in ('numeric', 'decimal') then ',128'

  when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'

  when data_type in ('datetime', 'smalldatetime') then ',120'

  else ''

  end + ') as ' + column_name

  FROM tempdb.INFORMATION_SCHEMA.Columns

  WHERE table_name = '##TempExportData'

  -- execute select query to insert data and column names into new temp table

  SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'

  exec (@sql)

  -- build full BCP query

  DECLARE @bcpCommand VARCHAR(8000)

  SET @bcpCommand = 'bcp " SELECT * from ##TempExportData2" queryout'

  SET @bcpCommand = @bcpCommand + ' C:\CallCenter_Audit_Report_%Date:~-4,4%%Date:~-10,2%%Date:~-7,2%.xls -c -w -T -U sa -P sa","-CRAW'

  EXEC assyst..xp_cmdshell @bcpCommand

  if @@error > 0

  begin

  select 0 as ReturnValue -- failure

  return

  end

  drop table ##TempExportData

  drop table ##TempExportData2

  set @columnNames =' '

  set @columnConvert =' '

  set @tempSQL =' '

  select 1 as ReturnValue