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
*/
Ïà¹ØÎĵµ£º
Ò»¡¢AccessÓëSqlServerÊý¾ÝÀàÐÍ±È½Ï Microsoft Access Êý¾ÝÀàÐÍ SQL Server Êý¾ÝÀàÐÍ ÊÇ/·ñ£¬£¨0ºÍ-1£©
1 룬²»ÔÊÐíÓÐ Null Öµ¡£ bit(1¡¢0 »ò NULL) Êý×Ö£¨×Ö½Ú£©£ ......
MS Sqlserver 2005 ¼¯ÈºÊÇÖ¸Êý¾Ý¿â¸ß¿ÉÓÃÐÔ¼¯Èº£¬ÄÜʵÏÖͨ³£Ëù˵µÄ“Ë«»úÈȱ¸”£¬¼´µ±Ò»Ì¨·þÎñÆ÷Í£µôÊý¾Ý¿â·þÎñ¿ÉÒÔ×Ô¶¯×ªÒƵ½Áíһ̨·þÎñÆ÷¡£
»·¾³ÒªÇó£º
1¡¢windows 2003 serverÆóÒµ°æ
&nb ......
»ñÈ¡sqlserverÊý¾Ý¿âÖÐËùÓп⡢±í¡¢×Ö¶ÎÃûµÄ·½·¨
2009Äê03ÔÂ12ÈÕ ÐÇÆÚËÄ ÏÂÎç 12:51
1.»ñÈ¡ËùÓÐÊý¾Ý¿âÃû:
SELECT Name from Master..SysDatabases ORDER BY Name
2.»ñÈ¡ËùÓбíÃû:
SELECT Name from DatabaseName..SysObjects Where XType='U' ORDER BY Name
XType='U':±íʾËùÓÐÓû§±í;
XType='S':±íʾËùÓÐϵͳ± ......
//ÕâÊÇÌí¼Ó
private void button1_Click(object sender, EventArgs e)
{
string s = "Persist Security Info=False;Integrated Security=SSPI;database=IIntegration;server=(local)";
&n ......
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 ......