Combining Row Data

I am adding data to a table from a table of identified duplicates.
These duplicates are not Key Violations, they are actual double entries
where one was deactivated prior to the new record being added (not very
clean but necessary given the system the data originates from). What I
would like to do is to take the data and combine some fields of the
duplicate rows.
I have created my mapping table (the table I want to add data to) which
looks like:
UniversalID (Identity)(PK)
Original_ID1 <-key from 1st row
Original_ID2 <-key from 2nd row
2ndSystemID
My data would look like this:
OriginalID Name 2ndSystemID
123 Smith, John KHGK39
124 Smith, John KHGK39
..
What I want is
UniversalID Original_ID1 Original_ID2 2ndSystemID
1 123 124 KHGK39
...
I know that this is doable, and I also know that I should know how, but
just can't seem to see the forest because all of the trees are in my
way.
Thanks for any help you can provide.
MTAnd if there are triplicates?
Don't try fixing old flaws by introducing new ones - such as breaking normal
form. I'd sugesst either deleting unwanted rows or adding a table to store
duplicate values of OriginalID referencing a proper primary key (e.g
2ndSystemID). Normalize now and prevent further difficulties.
ML
http://milambda.blogspot.com/|||Not really trying to break normal form. I need all of these values to
reference back to the original systems where the data lives. This is
why I need all of them. The UniversalID will be the new Key, the old
IDs, will become simple references.
I truly wish I could use only one, but unfortunately due to the nature
of the data I am dealing with, I am unable to and must find a work
around.
MT|||MT wrote:
> Not really trying to break normal form. I need all of these values to
> reference back to the original systems where the data lives. This is
> why I need all of them. The UniversalID will be the new Key, the old
> IDs, will become simple references.
> I truly wish I could use only one, but unfortunately due to the nature
> of the data I am dealing with, I am unable to and must find a work
> around.
> MT
Won't your proposal fail if there are more than 2 original ids? How
many do you expect to have to cope with?
Unfortunately you haven't given much information about keys. Here's a
guess at what I'd do:
CREATE TABLE users (universalid INTEGER PRIMARY KEY, name VARCHAR(50)
NOT NULL UNIQUE, systemid INTEGER NOT NULL);
CREATE TABLE original_ids (original_id INTEGER NOT NULL PRIMARY KEY,
universalid INTEGER NOT NULL REFERENCES users (universalid));
INSERT INTO users VALUES (1, 'Smith, John', 'KHGK39');
INSERT INTO orginal_ids VALUES (123,1);
INSERT INTO orginal_ids VALUES (124,1);
A join will map any number of original ids to the universal one.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Column widths are changing when deploying report

I designed a report in VS.Net 2003. It was actually an existing RDL file
that I just modified for my new report, adding some columns and doing various
formatting things.
In VS when I look at the report design, I have the report at 8.5 in high by
11 in wide with .25 in margins all around. I have 2 columns on the right
side of the table that, when deployed and viewed in PDF, are wider than the
width I defined for the column. For all cells in the columns Can Grow is
false. I have tried moving things all over the place but it won't affect
these two columns, they always stay as wide as they are each time.CanGrow only applies to vertical sizes, not horizontal sizes. The only
things that will grow horizontally are matrices and sized images. Do you
have any images?
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:1E9C6385-B0D0-44DC-95D8-B0317A3F07A5@.microsoft.com...
>I designed a report in VS.Net 2003. It was actually an existing RDL file
> that I just modified for my new report, adding some columns and doing
> various
> formatting things.
> In VS when I look at the report design, I have the report at 8.5 in high
> by
> 11 in wide with .25 in margins all around. I have 2 columns on the right
> side of the table that, when deployed and viewed in PDF, are wider than
> the
> width I defined for the column. For all cells in the columns Can Grow is
> false. I have tried moving things all over the place but it won't affect
> these two columns, they always stay as wide as they are each time.|||No, but I seem to have resolved the problem by deleting then recreating the
columns
"Brian Welcker [MS]" wrote:
> CanGrow only applies to vertical sizes, not horizontal sizes. The only
> things that will grow horizontally are matrices and sized images. Do you
> have any images?
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "AdamB" <AdamB@.discussions.microsoft.com> wrote in message
> news:1E9C6385-B0D0-44DC-95D8-B0317A3F07A5@.microsoft.com...
> >I designed a report in VS.Net 2003. It was actually an existing RDL file
> > that I just modified for my new report, adding some columns and doing
> > various
> > formatting things.
> >
> > In VS when I look at the report design, I have the report at 8.5 in high
> > by
> > 11 in wide with .25 in margins all around. I have 2 columns on the right
> > side of the table that, when deployed and viewed in PDF, are wider than
> > the
> > width I defined for the column. For all cells in the columns Can Grow is
> > false. I have tried moving things all over the place but it won't affect
> > these two columns, they always stay as wide as they are each time.
>
>

Column Limit = 1024?

I am adding columns to one of my tables since I noticed the column
limit to SQL Server tables is now 1024. However, I am using the
Enterprise Manager to add the columns, and it stopped allowing me to
enter more columns once I got up to about 292 columns. Is the
Enterprise Manager not capable of handling 1024 columns? Is there
another method I should use to add columns? Or is the column limit
not really 1024?
Thanks in advance for your help,
Dominic Isaia
disaia@.spacecraft.comyou won't get this problem if you use Query Analyzer.
"Dominic Isaia" <disaia@.spacecraft.com> wrote in message
news:d8ecad62.0311201503.567f217a@.posting.google.com...
> I am adding columns to one of my tables since I noticed the column
> limit to SQL Server tables is now 1024. However, I am using the
> Enterprise Manager to add the columns, and it stopped allowing me to
> enter more columns once I got up to about 292 columns. Is the
> Enterprise Manager not capable of handling 1024 columns? Is there
> another method I should use to add columns? Or is the column limit
> not really 1024?
>
> Thanks in advance for your help,
> Dominic Isaia
> disaia@.spacecraft.com|||Thanks, it works fine by using 'alter table' in the Query Analyzer.
However, it did generate an error message which might be why the
Enterprise Manager stopped allowing me to add columns. Even though
the column limit is 1024, there is a limit on the bytes per row, which
is 8060. I am using varchar as my type giving everything a 50
character length, knowing that if the data is only 2 characters it
will only take up 2 bytes of storage space and discard the 48
remaining. But I think it has to allow for 50 characters just in
case, which is why I went over that 8060 limit. Problem solved by
lowering the character length of most of my columns.
Dominic Isaia
disaia@.spacecraft.com

column descriptions

We are documenting our database by adding column descriptions for each column
in our tables. Where can I find these so I can print all them out together?
Is there a stored procedure that will help?
Mike,
You can retrieve the value of an existing extended property using the system
function FN_LISTEXTENDEDPROPERTY.
Also, rather than adding descriptions via Enterprise Manager, I would highly
suggest using:
sp_addextendedproperty
Adds a new extended property to a database object.
sp_updateextendedproperty
Updates the value of an existing extended property.
sp_dropextendedproperty
Drops an existing extended property.
All of this is available on SQL BOL by searching "Extend Properties".
Hope this helps.
- Miguel
"Mike Collins" wrote:

> We are documenting our database by adding column descriptions for each column
> in our tables. Where can I find these so I can print all them out together?
> Is there a stored procedure that will help?
|||Thanks, that sounds good, except I have one question. I hate to ask this,
because I am feeling real dumb, but where are the extended procedures? I
looked up the BOL article "Using Extended Properties on Database Objects" and
ran the Northwind example, but for each call to the extended stored
procedures, I got a not found error message in QA. Can you help with this?
Thanks.
Here is the BOL example:
USE Northwind
GO
CREATE TABLE TestExProp
(PriKey int PRIMARY KEY IDENTITY(1,1),
USPhoneNmbr char(13)
CHECK (USPhoneNmbr LIKE
'([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
),
USSocialScrty char(11)
CHECK (USSocialScrty LIKE
'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
)
)
GO
sp_addextendedproperty 'Caption', 'Caption Test Table',
'user', dbo, 'table', TestExProp
GO
sp_addextendedproperty 'Caption', 'Primary Key',
'user', dbo, 'table', TestExProp, 'column', PriKey
GO
sp_addextendedproperty 'Input Mask', '(NNN)NNN-NNNN',
'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
GO
sp_addextendedproperty 'Caption', 'US Phone Number',
'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
GO
sp_addextendedproperty 'Input Mask', 'NNN-NN-NNNN',
'user', dbo, 'table', TestExProp, 'column',
USSocialScrty
GO
sp_addextendedproperty 'Caption', 'US Social Security Number',
'user', dbo, 'table', TestExProp, 'column',
USSocialScrty
GO
This statement updates the primary-key caption property:
sp_updateextendedproperty 'Caption', 'Primary Key - Integer',
'user', dbo, 'table', TestExProp, 'column', PriKey
This statement drops the input-mask properties:
sp_dropextendedproperty 'Input Mask',
'user', dbo, 'table', TestExProp,
'column', USSocialScrty
GO
sp_dropextendedproperty 'Input Mask',
'user', dbo, 'table', TestExProp,
'column', USPhoneNmbr
GO
"Miguel" wrote:
[vbcol=seagreen]
> Mike,
> You can retrieve the value of an existing extended property using the system
> function FN_LISTEXTENDEDPROPERTY.
> Also, rather than adding descriptions via Enterprise Manager, I would highly
> suggest using:
> sp_addextendedproperty
> Adds a new extended property to a database object.
> sp_updateextendedproperty
> Updates the value of an existing extended property.
> sp_dropextendedproperty
> Drops an existing extended property.
> All of this is available on SQL BOL by searching "Extend Properties".
> Hope this helps.
> - Miguel
> "Mike Collins" wrote:
|||Mike,
I'm not sure why you got that particular error. I'm assuming you are DBO?
Extended SPs are stored in th master database, so as long as you have DBO
rights you should be able to execute the example script below. If you are not
DBO, I would expect an error similar to "User does not have permission to
perform this action". Sorry if that didn't fully answer your question.
- Miguel
"Mike Collins" wrote:
[vbcol=seagreen]
> Thanks, that sounds good, except I have one question. I hate to ask this,
> because I am feeling real dumb, but where are the extended procedures? I
> looked up the BOL article "Using Extended Properties on Database Objects" and
> ran the Northwind example, but for each call to the extended stored
> procedures, I got a not found error message in QA. Can you help with this?
> Thanks.
> Here is the BOL example:
> USE Northwind
> GO
> CREATE TABLE TestExProp
> (PriKey int PRIMARY KEY IDENTITY(1,1),
> USPhoneNmbr char(13)
> CHECK (USPhoneNmbr LIKE
> '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
> ),
> USSocialScrty char(11)
> CHECK (USSocialScrty LIKE
> '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
> )
> )
> GO
> sp_addextendedproperty 'Caption', 'Caption Test Table',
> 'user', dbo, 'table', TestExProp
> GO
> sp_addextendedproperty 'Caption', 'Primary Key',
> 'user', dbo, 'table', TestExProp, 'column', PriKey
> GO
> sp_addextendedproperty 'Input Mask', '(NNN)NNN-NNNN',
> 'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
> GO
> sp_addextendedproperty 'Caption', 'US Phone Number',
> 'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
> GO
> sp_addextendedproperty 'Input Mask', 'NNN-NN-NNNN',
> 'user', dbo, 'table', TestExProp, 'column',
> USSocialScrty
> GO
> sp_addextendedproperty 'Caption', 'US Social Security Number',
> 'user', dbo, 'table', TestExProp, 'column',
> USSocialScrty
> GO
> This statement updates the primary-key caption property:
> sp_updateextendedproperty 'Caption', 'Primary Key - Integer',
> 'user', dbo, 'table', TestExProp, 'column', PriKey
> This statement drops the input-mask properties:
> sp_dropextendedproperty 'Input Mask',
> 'user', dbo, 'table', TestExProp,
> 'column', USSocialScrty
> GO
> sp_dropextendedproperty 'Input Mask',
> 'user', dbo, 'table', TestExProp,
> 'column', USPhoneNmbr
> GO
>
> "Miguel" wrote:
|||Thanks...now that I know where to look, I'll make sure I did not miss them
and see what I can do about getting them back on.
"Miguel" wrote:
[vbcol=seagreen]
> Mike,
> I'm not sure why you got that particular error. I'm assuming you are DBO?
> Extended SPs are stored in th master database, so as long as you have DBO
> rights you should be able to execute the example script below. If you are not
> DBO, I would expect an error similar to "User does not have permission to
> perform this action". Sorry if that didn't fully answer your question.
> - Miguel
> "Mike Collins" wrote:

column descriptions

We are documenting our database by adding column descriptions for each colum
n
in our tables. Where can I find these so I can print all them out together?
Is there a stored procedure that will help?Mike,
You can retrieve the value of an existing extended property using the system
function FN_LISTEXTENDEDPROPERTY.
Also, rather than adding descriptions via Enterprise Manager, I would highly
suggest using:
sp_addextendedproperty
Adds a new extended property to a database object.
sp_updateextendedproperty
Updates the value of an existing extended property.
sp_dropextendedproperty
Drops an existing extended property.
All of this is available on SQL BOL by searching "Extend Properties".
Hope this helps.
- Miguel
"Mike Collins" wrote:

> We are documenting our database by adding column descriptions for each col
umn
> in our tables. Where can I find these so I can print all them out together
?
> Is there a stored procedure that will help?|||Thanks, that sounds good, except I have one question. I hate to ask this,
because I am feeling real dumb, but where are the extended procedures? I
looked up the BOL article "Using Extended Properties on Database Objects" an
d
ran the Northwind example, but for each call to the extended stored
procedures, I got a not found error message in QA. Can you help with this?
Thanks.
Here is the BOL example:
USE Northwind
GO
CREATE TABLE TestExProp
(PriKey int PRIMARY KEY IDENTITY(1,1),
USPhoneNmbr char(13)
CHECK (USPhoneNmbr LIKE
'([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9]
[0-9][0-9]'
),
USSocialScrty char(11)
CHECK (USSocialScrty LIKE
'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9]&
#91;0-9]'
)
)
GO
sp_addextendedproperty 'Caption', 'Caption Test Table',
'user', dbo, 'table', TestExProp
GO
sp_addextendedproperty 'Caption', 'Primary Key',
'user', dbo, 'table', TestExProp, 'column', PriKey
GO
sp_addextendedproperty 'Input Mask', '(NNN)NNN-NNNN',
'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
GO
sp_addextendedproperty 'Caption', 'US Phone Number',
'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
GO
sp_addextendedproperty 'Input Mask', 'NNN-NN-NNNN',
'user', dbo, 'table', TestExProp, 'column',
USSocialScrty
GO
sp_addextendedproperty 'Caption', 'US Social Security Number',
'user', dbo, 'table', TestExProp, 'column',
USSocialScrty
GO
This statement updates the primary-key caption property:
sp_updateextendedproperty 'Caption', 'Primary Key - Integer',
'user', dbo, 'table', TestExProp, 'column', PriKey
This statement drops the input-mask properties:
sp_dropextendedproperty 'Input Mask',
'user', dbo, 'table', TestExProp,
'column', USSocialScrty
GO
sp_dropextendedproperty 'Input Mask',
'user', dbo, 'table', TestExProp,
'column', USPhoneNmbr
GO
"Miguel" wrote:
[vbcol=seagreen]
> Mike,
> You can retrieve the value of an existing extended property using the syst
em
> function FN_LISTEXTENDEDPROPERTY.
> Also, rather than adding descriptions via Enterprise Manager, I would high
ly
> suggest using:
> sp_addextendedproperty
> Adds a new extended property to a database object.
> sp_updateextendedproperty
> Updates the value of an existing extended property.
> sp_dropextendedproperty
> Drops an existing extended property.
> All of this is available on SQL BOL by searching "Extend Properties".
> Hope this helps.
> - Miguel
> "Mike Collins" wrote:
>|||Mike,
I'm not sure why you got that particular error. I'm assuming you are DBO?
Extended SPs are stored in th master database, so as long as you have DBO
rights you should be able to execute the example script below. If you are no
t
DBO, I would expect an error similar to "User does not have permission to
perform this action". Sorry if that didn't fully answer your question.
- Miguel
"Mike Collins" wrote:
[vbcol=seagreen]
> Thanks, that sounds good, except I have one question. I hate to ask this,
> because I am feeling real dumb, but where are the extended procedures? I
> looked up the BOL article "Using Extended Properties on Database Objects"
and
> ran the Northwind example, but for each call to the extended stored
> procedures, I got a not found error message in QA. Can you help with this?
> Thanks.
> Here is the BOL example:
> USE Northwind
> GO
> CREATE TABLE TestExProp
> (PriKey int PRIMARY KEY IDENTITY(1,1),
> USPhoneNmbr char(13)
> CHECK (USPhoneNmbr LIKE
> '([0-9][0-9][0-9])[0-9][0-9][0-9]-&
#91;0-9][0-9][0-9][0-9]'
> ),
> USSocialScrty char(11)
> CHECK (USSocialScrty LIKE
> '[0-9][0-9][0-9]-[0-9][0-9]-[0-9]&#
91;0-9][0-9][0-9]'
> )
> )
> GO
> sp_addextendedproperty 'Caption', 'Caption Test Table',
> 'user', dbo, 'table', TestExProp
> GO
> sp_addextendedproperty 'Caption', 'Primary Key',
> 'user', dbo, 'table', TestExProp, 'column', PriKey
> GO
> sp_addextendedproperty 'Input Mask', '(NNN)NNN-NNNN',
> 'user', dbo, 'table', TestExProp, 'column', USPhone
Nmbr
> GO
> sp_addextendedproperty 'Caption', 'US Phone Number',
> 'user', dbo, 'table', TestExProp, 'column', USPhone
Nmbr
> GO
> sp_addextendedproperty 'Input Mask', 'NNN-NN-NNNN',
> 'user', dbo, 'table', TestExProp, 'column',
> USSocialScrty
> GO
> sp_addextendedproperty 'Caption', 'US Social Security Number',
> 'user', dbo, 'table', TestExProp, 'column',
> USSocialScrty
> GO
> This statement updates the primary-key caption property:
> sp_updateextendedproperty 'Caption', 'Primary Key - Integer',
> 'user', dbo, 'table', TestExProp, 'column', PriKey
> This statement drops the input-mask properties:
> sp_dropextendedproperty 'Input Mask',
> 'user', dbo, 'table', TestExProp,
> 'column', USSocialScrty
> GO
> sp_dropextendedproperty 'Input Mask',
> 'user', dbo, 'table', TestExProp,
> 'column', USPhoneNmbr
> GO
>
> "Miguel" wrote:
>|||Thanks...now that I know where to look, I'll make sure I did not miss them
and see what I can do about getting them back on.
"Miguel" wrote:
[vbcol=seagreen]
> Mike,
> I'm not sure why you got that particular error. I'm assuming you are DBO?
> Extended SPs are stored in th master database, so as long as you have DBO
> rights you should be able to execute the example script below. If you are
not
> DBO, I would expect an error similar to "User does not have permission to
> perform this action". Sorry if that didn't fully answer your question.
> - Miguel
> "Mike Collins" wrote:
>

