Extended Properties SQL Server 2005

[dnordin]

Hello,

I'm having difficulty accessing Table extended properties in SQL server.

My script is C#.

I'm using the following code snippet I found on a post last year.

Code: Select all

MyMeta.Single single = MyMeta.Databases[0].Tables[0] as MyMeta.Single;
KeyValueCollection extendedProperties = single.DatabaseSpecificMetaData(\"ExtendedProperties\") as KeyValueCollection;

The KeyValueCollection is not null but it is always empty.

The connection string is of the form:

Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBName;Data Source=ServerName

The DB Target is SqlClient

The Driver is Microsoft SQL Server

What am I missing? Is there something special in the connection string I need or has the syntax changed for this? I'm running version 1.3.0.3 of the generator MyMeta 1.3.0.0

Thanks,

Dan

[mike.griffin]

I think you need to have admin privileges on your database, do you have that?

[dnordin]

Yes, I'm an admin on the server / db

[mike.griffin]

Hmmm, your connection string looks funny?

Provider=SQLNCLI.1;

We use OLEDB, please use our OLEDB button on the default settings dialog to setup your connection and it should work.

[dnordin]

My original connection string (built using the MyGeneration OLEDB dialogues) was:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=DBName;Data Source=ServerName

I thought maybe the problem was in the SQLOLEDB so I changed the provider (again using the dialouges provided) and changed it to the SQL Native provider: SQLNCLI.1

In either case I always get zero rows in the collections.

I've tested with table and column extended properties and with more than one property defined but still no luck.

[dnordin]

I notice when debugging that a few of the object in the MyMeta model have an XPath property implying to me that they hold their information in XML. Is there a property / method somewhere that will show me the complete XML document that makes up MyMeta's understanding of my database?

[dnordin]

I found the problem:

looking through the source code on line I noticed that in MyMeta.Sql.DatabaseSpecific you are using the following query to extract the properties:

Code: Select all

private const string QUERY = @\"SELECT [name], [value] FROM ::fn_listextendedproperty (NULL, 'user', {0}, {1}, {2}, {3}, {4})\";


SQL books online informs us that having 'user' in the second position is depricated and should now be 'schema'.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/59bbb91f-a277-4a35-803e-dcb91e847a49.htm

The database I am using does indeed leverage multiple schema, when I run the query in SQL Management Studio with 'user' changed to 'schema' I get the result set I am expecting.