How To Add Table Column Extended Properties To A View.
Apr 7, 2008
I am using SqlServer 2005 and I have created small sample table with culumns that have Extended properties.
FirstName have [First Name] extended propery called M_Caption.
Then I created a view from the table to use in an access database.
My problem is, I can't get the table column extended properties to show up in the view column extended properties.
Here is the table
/****** Object: Table [dbo].[Person] Script Date: 04/07/2008 14:42:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
[Id] [int] NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Caption', @value=N'First Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'FirstName'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Caption', @value=N'Last Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'LastName'
I created this view
/****** Object: View [dbo].[View_1] Script Date: 04/07/2008 14:50:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_1]
AS
SELECT FirstName, LastName
FROM dbo.Person
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "Person"
Begin Extent =
Top = 6
Left = 38
Bottom = 99
Right = 189
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
Can anyone help me with displaying several table properites at once. I know I can use sp_help 'tablename' to get one at time. What is the best method to get several databases at a time?
Plz help me to know whats wrong with following script:
EXEC sp_addextendedproperty @name = 'col_Programs_ID_Description', @Value = 'The Unique Identifier for a specific Program', @level1Type = 'Programs', @level2Type = 'ID'
I get the following error: Server: Msg 15600, Level 15, State 1, Procedure sp_addextendedproperty, Line 42 An invalid parameter or option was specified for procedure 'sp_addextendedproperty'.
NOTE: If I dont use the last 2 parameters this procedure runs fine, but it adds the Extended Property to the Database Level while I'm trying to add it to the TABLE-->COLUMN level.
Does anyone know of a good primer on extended properties and why I would want to use them. I have you the on-line books sections but that does a clear "why". I have search for white papers and similar documents.
I understand they are good for documentation, but I was looking for something that explain Microsoft long-term vision. How I might use them in template scripts, VSTS, etc.
Under SSMS I can add simple text and carriage returns to an extended property associated with a column. SSMS preserves the <CRLF> formatting that I used. But when I try to read this same text using, for example,
Hello, Can I read and write the extended properties with Access?
If it€™s possible is better create a project (ADP) or a classic MDB?
When I try with this query in the query panel (in a MDB)
SELECT objtype, objname, name, value FROM fn_listextendedproperty (NULL, 'schema', 'Person', 'table', default, NULL, NULL)
I have this error message:
The size of a field is too long
If I try with an ADP, when I ask a new query in design view, I have a message for some roblems between the Access version (2003) and SQL Server version (2005) and I can€™t save the query.
Hi!I have a user on my database that has only "select" access(db_datareader).Problem is, I also want him to also be able to create/update extendedproperties on tables or views, but without modifying the tables'schema.I played around with GRANT but apparently, a member of "db_datareader"cannot create/modify extended properties on an object if he's not theowner of this object. I tried making this user a member of"db_datawriter", but it didn't work.Nothing short of making him member of "db_ddladmin" worked... but thenthis is too much, the user can now alter to delete tables: i DON'T wantthat!Any ideas anyone? Cheers!Ben
"Object is invalid. Extended properties are not permitted on 'dbHSPS.dbo.Conflict', or the object does not exist."
However I can SELECT * FROM dbHSPS.dbo.Conflict and it returns results, and I know the test_me extended property exists for table "Conflict" in dbHSPS.dbo ...
When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,
AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine. What I haven't found anywhere on Help or MSDN online is what these extended properties mean. Could someone enlighten me? 1. What is a description of the above properties? 2. What is the benefit of using them (clearly the function works without using them). Does it help debugging? 3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?
I have documentation in the form of extended properties for tables which are subscribers in a replication scheme. The documentation describes the tables in reference to their replication scheme. I don't want to apply them to the source and have them published.I can't apply the extended properties receiving the error, 'don't have permission' yet I am DB creator on all systems. The theory is that I can't modify the subscription. Which makes sense.Can I turn off the replication, apply the extended properties, then turn on replication without causing harm?
I am trying to create a hard copy of all the table definitions in the database and have not found a way to do so. The report would have all the columns for a partcular table and each property assigned to the column. For instance Customers table has CustomerId, FirstName, LastName, Phone, etc. The CustomerId is PK, Identity, Auto Increment, BIGINT, NOT NULL. FirstName is NVARCHAR(30), NULL. LastName is NVARCHAR(30), NULL. Phone is NVARCHAR(20), NULL. Can anyone point me to the right system function or sp to get this info?
I store files in db in sql server 2008 by filestream. But when a column would be added to table which have filestream, properties of table would be changed. by every things change on table, retrieve files will faced to error. but store process work probably.
and filestream filegroup at following address will be empty. why?
Right click on table --> properties --> storage --> filestream filegroup
I have added alias datatypes using sp_addtype and would like to view the properties defined in it. please let me knowhow to view the properties of them and also is there a way to list all alias datatypes existing in the current database along with their properties.
Sudenly I stoped being able to view sql server object properties in the visual studio properties window . When I selected an sql server object in the server explorer i could see its properties in the prop. window . Now I can't see them anymore, the properties window apears blank.
Can anyone tell what do I need to do to be able to view them again.
Is there any way to get the base table/column from a view by supplying the view name/column name_in_view? I know about INFORMATION_SCHEMA.VIEW_COLUMN_USAGE, but it doesn't always work. Try it on a regular database, not pubs. Any help is appreciated!
Tab_Name & Load_Status columns provides the information about Name of table and status of data getting loaded to this table.
To this I would like to add a column to display the running count in the table, I mean how many records so far loaded, as below the recordCount coming from the same table tbl_collection
Have written stored procs to insert/update/drop column extended props but can't quite figure out how to drop all extended props for a particular table::column.
I have a date column that has 1/1/1900 entries and I want to write a formula..(i think)...in the column formula section that when this date is encountered, the column should show NULL. I don't know the syntax to use in the formula section of the column properties. Can anyone give some syntax examples.
I'm building a custom component and UI and am a bit confused on where I need to create and/or set custom column properties?
My UI will have a datagrid with three columns: 1) a check box to select a column for use by the component, 2) the input column name, and 3) a "differentiator" checkbox that indicates an extra property about some of the columns that have the first column checkbox checked (For example, my component may be using five input columns, but three of those need to be used in a slightly different way.)
The problem is, I don't understand when or where I'm supposed to create the custom property for the input column. SetUsageType is where I've been thinking, but I don't know if I'm supposed to be creating it for an input column or a virtual input column. I'd appreciate any guidance.
I am converting the contents of 64 lookup tables from individual tables (each called lookup_xxxxx) into a single LookupReference table. The individual lookup tables are my OLE DB Source objects. I want to derive the variable part of the lookup_xxxxx table name from the OLE DB Source 'OpenRowset' property as a variable and make it into a derived column (which will be the lookup Type column in the output table). For example, extract "SpeciesType" from the input source called 'lookup_SpeciesType' and put it into the derived column.
I cannot find a System variable that refers to the input data source. Does anyone know how I can do this?
I put togehter a package that opens a flat file, parses the data based on the semi-colon delimeter, and imports the rows into a database table. Thats the fun easy part.
What I cant figure out is how to add a variable that will hold a constant ID value that will be persisted with the same value to all rows inserted to the DB. Making the problem harder, I would like that this value be defined in a properties file or database table of some sort so that I can do a lookup based on the file name / location to find out what value should be used.
Any suggestions? I hope my explanation makes at least some sense - but basically I want to do a look up in a configuration of some sort, pull out a single value, and add it to a data import.
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A: (name phone house cost of house,zipcodecountystatecountry) -a view will later split this large varchar string based column b: is the source filename of the data load (varchar 256) ....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd (@buildd nvarchar(4), @avdate as nvarchar(25)) Returns nvarchar(25) as Begin declare @ret nvarchar(25) declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from ( select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.
Hello. I just started using SQL Server Management Studio Express. I am creating a new table by right-clicking the "Tables" folder and selecting "New Table". Then i hit F4 to bring up the "Properties" (because I want to associate a Schema to the table, but when the "Properties" section appears, it's blank. I can't figure out a way to get anything to appear there. Any ideas? Thanks!...
Query #1:select <list of fields>from Cjoin B on C.b_key = B.b_keyjoin A on B.a_key = A.a_keywhere A.o_key = <some value>Query #2:select <list of fields>from Cwhere b_key in (select b_keyfrom Bwhere a_key in (select a_keyfrom Awhere o_key = <some value>))#1 (and other things with the same general pattern) are used inliterally thousands of places in this one client's system, and ismuch nicer to write, but seems to be rather slower than #2. Isthere any way to tweak the tables to tell the system somethinglike "hey, B, whenever you're joined to A, you should seriouslyconsider waiting for A to be filtered down to a manageable levelfirst"? And similarly for C/B.MS SQL 2000, SP3, 6.5 compat mode. These are set in stone untilwe upgrade the accounting software (highly non-trivial).
I am setting up extended events more or less just fine, however I am a bit confused as to how to read and load them into a table for querying. In particular the offset part - is there a way to load just a given dates worth in?
I've got the files configured to be 20MB before rolling over, the XE is running all the time.
So if i load in the full file now, say that covers 2.5 days worth, when I load it again tomorrow to get the updated data I'm also reloading today, which is a waste?
I presume I am going about this wrong, but lack an example that really goes into detail of practicalities of loading this data.