I've read a blog post for Nikhil Kothari , he was talking about Fajax or the fake AJAX which he means the code that you write to simulate the effect of the AJAX but it ain't AJAX, so i liked the idea and i've searched for most of these effects that you can embbed in your site and gives you the feeling that there is AJAX in your site

Enable page transitions

 When you want to see page transitions you must have enabled them with:

 Tools - Options - Advanced - Browsing - Enable page transitions

Applying a transition

 You apply a transition by added a special META tag to the heading of your page: For a page exit transition you use:

We have faced a little problem in here with the reporting services 2005; suddenly it starts to log errors in the event log without no good reason.

Here is the error:

Error rsReportServerDisabled : The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content and then restart the service

After we've made investigations about this error it appears that it's cause was the changing of the user that was running the reporting services successfully.

when you want to change the user that running the reporting services... there are some steps to do first to avoid seeing that error.

How to Change Service Accounts Using the Reporting Services Configuration Tool

Use the following steps to reconfigure the Report Server Windows service account: Start the Reporting Services Configuration tool and connect to the report server. For more information, see How to: Launch Reporting Services Configuration. On the Windows Service Identity page, specify the new account and password. Click Apply. When prompted to backup the symmetric key, type a password and file name for the symmetric key backup, and click OK. Each step in the service account update is recorded in the tasks panel at the bottom of the page. The Report Server Windows service will be stopped and restated during the account update. Errors that might occur include login failures (for example, if you mistype an account or password). In the unlikely event that the symmetric key is not restored, you can perform this step yourself clicking Restore in the Encryption Keys page. To verify that the account has been reset properly, open a report that uses stored credentials. If you cannot restore a backup copy of the key, you must delete the encrypted content. For more information, see Deleting and Re-creating Encryption Keys ,Backing Up and Restoring Encryption Keys but i don't recommend that try to run this command in the command utility:

C:\Program Files\Microsoft SQL Server\80\Tools\Binn> rsactivate -r -c"C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config"

you should substitute your installation location with mine in the above command.

I hope this helps, if not and try this solution, if not and you can drop down the changing user issue; try to start the service with the previous user that used to run successfully and everything will be Ok.

• High Performance Data Integration with SQL Server Integration Services
• Using Analysis Services and Data Mining to understand your customers
• Reporting Services, Report Builder and Sharepoint

I'll talk in here about the first session which was from my point of view the most intresting, First he described the difference between the DTS and the SQL Server Integration Services and it's Huge from my experience in this field and then He described the OVAL Concept which He meant with it the following:

O --> Operations

V --> Volume

A --> Application

L --> Location

Let's discuss each and everyone of these factors:

Operations: All the operations that happens during the data transfer whether it was defined or hidden done by the system.

Volume: The Volume of the data that will be transfered, it differs whether i'm movind a large amount of data ( Millions of rows ) or small amount (hundreds) in the time it takes to be moved.

Application: We have to take care which application we will use to move the data , if we are moving a bulk amount of data without doing any operations , we can use BCP instead of SQL Server Integration Services , becasue it will saves us time.

Location:  We have to the Source and Destination , the Type of network between them and the type of the Harddisk we are using in both places , cause may be when i change the hardware we save time and this for many company will be very benificial.

so the conclusion is that we have to take care of these four factors when we design a SSIS package.

The second thing that i want to talk about is How you detect the time leakage in your Package and How can you optimize it?

Donald Farmer has described this nice method of calculating the time of execution in the package and how can we detect the leakage;

first we run the package as it is and we see the execution time it takes let assume that it took 29 sec

then the STD execution time was 29 sec

Secondly we replace the destination with the Row Count component which is very useful component in here; it just count rows and we see the execution time of the package without the destination let's assume its gonna be 20 sec then STC took 20 sec to caluculate the time the data took to be transfered to the destination D = STD - STC= 29-20 =9 sec
To calculate the source time, you will Replace the destinaiton and the operations with the Row Count Component and execute the package and let's caluclate the execution time, let's assume it will be 15 sec; so we know now that the source took about 15 sec then by small mathematical operation we will know that the Operations took T = STC - SC= 20-15 = 5 sec

To Optimize this Integaration Package let's analyze the situation , I guess that the operations doesn't take much time and the destination too, may be If we investigated more we will find that the problem issued from the Old Hard disk we use in the source which tooks us too much time to read the data , so it won't matter how much time and money we invest to develop a new package the problem will still in the old harddisks so if we replaced them with a new one the performance will be enhanced significantly.

This was the end of the session I hope you like it as I did.

Thank you Donald.

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

Well many will ask what is the SQL CLR , 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.

Hint:

you may face some problems regarding the database compatability version , so you will have to run this query in the SQL Server 2005 to ovveride this issue :

sp_dbcmptlevel databaseName, 90 -- the level of comatablility 

if you faced any other problems during the implementation feel free to contact me.