ʹÓÃSQL ServerµÄOPENROWSETº¯Êý
¡¡Äã¿ÉÄܳ£³£»áÐèÒªÔËÐÐÒ»¸öad hoc²éѯ´ÓÔ¶³ÌOLE DBÊý¾ÝÔ´ÌáÈ¡Êý¾Ý£¬»òÕßÅúÁ¿ÏòSQL Server±íµ¼ÈëÊý¾Ý¡£ÔÚÕâÖÖÇé¿öÏ£¬Äã¿ÉÒÔÔÚT-SQL(Transact-SQL£¬Î¢Èí¶ÔSQLµÄÀ©Õ¹)ÖÐÓÃOPENROWSETº¯Êý¸øÊý¾ÝÔ´´«ÈëÒ»¸öÁ¬½Ó´®ºÍ²éѯÀ´ÌáÈ¡ÐèÒªµÄÊý¾Ý¡£
¡¡¡¡Äã¿ÉÄܳ£³£»áÐèÒªÔËÐÐÒ»¸öad hoc²éѯ´ÓÔ¶³ÌOLE DBÊý¾ÝÔ´ÌáÈ¡Êý¾Ý£¬»òÕßÅúÁ¿ÏòSQL Server±íµ¼ÈëÊý¾Ý¡£ÔÚÕâÖÖÇé¿öÏ£¬Äã¿ÉÒÔÔÚT-SQL(Transact-SQL£¬Î¢Èí¶ÔSQLµÄÀ©Õ¹)ÖÐÓÃOPENROWSETº¯Êý¸øÊý¾ÝÔ´´«ÈëÒ»¸öÁ¬½Ó´®ºÍ²éѯÀ´ÌáÈ¡ÐèÒªµÄÊý¾Ý¡£
¡¡¡¡Äã¿ÉÒÔʹÓÃOPENROWSETº¯Êý´ÓÈκÎÖ§³Ö×¢²áOLE DBµÄÊý¾ÝÔ´»ñÈ¡Êý¾Ý£¬±ÈÈç´ÓSQL Server»òAccessµÄÔ¶³ÌʵÀýÖÐÌáÈ¡Êý¾Ý¡£Èç¹ûÄãÓÃOPENROWSET´ÓSQL ServerʵÀýÖлñÈ¡Êý¾Ý£¬¸ÃʵÀý±ØÐëÅäÖÃΪÔÊÐíad hoc·Ö²¼Ê½²éѯ¡£
¡¡¡¡ÒªÅäÖÃÔ¶³ÌSQL ServerʵÀýÖ§³Öad hoc²éѯ£¬ÐèҪʹÓÃϵͳ´æ´¢¹ý³Ìsp_configureÏÈÉèÖÃadvanced options£¬ÔÙÆôÓÃAd Hoc Distributed Queries(ad hoc·Ö²¼Ê½²éѯ)¡£Çë¿´ÏÂÃæµÄT-SQL½Å±¾£º
¡¡¡¡EXEC sp_configure 'show advanced options', 1;
¡¡¡¡GO
¡¡¡¡RECONFIGURE;
¡¡¡¡GO
¡¡¡¡EXEC sp_configure 'Ad Hoc Distributed Queries', 1
¡¡¡¡GO
¡¡¡¡RECONFIGURE;
¡¡¡¡GO
¡¡¡¡Òª×¢ÒâµÄÊÇ£¬ÔÚÔËÐÐÍê´æ´¢¹ý³ÌÖ®ºó£¬Äã±ØÐëÔËÐГRECONFIGURE”ÃüÁî¡£ Ò»µ©ÄãÅäÖúÃÁËÔ¶³ÌSQL ServerʵÀý£¬Äã¾Í¿ÉÒÔ¶ÔËüʹÓÃOPENROWSETº¯Êý¡£Õâ¸öº¯Êý¿ÉÒÔÔÚSELECTÓï¾äµÄfrom´Ó¾äÀïʹÓá£ÏÂÃæµÄÀý×ÓÏÔʾÁ˸ú¯ÊýµÄ»ù±¾Óï·¨£º
¡¡¡¡OPENROWSET('provider', 'connection string', target)
¡¡¡¡¿ÉÒÔ¿´µ½£¬Õâ¸öº¯ÊýÓÐÈý¸ö²ÎÊý£º
¡¡¡¡·Provider —— Ä³ÌØ¶¨Êý¾ÝÔ´Ö§³ÖµÄOLE DBÌṩÕßµÄÈË»úÓѺÃÃû³Æ(ProgID)¡£ProviderµÄÃû×Ö±ØÐëÓõ¥ÒýºÅÀ¨ÆðÀ´¡£
¡¡¡¡·Connection string —— Á¬½Ó´®¡£ËüÊÇÓë¾ßÌåÌṩÕßproviderÏà¹ØµÄ×Ö·û´®£¬°üÀ¨Á¬½Óµ½¸ø×Ö·û´®ÖÐÖ¸¶¨µÄÊý¾ÝÔ´ËùÐèÒªµÄϸ½ÚÐÅÏ¢¡£¸ù¾ÝproviderµÄ²»Í¬£¬Á¬½Ó´®ÐÅÏ¢ÐèÒªÓÃÒ»¶Ô»ò¶à¶Ôµ¥ÒýºÅÀ¨ÆðÀ´¡£
¡¡¡¡·Target —— target²ÎÊý¿ÉÒÔʹһ¸öÊý¾Ý¿â¶ÔÏó»òÕßÒ»¸ö²éѯ¡£
¡¡¡¡·Object —— Êý¾Ý¿â¶ÔÏóµÄÃû×Ö£¬±ÈÈç±í»òÕßÊÓͼµÄÃû³Æ¡£¶ÔÏóµÄÍêÕûÃû×Ö±ØÐëÌṩ£¬ËüÃDz»ÐèÒªÓõ¥ÒýºÅÀ¨ÆðÀ´¡£
¡¡¡¡·Query —— queryÊÇ´ÓÔ¶³ÌÊý¾ÝÔ´ÌáÈ¡Êý¾ÝµÄSelectÓï¾ä¡£Query±ØÐëÓõ¥ÒýºÅÀ¨ÆðÀ´¡£
¡¡¡¡ÏÂÃæµÄÀý×ÓչʾÁËOPENROWSETº¯ÊýµÄÓ÷¨£º
¡¡
Ïà¹ØÎĵµ£º
ϵͳ»·¾³£ºWindows 7
Èí¼þ»·¾³£ºVisual C++ 2008 SP1 +SQL Server 2005
±¾´ÎÄ¿µÄ£º±àдһ¸öº½¿Õ¹ÜÀíϵͳ
ÕâÊÇÊý¾Ý¿â¿Î³ÌÉè¼ÆµÄ³É¹û£¬ËäÈ»³É¼¨²»¼Ñ£¬µ«ÊÇ×÷ΪÎÒÓÃVC++ ÒÔÀ´±àдµÄ×î´ó³ÌÐò»¹ÊÇ´«µ½ÍøÉÏ£¬ÒÔ¹©²Î¿¼¡£ÓÃVC++ ×öÊý¾Ý¿âÉè¼Æ²¢²»ÈÝÒ×£¬µ«Ò²²»ÊDz»¿ÉÄÜ¡£ÒÔÏÂÊÇÎҵijÌÐò½çÃæ£¬ºóÃæ ......
Ò»¡¢¹ØÓÚ»ù´¡±í
Oc_COJ^c680758
rd-A6z\&[1R1] H680758
Oracle
10G֮ǰ£¬ÆôÓÃAUTOTRACE¹¦ÄÜÐèÒªÊÖ¹¤´´½¨plan_table±í£¬´´½¨½Å±¾Îª$ORACLE_HOME/rdbms/admin
/utlxplan.sql¡£µ«ÔÚ10gÖУ¬ÒѾĬÈÏ´´½¨ÁËPLAN_TABLE$µÄ»ù±í£¬²¢ÒÔpublicÓû§´´½¨ÁËÏàÓ¦µÄͬÒå´ÊPUBLIC¡£ITPUB¸öÈ˿ռäDR#IlHrT
ITPUB¸ ......
ÈÕÖ¾¶ÁÈ¡Æ÷Ö»½«ÒѾCOMMITµÄÊÂÎñ´«Ë͵½·Ö·¢Êý¾Ý¿â¡£
²âÊÔ·½·¨£º
1. ÔÚ·¢²¼Êý¾Ý¿âÖ´ÐУº
begin tran
insert testTable2 (aaa,bbb,ddd,ccc)
values ('jawefwao','jfowijef','jaiwejfo','civjoiw')
insert testTable2 (aaa,bbb,ddd,ccc)
values ('jawefwao2','jfowijef2','jaiwejfo2','civjoiw2')
insert& ......
IN
È·¶¨¸ø¶¨µÄÖµÊÇ·ñÓë×Ó²éѯ»òÁбíÖеÄÖµÏàÆ¥Åä¡£
EXISTS
Ö¸¶¨Ò»¸ö×Ó²éѯ£¬¼ì²âÐеĴæÔÚ¡£
±È½ÏʹÓà EXISTS ºÍ IN µÄ²éѯ
Õâ¸öÀý×ӱȽÏÁËÁ½¸öÓïÒåÀàËÆµÄ²éѯ¡£µÚÒ»¸ö²éѯʹÓà EXISTS ¶øµÚ¶þ¸ö²éѯʹÓà IN¡£×¢ÒâÁ½¸ö²éѯ·µ»ØÏàͬµÄÐÅÏ¢¡£
USE pubs
GO
SELECT DISTINCT pub_name
from publishers
WHERE ......