none
לא מצליח להריץ dynamic SQL שמכיל GO RRS feed

  • שאלה

  • שלום לכולם,

    ניסיתי להריץ סקריפט שמכיל בתוכו את GO מספר פעמים, בעזרת sp_executesql קיבלתי הודעת שגיעה "Incorrect syntax near 'GO'". מצאתי הסבר, ש-GO זה לא SQL תיקני, מכוון שזאת מילת מפטח ולא פקודה, לכן הדרך היחידה לגרום ל- sp_executesql k לעבוד היא להחליף את GO במשהו אחר בעזרת REPLACE. הפיתרון הזה לא מקובל עליי, כי הסקריפט שלי אמור לרוץ בתור טרנזקציה אחת ואני חייב את GO בפנים. 

    קיבלתי הצעה אחרת: לכתוב פרוצדורה משלי ב-C# שיודעת להתמודד עם GO.

    1) כתבתי את הפרוצדורה:

    using System;
    using System.Data.Sql;
    using System.Data.SqlClient;
    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Common;
    
    
    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
    
        public static void sp_executesqlgo (string Sql)
        {
            using (SqlConnection connection = new SqlConnection("context connection=true")) 
            
            try 
            {
                Server server = new Server(new ServerConnection(connection));
                SqlCommand cmd = new SqlCommand(Sql, connection);
                connection.Open();
                server.ConnectionContext.ExecuteNonQuery(Sql);
                connection.Close();
            }
            catch(Exception)
            {
                throw;
            }
        }
    }

    2) קימפלתי אותה, קיבלתי קובץ sp_executesqlgo.dll

    3) שמתי את הפרוצדורה ב-DB:

    exec sp_configure 'clr enabled', 1 
    RECONFIGURE
    GO
    
    USE [master]
    GO
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_executesqlgo]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sp_executesqlgo]
    GO
    IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'sp_executesqlgo' and is_user_defined = 1)
    DROP ASSEMBLY [sp_executesqlgo]
    GO
    CREATE ASSEMBLY sp_executesqlgo FROM 'C:\Users\iradchenko\Documents\Visual Studio 2013\Projects\sp_executesqlgo\sp_executesqlgo\bin\Debug\sp_executesqlgo.dll' WITH PERMISSION_SET = SAFE;
    GO
    
    CREATE PROC dbo.sp_executesqlgo(@SQL nvarchar(max))
    AS EXTERNAL NAME sp_executesqlgo.StoredProcedures.sp_executesqlgo;
    GO

    4) הרצתי את הפרוצדורה:

    DECLARE @SQL nvarchar(MAX) = '
    		;WITH NewRows ([AuditEventID], [AuditEventCode], [AuditEvent])  AS (
    				  SELECT ''ed94c8ad-f649-4e3d-8677-5e242735d7fd'', N''EditPassword'', N''Edit Password''
    		UNION ALL SELECT ''e263644c-db1d-4cfb-9037-745db414e8ff'', N''ViewAccountDetailsPage'', N''View Account Details Page''
    		)
    
    		 SELECT N.[AuditEventID], N.[AuditEventCode], N.[AuditEvent]
    		   FROM NewRows N
    		GO
    '
    exec [sp_executesqlgo] @SQL


    קיבלתי את השגיאה הבאה:

    Msg 6522, Level 16, State 1, Procedure sp_executesqlgo, Line 0
    A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_executesqlgo": 
    System.Security.SecurityException: That assembly does not allow partially trusted callers.
    System.Security.SecurityException: 
       at StoredProcedures.sp_executesqlgo(String Sql)
    .

    אני מבין שמקור הבעיה הוא כזה:

    server class  יש לו בעיה עם context connection, הוא רוצה connection string מלא, אבל אני לא רוצה לספק אותו ,מן הסתם, כי הפרוצדורה כבר יושבת על שרת, היא לא אובייקט חיצוני שמנסה להתחבר מבחוץ. האם יש למישהו רעיון? (רונן, wink-wink :))

    פיתרון אחר לחלוטין גם מבורך, כמובן. תודה!





    יום שלישי 10 דצמבר 2013 09:33

