Property QuotedIdentifierStatus is not available for UDF.. Help?

Hi ;
I'm using the release version of SQL Server 2005 tools (Microsoft SQL
Server Management Studio 9.00.1399.00) and .NET Framework 2.0
(2.0.50727.42) on Windows XP SP2.
My desire was to use the new tools against my existing SQL 2000 servers
as well as newly installed SQL 2005 so as to only have one place to
work in and access to new tool features. This has worked great except
for one issue. When trying to modify or script some table-valued
functions under Management Studio I get the following error:
===================================
Property QuotedIdentifierStatus is not available for
UserDefinedFunction '[dbo].[fnFunction]'. This property may not exist
for this object, or may not be retrievable due to insufficient access
rights. (Microsoft.SqlServer.Smo)
===================================
I can edit/script the function in the 2000 tools (Enterprise Manager
and SQL Analyzer) without problem. I can edit some table-valued
functions in Management Studio but not others. I have tried dropping
the functions and re-creating them in both 2000 and 2005 tools - the
create works fine in both places, but it remains
un-modifiable/scriptable in the 2005 tools.
Anyone else run into this problem?
Additional info available for the error
===================================
Program Location:
at
Microsoft.SqlServer.Management.Smo.PropertyCollection.HandleNullValue(Int32
index)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional(String
propName)
at
Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional[T](String
propName, T defaultValue)
at
Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.CheckTextCorectness(
String
ddlText, Boolean enforceCreate, Boolean checkName, String[]
expectedObjectTypes, DdlTextParserHeaderInfo& headerInfo)
at
Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.CheckAndManipulateTe
xt(String
ddlText, String[] expectedObjectTypes, ScriptingOptions so, Boolean
forCreate)
at
Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.GetTextHeader(Boolea
n
forAlter)
at
Microsoft.SqlServer.Management.Smo.UserDefinedFunction.ScriptHeader(Boolean
forAlter)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.S
criptExistingTextObjectToWindow(IManaged
Connection
mc)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.I
nvokeTextObject(IManagedConnection
connection)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.I
nvoke()
===================================
Essa, M. Mughal
Software Developer
Toronto, CanadaCan you post a CREATE script for one of your problematic table-valued
functions?
Hope this helps.
Dan Guzman
SQL Server MVP
"Essa" <essamughal@.hotmail.com> wrote in message
news:532E0285-F705-4CD7-BAE2-79C18B372B24@.microsoft.com...
> Hi ;
> I'm using the release version of SQL Server 2005 tools (Microsoft SQL
> Server Management Studio 9.00.1399.00) and .NET Framework 2.0
> (2.0.50727.42) on Windows XP SP2.
> My desire was to use the new tools against my existing SQL 2000 servers
> as well as newly installed SQL 2005 so as to only have one place to
> work in and access to new tool features. This has worked great except
> for one issue. When trying to modify or script some table-valued
> functions under Management Studio I get the following error:
> ===================================
> Property QuotedIdentifierStatus is not available for
> UserDefinedFunction '[dbo].[fnFunction]'. This property may not exist
> for this object, or may not be retrievable due to insufficient access
> rights. (Microsoft.SqlServer.Smo)
> ===================================
> I can edit/script the function in the 2000 tools (Enterprise Manager
> and SQL Analyzer) without problem. I can edit some table-valued
> functions in Management Studio but not others. I have tried dropping
> the functions and re-creating them in both 2000 and 2005 tools - the
> create works fine in both places, but it remains
> un-modifiable/scriptable in the 2005 tools.
> Anyone else run into this problem?
> Additional info available for the error
> ===================================
> Program Location:
> at
> Microsoft.SqlServer.Management.Smo.PropertyCollection.HandleNullValue(Int3
2
> index)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional(Strin
g
> propName)
> at
> Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropValueOptional[T](Strin
g
> propName, T defaultValue)
> at
> Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.CheckTextCorectnes
s(String
> ddlText, Boolean enforceCreate, Boolean checkName, String[]
> expectedObjectTypes, DdlTextParserHeaderInfo& headerInfo)
> at
> Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.CheckAndManipulate
Text(String
> ddlText, String[] expectedObjectTypes, ScriptingOptions so, Boolean
> forCreate)
> at
> Microsoft.SqlServer.Management.Smo.ScriptNameObjectBase.GetTextHeader(Bool
ean
> forAlter)
> at
> Microsoft.SqlServer.Management.Smo.UserDefinedFunction.ScriptHeader(Boolea
n
> forAlter)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem
. ScriptExistingTextObjectToWindow(IManage
dConnection
> mc)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem
.InvokeTextObject(IManagedConnection
> connection)
> at
> Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem
.Invoke()
> ===================================
> --
> Essa, M. Mughal
> Software Developer
> Toronto, Canada|||Essa (essamughal@.hotmail.com) writes:
> I'm using the release version of SQL Server 2005 tools (Microsoft SQL
> Server Management Studio 9.00.1399.00) and .NET Framework 2.0
> (2.0.50727.42) on Windows XP SP2.
> My desire was to use the new tools against my existing SQL 2000 servers
> as well as newly installed SQL 2005 so as to only have one place to
> work in and access to new tool features. This has worked great except
> for one issue. When trying to modify or script some table-valued
> functions under Management Studio I get the following error:
>===================================
> Property QuotedIdentifierStatus is not available for
> UserDefinedFunction '[dbo].[fnFunction]'. This property may not exist
> for this object, or may not be retrievable due to insufficient access
> rights. (Microsoft.SqlServer.Smo)
>===================================
> I can edit/script the function in the 2000 tools (Enterprise Manager
> and SQL Analyzer) without problem. I can edit some table-valued
> functions in Management Studio but not others. I have tried dropping
> the functions and re-creating them in both 2000 and 2005 tools - the
> create works fine in both places, but it remains
> un-modifiable/scriptable in the 2005 tools.
> Anyone else run into this problem?
Yes, this is a known bug, and it has been resolved as fixed. It only
affects connections to SQL 2000.
See also
http://lab.msdn.microsoft.com/produ...95-62d1fb31d0a4
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|||Erland Sommarskog (esquel@.sommarskog.se) writes:
> Yes, this is a known bug, and it has been resolved as fixed. It only
> affects connections to SQL 2000.
I should clarify that this does not mean that the fix is publicly
available. I only wanted to convey that Microsoft are aware of the
issue, and the fix is to be expected to appear in SP1 for SQL 2005.
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|||Cool beans. Glad there's a fix.
I've got a bunch of SQL 2000 servers I need to script the schema for,
and I ran into the same UDF scripting problem myself.
Hope SP1 will be available soon.
- john
Erland Sommarskog wrote:
> Erland Sommarskog (esquel@.sommarskog.se) writes:
> I should clarify that this does not mean that the fix is publicly
> available. I only wanted to convey that Microsoft are aware of the
> issue, and the fix is to be expected to appear in SP1 for SQL 2005.
>
> --
> 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.mspxsql

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