Report on SQL Server objects

Hi,
We have a large database for which we need to provide support. But there is
no documentation on number of tables, stored procedures etc.
Is there any easy way to find out number of objects in a SQL Server database
at all?
Can someone let me know please?
Many thanks,
Harish Mohanbabu
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
I know about the existence of Summary where we can see the list of objects.
Ideally I am looking for a way to document all objects in something like
spread sheets ...
Harish Mohanbabu
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/
|||On Feb 9, 7:58 pm, Harish Mohanbabu <Axa...@.online.nospam> wrote:
> I know about the existence of Summary where we can see the list of objects.
> Ideally I am looking for a way to document all objects in something like
> spread sheets ...
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]http://www.harishm.com/
Use ''DB''
select * from sysobjects where xtype not in ('s') and name not like 'dt
%'
copy the result on excel spreadsheet.
NJ

Report on SQL Server objects

Hi,
We have a large database for which we need to provide support. But there is
no documentation on number of tables, stored procedures etc.
Is there any easy way to find out number of objects in a SQL Server database
at all?
Can someone let me know please?
Many thanks,
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/I know about the existence of Summary where we can see the list of objects.
Ideally I am looking for a way to document all objects in something like
spread sheets ...
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/|||On Feb 9, 7:58 pm, Harish Mohanbabu <Axa...@.online.nospam> wrote:
> I know about the existence of Summary where we can see the list of objects.
> Ideally I am looking for a way to document all objects in something like
> spread sheets ...
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]http://www.harishm.com/
Use ''DB''
select * from sysobjects where xtype not in ('s') and name not like 'dt
%'
copy the result on excel spreadsheet.
NJsql

Report on SQL Server objects

Hi,
We have a large database for which we need to provide support. But there is
no documentation on number of tables, stored procedures etc.
Is there any easy way to find out number of objects in a SQL Server database
at all?
Can someone let me know please?
Many thanks,
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/I know about the existence of Summary where we can see the list of objects.
Ideally I am looking for a way to document all objects in something like
spread sheets ...
Harish Mohanbabu
--
Microsoft Dynamics Ax [MVP]
http://www.harishm.com/|||On Feb 9, 7:58 pm, Harish Mohanbabu <Axa...@.online.nospam> wrote:
> I know about the existence of Summary where we can see the list of objects
.
> Ideally I am looking for a way to document all objects in something like
> spread sheets ...
> Harish Mohanbabu
> --
> Microsoft Dynamics Ax [MVP]http://www.harishm.com/
Use ''DB''
select * from sysobjects where xtype not in ('s') and name not like 'dt
%'
copy the result on excel spreadsheet.
NJ

Report of bigger tables

hi, anyone have a script for creating a report of the bigger tables
of a database in SQL 2000?Yep;
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2004/12/21/41.aspx
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Gustavo Villaran" <gvillaran@.pandero.com.pe> wrote in message
news:u3JjepQfHHA.2396@.TK2MSFTNGP04.phx.gbl...
> hi, anyone have a script for creating a report of the bigger tables
> of a database in SQL 2000?

Report of bigger tables

hi, anyone have a script for creating a report of the bigger tables
of a database in SQL 2000?Yep;
http://blogs.sqlserver.org.au/blogs...4/12/21/41.aspx
HTH
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Gustavo Villaran" <gvillaran@.pandero.com.pe> wrote in message
news:u3JjepQfHHA.2396@.TK2MSFTNGP04.phx.gbl...
> hi, anyone have a script for creating a report of the bigger tables
> of a database in SQL 2000?sql

Report Models using Islookup and expandinline

I am building and deploying models.

I have many small description tables, they include two attributes. On attribute is the key (which is a code or type) and the other is a varchar description. They tables are each optionalone related to the primary table. I am "denormalizing" the description tables using Islookup. I change the Role in the primary table to ISLOOKUP, I modify the defaultattributes to remove the code or type key attribute, only the description attribute is now a detailattribute. I hide the key attribute just in case.

In most cases when I do this the description tables are denormalized and the user now sees only the description in the primary table in report builder. But in a few cases the key attribute (which is normally a code - 1, 2, 4, 5, etc) is displayed, it displays even though it is Hidden, ISlookup is defined and I remove it from the default attributes.

