OLAP cube - errorhandling

Dear all

i have inherited a SSAS 2k5 cube with a large number of calculated members.

I have a number of problems:

sometimes the calculated amounts are very very small like: 0.00000000000034.

This however, does not play nicely with excell... heximal display and so on. anyone know of a way to show these as being 0?

secondly

I have a number of calculated members who themselves contain calculated measures.

When one of their parents is 0, they resort into div/0 erros, etc

For my current project it is unacceptable that these errors are visible for the end user

is there any way that i can catch all the errors at once? or do in eed to include iif statements in the calculations of each calculated member?

Hi, unfortunately you need to put IIF statements around them. For example direct queries from the cube.

with

member measures.test as

0.000000003

member measures.test2 as

IIF(measures.test<0.00001,0,measures.test)

member measures.test3 as

0

member measures.test4 as

IIF(measures.test3=0,0,measures.test/measures.test3)

select {measures.test2,measures.test4} on 0 from [mycube]

or calculated members on the cube

CREATE MEMBER CURRENTCUBE.[MEASURES].[PercentUnavailable] as

IIF([Measures].[Total Count]=0,0,[Measures].[Total Unavailable])*100/[Measures].[Total Count]),

visible=1;

John

|||

Alternative which will perform better in cases where you have a large number of these is to use the format statement

member measures.test as

0.000000003,format='#.00'

okay, Im frustrated....how do I

Greetings, all!

I've searched the MS SQL Server Books Online and this group.

I've been doing Oracle on UNIX for 12+ years, and now I have some SQL
Server databases.

I've discovered the INFORMATION_SCHEMA tables (just like the good ol'
dba_* views in
Oracle)

I've figured out how to use SQL2005 Management Studio.

What'd I'd like to know is - can SQL Server be used to write SQL like
Oracle can; e.g:

spool csyn.sql
set heading off
set pagesize 6000

select 'CREATE PUBLIC SYNONYM '||table_name||' FOR
SYSADM.'||TABLE_NAME||';'
from dba_tables where owner='SYSADM';

spool off

In SQL*Plus, the above will create a script to create public synonyms
for tables in a PeopleSoft database (tables in PeopleSoft multiply like
Tribbles in a storage bin of quadrotriticale...).

Yah, GUI's are fine...until you have thousands and thousands of tables
to deal with!

Thanks ever so mucha_dba_used_to_oracle wrote:

Quote:

Originally Posted by

Greetings, all!
>
>
I've searched the MS SQL Server Books Online and this group.
>
I've been doing Oracle on UNIX for 12+ years, and now I have some SQL
Server databases.
>
I've discovered the INFORMATION_SCHEMA tables (just like the good ol'
dba_* views in
Oracle)
>
I've figured out how to use SQL2005 Management Studio.
>
What'd I'd like to know is - can SQL Server be used to write SQL like
Oracle can; e.g:
>
spool csyn.sql
set heading off
set pagesize 6000
>
select 'CREATE PUBLIC SYNONYM '||table_name||' FOR
SYSADM.'||TABLE_NAME||';'
from dba_tables where owner='SYSADM';
>
spool off
>
In SQL*Plus, the above will create a script to create public synonyms
for tables in a PeopleSoft database (tables in PeopleSoft multiply like
Tribbles in a storage bin of quadrotriticale...).
>
Yah, GUI's are fine...until you have thousands and thousands of tables
to deal with!
>
Thanks ever so much

Try:

SELECT 'CREATE SYNONYM dbo.'
+QUOTENAME(table_name)+' FOR '
+QUOTENAME(table_schema)+'.'
+QUOTENAME(table_name)
FROM information_schema.tables
WHERE table_schema = 'SYSADM' ;

This example assumes you have a schema called SYSADM and that you want
the synonym to belong to the dbo schema. You can do without the
QUOTENAME functions if your table names are such that they don't need
quoting (essentially that means no spaces, punctuation or reserved
words.

Run the script in Text mode (CTRL+T or click the "Results to Text"
button on the toolbar). Then either cut-and-paste the result into the
query window or save it to a file (Right-click, Save As).

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--

ODBC Trusted_Connection call to SQLGetInfo returns DBO as user name, not NT User

Hello, all:
I am connecting to SQL Server 2000 with a trusted connection, and that is working fine. I then am issuing a SQLGetInfo call to find out the SQL_USER_NAME, which is always returning dbo instead of (my) Windows NT login name, which I expect to see. THe authentication is (apparently) confirmed by the SQL Server session monitor where it shows the right Windows NT user name logged in, over ODBC. With a standard SQL Login, untrusted, I definitely get the right user name back, not dbo.

So:

Login Type / user id /returned value from SQLGetInfo

Windows Authentication / Windows login / dbo
SQL Authentication / Windows login (UID) / UID

The problem is that I am trying to confirm that the userid entered in a dialog or passed on the command line to my application matches the actual connected user name inside SQL Server...

My questions:
Is there some pathology in Trusted_Connections that masks the Windows/NT login name and always returns UID 'dbo' from a call to SQLGetInfo? Is there a MSS (public) stored procedure that can robustly give back the logged in user name so I can bypass the SQLGetInfo call?I think I have finally wrestled this beast to the ground. Here's the story, in case anybody is interested:

1) When making an NTLM trusted connection to MSS, logging in using my NT login name e.g. as amarshall, the system was always saying the connected user is dbo, no matter what. This can be verified by using a SQL tool like SQL Query Analyzer, connecting over a trusted connection to MSS, and entering SELECT USER; or SELECT USER_NAME() or any variants. These will always return dbo, which is not the real 'user' of interest, but the schema owner. What we want is to confirm 'amarshall' is a (legitimate) user, meaning, SQL Server knows who this is..

2) So, use the SYSTEM_USER call, and the result is AMARSHALL-CAM\amarshall (Host\user).

3) Then, just perform substring match to see if what the user has typed into the login dialog (amarshall) matches what is in the call result. If so, good user, otherwise, reject (program decides).

4) I do NOT strip off the host name or anything - just substring match result of SYSTEM_USER call to logins userid.

5) Database connectstring for Powerbuilder must include SECURE=1. For ADO/VB/.NET, has to have something like TRUSTED_CONNECTION=yes (PB does this for me)

6) Have not tested this against the SQL Server Desktop Edition. Should be identical, but who knows?

7) If anybody has a superior resolution, or security concerns, please advise, but this suffices for now.|||dbo is a database user. You seem to want the server login information, which is quite different. I'd use suser_sname (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_86xx.asp).

-PatP

ODBC LIKE clause does not work - SQL Server *BUG*

