Call A .NET Assembly

Question

Call A .NET Assembly

TaffyLewis

Ten Centuries

Points: 1033
More actions
July 6, 2004 at 9:27 am

#84961

Can you call a .NET dll from SQL Server (like in a DTS or a Task or stored procedure). I want to be able to call a set of .net functions or a class that will be ran from sql daily?
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Hi, you will be able to do it in SQL Server 2005 as I understand.

Meanwhile did you try to add it as an extended stored proceedure like described in "Adding an Extended Stored Procedures to SQL Server" BOL article? sp_addextendedproc 'xp_hello', 'xp_hello.dll'

You may want to use dot net tools probably to export types ( tlbexp.exe) and register your assembly (regasm.exe, yes, that is how it is called) to make non-dot net apps use dot net types.

See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cptools/html/cpgrfTypeLibraryExporterTlbExpexe.asp

`Type Library Exporter (Tlbexp.exe)`

Regards,Yelena Varsha

mgabriele SSC Enthusiast Points: 171 More actions · Answer 2

You may be able to create a COM wrapper for the .Net dll then use the SQL Server's OLE Automation apis (e.g. stored procedure sp_OACreate to create an instance of the object).

akka Valued Member Points: 70 More actions · Answer 3

You need to create an extended Stored Procudure DLL.

Open Visual Studio (.NET) and select the Visual C++ Projects. There is a Template for an Extended Stored Procudure in this section. Follow the instructions inclosed in this template for construction, installation and use within SQL Server.

You can call other .NET assemblies from within the C++ assembly so if you have already built something in VB or C# you can use the access points.

Once you have completed you should have an extended stored procedure in SQL that you can call and pass parameters to.

TaffyLewis Ten Centuries Points: 1033 More actions · Answer 4

Thanks akka, I'm going to take a look and try this! I'll let you know how I do!

Taffy

TaffyLewis Ten Centuries Points: 1033 More actions · Answer 5

akka, there were no instructions/options for this wizard--it just had a creation page!

pilam152 Old Hand Points: 384 More actions · Answer 6

Here is some stripped down code that I use at work to do such things. I hope this helps.

RETCODE __declspec(dllexport) xp_DoStuff(SRV_PROC *srvproc)

{

//My Variables

//Return Variables

//Other Varaibles

BYTE bType;

unsigned long cbMaxLen;

unsigned long cbActualLen;

unsigned long cbActualLen2;

BOOL fNull;

BYTE bData[100];

BYTE bLibrary[100];

int nParams;

DBINT paramtype;

try

{

// Get number of parameters.

nParams = srv_rpcparams(srvproc);

// Check number of parameters

if (nParams != 1) {

// Send error message and return

srv_sendmsg(srvproc, SRV_MSG_ERROR, NULL, SRV_INFO, (DBTINYINT)0,

NULL, 0, 0, "Error executing extended stored procedure: Invalid Parameter(s)",

SRV_NULLTERM);

// A SRV_DONE_MORE instead of a SRV_DONE_FINAL must complete the

// result set of an Extended Stored Procedure.

srv_senddone(srvproc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);

return(XP_ERROR);

}

// If parameter 1 is not varchar, send an

// error and return.

paramtype = srv_paramtype(srvproc, 1);

if (paramtype != SRVVARCHAR) {

srv_sendmsg(srvproc, SRV_MSG_ERROR, 999995, SRV_INFO, (DBTINYINT)0,

NULL, 0, 0,

"Error executing extended stored procedure: Invalid Parameter Type - Parameter 1 (NOT SQLVARCHAR)",

SRV_NULLTERM);}

// If we made it this far, all the parameters passed

//Read The Data in

if (srv_paraminfo(srvproc,1, &bType, &cbMaxLen, &cbActualLen,

bData, &fNull) == FAIL)

{

srv_sendmsg(srvproc, SRV_MSG_ERROR, 999995, SRV_INFO, (DBTINYINT)0,

NULL, 0, 0,

"There is something screwed up!",

SRV_NULLTERM);

return (XP_ERROR);

}

char sAccount[7];

strncpy((char *)sAccount,(char *)bData,6);

//Null Teminate the character Array

sAccount[6] = 0;

System::String *ptrAccount=sAccount;

//Execute Managed Code

if(!(ManagedCode::NameSpace::Class::Function(ptrAccount))

{

srv_sendmsg(srvproc, SRV_MSG_ERROR, 999995, SRV_INFO, (DBTINYINT)0,

NULL, 0, 0,

"Managed Code Failed!",

SRV_NULLTERM);

}

catch

(System::Exception *e)

{

char

__nogc* pStrEx = static_cast<char*>(Marshal::StringToHGlobalAnsi(e->ToString()).ToPointer());

srv_sendmsg(srvproc, SRV_MSG_INFO, 999995, SRV_INFO, (DBTINYINT)0,

NULL, 0, 0,

pStrEx,

SRV_NULLTERM);

}

return XP_NOERROR ;

}