I don't know why some work and some don't, the relationships seem to be identical in my view. Is there something in the model that could designate a table can be used as a ISLookup?

To attempt to work around this I have tried using expandinline.

Any ideas?

Report Builder "honors" the Entity.IsLookup property in the UI if and only if the cardinality of the role used to reach the entity is One/OptionalOne, and the lookup entity has exactly one attribute in its IdentifyingAttributes collection.

|||the entity lookup property is one/optional one and there is only one identifying attribute, this is exactly the issue I have, it does not seem to be consistent. And I cannot find any other differences in the tables to help me understand why this is happening.|||There is one other minor constraint - the one identifying attribute must actually belong to the lookup entity (it cannot come from a related entity). Is this the problem?|||No, it does not come from a related table. I used expandinline and it looks good, if I only have the one identifying attribute I seem to get the correct result, anything I should be aware of?|||

If you use ExpandInline, the user will not be able to navigate to the lookup entity using the Advanced Explorer mode in Report Builder. This is useful when creating reports that compare an item to other items with the same lookup value.

It also sounds like you may have confused the DefaultDetailAttributes and IdentifyingAttributes collections on the lookup entity. For an entity to be treated as a lookup, the IdentifyingAttributes collection must have exactly one attribute in it. The contents of DefaultDetailAttributes is irrelevant.

Report Models using Islookup and expandinline

I am building and deploying models.

I have many small description tables, they include two attributes. On attribute is the key (which is a code or type) and the other is a varchar description. They tables are each optionalone related to the primary table. I am "denormalizing" the description tables using Islookup. I change the Role in the primary table to ISLOOKUP, I modify the defaultattributes to remove the code or type key attribute, only the description attribute is now a detailattribute. I hide the key attribute just in case.

In most cases when I do this the description tables are denormalized and the user now sees only the description in the primary table in report builder. But in a few cases the key attribute (which is normally a code - 1, 2, 4, 5, etc) is displayed, it displays even though it is Hidden, ISlookup is defined and I remove it from the default attributes.

I don't know why some work and some don't, the relationships seem to be identical in my view. Is there something in the model that could designate a table can be used as a ISLookup?

To attempt to work around this I have tried using expandinline.

Any ideas?

Report Builder "honors" the Entity.IsLookup property in the UI if and only if the cardinality of the role used to reach the entity is One/OptionalOne, and the lookup entity has exactly one attribute in its IdentifyingAttributes collection.

|||the entity lookup property is one/optional one and there is only one identifying attribute, this is exactly the issue I have, it does not seem to be consistent. And I cannot find any other differences in the tables to help me understand why this is happening.|||There is one other minor constraint - the one identifying attribute must actually belong to the lookup entity (it cannot come from a related entity). Is this the problem?|||No, it does not come from a related table. I used expandinline and it looks good, if I only have the one identifying attribute I seem to get the correct result, anything I should be aware of?|||

If you use ExpandInline, the user will not be able to navigate to the lookup entity using the Advanced Explorer mode in Report Builder. This is useful when creating reports that compare an item to other items with the same lookup value.

It also sounds like you may have confused the DefaultDetailAttributes and IdentifyingAttributes collections on the lookup entity. For an entity to be treated as a lookup, the IdentifyingAttributes collection must have exactly one attribute in it. The contents of DefaultDetailAttributes is irrelevant.

Report Model/Builder

In a database there are 2 tables, one is student and the other is activities. A student can have multiple activities. There is a one to many relationship between student and activities based upon student_id. Not every student has an activity.
In report builder I would like all of the students to appear regardless of whether they have an activity or not. By default, only those students who have activities appear.

This appears:
Student Activity
1 Track
2 Track
4 Volleyball

I would like to see:
Student Activity
1 Track
2 Track
3 NULL
4 Volleyball

What settings need to be tweaked to make this happen?

Thanks for your assistance.Hi

What query are you using to generate this report? I created 2 tables: student, activity. Not all students have activities. If I use the following query:

select * from student
left outer join activity on student.i = activity.i

