CLR Stored Procedure "Failed to get permission to execute"

Hello All,
This is variant/continuation of a problem I've discussed on the
sqlserver.programming group (Thanks to Erland for a lot of help over there).
I thought it might benefit me to raise the issue here as well.
In a nutshell:
I have a C# stored procedure (called SProc, in cs.dll, but "imported" as
"csfn") that uses PInvoke to access a function in a C++ dll (called
cpp.dll). The function is __declspec(dllexport)'d. If the C++ dll is
UNMANAGED this now seems to work OK. (Thank again to Erland helping me get
this far). Now, if I add some managed code to the C++ dll (leaving the
unmanaged entry point unchanged, but now compiling with /clr) I get a
"Failed to get permisssion to execute" message as below.
I've tried all the obvious (to me anyway) ways of adding the appropriate
permissions. I've used the "Trust Assembly" node of the .Net 2.0
Configuration tool to give "Full Trust" to both cs.dll AND cpp.dll but it
still fails. I've confirmed that I can call the C++ dll from a c# EXE from
the command line, and that works OK.
I'd really appreciate any help.
/john
Error text follows.
Msg 6522, Level 16, State 1, Procedure csfn, Line 0
A .NET Framework error occurred during execution of user defined routine or
aggregate 'csfn':
System.IO.FileLoadException: Could not load file or assembly 'cpp,
Version=11.0.1.0, Culture=neutral, PublicKeyToken=eb5ebc232de94dcf' or one
of its dependencies. Failed to grant permission to execute. (Exception from
HRESULT: 0x80131418) --> System.Security.Policy.PolicyException: Execution
permission cannot be acquired.
System.Security.Policy.PolicyException:
at System.Security.SecurityManager.ResolvePolicy(Evidence evidence,
PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset,
PermissionSet& denied, Boolean checkExecutionPermission)
at System.Security.SecurityManager.ResolvePolicy(Evidence evidence,
PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset,
PermissionSet& denied, Int32& securitySpecialFlags, Boolean
checkExecutionPermission)
System.IO.FileLoadException:
at DotNetFun.SqlServer.SProc.CPPTest()
at DotNetFun.SqlServer.SProc.CPPTest()
at DotNetFun.SqlServer.SProc.PrintMessage(String Message)
.Hello Eric,
The consensus is (I think), that you can use pInvoke to call an
unmanaged DLL from a managed stored procedure. However, it looks like
calling a MIXED (ie managed and unmanaged) DLL is not going to work. You can
look at the thread I started on the sqlserver.programming group for more
information.
It seems that mixed and/or managed DLLs need to be added to SQL with
CREATE ASSEMBLY, and this fails on a mixed dll, even if set it as UNSAFE.
I'd like to establish if that is expected to work, but I suspect it is
failing for good reason.
/john
"Eric" wrote in message news:200762618736eric_hackett@.kindermorg
an.com...
> Did you find any satisfaction for this? I have a nearly identical problem.
> Using a third party dll, wrapping it with my own dll, and then writing CLR
> stored proc to use wrapped function. Other functions in the stored proc
> dll work. Only the ones that need external resources are choking.
> Please reply here if you get this working.
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com

CLR programming: best place to define utilities functions

Hallo,

I find the CLR integration very handy and I would like to use it in the proper way but I need some helps because is not easy to find "best practice" programming info.

This is what I have to do: from a "period" coming from the DB I have to compute start/End date and save them in the DB.

The computation involves regular expression and several functions that makes several calculations on date type variable.

I have created my UserDefinedFunction class that contains the TV function to return a row with 2 fields (startdate,enddate).
Now my question is: what is the base place to define my utilities functions that perform the computation?
In the same class or it is better to add a new class?

For example I have created a module that contains all the Regular expressions patterns.

Any help is appreciated!

Thankx

Marina B.

I think that this is no matter of a SSQL Server decision rather than a cidong decision. It is always good to have a namespace schema and separated classes which allow you to reuse your your. I would split the regular expressions in you case out of the datetime classes if you want to use them in other projects as well. If this is an internal regular expression calculation you should use them in the same class (and make them evtl. internal if you don′t want to call them directly from SQL Server).

Jens K. Suessmeyer.

http://www.sqlserver2005.de

CLR integration - Could not find Type '?' in assembly '?'.

Hi,

I'm new to Integration services and .Net programming but am trying to
create a dll that I can access from Sql server 2005.

The dll read's an xml file and carries out some processing. I've run
the code as an console app and it works fine.

I have created the assembly in sqlserver thus:

