Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP Error !! Trying to copy Data in Excel format but errors !! Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 12:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 18, 2013 12:57 AM
Points: 25, Visits: 92
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




Post #1357158
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse