January 6, 2011 at 12:52 pm
Hello All,
I am trying to create a UDF which needs to be executed from a view. The UDF pulls data from another database. The view and the UDF reside in the same database (db1 for our example). the db1.UDF pulls data from a table in another database db2.
Now when I am trying to create the UDF, I am getting the error
"An invalid option was specified for the statement "CREATE/ALTER FUNCTION". Below is the UDF that I am trying to create.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [db1].[dbo].[udf1]()
RETURNS TABLE
WITH EXECUTE AS 'DomainName\GroupName'
AS
BEGIN
RETURN
SELECT * FROM [db2].[dbo].
END
I might be making a syntactical mistake, but I am stuck and cant figure out whats happening. Any help would be appreciated.
Many thanks.
January 6, 2011 at 2:05 pm
From The SQL Server 2008 Books Online (August 2008)
CREATE FUNCTION (Transact-SQL) topic:
"EXECUTE AS cannot be specified for inline user-defined functions."
In addition, EXECUTE AS can only be used in a function to specify a database user, not a server login.
January 6, 2011 at 2:12 pm
Thank you Michael,
I just found out a possible work around for this issue. I could be wrong as well
CREATE FUNCTION dbo.xTest ( )
RETURNS @tbl TABLE ( x int, name varchar(100) )
WITH EXECUTE AS 'DomainName\GroupName'
AS
BEGIN
INSERT @tbl
SELECT CD_ID, user_name()
FROM [db2].[dbo].
RETURN
END
GO
This is not exactly what I was expecting to do, but nonetheless, it works 🙂
January 6, 2011 at 2:17 pm
The only problem is that I have to parametrize my function statement which sucks and is not that readable.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply