Estimate disk space required for my DB

Hi All,
I got some hints on estimating the table size from BOL.
But how do I estimate other objects like SP, UDF etc.
I want to estimate the disk size required at my deployment
SQL Server box.
Is there any tool available to estimate the DB size?
Thanks in advance,
Hari
These objects basically don't use any space. Well, the do use a row per object in sysobjects, a row
per parameter in syscolumns and a row per object in syscomments. The last one will contain the
actual source code for the object. I wouldn't worry about these things...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Shankar" <anonymous@.discussions.microsoft.com> wrote in message
news:23b5101c45f37$15b43c70$a601280a@.phx.gbl...
> Hi All,
> I got some hints on estimating the table size from BOL.
> But how do I estimate other objects like SP, UDF etc.
> I want to estimate the disk size required at my deployment
> SQL Server box.
> Is there any tool available to estimate the DB size?
> Thanks in advance,
> Hari
|||Hi
The biggest size will always be the data, stored procedures and function
definitions are mainly held in the syscomments tables. It should be pretty
static therefore once you have loaded them into the database it will not
change.
John
"Hari Shankar" <anonymous@.discussions.microsoft.com> wrote in message
news:23b5101c45f37$15b43c70$a601280a@.phx.gbl...
> Hi All,
> I got some hints on estimating the table size from BOL.
> But how do I estimate other objects like SP, UDF etc.
> I want to estimate the disk size required at my deployment
> SQL Server box.
> Is there any tool available to estimate the DB size?
> Thanks in advance,
> Hari
|||Hi,
Add on to Tiber,
Is there any tool available to estimate the DB size?
No tool can suggest you the actual hard disk requirement.
There is no easy way to estimate precisely the storage requirements for a
given database.
If an accurate size estimate is needed, it is suggested that a test database
is setup in a test server with
suitable fields, numbers of tables and queries. It should be filled with
suitable random data
and operated as a database for a short time before attempting to measure the
disk storage needed
to store and manage all the information.
If you have an existing system you can calculate the hard disk requirement
based on that.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OWbFFozXEHA.712@.TK2MSFTNGP11.phx.gbl...
> These objects basically don't use any space. Well, the do use a row per
object in sysobjects, a row
> per parameter in syscolumns and a row per object in syscomments. The last
one will contain the
> actual source code for the object. I wouldn't worry about these things...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hari Shankar" <anonymous@.discussions.microsoft.com> wrote in message
> news:23b5101c45f37$15b43c70$a601280a@.phx.gbl...
>
|||Thanks Hari for your reply.
I fully agree with you. But I'm looking for a tool/script
which will implement the table estimate calculation (as in
BOL). Instead of applying those calculation manually for
each table, a script/tool may help me.
What is the standard approach for disk sizing for an
application?
Thanks a lot.
Hari
>--Original Message--
>Hi,
>Add on to Tiber,
>Is there any tool available to estimate the DB size?
>No tool can suggest you the actual hard disk requirement.
>There is no easy way to estimate precisely the storage
requirements for a
>given database.
>If an accurate size estimate is needed, it is suggested
that a test database
>is setup in a test server with
>suitable fields, numbers of tables and queries. It should
be filled with
>suitable random data
>and operated as a database for a short time before
attempting to measure the
>disk storage needed
>to store and manage all the information.
>If you have an existing system you can calculate the hard
disk requirement
>based on that.
>--
>Thanks
>Hari
>MCDBA
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in[vbcol=seagreen]
>message news:OWbFFozXEHA.712@.TK2MSFTNGP11.phx.gbl...
do use a row per[vbcol=seagreen]
>object in sysobjects, a row
syscomments. The last[vbcol=seagreen]
>one will contain the
about these things...[vbcol=seagreen]
wrote in message[vbcol=seagreen]
BOL.[vbcol=seagreen]
deployment
>
>.
>

Estimate disk space required for my DB

