הבלוג של גרי רשף

09/06/2010

האח הגדול עינו פקוחה (6)- זיהוי עצמי בזמן ריצה

Filed under: Uncategorized — תגיות: , , , , , , , — גרי רשף @ 15:50

לפרוצדורה MyProc שרצה בדטבייס MyDB בשרת MyServer אין בעייה "לדעת" מי והיכן היא, אלא שלעיתים אנו מוסיפים לאובייקטים תכנותיים כמו פרוצדורות או טריגרים או ג'ובים שגרות שגיאה סטנדרטיות או דיווחים סטנדרטיים  שכוללים כתיבה לטבלת Log, ואנחנו מחפשים דרך להימנע מהצורך לשנות בכל אובייקט את תוכן הפקודה (פקודה כזו בוודאי תכלול את שם הפרוצדורה בתוכה היא רצה).
חשוב להדגיש כבר כאן שזה יהיה רעיון לא מוצלח אם מדובר באובייקטים פשוטים שרצים מספר רב מאוד של פעמים, ומחיר עדכון טבלת הלוג עלול ליצור עומס בלתי נסבל (לגבי מעקב שגיאות- נניח באופן אופטימי ששגיאות לא קורות בכל יום :-)).
ניצור לצורך ההדגמה טבלת מעקב, טבלת מעקב שגיאות,  ופרוצדורה שמעדכנת את טבלת המעקב בתחילת כל ריצה, וכשנוצרת בה שגיאה (ניצור שגיאה מכוונת על ידי חלוקת מספר ב-0) – מעדכנת את טבלת מעקב השגיאות:

Use tempdb;

Go

Create Table T_Maakav([Date] Datetime ,

                    Server_Name Varchar(Max),

                    Instance Varchar(Max),

                    [DB_Name] Varchar(Max),

                    [Object_Name] Varchar(Max),

                    [User_Name] Varchar(Max),

                    [System_User] Varchar(Max),

                    [App_Name] Varchar(Max),

                    [Host_Name] Varchar(Max),

                    SPID Int);

Go

 

Create Table T_MaakavShgiot([Date] Datetime,

                            SPID Int,

                            [Error_Line] Int,

                            [Error_Message] Varchar(Max),

                            [Error_Number] Int,

                            [Error_Procedure] Varchar(Max),

                            [Error_Severity] Int,

                            [Error_State] Int);

Go

 

Create Proc MyProc As

Begin Try

    Insert Into T_Maakav

    Select    GetDate() [Date],

            @@ServerName Server_Name,

            @@ServiceName Instance, --MSSQLSERVER = No Instance

            DB_Name() [DB_Name], 

            Object_Name(@@ProcID) [Object_Name],

            User_Name() [User_Name],

            System_User [System_User],

            App_Name() [App_Name],

            Host_Name() [Host_Name],

            @@SPID [SPID]

    Begin Tran

    Declare @I Int= 3/0;--חלוקה ב-0

    Commit

End Try

Begin Catch

    RollBack

    Insert Into T_MaakavShgiot

    Select    GetDate() [Date],

            @@SPID [SPID],

            Error_Line(),

            Error_Message(),

            Error_Number(),

            Error_Procedure(),

            Error_Severity(),

            Error_State()

End Catch;

Go

נפעיל את הפרוצדורה על ידי Exec tempdb..MyProc, ולצורך ההשוואה אפשר ליצור ג'וב חדש שמריץ את הפרוצדורה ונפעיל אותו על ידי 'Exec msdb..sp_start_job 'J_Try001 (בהנחה שזה שמו) ונעיין בתכולתן של שתי הטבלאות:

Select * From T_Maakav Order By [Date];

Select * From T_MaakavShgiot Order By [Date];

Go

שוב- פקודות ה-Insert יכולות להיות מועתקות כמו שהן לכל אובייקט אחר מבלי לשנות בהן דבר, וזה מה שהן נועדו להמחיש (זיהוי עצמי).

מה קורה כאשר מדובר בפונקציות משתמש (UDF ושוב- אני מניח שמדובר בפונקציות שרצות מספר מועט של פעמים)?

לא ניתן לעדכן טבלאות מתוך פונקציות וגם לא להפעיל פרוצדורות שיעדכנו בעצמן בעקיפין את הטבלה. דרך מעט צולעת לעקוף את הבעייה היא להשתמש ב-xp_CmdShell (באורח פלא מותר להפעיל Extended Stored Procedures): מפעילים בעזרתה פקודת SQLCmd שמעדכנת את הטבלה..

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

Create Function dbo.MyFunc(@X Float) Returns Float As

Begin

Declare    @SQL Varchar(8000);

Set        @SQL=

'sqlcmd -E -S ' + @@ServerName + ' -Q '

+'"Insert Into tempdb..T_Maakav '

+'Select    ''' + Convert(NVarChar(20),GetDate(),112)+' '+Convert(NVarChar(20),GetDate(),114) + ''' [Date],'

+        '''' + @@ServerName + ''' Server_Name,'

+        '''' + @@ServiceName + ''' Instance,'

+        '''' + DB_Name() + ''' [DB_Name],'

+        '''' + IsNull(Object_Name(@@ProcID),'') + ''' [Object_Name],'

+        '''' + User_Name() + ''' [User_Name],'

+        '''' + System_User + ''' [System_User],'

+        '''' + App_Name() + ''' [App_Name],'

+        '''' + Host_Name() + ''' [Host_Name],'

+        '' + Cast(@@SPID As Varchar) + ' [SPID];"';

Exec    xp_CmdShell @SQL;

Return @X+@X;

End;

Go

הנחתי שמעדכנים את השרת ולא את אחד ה-Instances שלו, אחרת- יש לציין את שם השרת וה-Instance ליד המתג המתאים של ה-SQLCmd.

לא ניתן לצרף שגרת שגיאה- השימוש ב-Try & Catch אינו אפשרי בפונקציות!

בג'ובים של SQL Server הבעייה היא זיהוי שם הג'וב: ניתן, אם רוצים, לכתוב אותו במפורש בתוך הג'וב – זה לא כל כך מסובך, אבל מי שמתעקש לשלוף את שמו תוך כדי ריצה- הנה הפתרון המעט מסורבל (כולל שגרת שגיאה):

Begin Try

    Declare    @SQL NVarchar(Max),

            @jobID UniqueIdentifier,

            @jobName Sysname;

    Set        @SQL = 'SET @guid = Cast(' + SubString(App_Name(), 30, 34) + ' As UniqueIdentifier)'

    Exec    SP_ExecuteSQL @SQL, N'@guid UniqueIdentifier Out', @guid = @jobID Out

    Set        @jobName = (Select name From msdb..sysjobs Where job_id=@jobID);

    Insert Into tempdb..T_Maakav

    Select    GetDate() [Date],

            @@ServerName Server_Name,

            @@ServiceName Instance, --MSSQLSERVER = No Instance

            DB_Name() [DB_Name], 

            @jobName [Object_Name],

            User_Name() [User_Name],

            System_User [System_User],

            App_Name() [App_Name],

            Host_Name() [Host_Name],

            @@SPID [SPID]

    Begin Transaction

    Declare @I Int= 3/0;--חלוקה ב-0

    Commit

End Try

Begin Catch

    RollBack

    Insert Into tempdb..T_MaakavShgiot

    Select    GetDate() [Date],

            @@SPID [SPID],

            Error_Line(),

            Error_Message(),

            Error_Number(),

            IsNull(Error_Procedure(),@jobName),

            Error_Severity(),

            Error_State()

End Catch;

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

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

%d בלוגרים אהבו את זה: