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
Ïà¹ØÎĵµ£º
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
create table tb (ptoid int,proclassid int,proname varchar(10))
insert tb
select 1,1,'Ò·þ1'
union all
select 2,2,'Ò·þ2'
union all
select 3,3,'Ò·þ3'
union all
select 4,3,'Ò·þ4'
union all
select 5,2,'Ò·þ5'
union all
select 6,2,'Ò·þ6'
union all
select 7,2,'Ò·þ7'
union all
select 8 ......
¡¾1¡¿
create procedure proc_pager1
( @pageIndex int, -- ҪѡÔñµÚXÒ³µÄÊý¾Ý
@pageSize int -- ÿҳÏÔʾ¼Ç¼Êý
)
AS
BEGIN
declare @sqlStr varchar(500)
set @sqlStr='select top '+con ......
Ò»¡¢±³¾°½éÉÜ
¡¡¡¡
¡¡¡¡½á¹¹»¯²éѯÓïÑÔ(Structured Query Language£¬¼ò³ÆSQL)ÊÇÓÃÀ´·ÃÎʹØÏµÐÍÊý¾Ý¿âÒ»ÖÖͨÓÃÓïÑÔ£¬ÊôÓÚµÚËÄ´úÓïÑÔ£¨4GL£©£¬ÆäÖ´ÐÐÌØµãÊǷǹý³Ì»¯£¬¼´²»ÓÃÖ¸Ã÷Ö´ÐеľßÌå·½·¨ºÍ;¾¶£¬¶øÊǼòµ¥µØµ÷ÓÃÏàÓ¦Óï¾äÀ´Ö±½ÓÈ¡µÃ½á¹û¼´¿É¡£ÏÔÈ»£¬ÕâÖÖ²»¹Ø×¢ÈκÎʵÏÖϸ½ÚµÄÓïÑÔ¶ÔÓÚ¿ª·¢ÕßÀ´ËµÓÐ׿«´óµÄ±ãÀû¡£È»¶ø£¬Ó ......
1 ·ÀÖ¹sql×¢Èëʽ¹¥»÷(¿ÉÓÃÓÚUI²ã¿ØÖÆ£© #region ·ÀÖ¹sql×¢Èëʽ¹¥»÷(¿ÉÓÃÓÚUI²ã¿ØÖÆ£©
2
3 /**/ ///
4 /// ÅжÏ×Ö·û´®ÖÐÊÇ·ñÓÐSQL¹¥»÷´úÂë
5 ///
6 /// ´«ÈëÓû§Ìá½»Êý¾Ý
7 /// ......