2005: calling .NET procedure

Hi,
I am learning SQL Server 2005. I need to call .NET assembly procedure
from T-SQL.
Here's part of my assembly:

using System;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
namespace DemoSQLServer
{
public sealed class Demo
{
[SqlProcedure(Name="PodajKsiki")]
public static void PodajKsiki()
{
SqlCommand cmd = new SqlCommand("SELECT * FROM Ksiki");
SqlDataReader dr = cmd.ExecuteReader();
SqlContext.Pipe.Send(dr);
}
...
}
}

I have created assembly in Object Explorer (Programmability /
Assemblies).
How to call procedure? I tried:
exec DemoSQLServer.PodajKsiki
but I got a message:

Could not find stored procedure 'DemoSQLServer.PodajKsiki'.

Please help.
Thank you.
/RAM/On Mon, 10 Jul 2006 20:38:44 +0200, R.A.M. wrote:

Quote:

Originally Posted by

>Hi,
>I am learning SQL Server 2005. I need to call .NET assembly procedure
>from T-SQL.
>Here's part of my assembly:

(snip)

Quote:

Originally Posted by

>I have created assembly in Object Explorer (Programmability /
>Assemblies).

Hi RAM,

How did your CREATE ASSEMBLY statement look? Can you post it?

--
Hugo Kornelis, SQL Server MVP|||On Tue, 11 Jul 2006 00:18:29 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

>On Mon, 10 Jul 2006 20:38:44 +0200, R.A.M. wrote:
>

Quote:

Originally Posted by

>>Hi,
>>I am learning SQL Server 2005. I need to call .NET assembly procedure
>>from T-SQL.
>>Here's part of my assembly:

>(snip)

Quote:

Originally Posted by

>>I have created assembly in Object Explorer (Programmability /
>>Assemblies).

>
>Hi RAM,
>
>How did your CREATE ASSEMBLY statement look? Can you post it?

I haven't used CREATE ASSEMBLY. I simply right-clicked
Programmability/Assemblies, and chosen "New Assembly...".
Please help
/RAM/|||On Tue, 11 Jul 2006 00:18:29 +0200, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

>On Mon, 10 Jul 2006 20:38:44 +0200, R.A.M. wrote:
>

Quote:

Originally Posted by

>>Hi,
>>I am learning SQL Server 2005. I need to call .NET assembly procedure
>>from T-SQL.
>>Here's part of my assembly:

>(snip)

Quote:

Originally Posted by

>>I have created assembly in Object Explorer (Programmability /
>>Assemblies).

>
>Hi RAM,
>
>How did your CREATE ASSEMBLY statement look? Can you post it?

I scripted:

CREATE ASSEMBLY [DemoSQLServer]
AUTHORIZATION [dbo]
FROM ...
WITH PERMISSION_SET = SAFE|||On Tue, 11 Jul 2006 14:22:00 +0200, R.A.M. wrote:

Quote:

Originally Posted by

>On Tue, 11 Jul 2006 00:18:29 +0200, Hugo Kornelis
><hugo@.perFact.REMOVETHIS.info.INVALIDwrote:
>

Quote:

Originally Posted by

>>On Mon, 10 Jul 2006 20:38:44 +0200, R.A.M. wrote:
>>

Quote:

Originally Posted by

>>>Hi,
>>>I am learning SQL Server 2005. I need to call .NET assembly procedure
>>>from T-SQL.
>>>Here's part of my assembly:

>>(snip)

Quote:

Originally Posted by

>>>I have created assembly in Object Explorer (Programmability /
>>>Assemblies).

>>
>>Hi RAM,
>>
>>How did your CREATE ASSEMBLY statement look? Can you post it?

>
>I scripted:
>
>CREATE ASSEMBLY [DemoSQLServer]
>AUTHORIZATION [dbo]
>FROM ...
>WITH PERMISSION_SET = SAFE

Hi RAM,

Thanks. Unfortunately, I now realise that I forgot to ask to post the
CREATE PROCEDURE statement you used as well - my apologies.

To cut this short, I'll just post my assumption: I _think_ that yoru
CREATE PROCEDURE statement looks like this:

CREATE PROCEDURE [PodajKsiki]
AS
EXTERNAL NAME DemoSQLServer.[DemoSQLServer.Demo].[PodajKsiki]
go

If this is indeed hoow yoou created the stored procedure, then you use
the following syntax to call it:

EXEC [PodajKsiki]

(or, if you want to follow best practice and explicitly add the schema:
EXEC dbo.[PodajKsiki] - but of course, then you'd add an explicit
schema on the various CREATE statements as well).

(Note - I used copy and paste for the procedure name since some of the
characters appear to be from a character set that's not installed on my
computer - I hope the procedure name looks right to you!)

(Second note - I chose safety first and enclosed the stored procedure's
name between bracktes, since I don't know if the actual characters that
look like on my computer are valid or not in an identifier. You can
always try to use PodajKsiki instead of [PodajKsiki]).

--
Hugo Kornelis, SQL Server MVP|||>CREATE PROCEDURE [PodajKsiki]

Quote:

Originally Posted by

>AS
>EXTERNAL NAME DemoSQLServer.[DemoSQLServer.Demo].[PodajKsiki]
>go

Thanks.

I cannot run procedure because of the followinf error:

Execution of user code in the .NET Framework is disabled. Enable "clr
enabled" configuration option.

I couldn't find the option in Configuration Manager nor Management
Studio, neigher a .config file. Do you know how to enable the option?
Thank you very much!
/RAM/|||sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO|||I have one more question - concernign functions.
I've written function:

[SqlFunction(Name="GetCurrentDateText")]
public static SqlString GetCurrentDateText()
{
DateTime dt = new DateTime();
return dt.ToString();
}

And I have tried to create a function:

CREATE FUNCTION GetCurrentDateText RETURNS nvarchar(MAX) AS EXTERNAL
NAME DemoSQLServer.DemoGetCurrentDateText

(according to Books Online) But I recive an error:

Incorrect syntax near 'RETURNS'.

I cannot guess what is wrong. Could you help me please?
Thank you!
/RAM/|||R.A.M. (r_ahimsa_m@.poczta.onet.pl) writes:

Quote:

Originally Posted by

I have one more question - concernign functions.
I've written function:
>
[SqlFunction(Name="GetCurrentDateText")]
public static SqlString GetCurrentDateText()
{
DateTime dt = new DateTime();
return dt.ToString();
}
>
And I have tried to create a function:
>
CREATE FUNCTION GetCurrentDateText RETURNS nvarchar(MAX) AS EXTERNAL
NAME DemoSQLServer.DemoGetCurrentDateText
>
(according to Books Online) But I recive an error:
>
Incorrect syntax near 'RETURNS'.
>
I cannot guess what is wrong. Could you help me please?

Parentheses are mandatory, even for parameterless funtions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx