Authentication problems

I am using form authentication to login into report service. When I execute
a report, the following error is displayed:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'DAISYS'.
(rsErrorOpeningConnection) Get Online Help
Unable to load DLL (oci.dll).
But if I use window authentication, it works fine.
Please help !!!Can someone help me ?
"May Liu" wrote:
> I am using form authentication to login into report service. When I execute
> a report, the following error is displayed:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'DAISYS'.
> (rsErrorOpeningConnection) Get Online Help
> Unable to load DLL (oci.dll).
> But if I use window authentication, it works fine.
> Please help !!!|||This sounds like a file permission issue with the installation of the Oracle
client software. The ASP worker process is unable to load OCI.dll and other
configuration settings which are stored in the Oracle client installation
directory.
For instance, the Oracle 9.2 client is typically installed at:
C:\oracle\ora92. When you use Windows authentication, it seems like the
users executing reports have "Read & Execute" permissions at least on
\oracle\ora92\bin and \oracle\ora92\network\admin directories - and
therefore the ASP.NET work process can access and load the Oracle client
software (dlls and configuration fiiles) from there.
When you use Forms authentication, most likely the ASP.NET worker process
will run under a user account that does *not* have explicit Read & Execute
permissions on the directories of the Oracle client software. Make sure
these rights are explicitly granted to all files (child objects) in those
directories (on Win2003 on the directory security tab you have to click on
the Advanced button and in the new popup window you have to select "Replace
permission entries on all child objects ..." and click OK).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:823537DA-85E8-4C71-8F0D-6CFA0EAE4182@.microsoft.com...
> Can someone help me ?
> "May Liu" wrote:
> > I am using form authentication to login into report service. When I
execute
> > a report, the following error is displayed:
> >
> > An error has occurred during report processing. (rsProcessingAborted)
Get
> > Online Help
> > Cannot create a connection to data source 'DAISYS'.
> > (rsErrorOpeningConnection) Get Online Help
> > Unable to load DLL (oci.dll).
> >
> > But if I use window authentication, it works fine.
> > Please help !!!|||It works. Thanks a lot !!!
"Robert Bruckner [MSFT]" wrote:
> This sounds like a file permission issue with the installation of the Oracle
> client software. The ASP worker process is unable to load OCI.dll and other
> configuration settings which are stored in the Oracle client installation
> directory.
> For instance, the Oracle 9.2 client is typically installed at:
> C:\oracle\ora92. When you use Windows authentication, it seems like the
> users executing reports have "Read & Execute" permissions at least on
> \oracle\ora92\bin and \oracle\ora92\network\admin directories - and
> therefore the ASP.NET work process can access and load the Oracle client
> software (dlls and configuration fiiles) from there.
> When you use Forms authentication, most likely the ASP.NET worker process
> will run under a user account that does *not* have explicit Read & Execute
> permissions on the directories of the Oracle client software. Make sure
> these rights are explicitly granted to all files (child objects) in those
> directories (on Win2003 on the directory security tab you have to click on
> the Advanced button and in the new popup window you have to select "Replace
> permission entries on all child objects ..." and click OK).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:823537DA-85E8-4C71-8F0D-6CFA0EAE4182@.microsoft.com...
> > Can someone help me ?
> >
> > "May Liu" wrote:
> >
> > > I am using form authentication to login into report service. When I
> execute
> > > a report, the following error is displayed:
> > >
> > > An error has occurred during report processing. (rsProcessingAborted)
> Get
> > > Online Help
> > > Cannot create a connection to data source 'DAISYS'.
> > > (rsErrorOpeningConnection) Get Online Help
> > > Unable to load DLL (oci.dll).
> > >
> > > But if I use window authentication, it works fine.
> > > Please help !!!
>
>sql

Authentication mode="none"

Has anyone gotten this to work?
I keep getting the following errors
w3wp!library!930!11/02/2004-14:43:34:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files, Could not load Authorization extension;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files
w3wp!webserver!930!11/02/2004-14:43:37:: e ERROR: Reporting Services error
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log filesI'll second that, and also throw in the call stack that makes it back to
UILogon.aspx:
at
Microsoft.ReportingServices.Diagnostics.AuthenticationExtensionFactory.get_AuthenticationExtension()
at
Microsoft.ReportingServices.Diagnostics.UserUtil.GetUserNameFromExtension()
at Microsoft.ReportingServices.Diagnostics.UserUtil.GetCurrentUserName()
at Microsoft.ReportingServices.WebServer.Global.ShouldRejectAntiDos()
at
Microsoft.ReportingServices.WebServer.Global.Application_AuthenticateRequest(Object sender, EventArgs e)"
Strangely, this says the error was thrown by get_AuthenticationExtension,
but the logged error is that the Authorization extension could not be loaded.
Anyone with any ideas on where to look to nail down the specifics of why
this is failing?
"lagalot" wrote:
> Has anyone gotten this to work?
> I keep getting the following errors
>
> w3wp!library!930!11/02/2004-14:43:34:: e ERROR: Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
> The Report Server has encountered a configuration error; more details in the
> log files, Could not load Authorization extension;
> Info:
> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
> The Report Server has encountered a configuration error; more details in the
> log files
> w3wp!webserver!930!11/02/2004-14:43:37:: e ERROR: Reporting Services error
> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
> The Report Server has encountered a configuration error; more details in the
> log files
>|||"Marc Lewandowski" wrote:
> I'll second that, and also throw in the call stack that makes it back to
> UILogon.aspx:
dont know if it helps but
for my deal i ended up doing something weird:
I set 2 virtuals. "ReportServer" (as per install), and a new one
"ReportsWeb", restricting ReportServer to only localhost (so the admin tools
still work) and setting ReportsWeb with IUSR browser role. This worked for
what i needed.
I've written a FAQ on this if any one is interested.|||Lagalot,
Well, it doesn't help, but that's not your fault. We were getting the same
error for the same underlying reason, but while trying to do very different
things.
The error arises because (apparently) RS requires a match between the
Authentication mode name in web.config and the Authentication Extension Name
in RSReportServer.config. You got the error because you changed the mode in
an effort to disable authentication entirely. I got the error because I
thought I could be creative and name my Authentication Extension something
other than "Forms."
So, FWIW, problem (sort of) solved.
-Marc
"lagalot" wrote:
> "Marc Lewandowski" wrote:
> dont know if it helps but
> for my deal i ended up doing something weird:
> I set 2 virtuals. "ReportServer" (as per install), and a new one
> "ReportsWeb", restricting ReportServer to only localhost (so the admin tools
> still work) and setting ReportsWeb with IUSR browser role. This worked for
> what i needed.
> I've written a FAQ on this if any one is interested.|||I am definitely interested. May I see the FAQ?
"lagalot" <lagalot@.discussions.microsoft.com> wrote in message
news:EECFB375-E975-4E61-9EBE-64C03B923192@.microsoft.com...
> "Marc Lewandowski" wrote:
>> I'll second that, and also throw in the call stack that makes it back to
>> UILogon.aspx:
> dont know if it helps but
> for my deal i ended up doing something weird:
> I set 2 virtuals. "ReportServer" (as per install), and a new one
> "ReportsWeb", restricting ReportServer to only localhost (so the admin
> tools
> still work) and setting ReportsWeb with IUSR browser role. This worked
> for
> what i needed.
> I've written a FAQ on this if any one is interested.|||May I please see this FAQ you have written? I believe it may help me
greatly.
"lagalot" <lagalot@.discussions.microsoft.com> wrote in message
news:EECFB375-E975-4E61-9EBE-64C03B923192@.microsoft.com...
> "Marc Lewandowski" wrote:
>> I'll second that, and also throw in the call stack that makes it back to
>> UILogon.aspx:
> dont know if it helps but
> for my deal i ended up doing something weird:
> I set 2 virtuals. "ReportServer" (as per install), and a new one
> "ReportsWeb", restricting ReportServer to only localhost (so the admin
> tools
> still work) and setting ReportsWeb with IUSR browser role. This worked
> for
> what i needed.
> I've written a FAQ on this if any one is interested.

Authentication methods for connections to SQL Server in ASP Pages

I am trying to access SQL Server via an ASP page using a Trusted Connection,
but it is not working. When I run the page I receive the following error
message:
Microsoft OLE DB Service Components error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status
value, if available. No work was done.
line 35
My connection string is in a separate file: cst = "data
source=X099789\Widgets;Initial Catalog=Automotive; Integrated Security=SSPI;
"
My code snipet looks like the following:
set OBJRST = Server.CreateObject("ADODB.Recordset")
Set objComm = Server.CreateObject("ADODB.Command")
objComm.ActiveConnection = cst '****LIne 35
MotorsSQL = "usp_MotorAll"
UIPWSQL = "usp_UIPW '" & struserid &"', '"& strpassword &"';"
objConnAll.open cst
What is the correct coding to connect to the SQL Server using Windows
Authentication in an ASP page?
I have read the instructions on:
http://support.microsoft.com/default.aspx/kb/247931, made the changes,
however the page still will not work.
Kindly assist. I will be thankful.
AuntieAuntieAuntie> Microsoft OLE DB Service Components error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
These errors are probably because there is no 'Provider' keyword in your
OLEDB connection string. Try adding 'Provider=SQLOLEDB'.
Hope this helps.
Dan Guzman
SQL Server MVP
"AuntieAuntieAuntie" <AuntieAuntieAuntie@.discussions.microsoft.com> wrote in
message news:EE4C5259-1F64-4893-9AF1-657A1C03917B@.microsoft.com...
>I am trying to access SQL Server via an ASP page using a Trusted
>Connection,
> but it is not working. When I run the page I receive the following error
> message:
> Microsoft OLE DB Service Components error '80040e21'
> Multiple-step OLE DB operation generated errors. Check each OLE DB status
> value, if available. No work was done.
> line 35
> My connection string is in a separate file: cst = "data
> source=X099789\Widgets;Initial Catalog=Automotive; Integrated
> Security=SSPI;"
> My code snipet looks like the following:
> set OBJRST = Server.CreateObject("ADODB.Recordset")
> Set objComm = Server.CreateObject("ADODB.Command")
> objComm.ActiveConnection = cst '****LIne 35
> MotorsSQL = "usp_MotorAll"
> UIPWSQL = "usp_UIPW '" & struserid &"', '"& strpassword &"';"
> objConnAll.open cst
> What is the correct coding to connect to the SQL Server using Windows
> Authentication in an ASP page?
> I have read the instructions on:
> http://support.microsoft.com/default.aspx/kb/247931, made the changes,
> however the page still will not work.
> Kindly assist. I will be thankful.
> AuntieAuntieAuntie
>

Authentication and authorization in MS Reports 2000

Hi,
I am Strug'ling from 1 week. Please help me with following problem.
1. I am developing ASP.net, C# web application.
2. I am using MS Reporting Services 2000 for Report Generation.
3. I am using Report Viewer Control for Displaying the Reports into my web
application.
4. I am able to see my all reports in report viewer control but for that
either i have to have Anonymous access true in IIS Or I have to add that user
to Report Server.
My problem :-
I don't want to use Anonymous access, because then it will become public.
I use form authentication in My application and based on this i want to
validate the report to be seen to user.
So how do i do this.
Please help me.
Thanks in advance.
Labhesh Shrimali
BangaloreReporting services by default using Windows authentication as you have
discovered. If you want to use forms authentication you have to use the
security extensions which allow you to authenticate the users rather than
Reporting Services. Here is a link to start off with.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Labhesh Shrimali - Bangalore"
<LabheshShrimaliBangalore@.discussions.microsoft.com> wrote in message
news:B2053588-9CAF-4FA0-9F65-1B5621FAD01A@.microsoft.com...
> Hi,
> I am Strug'ling from 1 week. Please help me with following problem.
> 1. I am developing ASP.net, C# web application.
> 2. I am using MS Reporting Services 2000 for Report Generation.
> 3. I am using Report Viewer Control for Displaying the Reports into my web
> application.
> 4. I am able to see my all reports in report viewer control but for that
> either i have to have Anonymous access true in IIS Or I have to add that
> user
> to Report Server.
> My problem :-
> I don't want to use Anonymous access, because then it will become public.
> I use form authentication in My application and based on this i want to
> validate the report to be seen to user.
> So how do i do this.
> Please help me.
> Thanks in advance.
> Labhesh Shrimali
> Bangalore
>sql

Auditing Reports

We have a many reports in many folders. We are now at the point that we need
some kind of tool to audit all these reports for following:
Security
connectionStrings
Data Sources
ReportLocations
I saw in ReportServer database there are all kind of tables which has all of
these information, but I was wondering if there is a solution out there to
query those tables to achive this goal.
Thanks
VipulHi,
Ofcourse you can use those system tables, but with care. each table is
useful for collecting information. Infact the table name are self explanatory
and you can query it for info.
Amarnath
"Vipul Shah" wrote:
> We have a many reports in many folders. We are now at the point that we need
> some kind of tool to audit all these reports for following:
> Security
> connectionStrings
> Data Sources
> ReportLocations
> I saw in ReportServer database there are all kind of tables which has all of
> these information, but I was wondering if there is a solution out there to
> query those tables to achive this goal.
> Thanks
> Vipul
>|||Do you know of any available sample queries to query those tables?
"Amarnath" wrote:
> Hi,
> Ofcourse you can use those system tables, but with care. each table is
> useful for collecting information. Infact the table name are self explanatory
> and you can query it for info.
> Amarnath
>
> "Vipul Shah" wrote:
> > We have a many reports in many folders. We are now at the point that we need
> > some kind of tool to audit all these reports for following:
> > Security
> > connectionStrings
> > Data Sources
> > ReportLocations
> >
> > I saw in ReportServer database there are all kind of tables which has all of
> > these information, but I was wondering if there is a solution out there to
> > query those tables to achive this goal.
> >
> > Thanks
> > Vipul
> >

audit tables with composite keys

I am trying to write triggers on each tables in my database to audit data changes. My AuditLog table consists of the following columns -

LoginName varchar(100) - user name

Action varchar(5) - this will store 'INSERT','UPDATE','DELETE'

TableName varchar(30) - name of the table updated

PrimaryKey int - primary key of the record updated

ColumnName varchar(30) - name of the column updated

OldValue varchar(1000) - old value converted to varchar

NewValue varchar(1000) - new value converted to varchar

RecUpdDate datetime - record update date.

This table design will work for tables with single column primary keys. However, it will not work for tables with composite primary keys. Any suggestions on how to make this work with composite primary keys? I prefer not to change the tables in my database to use single column primary key.

Thanks in advance.

I had same kind of audit table. But i had PrimaryKey column as varchar. and we concatinate primarykeys of the table and insert in this table.

Madhu

|||

I had same kind of audit table. But i had PrimaryKey column as varchar. and we concatinate primarykeys of the table with a delimitter and insert in this table.

Madhu

|||

If you concatinate the primary keys, you need to make sure it's done is specific column order. If you want to use the primary key to locate the record in the data table, then you need to parse the string first. Do you find any performance issues with this method?

audit tables with composite keys

I am trying to write triggers on each tables in my database to audit data changes. My AuditLog table consists of the following columns -

LoginName varchar(100) - user name

Action varchar(5) - this will store 'INSERT','UPDATE','DELETE'

TableName varchar(30) - name of the table updated

PrimaryKey int - primary key of the record updated

ColumnName varchar(30) - name of the column updated

OldValue varchar(1000) - old value converted to varchar

NewValue varchar(1000) - new value converted to varchar

RecUpdDate datetime - record update date.

This table design will work for tables with single column primary keys. However, it will not work for tables with composite primary keys. Any suggestions on how to make this work with composite primary keys? I prefer not to change the tables in my database to use single column primary key.

Thanks in advance.

I had same kind of audit table. But i had PrimaryKey column as varchar. and we concatinate primarykeys of the table and insert in this table.

Madhu

|||

I had same kind of audit table. But i had PrimaryKey column as varchar. and we concatinate primarykeys of the table with a delimitter and insert in this table.

Madhu

|||

If you concatinate the primary keys, you need to make sure it's done is specific column order. If you want to use the primary key to locate the record in the data table, then you need to parse the string first. Do you find any performance issues with this method?

Attributes of Database

Hi,
Does anybody know how to get the following information for a particular
database in SQL 2000
1. Whether it is a System Object or not.
2. Create for Attach
3. Replication Status
We can get this values through SQL-DMO, but can I get these values from some
system tables or in-built functions
TIA
PrasadTake a look at OBJECTPROPERTY ( id , property ) command in the BOL
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Hi Prasad
You can use the DATABASEPROPERTYEX function to check the database
replication status :
SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
To test if the db is a system database, just check the name. There is only a
short list of 'system databases' (master, model, tempdb, msdb, distribution)
and if it's not one of the known ones, it's not a system database.
To check if the database was created for attach is not possible. Once the
database is created, it doesn't retain history as to how it was created. It
is equal to all other databases. Why do you want to know this?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Thanks Kalen
Isn't there a more cleaner way of finding the system databases, comparing
the names would mean hard-coding the stuff.
and regarding the "created for attach" field bcoz SQL-DMO returns this value
I also wanted to show the same.
Thanks
Prasad
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23jMoCSYMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi Prasad
> You can use the DATABASEPROPERTYEX function to check the database
> replication status :
> SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
> To test if the db is a system database, just check the name. There is only
> a short list of 'system databases' (master, model, tempdb, msdb,
> distribution) and if it's not one of the known ones, it's not a system
> database.
> To check if the database was created for attach is not possible. Once the
> database is created, it doesn't retain history as to how it was created.
> It is equal to all other databases. Why do you want to know this?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
>
>|||For an alternative look at the sysdatabases table. The sid column stores the
System ID of the database creator - its the same "fake" value for each syste
m
database created at install.
I'd hard-code the names, though.
ML
http://milambda.blogspot.com/|||Thanks
But its not fake sid its the sid for "sa" which means suppose if a new
database is created by "sa" it would also have the same sid.
Thanks
Prasad
"ML" <ML@.discussions.microsoft.com> wrote in message
news:01649392-978B-4C06-889F-8EE342A5C7E1@.microsoft.com...
> For an alternative look at the sysdatabases table. The sid column stores
> the
> System ID of the database creator - its the same "fake" value for each
> system
> database created at install.
> I'd hard-code the names, though.
>
> ML
> --
> http://milambda.blogspot.com/|||You're right. Sorry. What was I thinking...?
ML
http://milambda.blogspot.com/|||Hi, Prasad

> Isn't there a more cleaner way of finding the system databases,
> comparing the names would mean hard-coding the stuff.
I don't know any other way; AFAIK, Enterprise Manager and Management
Studio are doing the same thing.
[vbcol=seagreen]
> and regarding the "created for attach" field bcoz SQL-DMO returns this value[/vbco
l]
The CreateForAttach property in SQL-DMO is used to specify how the
database will be created (before appending the Database object to the
Databases collection).
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015795.987952.21330@.f14g2000cwb.googlegroups.com...
> Hi, Prasad
>
> I don't know any other way; AFAIK, Enterprise Manager and Management
> Studio are doing the same thing.
>
> The CreateForAttach property in SQL-DMO is used to specify how the
> database will be created (before appending the Database object to the
> Databases collection).
> Razvan
>

Attributes of Database

Hi,
Does anybody know how to get the following information for a particular
database in SQL 2000
1. Whether it is a System Object or not.
2. Create for Attach
3. Replication Status
We can get this values through SQL-DMO, but can I get these values from some
system tables or in-built functions
TIA
Prasad
Take a look at OBJECTPROPERTY ( id , property ) command in the BOL
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>
|||Hi Prasad
You can use the DATABASEPROPERTYEX function to check the database
replication status :
SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
To test if the db is a system database, just check the name. There is only a
short list of 'system databases' (master, model, tempdb, msdb, distribution)
and if it's not one of the known ones, it's not a system database.
To check if the database was created for attach is not possible. Once the
database is created, it doesn't retain history as to how it was created. It
is equal to all other databases. Why do you want to know this?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>
|||Thanks Kalen
Isn't there a more cleaner way of finding the system databases, comparing
the names would mean hard-coding the stuff.
and regarding the "created for attach" field bcoz SQL-DMO returns this value
I also wanted to show the same.
Thanks
Prasad
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23jMoCSYMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi Prasad
> You can use the DATABASEPROPERTYEX function to check the database
> replication status :
> SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
> To test if the db is a system database, just check the name. There is only
> a short list of 'system databases' (master, model, tempdb, msdb,
> distribution) and if it's not one of the known ones, it's not a system
> database.
> To check if the database was created for attach is not possible. Once the
> database is created, it doesn't retain history as to how it was created.
> It is equal to all other databases. Why do you want to know this?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
>
>
|||For an alternative look at the sysdatabases table. The sid column stores the
System ID of the database creator - its the same "fake" value for each system
database created at install.
I'd hard-code the names, though.
ML
http://milambda.blogspot.com/
|||Thanks
But its not fake sid its the sid for "sa" which means suppose if a new
database is created by "sa" it would also have the same sid.
Thanks
Prasad
"ML" <ML@.discussions.microsoft.com> wrote in message
news:01649392-978B-4C06-889F-8EE342A5C7E1@.microsoft.com...
> For an alternative look at the sysdatabases table. The sid column stores
> the
> System ID of the database creator - its the same "fake" value for each
> system
> database created at install.
> I'd hard-code the names, though.
>
> ML
> --
> http://milambda.blogspot.com/
|||You're right. Sorry. What was I thinking...?
ML
http://milambda.blogspot.com/
|||Hi, Prasad

> Isn't there a more cleaner way of finding the system databases,
> comparing the names would mean hard-coding the stuff.
I don't know any other way; AFAIK, Enterprise Manager and Management
Studio are doing the same thing.

> and regarding the "created for attach" field bcoz SQL-DMO returns this value
The CreateForAttach property in SQL-DMO is used to specify how the
database will be created (before appending the Database object to the
Databases collection).
Razvan
|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015795.987952.21330@.f14g2000cwb.googlegro ups.com...
> Hi, Prasad
>
> I don't know any other way; AFAIK, Enterprise Manager and Management
> Studio are doing the same thing.
>
> The CreateForAttach property in SQL-DMO is used to specify how the
> database will be created (before appending the Database object to the
> Databases collection).
> Razvan
>

Attributes of Database

Hi,
Does anybody know how to get the following information for a particular
database in SQL 2000
1. Whether it is a System Object or not.
2. Create for Attach
3. Replication Status
We can get this values through SQL-DMO, but can I get these values from some
system tables or in-built functions
TIA
PraTake a look at OBJECTPROPERTY ( id , property ) command in the BOL
"Pra" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Pra
>|||Hi Pra
You can use the DATABASEPROPERTYEX function to check the database
replication status :
SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
To test if the db is a system database, just check the name. There is only a
short list of 'system databases' (master, model, tempdb, msdb, distribution)
and if it's not one of the known ones, it's not a system database.
To check if the database was created for attach is not possible. Once the
database is created, it doesn't retain history as to how it was created. It
is equal to all other databases. Why do you want to know this?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pra" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Pra
>|||Thanks Kalen
Isn't there a more cleaner way of finding the system databases, comparing
the names would mean hard-coding the stuff.
and regarding the "created for attach" field bcoz SQL-DMO returns this value
I also wanted to show the same.
Thanks
Pra
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23jMoCSYMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi Pra
> You can use the DATABASEPROPERTYEX function to check the database
> replication status :
> SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
> To test if the db is a system database, just check the name. There is only
> a short list of 'system databases' (master, model, tempdb, msdb,
> distribution) and if it's not one of the known ones, it's not a system
> database.
> To check if the database was created for attach is not possible. Once the
> database is created, it doesn't retain history as to how it was created.
> It is equal to all other databases. Why do you want to know this?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Pra" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
>
>|||For an alternative look at the sysdatabases table. The sid column stores the
System ID of the database creator - its the same "fake" value for each syste
m
database created at install.
I'd hard-code the names, though.
ML
http://milambda.blogspot.com/|||Thanks
But its not fake sid its the sid for "sa" which means suppose if a new
database is created by "sa" it would also have the same sid.
Thanks
Pra
"ML" <ML@.discussions.microsoft.com> wrote in message
news:01649392-978B-4C06-889F-8EE342A5C7E1@.microsoft.com...
> For an alternative look at the sysdatabases table. The sid column stores
> the
> System ID of the database creator - its the same "fake" value for each
> system
> database created at install.
> I'd hard-code the names, though.
>
> ML
> --
> http://milambda.blogspot.com/|||You're right. Sorry. What was I thinking...?
ML
http://milambda.blogspot.com/|||Hi, Pra

> Isn't there a more cleaner way of finding the system databases,
> comparing the names would mean hard-coding the stuff.
I don't know any other way; AFAIK, Enterprise Manager and Management
Studio are doing the same thing.
> and regarding the "created for attach" field bcoz SQL-DMO returns this value[/colo
r]
The CreateForAttach property in SQL-DMO is used to specify how the
database will be created (before appending the Database object to the
Databases collection).
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015795.987952.21330@.f14g2000cwb.googlegroups.com...
> Hi, Pra
>
> I don't know any other way; AFAIK, Enterprise Manager and Management
> Studio are doing the same thing.
>
> The CreateForAttach property in SQL-DMO is used to specify how the
> database will be created (before appending the Database object to the
> Databases collection).
> Razvan
>

Attributes of Database

Hi,
Does anybody know how to get the following information for a particular
database in SQL 2000
1. Whether it is a System Object or not.
2. Create for Attach
3. Replication Status
We can get this values through SQL-DMO, but can I get these values from some
system tables or in-built functions
TIA
PrasadTake a look at OBJECTPROPERTY ( id , property ) command in the BOL
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Hi Prasad
You can use the DATABASEPROPERTYEX function to check the database
replication status :
SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
To test if the db is a system database, just check the name. There is only a
short list of 'system databases' (master, model, tempdb, msdb, distribution)
and if it's not one of the known ones, it's not a system database.
To check if the database was created for attach is not possible. Once the
database is created, it doesn't retain history as to how it was created. It
is equal to all other databases. Why do you want to know this?
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Does anybody know how to get the following information for a particular
> database in SQL 2000
> 1. Whether it is a System Object or not.
> 2. Create for Attach
> 3. Replication Status
> We can get this values through SQL-DMO, but can I get these values from
> some system tables or in-built functions
> TIA
> Prasad
>|||Thanks Kalen
Isn't there a more cleaner way of finding the system databases, comparing
the names would mean hard-coding the stuff.
and regarding the "created for attach" field bcoz SQL-DMO returns this value
I also wanted to show the same.
Thanks
Prasad
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23jMoCSYMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Hi Prasad
> You can use the DATABASEPROPERTYEX function to check the database
> replication status :
> SELECT DATABASEPROPERTYEX ('database_name, 'IsPublished')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsSubscribed')
> SELECT DATABASEPROPERTYEX ('database_name, 'IsMergePublished')
> To test if the db is a system database, just check the name. There is only
> a short list of 'system databases' (master, model, tempdb, msdb,
> distribution) and if it's not one of the known ones, it's not a system
> database.
> To check if the database was created for attach is not possible. Once the
> database is created, it doesn't retain history as to how it was created.
> It is equal to all other databases. Why do you want to know this?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23RrjJaVMGHA.648@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> Does anybody know how to get the following information for a particular
>> database in SQL 2000
>> 1. Whether it is a System Object or not.
>> 2. Create for Attach
>> 3. Replication Status
>> We can get this values through SQL-DMO, but can I get these values from
>> some system tables or in-built functions
>> TIA
>> Prasad
>>
>
>|||For an alternative look at the sysdatabases table. The sid column stores the
System ID of the database creator - its the same "fake" value for each system
database created at install.
I'd hard-code the names, though.
ML
--
http://milambda.blogspot.com/|||Thanks
But its not fake sid its the sid for "sa" which means suppose if a new
database is created by "sa" it would also have the same sid.
Thanks
Prasad
"ML" <ML@.discussions.microsoft.com> wrote in message
news:01649392-978B-4C06-889F-8EE342A5C7E1@.microsoft.com...
> For an alternative look at the sysdatabases table. The sid column stores
> the
> System ID of the database creator - its the same "fake" value for each
> system
> database created at install.
> I'd hard-code the names, though.
>
> ML
> --
> http://milambda.blogspot.com/|||You're right. Sorry. What was I thinking...?
ML
--
http://milambda.blogspot.com/|||Hi, Prasad
> Isn't there a more cleaner way of finding the system databases,
> comparing the names would mean hard-coding the stuff.
I don't know any other way; AFAIK, Enterprise Manager and Management
Studio are doing the same thing.
> and regarding the "created for attach" field bcoz SQL-DMO returns this value
The CreateForAttach property in SQL-DMO is used to specify how the
database will be created (before appending the Database object to the
Databases collection).
Razvan|||Thanks Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1140015795.987952.21330@.f14g2000cwb.googlegroups.com...
> Hi, Prasad
>> Isn't there a more cleaner way of finding the system databases,
>> comparing the names would mean hard-coding the stuff.
> I don't know any other way; AFAIK, Enterprise Manager and Management
> Studio are doing the same thing.
>> and regarding the "created for attach" field bcoz SQL-DMO returns this
>> value
> The CreateForAttach property in SQL-DMO is used to specify how the
> database will be created (before appending the Database object to the
> Databases collection).
> Razvan
>

Attribute Relationships, Related Attributes, Uniqueness

I haven't see a clear example of how to setup the following for efficient aggregations:

Example 1:

If I have a Hierarchy like:

Year->Month->Day

Its clear how to setup the attribute relationships as long as the Month members are all unique. Can I somehow include an Attribute like WeekendIndicator? Weekend certainly isn't unique, but it would seem there should be a way to roll this up and include it in the attribute relationships.

Example 2:

In many of my dimensions, I have Hierarchies whose Name member is not unique, but there is an Id value that is unique. I want to build the relationships using the Ids since I know the aggregations will be good...the problem is, that a program querying the data would use the IDs, but a person writing a report would want to use the Name attributes. Is there a way to link the two together so that the non-unique name members are tied to the Id fields. The User Defined Hierarchy define on names works fine from a UI perspective, but I want to make sure I have aggregations as well.

For example, if I have a Campaign dimension, and users frequently want to navigate these for rollups:

Account -> MarketingProgram->MarketingCampaign

The fact is an Order

The Program and Campaign names can be anything, but their IDs are enforced to be unique. How do I manage these together?

Example 3:

And finally, if I want to do rollups with the following types of attributes

AccountStatus->AccountCategory->Account

I can't build an attribute relationship for this since Account Category does not imply Account Status since all accounts of a certain category are not guaranteed to be within an Account Status. How do I efficiently build aggregations for this type of hierarchy? The User Defined Hierarchy allows the customer to navigate it, but the aggregations don't allow it.

Thanks for any insight!

Example 1

Create a new attribute WeekeendIndicator and use it to filter the time dimension.

Example 2

One way to resolve this by adding the name and id in a view or Data Source View as a calculated value, e.g.
Market Development Fund (1)
Market Development Fund (2)
Market Development Fund (3)

Example 3

If the hierarchy is enforced (or a strong hierarchy) such as the time dimension, then aggregations can roll up.

Hope this helps.

|||

That's a solution , but not quite what I want to do.

Example 1 -> Is there a way to pre-aggregate this information with attribute relationships?

Example 2 -> I know I can generate unique names, but is there a way to "pair" attributes together so I can leverage the Ids for the aggregation but see the names elsewhere

Example 3 -> The issue is the hierarchy is not enforced, so is there no option for attribute relationships of any kind, is there another way I should model this?

Attribute Relationship ?

I think understand how and why we need to setup attribute relationship but I'm probably missing one important thing here...

If I have the following hierarchies in my dimension:

Circulaire > Segment > Promotion

Circulaire > Promotion

Logically I should have defined my attribute relationship like this:

Circulaire

Segment

- Circulaire

Promotion

- Segment

- Circulaire

Promotion Key

- Promotion

This result in the following error:

This dimension contains one or more redundant attribute relationships. These relationships may prevent data from being aggregated when a non-key attribute is used as a granularity attribute in a cube. Verify the following relationships and delete those that are not needed: [Code Promotion] -> [Promotion - Circulaire].

What is the best practice to manage those issues with attribute relationship? I want to make sure that all my hierarchies are designed for best performance.

On the same note how should we set-up attribute relationship when multiple hierarchies are using the same level in different order?

LEVEL 1 > LEVEL 2 > LEVEL 3 > LEVEL 4

LEVEL 1 > LEVEL 3 > LEVEL 2 > LEVEL 4

thanks,

In the 1st scenario, since Segment directly relates to Promotion and Circulaire directly relates to Segment, relating Circulaire to Promotion is redundant. This is similar to a Year->Month->Day hierarchy, where relating Year to Day directly would be redundant.

In the 2nd scenario, I assume that there are 2 alternate hierarchies for user navigation. They can't both be natural (strong) hierachies (unless there is a strict 1:1 relationship between Level 2 and Level 3 members). So attribute relationships should only reflect strict functional dependencies, not navigational convenience. This paper discusses attribute relationships in more detail:

http://www.sqlserveranalysisservices.com/OLAPPapers/AttributeRelationships.htm

|||Thank you

attempted to divide by zero

hi,

i had this formula written for a textbox in a table, but yet still encounter the following error:

expression:

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/countdistinct(Fields!room.Value))

error:

attempted to divide by zero.

any way i can solve this problem?

thanks!

IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible. Try the following expression instead:
=IIf( CountDistinct(Fields!room.Value) = 0, 0, Sum(Fields!rate.Value) / iif(CountDistinct(Fields!room.Value) = 0, 1, CountDistinct(Fields!room.Value)))

In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))

You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.

-- Robert

|||thanks a lot, Robert.|||

Hello Robert,

Thanks for this post.This helps me a lot.

I have tried on many sites to get help but not getting much

Thanks again

Sunil Pawar.

|||This worked for me good work on this
|||It would be good if we had a VBA function to do this. This is a common requirement. And a time waster until I found this post.|||

HI Everyone,

I generally use the following statement

=IIF(Fields!Profit.Value<>0, Fields!Profit.Value/ Fields!Sales.Value, Nothing)

For the most part this formula is simple and effective...

BUT (there is always a but!!)

I received an error message "attempted to divide by zero". I checked the tables to validate column formatting and everything appears to be okay (decimals(11,2) on both columns. If any one has any suggestions, it would be greatly appreciated.

Regards,

A.Akin

|||Hi,
Please try out with this formula,

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/IIF(countdistinct(Fields!room.Value))=0,1,countdistinct(Fields!room.Value))

I think it will work.

Cheers,

Shri

|||Can Someone help me with this one I have the same issue.
= IIF(Fields!Cash_Resolution.Value = 0 Or Fields!Amount.Value = 0,0,
Sum(Fields!Cash_Resolution.Value)/ Sum(Fields!Amount.Value))

attempted to divide by zero

hi,

i had this formula written for a textbox in a table, but yet still encounter the following error:

expression:

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/countdistinct(Fields!room.Value))

error:

attempted to divide by zero.

any way i can solve this problem?

thanks!

IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible. Try the following expression instead:
=IIf( CountDistinct(Fields!room.Value) = 0, 0, Sum(Fields!rate.Value) / iif(CountDistinct(Fields!room.Value) = 0, 1, CountDistinct(Fields!room.Value)))

In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))

You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.

-- Robert

|||thanks a lot, Robert.|||

Hello Robert,

Thanks for this post.This helps me a lot.

I have tried on many sites to get help but not getting much

Thanks again

Sunil Pawar.

|||This worked for me good work on this
|||It would be good if we had a VBA function to do this. This is a common requirement. And a time waster until I found this post.|||

HI Everyone,

I generally use the following statement

=IIF(Fields!Profit.Value<>0, Fields!Profit.Value/ Fields!Sales.Value, Nothing)

For the most part this formula is simple and effective...

BUT (there is always a but!!)

I received an error message "attempted to divide by zero". I checked the tables to validate column formatting and everything appears to be okay (decimals(11,2) on both columns. If any one has any suggestions, it would be greatly appreciated.

Regards,

A.Akin

|||Hi,
Please try out with this formula,

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/IIF(countdistinct(Fields!room.Value))=0,1,countdistinct(Fields!room.Value))

