ארכיון הקטגוריה: SQL Server

העברת DB מ- Access על Sql Server באמצעות SSMA

בפוסט הזה אני מציין מספר נקודות מעניינות לגבי העברה של DB מסביבת Access אל Sql Server ( במקרה שלי גירסת Exrpress )

1- מיקרוסופט הסירו מאקסס את האפשרות  לייצא ישירות ל- SQL SERVER

הם ממליצים להשתמש בכלי שלהם שנקרא Sql Server Migration Assitant

יש גירסאות שלו עבור כמה סוגי DB נפוצים ( MySQL ועוד )

 

2- הכלי עובד יפה, אם כי לא אינטואיטיבי כל כך – ועל כך הפוסט הזה.

דברים שצריך לשים לב אליהם :

שלב 1  – יצוא הטבלאות מ- Access אל Sql Server

 

  • עוד לפני שמפעילים את הכלי – צריך להיות מודעים להתאים גירסת האופיס לגירסת הכלי ( ה=SSMA)
    אם ה-Access הוא בגירסת 32 ביט, צריך להפעיל את ה-SSMA 32 Bit  ( כי ה-Default הוא 64  ביט )
    שימו לב שההתקנה הרגילה מתקינה אצלכם ברשימת התוכניות – 2 גירסאות (32+64 )
    אם לא מפעילים את הגירסה שתואמת ל-Office שמותקן על המכונה, מקבלים בהמשך הודעת שגיאה שה-DB שניסיתם לטעון בלתי ניתן לקריאה…מה שכמובן לא נכון.
  • יצירת DB – לפני שמפעילים את הכלי, יש לייצור DB ב-SQL Server  שיקבל את הטבלאות שנייצא.
  • יצירת USER עבור ה-DB – שרת ה-Sql server מציע 2 צורות של אוטנטיקציה.
    אם משתמשים ב-Windows Auth… על אותה מכונה, לרוב לא תהיה בעיה.
    אם זה תחת שרתים אירגוניים, צריך לוודא שליוזרים יש הרשאות ל-DB,
    חשוב  לתת ליוזרים הרשאות , גם תחת "Security" של ה-Sql Server (=הכללי בעץ)
    וגם תחת Security של ה-DB הספציפי.
    אפשרות קצת פשוטה יותר -היא שימוש ב-Sql Auth, וגם שם צריך לוודא שהיוזר שיצרתם, מורשה הן לגישה ל-Server והן ל-DB הספציפי.
  • כאשר סף סוף מפעילים את הכלי – עדיף לא להשתמש ב-wizard שקופץ בכניסה – הוא אומנם מוביל שלב אחרי שלב, אך מדלג  על חלק מהשלבים, ולכן לא כל כך עוזר…
  • הגדרה שכדאי, ואפילו חובה לבצע עוד בהתחלה, היא להיכנס למקום שבו מוגדר לאיזה סוג שדה הופך כל אחד מהשדות ( למשל :  שדה TEXT של Access הופך ל- nvarchar של Sql Server וכדומה )
    זה נעשה תחת Tools < Default Project Settings > Type Mapping
    כל ההגדרות תקינות …חוץ מאשר….Date של Access  – שמועבר בצורה שגויה אל Datetime(2) של Sql Server , הבעיה היא, שכאשר נסיים את תהליך העברת הטבלאות, ונקשור את הטבלאות בחזרה אל פרוייקט ה-Access שלנו, תהיה לנו בעיה – ש-Access לא יודע לקרוא את סוג השדה הזה , ולכן ממיר אותו אל Text …וזה כבר יוצר שגיאות וולידציה, ובעיות בקוד ה-VBA שמצפה ל- Datetime.
    הפתרון הוא להגדיר את השדות כ- Datetime רגיל ( בלי ה-2 )
    במקרה זה – ההמרה עוברת תקין.
  • השלב הראשון, הוא "יצירת פרוייקט" (דרך התפריט או דרך כפתור למעלה)
    חשוב לשים לב – לבחור במסך יצירת הפרוייקט את גירסת ה- Sql Server שלכם
    ה-DropDown הוא כמעט מוסתר, וברירת המחדל היא Azure….מה שלא בטוח שהתכוונתם.

    • נקודה נוספת באותו עניין – יש אפשרות להגדיר את זה תחת Tools > Defailt project settings  (גם כאן ה-Dropdown נסתר מהעין)
  • לאחר שמוסיפים את ה-DB של Access באמצעות Add Databases , יש לבצע שמירה של הפרויקט – השמירה גורמת ל-SSMA לטעון את ה-Metadata של הטבלאות.
    למה…ככה (מיקרוסופט ? )
  • השלב הבא הוא חיבור ל-SQL SERVER,  אם לא בחרתם מראש בגירסת ה-SQL SERVER הנכונה, אזי לא תוכלו להתחבר ל-SQL שלכם.
  • בסוף אחרי שהכל מוגדר , כדי לגרום לכפתור ה- Convert-Load_AND-MIGRATE לעבוד , חייבים ללחוץ ללחוץ על ה-Access DB הרלוונטי – ורק אז הכפתור הופך ל-Enabled
  • לאחר לחיצה על כפתור המרה ( Convert..load..and migrate )
    הכלי עובד די מהר וחלק, טוען את כל הטבלאות , ובונה אותן, כולל את המפתחות הזרים והאינדקסים.  אפשר לומר שזה החלק הטוב בכלי הזה, שבסופו של דבר הוא באמת מבצע את העבודה.

שלב 2 – חיבור של קובץ ה-Access אל הטבלאות ב-Sql Server

זה תהליך די מוכר לכל מתכנת Access, ואכתוב אותו בקצרה :

  • מוחקים ( בלי לפחד 🙂 ) את הטבלאות המקוריות ( בין אם הם מקושרות, ובין אם מאוחסנות בקובץ הזה )
  • תחת "נתונים חיצוניים" > יבוא וקישור >  מסד נתונים של ODBC > ניצור Connection חדש או בקובץ, או של מערכת ההפעלה  ולאחר יצירת ה-Connection נבחר בו כדי לקשור את הטבלאות.
    • בשלב יצירת ה-Connection יש לבחור בדרייבר של Sql Server , או Sql Server ODBC Driver
    • באחד ממסכי יצירת ה-Connection יש לשים לב לאפשרות של בחירת ה-Default Database – ולהפעיל אותה רק על ה-DB הרלוונטי ב-SQL Server
  • לאחר שנבחר ב-Connection שיצרנו – נסמן את הטבלאות לקישור, יש לשים לב לא לסמן גם את טבלאות המערכת של Sql server , אלא רק את הטבלאות המקוריות ששיכות לפרויקט שלנו ( זה לא משנה, אבל חבל סתם להעמיס טבלאות מקושרות שלא נחוצות על הפרוייקט)
  • אם אתם לא רוצים לשגע את היוזרים – כדאי לסמן את האפשרות לשמור את הסיסמה ( ואז ללחוץ על אישור בנפרד לכל אחת מהטבלאות….מיקרוסופט …!!!! )
  • זה עוד לא נגמר …בשלב זה, כיוון שהטבלאות ב-Sql Server מתחילות תחת "איזור" שנקרא לרוב dbo. אז בקישור ל-Access, פתאום מתווסף לכל הטבלאות בתחילת השם, הביטוי dbo_ , צריך לעבור טבלה-טבלה, ולשנות את השם בחזרה לשם המקורי. (או לכתוב\למצוא סקריפט שעושה זאת …)
    אחרת – אם לא נעשה זאת, השאילתות שלנו לא יעבדו.
  • וכאן הגיע הזמן לבדוק שהכל תקין.

בהצלחה.

 

נקודה אחרונה, אך ממש לא קשורה – למי שמפתח Web על אותה מכונה שעליה מותקן ה-Sql Server.

אחד ה-Services שמופעלים בהתקנת ה-Sql Server, נקרא Sql Server Reporting Service

ולמרבה הפלא …הוא תופס את Port 80  ….

כך שאם רוצים להמשיך לעבוד , צריך : או להשבית אותו ( services.msc וכו' )  או להמשיך לעבוד בפורט אחר עבור ה-localhost על המכונה.

להפוך שם פרטי ומשפחה ב-SQL SERVER

במערכות שלי, בדרך כלל שם הלקוח בא בשדה אחד שמכיל גם שם פרטי וגם שם משפחה

לפעמים הלקוח מבקש אפשרות חיפוש גם לפי שם פרטי וגם לפי שם משפחה

זו הצורה שאני עושה זאת ב-T-SQL (SQL SERVER 2012) :


SUBSTRING(dbo.vwWorkersAndDepts.WorkerName + ' ' + dbo.vwWorkersAndDepts.WorkerName ,
 CHARINDEX(' ', dbo.vwWorkersAndDepts.WorkerName)+1,LEN(dbo.vwWorkersAndDepts.WorkerName))

פתרון בעית Cannot resolve collation conflict

באחת השרתים שאני משכיר, התגלה, *אחרי* שהיה עליו כבר דטאטבייס פעיל, שצריך לשנות את ה-collation .
ביקשתי מהאדמין ועשו זאת, שינו לעברית.
אבל זה תקף רק מכאן – ואילך.
וכאן זה יצר לי בעיה כזו.
כאשר אני בא לעשות שאילתות מסוימות שמשלבות נתונים מ-2 סוגי collation שונים (או יותר)
אז ה-Sql server נותן הודעת שגיאה כזו

Cannot resolve collation conflict between "Hebrew_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in UNION ALL operator occurring in SELECT statement column 2.

אז הפתרון הוא כזה, אחרי שדות טקסט בשאילתא, צריך להוסיף את הביטוי הבא

COLLATE DATABASE_DEFAULT

ואז השאילתא לדוגמא נראית כך :

SELECT tblDepts.DeptID , tblDepts.DeptName COLLATE DATABASE_DEFAULT, 
       tblWorkers.WorkerName COLLATE DATABASE_DEFAULT, 'מחלקה' as sinun  
FROM tblDepts  LEFT JOIN tblWorkers ON tblDepts.manager = tblWorkers.WorkerIDnumber

union all 
SELECT  [supplier_id] ,[supplier_name] COLLATE DATABASE_DEFAULT
        ,'-' as man ,'קבלן' as sinun
  FROM [tbl_suppliers]

מידע נוסף : http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/

פתרון בעיית Collation שונים בין 2 שדות

בהמשך לפוסט הקודם,
לפעמים אין אפשרות לכתוב Collate בשאילתא, כי השאילתא נשלחת ב-ODBC ואנחנו לא שולטים על ה-Syntax שלה.
זה בדיוק המקרה שקרה לי:
הדאטאבייס במקור עלה מ-Access אל ה-Sql server
כאשר עמודות הטקסט היו במקור ב -collation שנקרא SQL_Latin1_General_CP1_CI_AS

ואחר כך האדמין של השרת שינה לי את ה-collation של ה-Database ל – Hebrew_CI_AS

ואז – כאשר הוספתי עמודה – העמודה החדשה הוגדרה למעשה ב -collation שנקרא Hebrew_CI_AS

ובגלל זה השאילתא נתנה לי הודעת השגיאה.

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Hebrew_CI_AS" in the equal to operation.

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

alter table dbo.tblArm3OLD 
add WorkerIDnumber nvarchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS

איך לעשות גיבוי לטבלה עם המידע ב-MS SQL

אם רוצים לקבל את כל תוכן הטבלה בשאילתות INSERT וכו'
אז עושים ככה ב-SSMS :
1. מקש ימני על הדאטאבייס
2. בוחרים Tasks > Generate Scripts
3. מתקדמים לפי ה-Wizard שלב אחרי שלב
4. בכפתור Advanced בוחרים במאפיין Types of Data – בוחרים באפשרות שכוללת את הנתונים

וזהו

Sql Server 2012 – איך יוצרים מפתח ראשי בטבלה שכבר קיימת

קודם כל חייבים לוודא שהעמודה שהולכים להפוך אותה למפתח ראשי –

  1. אין בה ערכים ריקים (Null )
  2. היא מקונפגת כך שהיא לא יכולה לקבל ערכים ריקים (No Null )

    כדי לקנפג את זה , רושמים את הפקודה הבאה

ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL

  1. עכשיו, הולכים לטבלה ב-SSMS, ולוחצים מקש ימני > Design
  2. בטבלה שנפתחה לנו, לוחצים מקש ימני על השדה (ברווח האפור הקטן) > Set Primary Key