Extended Stored Procedure Help

Question

Hi,

I'm trying to run one of my methods in my own assembly and just return Hello in a table.

use IdentityNumberTracker;
go

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'HelloWorld')
   DROP FUNCTION HelloWorld;
go

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'ExtendedStoredProcedure')
   DROP ASSEMBLY ExtendedStoredProcedure;
go




CREATE ASSEMBLY ExtendedStoredProcedure FROM 'C:\ExtendedStoredProcedure.dll'
WITH PERMISSION_SET = SAFE -- EXTERNAL_ACCESS;
GO

CREATE FUNCTION HelloWorld(@theString datetime)
RETURNS TABLE (
  HelloWorld nvarchar(400)
)
AS EXTERNAL NAME ExtendedStoredProcedure.ExtendedStoredProcedure.ValidateIdNumber.[HelloWorld];
go

SELECT * FROM HelloWorld('2000-01-01');
go

Msg 102, Level 15, State 1, Procedure HelloWorld, Line 6
Incorrect syntax near '.'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'HelloWorld'.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;


namespace ExtendedStoredProcedure
{
    public class TestReturn
    {
        public SqlString HelloWorld;
       
        public TestReturn(SqlString helloworld)
        {
            this.HelloWorld = helloworld;
        }
    }
 
    public static class ValidateIdNumber
    {
           [SqlFunction(
    DataAccess = DataAccessKind.Read,
    FillRowMethodName = "HelloWorld_FillRow",
    TableDefinition = "HelloWorld nvarchar(400)")]
        public static IEnumerable HelloWorld(SqlDateTime theString)
        {
            ArrayList toReturn = new ArrayList();

            TestReturn tesRetunr = new TestReturn("Hello world");
            toReturn.Add(tesRetunr);
            return toReturn;
        }
       public static void HelloWorld_FillRow(
       object emailResultObj,
       out SqlString helloWorld)
        {
            TestReturn emailResult = (TestReturn)emailResultObj;

            helloWorld = emailResult.HelloWorld;
        }
    }
    
}

Answer 1

Msg 102, Level 15, State 1, Procedure HelloWorld, Line 6
Incorrect syntax near '.'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'HelloWorld'.

Answer 2

CREATE FUNCTION HelloWorld(@theString datetime)
RETURNS TABLE (
  HelloWorld nvarchar(400)
)
AS EXTERNAL NAME ExtendedStoredProcedure.ExtendedStoredProcedure.ValidateIdNumber.[HelloWorld];
go

Msg 102, Level 15, State 1, Procedure HelloWorld, Line 6
Incorrect syntax near '.'.

You need brackets around the namespace-qualified class name in the EXTERNAL NAME clause:

CREATE FUNCTION [dbo].[HelloWorld]
(@theString DATETIME)
RETURNS 
     TABLE (
        [HelloWorld] NVARCHAR (400) NULL)
AS
 EXTERNAL NAME ExtendedStoredProcedure.[ExtendedStoredProcedure.ValidateIdNumber].HelloWorld;

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

Answer 3

Thank allot. Did the trick.