Hi All,
I got some hints on estimating the table size from BOL.
But how do I estimate other objects like SP, UDF etc.
I want to estimate the disk size required at my deployment
SQL Server box.
Is there any tool available to estimate the DB size?
Thanks in advance,
HariThese objects basically don't use any space. Well, the do use a row per object in sysobjects, a row
per parameter in syscolumns and a row per object in syscomments. The last one will contain the
actual source code for the object. I wouldn't worry about these things...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Shankar" <anonymous@.discussions.microsoft.com> wrote in message
news:23b5101c45f37$15b43c70$a601280a@.phx.gbl...
> Hi All,
> I got some hints on estimating the table size from BOL.
> But how do I estimate other objects like SP, UDF etc.
> I want to estimate the disk size required at my deployment
> SQL Server box.
> Is there any tool available to estimate the DB size?
> Thanks in advance,
> Hari|||Hi
The biggest size will always be the data, stored procedures and function
definitions are mainly held in the syscomments tables. It should be pretty
static therefore once you have loaded them into the database it will not
change.
John
"Hari Shankar" <anonymous@.discussions.microsoft.com> wrote in message
news:23b5101c45f37$15b43c70$a601280a@.phx.gbl...
> Hi All,
> I got some hints on estimating the table size from BOL.
> But how do I estimate other objects like SP, UDF etc.
> I want to estimate the disk size required at my deployment
> SQL Server box.
> Is there any tool available to estimate the DB size?
> Thanks in advance,
> Hari|||Hi,
Add on to Tiber,
Is there any tool available to estimate the DB size?
No tool can suggest you the actual hard disk requirement.
There is no easy way to estimate precisely the storage requirements for a
given database.
If an accurate size estimate is needed, it is suggested that a test database
is setup in a test server with
suitable fields, numbers of tables and queries. It should be filled with
suitable random data
and operated as a database for a short time before attempting to measure the
disk storage needed
to store and manage all the information.
If you have an existing system you can calculate the hard disk requirement
based on that.
--
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OWbFFozXEHA.712@.TK2MSFTNGP11.phx.gbl...
> These objects basically don't use any space. Well, the do use a row per
object in sysobjects, a row
> per parameter in syscolumns and a row per object in syscomments. The last
one will contain the
> actual source code for the object. I wouldn't worry about these things...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hari Shankar" <anonymous@.discussions.microsoft.com> wrote in message
> news:23b5101c45f37$15b43c70$a601280a@.phx.gbl...
> > Hi All,
> >
> > I got some hints on estimating the table size from BOL.
> > But how do I estimate other objects like SP, UDF etc.
> >
> > I want to estimate the disk size required at my deployment
> > SQL Server box.
> >
> > Is there any tool available to estimate the DB size?
> >
> > Thanks in advance,
> > Hari
>|||Thanks Hari for your reply.
I fully agree with you. But I'm looking for a tool/script
which will implement the table estimate calculation (as in
BOL). Instead of applying those calculation manually for
each table, a script/tool may help me.
What is the standard approach for disk sizing for an
application?
Thanks a lot.
Hari
>--Original Message--
>Hi,
>Add on to Tiber,
>Is there any tool available to estimate the DB size?
>No tool can suggest you the actual hard disk requirement.
>There is no easy way to estimate precisely the storage
requirements for a
>given database.
>If an accurate size estimate is needed, it is suggested
that a test database
>is setup in a test server with
>suitable fields, numbers of tables and queries. It should
be filled with
>suitable random data
>and operated as a database for a short time before
attempting to measure the
>disk storage needed
>to store and manage all the information.
>If you have an existing system you can calculate the hard
disk requirement
>based on that.
>--
>Thanks
>Hari
>MCDBA
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:OWbFFozXEHA.712@.TK2MSFTNGP11.phx.gbl...
>> These objects basically don't use any space. Well, the
do use a row per
>object in sysobjects, a row
>> per parameter in syscolumns and a row per object in
syscomments. The last
>one will contain the
>> actual source code for the object. I wouldn't worry
about these things...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Hari Shankar" <anonymous@.discussions.microsoft.com>
wrote in message
>> news:23b5101c45f37$15b43c70$a601280a@.phx.gbl...
>> > Hi All,
>> >
>> > I got some hints on estimating the table size from
BOL.
>> > But how do I estimate other objects like SP, UDF etc.
>> >
>> > I want to estimate the disk size required at my
deployment
>> > SQL Server box.
>> >
>> > Is there any tool available to estimate the DB size?
>> >
>> > Thanks in advance,
>> > Hari
>>
>
>.
>sql

Estimate disk space required for my DB

Hi All,
I got some hints on estimating the table size from BOL.
But how do I estimate other objects like SP, UDF etc.
I want to estimate the disk size required at my deployment
SQL Server box.
Is there any tool available to estimate the DB size?
Thanks in advance,
HariThese objects basically don't use any space. Well, the do use a row per obje
ct in sysobjects, a row
per parameter in syscolumns and a row per object in syscomments. The last on
e will contain the
actual source code for the object. I wouldn't worry about these things...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Shankar" <anonymous@.discussions.microsoft.com> wrote in message
news:23b5101c45f37$15b43c70$a601280a@.phx
.gbl...
> Hi All,
> I got some hints on estimating the table size from BOL.
> But how do I estimate other objects like SP, UDF etc.
> I want to estimate the disk size required at my deployment
> SQL Server box.
> Is there any tool available to estimate the DB size?
> Thanks in advance,
> Hari|||Hi
The biggest size will always be the data, stored procedures and function
definitions are mainly held in the syscomments tables. It should be pretty
static therefore once you have loaded them into the database it will not
change.
John
"Hari Shankar" <anonymous@.discussions.microsoft.com> wrote in message
news:23b5101c45f37$15b43c70$a601280a@.phx
.gbl...
> Hi All,
> I got some hints on estimating the table size from BOL.
> But how do I estimate other objects like SP, UDF etc.
> I want to estimate the disk size required at my deployment
> SQL Server box.
> Is there any tool available to estimate the DB size?
> Thanks in advance,
> Hari|||Hi,
Add on to Tiber,
Is there any tool available to estimate the DB size?
No tool can suggest you the actual hard disk requirement.
There is no easy way to estimate precisely the storage requirements for a
given database.
If an accurate size estimate is needed, it is suggested that a test database
is setup in a test server with
suitable fields, numbers of tables and queries. It should be filled with
suitable random data
and operated as a database for a short time before attempting to measure the
disk storage needed
to store and manage all the information.
If you have an existing system you can calculate the hard disk requirement
based on that.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OWbFFozXEHA.712@.TK2MSFTNGP11.phx.gbl...
> These objects basically don't use any space. Well, the do use a row per
object in sysobjects, a row
> per parameter in syscolumns and a row per object in syscomments. The last
one will contain the
> actual source code for the object. I wouldn't worry about these things...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Hari Shankar" <anonymous@.discussions.microsoft.com> wrote in message
> news:23b5101c45f37$15b43c70$a601280a@.phx
.gbl...
>|||Thanks Hari for your reply.
I fully agree with you. But I'm looking for a tool/script
which will implement the table estimate calculation (as in
BOL). Instead of applying those calculation manually for
each table, a script/tool may help me.
What is the standard approach for disk sizing for an
application?
Thanks a lot.
Hari
>--Original Message--
>Hi,
>Add on to Tiber,
>Is there any tool available to estimate the DB size?
>No tool can suggest you the actual hard disk requirement.
>There is no easy way to estimate precisely the storage
requirements for a
>given database.
>If an accurate size estimate is needed, it is suggested
that a test database
>is setup in a test server with
>suitable fields, numbers of tables and queries. It should
be filled with
>suitable random data
>and operated as a database for a short time before
attempting to measure the
>disk storage needed
>to store and manage all the information.
>If you have an existing system you can calculate the hard
disk requirement
>based on that.
>--
>Thanks
>Hari
>MCDBA
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:OWbFFozXEHA.712@.TK2MSFTNGP11.phx.gbl...
do use a row per[vbcol=seagreen]
>object in sysobjects, a row
syscomments. The last[vbcol=seagreen]
>one will contain the
about these things...[vbcol=seagreen]
wrote in message[vbcol=seagreen]
BOL.[vbcol=seagreen]
deployment[vbcol=seagreen]
>
>.
>

Errors not logged

