Lm th? no ? v?t qua m?t bi?n v?i m?t truy v?n my ch? ?c lin k?t

D?ch tiu ? D?ch tiu ?
ID c?a bi: 314520 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny

TM T?T

Bi vi?t ny m t? cch ? v?t qua m?t bi?n v?i m?t truy v?n my ch? ?c lin k?t.

Khi b?n truy v?n m?t my ch? ?c lin k?t, b?n th?ng xuyn th?c hi?n m?t truy v?n pass-through s? d?ng OPENQUERY, OPENROWSET ho?c OPENDATASOURCE tuyn b?. B?n c th? xem cc v d? trong SQL Server sch m?ng ? xem lm th? no ? lm i?u ny b?ng cch s? d?ng ?c xc ?nh tr?c Transact-SQL strings, nhng khng c khng c v d? v? lm th? no ? v?t qua m?t bi?n v?i cc ch?c nng ny. Bi vi?t ny cung c?p ba v d? v? lm th? no ? v?t qua m?t bi?n v?i m?t truy v?n my ch? ?c lin k?t.

? v?t qua m?t bi?n v?i m?t trong cc ch?c nng pass-through, b?n ph?i xy d?ng m?t truy v?n nng ?ng.

B?t k? d? li?u no bao g?m cc bo gi nhu c?u c? th? x? l?. ? bi?t thm chi ti?t, xem ch? ? "s? d?ng char v varchar d? li?u" trong SQL Server sch tr?c tuy?n v xem bi vi?t sau trong c s? ki?n th?c Microsoft:
156501 INF: QUOTED_IDENTIFIER v b? dy v?i d?u ngo?c kp n

V?t qua gi tr? c b?n

Khi Transact-SQL tuyn b? c b?n ?c bi?t ?n, nhng b?n ph?i v?t qua trong m?t ho?c nhi?u gi tr? c? th?, s? d?ng m? s? l tng t? nh m?u sau:
      DECLARE @TSQL varchar(8000), @VAR char(2)
      SELECT  @VAR = 'CA'
      SELECT  @TSQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM pubs.dbo.authors WHERE state = ''''' + @VAR + ''''''')'
      EXEC (@TSQL)
				

V?t qua ton b? truy v?n

Khi b?n ph?i v?t qua trong cc truy v?n Transact SQL ton b? ho?c tn my ch? ?c lin k?t (ho?c c? hai), s? d?ng m? s? l tng t? nh m?u sau:
DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = 'MyLinkedServer'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT au_lname, au_id FROM pubs..authors'')' 
EXEC (@OPENQUERY+@TSQL) 
				

S? d?ng Sp_executesql lu tr? th? t?c

? trnh cc d?u ngo?c kp a l?p, s? d?ng m? s? l tng t? nh m?u sau:
DECLARE @VAR char(2)
SELECT  @VAR = 'CA'
EXEC MyLinkedServer.master.dbo.sp_executesql
     N'SELECT * FROM pubs.dbo.authors WHERE state = @state',
     N'@state char(2)',
     @VAR
				

THAM KH?O

? bi?t thm chi ti?t, xem cc ch? ? sau trong SQL Server sch tr?c tuy?n:
"OPENROWSET"
"OPENQUERY"
"OPENDATASOURCE"
"B?ng cch s? d?ng sp_executesql"
"sp_executesql"

Thu?c tnh

ID c?a bi: 314520 - L?n xem xt sau cng: 27 Thang Tam 2011 - Xem xt l?i: 2.0
p d?ng
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
T? kha:
kbhowtomaster kbmt KB314520 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:314520

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com