Combining reports

I want to combine several reports into a single report to print out.
I want the correct page numbering on each of the individual reports so
I don't want to use subreports on a main report. Is there a way to
combine reports into a single report to allow the user to print
without having to print out each of the individual reports?On Apr 25, 8:03 pm, jwchoi...@.gmail.com wrote:
> I want to combine several reports into a single report to print out.
> I want the correct page numbering on each of the individual reports so
> I don't want to use subreports on a main report. Is there a way to
> combine reports into a single report to allow the user to print
> without having to print out each of the individual reports?
The only thing I can think of is to create a single report that has
all the controls of each report (i.e., add x number of table controls
to a single report for x number of reports). Sorry that I could not be
of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Combining multiple reports into one print job

I have a vb program that uses many different crystal reports. They are all slightly complex and have subreports already in them. When the user wantes to print the whole project it comes out as 10 different print jobs. I would like to make it one print job or append consecutive reports together to create one report (programaticaly). I would appreciate any help. Thank you.Did you ever get a solution to this issue? I am trying to do a similar thing. If so could you send me some info.

Thanks.sqlsql

Column order/presentation in virtual table (result set from viewor UDF)

I was just messing around with some ad hoc views and table returning
UDFs today so I could look at and print out data from a small table
and noticed something strange.

If I stick my select statement into a View the columns are returned in
the order I specify in the SELECT, but if the same statement is in a UDF
(so I can specify a parameter), the columns are not returned in the
order specified in statement.

I know that relations don't have a specified column order, but it was my
understanding that a SELECT statement could be used to define how you
want your data presented. Views seem to respect the order specified in
the SELECT, but functions don't.

What am I missing? Is there some way to force the order of the columns
returned from a SELECT?

View:

CREATE VIEW dbo.View1
AS
SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,
ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE '4.2.%')

Column order from this view:
Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,
ElseStmt, NextStmt

Function:

ALTER FUNCTION dbo.Function1
(@.SearchPrm varchar(255))
RETURNS TABLE
AS
RETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,
ChildStmt, ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE @.SearchPrm) )

Column order from this function:
Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,
ParentStmt, ForStmt

Table:
(I know that this table isn't entirely normalized, but it serves my
purposes to have a matrix instead of a fully normalized relation):

CREATE TABLE dbo.tblStmt (
StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,
Ident VARCHAR(255),
Text TEXT,
ErrorText TEXT,
Type INT,
ParentStmt VARCHAR(255),
ChildStmt VARCHAR(255),
IfStmt VARCHAR(255),
ForStmt VARCHAR(255),
ThenStmt VARCHAR(255),
ElseStmt VARCHAR(255),
NextStmt VARCHAR(255),
FullName VARCHAR(255),
LocalName VARCHAR(255),
Method INT
)

INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')

INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')

INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')

INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')I forgot to mention an important detail. I'm creating the VIEW and
FUNCTION within the context of a Microsoft Access ADP file that's
pointed at the SQL Server 2000 database in question.

If I execute this statement in Query Analyzer:
SELECT * FROM dbo.Function1('4.2.%')
the columns are output as specified in the column list inside the
Function definition (Ident, Text, ..., etc.).

If I double-click on the Function's object in MS Access and enter 4.2.%
in the prompt for the parameter, then the column list is output in the
strange order as noted below (Type, Text, ElseStmt, ..., etc.).

So, this may actually be a Microsoft Access problem, but if anyone has
any information, I'd appreciate it. Thanks.

Beowulf wrote:

Quote:

Originally Posted by

I was just messing around with some ad hoc views and table returning
UDFs today so I could look at and print out data from a small table
and noticed something strange.
>
If I stick my select statement into a View the columns are returned in
the order I specify in the SELECT, but if the same statement is in a UDF
(so I can specify a parameter), the columns are not returned in the
order specified in statement.
>
I know that relations don't have a specified column order, but it was my
understanding that a SELECT statement could be used to define how you
want your data presented. Views seem to respect the order specified in
the SELECT, but functions don't.
>
What am I missing? Is there some way to force the order of the columns
returned from a SELECT?
>
View:
>
CREATE VIEW dbo.View1
AS
SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,
ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE '4.2.%')
>
Column order from this view:
Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,
ElseStmt, NextStmt
>
Function:
>
ALTER FUNCTION dbo.Function1
(@.SearchPrm varchar(255))
RETURNS TABLE
AS
RETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,
ChildStmt, ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE @.SearchPrm) )
>
Column order from this function:
Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,
ParentStmt, ForStmt
>
Table:
(I know that this table isn't entirely normalized, but it serves my
purposes to have a matrix instead of a fully normalized relation):
>
CREATE TABLE dbo.tblStmt (
StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,
Ident VARCHAR(255),
Text TEXT,
ErrorText TEXT,
Type INT,
ParentStmt VARCHAR(255),
ChildStmt VARCHAR(255),
IfStmt VARCHAR(255),
ForStmt VARCHAR(255),
ThenStmt VARCHAR(255),
ElseStmt VARCHAR(255),
NextStmt VARCHAR(255),
FullName VARCHAR(255),
LocalName VARCHAR(255),
Method INT
)
>
INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')
>
INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')
>
INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')
>
INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')