תשובות

  • הי איוון,

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

    אין קשר בין טרנזקציה לבין GO. טרנזקציה מתחילה ב-BEGIN TRANSACTION ומסתיימת ב-COMMIT TRANSACTION או ב-ROLLBACK TRANSACTION. המילה GO היא מילת מפתח ששייכת ל-SSMS ולא ל-SQL Server, ותפקידה להפריד בין Batches. כשאתה מריץ בחלון אחד ב-SSMS שתי שאילתות שביניהן יש את המילה GO, אז SSMS מאחורי הקלעים מפצל את הסקריפט לשני Batches, ומריץ אותם אחד אחרי השני. מה שמגיע לשרת זה השאילתה הראשונה ואחר-כך השאילתה השניה. השרת לא מכיר את המילה GO. טרנזקציה יכולה להתחיל ולהסתיים באותו Batch או להתחיל ב-Batch אחד ולהסתיים ב-Batch אחר. מצד שני, Batch אחד יכול להכיל בתוכו כמה טרנזקציות. בקיצור, אין קשר בין טרנזקציה ל-Batch.

    כשאתה לוקח טקסט שמכיל בתוכן את המילה GO ומנסה להריץ אותו באמצעות sys.sp_executesql, אתה בעצם שולח את כל הטקסט כ-Batch אחד לשרת, והשרת מנסה להריץ את ה-Batch הזה, אבל מכיוון שהוא לא מכיר את הפקודה GO, הוא נכשל. במקרה הזה, אין אף אחד שיעשה את ההפרדה של הטקסט ל-Batches במקומות שבהם מופיעה המילה GO.

    עכשיו בוא נעבור לפתרונות:

    1. למה אתה צריך GO בתוך הטקסט שלך? תשנה את הסניפט שלך שיצור את אותו קוד בלי כל ה-GO.

    2. אם בכל זאת אתה חייב GO, אז תכתוב לולאה (ב-TSQL) שמחפשת בתוך הטקסט בכל פעם את ה-GO הבא, ומריצה רק את ה-Batch שמסתיים ב-GO הזה. זה בדיוק מה ש-SSMS עושה.

    3. אני מסכים עם רונן שאין שום סיבה להשתמש כאן ב-CLR.

    4. אני גם ממש לא רואה סיבה להשתמש כאן ב-Service Broker.

    מקווה שעזרתי...

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Ivan Radchenko יום שני 16 דצמבר 2013 11:46
    יום חמישי 12 דצמבר 2013 19:03
    מנחה דיון
  • 1. כפי שכתבתי מעל

    2. אני לא ממליץ לכתוב לולאה שתפרק את הקוד אלא פשוט לעשות שימוש בפונקציית SPLIT פשוטה של CLR שתבצע SPLIT לפי המילה GO. ביננו ככה גם עובדות התוכנות GUI שמכירות ב GO.
    * אני ממליץ להכניס את התוצאות של השאילתות הנקיות לטבלה מסודרת בה נוכל לנהל סטאטוס של מצב ההרצה ולא להריץ ישירות דינאמית.

    3. כפי שכתבתי מעל

    4. בהחלט אני יכול למצוא סיבות :-) אני כמובן לא יודע אם הן מתאימות לצרכים באפיון כאן (חלק זה חסר לי באפיון ואולי האפשרות לא היתה ידועה והאפיון נקבע בהתאם ואולי באמת אין בכך צורך). האפיון/צרכים לא ברור לי בצורה מלאה, ולכן ייתכן בהחלט שאין שום הגיון בשימוש ב SERVICE BROKER אלא פשוט להריץ את השאילתות בצורה סינכרונית בלולאה/CURSOR.

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


    [Personal Site] [Blog] [Facebook]signature

    • נערך על-ידי pituachMVP, Editor יום ראשון 15 דצמבר 2013 05:30
    • סומן כתשובה על-ידי Ivan Radchenko יום שני 16 דצמבר 2013 11:49
    שבת 14 דצמבר 2013 17:13
    מנחה דיון

