Home page access

We are running the June CTP.
When some users go to htttp://theserver/Reports, all they see is the
word 'Home' and a line across the page.
This happens even though I have added these users as both
Administrators and Users.
I imagine that this has something to do with the domain, but I don't
see what arrangement would allow them to get to a partially completed
page.
Any ideas?
JimMake sure the RS services are running under a domain log in and that the
login has permissions on the Tempfiles directory under ths MSSQL.3
directory.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<jhcorey@.yahoo.com> wrote in message
news:1125062702.187423.141040@.g14g2000cwa.googlegroups.com...
> We are running the June CTP.
> When some users go to htttp://theserver/Reports, all they see is the
> word 'Home' and a line across the page.
> This happens even though I have added these users as both
> Administrators and Users.
> I imagine that this has something to do with the domain, but I don't
> see what arrangement would allow them to get to a partially completed
> page.
> Any ideas?
> Jim
>|||The services are running under a login that is an admin in the domain,
as well as an admin on the machine.
We notice that there is a user group on the machine called.
SQLServer2005ReportServerUser$MSSQLServer. Users that we've added to
this can use RS normally. But as I noted, users that we've added from
the web site cannot. These are all users in the same domain.|||My guess here is that they have not been setup in a role. The windows groups
are for validation of who they are, not what they can do. You need to assign
users or groups to a role (in report manager). Read up in books online about
roles. What I do is have a local group to that local group I add domain
groups and invidual users. I then assign the local group to the browser
role.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<jhcorey@.yahoo.com> wrote in message
news:1125330865.896229.33860@.f14g2000cwb.googlegroups.com...
> The services are running under a login that is an admin in the domain,
> as well as an admin on the machine.
> We notice that there is a user group on the machine called.
> SQLServer2005ReportServerUser$MSSQLServer. Users that we've added to
> this can use RS normally. But as I noted, users that we've added from
> the web site cannot. These are all users in the same domain.
>|||Ah yes, it was simple of course once one knew where to look -- in the
properties for the home page.
I was thinking that System security would take care of everything.

Hold group constant across time?

Hi eveyone,

My users asked me a question today that I wasn't sure of so I was hoping you guys could give me some advice.

Is it possible to make the cube hold a group constant across time? For example, I want to take all of the customers that were here in December 2006 and see how they performed during the year. Is that something I can do in a cube? Obviously, I could create a seperate cube with only those customers in it, but I'd like to avoid that if possible.

Thanks!

Yes, you should be able to accomplish this. You'd likely want to create a named set in your Customers dimension that defines the group of customers in Dec 2006 (however you want to define that -- they had a value for a given measure, they have an attribute that defines their current status, etc.) Then, you (or your users) should be able to put that named set of customers into a pivot table and see measures across time just for that set of customers.

HTH,

Dave Fackler

|||

Thanks for your response! I guess I'm confused how to define the group of customers in 2006 and show them across time since 'time' is what defines them. If I limit the query in the where statement to those in 2006 I don't get data for anything but 2006. How do I write the query so it keeps 2006 customers and then shows those customers over time?

|||

You'll need to define the set of 2006 customers as a named set and then use that named set in your query...

For example, the following query shows sales to customers in the US for 2004 from the Adventure Works cube:

select

{[Measures].[Internet Sales Amount]} on columns,

[Customer].[Customer Geography].[United States].Children on rows

from

[Adventure Works]

where

([Date].[Calendar Year].[CY 2004])

That is likley similar to the query you are using -- it only shows 2004 sales and the customers associated with those sales since the where clause includes 2004 as a slicer. However, this slightly different query shows all sales over time for the customers who had sales in 2004:

with set [CY 2004 Customers] as

'filter([Customer].[Customer Geography].[United States].Children, ([Measures].[Internet Sales Amount], [Date].[Calendar Year].[CY 2004]) > 0)'

select

[Date].[Calendar Year].Members on columns,

[CY 2004 Customers] on rows

from

[Adventure Works]

where

([Measures].[Internet Sales Amount])

The key here is that the named set returns the set of US customers who had sales in 2004. This set is then used to define the rows in the subsequent query, but the query returns sales across time for that set of customers.

HTH,

Dave Fackler

|||

Ahh Dave, you're excellent!! And completely right.. the first query is exactly what I had. I know SQL pretty well so I knew that wasn't going to work but I'm a novice at MDX. So I guess the named set is basically a SQL subquery?

Your example makes sense but now how do I apply this as a named set in my cube through Visual Studio?

Thanks for you patience! I'm reading several MDX books as quickly as possible!

