Force View || Create View Accessing view that has not been created yet?

Hello All.

Does Sql 2005 support forced-later-compliation? Which is to say, can I create a view that access another view which has not been created yet? e.g. "Create Force View Foo" in Oracle.

Thanks,

Steve

You can only do that for stored procedures. This is known and filed in the BOL under "Deferred Name Resolution".

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
sql

Force Encryption

I need to setup SQL encryption on a remote server running W2k3, SQL2k5 std.
The server doesn't have access to a CA. SQL connections are limited to my
server and IIS running on the same remote server.
Can I use a SelfSSL certificate created with the IIS 6.0 Toolkit when
configuring forcing encryption at the network level? If so, are there any
recommendations or pitfalls?
ThanksWhile I've never had to actually do this, yeah, it should be possible. Just
be careful that you setup the domain correctly in the cert and theoretically
you should be fine.
"BigSam" wrote:

> I need to setup SQL encryption on a remote server running W2k3, SQL2k5 std
.
> The server doesn't have access to a CA. SQL connections are limited to my
> server and IIS running on the same remote server.
> Can I use a SelfSSL certificate created with the IIS 6.0 Toolkit when
> configuring forcing encryption at the network level? If so, are there any
> recommendations or pitfalls?
> Thanks

Forbidden access to ReportServer (error 403)

Hi,

I am gradually tearing my hair out and other bits of my anatomy tryong
to get web access to my reportserver.

Currently I can access the rportmanager via a web interface and
configure user access etc etc, at which point the fun begins.

When I try to browse to the URL
http://domain/reportserver?/reachUmobile/Users I get a 403 error,
so....

I check the permissions on the directory and the web directories and
add what I think are the user accounts needed to access these resources
but unfortunately I am at a position where I have added EVERY user
account with FULL control to both the directories AND the report
manager via the web browser.

I cant possibly add any more user/permissions and I have been trying to
find the problem for a day now and getting rather "angry"..

Anyway, apologies if there is not enough info here but if any of you
guys know of any good resources that are available (I have tried
books/web etc) then I would be grateful for any pointers to debugging,
logs etc (I have checked the usual logs and nothing!)

Short of uninstalling the report server I have tried everything, and I
dont really want to go that route as the previous developer didn;t
leave me the report project (.NET). bugger...

Anyway really appreciate some help on this..

Many thanks

Richard

place in the url create and update the settings for all my datasources
via the reportmanager.
2.I have no idea myself, but there are a number of messages about error
403 in microsoft.public.sqlserver.reportingsvcs:

http://groups-beta.google.com/group...arch+this+group

Simon|||Thats a great shout Simon,

I found this comment from your link and hey presto I now have access to
the pages (Still have a data connection problem but I think this
shoulld be easily fixed)

"Quote"
It sounds like your IIS setting may be incorrect. Have you done
anything to
them, outside of switching them to basic? If you click on the
properties
tab for the ReportServer virtual directory, then click the
configuration
button. You should see a mapping for extension * that maps to the
aspnet_isapi.dll. If that mapping is not there then RS will not work.

Many thanks

Richard

Forbid Excel access for users

It seems that people can use Excel to browse the Cube, thats all good and fun. But we do not like for all the users to be able to go through the cube using excel (only a select group). The problem is that i have to create roles for the users to set their security for the report server. So that they can open the reports i've created. But if i'm not mistaking by granting users access to the cube to be able to use my reports I automaticly give them access through Excel ?!

Is there a way to give people access to the cube via report server and block their access for Excel ?

Please help me you experts

Hello! It is possible to block the access from an Excel client by using a dedicated SSAS2005 role for reporting services.

You create a single windows account for SSRS2005 execution of SSAS2005 reports, add that account to a cube role and stop using windows integrated security.

In this way it is only this dedicated SSRS2005 account that have access to the cube.

I assume that you do not use any detailed security in the cubes like dimension restrictions and cell security.

