en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
יונ16

Written by: ronen ariely
16/06/2011 22:09 RssIcon

linked server – part 2

הקדמה

לפני כמה ימים העלתי לאוויר מדריך קצר על linked server בהמשך לדיון בפורום. בחלק זה אני אוסיף למדריך מספר נקודות חשובות למחשבה בהמשך לשאלות שעלו ובכלל. כמו כן אצרף עזרים שונים (שאילתות וקישורים) והארות והערות לנקודות שונות שעלו דיון. חלק זה אינו מדריך ואינו עומד בפני עצמו. לפני המשך הקריאה אני ממליץ לעבור על החלק הראשון הכולל את המדריך לשימוש ב linked server לעומק.

לפתיחת המדריך לשימוש ב linked server אפשר להשתמש בקישור זה.

אני אנצל את חלק זה גם להוספת מספר מדריכים קטנים לביצוע פעולות שלא הוזכרו במדריך המקורי (ייתכן שבהמשך הדרך אעביר חלקים אלו למדריך המקורי).

 

מדריך הגדרת פרמטרים של קישר לשרת

לצערי קיים באג מוכר בזמן ביצוע שינויים בהגדרות המאפיינים של קישור לשרת בממשק ה GUI של תוכנת ה SSMS. מצאתי ברשת מספר הודעות של מי שהציגו עצמן כנציגים של מייקרוסופט אשר טענו שהדבר יתוקן בגרסה הבאה (ההודעה היתה בגרסה 2005). נכון לגרסה הנוכחית 2008r2 בזמן כתיבת תוכן עדיין התקלה קיימת ומוכרת.

http://connect.microsoft.com/SQLServer/feedback/details/573861/editing-linked-server-properties-throws-ad-hoc-updates-error

ניסיון לבצע שינוי בקישור לשרת קיים בממשק הגרפי תוביל להודעת השגיאה הבאה:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

Ad hoc updates to system catalogs are not allowed. (Microsoft SQL Server, Error: 259)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1777&EvtSrc=MSSQLServer&EvtID=259&LinkId=20476

אם חייבים לעבוד עם הממשק הרפי הפתרון פשוט: למחוק את הקישור לשרת הקיים ולהגדיר קישור חדש באותו שם. במדריך זה נראה כיצד מבוצע הדבר בעזרת שאילתות תוך שימוש בפרוצדורה השמורה sp_serveroption.

הגדרת פרמטרים של קישור לשרת בעזרת הפרוצדורה השמורה sp_serveroption

http://msdn.microsoft.com/en-us/library/aa260299(v=sql.80).aspx

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sprocs/html/sprocs/sprocs192.htm

-- שינוי הגדרת תכונות של לינק סרבר:

EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'use remote collation', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'MyLinkedServer', @optname=N'remote proc transaction promotion', @optvalue=N'true'

GO

* המלצה: אם לא זוכרים את אחת האפשרויות תמיד אפשר להעזר במדריך זה או בקישורים מעל. עם זה מצאתי שינה תמיד דרך יעילה להיזכר בשאילתות והיא לבצע פעולה בממשק ה GUI ואז לייצא את התוצאה כשאילתה.

טבלת העזר spt_values

במדריך זה אנו נעשה שימוש בטבלת מערכת בשם spt_values. זוהי טבלת עזר של שרת ה SQL המשמש למשל לתירגום של קוד לטקסט. טבלה זו היא זכרון מתקופת ה Sybase וייתכן שלא תופיע בגרסאות הבאות של השרת.

הטבלה כוללת עמודת סוג שהיא מהן הגדרת לשם מה אנו עושיםש שימוש ברשומות אלו, עמודת מיספור של הנתונים של סוג זה ועמודת הערך הטקסטואלי בו רוצים לעשות שימוש. ניתן למשל למצוא בטבלה זו תחת סוג "P" פשוט רשימת מספרים רציפים מאפס ועד 2047 לעזרה.

במדריך זה נעזר בטבלה לתרגום נתונים מטבלת ה sysservers. כאשר נאסוף את הנתונים מהטבלה מסוג type='A', נוכל למצוא בעמודת המספרים רשימה של מספרי חזקה 2:  1, 2, 4, 8, 16, 32, 64……4096

בעזרת מספרים אלו מגדירים סטאטוסים של השרת, כאשר בעמודה הראשונה מוצמד לכל מספר שם של הסטאטוס. אלו הן תכונות שניתן להגדיר ב Linked Server. ניתן לראות רשימה והסברים לתכונות בקישור הבא:

http://msdn.microsoft.com/en-us/library/aa260299(v=sql.80).aspx

ניתן לראות למעלה במדריך זה כיצד אנו מגדירים תכונות אלו בעזרת שימוש בפרוצדורה השמורה sp_serveroption.

select * from spt_values where type='A'

שאילתות עזר שונות - 1

* המלצה: אם אינך יכול חלק זה בצורה ברורה (למשל מעברי שורות), אז מומלץ להעתיק אותו ל SSMS. חלק זה נכתב כולו בצורה שהוא מותאם להעתקה ישירות לשאילתות.

--------------------------------------------------------------

------------------------------- סקריפט לבדיקה של התכונות שהוגדרו ב Linked Server שלנו

--  http://www.dbforums.com/sybase/1628631-help-needed-regarding-sp_serveroption.html

-- שאילתה זו עוברת על התכונות של השרת ובודקת אם התכונה הוגדרה על ידנו או לא

-- נוכל להעזר בשאילתה זו למשל אם איננו בטוחים אם הגדרנו פרמטר כלשהו או לא

select v.name ServerOption , s.srvstatus & v.number

,case when s.srvstatus & v.number = v.number then 'True' else 'False' end

from master..sysservers s, master..spt_values v

where v.type='A'

  and v.number>0

  and s.srvname='MyLinkedServerA'

-- ניתן לשנות את הערכים בעזרת השאילתות המופיעות מעל

-- ולהריץ שוב לראות את התוצאה של השינוי


--------------------------------------------------------------

--------------------------------------------- get the value of LinkedServer Option:

select

      v.name ServerOption

      ,s.srvstatus & v.number

      ,*

from sysservers s, spt_values v

where

      v.type='A'

      --and s.srvstatus & v.number != 0

      and v.number>=0

      and s.srvname='MyLinkedServerA'

 

--------------------------------------------------------------

----------------------------------------- get the value of LinkedServer Option:

select * from sysservers

where srvname='MyLinkedServer'


select distinct ConnectionProperty('כאן נרשום את התכונה שמחפשים')

from MyLinkedServerA.DataBase.dbo.MyTbl

--------------------------------------------------------------

----------------------------------------------------- מחיקת אלמנט התחברות לשרת

-- drop remote logins

sp_dropremotelogin LogInName

GO

--------------------------------------------------------------

----------------------------------------Tests the connection to a linked server.

sp_testlinkedserver MyLinkedServer

GO

מעט הרחבה לגבי הפרמטר: @provstr

מדריך זה אינו מדריך תכנות או פיתוח ולכן אני אשתמש  בהגדרה בסיסית של המושג שרשרת התחברות (Connection String) בהקשר של המדריך בלבד. לפרטים נוספים ואולי הגדרה יותר מדויקת יותר ניתן להיעזר בידידנו גוגל.

האם הגדרות שרשרת התחברות הן למעשה הגדרות השרת שלנו?

בנקודה זו היה בלבול לא קטן בדיונים שערכנו. שרשרת ההתחברות אינה קשורה להגדרות של השרת שלנו, כי אם מגדירה לאפליקציות חיצוניות העושות שימוש בשרת את הגדרות הפניה לשרת כאשר הן עובדות תחת אובייקט התחברות כגון OLEBD או SQLClient ואחרים. בדיוק כמו שאנו יכולים להגדיר לשרת ה SQL המקומי שלנו מאפייני ברירת מחדל שונים כגון כמה זמן לאפשר לכל שאילתה לרוץ הרי שגם לקישור לשרת שלנו ישנם הגדרות בסיס שלו (דיון יותר מעמיק בחלק זה נעשה למעלה).