Hi All:
I think there is a problem with the LIKE clause in a
SELECT statement. I am using the latest SQL Server 2000
(ver 8.00.760) and Visual C/C++ 6.00 (with SP3) on
Windows XP.
I created an ODBC connection and tied into a database
containing a customer table. The following piece of code
returns a record count of 0 (zero) when, in fact, the
record count should be over 2000.
=========================
#include <afxwin.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#define DATABASE_CLAUSE "MPOS_SQLSERVER"
#define USERNAME_CLAUSE ""
#define PASSWORD_CLAUSE ""
#define SELECT_CLAUSE "SELECT COUNT(*) from Customers
WHERE LastName LIKE ? "
#define LIKE_CLAUSE "A%"
void main( void )
{
HENV hEnv = SQL_NULL_HENV;
HDBC hDbc = SQL_NULL_HDBC;
HSTMT hStmt = SQL_NULL_HSTMT;
long lValue = 0;
SQLINTEGER sqlNull = 0;
SQLINTEGER sqlStrLen = SQL_NTS;
SQLUINTEGER sqlColumnLen = strlen( LIKE_CLAUSE );
SQLINTEGER sqlBufferLen = strlen( LIKE_CLAUSE );
SQLINTEGER sqlValue = 0;
if ( ! SQL_SUCCEEDED( SQLAllocEnv( &hEnv )))
printf( "Error in SQLAllocEnv()\n" );
else if ( ! SQL_SUCCEEDED( SQLAllocConnect( hEnv,
&hDbc )))
printf( "Error in SQLAllocConnect()\n" );
else if ( ! SQL_SUCCEEDED( SQLConnect( hDbc,
(SQLCHAR *)
DATABASE_CLAUSE, SQL_NTS,
(SQLCHAR *)
USERNAME_CLAUSE, SQL_NTS,
(SQLCHAR *)
PASSWORD_CLAUSE, SQL_NTS )))
printf( "Error in SQLConnect()\n" );
else if ( ! SQL_SUCCEEDED( SQLAllocStmt( hDbc,
&hStmt )))
printf( "Error in SQLAllocStmt()\n" );
else if ( ! SQL_SUCCEEDED( SQLPrepare( hStmt,
(SQLCHAR *)
SELECT_CLAUSE,
SQL_NTS )))
printf( "Error in SQLPrepare()\n" );
else if ( ! SQL_SUCCEEDED( SQLBindParameter( hStmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
0,
0,
LIKE_CLAUSE,
sqlBufferLen,
&sqlStrLen )))
printf( "Error in SQLBindParameter()\n" );
else if ( ! SQL_SUCCEEDED( SQLExecute( hStmt )))
printf( "Error in SQLExecute()\n" );
else if ( ! SQL_SUCCEEDED( SQLFetch( hStmt )))
printf( "Error in SQLFetch()\n" );
else if ( ! SQL_SUCCEEDED( SQLGetData( hStmt,
1,
SQL_C_LONG,
&sqlValue,
sizeof(
sqlValue ),
&sqlNull )))
printf( "Error in SQLGetData()\n" );
else
printf( "sqlValue/sizeof( sqlValue )/sqlNull = <%
ld>/<%ld>/<%ld>\n", sqlValue, sizeof( sqlValue ),
sqlNull );
}
=========================
When using the Query Analyzer and Enterprise Manager it
works. This is because, in the above code, I use the
SQLBindParameter() function to do the binding.
I've tried using the SQL Trace facility but it only shows
me the SQL statement being executed but does not indicate
any translations with the binded parameter.
Any suggestions or solutions would be greatly appreciated.
Regards,
AngeloWhen using SQL Trace, what is the text content of the query being executed?
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
news:06b101c3c712$93afb310$a401280a@.phx.gbl...

quote:

---