Hi :

I have created the followign ddl trigger to log all the ddl activities. It is workign fine for all the sucessfully created objects.

The problem is, how do i log errors also. Like if i am trying to create a table with same name twice then the trigger should log a stmt saying that the table already exists in the database. or while creating synonyms twice with same name, etc

I want to log all the failed DDL stmts also.

Any solution will be of good help to me.

CREATE TRIGGER [DDLLogging] ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS AS
BEGIN
SET NOCOUNT ON;

DECLARE @.data XML;
DECLARE @.schema sysname;
DECLARE @.object sysname;
DECLARE @.eventType sysname;

DECLARE @.logstmt varchar(500);

DECLARE @.query VARCHAR(255)
DECLARE @.file VARCHAR(255)

SET @.data = EVENTDATA();
SET @.eventType = @.data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
SET @.schema = @.data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
SET @.object = @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
SET @.file = 'c:\xzy\'+REPLACE(CONVERT(char(10),GETDATE(),111),'/','_')+'.log'

IF @.object IS NOT NULL
BEGIN
SET @.logstmt = CONVERT(varchar(20),GETDATE(),9) + ', ' + @.eventType + ' ' + @.object + ' successful';
SET @.query = RTRIM('echo ' + COALESCE(LTRIM(@.logstmt),'-') + ' >> ' + RTRIM(@.file))
EXEC master..xp_cmdshell @.query, NO_OUTPUT
END
ELSE
BEGIN
SET @.logstmt = CONVERT(varchar(20),GETDATE(),9) + ', ' + @.eventType + ' successful';
SET @.query = RTRIM('echo ' + COALESCE(LTRIM(@.logstmt),'-') + ' >> ' + RTRIM(@.file))
EXEC master..xp_cmdshell @.query, NO_OUTPUT
END

END;

i am using sql express.

|||DDL Triggers only fire for successfully completed DDL. If the DDL fails due to a error like a duplicate object, then the DDL trigger is never fired. If you want to audit error messages for failed DDL, you will have to use profiler or trace to observe the failures.|||

Hi:

Thank you for the reply.

I would like to know if there is any other mechnism to log unsucessfully executed DDL stmt.

All error while executing DDL stmts should be logged into log file.

By looking the log file, we should know which ddl stmts have executed and which have failed.

Please let me know of any solution.

i am using sqlcmd for executing .sql files. SQL Express is my database.

|||

Hi,

there is no builtin way to do this, you can temporary setup profiler to get the exception that come up while using false DDL statement, but using the profiler should only be a temporary solution as it it (in my opinion) very expensive.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Tracing is more expensive when capturing unneeded columns, when filtering rows (since events are generated no matter what is filtered), and (most importantly) when the trace consumer (i.e., the trace table or the trace file) cannot consume those traced events in an expedient manner. Traces will drive up logical reads. Your experience will depend upon the trace system you design/choose (in geometric addition to load upon the client/server system).

For failed DDL, one should hope that (say) 1000 such events per second (or minute) is never seen , or, you may need to throttle the client (if the rate of DDL failures gets "excessive"). IMO, good development includes scalability testing from about day one (of any project), thus improper tracing should be detectable from about day one .

You can fire up SQL Server Profiler (the GUI) to create a server-side script (best to forbid the GUI in production and strive to avoid the GUI in development), remove the "profiler" filter (if present), and (for that scalability testing) profile the impact of profiler. Just make sure you don't ever overload the consumer's I/O (the consumer must consume quickly), otherwise life will suck.

Keep it short, sweet, and simple. And remember that you cannot squeeze an elephant through the eye of a needle.

Errors Importing From Enterprise Manager

Hi,
I am attempting to import objects from one database to another, including co
nstraints. However, the operation keeps failing. How do I find out the spe
cific reason for the failure? Enterprise Manager does not provide specifics
in the error message.
Thanks,
MarkIf you are using the wizard, you can find out more specifics by
double-clicking the line that says "error occurred"...
http://www.aspfaq.com/
(Reverse address to reply.)
"mark" <mark@.discussions.microsoft.com> wrote in message
news:4FE6D4E5-7285-4A01-927D-B47C4F4C3AE6@.microsoft.com...
> Hi,
> I am attempting to import objects from one database to another, including
> constraints. However, the operation keeps failing. How do I find out the
> specific reason for the failure? Enterprise Manager does not provide
> specifics in the error message.
> Thanks,
> Mark

Errors Importing From Enterprise Manager

Hi,
I am attempting to import objects from one database to another, including constraints. However, the operation keeps failing. How do I find out the specific reason for the failure? Enterprise Manager does not provide specifics in the error message.
Thanks,
Mark
If you are using the wizard, you can find out more specifics by
double-clicking the line that says "error occurred"...
http://www.aspfaq.com/
(Reverse address to reply.)
"mark" <mark@.discussions.microsoft.com> wrote in message
news:4FE6D4E5-7285-4A01-927D-B47C4F4C3AE6@.microsoft.com...
> Hi,
> I am attempting to import objects from one database to another, including
> constraints. However, the operation keeps failing. How do I find out the
> specific reason for the failure? Enterprise Manager does not provide
> specifics in the error message.
> Thanks,
> Mark

Errors Importing From Enterprise Manager

Hi,
I am attempting to import objects from one database to another, including constraints. However, the operation keeps failing. How do I find out the specific reason for the failure? Enterprise Manager does not provide specifics in the error message.
Thanks,
MarkIf you are using the wizard, you can find out more specifics by
double-clicking the line that says "error occurred"...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"mark" <mark@.discussions.microsoft.com> wrote in message
news:4FE6D4E5-7285-4A01-927D-B47C4F4C3AE6@.microsoft.com...
> Hi,
> I am attempting to import objects from one database to another, including
> constraints. However, the operation keeps failing. How do I find out the
> specific reason for the failure? Enterprise Manager does not provide
> specifics in the error message.
> Thanks,
> Mark

Error: Subreport could not be shown