where i = student id in both the tables,
I get the list of students, activities (even for students who dont have activities mapped on the activity table)

The Left Outer Join operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row.

Is this what you were looking for?

Thanks

|||Within Report Designer you can create a query and specify any type of join you need. This is IN REPORT BUILDER, where you are not able to specifiy the type of join you would like to use. The query is generated from the report model and dragging fields into the design area.

Thanks for your response though.|||Hi Chris

Can you try this out and let me know if this works?

Create a report model and within the data source view, right click and add a NEW NAMED QUERY, here you can specify a query with joins. Once this query is created, create the report model. This should also list the query that you have created, apart from the 2 tables.

Now from Report Builder, select this datasource and drag/drop your query.
Remove the columns that you dont want (I just had student id and activity)
and run report.

This will give you all records, even the students who dont have activities listed.

If you are just using a report model, I think you need to edit that and add a query into that, so you can use it in your report builder to get the required data.

Thanks.|||

Actually, you don't need to create a named query to do this. You can do it in the Report Model. In your example, you need to set Cardinality=OptionalMany on the role from Student to activity. This tells RB that not all students have activities (so don’t do an inner join or you might lose a few).

Report Model Wizard

I created a data soure view that has tables from two data sources (both sql databases). when I try to generate a report model using this data source view, I get an "invalid object name" error for the first table it encounters that is not from the "primary" data source for the view. Is there something I need to do so the report model retrieves the connection strings for both data sources? Any help is greatly appreciated. Thank you in advance!Did you ever figure this out? I'm having the same problem.|||Cross-database report models are not supported in Model Designer. You can get around this by creating a view on top of the second table. We should probably give you a more informative error (or add support for it).|||Thank you for the reply.. I'm not sure I understand the workaround though. I am trying to do a proof of concept for my department and we have several data sources that I need to report from. Any help I can get is truly appreciated. I've been reading up on the different BI tools available with SQL server 2005 and it seems that maybe my best bet is to work on a data integration project... not sure which route to go. I do have a very good understanding of our database schema, its just a matter of how I can simplify it for end users to be able to write their own reports.|||I think I might have misread your original question. Are the two databases on the same server? If not, you will need to use the Linked Server feature of SQL to make this work. There are some performance implications with cross-server joins that may mean that you should pull the two sources into a single database using Integration Services,|||I actually have both cases... But I can't even get the view to work for two databases on the same server.. can you shed some light on that scenario?|||

Yudi,

If the databases are on one instance on one server, you can use the full name of the table to access the data in a named query. For instance,

SELECT col1, col2, col3 FROM table1

would be the first named query in the original database. In another named query you could use

SELECT col1, col2, col3 FROM DB1.dbo.table2

Provided you have the correct permissions on that database, you will be able to see the data in both databases. I use that technique and it works fairly well.

R

sql

Report Model Wizard

I created a data soure view that has tables from two data sources (both sql databases). when I try to generate a report model using this data source view, I get an "invalid object name" error for the first table it encounters that is not from the "primary" data source for the view. Is there something I need to do so the report model retrieves the connection strings for both data sources? Any help is greatly appreciated. Thank you in advance!Did you ever figure this out? I'm having the same problem.|||Cross-database report models are not supported in Model Designer. You can get around this by creating a view on top of the second table. We should probably give you a more informative error (or add support for it).|||Thank you for the reply.. I'm not sure I understand the workaround though. I am trying to do a proof of concept for my department and we have several data sources that I need to report from. Any help I can get is truly appreciated. I've been reading up on the different BI tools available with SQL server 2005 and it seems that maybe my best bet is to work on a data integration project... not sure which route to go. I do have a very good understanding of our database schema, its just a matter of how I can simplify it for end users to be able to write their own reports.|||I think I might have misread your original question. Are the two databases on the same server? If not, you will need to use the Linked Server feature of SQL to make this work. There are some performance implications with cross-server joins that may mean that you should pull the two sources into a single database using Integration Services,|||I actually have both cases... But I can't even get the view to work for two databases on the same server.. can you shed some light on that scenario?|||

Yudi,

