Is it possible to put several queries into one sp

I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.

After that query could be a good time to count total of all calculated sub sums.

Something like this.

previous query

END

go
SELECT SUM(SubTotal)
FROM dbo.t_Shoppings

I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)

Is this possible or do I have make and call another stored procedure.

I can send my sp if someone wants.

No, this is for sure possible. Try taking the "END" between your first and second query and put it after your second query. Let me know what errors you are getting when you try to execute it if that doesn't work

|||

you can just put queries on eby one withiout any separator , just start every select statement in new line. Do not use GO it unless you run your query in Management studio environment (Go is not SQL comamnd)

|||

Thank you both for your advises. I'll test the sp later today. And I have to check how to return parameters and so on first.

I'll mark your answers as answers then too.

Regards

Leif

|||

Hi

I tried today my sp and got it working. Server management studio didn't show output parameter right but the query itself seems to work. I'll test it with code later.

Thanks for help.

Here is my stored procedure now. I didn't translate it, so variables may look strange. Kokonaissumma means total value btw.

create PROCEDURE [dbo].[kori2]
(
@.Tuotekoodi varchar(20),
@.kokonaissumma money output
)
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_osto WHERE Tuotekoodi=@.Tuotekoodi)
BEGIN
INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)
SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv
FROM dbo.t_Tuote
WHERE Tuotekoodi= @.Tuotekoodi
END
ELSE
BEGIN
UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
END

return (SELECT count(*) FROM dbo.t_osto)

(select @.kokonaissumma=sum(Yhteensa)FROM dbo.t_osto)

Is it Possible to Join 2 Queries Using MDX?

Hi,

First, my knowledge of MDX is very limited :o

I am wondering if it is possible to return the result of 2 different queries as one. Similarly, like using UNION in SQL. I looked into the MDX UNION but it works on sets.

Basically, I have 2 queries; one with territory sales and one with region sales. I would like to return the results of all the territories followed by the sales of the region in one go.

Thanks in advance.HUH?

Want to post some sample code?

Read the sticky at the top of the board|||Hi Brett,

I am working with Reporting Services 2005 and Cubes. I create my dataset by picking my measures, dimensions and default parameters in design mode. I face the scenario now where I have two datasets but that I would like to combine them into one so that I can just output them as a list.

I can go into MDX mode and see the MDX query generated by RS, but it is very messy and complex, and I don't think it is worth posting.

So back to the problem, I have two MDX queries (whatever they are), that return the same columns but different rows. What I am hoping for is that there is a way to combine the MDX queries into one big query. Just like in SQL where you can use UNION to join the results of two SELECTS.|||I really need to play with Reporting Services an Analysis Services...

I suspect I do all that stuff manually in the first place|||you might be able to use UNION in sql if you use calls to OPENROWSET to fetch the rows from AS. basically union the two OPENROWSET results together.

Like this:

select * from OPENROWSET('MSOLAP', 'connstr', 'query1') union
select * from OPENROWSET('MSOLAP', 'connstr', 'query2')|||Hi jezemine,

That's big hack ;)

The thing is that, due to security reasons, OPENROWSET is blocked on our servers.

Still, thanks for your input.

Is Excel ASOLEDB9 taking advantage of cube partitioning?

Hi,

I wonder if Excel ASOLEDB9 is benefiting from cube partitioning?
Some queries are very slow and the Excel generated code look not that good

I tried to pick some queries from SQL Server Profiler and run them in an mdx query window and I get syntax errors.

This leaves me perplex since I have the feeling that people try endless queries through their Excel pivot cube, then, after a while they cancel the Excel query because it takes forever, then the server remain stuck on a high level of CPU usage.

Is it because the syntax error or is it because they just ask for too much data?
Is canceling an Excel pivot data refresh enough to stop the server's query processing?

Yesterday night, it was so bad (100% CPU) that I had to restart the server.

Thanks,

Philippe

Cube partitioning is server-side, so all clients should benefit from it.

When you cancel a query in Excel, you dont cancel it server-side. Check out the following thread for more info on this.

|||Guys,
This is going to be a big problem.
Queries cancelled by the user keep running on the server.

This kills the server and there is no way that someone would spend time trying to manually trace these runaway queries and manually cancel them on the server.

It is also a big issue to have to restart the server everyday just because of these runaway queries.

I would like to see a fix for it in SP2 with a high Priority rating.

This is a server killer.

Probably the biggest bug ever in SSAS2005.

Philippe|||

If anything, this is probably a Excel bug.

If you are really struggeling with this you could try to write some custom code that identifies long running queries (look at the activityviewer sample application). Then you could cancel these queries with a xmla cancel command. Finally schedule your code to run every ten minutes or so with SQL Server Agent.