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

Programatically viewing the data in a particular column

This is my first attempt to create a Vb.net class as well as my first attempt to programmatically view SQL retrieved data as opposed to using the built-in data controls...so be gentle.

My question is looking at the code below which I have already put together...how do I actually see what the data is in a particular column... One of my columns for example is the "Title" which contains the Page Title for Search Engine Optomization. I need to be able to read this value so I can assign it to the Page.

'First Do a Database Connection

IfNot Page.IsPostBackThen

'Start by determining the connection string value

Dim connStringAsString = _

ConfigurationManager.ConnectionStrings("CMConnectionString").ConnectionString

'Create a SqlConnection instance

Using myConnectionAsNew SqlConnection(connString)

'Specify the SQL query

Const sqlAsString ="SELECT * FROM SEO where ParentType='Department' AND ParentLevel='0' "

'Create a SqlCommand instance

Dim myCommandAsNew SqlCommand(sql, myConnection)

'Get back a DataSet

Dim myDataSetAsNew DataSet

'Create a SqlDataAdapter instance

Dim myAdapterAsNew SqlDataAdapter(myCommand)

myAdapter.Fill(myDataSet,"SEO")

'Create a DataView

Dim myDataViewAsNew DataView

myDataView.Table = myDataSet.Tables("SEO")

'Ok...this is where I'm lost. Now how do I look up the value for the column "Title" in this Dataset

'Close the connection

myConnection.Close()

'End database connections

EndUsing

You can use the following line to get a datacolumn of a known column name or column number:
Dim myColumnValueAs DataColumn = myDataSet.Tables("SEO").Columns("Title")

To access other columns, simply replace the column name ("Title") with a respective column name

Alternatively you can use the following line to access the value inside the1st row of the coloumn
Dim strAsString =CStr(myDataSet.Tables("SEO").Rows(0).Item("Title"))

To access other rows, simply replace the row number (0) with a respective row number

Hope this helps you.

|||

I'm relatively new too.

Instead of writing the entire class as 'codeaholic' did (looks daunting), can I use the drag'n'drop methodology to place a SqlDataSource1 control onto an ASPX page and configure the datasource with an appropriate Select statement, (easy part) and then use less VB code to find the value of a column for a given record?

In other words, if I want to extract a single column of a record do I have to 'throw away' all those luxury features of design time

|||

i'm personally not familier with using data controls in my pages as im currently using either 2-tier or 3-tier application development, but it's possible to do what you want.
just create the data controls one-by-one till you get a datagrid or dataview object containing the values you wanted, then use code-behind to access the datagrid or dataview objects for your values.
or you could even directly bind your values to controls using the datasource property and other respective properties.
however it may get tedious when you need a large number of data objects to access different values in different tables.

i would also advise strongly against doing so as it would affect the performance of your pages due to unneeded controls needed to be complied and loaded.
and there's always the security issue.
there's always better ways to do it.
hope this helps you.

|||

AnjinG:

You can use the following line to get a datacolumn of a known column name or column number:
Dim myColumnValueAs DataColumn = myDataSet.Tables("SEO").Columns("Title")

Hmm, just getting around to trying this and it doesn't work. Using this code above. myColumnValue.toString is simply the word "Title". Its not actually extracting the value of the column Title but simply setting this to the name of the column...

|||

Hi,

you can try doing this to directly access the particular column value
you willl need to traverse the rows in the table

Dim i as integer

For i = 0To myDataSet.Tables("SEO").Rows.Count - 1

Dim vAsString = myDataSet.Tables("SEO").Rows(i).Item(columnIndex)

Next

|||Yeh, I just used the row syntax you indicated as the "second" possibility. Since there is only one row, we don't have to loop it. Thanks.