If you create a table UDF in a SQL 8 database from Server Management
Studio, and then attempt to edit it again, you get the following error:

Property AnsiNullsStatus is not available for UserDefinedFunction
'[dbo].[TF_TEST]'. This property may not exist for this object, or may
not be retrievable due to insufficient access rights.

I have reproduced this with a number of UDFs

The UDF appears to be OK, but it is kind of irritating to have to open
up a different tool to edit it again.

Hello,

I have tested the issue on my side, but I didn't reproduce the issue.  To
help me troubleshoot the issue, please post here the detail steps to
reproduce the issue. For your reference, I tested the issue by performing
the following steps:

1. Create a function on a SQL server 2000 database using SQL server 2005
management studio.

create function myuf()
returns table
return (select * from authors)

select * from test5.dbo.myuf()

2. Alter the function:

alter function myuf()
returns table
return (select * from authors where au_id='172-32-1176')

The following command works fine:

select * from test5.dbo.myuf()

I look forward to hearing from you.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Try these steps:

1.    Create a multi-statement table function (not an inline; that works
OK) on a SQL 2000 db using SQL server 2005 management studio.  I used
the wizard, but doing it by hand produces the same results:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        ScpoRich
-- Create date: 11/25/2005
-- Description:    Test Table Function
-- =============================================
CREATE FUNCTION MSDNTest
(
    -- Add the parameters for the function here
    @p1 int,
    @p2 char
)
RETURNS
@Table_Var TABLE
(
    c1 int,
    c2 int
)
AS
BEGIN
    insert into @Table_Var(c1,c2)
    values(@p1,100)
    RETURN
END
GO

2.    The following command works fine:

SELECT * FROM [pubs].[dbo].[MSDNTest] (1,'')

3.    The following command works fine:

ALTER FUNCTION MSDNTest
(@p1 int, @p2 char)
)
RETURNS
@Table_Var TABLE (c1 int, c2 int)
AS
BEGIN
    insert into @Table_Var(c1,c2)
    values(100,@p1)
    RETURN
END
GO


4.    Right click function in the Object explorer and select 'Modify' from
the context menu.

5.    Observe a dialog box with the following message:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Property QuotedIdentifierStatus is not available for UserDefinedFunction
'[dbo].[MSDNTest]'. This property may not exist for this object, or may
not be retrievable due to insufficient access rights.
(Microsoft.SqlServer.Smo)

For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=QuotedIdentifierStatus&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

6.    Selecting Script AS -> CREATE To or Script As -> ALTER to will cause
similar errors to occur.

---------------------------------------------------------------------------



Sophie Guo [MSFT] wrote:
Show quoteHide quote

Hi,

I have been having a similar problem whereby I am trying to script a
bunch of table functions from a SQL Server 2000 database through SQL
Management Studio.  I have fiddled around with DB Options but to no
avail. Is there any resolution on this?

Cheers

Dave Mc

david.mcma***@ridgian.co.uk
http://www.xmlexperience.com

Hello,

Based on my test, when connectting to a SQL2000 database using the SQL
Managemenet Studio, I can reproduce the issue. However, if I create a SQL
server 2000 database in SQL Managemenet Studio, which means the
compatibility level is "SQL server 2000(80)", everything works fine.
Therefore I think the workaround is to import the SQL server 2000 database
into SQL server 2005 instance. You can import the database using the Copy
database wizard.

I hope the information is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Rich (scporich@community.nospam) writes:
There is a very similar bug on
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=0750596e-9915-42ea-9295-62d1fb31d0a4
which is reported as fixed for SP1.

Erland Sommarskog wrote:
Show quoteHide quoteYep - That looks like the one - thanks for the post.

Rich

Mobile Only? Why?
request/response style profiling
SQL 2005 Managemet Studio ...Unicode?
Mgmt Studio not supporting 7.0?
Where as the SQL Profiler gone