Can I group stored procedures some way

Hi folks!
I'm working on a report that requires about 20 SPs to retrieve data.
My code would be better if I could somehow create some object
(package?) that would have all my 20 SPs in it.
This way if I have 100 reports it will be easy to manage the SPs in the
database.
I thought I read several years ago there was a way to do this. Is there
still a way?
Thanks in advance.Bob wrote:
> Hi folks!
> I'm working on a report that requires about 20 SPs to retrieve data.
> My code would be better if I could somehow create some object
> (package?) that would have all my 20 SPs in it.
> This way if I have 100 reports it will be easy to manage the SPs in
> the database.
> I thought I read several years ago there was a way to do this. Is
> there still a way?
> Thanks in advance.
I'm about you're actually trying to do here. Are you talking
about packaging your procedures in order to create them on another
database? Can you give an example of what you need?
David Gugick
Imceda Software
www.imceda.com|||"Bob" <Go1369@.Yahoo.Com> wrote in message
news:1109359684.432319.268490@.l41g2000cwc.googlegroups.com...
> Hi folks!
> I'm working on a report that requires about 20 SPs to retrieve data.
> My code would be better if I could somehow create some object
> (package?) that would have all my 20 SPs in it.
> This way if I have 100 reports it will be easy to manage the SPs in the
> database.
> I thought I read several years ago there was a way to do this. Is there
> still a way?
>
Are you refering to the stored procedure number?
CREATE PROC [ EDURE ] procedure_name [ ; number ]
. . .
;number
Is an optional integer used to group procedures of the same name so they can
be dropped together with a single DROP PROCEDURE statement. For example, the
procedures used with an application called orders may be named orderproc;1,
orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the
entire group. If the name contains delimited identifiers, the number should
not be included as part of the identifier; use the appropriate delimiter
around procedure_name only.
This can be used to group procedures, but it's an old and rarely used
feature, and you run the risk of confusing people. I would probably just
use a common name prefix to sort and identify the related procedures.
David

Can i group records in an ador.recordset?

Hi,

I have the need to group records in an ador.recordset.

Is this possible and in that case how?

When I hear recordset, I have to assume that you are referring to classic ADO rather than ADO.Net. So I think it best to take this question up in the SQL Server Data Access forum. Moving the thread there.

- Danny

Can I GROUP BY aggregate Function (Like SUM)

Hello,
I column that calculated at run time in insert , can i gruop by this column,the new one that not exist yetyes, with a derived table, but why would you want to?

please show an example, using sample data to illustrate|||this is my case:

i have a table with a column datetime (I use to record time of calls) , i want to create table that hold data for each hour, so i round the column and want to group by this column in the same time|||you can round a datetime? please show your query|||This is the Query of round datetime

DATEADD(Hour, DATEDIFF(Hour, 0, cdrCallDate), 0)

You can change [hour] to day , minutes , second , etc...|||yes, you can GROUP BY that expression :)

and you cannot use second, it causes an overflow

:)|||thanks man for this information

Can I grant a group access to my db

I would like to be able to do something like this:
use mydb
exec sp_grantdbaccess 'localhost\Users'
go
When I do, I get this error msg:
Windows NT user or group 'localhost\Users' not found. Check the name
again.
The error message implies that I should be able to add a group.
A co-worker read on the web somewhere that this should work:
exec sp_grantdbaccess 'localhost\domain Users'
but it also fails the same way.
Any help would be appreciated,
TIA,
DaveDid you add that group as a login to SQL Server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dave" <kaatzd@.hotmail.com> wrote in message
news:1155062014.520009.304330@.b28g2000cwb.googlegroups.com...
>I would like to be able to do something like this:
> use mydb
> exec sp_grantdbaccess 'localhost\Users'
> go
> When I do, I get this error msg:
> Windows NT user or group 'localhost\Users' not found. Check the name
> again.
> The error message implies that I should be able to add a group.
> A co-worker read on the web somewhere that this should work:
> exec sp_grantdbaccess 'localhost\domain Users'
> but it also fails the same way.
> Any help would be appreciated,
> TIA,
> Dave
>|||Users is a predefined group, my understanding was that SQL would
recognize it.
Be that as it may, someone else in my company gave me the solution:
exec sp_grantdbaccess 'NT AUTHORITY\Authenticated Users'
and then I also needed to add above users to a role for my db, like so:
exec sp_addrolemember 'db_datareader', 'NT AUTHORITY\Authenticated
Users'
Thanks,
Dave
Tibor Karaszi wrote:
> Did you add that group as a login to SQL Server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dave" <kaatzd@.hotmail.com> wrote in message
> news:1155062014.520009.304330@.b28g2000cwb.googlegroups.com...
> >I would like to be able to do something like this:
> >
> > use mydb
> > exec sp_grantdbaccess 'localhost\Users'
> > go
> >
> > When I do, I get this error msg:
> > Windows NT user or group 'localhost\Users' not found. Check the name
> > again.
> >
> > The error message implies that I should be able to add a group.
> >
> > A co-worker read on the web somewhere that this should work:
> >
> > exec sp_grantdbaccess 'localhost\domain Users'
> >
> > but it also fails the same way.
> >
> > Any help would be appreciated,
> > TIA,
> > Dave
> >

Can I grant a group access to my db

I would like to be able to do something like this:
use mydb
exec sp_grantdbaccess 'localhost\Users'
go
When I do, I get this error msg:
Windows NT user or group 'localhost\Users' not found. Check the name
again.
The error message implies that I should be able to add a group.
A co-worker read on the web somewhere that this should work:
exec sp_grantdbaccess 'localhost\domain Users'
but it also fails the same way.
Any help would be appreciated,
TIA,
DaveDid you add that group as a login to SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dave" <kaatzd@.hotmail.com> wrote in message
news:1155062014.520009.304330@.b28g2000cwb.googlegroups.com...
>I would like to be able to do something like this:
> use mydb
> exec sp_grantdbaccess 'localhost\Users'
> go
> When I do, I get this error msg:
> Windows NT user or group 'localhost\Users' not found. Check the name
> again.
> The error message implies that I should be able to add a group.
> A co-worker read on the web somewhere that this should work:
> exec sp_grantdbaccess 'localhost\domain Users'
> but it also fails the same way.
> Any help would be appreciated,
> TIA,
> Dave
>|||Users is a predefined group, my understanding was that SQL would
recognize it.
Be that as it may, someone else in my company gave me the solution:
exec sp_grantdbaccess 'NT AUTHORITY\Authenticated Users'
and then I also needed to add above users to a role for my db, like so:
exec sp_addrolemember 'db_datareader', 'NT AUTHORITY\Authenticated
Users'
Thanks,
Dave
Tibor Karaszi wrote:[vbcol=seagreen]
> Did you add that group as a login to SQL Server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dave" <kaatzd@.hotmail.com> wrote in message
> news:1155062014.520009.304330@.b28g2000cwb.googlegroups.com...

Can I Created a BPA 'Job'

Can I created a job that I can send to my Service Delivery group to run on the Production servers? I'm a developer and don't WANT access to PROD, but I want BPA run against PROD. I'd like to have them install BPA and them send them a 'package' to run. Is
this available with BPA?
Thanks,
Maybe not exactly what you want, but there's a way you can get close.
Install BPA, which creates the repository. In it, create a best practice
group that scans things the way you want. You should register the servers
from production, or register them as (local).
Have your production team install BPA (they will have to create a repository
but it can be discarded) and then ask them to run bpa (likely the command
line version bpacmd.exe) pointing at your repository database. If they do
that, they'll have to execute the best practice group that you created.
- Christian
"B.W." <bw.lightsey@.tetrapak.com> wrote in message
news:611E0DF9-C69E-4EF6-AA6B-0E390C3430A8@.microsoft.com...
> Can I created a job that I can send to my Service Delivery group to run on
the Production servers? I'm a developer and don't WANT access to PROD, but I
want BPA run against PROD. I'd like to have them install BPA and them send
them a 'package' to run. Is this available with BPA?
> Thanks,
>
sql

Can I add a named calculation to a measure group

Hi everyone,

Here's my question:

I add a named calculation to one fact table, let's say total cost = units * cost

After doing so, I add this named calculation to a measure group with a sum aggregation method. But while I was processing the cube, an error occurred, which said "OLAP storage engine error..."

Is there anything I had done wrong? or there's better way to achieve the require.

Thanks,

It's possibble to add a named calculation to a measure group...

But If you want to aggregate as sum and generates an error, check the datatypes of the fields Units and Cost... maybe be you need to do a CONVERT... try to explore your facttable and see if there is any error...

Give more details...

regards!!

|||Hello Pedro, thanks for the reply.

I tried it again, and it works fine without an error this time.
|||

I'm happy you get it!

Mark your answer as resolved!

regards!!

Can fields span TableGroups independantly of columns

Hi,

I'm trying to add a heading to my report using Table and TableGroups. But I don't want my group headings to line up with the detail rows. This seems like a common requirement in my mind but I can't figure out how to do it.

See made up example of a report below:

---

Person Name Address <-Report header

---

Area: A12 3BC, Newtown, Someplace <-TableGroup header

---

Mr Some Body 1 The Street <-Detail rows

Mrs Anne Other 2 The Street

---

Area: B23 4CD, Oldtown, Someplace <-TableGroup header

---

Mr Ran Dom 3 The Avenue <-Detail rows

---

So the TableGroup displays Area which is grouped by Post code, city etc.

The detail rows displays individual people and their street address.

NOTE: The Area fields do not line up with the Person fields in fact they overlap how can I do this in SSRS?

Thanks in advance for your help,

Mike G

I've found out how to do this now...

You need to have a single column in the table that spans the whole width of the report, (as opposed to having one column in the table control for each field)

Then add rectangle controls to each of the cells (Report header, table group headers and details etc.)

Then you can add textbox controls onto the rectangle controls and put them where you like within the cell.

It's a shame you have to do it this way because you loose the nice feature that lines up the details columns with the labels you've added in the report heading.

Hope that all makes sense, it's difficult to explain graphical layout issues in text!

-MIke G

|||

have you tried the list control?

Can fields span TableGroups independantly of columns

Hi,

I'm trying to add a heading to my report using Table and TableGroups. But I don't want my group headings to line up with the detail rows. This seems like a common requirement in my mind but I can't figure out how to do it.

See made up example of a report below:

---

Person Name Address <-Report header

---

Area: A12 3BC, Newtown, Someplace <-TableGroup header

---

Mr Some Body 1 The Street <-Detail rows

Mrs Anne Other 2 The Street

---

Area: B23 4CD, Oldtown, Someplace <-TableGroup header

---

Mr Ran Dom 3 The Avenue <-Detail rows

---

So the TableGroup displays Area which is grouped by Post code, city etc.

The detail rows displays individual people and their street address.

NOTE: The Area fields do not line up with the Person fields in fact they overlap how can I do this in SSRS?

Thanks in advance for your help,

Mike G

I've found out how to do this now...

You need to have a single column in the table that spans the whole width of the report, (as opposed to having one column in the table control for each field)

Then add rectangle controls to each of the cells (Report header, table group headers and details etc.)

Then you can add textbox controls onto the rectangle controls and put them where you like within the cell.

It's a shame you have to do it this way because you loose the nice feature that lines up the details columns with the labels you've added in the report heading.

Hope that all makes sense, it's difficult to explain graphical layout issues in text!

-MIke G

|||

have you tried the list control?

Can fields span TableGroups independantly of columns

Hi,

I'm trying to add a heading to my report using Table and TableGroups. But I don't want my group headings to line up with the detail rows. This seems like a common requirement in my mind but I can't figure out how to do it.

See made up example of a report below:

---

Person Name Address <-Report header

---

Area: A12 3BC, Newtown, Someplace <-TableGroup header

---

Mr Some Body 1 The Street <-Detail rows

Mrs Anne Other 2 The Street

---

Area: B23 4CD, Oldtown, Someplace <-TableGroup header

---

Mr Ran Dom 3 The Avenue <-Detail rows

---

So the TableGroup displays Area which is grouped by Post code, city etc.

The detail rows displays individual people and their street address.

NOTE: The Area fields do not line up with the Person fields in fact they overlap how can I do this in SSRS?

Thanks in advance for your help,

Mike G

I've found out how to do this now...

You need to have a single column in the table that spans the whole width of the report, (as opposed to having one column in the table control for each field)

Then add rectangle controls to each of the cells (Report header, table group headers and details etc.)

Then you can add textbox controls onto the rectangle controls and put them where you like within the cell.