I think it will work.

Cheers,

Shri

attempted to divide by zero

hi,

i had this formula written for a textbox in a table, but yet still encounter the following error:

expression:

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/countdistinct(Fields!room.Value))

error:

attempted to divide by zero.

any way i can solve this problem?

thanks!

IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible. Try the following expression instead:
=IIf( CountDistinct(Fields!room.Value) = 0, 0, Sum(Fields!rate.Value) / iif(CountDistinct(Fields!room.Value) = 0, 1, CountDistinct(Fields!room.Value)))

In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))

You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.

-- Robert

|||thanks a lot, Robert.|||

Hello Robert,

Thanks for this post.This helps me a lot.

I have tried on many sites to get help but not getting much

Thanks again

Sunil Pawar.

|||This worked for me good work on this|||It would be good if we had a VBA function to do this. This is a common requirement. And a time waster until I found this post.|||

HI Everyone,

I generally use the following statement

=IIF(Fields!Profit.Value<>0, Fields!Profit.Value/ Fields!Sales.Value, Nothing)

For the most part this formula is simple and effective...

BUT (there is always a but!!)

I received an error message "attempted to divide by zero". I checked the tables to validate column formatting and everything appears to be okay (decimals(11,2) on both columns. If any one has any suggestions, it would be greatly appreciated.

Regards,

A.Akin

|||Hi,
Please try out with this formula,

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/IIF(countdistinct(Fields!room.Value))=0,1,countdistinct(Fields!room.Value))

