today I'll demonstrate a very nice feature that has been added recently in Microsoft SQL Server 2005 which is SQL CLR Integration.

This feature It’s the possibility to deploy C# or VB.NET code that is used within the SQL Server process. this means that if you need complex procedural code, you can write it as managed code and of course it has plenty of benefits :

¨T-SQL is interpreted and hence slower.

¨Error handling in T-SQL was frankly abysmal (although it has improved in SQL Server 2005)

¨String handling in T-SQL is un-optimized and slow

¨SQL-CLR is compiled so faster

¨SQL-CLR lets you take advantage of the huge base class library

and now I'll show you a small demonstration on how you create a SQL CLR project:

first of all you should have Microsoft SQL Server 2005 Installed and Microsoft Visual Studio 2005.

you should enable the SQL CLR feature by clicking Start --> All Programs --> Microsoft SQL Server 2005 --> Configuration Tools --> SQL Server Surface Area Configuration --> click on Surface Area Configuration for features -->you will find in the left pane MSSQLSERVER underneath it Database Engine underneath it you will find CLR Integration --> select it and Check it from the right pane.

my Example is so simple imagine that we have a table that we need to capture what is inserted in it on insertion and pass it to a web service to through it into another database, I chose web service particularly because it will give the opportunity to do anything you with the captured data so let's open the Visual Studio 2005 from Start --> All Programs --> Visual Studio 2005 --> click on File--> New Project

Choose SQL Server project from the Database Template under the Visual C# template and write in the Name "CLRDemo" , save it where ever you like and click OK.

The Add database Reference window will open to create the database reference you want to connect to ...

Click on Add New Reference to add Reference to "Northwind" Database ...

A new Database Reference Window will open type the SQL Server name in the Server Name textbox and Select "Northwind" database from the Select or enter a database Name drop down , click on Test Connection button to make sure that the connection is successful , click Ok, Click OK.

Right click on the Solution Icon "CLR Demo" on the right pane  and click Add Trigger , Add New Item Window will appear with the trigger class selected , type in the Name "CustomersTrigger.cs"

and click Add  the Trigger class will be added to the project as shown in the figure below.

 

let's create our web service that we will pass to it the captured inserted values to insert them into another database.

click on File --> Add --> New Web Site  --> choose ASP.NET Web Service Template make sure that the selected Language is C# and change the name of the Project to "PassValues" and Click OK

The Web Service Project will be added to our solution as intended now , let's add our connection string value in the "Web.config" file in the Web service Project so right click on the Web Service Project and click on add New Item and choose Web Configuration File from the Add New Item Window and Click Add

double click on the "Web.Config" file and replace the following code

<connectionStrings/> Tag

    <connectionStrings>
        <add name="SQLcon" connectionString ="uid=your_username;pwd=database Password;data source=.;Initial Catalog=database_name"/>
    </connectionStrings>

 

this connection string that I've just wrote down is the one that the web service will connect on to throw the captured inserted data by our trigger.

to make the web Service work successfully you will have to run the following script in your SQL Server

USE [Northwind]
GO
/****** Object:  Table [dbo].[Users]    Script Date: 09/06/2007 12:43:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
    [UserName] [varchar](100) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [Northwind]
GO
/****** Object:  Table [dbo].[test1]    Script Date: 09/09/2007 12:08:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
    [CustomerName] [nchar](100) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]

Press F5 to execute the SQL Code

to create a Table named "Users" and another one named "Test" in the "Northwind" database.

 

let's write some code in our Web Service , double click on the "service.cs" file , copy and paste the following code in it :

[WebMethod]
 public void InsertIntoTemp(string CustomerName)
 {
     SqlConnection con = new SqlConnection();
     con.ConnectionString = ConfigurationManager.ConnectionStrings["sqlcon"].ToString();
     con.Open();
     SqlCommand com = new SqlCommand();
     com.Connection = con;
     com.CommandText = "Insert users values('" + CustomerName + "')";
     com.ExecuteNonQuery();
 }

Compile the code to make sure that it has no errors...

now you've created a Web service that has one web Method that takes one Parameter which is "CustomerName" and inserts it in the "users" Table in the "Northwind" database.

to attach our web service to the CLR project you will have to right click on the "CLRDemo" Project Icon and click on add Web Reference button.

click on Web services in this solution the following window will appear to you

click on the Service Link the following window will appear to you...

Type in the Web reference Name "PassValues" and click Add Reference button

 

let's get back to the our "CustomerTrigger.cs" class , double click on the "CustomerTrigger.cs" class and copy and paste the following code :

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

using CLRDemo.PassValues;
public partial class Triggers
{
    // Enter existing table or view for the target and uncomment the attribute line 

//Target Means = the name of the Table we need to monitor which is in our case Table "test" , Event Means = Monitors which Action

    [Microsoft.SqlServer.Server.SqlTrigger(Name = "CustomerTrigger", Target = "test", Event = "FOR INSERT")]
    public static void CustomerTrigger()
    {
        CLRDemo.PassValues.Service service = new CLRDemo.PassValues.Service();
        // Replace with your own code
        SqlTriggerContext triggerContext = SqlContext.TriggerContext;
        SqlPipe sqlPipe = SqlContext.Pipe;
        if (triggerContext.TriggerAction ==
            TriggerAction.Insert)
        {
            SqlDataReader sdr;

       // here I'll set the context of the sqlconnection I use as the same context of my Trigger
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {

      //open SQL Connection
                conn.Open();

      // Setting my SQL command
                SqlCommand cmd = new SqlCommand("SELECT * FROM INSERTED", conn);

      //Executing my command in the Data reader               

                sdr = cmd.ExecuteReader();

       //Checking if the data reader has Rows or not        

                  if (sdr.HasRows)
                {

         // read the data reader
                    while (sdr.Read())
                    {
         // passing the captured data to the created web Service

                        service.InsertIntoTemp(sdr[0].ToString().Trim());
                    }
                }

            }
        }

        SqlContext.Pipe.Send("Trigger FIRED");
    }
}

you will find a folder named Test Scripts Folder under the root of the"CLRDemo" project and under it "Test.sql" file; this file is used for testing , you can write in it the SQL test script you want to test your CLR code so just add in it the following code:

insert test values ('Karim')

where test is our table name in the "Northwind" database

and comment anything else...by putting "//" at the beginning of the line.

try to compile the project right now, the following error will appear in the output window

That's because SQL Server need another assembly to access the web services, a "Serialization Assembly". How can I do that? Simple, go to the project's properties, and set the feature "generates serialization assembly" to "on".

then go to the Database tab in the project properties to set the permission level to External because you are calling web service so you have to set the database permission level to External.

After you try to compile the code after making the Generate Serialization assembly to ON the Code will generate the following error ...

Calling web service methods requires serializing all the types being used to XML. Outside SQL Server, this serialization code is generated at runtime, compiled and loaded in the application. However, for security and reliability reasons, SQL Server does not allow you to dynamically load assemblies. Hence, to make this scenario work, you need to pre-generate the serialization assembly and register it in the database. Visual Studio 2005 beta2 allows you to generate the serialization assembly from your project. You need to turn the Generate serialization assembly option on in project properties a Build. You can also generate the serialization assembly using a tool called ‘sgen’ that is shipped with the .NET Framework SDK as follows:

>sgen.exe myAsm.dll

Where myAsm.dll is the assembly that you want to use inside SQL Server and contains code that is calling webservices. If you have installed Visual Studio 2005, you would usually find sgen at C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. When you run sgen, it would generate an assembly with the name myAsm.XmlSerializers.dll.

Once you have these two assemblies available. You need to register them in SQL Server as follows:

CREATE ASSEMBLY myAsm from ‘<path>\myAsm.dll’

with permission_set = EXTERNAL ACCESS

CREATE ASSEMBLY myAsmXml from ‘<path>\myAsm.XmlSerializers.dll’

with permission_set = SAFE

Visual Studio 2005 allows you to register the first assembly in SQL Server using the deploy feature in SQL Server projects. However, it does not support deploying the serialization assembly and you would need to manually register it. If you are using Visual Studio on the same machine as your SQL Server, you can use the pre / post deploy script feature in Visual Studio to automate this in your project. This can be done as follows:

1. Add the following to a file called predeployscript.sql:

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'myAsmXML')

DROP ASSEMBLY myAsmXML with NO DEPENDENTS;

2. Add the following to a file called postdeployscript.sql:

CREATE ASSEMBLY myAsmXML from '<path>\myAsm.XmlSerializers.dll'

3. Add these two files to your project by right clicking your project in Solution Explorer and Add an Existing Item. 

Make sure you add this in the root of the project and not in the Test Scripts. When you click deploy, Visual Studio will automatically run these scripts before and after the deployment of your project. However since the scripts are run on the SQL Server, the path must be relative to the machine on which you have SQL Server. If SQL Server and VS are on separate machines then you must make sure the path is a network path and accessible from SQL Server machine.

after this the try to compile the code and it will work just fine.

References:

http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx

 

I've faced this error earlier when I was trying to create a reporting services Subscription , I chose the delivery method as report server File share

I've typed the my file share path in the Path field and I've chosen the render format from the render format drop down and I've typed my intended credentials that I want to access the file share with in the User Name and Password fields and I've selected the schedule by pressing the Select Schedule button  and Clicking on OK button.

the following error has been generated to me

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help EXECUTE permission denied on object 'sp_add_category', database 'msdb', schema 'dbo'.

to solve the above error you should give the Execute permission to the SQL Server Agent that SRSS use by executing the following code in the SQL server

USE master

GO

GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole

GO

GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole

GO

GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole

GO

USE msdb

GO

-- Permissions for SQL Agent SP's

GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole

GO

GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole

GO

GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole

GO

The world today is heading towards business Intelligence with very fast steps, every company wants to know where it stands, where they should go and invest , what are there weaknesses, why and how to fix them and improve their business to compete with other companies and get ahead of 'em so I found that I should write down my humble experience in this field on couple of posts.

First let's make a fast business intelligence orientation:

What is Business Intelligence ?

§Business intelligence (BI) is a business management term, which refers to applications and technologies that are used to gather, provide access to, and analyze data and information about company operations. Business intelligence systems can help companies have a more comprehensive knowledge of the factors affecting their business, such as metrics (KPIS) on sales, production, internal operations, and they can help companies to make better business decisions.

§Business Intelligence should not be confused with competitive intelligence, which is a separate management concept.

What are the Advantages of the Business Intelligence ?

1. Productivity Return

§High degree of control over data security.

§Improved reporting and query capabilities.

§Integrated data across platforms, data sources and locations.

§Management reports at your fingertips, with real-time and worldwide access.

§Multi-country reporting and project planning, supported through one data warehouse solution.

§Support at sites across the world with localized representation in each country.

2. Technological Return

§Automated solutions to enable business growth without increasing head count.

§E-Commerce solutions.

§Flexible and scalable applications.

§Globalization of local products, services and functionality.

§Maintaining your competitive edge by being flexible and responsive to change.

§Reduced IT support requirements.

§User friendly, dynamic, web-enabled productivity tools for analysis.

3. Financial Return

§Enhanced target marketing efforts.

§Increase in customer retention and cross sales.

§Increase in customer satisfaction.

§Increase in new business acquisition.

§Increase in response rates from marketing campaigns.

§Increase in return on investment.

Business Intelligence Process

Preparing Data sources-->ETL Process -->Analysis Phase -->Situation Awareness -->Reports, Scorecards and Dashboards.

those are main Phase in creating a Business Intelligence Process first of all you have to define the data sources that you need to work on and then you need to develop the ETL process that will transform this data to the place you like and make the transformation you need on it , then you should develop the cubes you need to make some analysis on your data then you will have to define what do you want from this data so you can design your reports , scorecards and dashboards.