> Hi All:
> I think there is a problem with the LIKE clause in a
> SELECT statement. I am using the latest SQL Server 2000
> (ver 8.00.760) and Visual C/C++ 6.00 (with SP3) on
> Windows XP.
> I created an ODBC connection and tied into a database
> containing a customer table. The following piece of code
> returns a record count of 0 (zero) when, in fact, the
> record count should be over 2000.
> =========================
> #include <afxwin.h>
> #include <stdio.h>
> #include <sql.h>
> #include <sqlext.h>
> #define DATABASE_CLAUSE "MPOS_SQLSERVER"
> #define USERNAME_CLAUSE ""
> #define PASSWORD_CLAUSE ""
> #define SELECT_CLAUSE "SELECT COUNT(*) from Customers
> WHERE LastName LIKE ? "
> #define LIKE_CLAUSE "A%"
> void main( void )
> {
> HENV hEnv = SQL_NULL_HENV;
> HDBC hDbc = SQL_NULL_HDBC;
> HSTMT hStmt = SQL_NULL_HSTMT;
> long lValue = 0;
> SQLINTEGER sqlNull = 0;
> SQLINTEGER sqlStrLen = SQL_NTS;
> SQLUINTEGER sqlColumnLen = strlen( LIKE_CLAUSE );
> SQLINTEGER sqlBufferLen = strlen( LIKE_CLAUSE );
> SQLINTEGER sqlValue = 0;
> if ( ! SQL_SUCCEEDED( SQLAllocEnv( &hEnv )))
> printf( "Error in SQLAllocEnv()\n" );
> else if ( ! SQL_SUCCEEDED( SQLAllocConnect( hEnv,
> &hDbc )))
> printf( "Error in SQLAllocConnect()\n" );
> else if ( ! SQL_SUCCEEDED( SQLConnect( hDbc,
> (SQLCHAR *)
> DATABASE_CLAUSE, SQL_NTS,
> (SQLCHAR *)
> USERNAME_CLAUSE, SQL_NTS,
> (SQLCHAR *)
> PASSWORD_CLAUSE, SQL_NTS )))
> printf( "Error in SQLConnect()\n" );
> else if ( ! SQL_SUCCEEDED( SQLAllocStmt( hDbc,
> &hStmt )))
> printf( "Error in SQLAllocStmt()\n" );
> else if ( ! SQL_SUCCEEDED( SQLPrepare( hStmt,
> (SQLCHAR *)
> SELECT_CLAUSE,
> SQL_NTS )))
> printf( "Error in SQLPrepare()\n" );
> else if ( ! SQL_SUCCEEDED( SQLBindParameter( hStmt,
> 1,
> SQL_PARAM_INPUT,
> SQL_C_CHAR,
> SQL_CHAR,
> 0,
> 0,
> LIKE_CLAUSE,
> sqlBufferLen,
> &sqlStrLen )))
> printf( "Error in SQLBindParameter()\n" );
> else if ( ! SQL_SUCCEEDED( SQLExecute( hStmt )))
> printf( "Error in SQLExecute()\n" );
> else if ( ! SQL_SUCCEEDED( SQLFetch( hStmt )))
> printf( "Error in SQLFetch()\n" );
> else if ( ! SQL_SUCCEEDED( SQLGetData( hStmt,
> 1,
> SQL_C_LONG,
> &sqlValue,
> sizeof(
> sqlValue ),
> &sqlNull )))
> printf( "Error in SQLGetData()\n" );
> else
> printf( "sqlValue/sizeof( sqlValue )/sqlNull = <%
> ld>/<%ld>/<%ld>\n", sqlValue, sizeof( sqlValue ),
> sqlNull );
> }
>
> =========================
> When using the Query Analyzer and Enterprise Manager it
> works. This is because, in the above code, I use the
> SQLBindParameter() function to do the binding.
> I've tried using the SQL Trace facility but it only shows
> me the SQL statement being executed but does not indicate
> any translations with the binded parameter.
> Any suggestions or solutions would be greatly appreciated.
> Regards,
> Angelo

---

|||When using the SQL Trace, the following is the snippet
from the log file...
=======================
rawodbc f38-f34 ENTER SQLPrepare
HSTMT 00391FE0
UCHAR * 0x00402104 [ -
3] "SELECT COUNT(*) from Customers WHERE LastName LIKE ?
\ 0"
SDWORD -3
=======================
If you'd like to see the entire log file, I can post that
too.

quote:

---

>--Original Message--
>When using SQL Trace, what is the text content of the

---

query being executed?

quote:

---

>--
>Brannon Jones
>Developer - MDAC
>This posting is provided "as is" with no warranties and

---

confers no rights.

quote:

---

>
>"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
>news:06b101c3c712$93afb310$a401280a@.phx.gbl...
code[QUOTE]
SQL_CHAR,[QUOTE]
<%[QUOTE]
shows[QUOTE]
indicate[QUOTE]
appreciated.[QUOTE]
>
>.
>

---

