CLR Return Types

I would like my C# dll to return a float (value) so that I can use that
value within the SQL Server stored procedure. There seems to be a limitation
on the return types from CLR (CLR methods return either SqlInt32,
System.Int32, void.)
I would appreciate if anyone can suggest a workaround (if there is one).
Thanks.In SQL Server, procedures return either set a return code (which is INT ==
SqlInt32) or they don't (CLR return type is void). To return a value from a
stored procedure use a parameter of type OUTPUT in SQL Server (which
actually acts like ref in .NET) or use a user-defined function instead.
User-defined functions can return float (REAL in SQL Server) or double
(FLOAT in SQL Server) or other data types.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:5CBF5704-0FF7-4C58-A81D-C8837416BA34@.microsoft.com...
> I would like my C# dll to return a float (value) so that I can use that
> value within the SQL Server stored procedure. There seems to be a
> limitation
> on the return types from CLR (CLR methods return either SqlInt32,
> System.Int32, void.)
> I would appreciate if anyone can suggest a workaround (if there is one).
> Thanks.|||I guess I don't quite understand what you are saying. Could you please be
more clear? I am not sure if my question is confusing, but I want to return
a
float value from a dll.
Thanks.
"Bob Beauchemin" wrote:

> In SQL Server, procedures return either set a return code (which is INT ==
> SqlInt32) or they don't (CLR return type is void). To return a value from
a
> stored procedure use a parameter of type OUTPUT in SQL Server (which
> actually acts like ref in .NET) or use a user-defined function instead.
> User-defined functions can return float (REAL in SQL Server) or double
> (FLOAT in SQL Server) or other data types.
> Cheers,
> Bob Beauchemin
> http://www.SQLskills.com/blogs/bobb
>
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:5CBF5704-0FF7-4C58-A81D-C8837416BA34@.microsoft.com...
>
>|||"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:ACC4559E-3DB8-4E5A-BC3C-F6A4AA1E2477@.microsoft.com...
> I guess I don't quite understand what you are saying. Could you please be
> more clear? I am not sure if my question is confusing, but I want to
> return a
> float value from a dll.
You need to create a user-defined function rather than a stored
procedure.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

CLR Procedure error with third party .dll

I have a CLR procedure with the below code calling a third party .dll in the system32 directory of the SQL server:

using System;

using System.Collections;

using System.Data;

using System.Data.SqlTypes;

using System.Runtime.InteropServices;

using System.Text;

using Microsoft.SqlServer.Server;

public class CorrectAddressProcedures

{

[DllImport(@."C:\WINDOWS\system32\CorrectA.dll", EntryPoint = "FindCityCounty")]

public static extern int FindCityCounty(StringBuilder zip, StringBuilder cityname, StringBuilder state, StringBuilder countyname, StringBuilder countynum);

public static String Space(int len)

{

StringBuilder str = new StringBuilder("");

str.Append(' ', len);

return str.ToString();

}

[Microsoft.SqlServer.Server.SqlProcedure]

public static void spFindCityCounty(SqlString _zip, out SqlString _cityname, out SqlString _state, out SqlString _countyname, out SqlString _countynum)

{

int rc;

StringBuilder zip = new StringBuilder(5);

StringBuilder cityname = new StringBuilder(28);

StringBuilder state = new StringBuilder(2);

StringBuilder countyname = new StringBuilder(25);

StringBuilder countynum = new StringBuilder(3);

try

{

cityname.Append(Space(28));

state.Append(Space(2));

countyname.Append(Space(25));

countynum.Append(Space(3));

zip.Append(_zip);

rc = FindCityCounty(zip, cityname, state, countyname, countynum);

_cityname = cityname.ToString();

_state = state.ToString();

_countyname = countyname.ToString();

_countynum = countynum.ToString();

}

catch (Exception ex)

{

throw (ex);

}

}

}

I am getting the error

Msg 6522, Level 16, State 1, Procedure spFindCityCounty, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'spFindCityCounty':

System.DllNotFoundException: Unable to load DLL 'C:\WINDOWS\system32\CorrectA.dll': Not enough storage is available to process this command. (Exception from HRESULT: 0x80070008)

System.DllNotFoundException:

at CorrectAddressProcedures.spFindCityCounty(SqlString _zip, SqlString& _cityname, SqlString& _state, SqlString& _countyname, SqlString& _countynum)

.

I have restarted the service with memory allocation settings -g512 and -g1024 but still receive the error. The SQL server is an Intel Xeon 2.8GHZ with 2GB of RAM. Any thoughts of how to solve this would be greatly appreciated.

thanks,
Whitney

I failed to mention above that the .dll in question is a rather large one. 154MB to be exact.|||

hi,

how much memory space have you reserved for your SQL 2005 server.

in sql200 Management Studio:

Server Properties\Memory.

Try to enlarge your maximum memory or reduce the minimum memory to zero.

Restart the SQL Server and try again.

regards,

Martin

|||Checking SSMS I'm assuming the defaults were used. The minimum is set to 0MB and maximum is 2147483647MB. I have a startup parameter of 512MB and have also tried 1024MB with no luck.|||

Our vendor sent us a smaller DLL today (98MB vs 154MB) and the CLR procedures now work without code changes. Does anyone know if there is an undocumented ceiling (say around 100MB) that DLLImport has within the SQL CLR?

While I'm happy my code works I would still like to know the deeper detail on why I was getting the error.

Thanks,
Whitney

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