I think it will work.

Cheers,

Shri

Attempted to divide by zero

I have the following expression as a Calculated Field:
=IIf(Fields!PROJ_Y.Value <> 0 Or Not Fields!PROJ_Y.Value Is Nothing,
((Fields!PROJ_Y.Value-Fields!ACTUAL_Y1.Value)/Fields!PROJ_Y.Value),
0)
and I get the error "The Value expression for the field â'GROWTH_Yâ' contains
an error: Attempted to divide by zero." when I run the report. Obviously the
If statement is written to avoid the division by zero, so I am not sure how
this would happen.
BJI was able to resolved this issue by writing a custom function to handle the
division instead of the IIf statement.
BJ
"bjkaledas" wrote:
> I have the following expression as a Calculated Field:
> =IIf(Fields!PROJ_Y.Value <> 0 Or Not Fields!PROJ_Y.Value Is Nothing,
> ((Fields!PROJ_Y.Value-Fields!ACTUAL_Y1.Value)/Fields!PROJ_Y.Value),
> 0)
> and I get the error "The Value expression for the field â'GROWTH_Yâ' contains
> an error: Attempted to divide by zero." when I run the report. Obviously the
> If statement is written to avoid the division by zero, so I am not sure how
> this would happen.
> BJ|||I think you just needed to replace the 'Or' with an 'And'
~ Magendo_man
"bjkaledas" wrote:
> I was able to resolved this issue by writing a custom function to handle the
> division instead of the IIf statement.
> BJ
> "bjkaledas" wrote:
> > I have the following expression as a Calculated Field:
> >
> > =IIf(Fields!PROJ_Y.Value <> 0 Or Not Fields!PROJ_Y.Value Is Nothing,
> > ((Fields!PROJ_Y.Value-Fields!ACTUAL_Y1.Value)/Fields!PROJ_Y.Value),
> > 0)
> >
> > and I get the error "The Value expression for the field â'GROWTH_Yâ' contains
> > an error: Attempted to divide by zero." when I run the report. Obviously the
> > If statement is written to avoid the division by zero, so I am not sure how
> > this would happen.
> >
> > BJ