It's a shame you have to do it this way because you loose the nice feature that lines up the details columns with the labels you've added in the report heading.

Hope that all makes sense, it's difficult to explain graphical layout issues in text!

-MIke G

|||have you tried the list control?

Can Classic ASP Interact with SQL Server 2005?

Forgive me if this is not the most appropriate group to pose this question.
I have a classic ASP app that is currently connecting to a SQL Server 2000
database using MDAC 2.8. Can a classic ASP application using MDAC 2.8
connect to and interact with SQL Server 2005? What issues might I encounter?
What known requirements are there to do this?
Thank you very much for any insight you can offer.
Ken Sturgeon
Most probably you dont need to change anything in your code to make this
work. At least I didn't have to change anything
Milan
P.S. Most probably you use ADO objects.
"Ken Sturgeon" <aksturgeon@.charter.net> wrote in message
news:O8Wn0j4FGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Forgive me if this is not the most appropriate group to pose this
> question.
> I have a classic ASP app that is currently connecting to a SQL Server 2000
> database using MDAC 2.8. Can a classic ASP application using MDAC 2.8
> connect to and interact with SQL Server 2005? What issues might I
> encounter? What known requirements are there to do this?
> Thank you very much for any insight you can offer.
> Ken Sturgeon
>

Can Classic ASP Interact with SQL Server 2005?

Forgive me if this is not the most appropriate group to pose this question.
I have a classic ASP app that is currently connecting to a SQL Server 2000
database using MDAC 2.8. Can a classic ASP application using MDAC 2.8
connect to and interact with SQL Server 2005? What issues might I encounter?
What known requirements are there to do this?
Thank you very much for any insight you can offer.
Ken SturgeonMost probably you dont need to change anything in your code to make this
work. At least I didn't have to change anything
Milan
P.S. Most probably you use ADO objects.
"Ken Sturgeon" <aksturgeon@.charter.net> wrote in message
news:O8Wn0j4FGHA.1028@.TK2MSFTNGP11.phx.gbl...
> Forgive me if this is not the most appropriate group to pose this
> question.
> I have a classic ASP app that is currently connecting to a SQL Server 2000
> database using MDAC 2.8. Can a classic ASP application using MDAC 2.8
> connect to and interact with SQL Server 2005? What issues might I
> encounter? What known requirements are there to do this?
> Thank you very much for any insight you can offer.
> Ken Sturgeon
>

Can Calculated Measures be in specific Measure Group?

I have two measure groups in my cube, and a calculated measure that only uses measures from one of the measure groups. Is there a way to assign that calc measure to one of my measure groups, so in client applications, users will find in in a logical place, rather than sitting all by itself where it might be confusing?

Thanks

Kory

Yes they can. The feature is well hidden away though:
In BIDS select your cube and the calculations tab. Then select the Cube-> Calculation Properties menu item.|||