כאשר אנו ניגשים למסד נתונים רגיל מאפליקציה חיצונית כמו אתר אינטרנט אנו מגדירים את הקשר בין האפליקציה לבין השרת תוך שימוש באחד מהאובייקטים של התקשרות למסדי נתונים. כמובן שיש לבחור אובייקט התואם הן לשרת והן לאפליקציה שלנו. בהגדרת קשר זה אנו נעזרים בשרשרת ההתחברות. אך מה נעשה כאשר נרצה לפנות מאפליקציה חיצונית אל ה Linked Server שלנו? האם הגדרות השרת המקומי תמיד זהות לשרת המקושר?!? כמובן שלא!

אנו רוצים שהחיבור לשרת המקושר יהיה שקוף מבחינת המשתמש (האפליקציה החיצונית) לכן עלינו לאפשר למשתמש לגשת לשרת הראשי ולאובייקטים בשרת המרוחק ללא צורך בהגדרה נוספת של שרשרת התחברות אל השרת המרוחק. כאמור השרת המרוחק מבחינת המשתמש והאפליקציה החיצונית אמור להיות שקוף.

אז כיצד נבצע את זה?

בנקודה זו מגיע לעזרתנו המאפיין @provstr. מאפיין זה יקבל את שרשרת ההתחברות אל הנתונים המרוחקים אליהם אנו מקשרים.

* יש לשים לב!
פרמטרים של קישור לשרת ופרמטרים של שרשרת התחברות בעזרת provider אינם זהים. ניתן לשנות את הגדרות הקישור לשרת כמוראה למעלה בעזרת הפרוצדורה sp_serveroption. הדבר לא ישפיע על שרשרת ההתחברות והפוך... שינוי שרשרת ההתחברות לא ישפיע על מאפייני הקישור לשרת שלנו.

כיצד ניגש לפרמטרים של השרת?

פרמטרים של השרת מוגדרים טבלת ה sysservers. גישה לשם צפייה בפרמטרים אלו נעשה על ידי גישה לטבלה זו (מספר דוגמאות ניתן לראות למעלה).

select * from sysservers where srvname='MyLinkedServer'

כיצד ניגש לפרמטרים של שרשת ההתחברות?

כל הפרמטרים של שרשרת ההתקשרות נמצאים בתוך פרמטר אחד של הקישור לשרת. לכן אנו יכולים לגשת לנתון בודד זה על מנת להוציא ממנו את כל הנתונים של שרשרת ההתקשרות שאנו רוצים.

select providerstring from sysservers where srvname='MyLinkedServer'

 

עתה אחרי שהוצאנו את שרשרת ההתחברות ניתן לפרק אותה לרכיבים ולהוציא את הנתון שמחפשים.

* אני מקווה שכולם מחזיקים פונקצית SPLIT בשרת. בעזרת פונקציה זו נוכל לפרק את כל הנתונים שיש לנו בשרשרת ההתחברות על מנת למצוא את המאפיין המסוים בו אנו רוצים לעשות שימוש

declare @Str as nvarchar(1000);

select @Str = (select providerstring from master.sys.sysservers where srvname='MyLinkedServer')

select * from SplitString(@Str,';')

נוכל כמובן להוסיף סינון בשאילתה בעזרת where ולהוציא נתון מסוים בלבד כגון ערך מאפיין שהוגדר על ידיד המשתמש.

* הערה: חלק מאוייקטי החיבור למסד הנתונים (לא כולם!) מאפשרים להוסיף בשרשרת ההתחברות מאפיינים לא מובנים. הדבר מאפשר למפתחים להעביר פרמטרים שונים בין האפליקציה למקור הנתונים.

** דוגמה לשימוש יכולה להיות למשל מציאת ערך מאפיין כלשהו ושימוש בו באלמנט VIEW על מנת לסנן נתונים שהמשתמש רואה כשאר הוא ניגש לשרת דרך "קישור לשרת". חשוב לזכור שלא מדובר על אבטחה ברמת הרשאות! אם המשתמש יכול לגשת למסד הנתונים דרך אפליקציה אחרת כגון SSMS הרי שהוא יוכל להגדיר שרשרת התחברות שתעקוף הגדרות שהכנסנו אם לא נפעל בזהירות!

שאילתות עזר שונות - 2

-- Get Current Connection's Properties

SELECT

ConnectionProperty('net_transport') AS 'Net transport',

ConnectionProperty('protocol_type') AS 'Protocol type',

ConnectionProperty('protocol_type') AS 'Protocol type',

ConnectionProperty('auth_scheme') AS 'Protocol type',

ConnectionProperty('auth_scheme') AS 'Protocol type',

ConnectionProperty('local_net_address') AS 'Protocol type',

ConnectionProperty('local_tcp_port') AS 'Protocol type',

ConnectionProperty('client_net_address') AS 'Protocol type'

 

-- Get Connection's Properties for all Connections

select

      a.SESSION_ID, a.CONNECT_TIME, a.NET_TRANSPORT, a.AUTH_SCHEME, b.HOST_NAME,b.PROGRAM_NAME,b.LOGIN_NAME,

      case b.quoted_identifier when 0 then 'OFF'else 'ON' end as 'QuotedIdentifierSettings',

      case b.arithabort when 0 then'OFF'else'ON' end as 'ARITHABORTSettings',

      case b.ansi_null_dflt_on when 0 then'OFF'else'ON' end as 'ANSINULLDEFAULTSettings',

      case b.ansi_defaults when 0 then'OFF'else'ON' end as 'ANSIDEFAULTSSettings',

      case b.ansi_warnings when 0 then'OFF'else'ON' end as 'ANSIWARNINGSSettings',

      case b.ansi_padding when 0 then'OFF'else'ON' end as 'ANSIPADDINGSSettings',

      case b.ansi_nulls when 0 then'OFF'else'ON' end as 'ANSINULLSSettings',

      case b.concat_null_yields_null when 0 then'OFF'else'ON' end as 'CONCATNULYIELDSNULLSettings',

      case b.transaction_isolation_level

            when 1 then 'ReadUncomitted'

            when 2 then 'ReadCommitted'

            when 3 then 'Repeatable'

            when 4 then 'Serializable'

            when 5 then 'Snapshot'

            else 'Unspecified'

            end as'TransactionalIsolationLevelSettings'

from sys.dm_exec_connections a, sys.dm_exec_sessions b

where a.session_id = b.session_id and a.parent_connection_id is null

זמן התחברות מול זמן הרצת שאילתה

נקודה נוספת שעלתה בדיון היא מדוע מגדירים Connection Time ולמרות שהשאילתה רצה מעל זמן זה אנו לא מקבלים שגיאה.

אני מקווה שאחרי שהבהרנו את ההבדל בין הגדרות שרשרת התחברות לבין הגדרות השרת ונקודה זו ברורה הרי שמובן שיש לוודא שאנחנו בכלל בודקים את הנתונים הנכונים. אבל יתרה מזאת... כדאי להזכיר ולציין שאסור לערב בין הנתון של Connection Time זמן התחברות (נתון שקיים בשם זהה גם בשרשרת התחברות באובייקטים רבים וגם במאפייני השרת) לבין הנתון של זמן הרצת השאילתה Query Timeout שניתן להגדיר במאפייני חיבור לשרת (זמן ההתחברות הוא זמן ההגעה לשרת וזמן הרצת השאילתה הוא שמן הפעלת השאילתה).

 

דברי סיום

לסיום אוכל רק להגיד שכל מה שנכתב כאן הובא כפי שמצאתי כצורה הבהירה/הברורה ביותר להעביר את הנושא, והכול מתבסס כמובן על מה שאני הבנתי מניסיוני ומקישורים שונים אליהם הגעתי בעזרתו האדיבה של גוגל. אני מקווה שהבנתי את הדברים נכונה ושהצלחתי להעביר אותם בצורה בהירה

אם יש שאלות נוספות או הערות אשמח לשמוע