public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString CLR_GET_SCHEMA(string tablename,int i) { // Put your code here List<string> strlist=new List<string>(); SqlConnection connectionstring = new System.Data.SqlClient.SqlConnection("server=localhost;database=S7;Integrated Security=true"); SqlCommand selectcommand = new System.Data.SqlClient.SqlCommand(); selectcommand.Connection = connectionstring; selectcommand.CommandText = "USP_SCHEMA"; selectcommand.CommandType = CommandType.StoredProcedure; selectcommand.Parameters.Add("@TABLENAME", SqlDbType.VarChar, 50); selectcommand.Parameters["@TABLENAME"].Value = tablename; connectionstring.Open(); SqlDataReader reader=selectcommand.ExecuteReader() ; while (reader.Read()) { strlist.Add(Convert.ToString(reader[0])); } return strlist[i]; } };
Quote:Msg 6522, Level 16, State 2, Line 2 A .NET Framework error occurred during execution of user-defined routine or aggregate "CLR_GET_SCHEMA": 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 UserDefinedFunctions.CLR_GET_SCHEMA(String tablename, Int32 i)
USE AdventureWorks GO ALTER PROC USP_SCHEMA @TABLENAME VARCHAR(50) AS BEGIN SELECT TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=@TABLENAME END GO EXEC USP_SCHEMA 'PRODUCT'
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)