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.
| 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/928083From 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 |
 |
|
|
senol01
Starting Member
3 Posts |
Posted - 2008-07-24 : 03:56:26
|
| hi step 1 asp.net c# and classweb configBaglanti 'sql connection string input unameinput ip or localinput pnameinput 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 desingpersonel_kontrol.Store_ProcedureRun_Sil("News_Delete", "@HaberID", Request.QueryString["sil"]);yeahthanks sqlteam.comhttp://www.evdenevenakliyatbul.com/ |
|
|
|
|
|
|
|
|
CLR bug