EDIT:

Well I spent my entire day at work reading up on MDX and trying to apply your example, but no luck I just can't seem to get the filter expression correct. I'm assuming when I apply this code to my cube as a named set I need to put the filter expression in the 'expression' box on the calculations tab? How do I modify the filter statement to show measures no matter how they're sliced?

Hold group constant across time?

Hi eveyone,

My users asked me a question today that I wasn't sure of so I was hoping you guys could give me some advice.

Is it possible to make the cube hold a group constant across time? For example, I want to take all of the customers that were here in December 2006 and see how they performed during the year. Is that something I can do in a cube? Obviously, I could create a seperate cube with only those customers in it, but I'd like to avoid that if possible.

Thanks!

Yes, you should be able to accomplish this. You'd likely want to create a named set in your Customers dimension that defines the group of customers in Dec 2006 (however you want to define that -- they had a value for a given measure, they have an attribute that defines their current status, etc.) Then, you (or your users) should be able to put that named set of customers into a pivot table and see measures across time just for that set of customers.

HTH,

Dave Fackler

|||

Thanks for your response! I guess I'm confused how to define the group of customers in 2006 and show them across time since 'time' is what defines them. If I limit the query in the where statement to those in 2006 I don't get data for anything but 2006. How do I write the query so it keeps 2006 customers and then shows those customers over time?

|||

You'll need to define the set of 2006 customers as a named set and then use that named set in your query...

For example, the following query shows sales to customers in the US for 2004 from the Adventure Works cube:

select

{[Measures].[Internet Sales Amount]} on columns,

[Customer].[Customer Geography].[United States].Children on rows

from

[Adventure Works]

where

([Date].[Calendar Year].[CY 2004])

That is likley similar to the query you are using -- it only shows 2004 sales and the customers associated with those sales since the where clause includes 2004 as a slicer. However, this slightly different query shows all sales over time for the customers who had sales in 2004:

with set [CY 2004 Customers] as

'filter([Customer].[Customer Geography].[United States].Children, ([Measures].[Internet Sales Amount], [Date].[Calendar Year].[CY 2004]) > 0)'

select

[Date].[Calendar Year].Members on columns,

[CY 2004 Customers] on rows

from

[Adventure Works]

where

([Measures].[Internet Sales Amount])

The key here is that the named set returns the set of US customers who had sales in 2004. This set is then used to define the rows in the subsequent query, but the query returns sales across time for that set of customers.

HTH,

Dave Fackler

|||

Ahh Dave, you're excellent!! And completely right.. the first query is exactly what I had. I know SQL pretty well so I knew that wasn't going to work but I'm a novice at MDX. So I guess the named set is basically a SQL subquery?

Your example makes sense but now how do I apply this as a named set in my cube through Visual Studio?

Thanks for you patience! I'm reading several MDX books as quickly as possible!

EDIT:

Well I spent my entire day at work reading up on MDX and trying to apply your example, but no luck I just can't seem to get the filter expression correct. I'm assuming when I apply this code to my cube as a named set I need to put the filter expression in the 'expression' box on the calculations tab? How do I modify the filter statement to show measures no matter how they're sliced?

Ho do I allow multiple users to share a SQL 2005 Express database?

Hello,

Ho do I allow multiple users to share a database?

Background

I have developed a Windows App in VS.NET 2005 which connects to a SQL 2005 Express database.

Now I want to install the app and database on the network and I am getting an error "File 'file_name' is on a network device not supported for database files"

What is the best way to get this working

Thanks in advance,

Phil

Sorry, please find my connection string below. (Note that Z:\ is my network share)

Data Source=.\SQLEXPRESS;AttachDbFilename=&quot;Z:\Data\SurveyDB.mdf&quot;;Integrated Security=True;Connect Timeout=30;User Instance=True

Thanks,

Phil

|||do you really need it as user instance, i suggest that you attach the database to the main instance of sqlexpress and modify your connection string. user instance bahaves differently since everytime an applicaqtion is calling this method, the calling application spawns a service to the installed sql server express and makes the user of the calling application its administrator|||

Hi, and thanks for your prompt reply,

It seems that I am only just learning about the different types of connections!

How do I attach the database to the main instance of SQL Express? And then what would an example of my connection string be?

Thanks

Phil

|||

OK, I somehow have made a "main instance" of the database, and have changed my connection string to Server=LAPTOP\SQLEXPRESS;Database=SurveySystemsDB;Trusted_Connection=True

BUT I have no idea on how I "converted" my database from its original "User Instance" to the "main instance"

Can anybody tell me how to do this?

