Database image retrieval causes datasource credential prompt

I have an image stored in a database that I use on a report with other non image data. When I run the report, the non image data is displayed, followed by a prompt for me to enter datasource credentials. None of the images are downloaded until I enter the credentials. If I don't enter the creds, the images aren't downloaded and appear only as the red x, file not found icon. I don't understand why I'm getting the prompt because I've already passed the exact set of credentials that the dialog expects to the web service with my call to the render method. THIS IS SO FRUSTRATING! I am experiencing this on both staging and production servers, but not on my development machine. I'm using reporting services service pack 1 on staging and production.

Thanks in advance for the help,
Kim

I guess the answer depends on what export format you're obtaining from the report server. If you're asking for HTML output, then the Images are not included in the HTML output. When the browser displays the HTML output, it will follow the links to each image and after a successful authentication, it will retrieve the images.

If you use the report viewer controls in your application rather than the SOAP api directly, it will probably alleviate this problem.

-Lukasz

sql

Database image retrieval causes datasource credential prompt

I have an image stored in a database that I use on a report with other non image data. When I run the report, the non image data is displayed, followed by a prompt for me to enter datasource credentials. None of the images are downloaded until I enter the credentials. If I don't enter the creds, the images aren't downloaded and appear only as the red x, file not found icon. I don't understand why I'm getting the prompt because I've already passed the exact set of credentials that the dialog expects to the web service with my call to the render method. THIS IS SO FRUSTRATING! I am experiencing this on both staging and production servers, but not on my development machine. I'm using reporting services service pack 1 on staging and production.

Thanks in advance for the help,
Kim

I guess the answer depends on what export format you're obtaining from the report server. If you're asking for HTML output, then the Images are not included in the HTML output. When the browser displays the HTML output, it will follow the links to each image and after a successful authentication, it will retrieve the images.

If you use the report viewer controls in your application rather than the SOAP api directly, it will probably alleviate this problem.

-Lukasz

Database image on report

Hi,

How to display image stored in the database into sql server report?

Please guide.

Thank you,

Regards,

Prashant

Please read this RS BOL topic: http://msdn2.microsoft.com/en-us/library/ms156342(SQL.90).aspx

Particularly read the comment near the top if the images came originally from an Access database, because in that case the actual image data is an OLE image and you have to write an expression to remove the OLE image header from the data.

-- Robert

Database image not displaying

