Do pass thru query connections persist?

I have an Access 2003 front end with a SQL Server 2005 Express backend. I was thinking of using pass thru queries as row sources for some combo boxes such as states/countries for addresses. My question is do pass thru queries, when used as a row source, keep a connection to the DB server? Or do they get the data, disconnect and populate the control?

I realize I could populate the controls with code, but this seems less hassle and will overcome the ValueList size limit if needed.Unless you explicitely dis-connect the connection always remains open. this will hold true even if you use a DB control.|||Hi

Access will open a connection the first time it interacts with SQL Server (linked table, pass through) and retain this connection until the application closes. A pass through query, however, is like a client side cursor (as I understand it) - it uses no further server resources once it has run.

BTW - disconnected list filling is perfectly simple and more secure than pass throughs:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart01/html/sa01l8.asp
The bottom entry (Assigning recordsets to controls) is one I like.

HTH|||Pootle that article was very helpful. It also mentioned using a properly shaped recordset for reports which was a question of mine on another post.

I did a quick search on properly shaped recordsets but didn't find anything. Does anyone know what it is?

Using a Access Project I was able to have a report use an ADO recordset however it seems the connection and recordset must remain open the whole time the report is open. When using the a recordset with a control I opened the recordset assigned it to the control and closed it and everything was fine. When I did the same for the report it would not open. If I don't close the recordset or connection the report works fine. Is there anyway around this?

Pootle thanks again for the article.|||Using a Access Project I was able to have a report use an ADO recordset however it seems the connection and recordset must remain open the whole time the report is open. When using the a recordset with a control I opened the recordset assigned it to the control and closed it and everything was fine. When I did the same for the report it would not open. If I don't close the recordset or connection the report works fine. Is there anyway around this?The key will be the cursor location - that is the magic setting that takes you into the world of The Disconnected. Did you set it the location to client in your first attempt (remembering that the default is server)?|||Yes, I copied the code verbatim from the control code. In the control code I had not set the connection cursor so I did it in the report code and it still did not work.

Here is my code behind the report.

Private Sub Report_Open(Cancel As Integer)

Dim conGlob As New ADODB.Connection
Dim rst As New ADODB.Recordset

conGlob.ConnectionString = "Provider=SQLOLEDB;" _
& "Data Source=SERVER;" _
& "Initial Catalog=DatabaseTable;" _
& "Trusted_Connection=Yes;"

conGlob.CursorLocation = adUseClient

conGlob.Open

With rst
.ActiveConnection = conGlob
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockReadOnly
End With

rst.Open "usp_GetStates", , , , adCmdStoredProc

Set Me.Recordset = rst

rst.Close
conGlob.Close

End Sub|||Your best bet would be to use an access data project and use stored procedures

First time I tried to "upgrade" a consultants "application" I noticed that one form opened 19 connections

1 for every objects data source and an additional connection for any object that was updateable...it was very ugly and very slow|||There's nothing inherent to disconnected access that means you need to open multiple connections. One form, one connection. 19+ connections sounds ugly and there would have been an overhead but I would have thought the other processes (populating 19 objects for starters) would be the killer. I imagine you smoothed a lot of other rough edges to get the improvement I presume you got.|||There's nothing inherent to disconnected access that means you need to open multiple connections. One form, one connection. 19+ connections sounds ugly and there would have been an overhead but I would have thought the other processes (populating 19 objects for starters) would be the killer. I imagine you smoothed a lot of other rough edges to get the improvement I presume you got.

Yeah, it's called a total rewrite using Java and actually doing data modeling with the business BEFORE we did a conversion|||I may have figured it out. I have been using the activity monitor in Management Studio Express looking at the active processes and locks.

If I set the recordset activeConnection to nothing I can then close the original connection without the report closing. In the activity monitor it looks like the connection times out or something. It doesn't disappear right away like when I close the report but it does after awhile even if the report is still open.

Do not SELECT

Hi,
Is there any way how to formulate SQL query to select all columns except
column D and E?
So if the table has the columns A,B,C,D the result woud contain only columns
A,B,C
SELECT " * except D,E"
FROM myTable
Thanks,
Lubomir
SELECT A, B, C
FROM myTable
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:A8B69DA2-E5B7-4E91-BCE9-89A673C6CCA9@.microsoft.com...
> Hi,
> Is there any way how to formulate SQL query to select all columns except
> column D and E?
> So if the table has the columns A,B,C,D the result woud contain only
> columns
> A,B,C
> SELECT " * except D,E"
> FROM myTable
> Thanks,
> Lubomir
|||Hello,
If you want it permamanent then you can create a view and then query the
view
CREATE VIEW A1
as
Select A,B,C from myTable
After the creation query the view
Select * from A1
THanks
Hari
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:A8B69DA2-E5B7-4E91-BCE9-89A673C6CCA9@.microsoft.com...
> Hi,
> Is there any way how to formulate SQL query to select all columns except
> column D and E?
> So if the table has the columns A,B,C,D the result woud contain only
> columns
> A,B,C
> SELECT " * except D,E"
> FROM myTable
> Thanks,
> Lubomir
|||The problem is, the tables are created on the fly, so I don't know what
columns the particular table will have. I know however, every table has two
columns ("help" columns) that will not be displayed, as they are used for
another purposes.
From that reason, it would be very convenient to make query like SELECT *
and to specify those 2 columns to be exclusive.
Thanks,
Lubomir
"Hari Prasad" wrote:

> Hello,
> If you want it permamanent then you can create a view and then query the
> view
> CREATE VIEW A1
> as
> Select A,B,C from myTable
> After the creation query the view
> Select * from A1
> THanks
> Hari
>
> "Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
> news:A8B69DA2-E5B7-4E91-BCE9-89A673C6CCA9@.microsoft.com...
>
>
|||On Fri, 4 May 2007 09:37:00 -0700, Lubomir wrote:

>The problem is, the tables are created on the fly, so I don't know what
>columns the particular table will have.
Hi Lubomir,
That is indeed a problem. And it's also a sign that you are using the
database in a way that it's not intended to be used - applications that
need to create tables on the fly are almost always the result of some
bad design decisions.
Could you explain in some more detail WHY your application does not have
a fixed data model?

>From that reason, it would be very convenient to make query like SELECT *
>and to specify those 2 columns to be exclusive.
There is no syntax for this in SQL. (And if anyone ever proposes it, I'd
vote against it - personally, I'd rather remove the SELECT * than to
extend it!)
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||How do you manage, populate, update, etc., tables that you don't know the
structure of? How do you know you are getting the right number of columns,
with the correct names, in your UI application? How do you know that your
datasets will not break the front end? And if you're just not "displaying"
the help columns, then just don't "display" them. What you do or don't
display in the UI doesn't have to be the same as what you retrieve from the
database.
In all seriousness though, get rid of the SELECT * and start naming your
columns. It will save you a bunch of headaches down the road.
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:8587187A-53B8-4C53-A2DD-2F300FADFF3A@.microsoft.com...[vbcol=seagreen]
> The problem is, the tables are created on the fly, so I don't know what
> columns the particular table will have. I know however, every table has
> two
> columns ("help" columns) that will not be displayed, as they are used for
> another purposes.
> From that reason, it would be very convenient to make query like SELECT *
> and to specify those 2 columns to be exclusive.
> Thanks,
> Lubomir
> "Hari Prasad" wrote:
|||Hi Hugo,
The reason is, that that this application serves more application (like
plugins) with their own tables.
Lubomir
"Hugo Kornelis" wrote:

> On Fri, 4 May 2007 09:37:00 -0700, Lubomir wrote:
>
> Hi Lubomir,
> That is indeed a problem. And it's also a sign that you are using the
> database in a way that it's not intended to be used - applications that
> need to create tables on the fly are almost always the result of some
> bad design decisions.
> Could you explain in some more detail WHY your application does not have
> a fixed data model?
>
> There is no syntax for this in SQL. (And if anyone ever proposes it, I'd
> vote against it - personally, I'd rather remove the SELECT * than to
> extend it!)
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
|||On Mon, 7 May 2007 09:38:00 -0700, Lubomir wrote:

>Hi Hugo,
>The reason is, that that this application serves more application (like
>plugins) with their own tables.
Hi Lubomir,
As long as the data requirements for all these plugin applications are
relatively stable, they can each have their own set of tables that
you'll have to design and deploy once (and possibly more often, if and
when requirements change).
If the data requirements can change on a daily basis, you're probably
best advised to move away from relational databases, since they're
really designed to be used with a fixed datamodel.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||If you are talking about SQL 2005, then you can write DDL trigger and
programically create view or procedure wich select all columns from table
exept those 2.
Ramunas
"Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
news:07134C0B-DBB4-4B60-ADDD-BF2B2E808CB1@.microsoft.com...[vbcol=seagreen]
> Hi Hugo,
> The reason is, that that this application serves more application (like
> plugins) with their own tables.
> Lubomir
>
> "Hugo Kornelis" wrote:
*[vbcol=seagreen]
|||Yes, that could work.
Thanks,
Lubomir
"Ramunas Balukonis" wrote:

> If you are talking about SQL 2005, then you can write DDL trigger and
> programically create view or procedure wich select all columns from table
> exept those 2.
> Ramunas
> "Lubomir" <Lubomir@.discussions.microsoft.com> wrote in message
> news:07134C0B-DBB4-4B60-ADDD-BF2B2E808CB1@.microsoft.com...
> *
>
>

Do not Email if No Data

Is there a way to set up a subscription so that it does not email the
blank report if there was no data resulting from the query?
Thanks
BobBob,
Did you figure this out ? Just posted the same issue.
Thanks, Steve.
"Bob" wrote:
> Is there a way to set up a subscription so that it does not email the
> blank report if there was no data resulting from the query?
> Thanks
> Bob
>

Do not display the result of my long text string, approx about 400 characters

I have the following problem. My SQL Query that i wrote works but the result that is displayed in Query analyzer cuts most of my long text that I want in my result. The long text string is approx about 400 characters and the type is varchar of the field. Any ideas??

SELECT '510', PRODCLASSID
, '1', COMPONENTID,'ENG'+SPACE(2),'#'+SPACE(254),'#'+SPAC E(254),'#'+SPACE(254),'#'+SPACE(99),externalid,
'Desc1' = CASE
WHEN SUBSTRING(externalid,1,2) = 'MF'
THEN 'Full machine warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'MP'
THEN 'Full machine warranty, parts only : parts covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PF'
THEN 'Power line warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. '
WHEN SUBSTRING(externalid,1,2) = 'PP'
THEN 'Power line warranty, parts only : parts are covered at warranty rates applicable at the time of repair. '
END
+
CASE
WHEN SUBSTRING(externalid,LEN(externalid)- 3,4) = '2018'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 18 month or 2000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '3024'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 24 month or 3000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '4030'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 30 month or 4000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '5036'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 36 month or 5000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '6042'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 42 month or 6000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '8054'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 54 month or 8000 HRS, whichever comes first. '
WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '1074'
THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 74 month or 10000 HRS, whichever comes first. '
END
+ 'Flexible warranty is handled according to the procedures described in ESPPM 3-10.'
+
CASE

WHEN prodclassid IN ('P1','P11','P8','P9')
THEN ' (mileage limited to 300 km)'
WHEN prodclassid IN ('P7')
THEN ' (mileage limited to 200 km)'
ELSE NULL
END
+
SPACE(5000 - LEN('Desc1'))
......If you are using the MS Query Analyzer from the SQL 2000 Client Tools, you can set the maximum column length. Select Tools | Options | Results and the length control is near the middle of the sheet.

-PatPsql

Do not delete things, it bites

Hello,

I noticed that when you delete things or substitute tables, you get in trouble.

- Delete a named query used in a partition before deleting the partition

- Delete a named Calculation from a table in the Ds view, whathever you do

- Replace a table with another table which has less columns even if none of the lesser columns are used nowhere.

The XML do not get updated accordingly leading in all kinds of errors, some requiring restore from an older version.

Just a FYI.

BTY, I do not know how to submit bug reports.

Philippe

Build 9.00.2175.00

go to connect.microsoft.com|||

I think we have both come across the same problem (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=640957&SiteID=1).

For permissions, I have manually cleaned up the xml files. If you post this on connect.microsoft.com, let me know as I cannot do that. I can vote for it.

btw, how did you get build 9.00.2175.00?

|||

Yes, the changes you made in DSV will not propagate to other linked ojects. Since you are deleting things from DSV, the related binding will be broken and it is up to the user to rebind or remove it. Anyway, you should get validation error when you try to deploy and find out the broken binding.

We have already got customer request on this issue. We will consider to improve the user experience in the next release.

|||I disagree. It is not a case of improving the user experience. It is a bug.|||

A bug means that the software is not doing what it is supposed to do in the design spec. This is not a bug because we don't have it in design spec. You can say it is design flaw. However, from the user experience, people hated to see something that deleted automatically without any control. Therefore, what I suggested is to enhance the user experience. After deleting the objects in DSV, we could show a list box to list all the broken bindings and give user the option to delete the broken objects. Some advanced users may want to leave the broken bindings so that they can bind to different things instead of regenerate the objects or redo all the modifications.

Anyway, we already addressed the issue. We will consider it in future release.

Thanks

|||

This often happens when building complex products such as SQL2005. Do not take it the wrong way.

When is the planned release of the fix?

Do Inserts into Temptable enforce order of select?

I have a temp table which I am trying to do a select INTO. The temptable has an identity field which increments by one. In the select query, I am doing an order by. It does not appear that the temp table retains the physical ordering of the records from the order by. When I do a select * FROM temptable without an order by, the first record that is displayed as an id of 113. Then about the 42nd record in the temp table is the ID=1 record. In addition, the table records are not ordered by the original order of the select that inserted into the temp table.

From this I am led to believe that SQL does not enforce the orders of records on INSERT into statesments. Is this a correct assumption, or might there be something else going on?Please take a look at the blog post below:

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

Ordering is not guaranteed for SELECT queries unless you include an explicit ORDER BY in the outer-most query. Similarly, the order of insertion of rows is not guranteed either. The identity values are however generated based on the order specified in the ORDER BY in INSERT...SELECT statement.

do i link or use different query?

first post, forum looks like it might help me keep from pulling my hair out :)

noob here to sql and crystal reports, but learning as I go. I hope I can get a bit of guidance here.

I am designing a report to show productivity of employees across our system. I have a query that I am using to extract data for each column and the query will always return 'employee' as the first column and then the data I want as the second column. (in the basic query results)

So, the first 2 columns on the report are both columns from the first query. Employee and hours worked. For the remainder of the columns, I am just insterting the 2nd column from the next queries. Sales, Trades, etc.

The problem I just ran into is that I want to display something which not every employee has a record for. For instance, I want to track how many memberships each employee sold. The way my query is written, when I add it to the report, it supressed the data for the employees that have not sold any memberships. Since it supressed the whole row, I cannot view the results from the other data. My thought is that it should keep the employee row, but list a '0' for that particular column.

Is this going to be a query re-write, or can crystal do what I need?

thanks in advance.Change your link from your employee query to your new query to a left outer join. That way all employee's will show up even if no memberships were sold.
GJ|||thanks. took me a minute to find out where to do that, but i found it.

how about getting those null values to display as a 0 (zero)

and if you can tell me where to find it this time :)

thanks again!|||With Crystal open go to file options and click the reporting tab. Check the convert database null values to default. If greyed out go to database tab and uncheck grouping on server, then you can check the null values check box.

GJ.|||awesome. you know, i try to dig around before i ask, because i just love learning new stuff. i actually got it to work a different way too by using a if else function with some searching around. im gonna try it your way too.

mind if i ask a couple more questions? hehe

i have basically created one report with 23 sub reports in the report footer.

1) is there a way to uniformly space out the subreports (vertically) if more employees show up so i dont have to edit the layout? maybe a percentage of distance away from the bottom of the report above?

2) I figured out how to alternate row color, but if my subreports have an uneven amount of rows, i get 2 rows with the same color

