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

06/05/2010

השימוש ב-SQLCmd וב-SQLCmd Mode

Filed under: Uncategorized — תגיות: , , — גרי רשף @ 09:17

אני מתבייש לספר אך עד לפני מספר ימים חשבתי ש-SQLCmd זה OSQL משופר שמאפשר להריץ Command Lines ותו לא,
ובזכות המצגת של מריה ב-ISUG למדתי שיש הרבה יותר משחשבתי..
באופן כללי התרשמתי שאם עד כה, כדי להריץ פקודות SQL ובמקביל פקודות של מערכת ההפעלה (נניח- יצירת מחיצה וביצוע גיבוי אליה), הייתי כותב קוד SQL ומשתמש ב-xp_CmdShell, או בונה פתרון SSIS שכולל לסירוגין אובייקטים מתאימים למשימות השונות; הרי כעת יש לנו אפשרות נוספת של כתיבת סקריפטים המשלבים פקודות SQL ופקודות מערכת שניתן להפעילם משורת הפקודה על ידי SQLCmd.

לפני שאעבור לדוגמאות (הרצת סקריפטים משורת הפקודה) כדאי לציין שניתן לעבוד ב-SSMS ב-SQLCmd Mode:

New Query => Query => SQLCmd Mode

נקבל מסך שדומה למסך העבודה הרגיל, אך בנוסף לפקודות SQL נוכל להריץ בו פקודות מערכת הפעלה (למשל- Dir או Copy..) בתנאי שלפניהם שני סימני קריאה:

!!Dir C:\

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

פקודות מערכת פשוטות

נתחיל עם הדוגמה הפשוטה של פקודת Dir: ניצור קובץ בשם Try001.sql במחיצה C:\Tmp (זאת תהיה מחיצת העבודה שלנו בהמשך ומי שמשתמש באחרת- שישנה את הפקודות בהתאם) ובו הפקודה הנ"ל עם שני סימני הקריאה, ונפעיל אותה כך:

SQLCmd –S MyServer –E -i c:\tmp\Try001.sql

את הסקריפט הזה, כאמור, ניתן להפעיל גם מה-SSMS ב-SQLCmd Mode (בתוספת צמד סימני קריאה לפניו) וגם מה-Command Line.
במקום MyServer יש לציין את שם השרת איתו עובדים,
אני מניח שאנחנו עובדים ב-Windows Authentication (אם לא- יש להשתמש במתגים / סוויצ'ים P -U- כדי לציין שם וסיסמה בהתאמה),
ומי שעובד ישירות על השרת – יכול לוותר על המתגים E- S-.

אם זה לא ברור, כדי להריץ שורות פקודה יש לפנות דרך Start של ה-Windows ל-Run ושם לכתוב Cmd ולהריץ.
זה יפתח חלון ברקע שחור שבו ניתן להריץ שורות פקודה.
לחילופין, ניתן להפעיל את הפקודה גם ממסך ה-SQLCmd Mode, אבל עם שני סימני קריאה לפני..

כעת נבצע אותו דבר בעזרת משתנה ונשמור את הסקריפט בקובץ C:\Tmp\Try002.sql:

:setvar MyPath "C:\Tmp\"
!!Dir $(MyPath)

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

SQLcmd –S MyServer –E –i c:\tmp\Try002.sql

כעת נשתמש במשתנה שהערך מועבר אליו על ידי המשתמש.
ניצור קובץ C:\Tmp\Try003.sql ובו הפקודות (אין צורך להגדיר ולאתחל את המשתנה):

!!Dir $(MyPath)

ונפעיל אותו על ידי הפקודה-

SQLcmd –S MyServer –E –i c:\tmp\Try003.sql -v MyPath="C:\Tmp\"

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

פקודות T-SQL והעברת ערכים אליהן

כעת נעבור לעבודה עם T-SQL בסקריפטים:
ניצור קובץ בשם C:\Tmp\Try004.sql ובו הפקודה

Select Top 5 * From sys.objects

ונריץ אותו כך:

SQLcmd –S MyServer –E –i C:\Tmp\Try004.sql

נעשה אותו דבר בעזרת משתנים, ואת הסקריפט הבא נשמור בתור C:\Tmp\Try005.sql:

:setvar MyTop 5
:setvar MyTable sys.objects
Select Top $(MyTop) * From $(MyTable)

ונריץ אותו:

SQLcmd –S MyServer –E –i C:\Tmp\Try005.sql

ולבסוף- אותו הדבר תוך העברת ערכי המשתנים דרך הפקודה, ולשם כך ניצור את הקובץ C:\Tmp\Try006.sql עם הפקודה הבאה:

Select Top $(MyTop) * From $(MyTable)

ונריץ אותה כך:

SQLcmd –S MyServer –E -i c:\tmp\Try006.sql -v MyTable=sys.objects -v MyTop=5

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

העברת ערכים מ-T-SQL לפקודות המערכת

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

Declare @Path VarChar(Max);
Set        @Path='C:\Tmp\'
:setvar MyPath @Path
!!Dir MyPath

המשתנה MyPath יקבל את הערך Path@ (לא את התוכן של המשתנה!), ופקודת Dir תיכשל.
ניתן למצוא דרך מתחכמת להתגבר על כך: נשתמש בפקודת Print של T-SQL כדי ליצור פלט,
נפנה את הפלט לקובץ SQL זמני,
ונריץ גם אותו..
נניח שאנחנו רוצים לפני הגיבוי, ליצור מחיצה לכל אחד מהדטבייסים בשרת, כדי להפנות אליו את הגיבוי.
יש לשלוף אם כך את רשימת הדטבייסים מטבלת המערכת sys.databases ולכל אחד נבצע Print יחד עם פקודת MkDir ליצירת ספריה.
את הסקריפט הבא נשמור בקובץ C:\Tmp\Try007.sql (קיבלתם פטנט להרצת לולאה ללא פתיחת Cursor באותו מחיר):

Declare @DB Varchar(Max);
Set        @DB=(Select Top 1 name From sys.databases Order By name);
While    @DB<>"
Begin
Print 'MkDir C:\Tmp\'+@DB;
Set        @DB=(Select Top 1 name From sys.databases Where name>@DB Order By name);
End

ואת הסקריפט נריץ כך:

sqlcmd -i C:\Tmp\Try007.sql -o c:\Tmp\Tmp.bat & Call c:\tmp\tmp.bat & Del c:\tmp\tmp.bat

הפקודה מפנה את הפלט של הסקריפט לקובץ אצווה (batch) זמני c:\Tmp\Tmp.bat,
מריצה אותו,
ולבסוף מבטלת את הקובץ הזמני.

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

להגיב »

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

RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

בלוג בוורדפרס.קום.

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