Thanks,

Phil

|||

Hi Phil,

User Instances are not about the database, it's about the server itself. A User Instance is a special instance of SQL Express that is started up at run-time; you can read about the details in the User Instance white paper. You can not actually share a User Instance between multiple users, they are designed to only allow local access.

The way you "converted" your database is simply by attaching it to your main instances of SQL Express rather than trying to attach it at run time using a connection string. Since I wasn't there when you attached your file, I couldn't tell you how you did it. The normal ways of attaching a database are to run T-SQL script, such as using sp_attach_db or by using a GUI tool such as Management Studio Express to attach the database.

Once a database is attached to a server, you appliction simply makes a connection as you've demonstrated above.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

sql

History/Data Change File Approach

I need to record in a table:
Who, When, What Field and New Value of Fields
When changes occur to an existing record.

The purpose is for users to occassionally view the changes. They'll want to be able to see the history of the record - who changed what and when.

I figured I'd add the needed code to the stored procedure that's doing the update for the record.

When the stored procedure is called to do the update, the PK and parameters are sent.

The SP could first retain the current state of the record from the disk,
then do the update, then "spin" thru the fields comparing the record state prior to the update and after. Differences could be parsed to a "Changes string" and in the end, this string is saved in a history record along with a few other fields:

Name, DateTime, Changes

FK to Changed Record: some int value
Name: Joe Blow
Date: 1/1/05 12:02pm
Changes: Severity: 23 Project: Everest Assigned Lab: 204

How does the above approach sound?

Is there a better way you'd suggest?

Any sample code for a system that spins thru the fields comparing 1 temporary record with another looking for changes?

Thanks,

PeterHave you considered using a trigger? You can use the inserted and deleted tables to compare your data without having to save it manually. Then insert your data into the history table as you suggested.

We often just save the before image to the history table along with the type of operation performed, the id that was used to perform it and a time stamp. Inserted records are not recorded (because all their data is already recorded on the live table) but deleted ones are. The differences for updated records can be determined at any time by comparing the before image to the next or previous stored image or the current actual record. We rarely actually look at this sort of history however unless data disappears or the customer tells us that there is something else wrong with the data and we need to trace what happened to it.|||ejustuss - thanks for the thoughts.

Good idea about simply saving the before image prior to the actual save of the new one.

Our users are used to systems where they can click a button and see essentially the change history of the record. This particular system is a Work Order system. The status of the WO changes over time, etc.

In other non-SQL Server systems I've developed I have a routine that prior to actual save:

1. saves "before save" copy of record
2. updates the record with new values into the DB
3. peels off an "After save" copy of record
4. runs a routine that compares side by side each field. Any changes are noted in a text variable (field name, new value).
5. Once all the fields are spun thru (compared), if there is any information in the text variable, a "change table" record is created with FK to the parent record, Who Changed it, When changed, and a single text field describing all the changes.

Weeks later when a user is viewing the particular record, they can press a button and have a query run against the change table to bring up a simple list of what changed when.

One wrinkle is that a Work Order has a huge text area. Once a WO is accepted by a lab, this text are becomes "frozen". So if we simply peel off a before save copy each time a user specifies an update - I wouldn't want to needlessly include this particular field due to space considerations.

Bottom line - I was assuming someone might have a canned routine for spinning thru a record comparing all field values against an identical layed out record. I figured there might be a system function or 2 to:
1. Identify how many fields are in a table
2. identify the content of a field - something like @.@.Field(i)
where i=1 to number of fields in the table.

Peter|||I don't know of any function like that although I am sure you can write one.

In triggers you can use IF UPDATE(column) to test if a column is updated but you still have to use the column names which means writing a different trigger for each case . The data in text fields will probably not be accessible within the trigger.

You don't have to save all the fields if you do a before image either just the fields you want to compare in case they are updated.

Hints

I am kind of confused about the way SQL Server 2000 handles the hints
that users supply with their SQL statements.

>From BOL, it seems that one can specify them with "WITH (...)" clauses
in SQL statements known as table hints. Sometimes, multiple uses of
this form in a statement is OK. Then there is the OPTION clause for
specifying statement hints. However, the documentation on OPTION
section discourages their use.

Being relatively new to SQL Server and still learning about it, what is
the general practice? Use hints or not? And if so, how (through WITH
or OPTION clauses)?

