problem with c# stored procedure calling a class

I wrote a stored procedure in c# that calls a class. It deployed fine but when I try to execute it, I get the error...

Msg 6522, Level 16, State 1, Procedure UpdateJobAdSearch, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'UpdateJobAdSearch':
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 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at JobAd.WhaleJobAd.CreateSearchableRecord()
at JobAd.WhaleJobAd.CreateSearchableRecord(String JobId)
at StoredProcedures.UpdateJobAdSearch(String JobAdId)

I read an article on MSDN that didn't directly relate, but mentioned the same error message. It suggested using...

System.Data.SqlClient.SqlClientPermission pSql = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
pSql.Assert();

I tried that, but I get the same error.

I finally found the answer to this. I needed to set the projects properties > database permission level to external. However, I don't understand why. I've scaled back my code, pulled it out the class so everything executes in the stored procedures method call and all it does it create 2 connections to the local database and pull info from 6 tables and put that info into 1. It's not trying to access anything outside the database, so why does it have to be set to external? In order to get that to work, I had to then alter the database and turn trustworthy on which is scary because of the security holes that opens up.|||

cakewalkr7:

I've scaled back my code, pulled it out the class so everything executes in the stored procedures method call and all it does it create 2 connections to the local database and pull info from 6 tables and put that info into 1.

I think maybe connections opened by the class are considered as access to external resource. Did you try to open a connection in this way?

SqlConnection conn = new SqlConnection("context connection = true");

sql

problem while using sp_ExecuteSql

Hi all

I have a stored procedure as I placed below

create procedure ps_Select_Student
@.RollNo nvarchar(50),
@.Class int
AS
Begin
declare @.MainQuery nvarchar(4000)
set @.MainQuery = 'select StudentName where RollNo=@.RollNo and Class=@.Class'

exec sp_ExecuteSql @.MainQuery
End

Go

While executing this procedure in Query Analyser i am agetting an error that

must declare @.RollNo

What is the problem. Please help me out.

You have to declare those variables on param declaration parameter of sp_executesql

Code Snippet

create procedure ps_Select_Student

@.RollNo nvarchar(50),

@.Class int

AS

Begin

Declare @.MainQuery nvarchar(4000)

Declare @.ParamDecl nvarchar(4000)

set @.MainQuery = N'select StudentName where RollNo=@.RollNo and Class=@.Class'

set @.ParamDecl = N'@.RollNo nvarchar(50),@.Class int'

Exec sp_ExecuteSql @.MainQuery , @.ParamDecl, @.RollNo, @.Class

End

Go

Problem using custom assemblies in SSRS2005

