Click here to Skip to main content
15,884,237 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
 --'Select Job_T_Job_Desc,Job_T_Job_ID from Pmsclient.dbo.PMS_Job_TManager_h 
 --'Where Job_T_Job_ID=''''JON78''''','c:\bcp\Siraj.xls'
alter Procedure [dbo].[Pms_Usp_GetBackupXlsResult1]    
 @server Varchar(100),  
 @DynamicSql Varchar(Max),    
 @fullFileName Varchar(1000)    
--Declare @DynamicSql  Varchar(Max)  
--Set @DynamicSql='select top 5 * from test.dbo.customer'  
--Declare @server varchar(500)  
--Set @server='shrigi104\SHRIUNO0301'  
--Select @server  
Declare @ExcSql Varchar(Max)    
Set @ExcSql='SELECT * INTO tempdb.dbo.##MyTempTable FROM OPENROWSET(''SQLNCLI'',
    ''Server='+@server+';UID=SA;PWD=welcome3#'',''SET NOCOUNT ON;SET FMTONLY OFF; '+@DynamicSql+''')'    
      Select (@ExcSql)  
     Exec (@ExcSql)    
     Select (@ExcSql)    
Declare @dbName Varchar(100)   
set @dbName= 'master'         
Declare  @sql varchar(5000)   
set @sql= 'Select * from tempdb.dbo.##MyTempTable'            
--Declare   @fullFileName varchar(100)   
--set @fullFileName= 'C:\bcp\ss.xls'        
Declare @dbName1 Varchar(100)     
if object_id('##TempExportData') is not null        
    drop table ##TempExportData        
if object_id('##TempExportData2') is not null        
    drop table ##TempExportData2    
    Select @dbName1=@dbName      
    Select @dbName = 'use ' + @dbName + ';'        
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)        
Select (@dbName + @tempSQL)        
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'        
Select @columnNames      
-- 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)        
Select @sql      
set   @sql = 'bcp " select * from ##TempExportData2 " queryout "' + @fullFileName + 
    '" -U uno -P uno   -c -T -S '+ @server   
Select @sql      
Exec master..xp_cmdshell @sql       
Drop table ##TempExportData      
Drop table ##TempExportData2      
Drop Table ##MyTempTable    

Using the above procedure I am able to create an Excel file and also copied data to it using BCP concept. But now the problem is I need to store the value in sheet1,sheet2,.... in separate manner..

for eg., i need to stored 1st 100 records in sheet1 and next 100 records in sheet2, on..

Can anyone help me to resolve this issue...:confused:
Updated 4-Dec-09 9:01am

What is the problem of creating multiple sheets. Run Create Table statement to create sheets and insert values.

If I was in your situation, I would have created one CLR stored procedure to do this job, rather than using OpenRowSet.

See how I am using creating tables using MDAC.

Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^]

This is from .NET and you can easily convert it to OpenRowSet easily.

Also, you can create CLR stored proc using this article :
Working with CLR Objects in SQL Server 2005 or Higher: Part I[^]

Finally, when you post a question, please remove all comments in code, and use "Code Block" to make us understand code properly. I can see there are lots of lines commented out and many of us will find difficulty to read them.

Share this answer
pls suggest me with the existing procedure...

can't we able to achieve it using the above one..
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900