Hi,
In my report I want to dispaly a different image based on the parameter user
enters. For ex., the user enters a company name , then the image for that
company should be displayed.
Now in my database, the images are not stored using the complete path or a
complete URL, but are stored as Logo1.gif , Logo2.gif etc in the field IMAGE.
I have a virtual directory which contains these images.
Now when i create a databse image, an pull from the field IMAGE, the image
is not displayed. how to solve this problem? Is it necessary to install SP1
for this purpose too?
--
pmudYou are actually not using "Database" images here. You are using "External"
images from some webserver. It is just that the image path is dynamically
constructed from a dataset field, right?
In this case, you will need SP1 (or SP2) installed. You would need to set
the image type to "External" and the image value expression to something
like
="http://myserver/vroot/" & Fields!RelativeImagePath.Value
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:6B54439E-15CE-4769-A88A-5A58CCD287E4@.microsoft.com...
> Hi,
> In my report I want to dispaly a different image based on the parameter
> user
> enters. For ex., the user enters a company name , then the image for that
> company should be displayed.
> Now in my database, the images are not stored using the complete path or a
> complete URL, but are stored as Logo1.gif , Logo2.gif etc in the field
> IMAGE.
> I have a virtual directory which contains these images.
> Now when i create a databse image, an pull from the field IMAGE, the
> image
> is not displayed. how to solve this problem? Is it necessary to install
> SP1
> for this purpose too?
> --
> pmud|||HI Robert,
I understand now. I will install SP1 & try to do the external images.
Thanks..
--
pmud
"Robert Bruckner [MSFT]" wrote:
> You are actually not using "Database" images here. You are using "External"
> images from some webserver. It is just that the image path is dynamically
> constructed from a dataset field, right?
> In this case, you will need SP1 (or SP2) installed. You would need to set
> the image type to "External" and the image value expression to something
> like
> ="http://myserver/vroot/" & Fields!RelativeImagePath.Value
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:6B54439E-15CE-4769-A88A-5A58CCD287E4@.microsoft.com...
> > Hi,
> >
> > In my report I want to dispaly a different image based on the parameter
> > user
> > enters. For ex., the user enters a company name , then the image for that
> > company should be displayed.
> >
> > Now in my database, the images are not stored using the complete path or a
> > complete URL, but are stored as Logo1.gif , Logo2.gif etc in the field
> > IMAGE.
> > I have a virtual directory which contains these images.
> >
> > Now when i create a databse image, an pull from the field IMAGE, the
> > image
> > is not displayed. how to solve this problem? Is it necessary to install
> > SP1
> > for this purpose too?
> > --
> > pmud
>
>|||Hi Robert,
I installed SP1 and put the image as an external image. All my images are
stored in C:/Inetpub/wwwroot/IMAGES . this folder has 2 subfolders which have
different images. What value should I use for generating the dynamic image
URL? My server name is WEBSERVER and the image field is =Fields!LOGO.Value.
Thanks
--
pmud
"pmud" wrote:
> HI Robert,
> I understand now. I will install SP1 & try to do the external images.
> Thanks..
> --
> pmud
>
> "Robert Bruckner [MSFT]" wrote:
> > You are actually not using "Database" images here. You are using "External"
> > images from some webserver. It is just that the image path is dynamically
> > constructed from a dataset field, right?
> >
> > In this case, you will need SP1 (or SP2) installed. You would need to set
> > the image type to "External" and the image value expression to something
> > like
> > ="http://myserver/vroot/" & Fields!RelativeImagePath.Value
> >
> >
> > -- Robert
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "pmud" <pmud@.discussions.microsoft.com> wrote in message
> > news:6B54439E-15CE-4769-A88A-5A58CCD287E4@.microsoft.com...
> > > Hi,
> > >
> > > In my report I want to dispaly a different image based on the parameter
> > > user
> > > enters. For ex., the user enters a company name , then the image for that
> > > company should be displayed.
> > >
> > > Now in my database, the images are not stored using the complete path or a
> > > complete URL, but are stored as Logo1.gif , Logo2.gif etc in the field
> > > IMAGE.
> > > I have a virtual directory which contains these images.
> > >
> > > Now when i create a databse image, an pull from the field IMAGE, the
> > > image
> > > is not displayed. how to solve this problem? Is it necessary to install
> > > SP1
> > > for this purpose too?
> > > --
> > > pmud
> >
> >
> >|||Assuming you have a virtual root defined in IIS called "Images", the URL
should be similar to this:
http://WebServer/Images/Subfolder/ImageName.gif
Make sure you can access the images through IE, and then just build the same
URL using an expression for the image reportitem value. For instance
="http://WebServer/Images/Subfolder/" & Fields!LOGO.Value
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:CA2277F0-DCEF-479D-BA70-6B9527B50B21@.microsoft.com...
> Hi Robert,
> I installed SP1 and put the image as an external image. All my images are
> stored in C:/Inetpub/wwwroot/IMAGES . this folder has 2 subfolders which
> have
> different images. What value should I use for generating the dynamic image
> URL? My server name is WEBSERVER and the image field is
> =Fields!LOGO.Value.
> Thanks
> --
> pmud
>
> "pmud" wrote:
>> HI Robert,
>> I understand now. I will install SP1 & try to do the external images.
>> Thanks..
>> --
>> pmud
>>
>> "Robert Bruckner [MSFT]" wrote:
>> > You are actually not using "Database" images here. You are using
>> > "External"
>> > images from some webserver. It is just that the image path is
>> > dynamically
>> > constructed from a dataset field, right?
>> >
>> > In this case, you will need SP1 (or SP2) installed. You would need to
>> > set
>> > the image type to "External" and the image value expression to
>> > something
>> > like
>> > ="http://myserver/vroot/" & Fields!RelativeImagePath.Value
>> >
>> >
>> > -- Robert
>> > This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >
>> >
>> > "pmud" <pmud@.discussions.microsoft.com> wrote in message
>> > news:6B54439E-15CE-4769-A88A-5A58CCD287E4@.microsoft.com...
>> > > Hi,
>> > >
>> > > In my report I want to dispaly a different image based on the
>> > > parameter
>> > > user
>> > > enters. For ex., the user enters a company name , then the image for
>> > > that
>> > > company should be displayed.
>> > >
>> > > Now in my database, the images are not stored using the complete path
>> > > or a
>> > > complete URL, but are stored as Logo1.gif , Logo2.gif etc in the
>> > > field
>> > > IMAGE.
>> > > I have a virtual directory which contains these images.
>> > >
>> > > Now when i create a databse image, an pull from the field IMAGE, the
>> > > image
>> > > is not displayed. how to solve this problem? Is it necessary to
>> > > install
>> > > SP1
>> > > for this purpose too?
>> > > --
>> > > pmud
>> >
>> >
>> >

Database files free space (SQL Server 2005)

Dear friends

I need to report the amount of free space in each datafile of a database.

I have tried sys.dm_db_file_space_usage , but the documentation says that it only works for the TempDB.

Please help.

Regards

Parviz

Take a look at the sp_spaceused text. You should be able to derive from there.

exec sp_helptext 'sp_spaceused'|||

Thanks for the fast reply, but please note that sp_spaceused returns the free space for the whole database files including the LOG file. I need the info fo each file in a database seperately.

If you have a look at summary reports for a database (SQL 2005 SSMS),you would see a table and a pie chart showng the status for each file.

Thanks again.

Parviz

|||Yup. sp_spacedused can be used to get the data file usage. You can then use 'dbcc sqlperf(logspace)' to get the log usage.|||

Here's some code, cribbed from a sqldbatips report designed to mimic the taskpad in SSMS. (http://sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/21/custom-ssms-reports-in-sp2-enterprise-manager-taskpad-view.aspx). All I did was add some code to show the amount free. Seems to work well. Posting here because this was one of the top results in Google when I went looking for code myself.

create table #data(Fileid int NOT NULL,

[FileGroup] int NOT NULL,

TotalExtents int NOT NULL,

UsedExtents int NOT NULL,

[Name] sysname NOT NULL,

[FileName] varchar(300) NOT NULL)

create table #log(dbname sysname NOT NULL,

LogSize numeric(15,7) NOT NULL,

LogUsed numeric(9,5) NOT NULL,

Status int NOT NULL)

insert #data exec('DBCC showfilestats with no_infomsgs')

insert #log exec('dbcc sqlperf(logspace) with no_infomsgs')

select [type], [name], totalmb, usedmb, totalmb - usedmb as EmptySpace from

(

select 'DATA' as [Type],[Name],(TotalExtents*64)/1024.0 as [TotalMB],(UsedExtents*64)/1024.0 as [UsedMB]

from #data

union all

select 'LOG',db_name()+' LOG',LogSize,((LogUsed/100)*LogSize) from #log where dbname = db_name()

--order by [Type],[Name]

)a

order by [Type],[Name]

drop table #data

drop table #log

|||

This will work for you:

-- Individual File Size query

SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *

FROM sys.database_files;

Database files free space (SQL Server 2005)

Dear friends

I need to report the amount of free space in each datafile of a database.

I have tried sys.dm_db_file_space_usage , but the documentation says that it only works for the TempDB.

Please help.

Regards

Parviz

Take a look at the sp_spaceused text. You should be able to derive from there.

exec sp_helptext 'sp_spaceused'|||

