Calling stored proc with default params from .NET not working.

All,

I have the following :

ALTERPROCEDURE [dbo].[sp_FindNameJon]

@.NameNamevarchar(50)='',

@.NameAddressvarchar(50)='',

@.NameCityvarchar(50)='',

@.NameStatevarchar(2)='',

@.NameZipvarchar(15)='',

@.NamePhonevarchar(25)='',

@.NameTypeIdint=0,

@.BureauIdint,

@.Pageint=1,

@.Countint=100000

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

DECLARE @.SqlStringnvarchar(3000),@.SelectClausenvarchar(1000), @.FromClausenvarchar(1000),@.WhereClausenvarchar(1000)

DECLARE @.ParentSqlStringnvarchar(4000)

DECLARE @.Startint, @.Endint

INSERTinto aaJonTempvalues(@.Page,'here2', @.NameCity);

And inside of aaJonTemp, I have the following :

NULLhere2NULLNULLhere2NULL

How is this possible? If @.Page or @.NameCity is NULL, how come it doesn't default to a value in the stored proc?

Thx

jonpfl

Because a parameter value being NULL is not the same thing as not supplying a parameter at all.

The defaults basically say... If the user hasn't supplied the parameter, then use this. You have supplied a parameter, although the value is NULL. If you want to make it so that if someone sends in a NULL and you want to change it to something else, then you need to code that.

IF @.param IS NULL SET @.param=...

sql

Calling SSRS web services from Java using Axis

Hi,

I'm working on a project where I'll need to call the SSRS web services from a java servlet. We are using the Axis implementation of the SOAP protocol.

I've previously used SSRS web services from C#, but not from Java. Before I start this endeavor does anyone have any bits of wisdom they'd like to share that's going to make this easier?

Also, I've searched for tutorials, samples, source code, etc.. for this situation (that is, using java to call SSRS web services), but can't find anything available. Does anyone know where I can find resources such as these for this particular technology pairing?

Thanks for the help.

-M

Try WSDL2Java tool to create a java proxy to the reporting services.

The WSDL is accessible from one of these url depending on the version(2000 or 2005) and flavor (management or execution) of the service

http://<server>/reportserver/reportservice.asmx?wsdl

http://<server>/reportserver/reportservice2005.asmx?wsdl

http://<server>/reportserver/reportexecution2005.asmx?wsdl

An example on accessing .NET web services (not SSRS specific) from Java is available here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/jdni_ch08.asp

Calling Reports without URL

Hi,

I am working in SSRS2005.creating reports using VS2005 and deploying reports in report server(http://localhost/reportserver/).
I am using URL for calling reports.
Now,I am working on another application which is runing on session.I am asked to develop a report for this session application.In this application security is an important factor.

Without explicitly calling an URL how can I call a report in a button click event .I tried using Iframe.Is there any other way.

Help me in this regard.

Thanks,
Prabaharaprabu

You could use the report execution web service to render the report and then stream the result via your own website. See the docs here.

http://msdn2.microsoft.com/en-us/library/ms155071.aspx

Let me know if you need more information.

Calling procs on other databases

Hi. I am currently working between sql server 2000 and 2005. I have written procs in 2005 using some new native features. I wish to remain using 2000 and just call and get return values from procs on 2005 Database. Procs on 2005 DB only access tables on that DB and vice versa.

Is there a way to call stored procedures across Servers?

Cheers,

Ewen

Yes.

Use the four part object name: server.database.schema.object

Of course, security will have to be set up properly.

|||Look up linked servers in books online. Setting it up is not terribly hard. Then use the four part names where you put the linked server name before the database name: server.database.schema/owner.procedureName

Calling other databases from stored procedure

Hi,

I am working with multiple databases on the same server and in a stored procedure I need to be able to call on one of them.

Here is an example of what I am trying to do in this stored procedure:
create procedure sp_procedure
(
@.variable int
)
select anitem from atable where selection = @.variable

declare @.anothervariable Char(3)

select @.anothervariable = item_that_determines_database from atable where selection = @.variable

usedbo.@.anothervariable
select count(id) from Some_table where selection = @.variable

The database is not found using this method and I do need to use it in a stored procedure. All of the databases being used are (3) letter names in lower case (aaa, bbb, ccc, etc...), the info that @.anothervariable pulls from the table is the name of that database but in all caps. Does this part make a difference?

Also, what method could I use to get the database variable to read from that selected database?

Thank-you for your help.

Eric

You can build a dynamic SQL.

Declare @.sql varchar(500)

SET @.sql = 'SELECT @.anothervariable = ' + @.otherdb + '.dbo.itemfromothertable where selection = @.variable'

use sp_ExecuteSQL the execute the SQL and add the parameters appropriately.

|||

I don't get it.

Where did the @.otherdb come from?

I am selecting an item from a table and using that item to get a count from a table in another database. The database is determined from the result of the first query.

|||Yes, since the DB name is dynamic, you need to get the name from your first query, assign it to the variable @.otherdb, build your T-SQl and execute it.

Calling DTS from C#

Hi all,

i have a DTS package that i call from a C# app, i had it working great until i decided to use an activeX script to do the data transformations instead of the row copy.

I need to use ActiveX to add a standard name to the last column in the destination table. the problem is the task is executing without errors (from c#) but nothing is happening, its failing silently. If i modify the Data Transformation back to a standard column mapping (with separate DTSTransformations for each column) it works fine, but as soon as i use activeX to handle the transformations it doesn't work.

Can anyone tell me what i may be doing wrong.

heres the calling code from C#

if(f.Name.Substring(13,7).ToLower() =="product"){try{activity.Log("Starting Product DTS Package..."); DTS.Package2Class package =new DTS.Package2Class();object pVarPersistStgOfHost =null;package.LoadFromSQLServer("192.168.8.8","username","thepassword",Microsoft.SqlServer.DTSPkg80.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,null,null,null,(string)ConfigurationSettings.AppSettings["productDTSPackage"],ref pVarPersistStgOfHost);package.GlobalVariables.Item(1).Value = f.FullName.ToString();package.Execute();package.UnInitialize();//force release of COM objectSystem.Runtime.InteropServices.Marshal.ReleaseComObject(package);package =null;}catch(Exception e){activity.Log(string.Format("Failureprocessing {0}",WorkingPath + f.Name) +" - "+ e.Message);}

and the activex i tried to use for the transformations is:

'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()
DTSDestination("Yesmail Id") = DTSSource("product series")
DTSDestination("Customer CKM CustId") = DTSSource("product family")
DTSDestination("Product SKU") = DTSSource("transaction date")
DTSDestination("product model name") = DTSSource("serial number")
DTSDestination("serial number") = DTSSource("product model name")
DTSDestination("transaction date") = DTSSource("Product SKU")
DTSDestination("product family") = DTSSource("Customer CKM CustId")
DTSDestination("product series") = DTSSource("Yesmail Id")
DTSDestination("fileName") = DTSGlobalVariables("sourceFile").Value <-- this is why im using the activex for this field to get a Global Variable.
Main = DTSTransformStat_OK
End Function

the weird thing is the PACKAGE runs fine from ENTERPRISE MANAGER with this activex, it just doesn't do it from my Calling app, perhaps i have missed something i need to change in the package constructor?

BTW: i do have my assembly signed in C# for the COM wrapper.

thanks in advance,

mcm

I have answered here in the other thread the next step and it is not complicated and I have not found anybody who could not get it to run so run a search on this site for setting up the proxy account link. Hope this helps.

http://forums.asp.net/thread/1440879.aspx

Calling All Dynamic SQL Gods!

Hello
I'm desperate for help with the following dynamic SQL. It used to work for
ages but suddenly stopped working today! I can't recall changing anything of
importance.. So I say. Anyway, I'm getting this error: "Cannot use empty
object or column names. Use a single space if necessary."
I've identified the location within the script that causes this message it's
this line:
(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +
CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,
3rd line of Set @.cmdSQL =.

I've been trying to insert a single space between "" which eliminates half
of the error but I can't figure out what quotes to use around 'MG' and 'MA'.

I'd be grateful if you can have a look at this and let me know how to
correct this problem.
Like I said it used to work and I'm perplexed about this sudden error. Is
there any change that can cause this behaviour?

Many thanks for your efforts!!
Have a nice day!!

Martin

Paging Script:

CREATE PROCEDURE dbo.sp_ListKeyOut(
@.page_number INT,
@.number_of_records INT,
@.cmdWHERE VARCHAR(200),
@.cmdORDERBY VARCHAR(200)
) AS
SET NOCOUNT ON

DECLARE
@.SizeString VARCHAR(5),
@.PrevString VARCHAR(5),
@.cmdSQL varchar(2000)

SET @.SizeString = CONVERT(VARCHAR, @.number_of_records)
SET @.PrevString = CONVERT(VARCHAR, @.number_of_records * (@.page_number - 1))

SET QUOTED_IDENTIFIER OFF

SET @.cmdSQL = 'SELECT COALESCE((i2b_vw_contact.Firstname + CHAR(32) +
i2b_vw_contact.Lastname),i2b_vw_company.CompanyNam e) AS CName,
i2b_vw_keytransactionlog.KeyTransactionLogID,
i2b_vw_keytransactionlog.KeyID,
(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +
CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,
i2b_vw_address.Address1 AS PropertyAddress, i2b_vw_contact.MobileNo,
A.ProgUserName AS ProgUserName,
CONVERT (varchar(10), i2b_vw_keytransactionlog.TransactionDate, 104 ) AS
TransactionDate,
CONVERT(varchar(10),i2b_vw_keytransactionlog.Retur nByDate,104) AS
ReturnByDate
FROM i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE KeyTransactionLogID IN'

IF @.cmdWHERE IS NULL OR @.cmdWHERE = ''
BEGIN

EXEC(
@.cmdSQL +
'(SELECT TOP ' + @.SizeString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE KeyTransactionLogID NOT IN
(SELECT TOP ' + @.PrevString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
ORDER BY ' + @.cmdORDERBY + ')
ORDER BY ' + @.cmdORDERBY + ') ORDER BY ' + @.cmdORDERBY
)

-- EXEC('SELECT (COUNT(*) - 1)/' + @.SizeString + ' + 1 AS PageCount FROM
i2b_vw_keytransactionlog')

END
ELSE
BEGIN

EXEC(
@.cmdSQL +
'(SELECT TOP ' + @.SizeString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE ' + @.cmdWHERE + ' AND KeyTransactionLogID NOT IN
(SELECT TOP ' + @.PrevString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE ' + @.cmdWHERE + ' ORDER BY ' + @.cmdORDERBY + ')
ORDER BY ' + @.cmdORDERBY + ') ORDER BY ' + @.cmdORDERBY
)

-- EXEC('SELECT (COUNT(*) - 1)/' + @.SizeString + ' + 1 AS PageCount FROM
i2b_vw_keytransactionlog WHERE ' + @.cmdWHERE)

END

SET QUOTED_IDENTIFIER ON

RETURN 0
GOMartin Feuersteiner (theintrepidfox@.hotmail.com) writes:
> I'm desperate for help with the following dynamic SQL. It used to work
> for ages but suddenly stopped working today! I can't recall changing
> anything of importance.. So I say. Anyway, I'm getting this error:
> "Cannot use empty object or column names. Use a single space if
> necessary."

In T-SQL there are two ways to delimit a string literal: '' and "". ANSI
SQL permits only '', and reserves "" for quoting identifiers with
"funny" characters, so you can have column names like "order date".

Of this reason, SQL Server offers a setting QUOTED_IDENTIFIER that can
be on or off. If ON, you can only use '' to delimit string literal, if
off, you can use both '' and "".

There is functionality (indexed views, indexed computed columns) in SQL
Server that is only available if QUOTED_IDENTIFIER is ON, so stick with this
setting. This is also the default setting with many client libraries.
Unfortunately, Enterprise Manager has the bad habit of setting it off, and
since this setting is saved with the procedure this can cause confusinon at
times.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland

I knew you would go for it :-)
Well, I know that stuff with '' and ", my problem is that I can't
comprehend how many of ' I need to use e.g. on each site of 'MG' to get
it working. It causes a brain overflow!! Can you please provide me with
a hands-on example perhaps using my code?

(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +
CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,

Many thanks for your efforts!
Have a nice day!

Martin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Martin Ashcroft (theintrepidfox@.hotmail.com) writes:
> I knew you would go for it :-)
> Well, I know that stuff with '' and ", my problem is that I can't
> comprehend how many of ' I need to use e.g. on each site of 'MG' to get
> it working. It causes a brain overflow!! Can you please provide me with
> a hands-on example perhaps using my code?
> (i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +
> CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,

Check out http://www.sommarskog.se/dynamic_sq...#good_practices
for some tips.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you Erland!
It helped!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Calling a package at Runtime

I am working on a package that will sense a text file in a folder. From this text file I will be extracting the file name which will tell me what task I need to run. How can I create a package that, depending upon what the file name is, call a specific package. To be more clear, I will have 3 different flat files that is each in a standard form. Their names will be unique. To transform their data will require different ETL logic. I will be using a loop to constantly check to see if a file has been put into the directory. I just need to know how I can direct the input file to the proper ETL task.

Use precedence constraints between the task that reads the file and three Execute Package tasks. Store the filename in a variable and use that in an expression on the precedence constraint. Make the expression dependent on the variable being equal to the appropriate file name.|||

As an aside, you should investigate using a WMI Event Watcher Task at control flow level to detect the files' presence rather than a loop. I believe it is much more efficient.

Calculations not working

I'm having a problem with my calculations not showing any data. I did delete the calculate script in my original cube so I created a new cube and recreated calculations but still get no data when I browse, any ideas?

Cheers,

Derek

I would guess you have a proble with calc script in your new cube.

Try first browsing your cube with default calc script and see if you got data for the real measures. Then start working your way and add more calculated measures to the point where you find a real culprit ;)

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Calculation using count function in SQL server Business Intelligence development studio

Hello All,

I am working on a report using SQL server Business Intelligence development studio in which I need to count the total number of saleslines that either has a status : Delivered or Invoiced.

I am using the inbuilt count function under the calcuations, as: Count(Fields!SalesStatus.Value, scope). I am not sure what scope means here however, I read that scope refers to some dataset, dataregion or grouping. I dont know what that means. I shall be really thankful if someone can help me with this.

Regards,

Rashi

Scope is optional and if not given, will refer to the current scope.

The current scope is exactly what you've said. If your dataset name is "dataset1" and you put a textbox outside a table or matrix and put the value: Count(Fields!SalesStatus.Value, "dataset1"), you'll get the accurate count of all items within that dataset.

If you were doing some grouping inside your table and wanted to get the count for each group, in a group header or footer, you could do: Count(Fields!SalesStatus.Value, "table1_YourGroupName") to acheive this.|||An alternative approach to using the Count function would be to add a calculated column to your dataset, setting the value to 0 or 1 depending on whether the row meets your conditions. Then sum the calculated column.|||

Thanks for your response!

Well, the dataset is salesstatus which is a field in the salestable. Now, this field "salesstatus" has some values like: invoiced, delivered, pending, on hold etc on each line-item. I just want to count the line items that have a sales status equals to delievered or invoiced. I am not sure if there is a way to calculate that. I am doing reporting for the first time so I might not be sure.

What I am trying to achieve is to calculate the total number of orders shipped. For this, I am thinkin if I can get a count of lineitems that have a salesstatus = delivered or invoiced I can acheive that. I would appreciate your response on this one.

Thanks,

Rashi

|||Try adding a calculated field with "=iif(fields!salesstatus = "delivered", 1, 0)" (freehanding this so it may not be 100% correct syntax, but it's close). To check additional values, you could use nested a nested iif, or the switch function.|||You can group by salestatus then count the group. You might want to add drill down capability on this group.|||

Thanks all

I have been able to figure it out. I grouped all and used a count function and it just works great!!!

Thanks again,

Rashi

calculated members from a cube using the OPENQUERY

We have a DTS package that runs once a month. It has been working for more than four years now, but all of the sudden it started to fail. The package starts with loading data from an sql database and then updating the cube and then send the calculated results to a SQL2000 server table.

the problem starts at the last part when reading from cube into sql2000 table. below is my select which fails:

select

convert(decimal(18,2), replace("[Measures].[Sales]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Cost]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Hours A1]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Data]", ',', '.'))

convert(decimal(18,2), replace("[Measures].[Orderlines A3]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Invoices E3]", ',', '.'))

from openquery

( GONS021_OLAP, 'select

{[Measures].[Sales], [Measures].[Cost], [Measures].[TB], [Measures].[TG], [Measures].M1, [Measures].[M2], [Measures].[A1], [Measures].[A2], [Measures].[A3], [Measures].[A4], [Measures].[A5], [Measures].[A6], [Measures].[A7], [Measures].[A8],[Measures].[T1], [Measures].[L1], [Measures].[L2], [Measures].[L3], [Measures].[E1], [Measures].[E2], [Measures].[E3], [Measures].[E4], [Measures].[E5],[Measures].[Hours A1],[Measures].[Data],[Measures].[Orderlines A3],[Measures].[Qty Returned A8],[Measures].[Invoices E3] } on columns,

{[Customer].[All Customer], [Customer].[Customer No].Members} on rows

from TotalTable where ([Time].&[2007])')

The error message:

The MDX queries executed through the OPENQUERY function returns the following error messages for some members of the dimension used to construct them:

Could not get the data of the row from the OLE DB provider 'MSOLAP'.

OLE DB error trace [OLE/DB Provider 'MSOLAP' IRowset::GetData returned 0x80040e21:Data status returned from the provider: [COLUMN_NAME=[Measures].[Data] STATUS=DBSTATUS_E_UNAVAILABLE], [COLUMN_NAME=[Measures].[Invoices E3] STATUS=DBSTATUS_E_UNAVAILABLE]]

The same MDX Queries execute without any problems through the MDX Sample

Application!

OBS: I have SP4 on both SQL and Analysis

Hi there i am wondering if you managed to solve this, as I had exactly the same problem? A query that worked previously, is now not working. I am also on SQL Server 2000 Analysis SP 4

calculated members from a cube using the OPENQUERY

We have a DTS package that runs once a month. It has been working for more than four years now, but all of the sudden it started to fail. The package starts with loading data from an sql database and then updating the cube and then send the calculated results to a SQL2000 server table.

the problem starts at the last part when reading from cube into sql2000 table. below is my select which fails:

select

convert(decimal(18,2), replace("[Measures].[Sales]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Cost]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Hours A1]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Data]", ',', '.'))

convert(decimal(18,2), replace("[Measures].[Orderlines A3]", ',', '.')),

convert(decimal(18,2), replace("[Measures].[Invoices E3]", ',', '.'))

from openquery

( GONS021_OLAP, 'select

{[Measures].[Sales], [Measures].[Cost], [Measures].[TB], [Measures].[TG], [Measures].M1, [Measures].[M2], [Measures].[A1], [Measures].[A2], [Measures].[A3], [Measures].[A4], [Measures].[A5], [Measures].[A6], [Measures].[A7], [Measures].[A8],[Measures].[T1], [Measures].[L1], [Measures].[L2], [Measures].[L3], [Measures].[E1], [Measures].[E2], [Measures].[E3], [Measures].[E4], [Measures].[E5],[Measures].[Hours A1],[Measures].[Data],[Measures].[Orderlines A3],[Measures].[Qty Returned A8],[Measures].[Invoices E3] } on columns,

{[Customer].[All Customer], [Customer].[Customer No].Members} on rows

from TotalTable where ([Time].&[2007])')

The error message:

The MDX queries executed through the OPENQUERY function returns the following error messages for some members of the dimension used to construct them:

Could not get the data of the row from the OLE DB provider 'MSOLAP'.

OLE DB error trace [OLE/DB Provider 'MSOLAP' IRowset::GetData returned 0x80040e21:Data status returned from the provider: [COLUMN_NAME=[Measures].[Data] STATUS=DBSTATUS_E_UNAVAILABLE], [COLUMN_NAME=[Measures].[Invoices E3] STATUS=DBSTATUS_E_UNAVAILABLE]]

The same MDX Queries execute without any problems through the MDX Sample

Application!

OBS: I have SP4 on both SQL and Analysis

Hi there i am wondering if you managed to solve this, as I had exactly the same problem? A query that worked previously, is now not working. I am also on SQL Server 2000 Analysis SP 4

Calculated Member not working

Please could somebody help me to understand what is wrong with the following calculated member definition (tried many variations of this now, always the same problem)

CREATE MEMBER CURRENTCUBE.[MEASURES].RatioVal

AS [Measures].[Val] / SUM(([Tbl Acc Codes].[Tbl Acc Lvl1].&[OUR],[Tbl Acc Codes].[Tbl Acc Lvl2].&[CORE],[Tbl Acc Codes].[Tbl Acc Lvl3 - Lvl 3 Code].&[INCOME]),[Measures].[Val]),

NON_EMPTY_BEHAVIOR = { [Val] },

VISIBLE = 1 ;

All I want is to show in cube browser alongside Measures.Val is another member called RatioVal.

RatioVal should be Val divided by the sum of Val with a lvl1 of 'OUR', a lvl2 of 'CORE' and a lvl3 of 'INCOME'

At the moment when I browse my cube I get a RatioVal subtotal for each lvl1, and each lvl2 but anything deeper into the hierarchy than that shows 1.#INF

Please give me any clues you can, this is driving me nuts!

Thanks

Hi Wayne,

The 1.#NF error indicates division by zero - so I would guess that your problem is caused by the expression

SUM(([Tbl Acc Codes].[Tbl Acc Lvl1].&[OUR],[Tbl Acc Codes].[Tbl Acc Lvl2].&[CORE],[Tbl Acc Codes].[Tbl Acc Lvl3 - Lvl 3 Code].&[INCOME]),[Measures].[Val]),

evaluating to zero at the lower levels. Without knowing more about your cube I can't say why this might be though.

Even after you've fixed this, if you think that there's ever chance that this expression could still evaluate to zero, then you should put add a test to your calculated member to handle this scenario, something like this:

CREATE MEMBER CURRENTCUBE.[MEASURES].RatioVal

AS

IIF(SUM(([Tbl Acc Codes].[Tbl Acc Lvl1].&[OUR],[Tbl Acc Codes].[Tbl Acc Lvl2].&[CORE],[Tbl Acc Codes].[Tbl Acc Lvl3 - Lvl 3 Code].&[INCOME]),[Measures].[Val]) = 0, NULL,

[Measures].[Val] / SUM(([Tbl Acc Codes].[Tbl Acc Lvl1].&[OUR],[Tbl Acc Codes].[Tbl Acc Lvl2].&[CORE],[Tbl Acc Codes].[Tbl Acc Lvl3 - Lvl 3 Code].&[INCOME]),[Measures].[Val])

),

NON_EMPTY_BEHAVIOR = { [Val] },

VISIBLE = 1 ;

HTH,

Chris