SQL ServerÈçºÎ¿çʵÀý·ÃÎÊÊý¾Ý¿â
ÔÚÎÒÃÇÈÕ³£Ê¹ÓÃSQL ServerÊý¾Ý¿âʱ£¬¾³£Óöµ½ÐèÒªÔÚʵÀýInstance01ÖпçʵÀý·ÃÎÊInstance02ÖеÄÊý¾Ý¡£ÀýÈçÔÚ×öÊý¾ÝÇ¨ÒÆÊ±£¬ÈçÏÂÓï¾ä:
insert into Instance01.DB01.dbo.Table01
select * from Instance02.DB01.dbo.Table01
ÆÕͨÇé¿öÏ£¬ÕâÑù×öÊDz»ÔÊÐíµÄ£¬ÒòΪSQL ServerĬÈϲ»¿ÉÒÔ¿çʵÀý·ÃÎÊÊý¾Ý¡£½â¾ö·½°¸ÊÇʹÓô洢¹ý³Ìsp_addlinkedserver½øÐÐʵÀý×¢²á¡£
sp_addlinkedserverÔÚMSDNÖе͍ÒåΪ:
sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
[ , [ @provider= ] 'provider_name' ]
[ , [ @datasrc= ] 'data_source' ]
[ , [ @location= ] 'location' ]
[ , [ @provstr= ] 'provider_string' ]
[ , [ @catalog= ] 'catalog' ]
ÀýÈ磺ÔÚInstance01ʵÀýÖУ¬Ö´ÐÐÈçÏÂSQLÓï¾ä
EXEC sp_addlinkedserver ‘Instance02’ //ֻдµÚÒ»¸ö²ÎÊý¼´¿É£¬Ä¬ÈÏÇé¿öÏ£¬×¢²áµÄÊÇSQL ServerÊý¾Ý¿â£¬ÆäËû²ÎÊýÓ÷¨Ïê¼ûMSDN¡£
Èç¹ûÄãµÄÁ½¸öʵÀýÔÚͬһ¸öÓòÖУ¬ÇÒInstance01ÓëInstance02Óй²Í¬µÄÓòµÇ½Õʺţ¬ÄÇô¾¹ýÉÏÃæµÄ×¢²áºó£¬Ç°ÃæµÄinsertÓï¾ä¾Í¿ÉÒÔÖ´ÐÐÁË¡£·ñÔò£¬»¹ÐèÒª¶Ô×¢²áµÄÔ¶³ÌʵÀý½øÐеǽÕʺÅ×¢²á£¬ÔÚInstance01ʵÀýÖУ¬Ö´ÐÐÈçÏÂSQLÓï¾ä
EXEC sp_addlinkedsrvlogin 'InstanceName','true' //ʹÓü¯³ÉÈÏÖ¤·ÃÎÊÔ¶³ÌʵÀý
»òÕß EXEC sp_addlinkedsrvlogin 'InstanceName','false','TJVictor,'sa','Password1' //ʹÓÃWindowsÈÏÖ¤·ÃÎÊÔ¶³ÌʵÀý£¬µ±Óû§ÒÔTJVictorÓû§µÇ½Instance01ʵÀý·ÃÎÊInstance02ʱ£¬Ä¬ÈϰÑTJVictorÓ³Éä³Ésa£¬ÇÒÃÜÂëΪPassword1
¾¹ý sp_addlinkedserverʵÀý×¢²áºÍsp_addlinkedsrvloginµÇ½ÕÊ»§×¢²áºó£¬¾Í¿ÉÒÔÔÚInstance01ÖÐÖ±½Ó·ÃÎÊInstance02ÖеÄÊý¾Ý¿âÊý¾ÝÁË¡£
Èç¹û»¹ÎÞ·¨·ÃÎÊ£¬Çë¼ì²é±¾»úDNSÊÇ·ñ¿ÉÒÔ½âÎöÔ¶³ÌÊý¾Ý¿âµÄʵÀýÃû¡£Èç¹ûÎÞ·¨½âÎö£¬¿ÉÒÔÔÚEXEC sp_addlinkedserver ‘Instance02’ÖаÑInstance02»»ÎªIP£¬»òÕßÔÚhostsÎļþÖУ¬×Ô¼º½¨Á¢ÏàÓ¦DNSÓ³Éä¡£
ÏÂÃæÁоټ¸¸ö¿çʵÀýÊý¾Ý¿â·ÃÎʵĴ洢¹ý³ÌºÍÊÓͼ¡£
´æ´¢¹ý³ÌÃû/ÊÓͼÃû
×÷ÓÃ
¾ÙÀý
sp_addlinkedserver
Ïà¹ØÎĵµ£º
ÎÒÃǵÄSQLÓï¾äµÄÖ´Ðж¼ÊÇÓÉÒ»¸öÀàÀ´Íê³ÉµÄ£¡Õâ¸öÀà¾ÍÊÇSQLStatement£¬Õâ¸öÀà¾ÍÊÇÎÒÃÇÓÃÀ´Ö´ÐÐSQLÓï¾äµÄÀ࣬¸ÃÀàµÄʹÓÃÒ²ÊǷdz£¼òµ¥µÄ£¬ÎÒÃÇÖ»ÐèÒª¼ÇסÁ½¸öÊôÐÔÁ½¸ö·½·¨¡£ÎÒÃÇÀ´¿´Ò»Ï£¡
textÊôÐÔ£ºËùÒªÖ´ÐеÄSQLÓï¾ä£¬¸ÃÊôÐÔÊÇÒ»¸ö×Ö·û´®¸ñʽ£¬ËùÒÔÎÒÃǵÄSQLÓï¾ä¶¼ÊÇ×Ö·û´®£¡
sqlConnectionÊôÐÔ£º¸ÃÊôÐÔÊÇÉèÖÃSQLSt ......
/***************************************************
×÷Õߣºherowang(ÈÃÄãÍû¼ûÓ°×ÓµÄǽ£©
ÈÕÆÚ£º2010.1.5
×¢£º ×ªÔØÇë±£Áô´ËÐÅÏ¢
......
1 MySQLÖ§³Öenum,ºÍsetÀàÐÍ£¬SQL Server²»Ö§³Ö
¡¡¡¡2 MySQL²»Ö§³Önchar,nvarchar,ntextÀàÐÍ
¡¡¡¡3 MySQLµÄµÝÔöÓï¾äÊÇAUTO_INCREMENT£¬¶øMS SQLÊÇidentity(1,1)
¡¡¡¡4 MS SQLĬÈϵ½´¦±í´´½¨Óï¾äµÄĬÈÏÖµ±íʾÊÇ((0)),¶øÔÚMySQLÀïÃæÊDz»ÔÊÐí´øÁ½À¨ºÅµÄ
¡¡¡¡5 MySQLÐèҪΪ±íÖ¸¶¨´æ´¢ÀàÐÍ
¡ ......
-- create by zh
-- n ÊÇ×÷ÎïµÄʱ¼ä,x ÊÇÏ£ÍûÔÚ¼¸µã³ÉÊì,·µ»Ø²¥ÖÖµÄʱ¼ä
with t as
(
select 64 n,9 x from dual union all
select 64 n,13 x from dual union all
select 64 n,17 x from dual union all
select 64 n,20 x from dual
)
select '³ÉÊìʱ¼ä:' || lpad(to_char(n),4,' ' ......
1 ·ÀÖ¹sql×¢Èëʽ¹¥»÷(¿ÉÓÃÓÚUI²ã¿ØÖÆ£© #region ·ÀÖ¹sql×¢Èëʽ¹¥»÷(¿ÉÓÃÓÚUI²ã¿ØÖÆ£©
2
3 /**/ ///
4 /// ÅжÏ×Ö·û´®ÖÐÊÇ·ñÓÐSQL¹¥»÷´úÂë
5 ///
6 /// ´«ÈëÓû§Ìá½»Êý¾Ý
7 /// ......