SQLSERVER，不使用BCP，把查询结果输出为txt文本文件的存储过程

 SQLSERVER，不使用BCP，把查询结果输出为txt文本文件的存储过程

由于有些服务器上，数据库由于服务器安全问题，禁用了cmd_shell 不能用BCP进行查询结果文本化输出。

所以特写了个存储过程。用于生成文本文件

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER  PROCEDURE stp_ExportDataToTxt
/**//************************************************************************
 *                    Powered by Tang Ren (R)                           *
 *                          2007-7-11                                   *
 ************************************************************************/

      @sqlstr nvarchar(4000),                   --查询语句
      @path nvarchar(1000),                --文件保存文件夹
      @fname nvarchar(250)                --文件保存名字
as
    --Define variable
    declare @strPath varchar(300)         --文件保存的地址 
    declare @colsCount int
    declare @hr int
    DECLARE @object int  
    DECLARE @src varchar(255), @desc   varchar(255)   
    Declare @file int   
        declare @sql varchar(1000)
    declare @tbname sysname
    
        --Initialize variable
    set @tbname = 'tb_' + convert(varchar(40),newid()) 
    set @sql = replace(@sqlstr,'from','into ['+@tbname+'] from')
    set @strPath = ''
    print @sql
    exec(@sql)

    --Estimate the dir suffix, if it not end in '' then add it.
    if right(@path,1)<>''
      set @path=@path+''
    set   @strPath=@path+@fname 
    print @strPath    

    --Create FSO Object for file operation.
    EXEC   @hr   =   sp_OACreate   'Scripting.FileSystemObject',   @object   OUT   
    IF   @hr   <>   0   
    BEGIN   
          EXEC   sp_OAGetErrorInfo   @object,   @src   OUT,   @desc   OUT     
          SELECT   hr=convert(varbinary(4),@hr),   Source=@src,   Description=@desc   
            RETURN   
    END   
    
        -- Create File ,if file exist then override it.
    EXEC   @hr   =   sp_OAMethod   @object,   'CreateTextFile',   @file   OUTPUT   ,   @strPath   
    IF   @hr   <>   0   
    BEGIN   
          EXEC   sp_OAGetErrorInfo   @object   
            RETURN   
    END
    
    set @sql ='select * from syscolumns where id=object_id('''+@tbname+''')'
    print object_id(@tbname)

    -- Create initial field name with each columns
    declare @name varchar(1000)
    declare @flag int
    set @name=''
    set @flag=0
    declare cur_data CURSOR for
    select name from syscolumns where id=object_id(@tbname)
    open cur_data
    fetch next from cur_data into @name
    while @@fetch_status=0
    begin
      if @flag=1
        exec sp_OAMethod @file,'Write',NULL,','
      exec sp_OAMethod @file,'Write',NULL,@name
      set @flag=1
      fetch next from cur_data into @name
    end
    close cur_data
    deallocate cur_data
 
    EXEC   @hr   =   sp_OAMethod   @file,   'Close',NULL   
    IF   @hr   <>   0   
    BEGIN   
          EXEC   sp_OAGetErrorInfo   @object   
            RETURN   
    END   
    
    set @sql = 'insert into openrowset(''microsoft.jet.oledb.4.0'',''text;hdr=no;database='+@path+''',''select * from['+@fname+']'')' +@sqlstr
    print @sql
    exec(@sql)

    set @sql='drop table ['+@tbname+']'
    print @sql
    exec(@sql)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO