גרי רשף

אנחנו ממשיכים במלאכת המעקב החשאית- לא רק מי שינה דברים (הפוסט הקודם), אלא גם- מי התחבר.. קודם כל ניצור טבלת מעקב ב-master:

USE master

וכעת ניצור טריגר DDL מתאים:

Create TRIGGER Prevent_Apps_logon ON ALL SERVER FOR LogOn AS

BEGIN Insert Into T_Maakav Select GetDate() Taarih, User_Name() [User_Name],System_User [System_User], App_Name()[App_Name], Host_ID( ) [Host_ID], Host_Name( ) [Host_Name], @@SPID [SPID] Where OBJECT_ID('master.dbo.T_Maakav') Is Not Null And App_Name()<>'Report Server' END

תנאי ה-Where הראשון הוא קריטי: בלעדיו אנחנו עלולים להיתקע מחוץ לשרת מבלי יכולת להיכנס, אם מישהו בטעות ימחק את טבלת המעקב! התנאי השני נועד להדגים כיצד לסנן הודעות מיותרות: הטבלה תתמלא מהר מאוד על ידי תהליכים שרצים ברקע ומתחברים לשרת, ואשר אנו לא מעוניינים בהם.

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

Create Trigger TrgEventLog On Database For DDL_Database_Level_Events As Insert Into DDLEventLog(Taarih, UserName, ObjectName, CommandText, EventType, DatabaseName, SchemaName, SpID, LoginName) Select EventData().value('(/EVENT_INSTANCE/PostTime)[1]', 'DateTime') Taarih, EventData().value('(/EVENT_INSTANCE/UserName)[1]','VarChar(Max)') UserName, EventData().value('(/EVENT_INSTANCE/ObjectName)[1]','VarChar(Max)') ObjectName, EventData().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VarChar(Max)') CommandText, EventData().value('(/EVENT_INSTANCE/EventType)[1]','VarChar(Max)') EventType, EventData().value('(/EVENT_INSTANCE/DatabaseName)[1]','VarChar(Max)') DatabaseName, EventData().value('data(/EVENT_INSTANCE/SchemaName)[1]','VarChar(Max)') SchemaName, EventData().value('(/EVENT_INSTANCE/SPID)[1]', 'VarChar(Max)') SpID, EventData().value('(/EVENT_INSTANCE/LoginName)[1]','VarChar(Max)') LoginName;

וכעת לבדיקה ננסה ליצור טבלה, לבטל אותה, ולראות מה מופיע בטבלת המעקב:

Create Table MyTest(I Int); Go Drop Table MyTest; Go Select * From DDLEventLog;

לא הכי שימושי, אבל אתגר נצחי לחובבי הז'אנר:

במקרה זה מוכפלים המספרים 1..1000 אלו באלו, ונשלפות המכפלות שמופיעות רק פעמיים: אלו הם המספרים הראשונים שנוצרים רק ממכפלה ב-1. ודרך שונה:

Declare @N Int=1000; With T As (Select 2 Mispar Union All Select Mispar+1 From T Where Mispar<@N) Select Mispar From T T1 Where Not Exists (Select * From T T2 Where T2.Mispar<=Sqrt(T1.Mispar) And T1.Mispar%T2.Mispar=0) Order By 1 option (MaxRecursion 0);

במקרה זה מחפשים את כל המספרים מ-2 ואילך שאין מספר אחר שהם מתחלקים בו ללא שארית (נבדקים כל המספרים מ-2 ועד שורש המספר - מטעמי יעילות). ולבסוף- הדרך הכי יעילה שמצאתי: קצת דומה לראשונה אבל היישום שונה. יוצרים על ידי ה-CTE הרקורסיבי את המספרים מ-2 עד 1000 (1 קצת מיותר..), יוצרים CTE רקורסיבי נוסף שעבור כל אחד מהמספרים של הקודם- יוצר את כל המכפלות שלו עד 1000, ולבסוף בודקים איזה מספר הופיע רק פעם אחת:

Declare @N Int=1000; With T As (Select 2 Mispar Union All Select Mispar+1 From T Where Mispar<@N), Tdpl As (Select Mispar Mispar, Mispar MisparDpl From T Union All Select Mispar, MisparDpl+Mispar From Tdpl Where MisparDpl<=@N) Select MisparDpl From Tdpl Where MisparDpl<=@N Group By MisparDpl Having Count(*)=1 Order By 1 option (MaxRecursion 0);

בשני המקרים ניתן להריץ עד מספר אחר (במקום עד 1000) על ידי שינוי ערכו של N@.

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

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

Create Table #T_Mosadot(Shem VarChar(Max),Yamim VarChar(Max))

Insert Into #T_Mosadot Select 'בית א','ב,ג,ש'

Insert Into #T_Mosadot Select 'בית ב','ג,ה'

Insert Into #T_Mosadot Select 'בית ג','א'

Insert Into #T_Mosadot Select 'בית ד','א,ב,ג,ד,ה,ו,ש'

Insert Into #T_Mosadot Select 'בית ה',''

מתנצל על התצוגה המשובשת בעברית.. את המידע נשלוף כך:

Select *

From #T_Mosadot M

Left Join #T_Yamim Y