I've created a custom assembly (in VB (VS-2005)) for use in some SSRS2005
reports.
project: ReportsLibrary
Public Class ReportsCodeLibrary
Public Shared Function ReturnXML(MVList as Object, Root As String, Node As
String, Element As String) As String
Dim ReturnString = ""
Dim sParamItem As Object
ReturnString = "<" & Root & ">"
For Each sParamItem In MultiValueList
ReturnString &= "<" & Node & "><" & Element & ">" & sParamItem & "</"
& Element & "></" & Node & ">"
Next
ReturnString &= "</" & Root & ">"
Return (ReturnString)
End Function
Public Shared Function Format(s As String) As String
Format = left(s,4) & "-" & mid(s,5,2) & "-" & mid(s,7,3) & "-" &
right(s,4)
End Function
End Class
Each of these functions work properly if I put them in the Code section. But
since they are being used in multiple reports, I want to have the code in one
place and use that assembly.
The project is compiled in "Release" configuration.
I attach this compiled dll (ReportsLibrary.dll) to the report in the
Report's properties "Reference" section. Since these are Shared functions, I
don't need to define individual classes on that sheet.
When I get this working, I'll copy the dll to the SSRS ReportServer\bin
directory on the SSRS computer.
On the TextBox's (txtTableField2) Value property, I enter the expression
"=ReportsLibrary.ReportsCodeLibrary.Format(Fields!fieldName.Value)"
When I preview the report in Visual Studio, I get these errors:
[rsErrorLoadingCodeModule] Error while loading code module: â'ReportsLibrary,
Version=1.0.0.0, Culture=neutral, PublicKeyToken=nullâ'. Details: Could not
load file or assembly 'ReportsLibrary, Version=1.0.0.0, Culture=neutral,
PublicKeyToken=null' or one of its dependencies. The system cannot find the
file specified.
[rsCompilerErrorInExpression] The Value expression for the textbox
â'txtTableField2â' contains an error: [BC30451] Name 'ReportsLibrary' is not
declared.
So, what am I missing? What do I need to do to get this assembly to work
with SSRS?
Thanks,
WayneIs the .dll copied to the following folder on your development machine:
"C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies"?
--
Matt
"Wayne" wrote:
> I've created a custom assembly (in VB (VS-2005)) for use in some SSRS2005
> reports.
> project: ReportsLibrary
> Public Class ReportsCodeLibrary
> Public Shared Function ReturnXML(MVList as Object, Root As String, Node As
> String, Element As String) As String
> Dim ReturnString = ""
> Dim sParamItem As Object
> ReturnString = "<" & Root & ">"
> For Each sParamItem In MultiValueList
> ReturnString &= "<" & Node & "><" & Element & ">" & sParamItem & "</"
> & Element & "></" & Node & ">"
> Next
> ReturnString &= "</" & Root & ">"
> Return (ReturnString)
> End Function
> Public Shared Function Format(s As String) As String
> Format = left(s,4) & "-" & mid(s,5,2) & "-" & mid(s,7,3) & "-" &
> right(s,4)
> End Function
> End Class
> Each of these functions work properly if I put them in the Code section. But
> since they are being used in multiple reports, I want to have the code in one
> place and use that assembly.
> The project is compiled in "Release" configuration.
> I attach this compiled dll (ReportsLibrary.dll) to the report in the
> Report's properties "Reference" section. Since these are Shared functions, I
> don't need to define individual classes on that sheet.
> When I get this working, I'll copy the dll to the SSRS ReportServer\bin
> directory on the SSRS computer.
> On the TextBox's (txtTableField2) Value property, I enter the expression
> "=ReportsLibrary.ReportsCodeLibrary.Format(Fields!fieldName.Value)"
> When I preview the report in Visual Studio, I get these errors:
> [rsErrorLoadingCodeModule] Error while loading code module: â'ReportsLibrary,
> Version=1.0.0.0, Culture=neutral, PublicKeyToken=nullâ'. Details: Could not
> load file or assembly 'ReportsLibrary, Version=1.0.0.0, Culture=neutral,
> PublicKeyToken=null' or one of its dependencies. The system cannot find the
> file specified.
> [rsCompilerErrorInExpression] The Value expression for the textbox
> â'txtTableField2â' contains an error: [BC30451] Name 'ReportsLibrary' is not
> declared.
> So, what am I missing? What do I need to do to get this assembly to work
> with SSRS?
> Thanks,
> Wayne|||Matt,
Thanks! this took care of it. If only I had found this documented somewhere...
"Matt" wrote:
> Is the .dll copied to the following folder on your development machine:
> "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies"?
> --
> Matt
>
> "Wayne" wrote:
> > I've created a custom assembly (in VB (VS-2005)) for use in some SSRS2005
> > reports.
> >
> > project: ReportsLibrary
> > Public Class ReportsCodeLibrary
> > Public Shared Function ReturnXML(MVList as Object, Root As String, Node As
> > String, Element As String) As String
> > Dim ReturnString = ""
> > Dim sParamItem As Object
> > ReturnString = "<" & Root & ">"
> > For Each sParamItem In MultiValueList
> > ReturnString &= "<" & Node & "><" & Element & ">" & sParamItem & "</"
> > & Element & "></" & Node & ">"
> > Next
> > ReturnString &= "</" & Root & ">"
> > Return (ReturnString)
> > End Function
> >
> > Public Shared Function Format(s As String) As String
> > Format = left(s,4) & "-" & mid(s,5,2) & "-" & mid(s,7,3) & "-" &
> > right(s,4)
> > End Function
> > End Class
> >
> > Each of these functions work properly if I put them in the Code section. But
> > since they are being used in multiple reports, I want to have the code in one
> > place and use that assembly.
> >
> > The project is compiled in "Release" configuration.
> > I attach this compiled dll (ReportsLibrary.dll) to the report in the
> > Report's properties "Reference" section. Since these are Shared functions, I
> > don't need to define individual classes on that sheet.
> > When I get this working, I'll copy the dll to the SSRS ReportServer\bin
> > directory on the SSRS computer.
> >
> > On the TextBox's (txtTableField2) Value property, I enter the expression
> > "=ReportsLibrary.ReportsCodeLibrary.Format(Fields!fieldName.Value)"
> > When I preview the report in Visual Studio, I get these errors:
> > [rsErrorLoadingCodeModule] Error while loading code module: â'ReportsLibrary,
> > Version=1.0.0.0, Culture=neutral, PublicKeyToken=nullâ'. Details: Could not
> > load file or assembly 'ReportsLibrary, Version=1.0.0.0, Culture=neutral,
> > PublicKeyToken=null' or one of its dependencies. The system cannot find the
> > file specified.
> > [rsCompilerErrorInExpression] The Value expression for the textbox
> > â'txtTableField2â' contains an error: [BC30451] Name 'ReportsLibrary' is not
> > declared.
> >
> > So, what am I missing? What do I need to do to get this assembly to work
> > with SSRS?
> >
> > Thanks,
> > Wayne|||Hi Wayne,
You're right - I had to dig around in samples to find it. Difficult to
search on unless one knows to look for "Custom Report Item" in BOL.
Cheers,
Matt
"Wayne" wrote:
> Matt,
> Thanks! this took care of it. If only I had found this documented somewhere...
> "Matt" wrote:
> > Is the .dll copied to the following folder on your development machine:
> > "C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies"?
> > --
> > Matt
> >
> >
> > "Wayne" wrote:
> >
> > > I've created a custom assembly (in VB (VS-2005)) for use in some SSRS2005
> > > reports.
> > >
> > > project: ReportsLibrary
> > > Public Class ReportsCodeLibrary
> > > Public Shared Function ReturnXML(MVList as Object, Root As String, Node As
> > > String, Element As String) As String
> > > Dim ReturnString = ""
> > > Dim sParamItem As Object
> > > ReturnString = "<" & Root & ">"
> > > For Each sParamItem In MultiValueList
> > > ReturnString &= "<" & Node & "><" & Element & ">" & sParamItem & "</"
> > > & Element & "></" & Node & ">"
> > > Next
> > > ReturnString &= "</" & Root & ">"
> > > Return (ReturnString)
> > > End Function
> > >
> > > Public Shared Function Format(s As String) As String
> > > Format = left(s,4) & "-" & mid(s,5,2) & "-" & mid(s,7,3) & "-" &
> > > right(s,4)
> > > End Function
> > > End Class
> > >
> > > Each of these functions work properly if I put them in the Code section. But
> > > since they are being used in multiple reports, I want to have the code in one
> > > place and use that assembly.
> > >
> > > The project is compiled in "Release" configuration.
> > > I attach this compiled dll (ReportsLibrary.dll) to the report in the
> > > Report's properties "Reference" section. Since these are Shared functions, I
> > > don't need to define individual classes on that sheet.
> > > When I get this working, I'll copy the dll to the SSRS ReportServer\bin
> > > directory on the SSRS computer.
> > >
> > > On the TextBox's (txtTableField2) Value property, I enter the expression
> > > "=ReportsLibrary.ReportsCodeLibrary.Format(Fields!fieldName.Value)"
> > > When I preview the report in Visual Studio, I get these errors:
> > > [rsErrorLoadingCodeModule] Error while loading code module: â'ReportsLibrary,
> > > Version=1.0.0.0, Culture=neutral, PublicKeyToken=nullâ'. Details: Could not
> > > load file or assembly 'ReportsLibrary, Version=1.0.0.0, Culture=neutral,
> > > PublicKeyToken=null' or one of its dependencies. The system cannot find the
> > > file specified.
> > > [rsCompilerErrorInExpression] The Value expression for the textbox
> > > â'txtTableField2â' contains an error: [BC30451] Name 'ReportsLibrary' is not
> > > declared.
> > >
> > > So, what am I missing? What do I need to do to get this assembly to work
> > > with SSRS?
> > >
> > > Thanks,
> > > Wayne