Click here to Skip to main content
14,272,287 members
Rate this:
Please Sign up or sign in to vote.
See more:
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


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.
Posted
Updated 19-Mar-15 2:07am
v3
Comments
ZurdoDev 19-Mar-15 9:33am
   
The error told you what is wrong. You have an unclosed quotation mark. You need to fix the syntax. It is tricky when you are doing dynamic sql because you have to double up the single quotes and it can be hard to find so just break it down and see what @sql1 ends up being.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

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,' ')


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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100