Monday, March 26, 2012
Microsoft Please help
Monday, March 12, 2012
Mgmt Studio: List versus Details view
Hi Bob,
We actually have plans to only display the Policy Health State in the Details section for only those objects that have policy state. For CTP6, we are looking to extend the Object Details to show many more properties than you had in the past. Please stay tuned.
Bill Ramos,
Lead PM
|||Many, many thanks Bill.
Friday, March 9, 2012
Metadata: Columns in Indexes
Hi, Do you know if there are any way to know the name of wich columns integrate the indexes?
I have just found a column named "keys" in the table "sysindexes" but it is just a kind of code.
I have a problem with the primary keys that have created,
something similar to the refered: http://support.microsoft.com/kb/262541/en-us
So I am trying to identify which of my indexes would make conflict with the PKs, and
also need to standarize them. Could you help me please?
There are a few ways.
1. In 2000 or earlier, you can use: INDEX_COL, which is a bit annoying, but it works
2. In 2005, use the sys.index_columns view. It gives you a much easier view.
3. In either, for KEY constraints, use the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view.
|||Thank Louis, Now I am using this query to get the column names of each index,
but just don't know how to compare the columns from one index with the ones that has
the other.
I need to be sure that the columns that I am using as a part of my PK's are not contained exactly as a
part of any Index because that will cause me problems with certain kind of querys used trough my
linked server (because of the bug that I have refered)
select so.name as tabla, si.name as indice, --, si.indid as tipoindice,
sc.name
from sysobjects so,
sysindexes si,
sysindexkeys sik,
syscolumns sc
where so.xtype = 'U'
and si.id = OBJECT_ID(so.name)
and so.id = sik.id
and si.indid = sik.indid
and so.id = sc.id
and sik.colid = sc.colid
and (indexproperty(si.id, si.name, 'IsAutoStatistics') = 0 /*Index was generated by the auto create statistics option of sp_dboption. 1 = True 0 = False NULL = Invalid input)*/
and indexproperty(si.id, si.name, 'IsHypothetical') = 0)
and indexproperty(si.id, si.name, 'IsStatistics') = 0
order by si.indid
Whit this query i get something like this:
Table Index Column
-
Author IX_Author_2 code
Author IX_Author_2 c_branch
Author IX_Author_1 code
Author PK_Author code
Author PK_Author c_branch
So, for example the case of IX_Author_2 and PK_Author will give me a problem and
I need to correct the Indexes but first i need a way to identify them.
I am trying with a cursor but just cannot get the right.
Could anybody give me an idea please.
metadata refresh?
What do you do to address this:
[OLE DB Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "objectName1" needs to be updated in the external metadata column collection.
A corollary question: what does right-clicking a package in Solution Explorer and clicking "Reload with Upgrade" do?
mruniqueid wrote:
A corollary question: what does right-clicking a package in Solution Explorer and clicking "Reload with Upgrade" do?
I can answer this one - the versioning story for tasks allows ISVs (or Microsoft) to release completely new version of the task (new assembly version for managed, or new CLSID for native tasks). Designer doesn't automatically upgrade package to use new task version, as it will probably break package compatibility with systems that have old task version installed. Reload with Upgrade command tells the designer to try to upgrade tasks to new version.
|||What do you do to address this:
[OLE DB Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "objectName1" needs to be updated in the external metadata column collection.
This means the metadata of your source column ("objectName1") had changed in the source table or maybe you added this column after configuring your OLE DB source component.
You should try to double-click on your OLE DB Source and the pop-up window, with a question if you want to automatically synchronize the metadata, will show up.
HTH.
|||Thanks - works as advertised.
In the meantime I found something interesting that wasn't immediately obvious. I have a foreach container with an item enumeration. The enumeration collection is a list of views, all with the same "basic" definition (I'll explain "basic" in a minute). The container has a data flow task that opens a view (with the view name defined by a variable from the foreach container) as an OLE DB source and copies the view contents to one OLE DB destination.
I kept getting errors about metadata differences and tracked the error to the view definitions. All views have the same column data type definijtions but some of the view columns are literals with differing text lengths, such as 'person' (6 chars), 'location' (8 chars), and so on. To solve the problem the view definition had to be modified to cast these literals to the destination column length, such as
CAST('person' AS varchar(50)) AS objectName
All of the views have to have exactly the same data types and lengths in order for the package to work.
Just wanted to pass this along in case anybody else runs into the problem
|||Correct. A data-flow is bound to the metadata at design-time. The expected metadata cannot change at runtime.
-Jamie
|||My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?
Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.
Editing every line is tedious. I have to remove "Copy of" and change the size. So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.
If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.
There needs to be a comprehesive, contains all clicks menu for this tool.
There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.
Is there no other way to clear the external metadata error?|||
My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.Editing every line is tedious. I have to remove "Copy of" and change the size. So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.
If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.
There needs to be a comprehesive, contains all clicks menu for this tool.
There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.
Is there no other way to clear the external metadata error?
I understand your pain and I have similar complains....
The Data conversion transformation adds a 'Copy of..' to each column because it preserves the original column; if you decided to remove the Copy of part of the name; then SSIS is forced to use a multipart name; as the name of the column is duplicate within the dataflow; so you can see things like (OLE DB Source.MyColumn and Data Convertion.MyColumn). So, I don’t recommend you to edit the names as it gets harder when you are mapping them in the destination components.
Please, feel free to post a suggestion in Microsoft Connect site:
http://connect.microsoft.com/SQLServer/Feedback
|||Thanks for your reply.
I had no idea it was ,,,beneficial to leave the "Copy of" prefix on the output names,
I went back to Kirk's book. If you have it, see page 479 from which I quote:
"You should avoid using this component if possible. If possible, change the column type at the source."
If there existed the magic all things menu that I wished for in my posting, it would allow access to all the features without digging deep to find a context or builder button, I might be able to refresh the meta data.
Since I posted, I have changed some of the fields data types to DT_WSTR.
Then I clicked the connection to the destination to see what types were being passed. My changes were not present in the connection stream. I saved the package, closed it and reopened. Still the changes are not propagating to the destination. I am wondering If an entire new package will make it work?
IanO|||
My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?
You can't. Its a new column so it has to call it something. "Copy of XXX" is as arbitrary as anything else.
Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.
Naturally it does. So your new column will have to be changed to the data type that you require. Should the development tools simply guess what length to put on there? Of course not, so it puts an arbitrary value of 50 in there. SSIS will never make changes for you - you are always responsible for making changes yourself (this is a good thing by the way)
Editing every line is tedious. I have to remove "Copy of" and change the size.
Hopefully you understand why this is unavoidable. YOU have to tell SSIS what the data type of each column should be - it will not try and guess.
So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.
Sorry, I'm a bit confused on this one. What button are you referring to on the OLE DB Connection Manager?
If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.
There needs to be a comprehesive, contains all clicks menu for this tool.
There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.
No, there is no place. Putting an average in like you suggest is a no-no because the would constitute SSIS guessing and as I explained that is something it won't do hence an arbitrary value is better. Besides, if it COULD do the average of the first 100 rows (which it cant because at design-time no actual rows exist in the pipeline - so where are these 100 rows that you are referring to?) that would create an error for any of the rows that exceeded that average - so that isn't a good idea.
What *might* be a good idea would be to have the option of selecting what the arbitrary value should be. That could be an option you set within BIDS. I do however see problems with this if different developers have this option set differently. For safety's sake I think the behaviour is just right as it is currently.
Is there no other way to clear the external metadata error?
I don't think so. The developer is responsible for setting what the external metadata should be.
Hope that helps.
-Jamie
|||
Thanks for your reply.
I had no idea it was ,,,beneficial to leave the "Copy of" prefix on the output names,
I think what Rafael means by this is that its not a good idea to change the name of the new column to be the same as the one that it is derived from as this causes confusion later in the pipeline.
I went back to Kirk's book. If you have it, see page 479 from which I quote:
"You should avoid using this component if possible. If possible, change the column type at the source."
I agree with the assertion that you should change at source if necassary although I'm not sure that avoidance is a good strategy. This component has its uses in certain circumstances. I don't have the book to hand so can't read the context in which Kirk stated this.
If there existed the magic all things menu that I wished for in my posting, it would allow access to all the features without digging deep to find a context or builder button, I might be able to refresh the meta data.
Since I posted, I have changed some of the fields data types to DT_WSTR.
Then I clicked the connection to the destination to see what types were being passed. My changes were not present in the connection stream. I saved the package, closed it and reopened. Still the changes are not propagating to the destination. I am wondering If an entire new package will make it work?
IanO
The reason changes don't get propogated will be because you have an asynchronous component such as a SORT or an AGGREGATE. I won't go into the details of asynchronous components (there's plenty of info out there though: http://search.live.com/results.aspx?q=ssis+asynchronous+synchronous&mkt=en-GB&form=QBNO) but you'll have to take my word for it that this is unavoidable because of the vary nature of the components that you are using. Any column that is input into an asynchronous component is NOT the same as any column that is output from it - even if they have the same name.
Its annoying, yes, but that's the way it is I'm afraid. If you want a fuller explanantion then let me know and I'll do my best to provide one.
-Jamie
metadata refresh?
What do you do to address this:
[OLE DB Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "objectName1" needs to be updated in the external metadata column collection.
A corollary question: what does right-clicking a package in Solution Explorer and clicking "Reload with Upgrade" do?
A corollary question: what does right-clicking a package in Solution Explorer and clicking "Reload with Upgrade" do?
I can answer this one - the versioning story for tasks allows ISVs (or Microsoft) to release completely new version of the task (new assembly version for managed, or new CLSID for native tasks). Designer doesn't automatically upgrade package to use new task version, as it will probably break package compatibility with systems that have old task version installed. Reload with Upgrade command tells the designer to try to upgrade tasks to new version.
|||What do you do to address this:
[OLE DB Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "objectName1" needs to be updated in the external metadata column collection.
This means the metadata of your source column ("objectName1") had changed in the source table or maybe you added this column after configuring your OLE DB source component.
You should try to double-click on your OLE DB Source and the pop-up window, with a question if you want to automatically synchronize the metadata, will show up.
HTH.
|||Thanks - works as advertised.
In the meantime I found something interesting that wasn't immediately obvious. I have a foreach container with an item enumeration. The enumeration collection is a list of views, all with the same "basic" definition (I'll explain "basic" in a minute). The container has a data flow task that opens a view (with the view name defined by a variable from the foreach container) as an OLE DB source and copies the view contents to one OLE DB destination.
I kept getting errors about metadata differences and tracked the error to the view definitions. All views have the same column data type definijtions but some of the view columns are literals with differing text lengths, such as 'person' (6 chars), 'location' (8 chars), and so on. To solve the problem the view definition had to be modified to cast these literals to the destination column length, such as
CAST('person' AS varchar(50)) AS objectName
All of the views have to have exactly the same data types and lengths in order for the package to work.
Just wanted to pass this along in case anybody else runs into the problem
|||Correct. A data-flow is bound to the metadata at design-time. The expected metadata cannot change at runtime.
-Jamie
|||My project has a flat file input and the destination table is using nvarchar(xx).When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?
Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.
Editing every line is tedious. I have to remove "Copy of" and change the size. So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.
If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.
There needs to be a comprehesive, contains all clicks menu for this tool.
There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.
Is there no other way to clear the external metadata error?
|||
My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.Editing every line is tedious. I have to remove "Copy of" and change the size. So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.
If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.
There needs to be a comprehesive, contains all clicks menu for this tool.
There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.
Is there no other way to clear the external metadata error?
I understand your pain and I have similar complains....
The Data conversion transformation adds a 'Copy of..' to each column because it preserves the original column; if you decided to remove the Copy of part of the name; then SSIS is forced to use a multipart name; as the name of the column is duplicate within the dataflow; so you can see things like (OLE DB Source.MyColumn and Data Convertion.MyColumn). So, I don’t recommend you to edit the names as it gets harder when you are mapping them in the destination components.
Please, feel free to post a suggestion in Microsoft Connect site:
http://connect.microsoft.com/SQLServer/Feedback
|||Thanks for your reply.
I had no idea it was ,,,beneficial to leave the "Copy of" prefix on the output names,
I went back to Kirk's book. If you have it, see page 479 from which I quote:
"You should avoid using this component if possible. If possible, change the column type at the source."
If there existed the magic all things menu that I wished for in my posting, it would allow access to all the features without digging deep to find a context or builder button, I might be able to refresh the meta data.
Since I posted, I have changed some of the fields data types to DT_WSTR.
Then I clicked the connection to the destination to see what types were being passed. My changes were not present in the connection stream. I saved the package, closed it and reopened. Still the changes are not propagating to the destination. I am wondering If an entire new package will make it work?
IanO
|||
My project has a flat file input and the destination table is using nvarchar(xx).
When I drag the Data Conversion tool on the Data Flow tab it makes the output alias for each field "Copy of original field name". Why is this? How can I make it stop prepending "Copy of"?
You can't. Its a new column so it has to call it something. "Copy of XXX" is as arbitrary as anything else.
Next it makes the width of every field 50.
Naturally, the next step complains because I am attempting to put 50 characters in to a two character state field.
Naturally it does. So your new column will have to be changed to the data type that you require. Should the development tools simply guess what length to put on there? Of course not, so it puts an arbitrary value of 50 in there. SSIS will never make changes for you - you are always responsible for making changes yourself (this is a good thing by the way)
Editing every line is tedious. I have to remove "Copy of" and change the size.
Hopefully you understand why this is unavoidable. YOU have to tell SSIS what the data type of each column should be - it will not try and guess.
So when I get done and have it working, I fee that battle has been won. But today I have the subject error and have not been successful in getting rid of the error because there is no OLE DB connection to double click and find yet another hidden button.
Sorry, I'm a bit confused on this one. What button are you referring to on the OLE DB Connection Manager?
If I have to delete the Data Conversion tool instance (because it apparently has lost its mind) then I will be forced to do all that editing again.
There needs to be a comprehesive, contains all clicks menu for this tool.
There has got to be "some" place where I can change that default 50 to some friendlier number like the average width of the first 100 rows for that column.
No, there is no place. Putting an average in like you suggest is a no-no because the would constitute SSIS guessing and as I explained that is something it won't do hence an arbitrary value is better. Besides, if it COULD do the average of the first 100 rows (which it cant because at design-time no actual rows exist in the pipeline - so where are these 100 rows that you are referring to?) that would create an error for any of the rows that exceeded that average - so that isn't a good idea.
What *might* be a good idea would be to have the option of selecting what the arbitrary value should be. That could be an option you set within BIDS. I do however see problems with this if different developers have this option set differently. For safety's sake I think the behaviour is just right as it is currently.
Is there no other way to clear the external metadata error?
I don't think so. The developer is responsible for setting what the external metadata should be.
Hope that helps.
-Jamie
|||
Thanks for your reply.
I had no idea it was ,,,beneficial to leave the "Copy of" prefix on the output names,
I think what Rafael means by this is that its not a good idea to change the name of the new column to be the same as the one that it is derived from as this causes confusion later in the pipeline.
I went back to Kirk's book. If you have it, see page 479 from which I quote:
"You should avoid using this component if possible. If possible, change the column type at the source."
I agree with the assertion that you should change at source if necassary although I'm not sure that avoidance is a good strategy. This component has its uses in certain circumstances. I don't have the book to hand so can't read the context in which Kirk stated this.
If there existed the magic all things menu that I wished for in my posting, it would allow access to all the features without digging deep to find a context or builder button, I might be able to refresh the meta data.
Since I posted, I have changed some of the fields data types to DT_WSTR.
Then I clicked the connection to the destination to see what types were being passed. My changes were not present in the connection stream. I saved the package, closed it and reopened. Still the changes are not propagating to the destination. I am wondering If an entire new package will make it work?
IanO
The reason changes don't get propogated will be because you have an asynchronous component such as a SORT or an AGGREGATE. I won't go into the details of asynchronous components (there's plenty of info out there though: http://search.live.com/results.aspx?q=ssis+asynchronous+synchronous&mkt=en-GB&form=QBNO) but you'll have to take my word for it that this is unavoidable because of the vary nature of the components that you are using. Any column that is input into an asynchronous component is NOT the same as any column that is output from it - even if they have the same name.
Its annoying, yes, but that's the way it is I'm afraid. If you want a fuller explanantion then let me know and I'll do my best to provide one.
-Jamie
Wednesday, March 7, 2012
Metadata Inheritance
I have written a mechanism which returns meta data back to a client for
different views. For example, one meta data item on a column is the Display
Name.
Now, so I don't have to keep attaching meta data to a particular column
every time I create a new view, I "inherit" the meta data from a parent view
or table by looking at all the same named columns in any views or tables
that the table depends (sysdepends) on.
The problem is, sometimes it make sense to change the name of a column in a
view from the underlying table (e.g. company table has a "name" column, but
in the view, you want to call it "company_name").
My question is, is there an easy way to determine where a view's column
really came from?
Thanks,
-PaulNever mind, I forgot to look at the depnumber column in sysdepends!
"Paul" <a@.b.com> wrote in message
news:eWN9knVCFHA.3940@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have written a mechanism which returns meta data back to a client for
> different views. For example, one meta data item on a column is the
> Display Name.
> Now, so I don't have to keep attaching meta data to a particular column
> every time I create a new view, I "inherit" the meta data from a parent
> view or table by looking at all the same named columns in any views or
> tables that the table depends (sysdepends) on.
> The problem is, sometimes it make sense to change the name of a column in
> a view from the underlying table (e.g. company table has a "name" column,
> but in the view, you want to call it "company_name").
> My question is, is there an easy way to determine where a view's column
> really came from?
> Thanks,
> -Paul
>|||Okay, now the hard question.
How do I correlate a column in the sysdepends table back to a column in the
resulting view's columns?
Thanks,
-Paul
"Paul" <a@.b.com> wrote in message
news:OBXyfyVCFHA.1836@.tk2msftngp13.phx.gbl...
> Never mind, I forgot to look at the depnumber column in sysdepends!
>
> "Paul" <a@.b.com> wrote in message
> news:eWN9knVCFHA.3940@.TK2MSFTNGP09.phx.gbl...
>|||On Wed, 2 Feb 2005 15:27:44 -0500, Paul wrote:
>Okay, now the hard question.
>How do I correlate a column in the sysdepends table back to a column in the
>resulting view's columns?
Hi Paul,
You don't.
You are probably thinking right now of fairly straight-forward views,
where such a mapping would be viable. But views can be fairly complex!
CREATE VIEW test
AS SELECT Col1 - Col2 AS A,
Col1 + Col2 AS B,
CASE WHEN EXISTS (SELECT *
FROM YZ
WHERE Foo = SomeTable.Bar)
THEN 1
ELSE 2
END AS C,
'Constant value' AS D,
Col3 + 1 AS E,
Col3 - 1 AS F
FROM SomeTable
How should each of the view's columns be correlated to each of the
underlying table's columns? And remember that this is also a fairly simple
example - I could come up with much more contrived views (some of my real
views for projects I do are much more contrived!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Metadata in sql server 2005
I add extended properties to table in sql server 2005, for example i add Caption property to every column.
In case I generate a datasource from table in sql server in visual studio 2005, Is there a way that "Caption" property in datatable to be filled from that metadata, or metadata is useless for dataset.
Every time i had to fill manually the caption property of datatable generated .
hi,
you can access those extended properties calling fn_listextendedproperty function, similar to
SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.Invoice ( ID INT NOT NULL PRIMARY KEY , BillNo INT NOT NULL -- , others ) GO EXEC sp_addextendedproperty '1st property' , '1St property value' , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL; EXEC sp_addextendedproperty '2nd property' , '2nd property value' , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL; GO PRINT 'All available properties'; SELECT CONVERT(VARCHAR(20), [name]) AS [Property Name] , CONVERT(VARCHAR(20), [value]) AS [Property vCharValue] FROM ::fn_listextendedproperty( NULL , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL ); GO PRINT 'just the required [1st property] property'; SELECT CONVERT(VARCHAR(20), [name]) AS [Property Name] , convert(VARCHAR(20),[value]) AS [Property vCharValue] FROM ::fn_listextendedproperty( '1st property' , 'user' , 'dbo' , 'table' , 'Invoice' , NULL , NULL ); GO DROP TABLE dbo.Invoice; --< All available properties Property Name Property vCharValue -- -- 1st property 1St property value 2nd property 2nd property value just the required [1st property] property Property Name Property vCharValue -- -- 1st property 1St property valueso, assuming you are interested in the Invoice management, you have to query for all the properties of the dbo.Invoice table columns...
this can obviously fill a dataset or a datareader you can then consume to map as desired the returned values so you could assume the "1st property" is the caption of the corresponding UI control and you can set it accordingly..
BTW, to manage those properties outside the Microsoft tools you have to resort on http://msdn2.microsoft.com/en-us/library/aa174648(SQL.80).aspx and http://msdn2.microsoft.com/en-us/library/ms190243.aspx
regards|||Thank you for your reply.I mean , what is the extended properties that may be used automatically by visual studio 2005.
For example , Can the "Caption " property of datatable created in vs 2005 , be set automatically using extended properties?
This property is useful, because form wizard creates label with that "caption" value(when i set it at design time), and textbox bound to column.
|||
hi,
no, you have to deal with that "by hand"...
regards
Metadata Detail
I want to get some metadata information for my tables
Some column properties I can get using System tabes and Information Schema
but I can not get some information as below
I want to get information of column properties Identity Seed, Identity
Increment, Is Rowguid , Formula value, Description for my any column which
this property applicable.
Any help will be highly appreciated
Thanks> I want to get information of column properties Identity Seed, Identity
> Increment, Is Rowguid , Formula value, Description for my any column whic
h
> this property applicable.
Use functionS COLUMNPROPERTY, IDENT_SEED, IDENT_INCR, and IDENT_CURRENT. For
"Description" use system function fn_listextendedproperty and for "Formula
value" check system table syscomments.
Example:
use northwind
go
create table dbo.t (
colA int not null identity,
colB uniqueidentifier ROWGUIDCOL not null default(newid()),
colC as power(2, colA),
colD as colA % 10
)
go
select
ordinal_position,
column_name,
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then 'Yes' else 'No'
end as [IsIdentity],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then
ltrim(ident_seed(table_name)) else '' end as [ident_seed],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then
ltrim(ident_incr(table_name)) else '' end as [ident_incr],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsIdentity') = 1 then
ltrim(ident_current(table_name)) else '' end as [ident_current],
case when columnproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), column_name, 'IsRowGuidCol') = 1 then 'Yes' else 'No
'
end as [IsRowGuidCol],
coalesce(sc.[text], '') as [Formula]
from
information_schema.columns as c
left join
syscomments as sc
on object_id(quotename(table_schema) + '.' + quotename(table_name)) =
sc.[id] and sc.number = c.ordinal_position
where
table_name = 't'
order by
ordinal_position
go
drop table t
go
AMB
"AM" wrote:
> Hi all
> I want to get some metadata information for my tables
> Some column properties I can get using System tabes and Information Schem
a
> but I can not get some information as below
> I want to get information of column properties Identity Seed, Identity
> Increment, Is Rowguid , Formula value, Description for my any column whic
h
> this property applicable.
>
> Any help will be highly appreciated
> Thanks
>
>|||Thanks
It helps me a lot
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:364FC5DB-E29F-47E8-BA44-A0842193E142@.microsoft.com...
which
> Use functionS COLUMNPROPERTY, IDENT_SEED, IDENT_INCR, and IDENT_CURRENT.
For
> "Description" use system function fn_listextendedproperty and for "Formula
> value" check system table syscomments.
> Example:
> use northwind
> go
> create table dbo.t (
> colA int not null identity,
> colB uniqueidentifier ROWGUIDCOL not null default(newid()),
> colC as power(2, colA),
> colD as colA % 10
> )
> go
> select
> ordinal_position,
> column_name,
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then 'Yes' else
'No'
> end as [IsIdentity],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then
> ltrim(ident_seed(table_name)) else '' end as [ident_seed],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then
> ltrim(ident_incr(table_name)) else '' end as [ident_incr],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsIdentity') = 1 then
> ltrim(ident_current(table_name)) else '' end as [ident_current],
> case when columnproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), column_name, 'IsRowGuidCol') = 1 then 'Yes' else
'No'
> end as [IsRowGuidCol],
> coalesce(sc.[text], '') as [Formula]
> from
> information_schema.columns as c
> left join
> syscomments as sc
> on object_id(quotename(table_schema) + '.' + quotename(table_name)) =
> sc.[id] and sc.number = c.ordinal_position
> where
> table_name = 't'
> order by
> ordinal_position
> go
> drop table t
> go
>
> AMB
>
> "AM" wrote:
>
Schema
which|||See if this helps:
http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"AM" wrote:
> Thanks
> It helps me a lot
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:364FC5DB-E29F-47E8-BA44-A0842193E142@.microsoft.com...
> which
> For
> 'No'
> 'No'
> Schema
> which
>
>|||Also check out following link for " Schema: How do I show the description
property of a column? ":
http://www.aspfaq.com/show.asp?id=2244
"AM" <anonymous@.examnotes.net> wrote in message
news:%23WseefgOFHA.1476@.TK2MSFTNGP09.phx.gbl...
> Hi all
> I want to get some metadata information for my tables
> Some column properties I can get using System tabes and Information
> Schema
> but I can not get some information as below
> I want to get information of column properties Identity Seed, Identity
> Increment, Is Rowguid , Formula value, Description for my any column
> which
> this property applicable.
>
> Any help will be highly appreciated
> Thanks
>