If the databases are on one instance on one server, you can use the full name of the table to access the data in a named query. For instance,

SELECT col1, col2, col3 FROM table1

would be the first named query in the original database. In another named query you could use

SELECT col1, col2, col3 FROM DB1.dbo.table2

Provided you have the correct permissions on that database, you will be able to see the data in both databases. I use that technique and it works fairly well.

R

Report Model Project: Multiple Data Sources

I have setup a "Report Model Project" with multiple data sources, and
imported tables from each of these into my data source view. When I try to
use the wizard to create a report model I get an error on the "Completing the
Wizard" stage when I click "Run".
An error occurred while executing a command.
Message: Invalid object name 'dbo.<table name>'.
Command:
SELECT COUNT(*) FROM [dbo].[<table name>] t
The <table name> will always relate to the first table it attempts to
process from the secondary (non-primary) data source. I figured this out by
setting up the project from scratch with the opposite data source as primary.
It seems like it is looking in the primary data source for the table that it
should be trying to use the secondary data source to query.
Any help is appreciated. :)A report model can only reference one data source in this release. Data
source views are more flexible than supported by report models.
-Carolyn
"Greg T. Smith" wrote:
> I have setup a "Report Model Project" with multiple data sources, and
> imported tables from each of these into my data source view. When I try to
> use the wizard to create a report model I get an error on the "Completing the
> Wizard" stage when I click "Run".
> An error occurred while executing a command.
> Message: Invalid object name 'dbo.<table name>'.
> Command:
> SELECT COUNT(*) FROM [dbo].[<table name>] t
> The <table name> will always relate to the first table it attempts to
> process from the secondary (non-primary) data source. I figured this out by
> setting up the project from scratch with the opposite data source as primary.
> It seems like it is looking in the primary data source for the table that it
> should be trying to use the secondary data source to query.
> Any help is appreciated. :)

report model project problem - entity not showing up

I have a client table and a clientdoctors table.
In the data source view i see all the needed tables in the diagram organizer
pane. I also see the relationship between the clientdoctors table (which
holds clientid and doctorid), as well as client table and doctor table.
That all looks fine. The problem however is that when in my report model i
do not see those relationships at all between the client - client doctor -
doctor tables. There are a few situations just like this that i cannot
explain.
I am new to the report model project but i dont know why i dont see these
relationships inthe report model or while using report builder.
Any ideas please'Have you deployed properly? I mean you didn;t get any error. After deploying
you get to see in Report Builder. Just check.
Amarnath
"dave" wrote:
> I have a client table and a clientdoctors table.
> In the data source view i see all the needed tables in the diagram organizer
> pane. I also see the relationship between the clientdoctors table (which
> holds clientid and doctorid), as well as client table and doctor table.
> That all looks fine. The problem however is that when in my report model i
> do not see those relationships at all between the client - client doctor -
> doctor tables. There are a few situations just like this that i cannot
> explain.
> I am new to the report model project but i dont know why i dont see these
> relationships inthe report model or while using report builder.
> Any ideas please'|||Yes everything deployed properly. It seems like the problem was related to
keys on the tables. i.e. i had to place keys on the middle table.
"Amarnath" wrote:
> Have you deployed properly? I mean you didn;t get any error. After deploying
> you get to see in Report Builder. Just check.
> Amarnath
>
> "dave" wrote:
> > I have a client table and a clientdoctors table.
> >
> > In the data source view i see all the needed tables in the diagram organizer
> > pane. I also see the relationship between the clientdoctors table (which
> > holds clientid and doctorid), as well as client table and doctor table.
> >
> > That all looks fine. The problem however is that when in my report model i
> > do not see those relationships at all between the client - client doctor -
> > doctor tables. There are a few situations just like this that i cannot
> > explain.
> >
> > I am new to the report model project but i dont know why i dont see these
> > relationships inthe report model or while using report builder.
> >
> > Any ideas please'|||You need to have key relationship otherwise it wont select tables in the
report model itself. If you have completed the wizard of creating report
model. then it would have asked for keys when you select tables for
relations. So just check if all the keys are in place and deploy.
Amarnath
"dave" wrote:
> Yes everything deployed properly. It seems like the problem was related to
> keys on the tables. i.e. i had to place keys on the middle table.
>
> "Amarnath" wrote:
> > Have you deployed properly? I mean you didn;t get any error. After deploying
> > you get to see in Report Builder. Just check.
> >
> > Amarnath
> >
> >
> > "dave" wrote:
> >
> > > I have a client table and a clientdoctors table.
> > >
> > > In the data source view i see all the needed tables in the diagram organizer
> > > pane. I also see the relationship between the clientdoctors table (which
> > > holds clientid and doctorid), as well as client table and doctor table.
> > >
> > > That all looks fine. The problem however is that when in my report model i
> > > do not see those relationships at all between the client - client doctor -
> > > doctor tables. There are a few situations just like this that i cannot
> > > explain.
> > >
> > > I am new to the report model project but i dont know why i dont see these
> > > relationships inthe report model or while using report builder.
> > >
> > > Any ideas please'