attempted to divide by zero

hi,

i had this formula written for a textbox in a table, but yet still encounter the following error:

expression:

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/countdistinct(Fields!room.Value))

error:

attempted to divide by zero.

any way i can solve this problem?

thanks!

IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible. Try the following expression instead:
=IIf( CountDistinct(Fields!room.Value) = 0, 0, Sum(Fields!rate.Value) / iif(CountDistinct(Fields!room.Value) = 0, 1, CountDistinct(Fields!room.Value)))

In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))

You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.

-- Robert

|||thanks a lot, Robert.|||

Hello Robert,

Thanks for this post.This helps me a lot.

I have tried on many sites to get help but not getting much

Thanks again

Sunil Pawar.

|||This worked for me good work on this|||It would be good if we had a VBA function to do this. This is a common requirement. And a time waster until I found this post.|||

HI Everyone,

I generally use the following statement

=IIF(Fields!Profit.Value<>0, Fields!Profit.Value/ Fields!Sales.Value, Nothing)

For the most part this formula is simple and effective...

BUT (there is always a but!!)

I received an error message "attempted to divide by zero". I checked the tables to validate column formatting and everything appears to be okay (decimals(11,2) on both columns. If any one has any suggestions, it would be greatly appreciated.

Regards,

A.Akin

|||Hi,
Please try out with this formula,

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/IIF(countdistinct(Fields!room.Value))=0,1,countdistinct(Fields!room.Value))

