if @querytext is null begin print 'set the query string' return end
--設(shè)置服務(wù)器名為本地服務(wù)器(@@servername返回運(yùn)行sql server的本地服務(wù)器名稱) if @server is null select @server = @@servername
--設(shè)置用戶名為當(dāng)前系統(tǒng)用戶名(使用system_user返回當(dāng)前系統(tǒng)用戶名) if @uname is null select @uname = system_user
set nocount on
--創(chuàng)建sqldmo.sqlserver對(duì)象 exec @hr = sp_oacreate 'sqldmo.sqlserver', @sqlserver out if @hr <> 0 begin print 'error create sqldmo.sqlserver' return end
--連接到sql server系統(tǒng) if @pwd is null begin exec @hr = sp_oamethod @sqlserver, 'connect', null, @server, @uname if @hr <> 0 begin print 'error connect' return end end else begin exec @hr = sp_oamethod @sqlserver, 'connect', null, @server, @uname, @pwd if @hr <> 0 begin print 'error connect' return end end
--the executewithresults method executes a transact-sql command batch --returning batch result sets in a queryresults object select @result_str = 'executewithresults("' + @querytext + '")' exec @hr = sp_oamethod @sqlserver, @result_str, @queryresults out if @hr <> 0 begin print 'error with method executewithresults' return end
--the currentresultset property controls access to the result sets of a queryresults object exec @hr = sp_oamethod @queryresults, 'currentresultset', @currentresultset out if @hr <> 0 begin print 'error get currentresultset' return end
--the columns property exposes the number of columns contained --in the current result set of a queryresults object exec @hr = sp_oamethod @queryresults, 'columns', @columns out if @hr <> 0 begin print 'error get columns' return end
--the rows property returns the number of rows in a referenced --query result set or the number of rows existing in a table exec @hr = sp_oamethod @queryresults, 'rows', @rows out if @hr <> 0 begin print 'error get rows' return end
--創(chuàng)建excel.application對(duì)象 exec @hr = sp_oacreate 'excel.application', @object out if @hr <> 0 begin print 'error create excel.application' return end
--獲得excel工作簿對(duì)象 exec @hr = sp_oagetproperty @object, 'workbooks', @workbooks out if @hr <> 0 begin print 'error create workbooks' return end
--在工作簿對(duì)象中加入一工作表 exec @hr = sp_oagetproperty @workbooks, 'add', @workbook out if @hr <> 0 begin print 'error with method add' return end
--range對(duì)象(a1單元格) exec @hr = sp_oagetproperty @object, 'range("a1")', @range out if @hr <> 0 begin print 'error create range' return end
while (@indrow <= @rows) begin select @indcolumn = 1 while (@indcolumn <= @columns) begin --the getcolumnstring method returns a queryresults object result set member converted to a string value exec @hr = sp_oamethod @queryresults, 'getcolumnstring', @result_str out, @indrow, @indcolumn if @hr <> 0 begin print 'error get getcolumnstring' return end
exec @hr = sp_oasetproperty @range, 'value', @result_str if @hr <> 0 begin print 'error set value' return end
exec @hr = sp_oagetproperty @range, 'offset', @range out, @off_row, @off_column if @hr <> 0 begin print 'error get offset'
return end
select @indcolumn = @indcolumn + 1
end
select @indrow = @indrow + 1 select @code_str = 'range("a' + ltrim(str(@indrow)) + '")' exec @hr = sp_oagetproperty @object, @code_str, @range out if @hr <> 0 begin print 'error create range' return end
end
select @result_str = 'exec master..xp_cmdshell ''del ' + @filename + ''', no_output' exec(@result_str) --如果存在@filename文件，則先刪除 select @result_str = 'saveas("' + @filename + '")' exec @hr = sp_oamethod @workbook, @result_str if @hr <> 0 begin print 'error with method saveas' return end
exec @hr = sp_oamethod @workbook, 'close' if @hr <> 0 begin print 'error with method close' return end
exec @hr = sp_oadestroy @object if @hr <> 0 begin print 'error destroy excel.application' return end
exec @hr = sp_oadestroy @sqlserver if @hr <> 0 begin print 'error destroy sqldmo.sqlserver' return end go