כל התגובות

  • היי איוון,

    תוכל להגיד לנו מה הצורך ולמה צריך להריץ את הסקריפט ב-sp_executesql? 


    My Blog

    יום שלישי 10 דצמבר 2013 13:08
  • היי מתן.

    יש לנו טבלת גרסאות ב-DB. בין השאר, יש בה עמודות VersionID, Status, SQL.

    VersionID זהו מס' תיקון

    Status - האם הסקריפט של התיקון הורץ או לא

    SQL - הסקריפט עצמו.

    אני רוצה לכתוב פרוצדורה שתריץ את כל הסקריפטים (תוכן של עמודת SQL) שהסטטוס שלהם הוא 1 (לא הורץ עדיין) ותעדכן את הסטטוס ל-2 (הורץ). כל סקריפט בעמודת SQL חייב להיות טרנזקציה אחת ולכן מופיע שם GO מלא פעמים.

    כתבתי snippet (תודה רבה לך על זה, אגב :)) ש"עוטף" פקודות בטרנזקציה בצורה הבאה:

    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
    GO
    CREATE TABLE #tmpErrors (Error int)
    GO
    SET XACT_ABORT ON
    GO
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    GO
    BEGIN TRANSACTION
    GO
    
    -- my code
    
    GO
    IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
    GO
    IF @@TRANCOUNT>0 BEGIN
    PRINT 'The database update succeeded'
    COMMIT TRANSACTION
    END
    ELSE PRINT 'The database update failed'
    GO
    DROP TABLE #tmpErrors
    GO

     
    • נערך על-ידי Ivan Radchenko יום שלישי 10 דצמבר 2013 14:44
    יום שלישי 10 דצמבר 2013 14:39
  • היי איוון,

    כיף לשמוע לגבי הסניפטים :)

    אני לא כל כך חזק ב-CLR לצערי, אבל דאגתם להפעיל את הדגל של TRUSTWORTHY ברמת ה-DB?

    ALTER DATABASE IvanDB
    SET TRUSTWORTHY ON


    My Blog

    יום שלישי 10 דצמבר 2013 21:32
  • 1. GO הוא בהחלט לא כתיבה תקנית אלא כתיבה שנועדה במקור ל GUIכמו SSMS ולא לשרת. הרבה פעמים אפשר להחליף את השימוש של GO בשימוש בסימון נקודה פסיק ; (זה לא תמיד זהה אבל בדרך מבצע את מה שאתה כניראה צריך אם הבנתי נכון).

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

    3. שימוש ב CLR לא אמור למטב כאן את העניין אלא רק יכביד על המערכת (את זה כותב מי שכל הזמן מנסה לגרום לכולם לעבוד עם CLT :-)). בעבודה עם CLR בגישה שלכם אתם מבצעים התחברות למסד הנתונים בכל פעם ואם אתם צריכים להפעיל משימות מרובות בצורה דינאמית, למשל משימות שנמצאות בטבלה של משימות אז מדובר על התחברויות רבות לשרת ללא סיבה (עלולים להיווצר לכם זמני המתנה ל POOL פנוי). הייתי ממליץ לחשוב על גישה שונה לחלוטין של שימוש ב service broker. השירות יפעיל את המשמות בצורה סינכרונית או א-סינכרונית לפי ההגדרה שלכם.

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

    * כאמור לא התעמקתי כרגע באפיון שלכם אבל על פני השטח ניראה שה CLR לא לעניין כאן.

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

    כרגע לא ברור לי בכלל התהליך שאתם מבצעים ולכן יותר מהערות כלליות כאלה אני לא יכול להציע משהו ספציפי שיתאים לכם :-) אבל ניראה לי שזה דיי נותן כיוון ותשובה כללית


    [Personal Site] [Blog] [Facebook]signature

    יום רביעי 11 דצמבר 2013 02:50
    מנחה דיון
  • מתן,

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

    Msg 6522, Level 16, State 1, Procedure sp_executesqlgo, Line 0
    A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_executesqlgo": 
    System.Exception: This functionality is disabled in the SQLCLR. It is recommended that you execute from your client application.
    System.Exception: 
       at Microsoft.SqlServer.Management.Common.ConnectionManager..ctor(SqlConnection sqlConnectionObject)
       at Microsoft.SqlServer.Management.Common.ServerConnection..ctor(SqlConnection sqlConnection)
       at StoredProcedures.sp_executesqlgo(String Sql)
    .
     

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

    יום רביעי 11 דצמבר 2013 10:14
  • רונן,

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

    2) כנראה שבאמת לא הבנת נכון. בקצרה:

    יש לנו עמודה ([SQL]) בטבלה ([Versions]) שמכילה סקריפטים בעלי תבנית הבאה:

    SET NUMERIC_ROUNDABORT OFF
    GO
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
    GO
    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
    GO
    CREATE TABLE #tmpErrors (Error int)
    GO
    SET XACT_ABORT ON
    GO
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    GO
    BEGIN TRANSACTION
    GO
    
    -- my code
    
    GO
    IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
    GO
    IF @@TRANCOUNT>0 BEGIN
    PRINT 'The database update succeeded'
    COMMIT TRANSACTION
    END
    ELSE PRINT 'The database update failed'
    GO
    DROP TABLE #tmpErrors
    GO

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

    3) CLR הוא גם לא הבחירה הראשונה שלי. הבנתי שלא ניתן לכתוב פרוצדורה כזאת ב-T-SQL, לכן פניתי למפתחים וחברה והתברר שאחד מהם כן עשה דבר כזה ב-C#. הבעיה, עד כמה שהבנתי, שהדרך שלו רלוונטית רק כאשר פונים ל-DB מתוך האפליקציה. כיוויתי שזה בדיוק הנושא שאתה יכול לסייע בו.

    4) לא עבדתי עם Service Broker, אני אבדוק את הנושא, תודה!


    יום רביעי 11 דצמבר 2013 10:33
  • הי איוון,

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

    אין קשר בין טרנזקציה לבין GO. טרנזקציה מתחילה ב-BEGIN TRANSACTION ומסתיימת ב-COMMIT TRANSACTION או ב-ROLLBACK TRANSACTION. המילה GO היא מילת מפתח ששייכת ל-SSMS ולא ל-SQL Server, ותפקידה להפריד בין Batches. כשאתה מריץ בחלון אחד ב-SSMS שתי שאילתות שביניהן יש את המילה GO, אז SSMS מאחורי הקלעים מפצל את הסקריפט לשני Batches, ומריץ אותם אחד אחרי השני. מה שמגיע לשרת זה השאילתה הראשונה ואחר-כך השאילתה השניה. השרת לא מכיר את המילה GO. טרנזקציה יכולה להתחיל ולהסתיים באותו Batch או להתחיל ב-Batch אחד ולהסתיים ב-Batch אחר. מצד שני, Batch אחד יכול להכיל בתוכו כמה טרנזקציות. בקיצור, אין קשר בין טרנזקציה ל-Batch.

    כשאתה לוקח טקסט שמכיל בתוכן את המילה GO ומנסה להריץ אותו באמצעות sys.sp_executesql, אתה בעצם שולח את כל הטקסט כ-Batch אחד לשרת, והשרת מנסה להריץ את ה-Batch הזה, אבל מכיוון שהוא לא מכיר את הפקודה GO, הוא נכשל. במקרה הזה, אין אף אחד שיעשה את ההפרדה של הטקסט ל-Batches במקומות שבהם מופיעה המילה GO.

    עכשיו בוא נעבור לפתרונות:

    1. למה אתה צריך GO בתוך הטקסט שלך? תשנה את הסניפט שלך שיצור את אותו קוד בלי כל ה-GO.

    2. אם בכל זאת אתה חייב GO, אז תכתוב לולאה (ב-TSQL) שמחפשת בתוך הטקסט בכל פעם את ה-GO הבא, ומריצה רק את ה-Batch שמסתיים ב-GO הזה. זה בדיוק מה ש-SSMS עושה.

    3. אני מסכים עם רונן שאין שום סיבה להשתמש כאן ב-CLR.

    4. אני גם ממש לא רואה סיבה להשתמש כאן ב-Service Broker.

    מקווה שעזרתי...

    -----------------------------
    גיא גלנצר
    יועץ ומדריך SQL Server
    Madeira - SQL Server Services
    http://www.madeira.co.il

    • סומן כתשובה על-ידי Ivan Radchenko יום שני 16 דצמבר 2013 11:46
    יום חמישי 12 דצמבר 2013 19:03
    מנחה דיון
  • אהלן איבן

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

    1. כמו שכתבתי GO זה לא חלק מהשאילתה אלא כתיבה לטובת ה GUI.

    אין מצב שאתם מקבלים הודעת שגיאה בגלל קיום המילה GO אם המילה GO לא קיימת. תעביר לנו דוגמה מלאה של קוד מדוייק (דוגמה ולא בהכרח אמיתי.. אבל כזה בלי נקודות אלא קוד שאפשר להריץ), שאתה מנסה להריץ ביחד עם DDL+DML מתאים אם אתה רוצה שנבדוק את הנושא. השגיאה נובעת ממשהו אחר אם היא קיימת וצריך לבדוק ממה.

    2. צרף לנו DDL+DML אז נוכל להבין יותר וגם לבדוק את הדברים, בעקרון מה שאתה מסביר ומה שאני מבין כרגע זהה כמעט לחלוטין למה שהבנתי קודם. לכן אין לי מה להוסיף הרבה אלא רק להבהיר ואני אנסה כאן בקצרה.

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

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

    את ה SPLIT כן יש לבצע בעזרת CLR (כאן מגיע הכוח הגדול של ה CLR במניפולציות על STRING).

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

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

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

    אפשרות נוספת היא לנהל את ההרצות דרך SERVICE BROKER שיפשר להריץ שאילתות גם במקביל. זה נוח מאוד במקרה שלכם אם יש צורך להריץ למשל כל קבוצה של שאילתות שמגיעות מאותו BATCH בסדר מסויים אבל קבוצות שונות של שאילתות רוצים להריץ במקביל (זה יותר מורכב לכתוב אבל זו הדרך המומלצת במקרה שרוצים אפשרות של גמישות לעבודה א-סינכרונית)

    3. גם בנושא ה CLR כתבתי בקצרה את ההסבר. חפש את המילה UNSAFE בהודעה שלי למעלה וחפש את המילה SAFE בקוד שלכם :-)
    CLR בשרתי SQL לא רץ ישירות על מנוע ה FRAME WORK אלא באמצעות מעין תיווך של שירות אירוח שקיים בשרת ה SQL. השרת מספק אבטחה נוספת על מנת להבטיח לנו שה DBA לא ימחוק לנו את כל המחשב. בברירת המחדל השרת לא מאפשר גישה לנתונים חיצוניים למסד הנתונים עצמו. שימוש ב CONNECTION הוא סוג של גישה לנתונים חיצוניים! לכן אנחנו צריכים להבטיח שהקוד שלנו מקומפל במצב UNSAFE.

    בדוק את הבלוג הבא לדוגמה. בבלוג זה אני מציג דוגמה לשימוש באלמנט שיושב בזכרון בדומה לרעיון הבסיסי (הכוונה רק לרעיון הבסיסי ביותר של אלמנט בזכרון, בלי כל הניהול מסביב) למה שיש בשרתי SQL 2014. אני למעשה בונה COUNTER בנמצא בזכרון. שים לב להסבר בנושא UNSAFE. הקוד הספציפי הזה חייב שיתוף של האלמנט בזכרון ולכן היינו צריכים לעבוד במצב UNSAFE. כמו שכתבתי מעל אני לא ממליץ לשנות הגדרות TRUSTWORTHY אלא אם אין ברירה.
    http://ariely.info/Blog/tabid/83/EntryId/117/Sharing-a-parameter-of-SQL-CLR-element-between-separate-requests-to-the-element-using-Static-Constructors.aspx

    חומר נוסף כדאי לחפש בנושא בגוגל. אפשר להתחיל בקישור הבא:
    http://technet.microsoft.com/en-us/library/ms345101.aspx

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

    4. מאוד מומלץ קודם ללמוד כי זה כלי חזק מאוד גם אם לא יתאים לכאן בסוף

    פרט לכך הוא יכול מאוד להתאים למקרה הנוכחי אולי (לניהול ההרצות של השאילתות מהטבלה של השאילתות המופרדות), אם יש צורך לאפשר הפעלה במקביל או תזמון של ההרצות. יש באינטרנט הרבה פרוייקטים והסברים בהקשר של SQL EXPRESS כאמור. הנה דוגמה לקישור טוב:
    http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express-2

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

    כאמור כל מה שכתבתי כאן כתבתי כבר מעל :-)
    אני מקווה שהדברים יותר ברורים עתה

    ** אני רוצה לציין שבמבט ראשון ניראה לי שאתם לא צריכים CLR בשום צורה שהיא, אתם צריכים בשלב הראשון לבצע SPLIT של הקוד לשאילתות נפרדות לפי קבוצות של BATCH-ים להרצה, ואז לבחור (1) לעבוד עם סמן CURSOR אם השאילתות אם הן צריכות לרוץ בצורה סיכרונית, (2) או עם SERVICE BROKER אם מדובר בשאילתות שרוצים גם להריץ במקביל בצורה של "שלח ושכח" (ז"א כל קבוצה בצורה כינסכרונית ובין הקבוצות בצורה א-סינכרונית).


    [Personal Site] [Blog] [Facebook]signature

    שבת 14 דצמבר 2013 17:03
    מנחה דיון
  • 1. כפי שכתבתי מעל

    2. אני לא ממליץ לכתוב לולאה שתפרק את הקוד אלא פשוט לעשות שימוש בפונקציית SPLIT פשוטה של CLR שתבצע SPLIT לפי המילה GO. ביננו ככה גם עובדות התוכנות GUI שמכירות ב GO.
    * אני ממליץ להכניס את התוצאות של השאילתות הנקיות לטבלה מסודרת בה נוכל לנהל סטאטוס של מצב ההרצה ולא להריץ ישירות דינאמית.

    3. כפי שכתבתי מעל

    4. בהחלט אני יכול למצוא סיבות :-) אני כמובן לא יודע אם הן מתאימות לצרכים באפיון כאן (חלק זה חסר לי באפיון ואולי האפשרות לא היתה ידועה והאפיון נקבע בהתאם ואולי באמת אין בכך צורך). האפיון/צרכים לא ברור לי בצורה מלאה, ולכן ייתכן בהחלט שאין שום הגיון בשימוש ב SERVICE BROKER אלא פשוט להריץ את השאילתות בצורה סינכרונית בלולאה/CURSOR.

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


    [Personal Site] [Blog] [Facebook]signature

    • נערך על-ידי pituachMVP, Editor יום ראשון 15 דצמבר 2013 05:30
    • סומן כתשובה על-ידי Ivan Radchenko יום שני 16 דצמבר 2013 11:49
    שבת 14 דצמבר 2013 17:13
    מנחה דיון
  • חשוב מאוד:

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

    SET NUMERIC_ROUNDABORT OFF

    וכן הלאה... כמובן :-)


    [Personal Site] [Blog] [Facebook]signature

    שבת 14 דצמבר 2013 17:32
    מנחה דיון
  • הי Ivan,

    נודה לך אם תסמן את התשובות שעזרו לך ו/או פתרו את בעייתך.

    תודה.


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

    יום ראשון 15 דצמבר 2013 10:09
  • היי גיא,

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

    1) כל הסקריפט חייב לרוץ כטרנזקציה אחת

    2) אני חייב GO בתוך הסקריפט, לדוגמא כי הסקריפט יוצר מסי פרוצדורות.

    לשאלות שלך:

    1) כמו שאמרתי, חלק מהסקריפטים יוצרים פרוצדורות ולכן הם יתחילו להכשל אם אני פשוט אחליף את GO במשהו אחר. נכון, אני יכול לפצל כל הסקריפטים האלה שכל אחד יצור רק פרוצדורה אחת, אבל זו כמות עצומה של עבודה ומטרה היא שפשט את ה-deployment לפי הטבלה הקיימת ולא לעשות לה overhaul מלא עכשיו.

    2) רעיון מצויין, תודה רבה! אני לא סגור על איך עושים את זה עדיין, אבל מקווה שאני אסתדר לבד. :)


    • נערך על-ידי Ivan Radchenko יום שני 16 דצמבר 2013 11:40
    יום שני 16 דצמבר 2013 11:39
  • רונן,

    הרעיון שלך להשתמש SPLIT גם נראה לי נחמד מאוד, אני אבדוק את זה. תודה רבה!

    יום שני 16 דצמבר 2013 11:45