SQL Server

How to assign a strong name key to 3rd party assembly?

Karim — Wed, 21 May 2008 18:00:00 GMT

I faced this problem during working and I found that I have two approaches to acheive this :

either I ask the 3rd party developer to assign strong name to the dll or you do the following:

you should use Ildasm.exe tool to dissamble the *.exe or *.dll assembly to *.il and *.res (compile resource script)

you can find Ildasm.exe tool at this path on your machine C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin

open the *.exe or *.dll assembly you want using the IL DASM and follow the images to dissamble the file

then you reassemble the *.il file again using Ilasm.exe and passing to it the strong name you desire and here is the code

you can find Ilasm.exe tool at this location C:\WINDOWS\Microsoft.NET\Framework\[your version of asp.net] Ex. v2.0.50727

first you will need to create your strong name key using sn.exe tool which you will find it in this location C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin

so to create a strong name key type in the visual studio 2005 command prompt the following sn.exe -k name_of_the _key.snk.

after you create the strong name key you will find in this location C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin

then use the Ilasm.exe to reassemble the *.il file again by typing the following code in the visual studio 2005 command prompt Ilasm.exe filename.il /key:name_of_the_strongnamekey.snk this will generate *.exe file from the *.il

if you want to generate *.dll file you will have to add the following option /dll to the code to be like this

Ilasm.exe filename.il /dll /key:name_of_the_strongnamekey.snk

refernces:

http://msdn.microsoft.com/en-us/library/aa309387(VS.71).aspx

http://msdn.microsoft.com/en-us/library/496e4ekx(VS.80).aspx

http://msdn.microsoft.com/en-us/library/f7dy01k1(VS.80).aspx

Reporting services and IIS default Web Site Identifier

Karim — Mon, 31 Mar 2008 18:20:00 GMT

SQL Server Reporting Services cannot use the Default Web site because it has an identifier that the SQL Server Reporting Services setup cannot handle. You can either reinstall IIS to have IIS create the Default Web site, or modify the IIS metabase by changing the identifier for Default Web site to 1.

When you see this error and you want to change the IIS default web site identifier to 1, use the following steps:

Click Start --> Run --> type the following:

CSCRIPT %SYSTEMDRIVE%\Inetpub\AdminScripts\adsutil.vbs STOP_SERVER W3SVC/old_identifier_number
CSCRIPT %SYSTEMDRIVE%\Inetpub\AdminScripts\adsutil.vbs MOVE W3SVC/old_identifier_number W3SVC/new_identifier_number
CSCRIPT %SYSTEMDRIVE%\Inetpub\AdminScripts\adsutil.vbs START_SERVER W3SVC/new_identifier_number

Hint:

Replace old_identifier_number with the old id of the web site

Replace new_identifier_number with the new id of the web site

IE Transitions (Nice Stuff)

Karim — Wed, 21 Nov 2007 10:25:00 GMT

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:

Blinds

Properties: bands (default=10), Direction (default="down"), Duration ( no default)

Barn

Properties: duration, motion, orientation (default="vertical")

CheckerBoard

Properties: Direction (default="right"), squaresX (default=12), squaresY (default=10)

Fade

Properties: duration, overlap (default=1.0)

GradientWipe

Properties: duration, gradientSize (default=0.25), motion

Inset

Properties: duration

Iris

Properties: duration, irisStyle (default="PLUS"), motion

Pixelate

Properties: duration, maxSquare (default=25)

RadialWipe

Properties: duration, wipeStyle (default="CLOCK")

RandomBars

Properties: duration, orientation (default="horizontal")

RandomDissolve

Properties: duration

Slide

Properties: bands (default=1), duration, slideStyle (default="SLIDE")

Spiral

Properties: duration, gridSizeX (default=16), gridSizeY (default=16)

Stretch

Properties: duration, stretchStyle (default="SPIN")

Strips

Properties: duration, motion

Wheel

Properties: duration, spokes (default=4)

ZigZag

Properties: duration, gridSizeX, gridSizeY

Property descriptions

bands (integer)
Sets or retrieves the number of strips into which the content is divided during the transition.

Direction (string)
Sets or retrieves the direction of motion in the transition.

Direction: "up" | "down" | "right" | "left"

Duration (floating point)
Sets or retrieves the length of time the transition takes to complete. The value is specified in seconds.milliseconds format (0.0000).

gradientSize
Sets or retrieves the percentage of the object that is covered by the gradient band.

Floating-point that specifies or receives a value that can range from 0.0 to 1.0.0.0 the gradient band has no width as new content is revealed.
0.0 the gradient band has no width as new content is revealed.
0.25 Default. 25% of the object is covered by a gradient band.
1.0 100% of the object is covered by a gradient band.

gridSizeX
Sets or retrieves the number of grid columns used for the filter. The value can range from 1 to 100.

Integer that specifies or receives the number of grid columns. 16 Default.

gridSizeY
Sets or retrieves the number of grid rows used for the filter. The value can range from 1 to 100.

Integer that specifies or receives the number of grid rows. 16 Default.

irisStyle
Sets or retrieves the shape of the Iris filter aperture.

String that specifies or receives one of the following values: DIAMOND Diamond-shaped aperture.
CIRCLE Circular aperture.
CROSS X-shaped aperture.
PLUS Default. Plus sign-shaped aperture.
SQUARE Square aperture.
STAR Star-shaped aperture.

maxSquare
Sets or retrieves the maximum width in pixels of a pixelated square.

Integer that specifies or receives a width that can range from 2 to 50 pixels.50 Default.

motion
Sets or retrieves the value that indicates whether new content is revealed from the outside or the inside first.

"out" Default. The transition moves from the center of the object toward the borders of the object.
"in" The transition moves from the borders of the object toward the center.

orientation
Sets or retrieves the value that indicates whether the filter effect is horizontal or vertical.

"horizontal" Transition lines are horizontal.
"vertical" Transition lines are vertical.

overlap
Sets or retrieves the fraction of the transition's duration that both original and new content are displayed.

Floating-point that specifies or receives a value that can range from 0.0 to 1.0.
1.0 Default.

slideStyle
Sets or retrieves the method used to reveal the new content.

String that specifies or receives one of the following values:
HIDE Default. Slides bands of original content out, exposing new content.
PUSH Slides bands of new content in, pushing original content out.
SWAP Alternating bands expose new content, or push original content out, at the same time.

spokes
Sets or retrieves the number of wedges that the content is divided into during the transition.

Integer that specifies or receives one of the following possible values:
2 - 20 Number of moving spokes that divide the content into wedges.
4 Default.

squaresX
Sets or retrieves the number of columns for the CheckerBoard transition.

Integer that specifies or receives any positive value greater than or equal to 2. 12 Default.

squaresY
Sets or retrieves the number of rows for the CheckerBoard transition.

Integer that specifies or receives any positive value greater than or equal to 2. 10 Default.

stretchStyle
Sets or retrieves the method used to reveal the new content.

String that specifies or receives one of the following possible values:
HIDE Stretches new content over original content from left to right.
PUSH Stretches new content in and squeezes original content out, moving from left to right. This motion resembles a cube rotating from one face to another.
SPIN Default. Stretches new content over original content from the center outward.

wipeStyle
Sets or retrieves the method used to reveal the new content.

String that specifies or receives one of the following possible values. CLOCK Default. Sweeps around the center, clockwise from the top.
WEDGE Sweeps around the center in both directions from the top.
RADIAL Sweeps from the top to the left side, with one end of the sweep anchored on the upper left corner.

I hope this helps :)

Reporting Services 2005 Critical Issue

Karim — Wed, 21 Nov 2007 10:24:00 GMT

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.

Undocumented Stored Procedures

Karim — Wed, 21 Nov 2007 10:21:00 GMT

How many of you wants to run a script on the whole tables in the database and don't want to do this manually???

well I'm one of those people who would like some sort of stored procedures which loops in all tables and apply the same script on all the tables so I've searched for this and finally I found the Undocumented SQL System Stored Procedures, they are punch of system Stored procedures which very helpfull and effective, I'll state down here two of'em and you can see the rest from here

sp_MSforeachdb

Sometimes, you need to perform the same actions for all databases. You can create cursor for this purpose, or you can also use the sp_MSforeachdb stored procedure to accomplish the same goal with less work.

For example, you can use the sp_MSforeachdb stored procedure to run a CHECKDB for all the databases on your server:

EXEC sp_MSforeachdb @command1="print '?' DBCC CHECKDB ('?')"

sp_MSforeachtable

Sometimes, you need to perform the same actions for all tables in the database. You can create cursor for this purpose, or you can also use the sp_MSforeachtable stored procedure to accomplish the same goal with less work.

For example, you can use the sp_MSforeachtable stored procedure to rebuild all the indexes in a database:

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

How you detect the time leakage in your SSIS Package and How can you optimize it?

Karim — Wed, 21 Nov 2007 10:13:00 GMT

This article is talking about the optimization of the SSIS, I've attended three sessions for Donald Farmer, Principal Program Manager for Microsoft SQL Server Integration Services in Microsoft Redmond,Seattle in the MDC 2007 for the Middle east developers which was held in Cairo International Conference Center; the three sessions were :

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.

SQL CLR Integartion

Karim — Wed, 21 Nov 2007 09:27:00 GMT

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.