sql ¿ç·þÎñÆ÷ ²éѯ
--´´½¨Á´½Ó·þÎñÆ÷
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', 'Ô¶³Ì·þÎñÆ÷Ãû»òipµØÖ· '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, 'Óû§Ãû ', 'ÃÜÂë '
--²éѯʾÀý
select * from ITSV.Êý¾Ý¿âÃû.dbo.±íÃû
--µ¼ÈëʾÀý
select * into ±í from ITSV.Êý¾Ý¿âÃû.dbo.±íÃû
--ÒÔºó²»ÔÙʹÓÃʱɾ³ýÁ´½Ó·þÎñÆ÷
exec sp_dropserver 'ITSV ', 'droplogins '
--Á¬½ÓÔ¶³Ì/¾ÖÓòÍøÊý¾Ý(openrowset/openquery/opendatasource)
--1¡¢openrowset
--²éѯʾÀý
select * from openrowset( 'SQLOLEDB ', 'sql·þÎñÆ÷Ãû '; 'Óû§Ãû '; 'ÃÜÂë ',Êý¾Ý¿âÃû.dbo.±íÃû)
--Éú³É±¾µØ±í
select * into ±í from openrowset( 'SQLOLEDB ', 'sql·þÎñÆ÷Ãû '; 'Óû§Ãû '; 'ÃÜÂë ',Êý¾Ý¿âÃû.dbo.±íÃû)
--°Ñ±¾µØ±íµ¼ÈëÔ¶³Ì±í
insert openrowset( 'SQLOLEDB ', 'sql·þÎñÆ÷Ãû '; 'Óû§Ãû '; 'ÃÜÂë ',Êý¾Ý¿âÃû.dbo.±íÃû)
select *from ±¾µØ±í
--¸üб¾µØ±í
update b
set b.ÁÐA=a.ÁÐA
from openrowset( 'SQLOLEDB ', 'sql·þÎñÆ÷Ãû '; 'Óû§Ãû '; 'ÃÜÂë ',Êý¾Ý¿âÃû.dbo.±íÃû)as a inner join ±¾µØ±í b
on a.column1=b.column1
--openqueryÓ÷¨ÐèÒª´´½¨Ò»¸öÁ¬½Ó
--Ê×ÏÈ´´½¨Ò»¸öÁ¬½Ó´´½¨Á´½Ó·þÎñÆ÷
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', 'Ô¶³Ì·þÎñÆ÷Ãû»òipµØÖ· '
--²éѯ
select *
from openquery(ITSV, 'SELECT * from Êý¾Ý¿â.dbo.±íÃû ')
--°Ñ±¾µØ±íµ¼ÈëÔ¶³Ì±í
insert openquery(ITSV, 'SELECT * from Êý¾Ý¿â.dbo.±íÃû ')
select * from ±¾µØ±í
--¸üб¾µØ±í
update b
set b.ÁÐB=a.ÁÐB
from openquery(ITSV, 'SELECT * from Êý¾Ý¿â.dbo.±íÃû ') as a
inner join ±¾µØ±í b on a.ÁÐA=b.ÁÐA
--3¡¢opendatasource/openrowset
SELECT *
from opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=µÇ½Ãû;Password=ÃÜÂë ' ).test.dbo.roy_ta
--°Ñ±¾µØ±íµ¼ÈëÔ¶³Ì±í
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=µÇ½Ãû;Password=ÃÜÂë ').Êý¾Ý¿â.dbo.±íÃû
select * from ±¾µØ±í
Ïà¹ØÎĵµ£º
SQLÖÐNµÄ×÷ÓÃ?
exec sp_executesql N'SELECT TOP 1 ISNULL(vDefine1,'''') AS vMonth, dtBegin, dtEnd from SM_ActMonth WHERE vActID=@P1 AND bCarryForward=@P2 ORDER BY iListID DESC
', N'@P1 varchar(3),@P2 bit', '001', 1
exec sp_executesql N'SELECT * from KEHU WHERE KHDM>=@P1 AND ZK=@P2',N'@P1 VARCHAR ......
1,ÏÔʾÓαêµÄ²Ù×÷
(1) ¶¨ÒåÓαê
¸ù¾Ý²éѯµÄÊý¾ÝÇé¿ö£¬ÔÚPL/SQL¿éµÄÉùÃ÷²¿·Ö¶¨ÒåÓα꣬Ó﷨Ϊ£º
cursor cursor_name is select_statement;
×¢Ò⣺
ÓÎ±ê±ØÐëÔÚPL/SQL¿éµÄÉùÃ÷²¿·Ö½øÐж¨Ò壻
......
SQL Server µÄÄ£Äâ
רÀ¸×÷¼Ò£ºDeanna Dicken 2010Äê2ÔÂ12ÈÕ
·Ò룺 UltraDBA 2010Äê5ÔÂ25ÈÕ
ÔÌûµØÖ·£ºhttp://www.databasejournal.com/features/mssql/article.php/3863516/SQL-Server-Impersonation.htm
½éÉÜ
SQL Server µÄÄ£Ä⣬»òÉÏÏÂÎÄÇл»£¬ÊÇÒ»ÖÖ·½Ê½£¬ÔÊÐíµ± ......
ÏêϸµØ²½Öè:
1¡¢´ò¿ªMicroSoft Excel 2000
2¡¢Îļþ(F)→н¨(N)→¹¤×÷²¾→
3¡¢ÊäÈëÊý¾Ýºó£¬´æÅÌΪtest.xls£¬
4¡¢Îļþ(F)→Áí´æÎª(A)→
±£´æÀàÐÍΪ£ºÖƱí·û·Ö¸ô£¬ÆðÃûΪtext.txt£¬±£´æµ½C:\ &nb ......
´´½¨Ò»ÕÅ±í£¬Ö±½ÓÔÚ±íÁÐÉϰó¶¨Ä¬ÈÏÖµ
CREATE TABLE hello
(
Id_P int PRIMARY KEY,
Firstname varchar(50) DEFAULT 'Bush',
Lastname varchar(50) DEFAULT 'Green',
Address varchar(50) DEFAULT 'China',
City varchar(50) DEFAULT 'beijing'
)
²åÈëÒ»ÌõÐÅÏ¢
INSERT INTO hello(Id_P) values(01)
½á¹ûÊÇ
×ܽ ......