column descriptions

We are documenting our database by adding column descriptions for each column
in our tables. Where can I find these so I can print all them out together?
Is there a stored procedure that will help?Mike,
You can retrieve the value of an existing extended property using the system
function FN_LISTEXTENDEDPROPERTY.
Also, rather than adding descriptions via Enterprise Manager, I would highly
suggest using:
sp_addextendedproperty
Adds a new extended property to a database object.
sp_updateextendedproperty
Updates the value of an existing extended property.
sp_dropextendedproperty
Drops an existing extended property.
All of this is available on SQL BOL by searching "Extend Properties".
Hope this helps.
- Miguel
"Mike Collins" wrote:
> We are documenting our database by adding column descriptions for each column
> in our tables. Where can I find these so I can print all them out together?
> Is there a stored procedure that will help?|||Thanks, that sounds good, except I have one question. I hate to ask this,
because I am feeling real dumb, but where are the extended procedures? I
looked up the BOL article "Using Extended Properties on Database Objects" and
ran the Northwind example, but for each call to the extended stored
procedures, I got a not found error message in QA. Can you help with this?
Thanks.
Here is the BOL example:
USE Northwind
GO
CREATE TABLE TestExProp
(PriKey int PRIMARY KEY IDENTITY(1,1),
USPhoneNmbr char(13)
CHECK (USPhoneNmbr LIKE
'([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
),
USSocialScrty char(11)
CHECK (USSocialScrty LIKE
'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
)
)
GO
sp_addextendedproperty 'Caption', 'Caption Test Table',
'user', dbo, 'table', TestExProp
GO
sp_addextendedproperty 'Caption', 'Primary Key',
'user', dbo, 'table', TestExProp, 'column', PriKey
GO
sp_addextendedproperty 'Input Mask', '(NNN)NNN-NNNN',
'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
GO
sp_addextendedproperty 'Caption', 'US Phone Number',
'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
GO
sp_addextendedproperty 'Input Mask', 'NNN-NN-NNNN',
'user', dbo, 'table', TestExProp, 'column',
USSocialScrty
GO
sp_addextendedproperty 'Caption', 'US Social Security Number',
'user', dbo, 'table', TestExProp, 'column',
USSocialScrty
GO
This statement updates the primary-key caption property:
sp_updateextendedproperty 'Caption', 'Primary Key - Integer',
'user', dbo, 'table', TestExProp, 'column', PriKey
This statement drops the input-mask properties:
sp_dropextendedproperty 'Input Mask',
'user', dbo, 'table', TestExProp,
'column', USSocialScrty
GO
sp_dropextendedproperty 'Input Mask',
'user', dbo, 'table', TestExProp,
'column', USPhoneNmbr
GO
"Miguel" wrote:
> Mike,
> You can retrieve the value of an existing extended property using the system
> function FN_LISTEXTENDEDPROPERTY.
> Also, rather than adding descriptions via Enterprise Manager, I would highly
> suggest using:
> sp_addextendedproperty
> Adds a new extended property to a database object.
> sp_updateextendedproperty
> Updates the value of an existing extended property.
> sp_dropextendedproperty
> Drops an existing extended property.
> All of this is available on SQL BOL by searching "Extend Properties".
> Hope this helps.
> - Miguel
> "Mike Collins" wrote:
> > We are documenting our database by adding column descriptions for each column
> > in our tables. Where can I find these so I can print all them out together?
> > Is there a stored procedure that will help?|||Mike,
I'm not sure why you got that particular error. I'm assuming you are DBO?
Extended SPs are stored in th master database, so as long as you have DBO
rights you should be able to execute the example script below. If you are not
DBO, I would expect an error similar to "User does not have permission to
perform this action". Sorry if that didn't fully answer your question.
- Miguel
"Mike Collins" wrote:
> Thanks, that sounds good, except I have one question. I hate to ask this,
> because I am feeling real dumb, but where are the extended procedures? I
> looked up the BOL article "Using Extended Properties on Database Objects" and
> ran the Northwind example, but for each call to the extended stored
> procedures, I got a not found error message in QA. Can you help with this?
> Thanks.
> Here is the BOL example:
> USE Northwind
> GO
> CREATE TABLE TestExProp
> (PriKey int PRIMARY KEY IDENTITY(1,1),
> USPhoneNmbr char(13)
> CHECK (USPhoneNmbr LIKE
> '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
> ),
> USSocialScrty char(11)
> CHECK (USSocialScrty LIKE
> '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
> )
> )
> GO
> sp_addextendedproperty 'Caption', 'Caption Test Table',
> 'user', dbo, 'table', TestExProp
> GO
> sp_addextendedproperty 'Caption', 'Primary Key',
> 'user', dbo, 'table', TestExProp, 'column', PriKey
> GO
> sp_addextendedproperty 'Input Mask', '(NNN)NNN-NNNN',
> 'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
> GO
> sp_addextendedproperty 'Caption', 'US Phone Number',
> 'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
> GO
> sp_addextendedproperty 'Input Mask', 'NNN-NN-NNNN',
> 'user', dbo, 'table', TestExProp, 'column',
> USSocialScrty
> GO
> sp_addextendedproperty 'Caption', 'US Social Security Number',
> 'user', dbo, 'table', TestExProp, 'column',
> USSocialScrty
> GO
> This statement updates the primary-key caption property:
> sp_updateextendedproperty 'Caption', 'Primary Key - Integer',
> 'user', dbo, 'table', TestExProp, 'column', PriKey
> This statement drops the input-mask properties:
> sp_dropextendedproperty 'Input Mask',
> 'user', dbo, 'table', TestExProp,
> 'column', USSocialScrty
> GO
> sp_dropextendedproperty 'Input Mask',
> 'user', dbo, 'table', TestExProp,
> 'column', USPhoneNmbr
> GO
>
> "Miguel" wrote:
> > Mike,
> >
> > You can retrieve the value of an existing extended property using the system
> > function FN_LISTEXTENDEDPROPERTY.
> >
> > Also, rather than adding descriptions via Enterprise Manager, I would highly
> > suggest using:
> >
> > sp_addextendedproperty
> > Adds a new extended property to a database object.
> >
> > sp_updateextendedproperty
> > Updates the value of an existing extended property.
> >
> > sp_dropextendedproperty
> > Drops an existing extended property.
> >
> > All of this is available on SQL BOL by searching "Extend Properties".
> >
> > Hope this helps.
> >
> > - Miguel
> >
> > "Mike Collins" wrote:
> >
> > > We are documenting our database by adding column descriptions for each column
> > > in our tables. Where can I find these so I can print all them out together?
> > > Is there a stored procedure that will help?|||Thanks...now that I know where to look, I'll make sure I did not miss them
and see what I can do about getting them back on.
"Miguel" wrote:
> Mike,
> I'm not sure why you got that particular error. I'm assuming you are DBO?
> Extended SPs are stored in th master database, so as long as you have DBO
> rights you should be able to execute the example script below. If you are not
> DBO, I would expect an error similar to "User does not have permission to
> perform this action". Sorry if that didn't fully answer your question.
> - Miguel
> "Mike Collins" wrote:
> > Thanks, that sounds good, except I have one question. I hate to ask this,
> > because I am feeling real dumb, but where are the extended procedures? I
> > looked up the BOL article "Using Extended Properties on Database Objects" and
> > ran the Northwind example, but for each call to the extended stored
> > procedures, I got a not found error message in QA. Can you help with this?
> > Thanks.
> >
> > Here is the BOL example:
> > USE Northwind
> > GO
> > CREATE TABLE TestExProp
> > (PriKey int PRIMARY KEY IDENTITY(1,1),
> > USPhoneNmbr char(13)
> > CHECK (USPhoneNmbr LIKE
> > '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
> > ),
> > USSocialScrty char(11)
> > CHECK (USSocialScrty LIKE
> > '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
> > )
> > )
> > GO
> > sp_addextendedproperty 'Caption', 'Caption Test Table',
> > 'user', dbo, 'table', TestExProp
> > GO
> > sp_addextendedproperty 'Caption', 'Primary Key',
> > 'user', dbo, 'table', TestExProp, 'column', PriKey
> > GO
> > sp_addextendedproperty 'Input Mask', '(NNN)NNN-NNNN',
> > 'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
> > GO
> > sp_addextendedproperty 'Caption', 'US Phone Number',
> > 'user', dbo, 'table', TestExProp, 'column', USPhoneNmbr
> > GO
> > sp_addextendedproperty 'Input Mask', 'NNN-NN-NNNN',
> > 'user', dbo, 'table', TestExProp, 'column',
> > USSocialScrty
> > GO
> > sp_addextendedproperty 'Caption', 'US Social Security Number',
> > 'user', dbo, 'table', TestExProp, 'column',
> > USSocialScrty
> > GO
> >
> > This statement updates the primary-key caption property:
> >
> > sp_updateextendedproperty 'Caption', 'Primary Key - Integer',
> > 'user', dbo, 'table', TestExProp, 'column', PriKey
> >
> > This statement drops the input-mask properties:
> >
> > sp_dropextendedproperty 'Input Mask',
> > 'user', dbo, 'table', TestExProp,
> > 'column', USSocialScrty
> > GO
> > sp_dropextendedproperty 'Input Mask',
> > 'user', dbo, 'table', TestExProp,
> > 'column', USPhoneNmbr
> > GO
> >
> >
> >
> > "Miguel" wrote:
> >
> > > Mike,
> > >
> > > You can retrieve the value of an existing extended property using the system
> > > function FN_LISTEXTENDEDPROPERTY.
> > >
> > > Also, rather than adding descriptions via Enterprise Manager, I would highly
> > > suggest using:
> > >
> > > sp_addextendedproperty
> > > Adds a new extended property to a database object.
> > >
> > > sp_updateextendedproperty
> > > Updates the value of an existing extended property.
> > >
> > > sp_dropextendedproperty
> > > Drops an existing extended property.
> > >
> > > All of this is available on SQL BOL by searching "Extend Properties".
> > >
> > > Hope this helps.
> > >
> > > - Miguel
> > >
> > > "Mike Collins" wrote:
> > >
> > > > We are documenting our database by adding column descriptions for each column
> > > > in our tables. Where can I find these so I can print all them out together?
> > > > Is there a stored procedure that will help?