SQL Server Out Put Excel File
SQL Server Out Put Excel File
ÔÚ SQL ServerÖÐ, µ¼³öEXCEL Îļþ, Óõ½ bcp.exe
bcp µ¼³öµÄ±¾ÖÊÊÇ´¿Îı¾Îĵµ,
ÈôÊý¾Ýº¬ÓÐÖÐÎÄ,Çëµ¼³öµ½ÖÐÎİæEXCEL,»òTXTÎĵµµÈ, ·ñÔòÂÒÂë....
ÓÃTXT ´ò¿ªÓ¢ÎİæEXCEL,Ò²¿ÉÒÔ,
µ¼³ö Êý¾Ýµ½C:\authors.xls, ÈôÎļþ´æÔÚÔòÖØÐ´Îļþ, ²»´æÔÚÔò´´½¨Îļþ
Exec master..xp_cmdshell 'bcp "select [DBName].dbo.[TableName].* from [DBName].dbo.[TableName] where [ColumnName] = Value" queryout C:\authors.xls -c -S".\SQLExpress" -U"sa" -P"Password"'
select SQLÓï¾ä¸ù¾Ýʵ¼ÊÐèÒªÀ´ÖØÐ´
µ¼³öÎļþÖ»ÓÐÊý¾Ý,ûÓбíÍ·.
Èç¹û, ÐèÒª´ø±íÍ·, ÔòÒªÔ¤ÏÈÉèÖúñíÍ·, Óà insert into ·½·¨.
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 8.0;HDR=YES;DATABASE=C:\author.xls',Sheet1$) select [DBName].dbo.[TableName].* from [DBName].dbo.[TableName] where [ColumnName] = Value
select SQLÓï¾ä¸ù¾Ýʵ¼ÊÐèÒªÀ´ÖØÐ´
Èç¹û,ÐèÒª±íÍ·, ¶øÇÒÊǵ¥±íµ½³ö, Çë·ÃÎÊÒÔÏÂÍøÖ·
1. ʹÓÃSQLÓï¾ä
http://blog.csdn.net/fcfd86/archive/2010/02/26/5329430.aspx
2. ʹÓô洢¹ý³Ì
http://blog.csdn.net/fcfd86/archive/2010/02/26/5329446.aspx
Ïà¹ØÎĵµ£º
1. In "MicroSoft SQL Server Management Studio", right click SQL Server instance, select "Properties", select "Security" in left panel and check "SQL Server and Windows Authentication mode" in right Panel.
2. In "SQL Server Configuration Manager", select "SQL Server Network Configuration > ......
Íâ¼ü
======================
Íâ¼üÊÇÏà¶ÔÓÚÖ÷¼ü˵µÄ£¬Êǽ¨Á¢±íÖ®¼ä µÄÁªÏµµÄ±ØÐëµÄǰÌá¡£
±ÈÈ磺ѧÉú±í ¡¢Ñ§Éú³É¼¨±íÒ»Ò»¶ÔÓ¦ÊÇÒòΪ ËûÃǶ¼¾ßÓÐÏàͬµÄ×ֶΣºÑ§ºÅ£¬°ÑѧÉú±í×÷ΪÖ÷±í£¬Ñ§ºÅÊÇËûµÄÖ÷¼ü£¬Ïà¶ÔÓÚÖ÷±íÀ´Ëµ£¬Ñ§Éú³É¼¨µÄ×ֶΠѧºÅ¾ÍÊÇѧÉú±íµÄÍâ¼ü¡£
ûÓÐÍâ¼ü£¬Á½¸ö±í¾Íû°ì·¨½¨Á¢ÁªÏµ°¡£¡ ......
·ÀÖ¹·Ç·¨±íD99_Tmp,kill_kkµÄ³öÏÖÊÇ·ÀÖ¹ÎÒÃǵÄÍøÕ¾²»±»¹¥»÷,ͬʱҲÊÇSQL°²È«·À·¶Ò»µÀ±ØÒªµÄ·ÀÏß,Ëä˵ÀûÓÃÕâÖÖ·½Ê½¹¥»÷µÄÈ˶¼ÊǺڿÍÖеÄСÄñ,µ«ÊÇÎÒÃÇÒ²²»µÃ²»·À,ÒÔÃâÔì³É²»¿ÉÏëÏóµÄºó¹û,·Ï»°²»¶à˵ÁË,˵Ï·À·¶·½·¨:
xp_cmdshell¿ÉÒÔÈÃϵͳ¹ÜÀíÔ±ÒÔ²Ù×÷ϵͳÃüÁîÐнâÊÍÆ÷µÄ·½Ê½Ö´Ðиø¶¨µÄÃüÁî×Ö·û´®,²¢ÒÔÎı¾Ðз½Ê½·µ»ØÈκÎÊ ......
SQL×¢Èëʽ¹¥»÷ÊÇÀûÓÃÊÇÖ¸ÀûÓÃÉè¼ÆÉϵÄ©¶´£¬ÔÚÄ¿±ê·þÎñÆ÷ÉÏÔËÐÐSqlÃüÁîÒÔ¼°½øÐÐÆäËû·½Ê½µÄ¹¥»÷¶¯Ì¬Éú³ÉSqlÃüÁîʱûÓжÔÓû§ÊäÈëµÄÊý¾Ý½øÐÐ
ÑéÖ¤ÊÇSql×¢Èë¹¥»÷µÃ³ÑµÄÖ÷ÒªÔÒò¡£
±ÈÈ磺
Èç¹ûÄãµÄ²éѯÓï¾äÊÇselect * from admin where
username="&user&" and password="&pwd&"&quo ......