如何编写将查询输出转换为excel的过程。 [英] How to write the procedure to transform the query output into excel.

问题描述

我正在编写查询，但仍然没有得到它的错误...

I am writing query but still am not getting what is the error it is...

alter procedure	toinsert
  (    
   @year int,    
   @month int,    
   @filepath varchar(2000),    
   @machinename varchar(100),    
   @dbname varchar(100)    
  )
  as
  begin
  
  set @dbname = @dbname + '..'
  declare @nl char(2)
  set @nl = CHAR(13) + CHAR(10)
  
  DECLARE @TmpHdrFileName varchar(1000)
  set @TmpHdrFileName = @filepath+'usercount.txt'
  
  declare @hdrstring varchar(100)
  
  set @hdrstring='select 
  ''''EmpCode'''' as empcode,
  ''''fsname'''' as fsname,
  ''''ntype'''' as ntype,
  ''''desg'''' as desg,
  ''''userid'''' as userid,
  ''''dcr_count'''' as dcr_count'  
   
   set @hdrstring = 'EXEC master..xp_cmdshell ''bcp "' + @hdrstring + 
	
   '" queryout '+@TmpHdrFileName+' -S '+@machinename+' -T -c '''  
  
  set @hdrstring = replace(@hdrstring,@nl,' ')
  
  exec (@hdrstring)	
  
  declare @sql1 varchar(max)
  set @sql1='select f.C_EmpNo as EmpCode,f.C_Name as FSName,ua.N_Type as NType,
ua.c_sh_name as Desg,
ui.C_UserID as UserID,dwr.dcrcount as DCR_Count
from 
Tbl_FS_Mst f
'+@dbname+'join Tbl_User_Access ua on f.N_Type=ua.N_Type
'+@dbname+'join Tbl_User_Info ui on f.C_EmpNo=ui.C_Code
left join
(
	select c_fs_code,COUNT(distinct n_srno) as dcrcount
	from '+@dbname+'tbl_dwr d
	where MONTH(d_date_report)=@month and year(d_date_report)=@year
	group by c_fs_code
)dwr on dwr.C_FS_Code=f.C_Code
where f.C_EmpNo<>''''000000'''' or dwr.dcrcount is not null
--and  dwr.dcrcount=null
order by f.C_EmpNo,f.N_Type,f.C_Name
'

declare @TmpDataFileName varchar(1000)
set @TmpDataFileName = @filepath+'usercount.txt'
set @sql1 = 'exec master..xp_cmdshell ''bcp "' + @sql1 + '" queryout
'+@TmpDataFileName+' -S '+@machinename+' -T -c'''
set @sql1 = replace(@sql1,@nl,' ')
exec(@sql1)

declare @filename varchar(1000)
set @filename = @filepath +	'usercountfor_'+LEFT(DATENAME(MONTH,DATEADD(month,@month,-1)),3)+
convert(varchar,@year)+'_creaton_'+REPLACE(convert(varchar(10),GETDATE(),103),'/','-')+'.xls'
declare @mergedata	varchar(1000)
set @mergedata='master..xp_cmdshell ''copy '+@TmpHdrFileName+' + '+@TmpDataFileName +'
'+@filename+''''
exec(@mergedata)

declare @delfiles varchar(500)
set @delfiles='master..xp_cmdshell ''del '+@TmpDataFileName+''''
set @delfiles='master..xp_cmdshell ''del '+@TmpHdrFileName+''''
exec @delfiles
end

错误上午得分低于



消息105，等级15，状态1，行1

字符串'bcp之后的未闭合引号'选择' EmpCode'为empcode，'fsname'为fsname，'ntyp'。



（7行受影响）



（2行受影响）

消息7202，等级11，状态2，过程插入，第76行

无法找到服务器'master'在sys.servers中。验证是否指定了正确的服务器名称。如有必要，执行存储过程sp_addlinkedserver将服务器添加到sys.servers。



请告诉我，因为这对我来说非常紧急。

Error am getting is below

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'bcp "select 'EmpCode' as empcode, 'fsname' as fsname, 'ntyp'.

(7 row(s) affected)

(2 row(s) affected)
Msg 7202, Level 11, State 2, Procedure toinsert, Line 76
Could not find server 'master' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

please tell me since it is very urgent for me.

推荐答案

首先请在您的SP中更正以下部分代码..您正在创建的文本部分不正确。你也可以参考下面的链接。



MSDN xp_cmdshell [< a href =https://msdn.microsoft.com/en-us/library/ms175046.aspxtarget =_ blanktitle =新窗口> ^ ]

First Please correct below part of code in your SP.. what text part you are creating is not correct. you can also refer below link for that.

MSDN xp_cmdshell[^]

set @hdrstring='select
''''EmpCode'''' as empcode,
''''fsname'''' as fsname,
''''ntype'''' as ntype,
''''desg'''' as desg,
''''userid'''' as userid,
''''dcr_count'''' as dcr_count'

 set @hdrstring = 'EXEC master..xp_cmdshell ''bcp "' + @hdrstring +

 '" queryout '+@TmpHdrFileName+' -S '+@machinename+' -T -c '''

set @hdrstring = replace(@hdrstring,@nl,' ')

取代master..xp_cmdshell，使用master.dbo.xp_cmdshell

Instead master..xp_cmdshell, use master.dbo.xp_cmdshell

这篇关于如何编写将查询输出转换为excel的过程。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！