HTH

Thomas Ivarsson

|||

That would be a nice solutions but its not possible in my case because i need to set security for users with restructions as you also posted below, i have like 30 different roles allowed groups of people to access certain dimensions. It would be much easier if there was an option in the roles to allow or disallow Excel, but i guess there is no such thing ? So basicly i'm stuck ?|||

In SQL Server you have application roles but I have never heard about that in SSAS2005.

One way can be to remove the OLEDB for OLAP 9 from each Excel client if your users are on the same LAN.

I can not see any other solution than the ones outside of SSAS2005.

Regards

Thomas Ivarsson

|||

We're dealing with a similar issue. One approach is to define a users dimension with a many-to-many relation to a security measure group. The linkages between the security measure and the various dimension members would allow you to redefine all your security roles as follows:

Everyone who should have direct access to the cube for Excel, ProClarity etc, would have a role attached to his particular username in the user dimension.

THen the Reporting Services reports would be run under the privileged account. The RS queries would have to specify the username perhaps a hidden parameter you can fill from either a data driven subscription or from an extension to the web browser.

Its' complicated. It's not all that efficient (many-to-many dimensions are not wonderful). It requires either a data driven subscription or a custom web viewer control to fill the parameter. But I think it will work.

|||

Darn so there is no "easy" solution

I suggest Microsoft brings out a servicepack 3 with application roles in it

FOR XML performance question

I am currently rewriting a data access component to make use of the FOR XML
SQL statement to return XML data as an ADO stream from a specified source.
The older current component requests this data using an ADO recordset and
then manually converts this to XML.
I have run several performance tests comparing the 2 and on narrow and
medium width tables I have found that the performance gain is massive (appro
x
80% gain). However, when I run the 2 on very wide tables, ones which contai
n
text/ntext columns, FOR XML only performs about 10% better pulling back 1 ro
w
but pulling back 20 rows it becomes over twice as slow as the older componen
t.
Can anyone suggest why? Or even better, any methods/tips that could improve
performance in this instance?
Thanks in advance.I'm cross posting this to microsoft.public.sqlserver.xml, a more appropriate
forum for this question.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:F314E284-6E34-403C-94C1-B307682B1418@.microsoft.com...
>I am currently rewriting a data access component to make use of the FOR XML
> SQL statement to return XML data as an ADO stream from a specified
> source.
> The older current component requests this data using an ADO recordset and
> then manually converts this to XML.
> I have run several performance tests comparing the 2 and on narrow and
> medium width tables I have found that the performance gain is massive
> (approx
> 80% gain). However, when I run the 2 on very wide tables, ones which
> contain
> text/ntext columns, FOR XML only performs about 10% better pulling back 1
> row
> but pulling back 20 rows it becomes over twice as slow as the older
> component.
> Can anyone suggest why? Or even better, any methods/tips that could
> improve
> performance in this instance?
> Thanks in advance.|||I am currently rewriting a data access component to make use of the FOR XML
SQL statement to return XML data as an ADO stream from a specified source.
The older current component requests this data using an ADO recordset and
then manually converts this to XML.
I have run several performance tests comparing the 2 and on narrow and
medium width tables I have found that the performance gain is massive (appro
x
80% gain). However, when I run the 2 on very wide tables, ones which contai
n
text/ntext columns, FOR XML only performs about 10% better pulling back 1 ro
w
but pulling back 20 rows it becomes over twice as slow as the older componen
t.
Can anyone suggest why? Or even better, any methods/tips that could improve
performance in this instance?
Thanks in advance.
----
In addition to the above I have done some further investigation. On a query
which returns the top row from a table the FOR XML method performed 83.5%
faster than the recordset version. However, when I run a where query which
I
know returns a single row the FOR XML method performance plunges and is
actually 6% slower than the recordset version.
Is SQLXML just one of those things which seems like a great idea but has no
real practical use in an enterprise environment? I find it very frustrating
that its performance is superb in some situations but is so awful in others.
Is it a work in progress?
That said, are there any resources which discuss various ways to pull data
from SQL server 2000 as(and convert to) XML format? Surely there is a bette
r
way than using a ADO recordset as described above?
Thanks.|||Hi Lee
This is hard to answer without having more specifics.
How does your FOR XML query look like? How does it compare to the previous
query, what indices do yo have on it?
Etc.
Best regards
Michael
"Lee" <Lee@.discussions.microsoft.com> wrote in message
news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@.microsoft.com...
>I am currently rewriting a data access component to make use of the FOR XML
> SQL statement to return XML data as an ADO stream from a specified
> source.
> The older current component requests this data using an ADO recordset and
> then manually converts this to XML.
> I have run several performance tests comparing the 2 and on narrow and
> medium width tables I have found that the performance gain is massive
> (approx
> 80% gain). However, when I run the 2 on very wide tables, ones which
> contain
> text/ntext columns, FOR XML only performs about 10% better pulling back 1
> row
> but pulling back 20 rows it becomes over twice as slow as the older
> component.
> Can anyone suggest why? Or even better, any methods/tips that could
> improve
> performance in this instance?
> Thanks in advance.
> ----
> In addition to the above I have done some further investigation. On a
> query
> which returns the top row from a table the FOR XML method performed 83.5%
> faster than the recordset version. However, when I run a where query
> which I
> know returns a single row the FOR XML method performance plunges and is
> actually 6% slower than the recordset version.
> Is SQLXML just one of those things which seems like a great idea but has
> no
> real practical use in an enterprise environment? I find it very
> frustrating
> that its performance is superb in some situations but is so awful in
> others.
> Is it a work in progress?
> That said, are there any resources which discuss various ways to pull data
> from SQL server 2000 as(and convert to) XML format? Surely there is a
> better
> way than using a ADO recordset as described above?
> Thanks.|||The query is a simple :-
SELECT stuff
FROM table
WHERE condition (optional)
FOR XML RAW
There is a single index on the primary key of the table and that was the
field I did my where clause on as described in my above posts.
"Michael Rys [MSFT]" wrote:

> Hi Lee
> This is hard to answer without having more specifics.
> How does your FOR XML query look like? How does it compare to the previous
> query, what indices do yo have on it?
> Etc.
> Best regards
> Michael
> "Lee" <Lee@.discussions.microsoft.com> wrote in message
> news:95F97E2B-CCE3-415F-AEBE-25E7B499825E@.microsoft.com...
>
>

For Remus - Access to the remote server is denied because the current security context is not tr

Remus (or anyone who can help) -

I am sorry for posting this question here. The only reason I am doing so is because Remus answered a question similar to this late last year, but I couldn't get access to the whole thread.

In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:

a.. The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password.
b.. The remote database is set to Trustworthy.
c.. I have tried every combination of WITH Execute As on the remote stored procedure but nothing works.
d.. I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.

Thank you for your help on this - Amos.

Do you still have this problem? I was in vacantion and could not respond to forum questions.

You have to set the local database as trustworthy and the dbo of the local database must correspond to a login that has the 'authenticate server' priviledge.

HTH,
~ Remus

For Remus - Access to the remote server is denied because the current security context is not tr

Remus (or anyone who can help) -

I am sorry for posting this question here. The only reason I am doing so is because Remus answered a question similar to this late last year, but I couldn't get access to the whole thread.

In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:

a.. The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password.
b.. The remote database is set to Trustworthy.
c.. I have tried every combination of WITH Execute As on the remote stored procedure but nothing works.
d.. I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.

Thank you for your help on this - Amos.

Do you still have this problem? I was in vacantion and could not respond to forum questions.

You have to set the local database as trustworthy and the dbo of the local database must correspond to a login that has the 'authenticate server' priviledge.

HTH,
~ Remus