Report Model on tables without primary key

I am trying to create a report model on some tables that do not have a
primary key. The wizard keeps failing with the message "Table does not have a
primary key". Is a primary key mandatory?You could define logic primary key in your dsv.
"Dan Varozza" wrote:
> I am trying to create a report model on some tables that do not have a
> primary key. The wizard keeps failing with the message "Table does not have a
> primary key". Is a primary key mandatory?|||WHen I run the DSV wizard it tells me that "no foreign keys were found. You
can create logical relationships on matching columns."
Then it gives me the choice to "Create logical relationships by matching
columns" and 3 Foreign Key Matches to choose from:
"Same as Primary key"
or
"Same name as destination table name"
or
"Destination table name + primary key name"
I have tried all 3 of the choices above and still the wizard fails with the
same error. "Table does not have a primary key".
Any ideas?
"yongli" wrote:
> You could define logic primary key in your dsv.
> "Dan Varozza" wrote:
> > I am trying to create a report model on some tables that do not have a
> > primary key. The wizard keeps failing with the message "Table does not have a
> > primary key". Is a primary key mandatory?sql

Report Model is generating without data

I have been generating report models for users to use with Report Builder and there is no data when they select the model. I noticed that the tables I chose did not have a primary key and when I chose a different table, with a primary key, and generated a model from it, then there was data for the user to use in Report Builder.

Is there a documented work around or will I need to set a primary key on each table?

You don't need a physical PK on each of your tables. Instead, open up the DSV in your Report Model project, right click each table, and choose "Set Logical Primary Key" and the appropriate field. You'll then need to rebuild the model. See if this works for you!|||Thanks Russell! That worked just fine after I rebuilt the model.

Report Model Generating Out of Memory Error

I am creating a report model of a large database with a few hundred tables, the idea being that the users can report on any part of the database and create perspectives for the areas they want users to have access to (In the past the users have moaned about report models in a CA product called Eureka as the models are too small, perspectives seemed a way of allowing one large model but limiting each perspective to a set of users?). Unfortunately presumably due to the size of the model visual studio is giving me an out of memory error when adding new tables. I have a two dual core xeons with 4Gig of ram and I'm running XP Pro 64bit with the developer edition of 2005 with service pack 1 and VS2005 is using around 1/2 a Gb. Is this a known issue and will there be a work around?

Seems to occur when removing anything from the report model. Can reproduce by deleting calculated fields and selecting save.|||This sounds like a bug. Can you describe a list of steps that would repro the issue?|||Its fairly simple I only have to delete a few fields or tables from my report model and it triggers an out of memory error. I can forward the report model if thats helpful? I would like to regenerate the fields from the report model by using the autogenerate/get rid of some of the autogenerated calculated fields bu can't as I get this error each time.|||

yes, the model and the list of steps would help almineev tata microsoft todtod moc

|||

Alexandre Mineev MSFT wrote:

yes, the model and the list of steps would help almineev tata microsoft todtod moc

? noticed the error location button so heres some more info, pls contact direct when you need the model and files, you should have my email. Cool signature!

===================================

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)

Program Location:

at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
at System.Text.StringBuilder.Append(Char value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BufferTextWriter.Write(Char ch)
at System.IO.TextWriter.Write(Char[] buffer, Int32 index, Int32 count)
at System.Xml.XmlEncodedRawTextWriter.FlushBuffer()
at System.Xml.XmlEncodedRawTextWriter.RawText(Char* pSrcBegin, Char* pSrcEnd)
at System.Xml.XmlEncodedRawTextWriter.RawText(String s)
at System.Xml.XmlEncodedRawTextWriterIndent.WriteIndent()
at System.Xml.XmlEncodedRawTextWriterIndent.WriteStartElement(String prefix, String localName, String ns)
at System.Xml.XmlWellFormedWriter.WriteStartElement(String prefix, String localName, String ns)
at System.Xml.XmlWriter.WriteStartElement(String localName, String ns)
at Microsoft.ReportingServices.Modeling.AttributeRefNode.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.FunctionNode.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelAttribute.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelAttribute.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelEntity.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelEntityFolder.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.SemanticModel.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.SemanticModel.WriteTo(XmlWriter xw, ModelingSerializationOptions options)
at Microsoft.ReportingServices.ModelDesigner.Serialization.ModelDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)

|||Hi Phil, I did not get an email from you|||Where do I send the email?|||almineev tata microsoft todtod moc

Report Model Generating Out of Memory Error

I am creating a report model of a large database with a few hundred tables, the idea being that the users can report on any part of the database and create perspectives for the areas they want users to have access to (In the past the users have moaned about report models in a CA product called Eureka as the models are too small, perspectives seemed a way of allowing one large model but limiting each perspective to a set of users?). Unfortunately presumably due to the size of the model visual studio is giving me an out of memory error when adding new tables. I have a two dual core xeons with 4Gig of ram and I'm running XP Pro 64bit with the developer edition of 2005 with service pack 1 and VS2005 is using around 1/2 a Gb. Is this a known issue and will there be a work around?

Seems to occur when removing anything from the report model. Can reproduce by deleting calculated fields and selecting save.|||This sounds like a bug. Can you describe a list of steps that would repro the issue?|||Its fairly simple I only have to delete a few fields or tables from my report model and it triggers an out of memory error. I can forward the report model if thats helpful? I would like to regenerate the fields from the report model by using the autogenerate/get rid of some of the autogenerated calculated fields bu can't as I get this error each time.|||

yes, the model and the list of steps would help almineev tata microsoft todtod moc

|||

Alexandre Mineev MSFT wrote:

yes, the model and the list of steps would help almineev tata microsoft todtod moc

? noticed the error location button so heres some more info, pls contact direct when you need the model and files, you should have my email. Cool signature!

===================================

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)

Program Location:

at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
at System.Text.StringBuilder.Append(Char value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BufferTextWriter.Write(Char ch)
at System.IO.TextWriter.Write(Char[] buffer, Int32 index, Int32 count)
at System.Xml.XmlEncodedRawTextWriter.FlushBuffer()
at System.Xml.XmlEncodedRawTextWriter.RawText(Char* pSrcBegin, Char* pSrcEnd)
at System.Xml.XmlEncodedRawTextWriter.RawText(String s)
at System.Xml.XmlEncodedRawTextWriterIndent.WriteIndent()
at System.Xml.XmlEncodedRawTextWriterIndent.WriteStartElement(String prefix, String localName, String ns)
at System.Xml.XmlWellFormedWriter.WriteStartElement(String prefix, String localName, String ns)
at System.Xml.XmlWriter.WriteStartElement(String localName, String ns)
at Microsoft.ReportingServices.Modeling.AttributeRefNode.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.FunctionNode.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelAttribute.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelAttribute.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelEntity.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelEntityFolder.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.SemanticModel.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.SemanticModel.WriteTo(XmlWriter xw, ModelingSerializationOptions options)
at Microsoft.ReportingServices.ModelDesigner.Serialization.ModelDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)

|||Hi Phil, I did not get an email from you|||Where do I send the email?|||almineev tata microsoft todtod moc

Report Model Generating Out of Memory Error

