MS SQL Server Tools - Property AnsiNullsStatus is not available for UserDefinedFunction...

Author

Property AnsiNullsStatus is not available for UserDefinedFunction...

Rich

2005-11-23, 9:24 am

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.

Sophie Guo [MSFT]

2005-11-24, 3:23 am

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.

Rich

2005-11-25, 7:24 am

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 QuotedIdentifierStat
us 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? Pro...us&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:
> 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.
>

dave mc

2005-11-25, 1:23 pm

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.mcmahon@ridgian.co.uk
http://www.xmlexperience.com

Erland Sommarskog

2005-11-26, 8:39 pm

Rich (scporich@community.nospam) writes:
> 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.

There is a very similar bug on
http://lab.msdn.microsoft.com/produ...95-62d1fb31d0a4
which is reported as fixed for SP1.

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

Sophie Guo [MSFT]

2005-11-28, 7:24 am

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

2005-11-28, 9:23 am

Erland Sommarskog wrote:
> Rich (scporich@community.nospam) writes:
>
>
>
> There is a very similar bug on
> http://lab.msdn.microsoft.com/produ...95-62d1fb31d0a4
> which is reported as fixed for SP1.
>
>
Yep - That looks like the one - thanks for the post.

Rich

Sponsored Links