IF (RecordNumber MOD 2 = 0) THEN
crSilver
ELSE
DefaultAttribute

maybe the formula is wrong if i want to have subreports?

3) When using the summary feature, I cannot seem to get it accurate. I have tested a couple sum summarys and avg summaries. If I double check it against an exported excel file, the numbers don't match. Out of the 23 stores, we have several 'regions'. I was initially going to create region reports to be summarized, so that when I add them to the main report, they can see the sum or average of certain data for other regions.

well, that is pretty much all i got left to figure out on this report, so thanks so much for the help!

Do I Get the GUI with SQL Server 2005 Developer Edition

Hi,

Do I Get the GUI (Query Analyser and Enterprice Manager) with SQL Server 2005 Developer edition (which is available as free with Visual Studio 2005). I will be thankful if u give information.

SQL Server Developer edition have all the features of SQL Server Enterprise edition... But the enterprise manager equivalent in SQL Server 2005 is Sql Server Management Stuiod...

Refer these link for an overview

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

http://www.microsoft.com/sql/editions/developer/default.mspx

Madhu

|||

SSMS is included with the installation media, however, it is not installed with the default installation.

You will need to install the 'Client Tools' from the installation media in order to obtain Management Studio.

|||

K. Ravinder Reddy wrote:

Do I Get the GUI (Query Analyser and Enterprice Manager) with SQL Server 2005 Developer edition (which is available as free with Visual Studio 2005). I will be thankful if u give information.

Are you sure you have the Developer Edition? I know that Visual Studio 2005 comes with SQL Server Express Edition (which is different than Developer Edition). The tool you are looking for is called SQL Server Management Studio. It combines and extends most of the functionality of Query Analyzer and Enterprise Manager (SQL Server 2000 tools).

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

sql

Do for each - how to write it more graceful?

"Do something for each row of the query"
The only way I know is:

--
declare @.C cursor
set @.C= cursor for
select F from T where ...
declare @.F int
open @.C
while 0=0 begin
fetch next from @.C into @.F
if not(@.@.FETCH_STATUS = 0) break
exec myStoredProc @.F
end
close @.C
deallocate @.C
--

How to write it simpler, maybe with implicit cursors?
For example, in the Borland Interbase it would be like:

--
declare variable F integer;
for select F from T into :F
do execute procedure myStoredProc :F;
--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!What does myStoredProc actually do? If the procedure just does some data
manipulation then maybe you can rewrite that code based on your cursor
query. For example:

SELECT ...
FROM Something
WHERE f
IN
(select F from T where ...)

As another alternative to a cursor you can try something like this, which
may be reasonably acceptable if the column F is unique and indexed.

DECLARE @.f INTEGER

WHILE EXISTS
(SELECT *
FROM T
WHERE f>@.f
OR @.f IS NULL)
BEGIN
SET @.f =
(SELECT MIN(f)
FROM T
WHERE f>@.f
OR @.f IS NULL)
EXEC myStoredProc @.F
END

--
David Portas
SQL Server MVP
--|||Thank you for these two ideas!
But... first case is not quite fit for me. My stored procedure is too
complicated, it makes some queries to remote server, calls some extended
procedures and so on.
Second case seems better, but (IMHO) it's a trick. And this trick (IMHO)
is too slow relative to case with cursor and fetch. And it looks like
T-SQL syntax limitation - no any common and short way to do something
for each row, like cycle by select :(

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

1) Build a Dynamic SQL statement in a loop and then execute it.

2) Put the loop in the middle tier or client code.

3) For non-production use you could try the undocumented xp_execresultset.
Example:

EXEC master..xp_execresultset
'SELECT ''EXEC myStoredProc ''+CAST(f AS VARCHAR) FROM T','DBNAME'

--
David Portas
SQL Server MVP
--|||Evgeny Gopengauz (evgop@.ucs.ru) writes:
> "Do something for each row of the query"
> The only way I know is:
> --
> declare @.C cursor
> set @.C= cursor for
> select F from T where ...
> declare @.F int
> open @.C
> while 0=0 begin
> fetch next from @.C into @.F
> if not(@.@.FETCH_STATUS = 0) break
> exec myStoredProc @.F
> end
> close @.C
> deallocate @.C
> --
> How to write it simpler, maybe with implicit cursors?

There are a couple of options, you can use SELECT MIN or SELECT TOP 1.
I would however recommend to stick with the cursors, they are in my
opinion the best way to iterate when you need to iterate. Solutions
with MIN or TOP 1 can have bad performance if there is no good index.

One tip is to make the cursor INSENSITIVE, since keyset-driven cursors
(the default) can sometimes have absymal performance when nailing
down which rows to operate on. Also insensitive saves you from
surprises if you update rows selected by the cursor.

Finally, I never use cursor variables, but always static names.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Do cursors use statistics?

I have the following RPC which is followed by 2 fetches (of 100 rows)
and then a close. Query returns 150 rows.
When I run it through the query analyzer, it runs fast. Each time
through the VB app, the first set of 100 takes 20 seconds with over 10
M reads. The second set takes 7 seconds with 2 M reads.
The only thing I can think of is that it is not using the stats. Any
debugging ideas or advice?
the sgrp_ext is a view that joins a 9M row table to a 170M row table on
a single column
declare @.P1 int
set @.P1=180150009
declare @.P2 int
set @.P2=4
declare @.P3 int
set @.P3=1
declare @.P4 int
set @.P4=-1
exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4Advice: Get rid of the cursor.
"Mark" <mark.kale@.guidant.com> wrote in message
news:1156449002.782650.266430@.74g2000cwt.googlegroups.com...
>I have the following RPC which is followed by 2 fetches (of 100 rows)
> and then a close. Query returns 150 rows.
> When I run it through the query analyzer, it runs fast. Each time
> through the VB app, the first set of 100 takes 20 seconds with over 10
> M reads. The second set takes 7 seconds with 2 M reads.
> The only thing I can think of is that it is not using the stats. Any
> debugging ideas or advice?
> the sgrp_ext is a view that joins a 9M row table to a 170M row table on
> a single column
>
> declare @.P1 int
> set @.P1=180150009
> declare @.P2 int
> set @.P2=4
> declare @.P3 int
> set @.P3=1
> declare @.P4 int
> set @.P4=-1
> exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
> SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
> SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
> SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
> SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
> SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
> (SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
> (PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
> AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
> DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
> SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
> select @.P1, @.P2, @.P3, @.P4
>|||I've never used sp_cursoropen, and can't even find it in my local copy
of BOL. How about using TSQL cursor syntax, or finding a way to
specify "forward_only" or "fast_forward" attributes via the SP?
Default cursor types of dynamic can run pathologically slow, as you
are seeing.
J.
On 24 Aug 2006 12:50:02 -0700, "Mark" <mark.kale@.guidant.com> wrote:
>I have the following RPC which is followed by 2 fetches (of 100 rows)
>and then a close. Query returns 150 rows.
>When I run it through the query analyzer, it runs fast. Each time
>through the VB app, the first set of 100 takes 20 seconds with over 10
>M reads. The second set takes 7 seconds with 2 M reads.
>The only thing I can think of is that it is not using the stats. Any
>debugging ideas or advice?
>the sgrp_ext is a view that joins a 9M row table to a 170M row table on
>a single column
>
>declare @.P1 int
>set @.P1=180150009
>declare @.P2 int
>set @.P2=4
>declare @.P3 int
>set @.P3=1
>declare @.P4 int
>set @.P4=-1
>exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
>SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
>SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
>SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
>SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
>SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
>(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
>(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
>AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
>DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
>SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
>select @.P1, @.P2, @.P3, @.P4|||Yes they will statistics. sp_cursoropen is just what the API
uses to access the data - it's just doing what the driver
tells it to do through the application (via ADO, ODBC,
etc). You would probably want to take a look at the VB end
of things as there are different settings on the application
end that could be affecting this.
-Sue
On 24 Aug 2006 12:50:02 -0700, "Mark"
<mark.kale@.guidant.com> wrote:
>I have the following RPC which is followed by 2 fetches (of 100 rows)
>and then a close. Query returns 150 rows.
>When I run it through the query analyzer, it runs fast. Each time
>through the VB app, the first set of 100 takes 20 seconds with over 10
>M reads. The second set takes 7 seconds with 2 M reads.
>The only thing I can think of is that it is not using the stats. Any
>debugging ideas or advice?
>the sgrp_ext is a view that joins a 9M row table to a 170M row table on
>a single column
>
>declare @.P1 int
>set @.P1=180150009
>declare @.P2 int
>set @.P2=4
>declare @.P3 int
>set @.P3=1
>declare @.P4 int
>set @.P4=-1
>exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
>SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
>SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
>SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
>SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
>SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
>(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
>(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
>AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
>DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
>SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
>select @.P1, @.P2, @.P3, @.P4

Do cursors use statistics?

I have the following RPC which is followed by 2 fetches (of 100 rows)
and then a close. Query returns 150 rows.
When I run it through the query analyzer, it runs fast. Each time
through the VB app, the first set of 100 takes 20 seconds with over 10
M reads. The second set takes 7 seconds with 2 M reads.
The only thing I can think of is that it is not using the stats. Any
debugging ideas or advice?
the sgrp_ext is a view that joins a 9M row table to a 170M row table on
a single column
declare @.P1 int
set @.P1=180150009
declare @.P2 int
set @.P2=4
declare @.P3 int
set @.P3=1
declare @.P4 int
set @.P4=-1
exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
select @.P1, @.P2, @.P3, @.P4Advice: Get rid of the cursor.
"Mark" <mark.kale@.guidant.com> wrote in message
news:1156449002.782650.266430@.74g2000cwt.googlegroups.com...
>I have the following RPC which is followed by 2 fetches (of 100 rows)
> and then a close. Query returns 150 rows.
> When I run it through the query analyzer, it runs fast. Each time
> through the VB app, the first set of 100 takes 20 seconds with over 10
> M reads. The second set takes 7 seconds with 2 M reads.
> The only thing I can think of is that it is not using the stats. Any
> debugging ideas or advice?
> the sgrp_ext is a view that joins a 9M row table to a 170M row table on
> a single column
>
> declare @.P1 int
> set @.P1=180150009
> declare @.P2 int
> set @.P2=4
> declare @.P3 int
> set @.P3=1
> declare @.P4 int
> set @.P4=-1
> exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
> SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
> SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
> SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
> SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
> SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
> (SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
> (PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
> AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
> DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
> SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
> select @.P1, @.P2, @.P3, @.P4
>|||I've never used sp_cursoropen, and can't even find it in my local copy
of BOL. How about using TSQL cursor syntax, or finding a way to
specify "forward_only" or "fast_forward" attributes via the SP?
Default cursor types of dynamic can run pathologically slow, as you
are seeing.
J.
On 24 Aug 2006 12:50:02 -0700, "Mark" <mark.kale@.guidant.com> wrote:

>I have the following RPC which is followed by 2 fetches (of 100 rows)
>and then a close. Query returns 150 rows.
>When I run it through the query analyzer, it runs fast. Each time
>through the VB app, the first set of 100 takes 20 seconds with over 10
>M reads. The second set takes 7 seconds with 2 M reads.
>The only thing I can think of is that it is not using the stats. Any
>debugging ideas or advice?
>the sgrp_ext is a view that joins a 9M row table to a 170M row table on
>a single column
>
>declare @.P1 int
>set @.P1=180150009
>declare @.P2 int
>set @.P2=4
>declare @.P3 int
>set @.P3=1
>declare @.P4 int
>set @.P4=-1
>exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
>SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
>SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
>SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
>SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
>SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
>(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
>(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
>AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
>DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
>SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
>select @.P1, @.P2, @.P3, @.P4|||Yes they will statistics. sp_cursoropen is just what the API
uses to access the data - it's just doing what the driver
tells it to do through the application (via ADO, ODBC,
etc). You would probably want to take a look at the VB end
of things as there are different settings on the application
end that could be affecting this.
-Sue
On 24 Aug 2006 12:50:02 -0700, "Mark"
<mark.kale@.guidant.com> wrote:

>I have the following RPC which is followed by 2 fetches (of 100 rows)
>and then a close. Query returns 150 rows.
>When I run it through the query analyzer, it runs fast. Each time
>through the VB app, the first set of 100 takes 20 seconds with over 10
>M reads. The second set takes 7 seconds with 2 M reads.
>The only thing I can think of is that it is not using the stats. Any
>debugging ideas or advice?
>the sgrp_ext is a view that joins a 9M row table to a 170M row table on
>a single column
>
>declare @.P1 int
>set @.P1=180150009
>declare @.P2 int
>set @.P2=4
>declare @.P3 int
>set @.P3=1
>declare @.P4 int
>set @.P4=-1
>exec sp_cursoropen @.P1 output, N'SELECT SGRP_EXT.F_SGRP,
>SGRP_EXT.F_PART, SGRP_EXT.F_PRCS, SGRP_EXT.F_TEST, SGRP_EXT.F_WKNO,
>SGRP_EXT.F_JOB, SGRP_EXT.F_LOT, SGRP_EXT.F_SPLT, SGRP_EXT.F_EMPL,
>SGRP_EXT.F_SGTM, SGRP_EXT.F_SGSZ, SGRP_EXT.F_FLAG, SGRP_EXT.F_SN,
>SGRP_EXT.F_TSNO, SGRP_EXT.F_SBNO, SGRP_EXT.F_VAL, SGRP_EXT.F_DEF,
>SGRP_EXT.F_GAGE FROM SGRP_EXT, PRCS_DAT WHERE
>(SGRP_EXT.F_PRCS=PRCS_DAT.F_PRCS) AND SGRP_EXT.F_PART=1141815113 AND
>(PRCS_DAT.F_PRGP=1141918205) AND (SGRP_EXT.F_SGTM BETWEEN 1149120000
>AND 1157068799) AND SGRP_EXT.F_TEST=1141918846 ORDER BY SGRP_EXT.F_SGTM
>DESC, SGRP_EXT.F_SGRP DESC, SGRP_EXT.F_TEST DESC, SGRP_EXT.F_TSNO,
>SGRP_EXT.F_SBNO', @.P2 output, @.P3 output, @.P4 output
>select @.P1, @.P2, @.P3, @.P4

DMX Shape query error

Hi I created a DMX query to retrieve predictions based on previous customer purchases and wanted to filter out my input data by only purchases made in the current year. I keep receiving this error:

Code Snippet

===================================

Internal error: An unexpected error occurred (file 'dmxinit.cpp', line 1343, function 'DMXNodeInput::InitFromASTOpenRowset'). (Microsoft SQL Server 2005 Analysis Services)

Program Location:

at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.IExecuteProvider.Execute(ICommandContentProvider contentProvider, AdomdPropertyCollection commandProperties, IDataParameterCollection parameters)
at Microsoft.AnalysisServices.AdomdClient.AdomdCommand.Execute()
at Microsoft.AnalysisServices.Controls.QueryResultGridStorage.ThreadProc()

And, here's my query:

Code Snippet

SELECTFLATTENED

(SELECT *

FROMPredictAssociation([PredictTable],

10,

INCLUDE_NODE_ID,

INCLUDE_STATISTICS

)

WHERE$NODEID <> ''

)

FROM

[Mining Model]

NATURALPREDICTIONJOIN

SHAPE {

OPENQUERY( [datasrc],

'SELECT ''1234'' AS [Customer_D_SID]'

)

} APPEND ({

SHAPE {

OPENQUERY( [datasrc],

'SELECT [Product_D_SID],[Customer_D_SID], [Transaction_Date]

FROM [Base_Sales_F]

WHERE [Customer_D_SID] = ''1234'' '

)

} APPEND ({

OPENQUERY( [datasrc],

'SELECT [Calendar_D_SID],[CALENDAR_YR_NBR]

FROM [dbo].[Calendar_D]

WHERE [CALENDAR_YR_NBR] >= ''2007'' '

)

} RELATE [Calendar_D_SID] TO [Transaction_Date]) AS B

} RELATE B.[Customer_D_SID] TO [Customer_D_SID]) AS [PredictTable]

AS T

I figured the only way to associate the calendar table with the sales table was to use a nested shape statement... is this wrong? Thanks for any help!

The internal error is being raised because your SHAPE statement is generating 2 levels of nesting which doesn't match your model defnition (SQL Server DM only supports single-level nesting i.e. nested tables cannot have table columns).

You need to remove the second nested join and instead, use a view on the transaction table that includes the column (CALENDAR_YR_NBR) you want to filter on.

|||Thank you! Making a view solved my problem.sql

DMX Query, Group by

I'm having some problem with this DMX prediction query. This is the first time I'm trying out the GROUP BY statement in the DMX query and I keep getting "Parse: the statement dialect could not be resolved due to ambiguity." message.

Is Group By supported by the DMX? What am I missing? If not supported, could I insert the result into a temporary table using SELECT ... INTO.. FROM and run a group by on a temporary table?

This is what the DMX query looks like...

SELECT
t.[AgeGroupName],
t.[ChildrenStatusName],
t.[EducationName],
Sum(t.[Profit]) as Profit
From
[Revenue Estimate DT]
PREDICTION JOIN
OPENQUERY([DM Reports DM],
'SELECT
[AgeGroupName],
[ChildrenStatusName],
[EducationName],
[Profit],
[IncomeName],
[HomeOwnerName],
[SexName],
[Country],
[ProductTypeCode],
[ProductName],
[MailCount],
[OrderAmount],
[SalesAmount],
[MailCost]
FROM
(SELECT AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode,
CompanyTypeCode, Country, ProductTypeCode, ProductName, SUM(MailCount) AS MailCount, SUM(OrderAmount) AS OrderAmount, SUM(SalesAmount)
AS SalesAmount, SUM(MailCost) AS MailCost, SUM(Profit) AS Profit, MIN(RevenueEstimateID) AS ReKey
FROM [DataMining.RevenueEstimate.Predict]
GROUP BY AgeGroupName, ChildrenStatusName, EducationName, IncomeName, HomeOwnerName, MaritalStatusName, SexName, JobName, JobTypeCode,
CompanyTypeCode, Country, ProductTypeCode, ProductName, ClientID
HAVING (ClientID = 1)) as [Prediction]
') AS t
ON
[Revenue Estimate DT].[Age Group Name] = t.[AgeGroupName] AND
[Revenue Estimate DT].[Education Name] = t.[EducationName] AND
[Revenue Estimate DT].[Income Name] = t.[IncomeName] AND
[Revenue Estimate DT].[Home Owner Name] = t.[HomeOwnerName] AND
[Revenue Estimate DT].[Sex Name] = t.[SexName] AND
[Revenue Estimate DT].[Country] = t.[Country] AND
[Revenue Estimate DT].[Product Type Code] = t.[ProductTypeCode] AND
[Revenue Estimate DT].[Product Name] = t.[ProductName] AND
[Revenue Estimate DT].[Mail Count] = t.[MailCount] AND
[Revenue Estimate DT].[Order Amount] = t.[OrderAmount] AND
[Revenue Estimate DT].[Sales Amount] = t.[SalesAmount] AND
[Revenue Estimate DT].[Mail Cost] = t.[MailCost] AND
[Revenue Estimate DT].[Profit] = t.[Profit] AND
[Revenue Estimate DT].[Children Status Name] = t.[ChildrenStatusName]
GROUP BY t.[AgeGroupName],
t.[ChildrenStatusName],
t.[EducationName]

Hello

GROUP BY is not supported in DMX, and neither are temporary tables. A solution would be to execute the query, store the results inside SQL Server, then execute the group by inside the relational engine.

You can find some details on executing predictions from the relational engine in this article: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx

|||Or you could do as Bogdan suggests without storing the results and performing SQL operations on an OPENROWSET DMX query result

dmx query probablity ?

CREATE MINING MODEL mortgage
(
[id] long key,
Edu_Status long DISCRETE,
Work_Status long DISCRETE,
age long CONTINUOUS,
asset_value long CONTINUOUS,
Net_income long CONTINUOUS,
paid_Status long DISCRETE PREDICT
)USING MICROSOFT_DECISION_TREES

i have a mining model above and i am designing a web cross ablication to see probablity of the a costumers' paid status.can you write for me dmx command for selecting paid status that have same criteria(ex;age=30 net income=2000,Edu_status=college) and when i write dxm window(select * from [paid_Status] ) it only shows "4" one row one column why ? as you see i dont know

dmx and datamining well..:)

string dxmcommand = "";

private AdomdCommand ascommand = new AdomdCommand();

private AdomdConnection asconnection=new AdomdConnection();

private AdomdDataReader asreader=new AdomdDataReader();

ascommand.CommandText = dmxcommand;

if I understand your question correctly, then the query would be something like:

SELECT Paid_Status, PredictProbability(Paid_Status)

FROM mortgage NATURAL PREDICTION JOIN

(SELECT 30 AS Age, 2000 as Net_Income ) AS T

This query will return the predicted value for Paid_Status as well as the proability for that prediction. Note that I did not include "College" as Edu_status. The reason is that the Edu_status column of the mining model is defined as "LONG DISCRETE" and "College" is a string, so it cannot be mapped to a long. You will have to convert "College" to the numeric code which I suppose it is used in describing this string, then add "<Numeric_Code_For_College> AS Edu_Status" to the query

Also, in C#, the sequence of operations should be along this line:

AdomdConnection cn = new AdomdConnection();

cn.ConnectionString = "Data Source=localhost; Initial Catalog=<Your Database>";

cn.Open(); // open the connection

AdomdCommand cmd = new AdomdCommand();

cmd.Connection = cn; // associate the command with the current connection

cmd.CommandText = dmxcommand;

AdomdDataReader rdr = cmd.ExecuteReader(); // obtain the reader from the command execution instead of creating it with new

Hope this helps

DMX Query for regression coefficients

How do I write a DMX query to return the coefficients of the independent variables in my regression equation?

Thanks,

Carrie

All algorithm content is in the content schema rowset available through

SELECT * FROM <model name>.CONTENT

Although the schema is the same for all algorithms, each uses the schema slightly differently. The schema itself is difficult to decode, but you can download a plug-in viewer from http://www.sqlserverdatamining.com/dmcommunity/_downloads/1348.aspx that decodes all the types/etc into their parts. Once you do this, you will see what columns/etc you need from the content.

|||

We do not have the sgKey.snk file. Can we generate one? If so how?

Cryptographic failure while signing assembly 'C:\Documents and Settings\dtm\My Documents\dot net examples\Generic Content Viewer\GenericContentTreeViewerSetup\obj\Debug\GenericContentTreeView.dll' -- 'Error reading key file 'c:\Documents and Settings\dtm\My Documents\dot net examples\Generic Content Viewer\GenericContentTreeViewerSetup\sgKey.snk' -- The system cannot find the file specified. '

Thanks

|||Something happened to the download - the snk file isn't the only one missing - we're looking into it. However, the setup should have installed the viewer anyway, so you should see it in BI Dev Studio, did it not?|||

I understand the content viewer now. I thought maybe I was looking for something different but now I see.

Three more questions:

1. My regressor variable has three values associated with it. I understand the first (the coefficient in the regression equation) and the third, used to calculate the constant - but what is the second value? A screenshot would probably be more helpful.

2. How do I set an input variable to regressor in the mining wizard. It is not listed as a modeling flag option. If I have more than one regressor - will both regressors be included as part of the regression equation?

3. In my regression trees, if a node does not have a regression equation associated with it, is the model overtrained? How do I interpret these results?

Thanks so much,

Carrie

|||

1: I don't have it in front of me right now, so a screenshot would help :)

2: When you create a decision tree with continuous inputs and outputs, I believe the wizard automatically marks all continuous values as REGRESSOR. You can verify this by going to the Mining Models pane in the Data Mining designer. Click on a column name under the mining model (not the mining structure) and look at its properties in the property panel. This is where you can set algorithm-specific modeling flags, and where you would set or clear the REGRESSOR flag.

3. I wouldn't say it was necessarily overtrained, just that there were no significant regressors for that node. For example, assume I had a bunch of demographic data including Age and IQ as my only continuous values and I tried to predict either one. Statistically speaking, they should be independent and there shouldn't be any regressions - just constants. That being said, and since it may not be the case for your model, there are a couple of options open to you. If you think the model may be overfitting, you can increase the MINIMUM_SUPPORT parameter, or the COMPLEXITY_PENALTY parameter. Both of these have the impact of reducing the size of your tree. Additionally, the decision tree algorithm has a FORCE_REGRESSOR parameter allowing you to specify a regressor that will be included in any regression, regardless of how minimal its contribution

|||Is there a dmx query that will return the actual numeric value of the diamond (residual) in each node of the regression tree?|||I am just making sure my question is still in the cue....thanks|||

Assuming your model name was "cp" and your attribute name was "IQ", I think this is the query you want

select FLATTENED NODE_CAPTION,
NODE_NAME,
(select ATTRIBUTE_VALUE AS mean,
[VARIANCE] as [variance]
from NODE_DISTRIBUTION WHERE VALUETYPE=3)
as stats
from cp.content WHERE ATTRIBUTE_NAME='IQ'

DMX Query for regression coefficients

How do I write a DMX query to return the coefficients of the independent variables in my regression equation?

Thanks,

Carrie

All algorithm content is in the content schema rowset available through

SELECT * FROM <model name>.CONTENT

Although the schema is the same for all algorithms, each uses the schema slightly differently. The schema itself is difficult to decode, but you can download a plug-in viewer from http://www.sqlserverdatamining.com/dmcommunity/_downloads/1348.aspx that decodes all the types/etc into their parts. Once you do this, you will see what columns/etc you need from the content.

|||

We do not have the sgKey.snk file. Can we generate one? If so how?

Cryptographic failure while signing assembly 'C:\Documents and Settings\dtm\My Documents\dot net examples\Generic Content Viewer\GenericContentTreeViewerSetup\obj\Debug\GenericContentTreeView.dll' -- 'Error reading key file 'c:\Documents and Settings\dtm\My Documents\dot net examples\Generic Content Viewer\GenericContentTreeViewerSetup\sgKey.snk' -- The system cannot find the file specified. '

Thanks

|||Something happened to the download - the snk file isn't the only one missing - we're looking into it. However, the setup should have installed the viewer anyway, so you should see it in BI Dev Studio, did it not?|||

I understand the content viewer now. I thought maybe I was looking for something different but now I see.

Three more questions:

1. My regressor variable has three values associated with it. I understand the first (the coefficient in the regression equation) and the third, used to calculate the constant - but what is the second value? A screenshot would probably be more helpful.

2. How do I set an input variable to regressor in the mining wizard. It is not listed as a modeling flag option. If I have more than one regressor - will both regressors be included as part of the regression equation?

3. In my regression trees, if a node does not have a regression equation associated with it, is the model overtrained? How do I interpret these results?

Thanks so much,

Carrie

|||

1: I don't have it in front of me right now, so a screenshot would help :)

2: When you create a decision tree with continuous inputs and outputs, I believe the wizard automatically marks all continuous values as REGRESSOR. You can verify this by going to the Mining Models pane in the Data Mining designer. Click on a column name under the mining model (not the mining structure) and look at its properties in the property panel. This is where you can set algorithm-specific modeling flags, and where you would set or clear the REGRESSOR flag.

3. I wouldn't say it was necessarily overtrained, just that there were no significant regressors for that node. For example, assume I had a bunch of demographic data including Age and IQ as my only continuous values and I tried to predict either one. Statistically speaking, they should be independent and there shouldn't be any regressions - just constants. That being said, and since it may not be the case for your model, there are a couple of options open to you. If you think the model may be overfitting, you can increase the MINIMUM_SUPPORT parameter, or the COMPLEXITY_PENALTY parameter. Both of these have the impact of reducing the size of your tree. Additionally, the decision tree algorithm has a FORCE_REGRESSOR parameter allowing you to specify a regressor that will be included in any regression, regardless of how minimal its contribution

|||Is there a dmx query that will return the actual numeric value of the diamond (residual) in each node of the regression tree?|||I am just making sure my question is still in the cue....thanks|||

Assuming your model name was "cp" and your attribute name was "IQ", I think this is the query you want

select FLATTENED NODE_CAPTION,
NODE_NAME,
(select ATTRIBUTE_VALUE AS mean,
[VARIANCE] as [variance]
from NODE_DISTRIBUTION WHERE VALUETYPE=3)
as stats
from cp.content WHERE ATTRIBUTE_NAME='IQ'

DMX Query examples

Can you give an example on how exactly to write each of the following? (I am clearly not a programmer :))

TopCount ( <table expr>,<rank expr>,<n-items>) =

TopPercent ( <table expr>,<rank expr>,<percent>) =

PredictTimeSeries ( <table expr>,<n1>,<n2>) =

PredictAssociation ( <table expr>,<n>) =

Thanks,

Carrie

SELECT TopCount(PredictHistogram(MyAttribute),$Probability,3), // returns top 3 values by probability
TopPercent(PredictHistogram(MyAttribute),$Support,0.20) // returns top values that contain at least 20% of the total support
FROM MyModel
PREDICTION JOIN
...

SELECT PredictTimeSeries(MyNestedTableTimeSeriesColumn, 2, 5) // predicts steps 2-5 in the series
FROM MyTimeSeriesModel

SELECT PredictAssociation(MyNestedTable, 5) // returns top 5 associated items
FROM My Model
PREDICTION JOIN
...

|||

So I did.....

select TopCount(PredictHistogram(Returnwithplay),$Support,.20)

From [REC FT All Cube DT]

PREDICTION JOIN

And received the following ( I think the query completed with errors):

Executing the query ...

Parser: The end of the input was reached.

Execution complete

|||And I tried

select PredictTimeSeries([Slot Theo Win],2,5) FROM [Player Market]

and received the following error:

Executing the query ...

Error (Data mining): The specified DMX column was not found in the context at line 1, column 26.

Execution complete

My time series is set up as the following.......

Player Market:

Date Dim Predict

Day Key

Rated Slot Theo Predict Only

Rated Table Theo Predict Only

Player Market Dim Key

|||

PredictTimeSeries only works on models using the Microsoft_Time_Series algorithm. Also, it doesn't seem that you have a column called "Slot Theo Win" in your data set.

It seems you would need a model somewhat like

CREATE MINING MODEL [Player Market]
(
DateDim DATE KEY TIME,
PlayerMarketDim TEXT KEY,
RatedSlotTheo DOUBLE CONTINUOUS PREDICT_ONLY,
RatedSlotTheo DOUBLE CONTINUOUS PREDICT_ONLY
) USING Microsoft_Time_Series

sql

DMX query and ASP.Net

I am trying to get along with SQL server 2005, made the mining model and i use this DMX query to get the time series prediction. Now this is working and i get results in SQL server management studio. I cant get predection result in to aspx. I found this article but still...nothing

http://www.aspnetpro.com/newsletterarticle/2004/10/asp200410ri_l/asp200410ri_l.asp

After spending some weeks of testing and reading I came up with this:

Itsa part of the code of cross web application, I managed to make a modelof timeseries in SQL server and I have hopefully (since I dont get anyerrors) run my dmx query through aspx. BUT I can get the result todisplay in aspx. My guess is because the original code was made forstring variables and I am tring to get numeric variables in it. Isuppose that all I need to do is to get the result from the DMX queryin the array

'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If


'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then

If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End If
End While

And here is my DMX query

Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)

'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5)" + _
"FROM [Sales]"

********************************

I would appriciate any answers since this project is for my diploma and I really cant seem to get through

Thanks

Try changing

"SELECT PredictTimeSeries([Apot Sales],5)" + _
"FROM [Sales]

to

"SELECT PredictTimeSeries([Apot Sales],5)AS 'APOT' FROM Sales"

Giving the column a name should allow it to be displayed.

DMX query and ASP

I am trying to get along with SQL server 2005, just made the mining model and i use this DMX query to get the time series prediction. Now this is working and i get results in SQL server management studio.

SELECT PredictTimeSeries([Apot Sales],5)

FROM [Sales Bycom]

I tried running this as a query in ASP but obviously this can't be done

Here is my question. I have made an SQL server connection in ASP. But how can I get the prediction results displayed in to ASP?

Thanx

You will need to make an Analysis Server connection to execute the DMX query from ASP. Take a look at the code download at the bottom of this article for an example of how to use DMX queries from ASP: http://www.aspnetpro.com/newsletterarticle/2004/10/asp200410ri_l/asp200410ri_l.asp.|||Ok thanx I will check it out|||After spending some weeks of testing and reading I came up with this:

Its a part of the code of cross web application, I managed to make a model of timeseries in SQL server and I have hopefully (since I dont get any errors) run my dmx query through aspx. BUT I can get the result to display in aspx. My guess is because the original code was made for string variables and I am tring to get numeric variables in it. I suppose that all I need to do is to get the result from the DMX query in the array

'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then

If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End If
End While

And here is my DMX query

Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)

'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5)" + _
"FROM [Sales]"

********************************

I would appriciate any answers since this project is for my diploma and I really cant seem to get through

Thanks

|||What error(s) are you seeing? Keep in mind that PredictTimeSeries(<col>, N) returns a table with two columns - the first column is a time index ($TIME) and the second column contains the predicted values for the column you're predicting.|||Thanks Raman Iyer , The weird thing is that I dont get any errors neither any results

in the place where the results appear I get "Microsoft.AnalysisServices.AdomdClient.AdomdDataReader"

here is the whole code from predict.vb.asp

Imports System
Imports System.Collections
Imports System.ComponentModel
Imports System.Drawing
Imports System.Web
Imports System.Web.SessionState
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports Microsoft.AnalysisServices.AdomdClient

Namespace MovieCrossSellApplication

Partial Public Class ShoppingBasket_Recommendations
Inherits System.Web.UI.Page

Protected Overrides Sub OnInit(ByVal e As EventArgs)
'
' CODEGEN: This call is required by the ASP.NET Web Form Designer.
'
InitializeComponent()
MyBase.OnInit(e)
End Sub 'OnInit

'/ <summary>
'/ Required method for Designer support - do not modify
'/ the contents of this method with the code editor.
'/ </summary>
Private Sub InitializeComponent()
End Sub 'InitializeComponent

Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)

'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5) " + _
"FROM [Sales] "

' "SELECT FLATTENED TopCount(" + _
' "Predict([Customer Movies], INCLUDE_STATISTICS)," + _
' "$AdjustedProbability, 5) From [Movie Recommendations] " + _
' "NATURAL PREDICTION JOIN (SELECT ("
'Dim strDMX2 As String = ") AS [Customer Movies]) AS t"

'Iterate shopping basket and produce input case
'Dim cItems As Integer = vInputItems.Count
' Dim strDMX As String = ""
' Dim i As Integer
'For i = 0 To cItems - 1
' Dim item As String = vInputItems(i).ToString()
' item = item.Replace("’", "’’")
'strDMX += "SELECT " + "'" + item + "' AS " + "[Movie]"
' If i < cItems - 1 Then
' strDMX += " UNION "
'End If
'Next i

'Put together DMX prediction query to get 5 recommendations
'strDMX = strDMX1 + strDMX '+ strDMX2

'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then

'If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
'End If
End While

'Disconnect from Analysis Server
asSession.DisConnect()

End Sub 'GetRecommendations

Public Sub Button1_Click( _
ByVal sender As Object, _
ByVal e As System.EventArgs) 'Handles Me.Button1.Click

' Parse the input into an ArrayList of strings.
Dim alInputItems As New ArrayList()
Dim splitchar As Char() = {";"c}
Dim szInputItems As String() = Me.TextBox1.Text.Split(splitchar, 20)
Dim i As Integer
For i = 0 To szInputItems.Length - 1
alInputItems.Add(szInputItems(i).Trim())
Next i

' Add items to the shopping basket
dgShoppingBasket.DataSource = alInputItems
dgShoppingBasket.DataBind()

' Get top 5 recommendations
Dim alRecommendedItems As New ArrayList(5)
GetRecommendations(alInputItems, alRecommendedItems)

' Display recommendations
dgRecommendations.DataSource = alRecommendedItems
dgRecommendations.DataBind()
End Sub 'Button1_Click

End Class 'ShoppingBasket_Recommendations

'
' AnalysisServerSession manages
' - connecting to Analysis Server using ADOMD.NET,
' - executing commands and
' - fetching results
'
' Need to add reference to Microsoft.AnalysisServices.AdomdClient.dll
' (located under Program Files\Microsoft.NET\ADOMD.NET\90).
' You may also change this class to use ADO.NET (System.Data.Oledb)
' instead if neccessary, by replacing the AdomdConnection, AdomdCommand
' and AdomdDataReader with OledbConnection, OledbCommand and
' OledbDataReader. The rest of the code should stay the same.
'
Public Class AnalysisServerSession
Protected asCommand As Microsoft.AnalysisServices.AdomdClient.AdomdCommand
Protected asConnection As Microsoft.AnalysisServices.AdomdClient.AdomdConnection
Public asDataReader As Microsoft.AnalysisServices.AdomdClient.AdomdDataReader
Public szServer As String = "localhost"
Public szCatalog As String = "myDSS"

Public Sub New()
asCommand = Nothing
asConnection = Nothing
asDataReader = Nothing
End Sub 'New

Public Function Connect() As Boolean
Dim asConnectionString As String = _
"Provider=MSOLAP.3;Data Source=" + _
szServer + ";Initial Catalog=" + szCatalog

asConnection = New AdomdConnection(asConnectionString)
asConnection.Open()

Return True
End Function 'Connect

Public Function ExecuteAndFetchResult(ByVal strCommand As string) As Boolean
If asConnection Is Nothing Then
Return False
End If
If asCommand Is Nothing Then
asCommand = New AdomdCommand()
End If
strCommand = strCommand.Replace("NaN", "null")
strCommand = strCommand.Replace("Infinity", "null")

Try
If Not (asDataReader Is Nothing) Then
If Not asDataReader.IsClosed Then
asDataReader.Close()
End If
End If
asCommand.Connection = asConnection
asCommand.CommandText = strCommand
asDataReader = asCommand.ExecuteReader()

Catch e As Exception

Log(e.Message)
Return False
End Try
Return True
End Function 'ExecuteAndFetchResult

Public Function DisConnect() As Boolean
Try
If Not (asConnection Is Nothing) Then
asConnection.Close()
End If
If Not (asCommand Is Nothing) Then
asCommand.Connection = Nothing
End If
If Not (asDataReader Is Nothing) Then
asDataReader.Close()
End If
Catch e As Exception
Console.WriteLine(e.Message)
End Try
Return True
End Function 'DisConnect

Private Sub Log(ByVal message As String)
'Log the message to some place
System.Diagnostics.Debug.Assert(False, message)
Return
End Sub 'Log

End Class 'AnalysisServerSession

End Namespace 'MovieCrossSellApplication

Any help appreciated
|||OK I think I am getting somewhere......

Raman Iyer you should be right I must have 2 collums in order to save the results in an array,
can't figure out how I can do that checked some tutorials but came up with nothing.....

If anyone could help or suggest a web site?

Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Dim vDSSItems(10) as string
While asSession.asDataReader.Read()

Dim val As string = asSession.asDataReader.GetString(0)
vDSSItems.add(val)
end while

DMX query and ASP

I am trying to get along with SQL server 2005, just made the mining model and i use this DMX query to get the time series prediction. Now this is working and i get results in SQL server management studio.

SELECT PredictTimeSeries([Apot Sales],5)

FROM [Sales Bycom]

I tried running this as a query in ASP but obviously this can't be done

Here is my question. I have made an SQL server connection in ASP. But how can I get the prediction results displayed in to ASP?

Thanx

You will need to make an Analysis Server connection to execute the DMX query from ASP. Take a look at the code download at the bottom of this article for an example of how to use DMX queries from ASP: http://www.aspnetpro.com/newsletterarticle/2004/10/asp200410ri_l/asp200410ri_l.asp.|||Ok thanx I will check it out|||After spending some weeks of testing and reading I came up with this:

Its a part of the code of cross web application, I managed to make a model of timeseries in SQL server and I have hopefully (since I dont get any errors) run my dmx query through aspx. BUT I can get the result to display in aspx. My guess is because the original code was made for string variables and I am tring to get numeric variables in it. I suppose that all I need to do is to get the result from the DMX query in the array

'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then

If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
End If
End While

And here is my DMX query

Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)

'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5)" + _
"FROM [Sales]"

********************************

I would appriciate any answers since this project is for my diploma and I really cant seem to get through

Thanks|||What error(s) are you seeing? Keep in mind that PredictTimeSeries(<col>, N) returns a table with two columns - the first column is a time index ($TIME) and the second column contains the predicted values for the column you're predicting.|||Thanks Raman Iyer , The weird thing is that I dont get any errors neither any results

in the place where the results appear I get "Microsoft.AnalysisServices.AdomdClient.AdomdDataReader"

here is the whole code from predict.vb.asp

Imports System
Imports System.Collections
Imports System.ComponentModel
Imports System.Drawing
Imports System.Web
Imports System.Web.SessionState
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports Microsoft.AnalysisServices.AdomdClient

Namespace MovieCrossSellApplication

Partial Public Class ShoppingBasket_Recommendations
Inherits System.Web.UI.Page

Protected Overrides Sub OnInit(ByVal e As EventArgs)
'
' CODEGEN: This call is required by the ASP.NET Web Form Designer.
'
InitializeComponent()
MyBase.OnInit(e)
End Sub 'OnInit

'/ <summary>
'/ Required method for Designer support - do not modify
'/ the contents of this method with the code editor.
'/ </summary>
Private Sub InitializeComponent()
End Sub 'InitializeComponent

Private Shared Sub GetRecommendations( _
ByVal vInputItems As ArrayList, _
ByRef vRecommendedItems As ArrayList)

'Templates for generating DMX prediction join statement
Dim strDMX As String = _
"SELECT PredictTimeSeries([Apot Sales],5) " + _
"FROM [Sales] "

' "SELECT FLATTENED TopCount(" + _
' "Predict([Customer Movies], INCLUDE_STATISTICS)," + _
' "$AdjustedProbability, 5) From [Movie Recommendations] " + _
' "NATURAL PREDICTION JOIN (SELECT ("
'Dim strDMX2 As String = ") AS [Customer Movies]) AS t"

'Iterate shopping basket and produce input case
'Dim cItems As Integer = vInputItems.Count
' Dim strDMX As String = ""
' Dim i As Integer
'For i = 0 To cItems - 1
' Dim item As String = vInputItems(i).ToString()
' item = item.Replace("’", "’’")
'strDMX += "SELECT " + "'" + item + "' AS " + "[Movie]"
' If i < cItems - 1 Then
' strDMX += " UNION "
'End If
'Next i

'Put together DMX prediction query to get 5 recommendations
'strDMX = strDMX1 + strDMX '+ strDMX2

'Connect to Analysis Server and execute query
Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Read prediction results and build list of recommendations
vRecommendedItems.Clear()
While asSession.asDataReader.Read()
Dim type As String = asSession.asDataReader.GetDataTypeName(0)
' If type = "DBTYPE_WVARCHAR" Or type = "String" Then

'If type = "String" Then
Try
Dim val As string = asSession.asDataReader.GetString(0)
vRecommendedItems.Add(val)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
'End If
End While

'Disconnect from Analysis Server
asSession.DisConnect()

End Sub 'GetRecommendations

Public Sub Button1_Click( _
ByVal sender As Object, _
ByVal e As System.EventArgs) 'Handles Me.Button1.Click

' Parse the input into an ArrayList of strings.
Dim alInputItems As New ArrayList()
Dim splitchar As Char() = {";"c}
Dim szInputItems As String() = Me.TextBox1.Text.Split(splitchar, 20)
Dim i As Integer
For i = 0 To szInputItems.Length - 1
alInputItems.Add(szInputItems(i).Trim())
Next i

' Add items to the shopping basket
dgShoppingBasket.DataSource = alInputItems
dgShoppingBasket.DataBind()

' Get top 5 recommendations
Dim alRecommendedItems As New ArrayList(5)
GetRecommendations(alInputItems, alRecommendedItems)

' Display recommendations
dgRecommendations.DataSource = alRecommendedItems
dgRecommendations.DataBind()
End Sub 'Button1_Click

End Class 'ShoppingBasket_Recommendations

'
' AnalysisServerSession manages
' - connecting to Analysis Server using ADOMD.NET,
' - executing commands and
' - fetching results
'
' Need to add reference to Microsoft.AnalysisServices.AdomdClient.dll
' (located under Program Files\Microsoft.NET\ADOMD.NET\90).
' You may also change this class to use ADO.NET (System.Data.Oledb)
' instead if neccessary, by replacing the AdomdConnection, AdomdCommand
' and AdomdDataReader with OledbConnection, OledbCommand and
' OledbDataReader. The rest of the code should stay the same.
'
Public Class AnalysisServerSession
Protected asCommand As Microsoft.AnalysisServices.AdomdClient.AdomdCommand
Protected asConnection As Microsoft.AnalysisServices.AdomdClient.AdomdConnection
Public asDataReader As Microsoft.AnalysisServices.AdomdClient.AdomdDataReader
Public szServer As String = "localhost"
Public szCatalog As String = "myDSS"

Public Sub New()
asCommand = Nothing
asConnection = Nothing
asDataReader = Nothing
End Sub 'New

Public Function Connect() As Boolean
Dim asConnectionString As String = _
"Provider=MSOLAP.3;Data Source=" + _
szServer + ";Initial Catalog=" + szCatalog

asConnection = New AdomdConnection(asConnectionString)
asConnection.Open()

Return True
End Function 'Connect

Public Function ExecuteAndFetchResult(ByVal strCommand As string) As Boolean
If asConnection Is Nothing Then
Return False
End If
If asCommand Is Nothing Then
asCommand = New AdomdCommand()
End If
strCommand = strCommand.Replace("NaN", "null")
strCommand = strCommand.Replace("Infinity", "null")

Try
If Not (asDataReader Is Nothing) Then
If Not asDataReader.IsClosed Then
asDataReader.Close()
End If
End If
asCommand.Connection = asConnection
asCommand.CommandText = strCommand
asDataReader = asCommand.ExecuteReader()

Catch e As Exception

Log(e.Message)
Return False
End Try
Return True
End Function 'ExecuteAndFetchResult

Public Function DisConnect() As Boolean
Try
If Not (asConnection Is Nothing) Then
asConnection.Close()
End If
If Not (asCommand Is Nothing) Then
asCommand.Connection = Nothing
End If
If Not (asDataReader Is Nothing) Then
asDataReader.Close()
End If
Catch e As Exception
Console.WriteLine(e.Message)
End Try
Return True
End Function 'DisConnect

Private Sub Log(ByVal message As String)
'Log the message to some place
System.Diagnostics.Debug.Assert(False, message)
Return
End Sub 'Log

End Class 'AnalysisServerSession

End Namespace 'MovieCrossSellApplication

Any help appreciated|||OK I think I am getting somewhere......

Raman Iyer you should be right I must have 2 collums in order to save the results in an array,
can't figure out how I can do that checked some tutorials but came up with nothing.....

If anyone could help or suggest a web site?

Dim asSession As New AnalysisServerSession
asSession.Connect()
If False = asSession.ExecuteAndFetchResult(strDMX) Then
Return
End If

'Dim vDSSItems(10) as string
While asSession.asDataReader.Read()

Dim val As string = asSession.asDataReader.GetString(0)
vDSSItems.add(val)
end while