|||It is indeed an MS Access problem (specific to ADP-s). You can reorder
the columns in the resulting datasheet freely and Access remembers the
position and width of the columns when you open the table/view/function
again. Access usually asks "Do you want to save changes to the layout
of function '...' ?". If you respond "yes", it stores this information
in the database, using extended properties for the objects and for the
columns.

Razvan|||Razvan Socol wrote:

Quote:

Originally Posted by

It is indeed an MS Access problem (specific to ADP-s). You can reorder
the columns in the resulting datasheet freely and Access remembers the
position and width of the columns when you open the table/view/function
again. Access usually asks "Do you want to save changes to the layout
of function '...' ?". If you respond "yes", it stores this information
in the database, using extended properties for the objects and for the
columns.

Thanks for the reply, even though the question turned out to be off-topic.

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?

Colour My World

In SQL Server 2000 Query Analyzer, is there a way to print T-SQL code with
the QA color coding? When I print, even to a color laser printer, everything
including the keywords and comments come out in black and white.
Thanks!>> In SQL Server 2000 Query Analyzer, is there a way to print T-SQL code
Not sure if direct printing is possible or not. This might be silly but if
the code is small enough to fit the screen, you could use a screen print to
a word document and then print it.
Anith|||>> When I print, even to a color laser printer, everything
including the keywords and comments come out in black and white. <<
Just for fun, you might want to research the effect of "colored code"
on maintaining it. There is a standard test for brain damage where you
show the subject a seires of flashcards with the names of colors in
colored ink (I.e. "RED" pritned in green ink) and ask them to call out
the either word or the color. This has nothing to do with being
color-blind.
Since it involves switching brain hemispheres and thus the physical
structure of the brain as a organ, the rate is fairly constant over a
person's lifetime. Unless they get some physical damage to the brain.
Strongly analytical ("left brain, right hand") people have an awful
time with "neon vomit programming tools" because they have to filter
out the colors to abstract the code from the text.
So, I have to ask, why would you ever want to do this? Get a copy of
SQL PROGRAMMING STYLE for some more info on how humans read code. I
did a bit of the work on this back in the 1980's for AIRMICS while I
was a Georgia Tech.|||Well, cut-n-paste it into MS Word - that should do the trick ;)|||Screen capture could work, but the code is many, many pages long.
Is this possible in SQL Server? If not , are there any 3rd party programs
that might work?
Thanks again...
"Anith Sen" wrote:

> Not sure if direct printing is possible or not. This might be silly but if
> the code is small enough to fit the screen, you could use a screen print t
o
> a word document and then print it.
> --
> Anith
>
>|||Nope, tried it. Still black-and-white in MS Word.
"Alexander Kuznetsov" wrote:

> Well, cut-n-paste it into MS Word - that should do the trick ;)
>|||Why would I want color-coded printouts? for the same reason they're
color-coded on the screen. Easier to separate what's code, what's keywords,
and what's comments.
As far as the brain damage research, although it sounds rather intriguing,
considering how many more years of my life I'm going to spend looking at M&M
colored typing, I may be better off not knowing...
"--CELKO--" wrote:

> including the keywords and comments come out in black and white. <<
> Just for fun, you might want to research the effect of "colored code"
> on maintaining it. There is a standard test for brain damage where you
> show the subject a seires of flashcards with the names of colors in
> colored ink (I.e. "RED" pritned in green ink) and ask them to call out
> the either word or the color. This has nothing to do with being
> color-blind.
> Since it involves switching brain hemispheres and thus the physical
> structure of the brain as a organ, the rate is fairly constant over a
> person's lifetime. Unless they get some physical damage to the brain.
>
> Strongly analytical ("left brain, right hand") people have an awful
> time with "neon vomit programming tools" because they have to filter
> out the colors to abstract the code from the text.
> So, I have to ask, why would you ever want to do this? Get a copy of
> SQL PROGRAMMING STYLE for some more info on how humans read code. I
> did a bit of the work on this back in the 1980's for AIRMICS while I
> was a Georgia Tech.
>|||On Fri, 10 Feb 2006 08:51:29 -0800, "Joel"
<Joel@.discussions.microsoft.com> wrote:

>In SQL Server 2000 Query Analyzer, is there a way to print T-SQL code with
>the QA color coding? When I print, even to a color laser printer, everythin
g
>including the keywords and comments come out in black and white.
>Thanks!
I use Macromedia's Homesite. You can set the colors for each entity.
What you see on the screen is what prints. I also use Ultra Edit and
Slick Edit. Both of these allow you to print a selection where
Homesite will only print the entire file. I like the printout from
Homesite best.
--
BettyB -- www.flamingo-code.com
"I have noticed even people who claim everything is
predestined, and that we can do nothing to change it,
look before they cross the road." - Stephen Hawking|||I don't know if it is an option or not, but the 2005 replacement of QA (Mana
gement Studio) does keep
formatting (including colour) when you copy text. Personally, I hate that, b
ut it might be an upside
in these situations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:F6077A67-C933-47CA-A4C5-654D2E7881A8@.microsoft.com...
> Nope, tried it. Still black-and-white in MS Word.
> "Alexander Kuznetsov" wrote:
>|||> Personally, I hate that,
me too. Sometimes I need to paste into notepad - that removes colors
and fonts