I have a data dictionary report. It lists table & column definitions, then
has a subreport in the group footer to pull up dependent objects (the only
way I could get more than the first dependent object to list). The report
has parameters where you can choose to look at All Tables or individual
tables in the DB. It passes this param (@.tblName) to the subreport.
When I preview individual tables, the subreport works great! When I preview
"All Tables", the subreport works on some tables, and then has that error
listed after other tables. Even tables that it works on fine individually
can have this error message.
The data set for the subreport is called TableDepends and uses the following
code:
---
Declare @.objid int
If @.tblName <> 'All Tables'
Begin
Set @.objid = Object_ID(@.tblName)
if exists (select *
from sysdepends
where depid = @.objid)
begin
select distinct 'name' = (s.name + '.' + o.name), @.tblName as TableName,
type = substring(v.name, 5, 16)
from sysobjects o, master.dbo.spt_values v, sysdepends d,
sysusers s
where o.id = d.id
and o.xtype = substring(v.name,1,2) collate database_default
and v.type = 'O9T'
and d.depid = @.objid
and o.uid = s.uid
and deptype < 2
End
End
---
It's weird that this code works in some parts of the main report when I make
@.tblName = 'All Tables' in the main report, but then doesn't parse on other
parts of the main report. And I can't figure out how to do an ELSE clause
that still lists all table dependencies due to the whole Object_ID issue.
Any suggestions?
Thanks,Did a little more looking around. It appears that this error renders in the
Preview tab of Report Designer at Page 6 (and above) of 52 pages. The first
5 pages render correctly. So, I guess my question is whether or not the
Designer can handle all those re-renderings of the subreport.
On the first 5 pages, it doesn't matter if there's 1 dependency or many
dependencies, they all seem to render fine.
Thoughts?
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Catadmin" wrote:
> I have a data dictionary report. It lists table & column definitions, then
> has a subreport in the group footer to pull up dependent objects (the only
> way I could get more than the first dependent object to list). The report
> has parameters where you can choose to look at All Tables or individual
> tables in the DB. It passes this param (@.tblName) to the subreport.
> When I preview individual tables, the subreport works great! When I preview
> "All Tables", the subreport works on some tables, and then has that error
> listed after other tables. Even tables that it works on fine individually
> can have this error message.
> The data set for the subreport is called TableDepends and uses the following
> code:
> ---
> Declare @.objid int
>
> If @.tblName <> 'All Tables'
> Begin
> Set @.objid = Object_ID(@.tblName)
> if exists (select *
> from sysdepends
> where depid = @.objid)
> begin
> select distinct 'name' = (s.name + '.' + o.name), @.tblName as TableName,
> type = substring(v.name, 5, 16)
> from sysobjects o, master.dbo.spt_values v, sysdepends d,
> sysusers s
> where o.id = d.id
> and o.xtype = substring(v.name,1,2) collate database_default
> and v.type = 'O9T'
> and d.depid = @.objid
> and o.uid = s.uid
> and deptype < 2
> End
> End
> ---
> It's weird that this code works in some parts of the main report when I make
> @.tblName = 'All Tables' in the main report, but then doesn't parse on other
> parts of the main report. And I can't figure out how to do an ELSE clause
> that still lists all table dependencies due to the whole Object_ID issue.
> Any suggestions?
> Thanks,
>|||Further information:
Finally found the ReportServer Error Logs. Here are the error messages I'm
getting:
----
aspnet_wp!processing!12f4!5/12/2005-10:30:34:: w WARN: Data source
'APACS_BASE': Report processing has been aborted.
aspnet_wp!processing!12f4!5/12/2005-10:30:34:: e ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An
error has occurred during report processing., ;
Info:
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An
error has occurred during report processing. -->
System.InvalidOperationException: Invalid attempt to read when no data is
present.
at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at
Microsoft.ReportingServices.DataExtensions.DataReaderWrapper.GetOrdinal(String fieldName)
at
Microsoft.ReportingServices.DataExtensions.MappingDataReader..ctor(String
dataSetName, IDataReader sourceReader, String[] aliases, String[] fieldNames)
at Microsoft.ReportingServices.ReportProcessing.h..ctor(String A_0,
IDataReader A_1, String[] A_2, String[] A_3)
at Microsoft.ReportingServices.ReportProcessing.a1.v()
at Microsoft.ReportingServices.ReportProcessing.ax.c()
at Microsoft.ReportingServices.ReportProcessing.a1.a(Boolean& A_0)
at Microsoft.ReportingServices.ReportProcessing.ax.b()
at Microsoft.ReportingServices.ReportProcessing.a1.a(Object A_0)
-- End of inner exception stack trace --
----
This error comes up in the log for every time the subreport tries to print
and can't. Anyone have any ideas how to get around it?
Thanks!
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Catadmin" wrote:
> I have a data dictionary report. It lists table & column definitions, then
> has a subreport in the group footer to pull up dependent objects (the only
> way I could get more than the first dependent object to list). The report
> has parameters where you can choose to look at All Tables or individual
> tables in the DB. It passes this param (@.tblName) to the subreport.
> When I preview individual tables, the subreport works great! When I preview
> "All Tables", the subreport works on some tables, and then has that error
> listed after other tables. Even tables that it works on fine individually
> can have this error message.
> The data set for the subreport is called TableDepends and uses the following
> code:
> ---
> Declare @.objid int
>
> If @.tblName <> 'All Tables'
> Begin
> Set @.objid = Object_ID(@.tblName)
> if exists (select *
> from sysdepends
> where depid = @.objid)
> begin
> select distinct 'name' = (s.name + '.' + o.name), @.tblName as TableName,
> type = substring(v.name, 5, 16)
> from sysobjects o, master.dbo.spt_values v, sysdepends d,
> sysusers s
> where o.id = d.id
> and o.xtype = substring(v.name,1,2) collate database_default
> and v.type = 'O9T'
> and d.depid = @.objid
> and o.uid = s.uid
> and deptype < 2
> End
> End
> ---
> It's weird that this code works in some parts of the main report when I make
> @.tblName = 'All Tables' in the main report, but then doesn't parse on other
> parts of the main report. And I can't figure out how to do an ELSE clause
> that still lists all table dependencies due to the whole Object_ID issue.
> Any suggestions?
> Thanks,
>