I think it will work.

Cheers,

Shri

attempted to divide by zero

hi,

i had this formula written for a textbox in a table, but yet still encounter the following error:

expression:

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/countdistinct(Fields!room.Value))

error:

attempted to divide by zero.

any way i can solve this problem?

thanks!

IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible. Try the following expression instead:
=IIf( CountDistinct(Fields!room.Value) = 0, 0, Sum(Fields!rate.Value) / iif(CountDistinct(Fields!room.Value) = 0, 1, CountDistinct(Fields!room.Value)))

In general, you want a pattern like this to avoid division by zero:
=iif(B=0, 0, A / iif(B=0, 1, B))

You could also define a generic DivideXByY function in the custom code section of the report that uses IF-ELSE-ENDIF statements (instead of the IIF function call) to perform the division and avoid the DivisionByZero exception.

-- Robert

|||thanks a lot, Robert.|||

Hello Robert,

Thanks for this post.This helps me a lot.

I have tried on many sites to get help but not getting much

Thanks again

Sunil Pawar.

|||This worked for me good work on this
|||It would be good if we had a VBA function to do this. This is a common requirement. And a time waster until I found this post.|||

HI Everyone,

I generally use the following statement

=IIF(Fields!Profit.Value<>0, Fields!Profit.Value/ Fields!Sales.Value, Nothing)