On ','+M.Yamim+',' Like '%,'+Y.Shem+',%'

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

פתרון אחר שיכול להתאים לרשימות קצרות (כמו רשימת ימי השבוע למשל) הוא לציין בטבלת המוסדות מספר עשרוני שמייצג מספר בינארי בן 7 ספרות לכל היותר (0-127) כך שהספרה הראשונה מציינת אם הוא פתוח ביום א' (1) או לא (0), השניה מייצגת את יום ב' וכך הלאה. בבית א' למשל נכתוב 70 שבבינארית הוא 10000110  ועל פיו המוסד פתוח בימים ב,ג,ש; ובאופן דומה בשאר הבתים. נמחק את הטבלה הקודמת וניצור אחת חדשה:

Drop Table #T_Mosadot

Create Table #T_Mosadot(Shem VarChar(Max),Yamim Int)

Insert Into #T_Mosadot Select 'בית א',70

Insert Into #T_Mosadot Select 'בית ב',20

Insert Into #T_Mosadot Select 'בית ג',1

Insert Into #T_Mosadot Select 'בית ד',127

Insert Into #T_Mosadot Select 'בית ה',0

וכעת נשלוף את הרשימה:

Select *

From #T_Mosadot M

Left Join #T_Yamim Y

On M.Yamim%Power(2,Y.Yom)>=Power(2,Y.Yom-1)

26/09/2010:  דרך חלופית לשליפה האחרונה- בעזרת Bitwise And:

Select * From #T_Mosadot M Left Join #T_Yamim Y On M.Yamim & Power(2,Y.Yom-1)<>0;

לפני מספר שנים עבדתי על מערכת דוחות ב-Reporting Services שהתבססה על קוביות OLAP. אחד הדוחות היה מאוד מורכב ולא ניתן היה לבצע אותו בעזרת MDX (כלומר- שליפה מקוביית ה-OLAP), ונאלצנו בלתי ברירה להתבסס על טבלת ה-Fact  שב-SQL Server (טבלת המקור של הקוביה). זה יצר בעייה חדשה: ב-OLAP מומשה מערכת הרשאות שאיפשרה למנהל של כל אזור לראות רק את הנתונים של האזור שלו, אבל כל זה לא היה קיים בטבלת ה-Fact, והדוח שיצרנו היה מאפשר לכל אחד לעיין בכל הנתונים.. מה עושים? לבנות מערכת הרשאות מקבילה ב-SQL Server זה בלתי אפשרי- תקציב הפיתוח כבר נוצל, היינו אמורים לסיים בהקדם את הדוחות, ולהקמת מערכת כזו נדרש אפיון, פיתוח, בדיקות וכו'.. בסוף נמצא פתרון פשוט שלא הצריך כל שינוי: הוספנו לשליפת ה-SQL הנ"ל (מתוך טבלת ה-Fact) תנאי מהסוג של (..) Where Ezor In כשהביטוי בתוך הסוגריים היה שליפה של האזורים מתוך ה-OLAP (מה שהבטיח שכל אחד קיבל רק את האזורים להם הייתה לו הרשאה). כיצד שולפים אם כך בעזרת שאילתת SQL נתונים מה-OLAP (מדובר בדוגמה שבניתי בבית ולא במקרה המסויים הנ"ל): 1. ניצור Linked Server לקוביה (צירפתי צילום מסך של ה-Object Explorer):

EXEC sp_addlinkedserver

@server='OlapServer',                           --שם שבחרתי

@srvproduct='',

@provider='MSOLAP',                             --שם שמור

@datasrc='USER-1FC9E5F6D8\SQL2008',       --Object Explorer => OLAPשם שרת ה

@catalog='Analysis Services Tutorial'           --Object Explorer => Databases לבחור

2. נשלוף נתונים מהקוביה כך (יש לבנות MDX מתאים):

SELECT "[Product].[Product Line].[Product Line].[MEMBER_CAPTION]" As Product

FROM OPENQUERY(OlapServer,'Select   [Measures].[Internet Sales-Total Product Cost] On 0,

[Product].[Product Line].[Product Line] On 1

From     [Analysis Services Tutorial];')

Order By "[Measures].[Internet Sales-Total Product Cost]";

בדוגמה זו ניתן לראות כיצד להתייחס לשמות ה"שדות" מחוץ לפונקציית OpenQuery. 3. ב-SQL 2008 ניתן לשלוף גם כך:

EXEC ('Select     [Measures].[Internet Sales-Total Product Cost] On 0,

[Product].[Product Line].[Product Line] On 1

From  [Analysis Services Tutorial];') AT [OlapServer];

4. ניתן לשלוף גם ללא יצירת Linked Server:

SELECT a.* FROM OpenRowset('MSOLAP','DATASOURCE=USER-1FC9E5F6D8\SQL2008; Initial Catalog= Analysis Services Tutorial;', 'Select [Measures].[Internet Sales-Total Product Cost] On 0 From [Analysis Services Tutorial];') as a

הפואנטה בשלוש השליפות הנ"ל (סעיפים 2-4) היא שניתן לפמות בהן גם לקוביה וגם לטבלאות רגילות ולבצע בינהן Join או התניות אחרות.