|||I tried the following and it works fine:
rc = SQLBindParameter(hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
2,
0,
"C%",
2,
NULL);
If I change the call to be like your example (passing 0 for the cbColDef
parameter) then I get an error trying to bind the parameter. What ODBC
version does your app set? 3.0 or earlier?
Try specifying the length of the string in the cbColDef parameter as well.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
news:0d9401c3d658$1c963ce0$a001280a@.phx.gbl...[QUOTE]
> When using the SQL Trace, the following is the snippet
> from the log file...
> =======================
> rawodbc f38-f34 ENTER SQLPrepare
> HSTMT 00391FE0
> UCHAR * 0x00402104 [ -
> 3] "SELECT COUNT(*) from Customers WHERE LastName LIKE ?
> \ 0"
> SDWORD -3
> =======================
> If you'd like to see the entire log file, I can post that
> too.
>
> query being executed?
> confers no rights.
> code
> SQL_CHAR,
> <%
> shows
> indicate
> appreciated.|||Thank you, thank you, thank you...
I am using ODBC version 2.x. The fix is the specification
of the 2 as the ColumnSize parameter.
It's funny how things work differently with Access and
SQLServer eventhough they are from the same manufacturer.
Thanks again.

quote:

---

>--Original Message--
>I tried the following and it works fine:
> rc = SQLBindParameter(hstmt,
> 1,
> SQL_PARAM_INPUT,
> SQL_C_CHAR,
> SQL_CHAR,
> 2,
> 0,
> "C%",
> 2,
> NULL);
>If I change the call to be like your example (passing 0

---

for the cbColDef

quote:

---

>parameter) then I get an error trying to bind the

---

parameter. What ODBC

quote:

---

>version does your app set? 3.0 or earlier?
>Try specifying the length of the string in the cbColDef

---

parameter as well.

quote:

---

>--
>Brannon Jones
>Developer - MDAC
>This posting is provided "as is" with no warranties and

---

confers no rights.

quote:

---

>
>"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
>news:0d9401c3d658$1c963ce0$a001280a@.phx.gbl...
LIKE ?[QUOTE]
that[QUOTE]
and[QUOTE]
message[QUOTE]
2000[QUOTE]
database[QUOTE]
the[QUOTE]
Customers[QUOTE]
LIKE_CLAUSE );[QUOTE]
hEnv,[QUOTE]
*)[QUOTE]
*)[QUOTE]
*)[QUOTE]
*)[QUOTE]
SQL_NTS )))[QUOTE]
hStmt,[QUOTE]
SQL_C_LONG,[QUOTE]
&sqlValue,[QUOTE]
&sqlNull )))[QUOTE]
sqlValue )/sqlNull =[QUOTE]
Manager it[QUOTE]
>
>.
>

---

|||Access and SQL Server are two completely different products (produced by
different groups here at Microsoft).
If you are using the SQL driver in 2.x mode, then it will allow you to
specify a zero-length precision, but obviously the behavior is not what you
expected. If you are using the SQL driver in 3.0 mode, then it will error
out if you specify a zero-length precision.
When binding parameters, you should always give the precision. In this
case, if you give a precision of zero, then we think the parameter (on the
server-side) has a length of zero, and so we truncate whatever data you give
us. Not the best behavior, but that's how it works. The precision (in
conjunction with the SQL data type) describes the type on the server.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Angelo Kalpakis" <angelo@.mrsonline.ca> wrote in message
news:0bc501c3d9e7$2fd1d7a0$a401280a@.phx.gbl...[QUOTE]
> Thank you, thank you, thank you...
> I am using ODBC version 2.x. The fix is the specification
> of the 2 as the ColumnSize parameter.
> It's funny how things work differently with Access and
> SQLServer eventhough they are from the same manufacturer.
> Thanks again.
>
> for the cbColDef
> parameter. What ODBC
> parameter as well.
> confers no rights.
> LIKE ?
> that
> and
> message
> 2000
> database
> the
> Customers
> LIKE_CLAUSE );
> hEnv,
> *)
> *)
> *)
> *)
> SQL_NTS )))
> hStmt,
> SQL_C_LONG,
> &sqlValue,
> &sqlNull )))
> sqlValue )/sqlNull =
> Manager it