For the most part this formula is simple and effective...

BUT (there is always a but!!)

I received an error message "attempted to divide by zero". I checked the tables to validate column formatting and everything appears to be okay (decimals(11,2) on both columns. If any one has any suggestions, it would be greatly appreciated.

Regards,

A.Akin

|||Hi,
Please try out with this formula,

=iif(countdistinct(Fields!room.Value)=0,0, sum(Fields!rate.Value)/IIF(countdistinct(Fields!room.Value))=0,1,countdistinct(Fields!room.Value))

I think it will work.

Cheers,

Shri

Attempted to divide by zero

Hello.
I'm having a little bit of a problem and i have no clue what's going on.
May be somebody can explain me how the following expression could generate the
"Attempted to divide by zero" error. I would really appreciate good advice.

Briefly about report itself - one dataset, on single table with three groups
this is a thid one. Grouping works just fine, but SUM/SUM in a footer of group #3
gives an error. Datatypes: JTD_Hours is decimal(18,2), JTD_Dollars is money.
None of the fields is NULL, all nulls converted to Zeros on dataset level.
Dataset created as result of stored procedure.

Here it is:
=IIf( Sum(Fields!JTD_Hours.Value,"table1_CostCode") = 0, 0, Sum(Fields!JTD_Dollars.Value,"table1_CostCode")/Sum(Fields!JTD_Hours.Value,"table1_CostCode"))

Thanks,
Konstantin

IIF is a function call which evaluates all arguments before it executes. Hence, given your expression a division by zero is possible.

Try the following expression instead:
=IIf( Sum(Fields!JTD_Hours.Value,"table1_CostCode") = 0, 0, Sum(Fields!JTD_Dollars.Value,"table1_CostCode") / iif(Sum(Fields!JTD_Hours.Value,"table1_CostCode") = 0, 1, Sum(Fields!JTD_Hours.Value,"table1_CostCode")))

In general, you want a pattern like this to avoid division by zero:

=iif(B=0, 0, A / iif(B=0, 1, B))

-- Robert

|||

Robert,
I appreciate your response.
Your tip really helped and now i see why it didn't work.
but i would have to admit that it's kind of wrong way how IIf works but it could be just me.
Anyhow, many thanks for you advice.

Konstantin

P.S.
It seems to me make more sence to create a custom function in a code section something like XdivY(x, y, whenYIsZero) so i can reuse this code over and over again.

|||IIF is a function call like any other function call in the VB runtime library. Function arguments always get evaluated before the function body is executed. That's how all programming environments work. It would be nice to support the IF - ELSE - ENDIF statements, but the VB runtime library has only support for function calls. Given that limitation, adding a custom function like you suggested is a good approach if you need this kind of division throughout the report.

-- Robert|||

The previous posts helped me a lot. I am new to coding and have the same problem but when I am trying to divide the totals on for a group. This is the code that is currently being used.

=Iif(ReportItems!Total_GALLONS1.Value > 0,ReportItems!Total_REVENUE1.Value/ReportItems!Total_GALLONS1.Value, 0)

Any help would be appreciated.

Thanks!

|||

You should do exectly the same as in second post here by Robert Bruckner MSFT

Your statement should be like

=Iif(ReportItems!Total_GALLONS1.Value > 0,ReportItems!Total_REVENUE1.Value/IIF(ReportItems!Total_GALLONS1.Value=0,1,ReportItems!Total_GALLONS1.Value), 0)