Property AnsiNullsStatus is not available for UserDefinedFunction...

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?ProdN...s&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.
>
|||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
|||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/produc...5-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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||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.
|||Erland Sommarskog wrote:
> Rich (scporich@.community.nospam) writes:
>
> There is a very similar bug on
> http://lab.msdn.microsoft.com/produc...5-62d1fb31d0a4
> which is reported as fixed for SP1.
>
Yep - That looks like the one - thanks for the post.
Rich