Mohamed Zaatar's Blog

From Months ago, i want to install MAC OS X on a virtual machine to try this beautiful OS ever :)
i said that because i'm a IPhone user from 3 years.

The Steps :
1- Download Snow Leopard Mac OS X iso from any torrents or ftps.
i used : Snow.Leopard.10.6.1-10.6.2.SSE2.SSE3.Intel.AMD.iso

2- Download boot-able iso for Mac from this link
and Extract the contents into specific folder.

3- Open Folder extracted and open Mac "OS X Server 10.6 (experimental).vmx" with notepad app.

4- Change the line of ("guestos = "darwin10") with this line ("guestos = "darwin10-64")
and save the file

5- now open VMWare (i used vmware workstation 7 ) and click on the file "OS X Server 10.6 (experimental).vmx" to open the virtual machine settings.

6- edit the CD/DVD player for the Virtual machine to be an ISO file located in the CD
"darwin_snow.iso", like this image:


7- Run the Virtual Machine, it will boot and ask you to press the "option" button, ignore and press F8. You will have this menu:
8- DO NOT take any action, now right-click on the most right bottom CD/DVD icon in the VMWare workstation and select settings.
- Change the ISO redirecting file for the DVD into the Snow Leopard ISO file downloaded from the first step (Make sure "Connected" check box is checked).
- Press OK

9- Still the VM is waitng for your choice, now press inside the VM itself and type "C" to boot from the installation DVD. :)

10- Now you will install MAC OS X into the Windows 7 :)
here is my screen shot:

11- After the installation finished, the OS X VM will restart, now change the DVD ISO pointing to see the "darwin_snow.iso" included (As step 6). and restart the VM again.
(Note: you have to point this ISO every time you start the VM), i know it's a bad thing to have but i believe that having OS X VM deserves. :)

12- Now configure your OS X with me :) , my screenshot:

CLR integration is a .NET facility allows you to write .NET code and be called and executed from SQL Servers. Simply it is a CLR hosting in Microsoft SQL Server beginning from SQL Server 2005. You can write SP, user define function, user define Data Types, user-define aggregate in .NET.

How to step into CLR integration:

1- Enable CLR integration in your SQL Server, by run this command:

sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

2- Write the mentioned code in your IDE:
C# Code:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void zaatar()
{
// Put your code here
using (SqlConnection sc =
new SqlConnection("Server=SERVERNAME\INSTANCENAME;Database=DB_Name;Trusted_Connection=True;"))
{
using (SqlCommand cmdupdate = new SqlCommand("update Country set Active = 1"))
{
cmdupdate.Connection = sc;
sc.Open();
cmdupdate.ExecuteNonQuery();
}
sc.Close();
}
}
};

3- Save the file as MyFirstProc.cs in c:\

4- compile it using .NET Frame work 2.0 /3.0 / 3.5 ,(but i preferred 2.0), using this command:

C:\Windows\Microsoft.NET\Framework\v2.0.50727>csc /target:library /output:c:\zaatar.dll c:\zaatar.cs

hints:
-i used CMD under administrator authority
- /output option: to specify the output location.
- /target option: to specify the output type

5- Go to your SQL Server and type this command to create an Assembly inside your DB :

CREATE ASSEMBLY ASS_Update
from 'c:\zaatar.dll' WITH PERMISSION_SET = SAFE
GO


6- After successfully creation the assembly, create a stored procedure to deal with the assembly and just to call the dll, by typing :

CREATE PROCEDURE dbo.UpdateCountryTable
AS
EXTERNAL NAME zaatar.StoredProcedures.zaatar
GO

Hints: After EXTERNAL NANME, there are three names with dot (.) separated
the first : is the dll name.
the second : is the class name in the CS file
and the third is the method defined in the CS file

7- Now you can call the Stored Procedure normally like any SP in the SQL Server.

Problems Hints:

-1- If you receive this error: “create assembly for assembly XYZ failed because assembly XYZ failed verification”

Solution: Try to compile your file or project with Framework 2/3/3.5,if you are working in VS IDE, change the target framework from project prosperities.

-2- If you receive this error: “The server principal "XYZ" is not able to access the database "XYZ" under the current security context”

Solution: Run this command on your DB:

ALTER DATABASE DB_NAME SET TRUSTWORTHY ON

GO

-3- if you are trying to set mode of your Assembly to UNRISTRICTED and you receive this error: “The database owner SID recorded in the master database differs from the database owner SID recorded in database DB_NAME.”

Solution: Run this command on your DB:

--To get owner SID recorded in the master database for the current database

SELECT owner_sid FROM sys.databases WHERE database_id=DB_ID()

--To get the owner SID recorded for the current database owner

SELECT sid FROM sys.database_principals WHERE name=N'dbo'

Each of the SID should be equal, if not run this command:

ALTER AUTHORIZATION ON Database::DB_NAME TO sa

Spare column is a normal column with storage optimized. Simply when the value in the column is NULL , it takes no space. It can be used for columns and filtered indexes (where index will index only the rows with values populated).

There are some SQL Data Types can't be used with Sparse , they are:
- geography
- geometry
- ntext
- text
- image
- timestamp
- varbinary
- user-defined data type

Restrictions about using Sparse Column:
- Sparse Column must be nullabe.
- Sparse Column can't have IDENTITY or ROWGUIDCOL or FILESTREAM or Default value attributes.
- Sparse Column can't be bounded to a rule.
- Sparse Column cant be computed column.
- Sparse Column cant be part of clustered index or PK but can be used in non-clustered index.
- Sparse Column is incompatible with data compression so can't compress a table with sparse column(s).

All of us know the typical three types of SQL Statement which are (DML , DCL and DDL) but they are more categorization:

1- DDL (Data Definition Language)
like Create, Alter.

2- DML (Data Manipulation Language)
like insert , update , delete

3- DCL (Data Control Language)
like : Revoke , Grant

4- DQL (Data Query Language)
Like Select with all clauses.

5- TCL (Transactional Control Language):
like Commit , RollBack

6- System Control Statements
In SQL Server : Set XYZ_OPTION TRUE
in Oracle : only one statement : ALTER SYSTEM

7- Embedded SQL Statements: It is an embedded programming into sql but not a SQL Language but each Vendor implements it in his own way.

In SQL Server: CLR integration
In Oracle: Module

Today i will show some T-SQL tricks and tips:

1- Delete Clause: Most of us when delete rows from SQL, we are using
delete from TABLE_NAME
you can also
delete TABLE_NAME
it gives the same effect

2- Candidate, Super and Primary keys...

Primary Key (PK): is a single or a set of collection columns that describe(s) unique rows. It enforces unique key plus clustered unique index. Plus any DB Table can have only one PK.

Candidate Key (Unique Key): can be a single or a set of columns that describe(s) uniqueness of data. DB Table can have more than one of them. Note: it allows only one NULL value in the column. Aslo it enforces creating non clustered index on the table.

Super Key: is rarely used. Formaly, it is a set of attributes that describe all relations assigned to the variables where no two distinct rows have the same value set of them. By other meaning the Cadidate Key is a special version of Super Key and the minimal set that uniqely describes a row, while the Super keys all unique keys.

3- ShowPlan_text Option: SQL option allows see the exceution plan for a SQL statement without exceuting it. It shows the constraints, indexes and keys used in the statement. It used like any SQL option:

set ShowPlan_text on

Hope that being helpful for you.