2005: calling .NET procedure

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • R.A.M.
    #1

    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.Sql Client;
    using System.Data.Sql Types;
    using Microsoft.SqlSe rver.Server;
    namespace DemoSQLServer
    {
    public sealed class Demo
    {
    [SqlProcedure(Na me="PodajKsi¹¿k i")]
    public static void PodajKsi¹¿ki()
    {
    SqlCommand cmd = new SqlCommand("SEL ECT * FROM Ksi¹¿ki");
    SqlDataReader dr = cmd.ExecuteRead er();
    SqlContext.Pipe .Send(dr);
    }
    ...
    }
    }

    I have created assembly in Object Explorer (Programmabilit y /
    Assemblies).
    How to call procedure? I tried:
    exec DemoSQLServer.P odajKsi¹¿ki
    but I got a message:

    Could not find stored procedure 'DemoSQLServer. PodajKsi¹¿ki'.

    Please help.
    Thank you.
    /RAM/
    Tags: None
    • Hugo Kornelis
      #2
      Re: 2005: calling .NET procedure

      On Mon, 10 Jul 2006 20:38:44 +0200, R.A.M. wrote:
      >Hi,
      >I am learning SQL Server 2005. I need to call .NET assembly procedure
      >from T-SQL.
      >Here's part of my assembly:
      (snip)
      >I have created assembly in Object Explorer (Programmabilit y /
      >Assemblies).
      Hi RAM,

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

      --
      Hugo Kornelis, SQL Server MVP

        Comment

        • R.A.M.
          #3
          Re: 2005: calling .NET procedure

          On Tue, 11 Jul 2006 00:18:29 +0200, Hugo Kornelis
          <hugo@perFact.R EMOVETHIS.info. INVALIDwrote:
          >On Mon, 10 Jul 2006 20:38:44 +0200, R.A.M. wrote:
          >
          >>Hi,
          >>I am learning SQL Server 2005. I need to call .NET assembly procedure
          >>from T-SQL.
          >>Here's part of my assembly:
          >(snip)
          >>I have created assembly in Object Explorer (Programmabilit y /
          >>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/

            Comment

            • R.A.M.
              #4
              Re: 2005: calling .NET procedure

              On Tue, 11 Jul 2006 00:18:29 +0200, Hugo Kornelis
              <hugo@perFact.R EMOVETHIS.info. INVALIDwrote:
              >On Mon, 10 Jul 2006 20:38:44 +0200, R.A.M. wrote:
              >
              >>Hi,
              >>I am learning SQL Server 2005. I need to call .NET assembly procedure
              >>from T-SQL.
              >>Here's part of my assembly:
              >(snip)
              >>I have created assembly in Object Explorer (Programmabilit y /
              >>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

                Comment

                • Hugo Kornelis
                  #5
                  Re: 2005: calling .NET procedure

                  On Tue, 11 Jul 2006 14:22:00 +0200, R.A.M. wrote:
                  >On Tue, 11 Jul 2006 00:18:29 +0200, Hugo Kornelis
                  ><hugo@perFact. REMOVETHIS.info .INVALIDwrote:
                  >
                  >>On Mon, 10 Jul 2006 20:38:44 +0200, R.A.M. wrote:
                  >>
                  >>>Hi,
                  >>>I am learning SQL Server 2005. I need to call .NET assembly procedure
                  >>>from T-SQL.
                  >>>Here's part of my assembly:
                  >>(snip)
                  >>>I have created assembly in Object Explorer (Programmabilit y /
                  >>>Assemblies ).
                  >>
                  >>Hi RAM,
                  >>
                  >>How did your CREATE ASSEMBLY statement look? Can you post it?
                  >
                  >I scripted:
                  >
                  >CREATE ASSEMBLY [DemoSQLServer]
                  >AUTHORIZATIO N [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 [PodajKsi¹¿ki]
                  AS
                  EXTERNAL NAME DemoSQLServer.[DemoSQLServer.D emo].[PodajKsi¹¿ki]
                  go

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

                  EXEC [PodajKsi¹¿ki]

                  (or, if you want to follow best practice and explicitly add the schema:
                  EXEC dbo.[PodajKsi¹¿ki] - 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 PodajKsi¹¿ki instead of [PodajKsi¹¿ki]).

                  --
                  Hugo Kornelis, SQL Server MVP

                    Comment

                    • R.A.M.
                      #6
                      Re: 2005: calling .NET procedure

                      >CREATE PROCEDURE [PodajKsi¹¿ki]
                      >AS
                      >EXTERNAL NAME DemoSQLServer.[DemoSQLServer.D emo].[PodajKsi¹¿ki]
                      >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/

                        Comment

                        • R.A.M.
                          #7
                          I solved

                          sp_configure 'show advanced options', 1;
                          GO
                          RECONFIGURE;
                          GO
                          sp_configure 'clr enabled', 1;
                          GO
                          RECONFIGURE;
                          GO

                            Comment

                            • R.A.M.
                              #8
                              Re: 2005: calling .NET procedure - one more question

                              I have one more question - concernign functions.
                              I've written function:

                              [SqlFunction(Nam e="GetCurrentDa teText")]
                              public static SqlString GetCurrentDateT ext()
                              {
                              DateTime dt = new DateTime();
                              return dt.ToString();
                              }

                              And I have tried to create a function:

                              CREATE FUNCTION GetCurrentDateT ext RETURNS nvarchar(MAX) AS EXTERNAL
                              NAME DemoSQLServer.D emoGetCurrentDa teText

                              (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/

                                Comment

                                • Erland Sommarskog
                                  #9
                                  Re: 2005: calling .NET procedure - one more question

                                  R.A.M. (r_ahimsa_m@poc zta.onet.pl) writes:
                                  I have one more question - concernign functions.
                                  I've written function:
                                  >
                                  [SqlFunction(Nam e="GetCurrentDa teText")]
                                  public static SqlString GetCurrentDateT ext()
                                  {
                                  DateTime dt = new DateTime();
                                  return dt.ToString();
                                  }
                                  >
                                  And I have tried to create a function:
                                  >
                                  CREATE FUNCTION GetCurrentDateT ext RETURNS nvarchar(MAX) AS EXTERNAL
                                  NAME DemoSQLServer.D emoGetCurrentDa teText
                                  >
                                  (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@sommarsk og.se

                                  Books Online for SQL Server 2005 at
                                  http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

                                  Books Online for SQL Server 2000 at
                                  http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                                    Comment

                                    Previous template Next
                                    Working...