Thanks for the fast reply, but please note that sp_spaceused returns the free space for the whole database files including the LOG file. I need the info fo each file in a database seperately.

If you have a look at summary reports for a database (SQL 2005 SSMS),you would see a table and a pie chart showng the status for each file.

Thanks again.

Parviz

|||Yup. sp_spacedused can be used to get the data file usage. You can then use 'dbcc sqlperf(logspace)' to get the log usage.|||

Here's some code, cribbed from a sqldbatips report designed to mimic the taskpad in SSMS. (http://sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/21/custom-ssms-reports-in-sp2-enterprise-manager-taskpad-view.aspx). All I did was add some code to show the amount free. Seems to work well. Posting here because this was one of the top results in Google when I went looking for code myself.

create table #data(Fileid int NOT NULL,

[FileGroup] int NOT NULL,

TotalExtents int NOT NULL,

UsedExtents int NOT NULL,

[Name] sysname NOT NULL,

[FileName] varchar(300) NOT NULL)

create table #log(dbname sysname NOT NULL,

LogSize numeric(15,7) NOT NULL,

LogUsed numeric(9,5) NOT NULL,

Status int NOT NULL)

insert #data exec('DBCC showfilestats with no_infomsgs')

insert #log exec('dbcc sqlperf(logspace) with no_infomsgs')

select [type], [name], totalmb, usedmb, totalmb - usedmb as EmptySpace from

(

select 'DATA' as [Type],[Name],(TotalExtents*64)/1024.0 as [TotalMB],(UsedExtents*64)/1024.0 as [UsedMB]

from #data

union all

select 'LOG',db_name()+' LOG',LogSize,((LogUsed/100)*LogSize) from #log where dbname = db_name()

--order by [Type],[Name]

)a

order by [Type],[Name]

drop table #data

drop table #log

|||

This will work for you:

-- Individual File Size query

SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB', *

FROM sys.database_files;

Database Field showing as zero in report footer but works in group footer

I'm fairly new to crystal so there may be an obvious solution but I just can't find it. So any help what-so-ever would be excellent and much appreciated...

Problem:

Database field shows correct value and works in formulas properly in group footer.
In Report footer this same database field shows as zero and therefore other formulas in the report footer do not calculate correctly.

Any solutions?Please. Any help would be greatly appreciated. I'm kind of in a time crunch.

I'm not sure why a database field would show the value in the group footer and come up zero in the report footer.

Any ideas??|||Sorry, don't know why either - the report footer always shows the final database record for me!
I have read of other people having this problem, and I believe that assigning the fiele to a shared variable has allowed the value to be used.|||I gave it a variable and now it works. How weird is that?!

Thanks for your help!sql

database error

I'm using 8.5. Have a report that runs via Latitude, custom db sw for the collections industry.

I have a report that I have to add a table to. Which I did, needed to set the location of the table that was there. Once onboard, I verify database and come up smelling like a rose. However when I run the report I get...

ODBC errror: [Microsoft] [ODBC SQL Server Driver][SQL Server]The column prefix 'tablename' does not match with a table name or alias name used in the query.

Followed by a Error detected by database DLL.

Make sense to anyone? Many thanks in advance for input...is the name of your table 'tablename'? Or is that just a substitution? I'm not by any means an expert with CR - but it sounds like something is wrong with your query. If you know anything about SQL Queries - go to Database>Show SQL Query and check it out. Paste it in here if you're still having problems.

Database DLL

Hope someone here can help.
When I set up a Crystal report to an application , I got an application
error like this:
****Error : 532 Cannot find databse DLL
****Execute<PEStartPrintJob>
Is ths related to different version of Crystal or SQL server?
Thanks
Catelin
I have some ugly memories about that...532 Cannot find
database DLL is a Crystal error. There should be several (if
not more) articles on their support web site about the
error.
-Sue
On Wed, 26 Jan 2005 16:40:30 -0600, "Catelin wang"
<cwang@.cemstone.com> wrote:

>Hope someone here can help.
>When I set up a Crystal report to an application , I got an application
>error like this:
>****Error : 532 Cannot find databse DLL
>****Execute<PEStartPrintJob>
>Is ths related to different version of Crystal or SQL server?
>
>Thanks
>Catelin
>

Database DLL

Hope someone here can help.
When I set up a Crystal report to an application , I got an application
error like this:
****Error : 532 Cannot find databse DLL
****Execute<PEStartPrintJob>
Is ths related to different version of Crystal or SQL server?
Thanks
CatelinI have some ugly memories about that...532 Cannot find
database DLL is a Crystal error. There should be several (if
not more) articles on their support web site about the
error.
-Sue
On Wed, 26 Jan 2005 16:40:30 -0600, "Catelin wang"
<cwang@.cemstone.com> wrote:

>Hope someone here can help.
>When I set up a Crystal report to an application , I got an application
>error like this:
>****Error : 532 Cannot find databse DLL
>****Execute<PEStartPrintJob>
>Is ths related to different version of Crystal or SQL server?
>
>Thanks
>Catelin
>

Database DLL

Hope someone here can help.
When I set up a Crystal report to an application , I got an application
error like this:
****Error : 532 Cannot find databse DLL
****Execute<PEStartPrintJob>
Is ths related to different version of Crystal or SQL server?
Thanks
CatelinI have some ugly memories about that...532 Cannot find
database DLL is a Crystal error. There should be several (if
not more) articles on their support web site about the
error.
-Sue
On Wed, 26 Jan 2005 16:40:30 -0600, "Catelin wang"
<cwang@.cemstone.com> wrote:
>Hope someone here can help.
>When I set up a Crystal report to an application , I got an application
>error like this:
>****Error : 532 Cannot find databse DLL
>****Execute<PEStartPrintJob>
>Is ths related to different version of Crystal or SQL server?
>
>Thanks
>Catelin
>

Database Diagram + Reporting Services/Report Builder

If I make a new database, why is it that VS 2005 server explorer can see it, access the tables and stored procs, etc ... but when using Reporting Services/Report Builder after I make the data source and generate the model NOTHING APPEARS in Report Builder (message about "there is no content in this data source")..

now this has been driving me nuts for a few days and the solution was to click on "DataBase Diagram" in Sql Managment studio for that DB and say "Yes" to the dialog that appears?

My question is why is that the case?

Carl

Ping Microsoft.

Is this a bug?

Carl

Database Design Tool

I've been desiging a database.

From a previous job, I've seen a report that runs off the database management tool in SQL Server 2005 that prints out comments entered at the table and column level. I can't find that report on my current system.

I wondered if this is a feature of the tool when the tool is installed as a client rather than the tool locally on the server, as part of the server install.

Any help is appreciated.

Andrew

Hi Andrew,
Just a guess but maybe you are talking about extended properties?
From the context menu of a table or column you will find a "page" called Extended Properties for that table or column. Should work the same regardless of whether you are on a local SSMS or a remote SSMS. I believe you can also script only the extended properties of all or selected objects if that is interesting. Note that extended properties are optional and must be provided by developer. Otherwise they are blank.
Regards,
Bill
|||

I've found a comment property for both columns and tables. I don't recall going to an 'extended' property page. The comment property at the table level appears on the regular list of table properties at the right of the design tool. To put a comment on a column, I've used the column modify method. I right click on the column and pick 'modify'.

Is the script you refer to something I have to write, or is there an existing script in the tool.

I wasn't paying close enough attention, but I think the output from the report I saw was a Word Doc.

Andrew

|||

There is not anything built in that will generate a word doc but I am sure the community has one, try seaerching some of the following sites;

www.codeplex.com

www.sqlteam.com

www.sqlservercentral.com

www.codeproject.com

Database Description Report