Cheers!<newtophp2000@.yahoo.com> wrote in message
news:1104122591.142300.19090@.f14g2000cwb.googlegro ups.com...
> I am kind of confused about the way SQL Server 2000 handles the hints
> that users supply with their SQL statements.
> >From BOL, it seems that one can specify them with "WITH (...)" clauses
> in SQL statements known as table hints. Sometimes, multiple uses of
> this form in a statement is OK. Then there is the OPTION clause for
> specifying statement hints. However, the documentation on OPTION
> section discourages their use.
> Being relatively new to SQL Server and still learning about it, what is
> the general practice? Use hints or not? And if so, how (through WITH
> or OPTION clauses)?

Generally, "don't".

Usually SQL server will make better guesses than you can.

> Cheers!|||>> what is the general practice? Use hints or not? <<

1) "Trust in the Optimizer, Luke!" It is usually smarter than you are.
What happens when you give a bad hint?

2) Once you write a query with a hint, that hint stays there. Even if
the pathological situation that made you use a hint heals up. Nobody
will dare remove it later, since it looks important.

3) Every product that supports hints has a different syntax and
underlying model, so your hint code will not port, and the logic of
your hint might not port either.

For example, in Sybase SQL Anywhere, you can give a guess as to what
percentage of the time a predicate will be TRUE. Their optimizer uses
that guess instead of it own computation to build the query. It is not
forced to use a particlar index or method. like other products.|||--CELKO-- wrote:

> 2) Once you write a query with a hint, that hint stays there. Even if
> the pathological situation that made you use a hint heals up. Nobody
> will dare remove it later, since it looks important.

That's funny! And too true!

Zach|||(newtophp2000@.yahoo.com) writes:
> I am kind of confused about the way SQL Server 2000 handles the hints
> that users supply with their SQL statements.
> From BOL, it seems that one can specify them with "WITH (...)" clauses
> in SQL statements known as table hints. Sometimes, multiple uses of
> this form in a statement is OK. Then there is the OPTION clause for
> specifying statement hints. However, the documentation on OPTION
> section discourages their use.
> Being relatively new to SQL Server and still learning about it, what is
> the general practice? Use hints or not? And if so, how (through WITH
> or OPTION clauses)?

Be very conservative with adding hints. In an ideal you would never have to
use them, but today I add two hints to one query: one index hint, and one
OPTION clause to turn of parallelism.

My general rule is that I add a hint, if 1) there is an apparent performance
problem and 2) there is an obvious choice of how the query plan should go.
The one hint I am the least conservative is OPTION (MAXDOP 1), because
even if the query would execute faster with parallelism, it will not at
least monopolize all processors in the machine. (And often parallel plans
are more ineffecient than the non-parallel plans.) The hint I am most
conservative of using is the join hint - you dump in a word between
INNER and JOIN, since this use of this hint results in a warning.

Whether to use WITH or OPTION depends on what you want to force. They
serve different purposes, therefore you cannot say that one is better
than the other.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> That's funny! And too true! <<

Remember the quote from early UNIX days? "There is nothing more
permanent than a temporary patch!"

High Cpu Usage During Logon

MY PROBLEM IS SOME HOW STRANGE TO ME AS I AM NOT AN EXPERT...

WE USE AN ERP PROGRAM ON A SQL7 DATABASE....WHEN USERS LOGON AND TRY TO WORK I HAVE 100% CPU USAGE AND THE SERVER IS NOT RESPONDING UNTIL I KILL THE APP. IF THE USER HAS ADMINISTRATOR RIGHTS EVERYTHING WORKS SMOOTHLY...ADMINISTRATOR RIGHTS FOR THE DATABASE...

WHAT I MANAGED TO FINDOUT IS THAT WHAT IS RUNNING DURING THE SERVER LOCKS IS SP_CURSOROPEN

ANY SUGESTIONS?.....Howdy

Sounds like you have possible wrong level of access for the app - try adding it to the db_datareader & db_datawriter database roles & see what happens. If that doesn't make any difference, remove from all roles ( except public ) then only to db_owner database role & see what happens.

Cheers,

SG.|||nothing.......

some new facts on the matter are that the users eventualy log on and can work but the speed is very slow when they try to change menu options.....the problem occured after 3 years of smoothly operation and that is the strang thing....also when i say admin right i mean from the apps option since on the DB i have only one user (SA) who is used by the app......|||Howdy

Well, sa will give you all the access you need.

How are you for disk space - especially for the TEMPDB database & the production db? Also, sounds possible you may be paging memory madly out to disk , hence the slow speed. Have you run perfmon to check the efficiency of the server?

Sounds odd that after 3 years of smooth operation all goes to custard....

What else has changed recently? Any service pack updates etc?

Cheers,

