BCP Command is not working. I need to pull the data from table and load into excel

[Reddeppa]

EXEC master..xp_cmds hell 'BCP "'select * from tmp_ACS_MultiDi mensional where cName = 'CH All Workstations''" QUERYOUT D:\Test\MultiDi mensionalReport s\EMEA\SMP7_374 1\CH_All_MultiD imensionalRepor ts.xls -T -c'

Is the command will work, can you please provide methe exact bcp command to execute..:)

[Rabbit]

BCP is a SQL Server command, not a Windows command so there's no need to invoke the command shell when running a BCP. Just run it from SSMS directly.

[Reddeppa]

BCP is a SQL Server command, not a Windows command so there's no need to invoke the command shell when running a BCP. Just run it from SSMS directly.

Hi rabbit,

I need to run the script from stored procedure to create the excel sheet based on cname.
"'select * from tmp_ACS_MultiDi mensional where cName = 'CH All Workstations''"

Regards,
Reddeppa G

[Rabbit]

You can call BCP from a stored procedure without invoking the command shell.

[Reddeppa]

hi Rabbit,

I have the procedure in SQL Server 2005 to create the excel sheets based on cname & Servername.

here I have few steps
1.I have the folders named

a).EMEA (under EMEA we have sub folders are SMP7_3741,SMP7_ 3742,SMP7_3743, SMp7_3744,SMP7_ 3745)
b).AM(under AM we have sub folders are SMP7_3746,SMP7_ 3747)
c)AP(under AP we have sub folders are SMP7_3759,SMp7_ 3790,SMP7_3791)


2.When i execute the below stored procedure it will generate the excel sheets based on cname.The cname also moved by servername into the respective folders.

CREATE proc [dbo].[sp_DHL_MultidimensionalReport_2005]       
as          
         
DECLARE @cName nvarCHAR(500)                          
DECLARE @serverName nvarCHAR(500)            
declare @opfile nvarchar(500)            
declare @qry nvarchar(1000)            
declare @delqry nvarchar(100)            
declare @delsrvname nvarchar(100)            
            
set @delsrvname = ''            
            
DECLARE C CURSOR                           
FOR SELECT distinct cName, servername FROM tmp_ACS_MultiDimensional order by servername, cName            
          
OPEN C                          
                          
FETCH NEXT FROM C INTO @cName, @serverName                          
WHILE @@FETCH_STATUS = 0                           
BEGIN                          
            
set @opfile = ''            
set @opfile = 'G:\DB\Data_Export_Import\MultiDimensionalExcelReports\'          
          
If Right(@serverName, 4) = '3746' or Right(@serverName, 4) = '3747'                    
begin                    
 set @opfile = @opfile+'AM\SMP7_'+Right(@serverName, 4)+'\'                    
end                    
else if Right(@serverName, 4) = '3759' or Right(@serverName, 4) = '3790' or Right(@serverName, 4) = '3791'                    
begin                    
 set @opfile = @opfile+'AP\SMP7_'+Right(@serverName, 4)+'\'                    
end                    
else                    
begin                    
 set @opfile = @opfile+'EMEA\SMP7_'+Right(@serverName, 4)+'\'                  
end                    
            
if ltrim(rtrim(@delsrvname )) = '' or ltrim(rtrim(@delsrvname)) <> ltrim(rtrim(@serverName))            
begin            
 set @delsrvname = @serverName            
            
 set @delqry = ''            
 set @delqry = 'del '+@opfile + '*.xls'             
 exec xp_cmdshell @delqry            
 print @delqry            
end            
                          
set @cName = replace(replace(replace(@cName, '[', ''), ']', ''), '/', '')            
            
set @opfile = @opfile+replace(@cName, 'Workstations', 'MultiDimensionalReports')+'.xls'                          
--print @opfile                          
                    
set @qry = ''                     
set @qry = 'select * from tmp_ACS_MultiDimensional where cName = '''+@cName+''''                     

exec sp_makewebtask  [B]---this system procedure is working on SQL 2005 only because we don'thave the same procedure in SQL server 2008)                         [/B]
    @outputfile = @opfile             
    , @query = @qry             
    , @colheaders =1            
    , @FixedFont=0,@lastupdated=0,@resultstitle='Multidimensional Report'   

            
    FETCH NEXT FROM C INTO @cName, @serverName                          
END                          
CLOSE C                          
DEALLOCATE C                          
                          
print 'Reports are placed in G:\DB\Reddeppa\MultiDimensionalExcelReports\ folder'

Now I need to implement the same in SQL Server 2008 database.

Can you please help me on this case.

Regards,
Reddeppa G

[Rabbit]

I don't know anymore what you're asking because the code you just posted doesn't contain a BCP command in it anywhere.

[Reddeppa]

Hi Rabbit,

IF the above code is not working in SQL 2008 because we don't have the SP_MAKEWEBTask system procedure.

So we have to implement with BCP Command.

Please find converted code in sql 2008...

USE [AexNS]
GO
/****** Object:  StoredProcedure [dbo].[sp_DHL_MultidimensionalReport]    Script Date: 03/27/2013 22:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_DHL_MultidimensionalReport]         
as           
          
DECLARE @cName nvarCHAR(500)                           
DECLARE @serverName nvarCHAR(500)             
declare @opfile nvarchar(1000)             
declare @qry nvarchar(1000)             
declare @delqry nvarchar(1000)             
declare @delsrvname nvarchar(100)             
declare @flname varchar(3000)      
        
set @delsrvname = ''             
             
DECLARE C CURSOR                            
FOR SELECT distinct cName, servername FROM tmp_ACS_MultiDimensional  order by servername, cName             
           
OPEN C                           
                           
FETCH NEXT FROM C INTO @cName, @serverName                           
WHILE @@FETCH_STATUS = 0                            
BEGIN                           
             
set @opfile = ''             
set @opfile = 'D:\Test\MultiDimensionalReports\'           
           
If Right(@serverName, 4) = '3746' or Right(@serverName, 4) = '3747'                     
begin                     
 set @opfile = @opfile+'AM\SMP7_'+Right(@serverName, 4)+'\'                     
end                     
else if Right(@serverName, 4) = '3759' or Right(@serverName, 4) = '3790' or Right(@serverName, 4) = '3791'                     
begin                     
 set @opfile = @opfile+'AP\SMP7_'+Right(@serverName, 4)+'\'                     
end                     
else                     
begin                     
 set @opfile = @opfile+'EMEA\SMP7_'+Right(@serverName, 4)+'\'                   
end                     
             
if ltrim(rtrim(@delsrvname )) = '' or ltrim(rtrim(@delsrvname)) <> ltrim(rtrim(@serverName))              
begin             
 set @delsrvname = @serverName             
             
 set @delqry = ''             
 set @delqry = 'del '+@opfile + '*.xls'              
exec xp_cmdshell @delqry             
 print @delqry             
end             
 set @cName = replace(replace(replace(@cName, '[', ''), ']', ''), '/', '')             

set @qry = ''                      
set @qry = 'select * from aexns.dbo.tmp_ACS_MultiDimensional where cName = '''+@cName+''''                      
             
set @opfile = @opfile +replace(Replace(cast(@cName as varchar(1000)), ' ', '_'), 'Workstations', 'MultiDimensionalReports')+'.xls'                           
print @opfile 


set @flname = '' 
set @flname = '''BCP aexns.dbo.tmp_ACS_MultiDimensional  OUT  '+@opfile+ ' -T -c''' 

--set @flname = '''BCP "'''+@qry+'''"  QUERYOUT  '+@opfile+ ' -T -c'''  

 print @flname
 
set @flname = 'EXEC master..xp_cmdshell ' + @flname

exec(@flname)             
    
print @flname 
    FETCH NEXT FROM C INTO @cName, @serverName                           
END                           
CLOSE C                            
DEALLOCATE C                           
                           
print 'Reports are placed in D:\Test\MultiDimensionalReports\ folder'

[Reddeppa]

Client need the data in excel sheets..not from reportign services...


While running the above code excel sheets are generating with our column headers in the excel.

That to I need to seperate the data based on cname.

for your reference please find the attached data...
load the data into a table and runt he above script.

Kind regards,
Reddeppa G

[Rabbit]

Please use code tags when posting code.

You can export from reporting services to Excel.

You're still invoking the command shell. My advice has not changed from my very first post. You didn't even implement my very first suggestion.