  Integrate DotNet in Sql Server 2005
[25 mn de lecture - paru le 10/29/2005 6:06:13 PM - Public : Confirmé]
|
|
Auteur
2. To create stored procedure with Visual Studio 2005
2.1. To create stored procedure
Choose in Exploring it solution "Stored Procedure"
Visual studio 2005 generates you directly a class partial StoredProcedures. As well as the attribute [ Microsoft.SqlServer.Server.SqlProcedure ] which will make it possible SqlServer 2005 to recognize that it is a stored procedure.
Not need to open connection because you will be connected to the object time of your procedure.
Simple example
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
partial public class StoredProcedures
{
[ Microsoft.SqlServer.Server. SqlProcedure ]
public static void MaProcedureStokee()
{
// your code here Could
SqlContext Pipe.Send( "Hello Word!!!" );
}
};
|
2.2. To deploy your procedure on Sql Server 2005
Compile your project. Here is your DLL east creates.
Then you must deploy your DLL,
Click right on the project > Deploy
To rock in Sql Server Management Studio (in the past undertaken to manage)
Make Refresh of your data base
In the tree structure of your data base: Programmabilty > > Assemblies > > SqlServeurProjectSupinfo. The DLL was well spread.
In the repertory: Programmabily > > Stored Procedures > > dbo.MaProcedureStokee you can see your stored procedure.
It is not of Transact SQL and if you wish to modify it you must modify your code in Visual Studio 2005.
To carry out the procedure you must create a new solution in SQL server 2005.
- Connected
- To create a new file of request
2.3. To activate the integration of the CLR in SQL Server 2005
If you have the message:
Msg 6263, Level 16, State 1, Line 1
Execution of to use code in the NET Framework is disabled. Use sp_configure "clr enabled" to enable execution of to use code in the NET Framework.
with the excécution of your stored procedure, it is that Frame work NET2 is decontaminated by defect in Sql Server 2005.
You must go in Sql server 2005 Surface Area Configuratoion > > Surface Area Configuration for Features.
Go in DataBase Engine > > CLR Intégration and coachman Enable CLR Integration, then Appliquer.
You can now carry out your stored procedure.
2.4. Safety and permissions of execution of the assemblies
It is necessary to be vigilant by defect you cannot carry out assemblies in SQL Server 2005 which make another thing that actions related to the data base.
For example if you wish to create a trigger which to create a file of loggues on your server with each time one removes a user in the data base, that it store the hour and the date of suppression and that in more one removes the account directly in Active Directory. Then you must go in the properties of your project > > in DataBase is to grant permissions external .
You have the choice between 3 levels:
- Safe: protected mode no access except to SQL Server 2005
- external: the assembly will have access to external sources like the access to the file, the regist, the networks...
- Unsafe: the protected mode which gives the full Access to the CLR in Sql Server 2005
|
15 Visites
Michel DEGREMONT
[25 mn de lecture - paru le 10/29/2005 4:41:28 PM - Public : Expert]
[10 mn de lecture - paru le 10/29/2005 4:32:47 PM - Public : ÐÂÊÖ]