SG|||nothing except a shrink and a reorganize.....the second was running on a schedule for some time now......
as for space everything is ok....
performance has nothing to do also i believe it is something else....something about the app... since the only user that logs on sql is sa nomatter if i give him rights using the app or not....|||Howdy

Common sense tells me SOMETHING has to have changed. If you havent run out of disk space, the server isnt under powered then someone, somewhere has to have altered something.

Try running Profiler and see what activity is occurring in the database just as it starts to slow down. Is there a particular sequence of events that happen as this problem starts, or is it an ongoing problem? Has the number of users gone up? have new functionality been added to the system?

It wouldnt be the first time someone has altered a system but not told anyone....

Cheers,

SG|||at first i would like to say thanks for your help....

i managed to fix the problem but i dont know yet what coused it....
the solution was to reindex the tables that are involve to security for the app and manualy updatestats for the production db.....
know i have a small delay which is acceptable and everybody is working ok...

thanks again...|||Howdy,

....well without sounding a little comical...that would have been one of my next suggestions......

Check the settings for the database - they should be ( by default ) set to update stats automatically. Its a bit scary that this was turned off...You can create a separate Maintenance Plan to do this - we do and sample 25% of the database. Also a reindex of each index in the database is useful - we do it weekly & it keeps the index fragmentation right down and speed up.

You can also turn off autoshrink for the databse which will lift the speed a bit more but you will need to schedule a job to do this at least weekly as this will keep the databases from getting too big & slowing down.

Also, turn off the autoclose for the database - this will remove a small overhead on performance.

Cheers,

SG.

High CPU time per user as soon as they connect?

On a couple of our servers, users are currently scoring very
high on "CPU Time" as reported by Enterprise Manager and by
sp_who2, both as soon as they log in, and afterwards -
5,000,000 at login and 47,000,000 later on are the top figures.
Most of the fleet is on SQL Server 2000 Service Pack 2, with
between 2 and 8 hard-working processors. The hardest working
server is on Service Pack 3(a), clustered, and its top 5
current values in CPU time are between 382,847 and 705,879.
The servers are all on clients' sites and networks, with client's
choice of firewall. Usual application is a Java interface using
jTDS, but CPU time is also reported high for a new Query Analyzer
connection. SQL Server, not Windows, accounts are most often used.
Clients aren't complaining of poor performance, apparently, but
maybe they already gave up phoning for that.
So, should we worry? Reboot? Reindex? Patch? Disinfect?
Or ask around, "Who left SQL Profiler running"...i would run profiler to find out whats going on.
i would also run perfmon to find out what average cpu is
over the entire day
otherwise, you have nothing from which to make reasonable
assessments
>--Original Message--
>On a couple of our servers, users are currently scoring
very
>high on "CPU Time" as reported by Enterprise Manager and
by
>sp_who2, both as soon as they log in, and afterwards -
>5,000,000 at login and 47,000,000 later on are the top
figures.
>Most of the fleet is on SQL Server 2000 Service Pack 2,
with
>between 2 and 8 hard-working processors. The hardest
working
>server is on Service Pack 3(a), clustered, and its top 5
>current values in CPU time are between 382,847 and
705,879.
>The servers are all on clients' sites and networks, with
client's
>choice of firewall. Usual application is a Java
interface using
>jTDS, but CPU time is also reported high for a new Query
Analyzer
>connection. SQL Server, not Windows, accounts are most
often used.
>Clients aren't complaining of poor performance,
apparently, but
>maybe they already gave up phoning for that.
>So, should we worry? Reboot? Reindex? Patch?
Disinfect?
>Or ask around, "Who left SQL Profiler running"...
>.
>

Hiding sub Reports after deployment

Hi All,
I have two reports and each of them contains 3 sub reports. When I deployed
my project, I don't want the users to see the sub reports. I have set Hide in
list view = Checked for each of the sub report. After doing this when the
users access the reports folder, they do not see the reports until they click
on the Show details, where they again see all the reports. How should I set
this up? Please help!
--
GBM
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200705/1On May 3, 7:29 pm, "gbaksh via SQLMonster.com" <u30235@.uwe> wrote:
> Hi All,
> I have two reports and each of them contains 3 sub reports. When I deployed
> my project, I don't want the users to see the sub reports. I have set Hide in
> list view = Checked for each of the sub report. After doing this when the
> users access the reports folder, they do not see the reports until they click
> on the Show details, where they again see all the reports. How should I set
> this up? Please help!
> --
> GBM
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200705/1
As far as I know, it does not seem feasible. You might want to create
a custom application that controls the access to the subreports. Sorry
I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant