Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 .NET Inside SQL Server (2005)
 CLR bug

Author  Topic 

aex
Yak Posting Veteran

60 Posts

Posted - 2008-07-23 : 22:37:31
I write a clr stored procedure which is called by a non clr stored procedure. The clr stored procedure processes million of records with huge number of batches, i.e. 50-80. I get the following error before my clr stored procedure finished executing:

A .NET Framework error occurred during execution of user-defined routine or aggregate "BulkInsertClrSP": System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.PermissionSet.Demand() at System.Data.Common.DbConnectionOptions.DemandPermission() at System.Data.SqlClient.SqlConnection.PermissionDemand() at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at BulkCopyService.BulkInsert(DataTable dataTable, String destinationTableName) at StoredProcedures.BulkInsertClrSP() .

I refer to microsoft support article at:

http://support.microsoft.com/kb/928083

From the article, it suggests me to apply the hotfix but recommended to use the software update such as sp1, sp2, etc. I've installed SP2 and the problem persist. So it makes to me to think to install the hotfix. But before I install the hotfix, I just wanna know is the hotfix for the problem has been already included in SP2. If that is the case, then it is no point for me to install the hotfix. Any idea?


aex

aex
Yak Posting Veteran

60 Posts

Posted - 2008-07-24 : 03:35:35
Ok, I got resolution for this, and it is tricky. Also, this is not a perfect solution. If you ever face same problem as mine, what you need to do is to create an EXTERNAL_ACCESS or UNSAFE Assemblies. Here is the useful link for guiding on how to create the abovementioned assemblies:

[url]http://msdn.microsoft.com/en-us/library/ms345106.aspx[/url]

I do not want to elaborate what is going on in details because there are people who have already post the thread. Following are the useful link for the problem I face, and the blog author does give some inside on what's going on with CLR regarding memory usage.

[url]http://blogs.msdn.com/sqlclr/archive/2006/03/24/560154.aspx[/url]

aex
Go to Top of Page

senol01
Starting Member

3 Posts

Posted - 2008-07-24 : 03:56:26
hi
step 1 asp.net c# and class

web config
Baglanti 'sql connection string
input uname
input ip or local
input pname
input dbname
<add key="Baglanti" value="data source=ip or local;UID=uname; pwd=pname;initial Catalog=dbname"/>

class
public static SqlConnection sqlBaglanti = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["Baglanti"]);
Store_ProcedureRun_Sil delete record class
public static void Store_ProcedureRun_Sil(string Store_Procedure_Name, string Store_Procedure_ID, string Store_Procedure_IDValue)
{

SqlCommand sqlCommand = new SqlCommand(Store_Procedure_Name, sqlBaglanti);

sqlCommand.CommandType = CommandType.StoredProcedure;

SqlParameter DeleteID = sqlCommand.Parameters.Add(Store_Procedure_ID, SqlDbType.Int);
DeleteID.Value = Store_Procedure_IDValue;

if (sqlCommand.Connection.State == ConnectionState.Closed)
{
sqlBaglanti.Open();
}

sqlCommand.ExecuteNonQuery();
sqlBaglanti.Close();
}

step 2

desing

personel_kontrol.Store_ProcedureRun_Sil("News_Delete", "@HaberID", Request.QueryString["sil"]);

yeah

thanks sqlteam.com
http://www.evdenevenakliyatbul.com/
Go to Top of Page
   

- Advertisement -