create assembly PinCodeLoader from
'C:\PinCodeLoader\PinCodeLoader\PinCodeLoader\bin\Debug\PinCodeLoader.dll'
with permission_set = external_access

But when I try to reference the assembly from a stored proc

create procedure dbo.interface_processPinCodefile(@.filename
nvarchar(1024))
as EXTERNAL name PinCodeLoader.PinCodeloader.Main

I get the following error:

Msg 6505, Level 16, State 1, Procedure interface_processPinCodefile,
Line 3
Could not find Type 'PinCodeloader' in assembly 'PinCodeLoader'.

I understand the context of the syntax should be
assembly_name.class_name.method_name. The first lines of the code in
the DLL are as follows

namespace PinCodeLoader
{
class PinCodeLoader
{
static void Main(string[] args)
{

Therefore assembly = PinCodeLoader, class_name = PinCodeLoader and
method_name = Main. Which should equal
EXTERNAL name PinCodeLoader.PinCodeloader.Main, I thought.

Has anybody come across this or can they offer any assistance?

Many thanks,

Paul

Hi, try to compile the .vb file with this command line:

csc.exe /t:library PinCodeLoader.bv (Put the correct name of the .vb file)

If this work look at the properties of the project and search for 'root namespace' (or something similar), and delete it, then try again compiling the entire solution from de IDE...

Good luck

Alejandro F.

|||

You need to include the namespace as well:

create procedure dbo.interface_processPinCodefile(@.filename
nvarchar(1024))
as EXTERNAL name PinCodeLoader.[PinCodeLoader.PinCodeloader].Main

However, you still won't be able to create your proc because SQL can't map nvarchar(1024) to the string array args.

|||

Thanks for your replies guys. I didn't actually try that method but found a way around it.

In my c# program I removed the reference to a namespace, as I was told it's not really necessary and changed the code as follows.

public class PinCodeLoad

{

public static void Main(string filename,string datasource,Int32 timeout, string companyname)

{

So instead of using the args parameter I'm implictly defining them. Plus I preceded my class and method with public

I kept the stored proc as before.

I hope that helps anybody else.

Paul

Client-side programming patterns/idioms for SQL Mobile 2005?

Hello,

Where can I get sample code, or a pointer to sample code demonstrating common programming pattern(s) for a client of SQL Mobile 2005 (where the client is also running on the device)?

For eg, with ADO.NET, I'd do something like:

SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = conn.CreateCommand();
conn.CommandText = “--...my query…”;
SqlDataReader reader = conn.ExecuteReader();
// loop and process the data

What are the corresponding APIs and calls that I’d use for a SQL Mobile 2005 application using the latest CF.Net 2.0 ADO.Net APIs (or whatever they’re called)? I could only find a couple of articles on MSDN, both applying to SQLCE 2.0 and not SQL Mobile:

[1]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnroad/html/road05222002.asp
[2]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnppcgen/html/ppcdatabase.asp

If this is documented in Books Online, how can I get to it? I couldn’t find it.

Thanks in advance!

-Ravi

The samples are in books on line of SQL Mobile and .NET CF 2
http://www.microsoft.com/sql/editions/sqlmobile/overview.mspx
http://msdn.microsoft.com/mobility/sqlmobile/default.aspx|||Navigating from the above links to get to the information I need is extremely cumbersome. For now, a search on MSDN2 led me to the following page, on which the link [Using Programming Interfaces] is exactly what I was looking for:

http://msdn2.microsoft.com/en-us/library/ms172961

Thanks,
ravi|||

For the benefit of others who may have the same question, I thought I would post some sample code that demonstrates a client connection programming pattern for SQL Mobile clients:

string dbFilePath = Environment.GetFolderPath(Environment.SpecialFolder.Personal) + "/data.sdf";
string connectionString = string.Format("Data Source = {0};", dbFilePath);
using (SqlCeConnection conn = new SqlCeConnection(connectionString)) {
try {
/* open the connection, get a command */
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();

cmd.CommandText = @."CREATE TABLE myTable(i int primary key)";
cmd.ExecuteNonQuery();
}
catch (Exception e) {
string logMessage = e.Message;
/* logging code goes here */
}
finally {
conn.Close();
}
} // end using

Note that this code is intended for a client application running on a device that is attempting to connect to a SQL Mobile database on the same device.

|||

Ravi Subramanian wrote:

Note that this code is intended for a client application running on a device that is attempting to connect to a SQL Mobile database on the same device.

Can you actually connect to a SQL Mobile db on another device? I thought it always had to be on the same device as your app.
Can anyone confirm this?|||SQL Mobile Client app and the DB should be on the same device except in the following scenarios:
1) SQL Server 2005 Management Studio (running on Desktop) can be used to connect to a SQL Mobile DB on the device
2) VS 2005 Management Studio (running on Desktop) can be used to connect to a SQL Mobile DB on the device

Thanks,
Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

Client-side or server-side XML support?

What are the advantages/disadvantages of using either
client programming support in SQL server (referred to as
SQLXML) or server-side support - especially when
extracting formatted information from SQL server into a
C# web service? Which one is faster? What type of support
should be used in what circumtances, e.g. when IIS and
SQL server run on the same machine, when extracted
information is more/less voluminous?
Thanks in advance.
Jens
If you are talking about extracting information from the database only, then
using ADO/ADO.net with FOR XML is normally slightly better in performance,
since the XPath against annotated schema is using FOR XML under the covers
as well, but has the translation overhead (assuming that you have the same
query).
So it becomes more of a difference of the programming model and whether you
also load data (in which case for example the SQLXML XML Bulkload object
provides better performance than OpenXML).
Best regards
Michael
"Jens Doss" <jens.dosse@.oecd.org> wrote in message
news:1ef101c4f801$858247a0$a301280a@.phx.gbl...
> What are the advantages/disadvantages of using either
> client programming support in SQL server (referred to as
> SQLXML) or server-side support - especially when
> extracting formatted information from SQL server into a
> C# web service? Which one is faster? What type of support
> should be used in what circumtances, e.g. when IIS and
> SQL server run on the same machine, when extracted
> information is more/less voluminous?
> Thanks in advance.
> Jens

Client-side or server-side XML support?

What are the advantages/divantages of using either
client programming support in SQL server (referred to as
SQLXML) or server-side support - especially when
extracting formatted information from SQL server into a
C# web service? Which one is faster? What type of support
should be used in what circumtances, e.g. when IIS and
SQL server run on the same machine, when extracted
information is more/less voluminous?
Thanks in advance.
JensIf you are talking about extracting information from the database only, then
using ADO/ADO.net with FOR XML is normally slightly better in performance,
since the XPath against annotated schema is using FOR XML under the covers
as well, but has the translation overhead (assuming that you have the same
query).
So it becomes more of a difference of the programming model and whether you
also load data (in which case for example the SQLXML XML Bulkload object
provides better performance than OpenXML).
Best regards
Michael
"Jens Doss" <jens.dosse@.oecd.org> wrote in message
news:1ef101c4f801$858247a0$a301280a@.phx.gbl...
> What are the advantages/divantages of using either
> client programming support in SQL server (referred to as
> SQLXML) or server-side support - especially when
> extracting formatted information from SQL server into a
> C# web service? Which one is faster? What type of support
> should be used in what circumtances, e.g. when IIS and
> SQL server run on the same machine, when extracted
> information is more/less voluminous?
> Thanks in advance.
> Jens

Client Side Redirect - Database Mirroring

Hi All,

My apologies if this question seems abit basic, but I'm a DBA by trade and programming .Net isn't my strong point ;)

I've enabled database mirroring on 3 SQL 2005 servers, a principal, a mirror and a witness.

Principal - SQL 2005 Enterprise Edition, SP1

Mirror - SQL 2005 Enterprise Edition, SP1

Witness - SQL 2005 Express, SP1

I've written some test code to test the mirroring but as soon as the connection is pulled from the principal, the client re-direct doesn't work and the program bombs. I'd be grateful fi someone could have a look at my code below and tell me if there's any schoolboy errors?

(NB, the user running the code is a sys admin on all 3 servers)

Thanks in advance.

Imports System.Data.SqlClient

Public Class Form1

Dim DCTADS As New SqlClient.SqlConnection

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

DCTADS.ConnectionString = "Network=dbmssocn; Server=STUD; Failover Partner=DBDRTEST; Database=mirrortest; Integrated Security=True"

DCTADS.Open()

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim cmd As New SqlClient.SqlCommand

cmd.CommandText = "Select fulldesc from unclproduct where internal = '20000110'"

cmd.Connection = DCTADS

Dim dr As SqlClient.SqlDataReader

dr = cmd.ExecuteReader

MsgBox(DCTADS.DataSource.ToString)

If dr.Read Then

MsgBox(dr("fulldesc").ToString)

End If

dr.Close()

End Sub

End Class

Here is more detailed discussion for client redirect (http://msdn2.microsoft.com/en-us/library/ms366348.aspx). From the code I did not see any problem. One thing to check is to make sure the TCP/IP and "allow remote connection" are enabled on both principal and mirror because by default these two settings are disable after SQL server is installed.