Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQLServer»ñÈ¡ExcelÖÐËùÓÐSheet

EÅ̸ùĿ¼Ð½¨Ò»¸öExcelÎļþaa.xlsºó²âÊÔÈçÏ´úÂë
use tempdb
go
if (object_id ('udf_getExcelTableNames' ) is not null )
    drop function dbo .udf_getExcelTableNames
go
create function udf_getExcelTableNames (@filename varchar (1000 ))
returns @t table (id int , name varchar (255 ))
as
begin
    declare   
    @error int , @obj int , @c int , @sheetname varchar (255 ) , @sheetstring varchar (255 )
  
    exec @error = sp_oacreate 'Excel.Application' , @obj   out  
    exec @error = sp_oamethod @obj , 'Workbooks.Open' , @c out , @filename
    exec @error = sp_oagetproperty @obj , 'ActiveWorkbook.Sheets.Count' , @c    out
    while (@c > 0 )
    begin
        set @sheetstring = 'ActiveWorkbook.Sheets(' + ltrim (@c )+ ').Name'
        exec @error = sp_oagetproperty @obj , @sheetstring , @sheetname    out
        insert into @t select @c , @sheetname
        set @c = @c - 1
    end
    exec @error = sp_oadestroy @obj  
    return
end
go
select * from dbo .udf_getExcelTableNames ('e:\aa.xls' )
/*--²âÊÔ½á¹û
3    Sheet3
2    Sheet2
1    Sheet1
*/


Ïà¹ØÎĵµ£º

.NET SQLServer2005 XML×Ö¶Î

//ÕâÊÇÌí¼Ó 
private void button1_Click(object sender, EventArgs e)
        {
            string s = "Persist Security Info=False;Integrated Security=SSPI;database=IIntegration;server=(local)";
 &n ......

SqlServer 2005 Êý¾Ý¿âµÄÓÅȱµã...

SQL Server 2005Êý¾Ý¿â±¸·ÝºÍ»Ö¸´ÌØÐÔ
 
SQL Server 2000ÖдæÔÚµÄÐí¶àµÄ±¸·ÝºÍ»Ö¸´ÌØÐÔ¶¼Í¬Ñù±£ÁôÔÚÁËSQL Server 2005ÖУ¬µ«ÊÇÓÐһЩеÄÌá¸ßͬÑùÖµµÃÎÒÃǹØ×¢¡£
¾µÏñ±¸·Ý
SQL Server 2005ÈÃÄã¿ÉÒÔ´´½¨¾µÏñ±¸·Ý¡£¾µÏñ±¸·ÝÔÊÐíÄãΪ±¸·ÝÎļþ´´½¨Á½¸ö»òÕßËĸöͬÑùµÄ¿½±´£¬ÒÔ·À±¸ÆäÖеÄijһ¸ö¼¯ºÏË𻵵ÄÇé¿ö¡£¾µÏñ¾ßÓ ......

SQLServer»ñȡÿ×éǰ10%µÄÊý¾Ý

sqlserver2005Óйؼü×Öntile(x)ºÍover(partition by.. order by..)×Ó¾äÅäºÏ.
±ÈÈç»ñȡÿ¸ö±íµÄǰ10%¸ö×ֶΡ£
select id , name , colid , rn from (
select * , rn = ntile (10 )
    over (partition by id order by colorder )
from syscolumns )t where rn = 1 ......

SQLServerÅúÁ¿µ¹ÈëĿ¼Îļþ ÊÕ²Ø


¿ÉÒÔÓÃÀ©Õ¹´æ´¢¹ý³Ìxp_dirtree»ñÈ¡ÎļþÁÐ±í£¬ÓÃopenrowsetµ¹ÈëÊý¾Ýµ½¶þ½øÖÆ×ֶΡ£
openrowsetµÄÓ÷¨¿ÉÒԲο¼msdn
http://technet.microsoft.com/zh-cn/library/ms190312.aspx
Èç¹ûÎļþºÜ¶à£¬½¨Ò黹ÊÇÓóÌÐòµ¹ÈëÁË
if (object_id ('t_bulkResult' ) is   not   null )
drop table t_bulkResult ......

SQLServerÖеÄScanfºÍPrintf ÊÕ²Ø

SQLServerÖÐÓÐÁ½¸öÀ©Õ¹´æ´¢¹ý³ÌʵÏÖScanfºÍPrintf¹¦ÄÜ£¬Ç¡µ±µÄʹÓÃËüÃÇ¿ÉÒÔÔÚÌáÈ¡ºÍÆ´½Ó×Ö·û´®Ê±´ó·ù¶È¼ò»¯SQL´úÂë¡£
1¡¢xp_sscanf£¬ÓÃËü¿ÉÒÔ·Ö½â¸ñʽÏà¶Ô¹Ì¶¨µÄ×Ö·û´®£¬Õâ¶ÔÓÚÑá¾ëʹÓÃÒ»¶ÑsubstringºÍcharindexµÄÅóÓÑÀ´Ëµ²»´í¡£±ÈÈçǰ¼¸ÌìµÄÒ»¸öÌû×ÓÖÐÌá³öµÄÈçºÎ·Ö½âipµØÖ·£¬Ïà¶Ô¼òÁ·ÇÒͨÓõĴúÂëÓ¦¸ÃÊÇÏÂÃæÕâÑù
------- ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