Yes, certainly. In the Cube Editor in BIDS, go to the Calculations tab and click on the 'Calculation Properties' button on the toolbar (it's next to the buttons which switch between form view and script view). You can associate calculated measures with measure groups in the resulting dialog.

HTH,

Chris

Can anyone explain this processing schedule

Hi,

I have a fairly complicated cube which has:

74 Measure groups

Each measure group contains 12 partitions

= 888 partitions in my cube

I have a parameterized XMLA processing script that processes 2 partitions in each measure group - i.e. 148 partitions. It does this inside a <Parallel></Parallel> element.

When I executed the XMLA script I recorded the progress in Profiler. I saw that, prior to processing the 148 paritions, there was lots and lots of "Progress Report End" EventClass records with a message of:

Finished processing the '<partition name>' partition

There were actually 518 of these records so whatever it is doing, it is not doing it for every single partition. Note that most of the listed partitions were NOT specified in my XMLA processing script.

I also has a trace running against the underlying source data and can see that none of these events resulted in a query being fired against the source.

So, the question is fairly simple. Why am I seeing all these "Finished processing..." events for partitions that:

I never asked to be processed and

Don't result ina query against the source.

Any explanation would be most welcome.

Thanks

Jamie

P.S. The .trc file from tracing AS is 2.14MB. I can provide it if anyone is interested.

It is possible you are seeing Analysis Server as part of preparing to load new data, removing data from the partitions that are no longer valid.

It does that by internally issuing ProcessClear command. ProcessClear will generate ProgressReportBegin and ProgressReportEnd commands.

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

|||

Edward Melomed wrote:

It is possible you are seeing Analysis Server as part of preparing to load new data, removing data from the partitions that are no longer valid.

It does that by internally issuing ProcessClear command. ProcessClear will generate ProgressReportBegin and ProgressReportEnd commands.

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

Thanks Edward but that doesn't yet explain what I am seeing.

In my scenario all the other partitions are perfectly valid. What circumstances might cause AS to think that data in those partitions was NOT valid and thus do a ProcessClear? if this is what IS happening then I am very worried.

Regards

|||

What does you processing command lists?

Does it list entire measure group or it lists individual partitions?

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

|||

Edward Melomed wrote:

What does you processing command lists?

Does it list entire measure group or it lists individual partitions?

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

Partitions only!

It also does a processUpdate on all my dimensions.

-Jamie

|||

That explains it. ProcessUpdate on dimension will case Analysis Server to drop aggregations and indexes from your partitoins. These are the events you are seeing.

To learn more about what to expect during processing you can click on Impact Analysis button in the processing dialog.

Processing dialog sends a kind of "prepare" statement to the sever and recives back a rowset with all objects affected by the processing command.

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

|||

Edward Melomed wrote:

That explains it. ProcessUpdate on dimension will case Analysis Server to drop aggregations and indexes from your partitoins. These are the events you are seeing.

To learn more about what to expect during processing you can click on Impact Analysis button in the processing dialog.

Processing dialog sends a kind of "prepare" statement to the sever and recives back a rowset with all objects affected by the processing command.

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

Edward,

I knew we'd get there in the end! :)

That's great service. Thank you very much.

-Jamie

|||

And I've done a small write-up here: https://blogs.conchango.com/jamiethomson/archive/2006/07/26/4272.aspx

-Jamie

|||

If I understand this correctly, we will lose aggregations on the cube when we do process update against dimensions.

I have a following scenario, what will happen in this case then? I loop through list of dimensions (16) and conduct process update by executing DDL statememt. Once I updated the dimensions I fully process 3 partitions (Current Fiscal Period, Current Fiscal Period-1, Current Fiscal Period-2) which we expect to get new / delta fact data.

I have got 7 years worth of history data loaded into the cube. Aggregations were built based on user reporting requirements.

Question is, will I lose aggregations all together, when I process update on dimensions every night?

Thanks

Sutha

|||

Yes, you might loose some of the aggregations.

For more information about the situation try and search for term "flexible aggregations".

In short: ProcessUpdate of the dimension allows you to move hierarcy member from one paren to another. For example JonD can change city from Seattle to Portland. If you allow such change in your dimension, Analysis Server needs to drop aggregation that sums up data across cities.

If you said that customer cannot move from one city to another by setting attribute relationship between customer attribute and city attribute as Rigid, Analysis Server will not drop aggregation going across the cities. So one way to treat this situation is to set all attribute relationsip types to Rigid across all dimensions.

Another solution is to use Process Add for dimension. This option only adds new members to dimension and can change some dimension member properties, but does not move member from parent to another. So Analysis Server doesnt need to touch aggregations in this case. To use Process Add you need to make sure you build a view restricting to only new members.

And your last option is to run Process Index command across all of your old partitions to make sure you build all aggregations.

Hope that helps.

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

|||

Edward

Thanks. Yes it does help. One quick question though.

In my scenario, I have a measure group with multi billion rows, but I do process 3 partitions daily. Do I have to "Process Index" for those 3 partitions or for the measure group? If I understand you correctly, I need to do Process Index against the measure group not just those 3 partitions I have just processed.

Thanks

Sutha

Can a Domain Group be set as the dbo to a database?

To keep the Login list more manageable and keep names out of it can I set a
AD Domain Group as the dbo to a database?
This would also help cover the cases when someone goes on vacation and a
different member of the group covers for them.You can add the group to Database Role db_owner.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Jim Abel" wrote:

> To keep the Login list more manageable and keep names out of it can I set
a
> AD Domain Group as the dbo to a database?
> This would also help cover the cases when someone goes on vacation and a
> different member of the group covers for them.