One more question regarding Reporting action

All the reporting action samples I read from AdventureWorks, when pass link and parameter to the report in reporting service, is linked to the report as Analysis Service data source based report.

I am wondering if reporting action can pass parameter to regular relational db based reporting service report not Analysis Service data source based report.

If yes, could you share a simple sample?

I can't see why the data source of a report would matter, when using a reporting action. The Adventure Works examples may all be using an Analysis Services data source just for convenience. Have you tried editing an existing Adventure Works reporting action, to point to a relational report instead?|||

Hi Deepak, thank you so much for your reply! Yes, I did try the following using AW AS and AW sample reports RS projects:

Here is my reporting action:

Name: Sales Order Detail

Target Type: Dimension members

Target object: Internet Sales Order Detail

ReportServer:localhost

ReportPath:ReportServer?/AdventureWorks Sample Reports/Sales Order Detail

Parameters NamealesOrderNumber (Match the one at Sales Order Detail report

Parameter Value: [Internet Sales Order Details].[Sales Order Number].[Sales Order Number].CurrentMember.Name

And I make sure Sales Order Detail Report is running correctly and parameter is matching the parameter name I defined at the cube reporting action. Then I deploy the cube, however when drop the Sale Order Number dimension and right click, there is no Reporting Action showing up.

Could you give me some pointers?

Thanks

|||

Did you configure the "Caption", under "Additional Properties" - if not, you can refer to the Adventure Works "Sales Reason Comparisons" action, whose Caption is defined as an MDX expression:

"Sales Reason Comparisons for " +

[Product].[Category].CurrentMember.Member_Caption + "..."

|||

Thanks again. Just did that and deployed.

"Sales Reason Comparisons for " + [Internet Sales Order Details].[Sales Order Number].[Sales Order Number].CurrentMember.Member_Caption + "..."

However reporting action is still not showing. Deepak, anything else I miss?

Thanks

|||Can't think of anything else - are you able to see the standard Reporting action which comes with Adventure Works, ie. "Sales Reason Comparisons"? If so, I would suggest making a test copy of Adventure Works, and progressively modifying this action to incorporate your report in small steps, till it breaks - or maybe it doesn't break!

Once only delivery (high availibiltity) ?

Hi There

I was wondering if someone could elaborate (or provide a link) on how the once only delivery works for servcie broker.

For example you have DB1 that sends messages to DB2. DB1 has corruption at 1pm, you retore the database to 12:30pm, this will have all the messages that were in this instance at 12:30 , however between 12:30 AND 1PM several successful messages were sent. These messages are in the restored DB1 queue, they get sent again?

How does service borker ensure that these messages are not processed again. The only thing i can think of is that the inititator or target keep a complete history of messages processed. But surely this "table" would get huge and slow down servcie broker if it had to check this tale every time a message is sent or received.

I cannot find much in BOL on this topic, or maybe i cannot find the topic.

Thanx

This question has no easy answer. On a typical database application you deal with the potential data loss of 'back-in-time' recovery by inspecting the application data and comparing it with some other record (web logs, or a paper trail like invoices or receipts etc) and you can probably recreate the lost data. If the data is critical for you business, you take steps to ensure such event cannot happen (e.g. you run with synchronous database mirroring, or you enforce some hardware disk mirroring).

On a Service Broker application the situation is more compl,ex, because you are now faced with a distributed state between the services involved. As you noticed, a back-in-time restore will put some conversation out of sync. Unless you roll back in time the other database as well (by restoring to an apropiate LSN), you cannot continue those conversations. The most difficult problem is when DB1 has sent a message between 12:30 and 1, DB2 has received the said message and acted upon (e.g. had printed and mailed a paper invoice). If you restore DB1 at 12:30, DB2 now has a message that DB1 had not sent. There is no possible 'history table' that can recover this situation.

How to act on such situations really depends on the business meaning of each conversation. After a restore, DB1 broker will be disabled. One would have to inspect the state of pending conversations in DB1 and compare them with the state of DB2 to see if a conversation can be allowed to continue or it has to be errored or event completely cleaned up. One possible action is to error out all restored conversations in DB1 (using ALTER DATABASE ... SET ERROR_BROKER_CONVERSATIONS) and then enable back the broker in DB1. This way any conversation between DB1 and DB2 that was still pending will be errored (and the application has to deal with the error appropiately). Any conversation that was started by DB1 between 12:30 and 1 and now only exists on DB2 will eventualy time out and error (and this is another reason why one should use the LIFETIME option in BEGIN DIALOG).

And again, if the business loss from loosing those conversations is critical, you must deploy a solution that simply does not allow this to happen (like a mirrored database solution).

HTH,
~ Remus

OMG, Having so much trouble connecting my SQL database/server to Dreamweaver

I think its my server because I cant get it to link up to an Access data base
either, heres the error I get.
[DBNETLIB]ConnectionOpen (Connect()),]SQL Server does not exist or access
denied.
Im pretty sure that all the fields that I entered are correct though.
What could be the problem?
The connection string is right I believe. How do i configure the network
protocol?
Jean-Pierre Riehl wrote:[vbcol=seagreen]
>What is your connection string ?
>Have you configure the right network protocol in SQL Server Configuration
>Manager ?
>--
>Jean-Pierre Riehl
>http://www.blog.djeepy1.net
>http://www.bewise.fr
>[quoted text clipped - 6 lines]
|||To configure your allowable network protocols...
SQL 2000 you are going to use the "Server/Client Network Utilities" located
in your program files...
http://msdn2.microsoft.com/en-us/library/aa213770(SQL.80).aspx
Basically make sure that TCIIP is in the enabled box.
In SQL 2005 you can use your "Surface Area Configuration" Tool
http://msdn2.microsoft.com/en-us/library/ms173748.aspx
thanks,

/*
Warren Brunk - MCITP,MCTS,MCDBA
www.TechIntSolutions.com
www.RealWorldSQL.com
*/
"Qaiphyx" <u40439@.uwe> wrote in message news:7dcd267ca767e@.uwe...
> The connection string is right I believe. How do i configure the network
> protocol?
> Jean-Pierre Riehl wrote:
>

OMG, Having so much trouble connecting my SQL database/server to Dreamweaver

I think its my server because I cant get it to link up to an Access data base
either, heres the error I get.
[DBNETLIB]ConnectionOpen (Connect()),]SQL Server does not exist or access
denied.
Im pretty sure that all the fields that I entered are correct though.
What could be the problem?What is your connection string ?
Have you configure the right network protocol in SQL Server Configuration
Manager ?
--
Jean-Pierre Riehl
http://www.blog.djeepy1.net
http://www.bewise.fr
"Qaiphyx" <u40439@.uwe> wrote in message news:7dcaeb7bf1706@.uwe...
>I think its my server because I cant get it to link up to an Access data
>base
> either, heres the error I get.
> [DBNETLIB]ConnectionOpen (Connect()),]SQL Server does not exist or access
> denied.
> Im pretty sure that all the fields that I entered are correct though.
> What could be the problem?
>|||The connection string is right I believe. How do i configure the network
protocol?
Jean-Pierre Riehl wrote:
>What is your connection string ?
>Have you configure the right network protocol in SQL Server Configuration
>Manager ?
>--
>Jean-Pierre Riehl
>http://www.blog.djeepy1.net
>http://www.bewise.fr
>>I think its my server because I cant get it to link up to an Access data
>>base
>[quoted text clipped - 6 lines]
>> What could be the problem?|||To configure your allowable network protocols...
SQL 2000 you are going to use the "Server/Client Network Utilities" located
in your program files...
http://msdn2.microsoft.com/en-us/library/aa213770(SQL.80).aspx
Basically make sure that TCIIP is in the enabled box.
In SQL 2005 you can use your "Surface Area Configuration" Tool
http://msdn2.microsoft.com/en-us/library/ms173748.aspx
thanks,
/*
Warren Brunk - MCITP,MCTS,MCDBA
www.TechIntSolutions.com
www.RealWorldSQL.com
*/
"Qaiphyx" <u40439@.uwe> wrote in message news:7dcd267ca767e@.uwe...
> The connection string is right I believe. How do i configure the network
> protocol?
> Jean-Pierre Riehl wrote:
>>What is your connection string ?
>>Have you configure the right network protocol in SQL Server Configuration
>>Manager ?
>>--
>>Jean-Pierre Riehl
>>http://www.blog.djeepy1.net
>>http://www.bewise.fr
>>I think its my server because I cant get it to link up to an Access data
>>base
>>[quoted text clipped - 6 lines]
>> What could be the problem?
>

OLE/DB provider returned message: Deferred prepare could not be completed

I have 2 SQL servers. And in the first one I have added the second SQL as a Link Server. When I run an SQL statement on the linked server I get the following message.

Server: Msg 7202, Level 11, State 1, Line 1
Could not find server 'PROD' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
[OLE/DB provider returned message: Deferred prepare could not be completed.]

The SQL statement that I am runnins is

Select * from openquery(PROD,'Select * from PROD.GMS.dbo.qryDispCL')

But when I run only the SQL statement "Select * from PROD.GMS.dbo.qryDispCL" it works perfect. But I need to have the first statement running.

Please help. Your valuable feedback is greatly appriciated.

It's really strange... You can run this to check registered server:

EXEC sp_helpservers

If the server 'PROD' is not in the result, then run this to add it:

EXEC sp_addlinkedserver 'PROD'

Note: you may need to add login information for the 'PROD' server with 'sp_addlinkedsrvlogin'.

OLE DB provider "MSOLAP" linked svr "Errors in the back-end database data provide

Is it that I have a syntax error in the nested OPENQUERY or is there another issue? Do I need to specify a different provider in the Server Link such as OLEDB? Non-nested OPENQUERYs work fine.

I'm generally following theTips and Tricks article.

"Executing predictions from the SQL Server relational engine". One problem is the sample doesn't actually complete the example query after the second nested OPENQUERY call.

e.g.

SELECT * FROM OPENQUERY(DMServer,

'select … FROM Modell PREDICTION JOIN OPENQUERY…')

The SQL Server server link's provider is configured to allow adhoc access. I appears that the inner OPENQUERY cannot be prepared by Analysis Server or the Server link provider? but I need to return a key value t.[CardTransactionID] for joining to SQL Server data elements.

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Errors in the back-end database access module. The data provider does not support preparing queries.".

Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query

SELECT * FROM OPENQUERY(DMServer,

'SELECT

t.[CardTransactionID],

t.[PostingDate],

[Misuse Abuse Profile].[Even Dollar Purchase],

PredictProbability([Misuse Abuse Profile].[Even Dollar Purchase]) AS Score,

PredictSupport([Misuse Abuse Profile].[Even Dollar Purchase]) AS Suppt,

t.[BillingAmount]

FROM

[Misuse Abuse Profile]

PREDICTION JOIN

OPENQUERY([Athena Dev],

''SELECT

[CardTransactionID],

[PostingDate],

[BillingAmount],

[AccountNumber],

[SupplierStateProvinceCode],

[MerchantCategoryCode],

[PurchaseIDFormat],

[TransactionTime],

[TaxAmountIncludedCode],

[Tax2AmountIncludedCode],

[OrderTypeCode],

[MemoPostFlag],

[EvenDollarPurchase]

FROM

[dbo].[vMisuseAbuseProfile]

'') AS t

ON

[Misuse Abuse Profile].[Account Number] = t.[AccountNumber] AND

[Misuse Abuse Profile].[Supplier State Province Code] = t.[SupplierStateProvinceCode] AND

[Misuse Abuse Profile].[Merchant Category Code] = t.[MerchantCategoryCode] AND

[Misuse Abuse Profile].[Purchase ID Format] = t.[PurchaseIDFormat] AND

[Misuse Abuse Profile].[Transaction Time] = t.[TransactionTime] AND

[Misuse Abuse Profile].[Tax Amount Included Code] = t.[TaxAmountIncludedCode] AND

[Misuse Abuse Profile].[Tax2 Amount Included Code] = t.[Tax2AmountIncludedCode] AND

[Misuse Abuse Profile].[Order Type Code] = t.[OrderTypeCode] AND

[Misuse Abuse Profile].[Memo Post Flag] = t.[MemoPostFlag] AND

[Misuse Abuse Profile].[Even Dollar Purchase] = t.[EvenDollarPurchase]

')

In desparation I tried returning the case key (CardTransactionID) and the predictive column elements but I get an error when I try that. I assume this is a no-no?

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 15.".

It appears the default Analysis Services Data Source wizard selection of SQL Native Client Data Source provider cannot prepare an OPENQUERY if passed from a linked server? I changed the Datasource to Provider=SQLOLEDB.1 and the query now works.

|||this is very odd - we will look into it - thanks for reporting.|||

Hi Bill,

Could you give more details about the setup. Is the SQL server where you defined the linked server DMTest on the same machine with the Analysis Server it refers too? And also is the SQL server refered on the inner openquery datasource [Athena Dev] on a different machine than the machines with AS and SQL server with added linked server?

Thanks,

Dana Cristofor

OLE DB provider "MSOLAP" linked svr "Errors in the back-end database data provide

Is it that I have a syntax error in the nested OPENQUERY or is there another issue? Do I need to specify a different provider in the Server Link such as OLEDB? Non-nested OPENQUERYs work fine.

I'm generally following theTips and Tricks article.

"Executing predictions from the SQL Server relational engine". One problem is the sample doesn't actually complete the example query after the second nested OPENQUERY call.

e.g.

SELECT * FROM OPENQUERY(DMServer,

'select … FROM Modell PREDICTION JOIN OPENQUERY…')

The SQL Server server link's provider is configured to allow adhoc access. I appears that the inner OPENQUERY cannot be prepared by Analysis Server or the Server link provider? but I need to return a key value t.[CardTransactionID] for joining to SQL Server data elements.

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Errors in the back-end database access module. The data provider does not support preparing queries.".

Msg 7321, Level 16, State 2, Line 2 An error occurred while preparing the query

SELECT * FROM OPENQUERY(DMServer,

'SELECT

t.[CardTransactionID],

t.[PostingDate],

[Misuse Abuse Profile].[Even Dollar Purchase],

PredictProbability([Misuse Abuse Profile].[Even Dollar Purchase]) AS Score,

PredictSupport([Misuse Abuse Profile].[Even Dollar Purchase]) AS Suppt,

t.[BillingAmount]

FROM

[Misuse Abuse Profile]

PREDICTION JOIN

OPENQUERY([Athena Dev],

''SELECT

[CardTransactionID],

[PostingDate],

[BillingAmount],

[AccountNumber],

[SupplierStateProvinceCode],

[MerchantCategoryCode],

[PurchaseIDFormat],

[TransactionTime],

[TaxAmountIncludedCode],

[Tax2AmountIncludedCode],

[OrderTypeCode],

[MemoPostFlag],

[EvenDollarPurchase]

FROM

[dbo].[vMisuseAbuseProfile]

'') AS t

ON

[Misuse Abuse Profile].[Account Number] = t.[AccountNumber] AND

[Misuse Abuse Profile].[Supplier State Province Code] = t.[SupplierStateProvinceCode] AND

[Misuse Abuse Profile].[Merchant Category Code] = t.[MerchantCategoryCode] AND

[Misuse Abuse Profile].[Purchase ID Format] = t.[PurchaseIDFormat] AND

[Misuse Abuse Profile].[Transaction Time] = t.[TransactionTime] AND

[Misuse Abuse Profile].[Tax Amount Included Code] = t.[TaxAmountIncludedCode] AND

[Misuse Abuse Profile].[Tax2 Amount Included Code] = t.[Tax2AmountIncludedCode] AND

[Misuse Abuse Profile].[Order Type Code] = t.[OrderTypeCode] AND

[Misuse Abuse Profile].[Memo Post Flag] = t.[MemoPostFlag] AND

[Misuse Abuse Profile].[Even Dollar Purchase] = t.[EvenDollarPurchase]

')

In desparation I tried returning the case key (CardTransactionID) and the predictive column elements but I get an error when I try that. I assume this is a no-no?

OLE DB provider "MSOLAP" for linked server "DMServer" returned message "Error (Data mining): Only a predictable column (or a column that is related to a predictable column) can be referenced from the mining model in the context at line 2, column 15.".

It appears the default Analysis Services Data Source wizard selection of SQL Native Client Data Source provider cannot prepare an OPENQUERY if passed from a linked server? I changed the Datasource to Provider=SQLOLEDB.1 and the query now works.

|||this is very odd - we will look into it - thanks for reporting.|||

Hi Bill,

Could you give more details about the setup. Is the SQL server where you defined the linked server DMTest on the same machine with the Analysis Server it refers too? And also is the SQL server refered on the inner openquery datasource [Athena Dev] on a different machine than the machines with AS and SQL server with added linked server?

Thanks,

Dana Cristofor