Hi All,
I am working on generating a manual explaining databases description. some
of the databases are SQL Server. is there a tool i can use to automatically
generate this report? it must show table name, description and all fields
with the attribute and description of each
Regards
Shawki
Hi
Usually you can get this from your modelling tool such as Visio. You can
also get your own information from the INFORMATION_SCHEMA catalogues and
extended properties can be used to add additional information for example:
http://tinyurl.com/ak43h
Third part tools to do this include:
http://www.ag-software.com/ags_scribe_index.asp you may want to search
google for more.
John
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki
|||Hi,
To get the description you need to add all the column desription using
extended properties and the description will be stored in "sysproperties"
system table in each database.
Eg:
CREATE table test (id int , name char (20))
go
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', 'test', 'column', id
go
EXEC sp_addextendedproperty 'caption', 'Employee Name', 'user', dbo,
'table', 'test', 'column', name
go
select * from sysproperties where object_name(id)='test'
Instead of querying the system tables use the below code using functions to
get the extended properties,
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'test', 'column', default)
APEXSQL had got a very good tool for documentation.
http://www.apexsql.com/sql_tools_doc.asp
Thanks
Hari
SQL Server MVP
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki

Database Description Report

Hi All,
I am working on generating a manual explaining databases description. some
of the databases are SQL Server. is there a tool i can use to automatically
generate this report? it must show table name, description and all fields
with the attribute and description of each
Regards
ShawkiHi
Usually you can get this from your modelling tool such as Visio. You can
also get your own information from the INFORMATION_SCHEMA catalogues and
extended properties can be used to add additional information for example:
http://tinyurl.com/ak43h
Third part tools to do this include:
http://www.ag-software.com/ags_scribe_index.asp you may want to search
google for more.
John
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki|||Hi,
To get the description you need to add all the column desription using
extended properties and the description will be stored in "sysproperties"
system table in each database.
Eg:
CREATE table test (id int , name char (20))
go
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', 'test', 'column', id
go
EXEC sp_addextendedproperty 'caption', 'Employee Name', 'user', dbo,
'table', 'test', 'column', name
go
select * from sysproperties where object_name(id)='test'
Instead of querying the system tables use the below code using functions to
get the extended properties,
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'test', 'column', default)
APEXSQL had got a very good tool for documentation.
http://www.apexsql.com/sql_tools_doc.asp
Thanks
Hari
SQL Server MVP
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki

Database Description Report

Hi All,
I am working on generating a manual explaining databases description. some
of the databases are SQL Server. is there a tool i can use to automatically
generate this report? it must show table name, description and all fields
with the attribute and description of each
Regards
ShawkiHi
Usually you can get this from your modelling tool such as Visio. You can
also get your own information from the INFORMATION_SCHEMA catalogues and
extended properties can be used to add additional information for example:
http://tinyurl.com/ak43h
Third part tools to do this include:
http://www.ag-software.com/ags_scribe_index.asp you may want to search
google for more.
John
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki|||Hi,
To get the description you need to add all the column desription using
extended properties and the description will be stored in "sysproperties"
system table in each database.
Eg:
CREATE table test (id int , name char (20))
go
EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo,
'table', 'test', 'column', id
go
EXEC sp_addextendedproperty 'caption', 'Employee Name', 'user', dbo,
'table', 'test', 'column', name
go
select * from sysproperties where object_name(id)='test'
Instead of querying the system tables use the below code using functions to
get the extended properties,
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table',
'test', 'column', default)
APEXSQL had got a very good tool for documentation.
http://www.apexsql.com/sql_tools_doc.asp
Thanks
Hari
SQL Server MVP
"Shawki" <Shawki@.discussions.microsoft.com> wrote in message
news:E9B548D1-4CEB-405A-81E8-DB681BB056DB@.microsoft.com...
> Hi All,
> I am working on generating a manual explaining databases description. some
> of the databases are SQL Server. is there a tool i can use to
> automatically
> generate this report? it must show table name, description and all fields
> with the attribute and description of each
> Regards
> Shawki