I am creating a report model of a large database with a few hundred tables, the idea being that the users can report on any part of the database and create perspectives for the areas they want users to have access to (In the past the users have moaned about report models in a CA product called Eureka as the models are too small, perspectives seemed a way of allowing one large model but limiting each perspective to a set of users?). Unfortunately presumably due to the size of the model visual studio is giving me an out of memory error when adding new tables. I have a two dual core xeons with 4Gig of ram and I'm running XP Pro 64bit with the developer edition of 2005 with service pack 1 and VS2005 is using around 1/2 a Gb. Is this a known issue and will there be a work around?

Seems to occur when removing anything from the report model. Can reproduce by deleting calculated fields and selecting save.|||This sounds like a bug. Can you describe a list of steps that would repro the issue?|||Its fairly simple I only have to delete a few fields or tables from my report model and it triggers an out of memory error. I can forward the report model if thats helpful? I would like to regenerate the fields from the report model by using the autogenerate/get rid of some of the autogenerated calculated fields bu can't as I get this error each time.|||

yes, the model and the list of steps would help almineev tata microsoft todtod moc

|||

Alexandre Mineev MSFT wrote:

yes, the model and the list of steps would help almineev tata microsoft todtod moc

? noticed the error location button so heres some more info, pls contact direct when you need the model and files, you should have my email. Cool signature!

===================================

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)

Program Location:

at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
at System.Text.StringBuilder.Append(Char value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BufferTextWriter.Write(Char ch)
at System.IO.TextWriter.Write(Char[] buffer, Int32 index, Int32 count)
at System.Xml.XmlEncodedRawTextWriter.FlushBuffer()
at System.Xml.XmlEncodedRawTextWriter.RawText(Char* pSrcBegin, Char* pSrcEnd)
at System.Xml.XmlEncodedRawTextWriter.RawText(String s)
at System.Xml.XmlEncodedRawTextWriterIndent.WriteIndent()
at System.Xml.XmlEncodedRawTextWriterIndent.WriteStartElement(String prefix, String localName, String ns)
at System.Xml.XmlWellFormedWriter.WriteStartElement(String prefix, String localName, String ns)
at System.Xml.XmlWriter.WriteStartElement(String localName, String ns)
at Microsoft.ReportingServices.Modeling.AttributeRefNode.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.FunctionNode.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.Expression.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelAttribute.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelAttribute.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelEntity.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.ModelEntityFolder.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.Microsoft.ReportingServices.Modeling.IXmlWriteable.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelingXmlWriter.WriteCollectionElement[T](String elementName, ICollection`1 items)
at Microsoft.ReportingServices.Modeling.SemanticModel.WriteXmlElements(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.ModelItem.WriteTo(ModelingXmlWriter xw)
at Microsoft.ReportingServices.Modeling.SemanticModel.WriteTo(XmlWriter xw, ModelingSerializationOptions options)
at Microsoft.ReportingServices.ModelDesigner.Serialization.ModelDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)

|||Hi Phil, I did not get an email from you|||Where do I send the email?|||almineev tata microsoft todtod moc

Report Model - where is it stored?

Hello,

I'm trying to figure out where a "report model" is stored in the ReportServer database. Can anyone tell me the tables and views where a report model definition is stored internally?

Thanks,

BCB

The ReportServer's Catalog table contains the information you want (I think). To get the info stored for each report, you can use

use ReportServer;

select * from Catalog

where Type = 2;

HTH.

|||

Thanks for your reply. The query you provided will return the RS reports that are stored in the Catalog table. I modified your query to return the Model data that I wanted. (See below.) The Type column has a value of 6 for models as you can see in the query. The answer to my original question appears to be that report models are stored in their entirety in the Content column. It is an image column that has a capacity of 2 billion bytes. I put the model in the "black box" category because it does not seem to be implemented (in the physical sense) as views or procedures that can be examined with the naked eye - my favorite tool.

USE ReportServer;

SELECT

ItemID,

Path,

Name,

Description,

Content,

DATALENGTH(Content) AS [Content Length],

Type

FROM

Catalog

WHERE

Type = 6;

Report Model - where is it stored?

Hello,

I'm trying to figure out where a "report model" is stored in the ReportServer database. Can anyone tell me the tables and views where a report model definition is stored internally?

Thanks,

BCB

The ReportServer's Catalog table contains the information you want (I think). To get the info stored for each report, you can use

use ReportServer;

select * from Catalog

where Type = 2;

HTH.

|||

Thanks for your reply. The query you provided will return the RS reports that are stored in the Catalog table. I modified your query to return the Model data that I wanted. (See below.) The Type column has a value of 6 for models as you can see in the query. The answer to my original question appears to be that report models are stored in their entirety in the Content column. It is an image column that has a capacity of 2 billion bytes. I put the model in the "black box" category because it does not seem to be implemented (in the physical sense) as views or procedures that can be examined with the naked eye - my favorite tool.

USE ReportServer;

SELECT

ItemID,

Path,

Name,

Description,

Content,

DATALENGTH(Content) AS [Content Length],

Type

FROM

Catalog

WHERE

Type = 6;

Report Layout

I have 2 tables in Access. I use VB to view a report in Crystal Report.

Table1: StaffMaster

EmpCode
EmpName

Table2: StaffHrs

EmpCode
Rfs
Project
Month_Year
ATH

I wish to display a report in Crystal Report as follows:

EmpCode | EmpName | Rfs | Prject | Month_Year | ATH

How do I accomplish this? Can this be done using crosstab query?

The criteria is for Month_Year. I tried using Crosstab query, but I have problems with the date format. The date should be displayed on the report as mmmm yyyy format. Can it be done using the following query?

"SELECT StaffMaster.EmpCode, StaffMaster.EmpName, StaffHrs.RFS, StaffHrs.Project, StaffHrs.Month_Year, " & _
"StaffHrs.ATH FROM StaffMaster LEFT OUTER JOIN StaffHrs ON StaffMaster.EmpCode=StaffHrs.EmpCode " & _
"WHERE StaffMaster.EmpCode=StaffHrs.EmpCode AND StaffMaster.Discipline='Architecture' AND " & _
"StaffHrs.Month_Year BETWEEN #July 2004# AND #September 2004#"

If so, how will I display the fields in the appropriate places?

ThanksI landed up in the crosstab query, without the date criteria, which I think will give me the result I want. I ignored the date criteria, so that I can retrieve all the records in the StaffMaster table, which is what I want. But one problem is that, the recordset contains all the dates that are there in the StaffHrs table. I would like to find out, if the Month_Year field of the recordset is the same as that I want, and then display it into a field. The Month_Year field is not fixed, as it depends on the user's selection. So it cannot be specified in the code. I know this can be done with formula fields. But how?

This is the crosstab query:

strsql = "TRANSFORM Sum(StaffHrs.ATH) AS SumOfATH " & _
"SELECT StaffMaster.EmpCode, StaffMaster.EmpName, StaffHrs.RFS, StaffHrs.Project " & _
"FROM StaffMaster LEFT JOIN StaffHrs ON StaffMaster.EmpCode = StaffHrs.EmpCode " & _
"Where (([StaffMaster]![Discipline] = 'Architecture')) " & _
"GROUP BY StaffMaster.EmpCode, StaffMaster.EmpName, StaffHrs.RFS, StaffHrs.Project " & _
"ORDER BY StaffMaster.EmpName " & _
"PIVOT StaffHrs.Month_Year"

The Field Count is 18. But this may also vary depending on the Discipline that the user selects.

If, for example, the Discipline is Architecture, then the field count is 18. I did a looping just to see what the fields are. This is the code:

For I = 1 To Report.Database.Tables(1).Fields.Count
K = Report.Database.Tables(1).Fields(I).DatabaseFieldDisplayName
MsgBox K
Next I

The result I got is as follows:

<>
01/01/05
02/01/05
03/01/05
04/01/05
05/01/05
05/01/04
06/01/04
07/01/04
08/01/04
09/01/04
10/01/04
11/01/04
12/01/04
EmpCode
EmpName
Project
RFS

If the result is this, how will I find out the appropriate data for the corresponding date and display it in the corresponding fields? The user needs to view the data for 6 months from the month he selects.

Thanks