HOME USER AND SQL

I am a simple home user, I do not develop nor do I extract information for
analysis or whatever - do I still need SQL? Can I just remove SQL?
On Sat, 4 Aug 2007 02:54:01 -0700, Denis Arial wrote:

>I am a simple home user, I do not develop nor do I extract information for
>analysis or whatever - do I still need SQL? Can I just remove SQL?
Hi Denis,
If you installed it yourself to have a look and decided not to pursue it
any further, it's probably safe to uninstall. However, if SQL was
installed as part of another program, that other program probably uses
it, and might sstop working if you decide to uninstall SQL Server.
What you can do is stop the SQL Server service (if it is currently
active) and change the startup settings to not start on system startup.
That way, programs using SQL Server will either fail, or (if they are
able to) start the service themselves. The former is very easy to
notice; the second can also be noted if you regularly check what
services are running.
Use the PC for some time, making sure to use all your relevant programs
(including those you use only once a month, and make sure to use all
functions in a program you typically need). If you got no errors and the
SQL Server service was not started, it's safe to assume that none of
your programs uses it, so you can proceed to uninstall.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Home page has no tabs

Using System Role Assignment I grant a domain user account "System Administrator" & "System User" within Reporting Services.

Then I connect via IE7 to Report Server with the domain account that has been granted the above rights. The page comes up but the "Contents" and "Properties" tabs are missing. This user can use "Site Settings" and perform admin though.

If I assign the users domain account to the local Administrator group on the Win 2003 Server, then when they reconnect to Reporting Services the missing tabs appear.

We are a development shop, need some of our qa testers and developers to have admin rights within Reporting Services but don't want to grant them local Administrator rights on the server. This could also be an issue upon deployment at customer sites.

So how to get round this. I have been trying ACL's on the Reporting Services directorys, ASP.Net security etc but with no success.

PS: Everything was sweet before SP2

OK found the answer. Use "MS SQL Server Management Studio" and connect to the Reporting Services instance.

Then on "Home" right mouse and select properties. Under permissions you have to add the windows users that are to have access.

So domain\uid, and tick the box's for the access that person needs.

Ho to run Enterprise Manager on another PC

Hi,
I want to let another user access Enterprise Manager from his PC. How do I
do this? I tried to setup MMC but there is no SQL snap-in to add.
Thanks a lot, Alex.You need to install the client tools from the SQLServer CD. You are entitled
to run the client tools on any device that is licensed with a CAL or any
device that connects to a processor-licensed server.
--
David Portas
--
Please reply only to the newsgroup
--sql

Highlight row

Hi,

After the report is rendered in the report viewer control (In windows forms application), I want to provide user with a facility to highlight rows (data). Is it possible with SSRS/Report viewer?

For example, amongst the list of 100 invoices, user should be able to highlight 10 invoices.

Thanks in advance.

Paresh

Not real easily. The only approach I can think of is to have a multi-value parameter on the report which contains invoice numbers. The user would need to select invoice report parameter values equal to the invoice numbers to highlight, then rerun the report checking to see if the row matched the selected report parameter values.

In other words, not real easily.

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"...
>.
>

High %privileged and interrupt time

Had a high CPU on my SQL Servers and on further investigation, it appears
that the % privileged and interrupt time were high and not user time.
What should I account for such behavior especially if it occurs on many SQL
servers at the same time ?
Cannot find anything odd in the event or SQL Logs.Hi
"Hassan" wrote:

> Had a high CPU on my SQL Servers and on further investigation, it appears
> that the % privileged and interrupt time were high and not user time.
> What should I account for such behavior especially if it occurs on many SQ
L
> servers at the same time ?
> Cannot find anything odd in the event or SQL Logs.
You can also look at interupts/sec. If you are getting excessive number of
interupts it may be a disc issue so also look at Physical Disk counters.
John

High %privileged and interrupt time

Had a high CPU on my SQL Servers and on further investigation, it appears
that the % privileged and interrupt time were high and not user time.
What should I account for such behavior especially if it occurs on many SQL
servers at the same time ?
Cannot find anything odd in the event or SQL Logs.
Hi
"Hassan" wrote:

> Had a high CPU on my SQL Servers and on further investigation, it appears
> that the % privileged and interrupt time were high and not user time.
> What should I account for such behavior especially if it occurs on many SQL
> servers at the same time ?
> Cannot find anything odd in the event or SQL Logs.
You can also look at interupts/sec. If you are getting excessive number of
interupts it may be a disc issue so also look at Physical Disk counters.
John

hierarchy in SSRS 2005?

is it possible to use a hierarchy (e.g: CountryCode, PostCode, CityName) as basis for a report parameter? I would like the user to have the possibility to drill down from e.g.: "Country = 'Austria' - PostCode '1010' - CityName 'Vienna'" and after that view the report with the selected parameters.

possible?

yes - no - maybe?

regards

pamike

Don't think so... At least using the standart issues!|||

is it hten possible to build the hierarchie in an Analysis Services Olap Cube and use parameters to pass througt to a predefined report? I also tried this but without any success;-(

thx

pamike

|||

Can't answer that... i'm not very experienced in Analisys Services...

Sorry...

|||

Are you looking to do this with one parameter or can it use multiple parameters? You can set up cascading parameters to do something like this. For example,

Parameter1 Datasource => SELECT countryid, country FROMCountryTable;

Parameter2 Datasource => SELECT stateid, state FROM StateTable WHERE countryid = Paramenters!Parameter1.Value;

Parameter3 Datasource => SELECT cityid, city FROM CityTable WHERE stateid = Paramenters!Parameter2.Value;

I am not sure if you can change the visibilty of each parameter after you select it (or if you need to).

R

|||

Small error. Those should read

Parameter1 Datasource => SELECT countryid, country FROM CountryTable;

Parameter2 Datasource => SELECT stateid, state FROM StateTable WHERE countryid = @.Parameter1 ;

Parameter3 Datasource => SELECT cityid, city FROM CityTable WHERE stateid = @.Parameter2;

R

|||

This is very possible using cascading parameters...

Parameter 1 (country code):

SELECT CountryCode FROM AccountGeography

Use something like the above as the dataset for the first parameter.

Multi value Parameter 2 (postal code):

SELECT PostalCode FROM AccountGeography WHERE CountryCode = @.CountryCode

Assuming you named the first parameter "CountryCode", the above statement will generate a list of postal codes that correspond to the CountryCode value selected in the first parameter.If the first parameter is a multi-valued parameter where you can select more than one CountryCode at a time then it gets a little trickier.To avoid brain numbing dynamic SQL you can use a user defined function to pass the comma delimited list of values from the CountryCode parameter to an IN statement in the child parameters.Example:

SELECT PostalCode FROM AccountGeography WHERE CountryCode IN (SELECT Param FROM ufn_MVParam(@.CountryCode,','))

Multi value Parameter 3 (city name):

SELECT CityName FROM AccountGeography WHERE PostalCode IN (SELECT Param FROM ufn_MVParam(@.PostalCode,','))

The third parameter is the same as the second except for that where parameter 2 uses the value of parameter 1 to filter its values, parameter 3 uses the value of parameter 2.

...Don't forget that when you're using reporting services parameters in your data set queries, the parameter name is case sensitive! WHERE CountryCode = @.CountryCode is not the same as WHERE CountryCode = @.Countrycode.

...SSRS will take care of generating the dynamic SQL for you!I'm not sure if that statement is technically correct, but it's at least operationally true.You will understand how wonderful this is if you have used SSRS 2000 x_x

...Here is the code to create the function which pareses the comma delimited string.I got this on the web somewhere but can't remember where:

CREATE FUNCTION [dbo].[ufn_MVParam](@.RepParam nvarchar(4000), @.Delim char(1)= ',')

RETURNS @.VALUES TABLE (Param nvarchar(4000))AS

BEGIN

DECLARE @.chrind INT

DECLARE @.Piece nvarchar(4000)

SELECT @.chrind = 1

WHILE @.chrind > 0

BEGIN

SELECT @.chrind = CHARINDEX(@.Delim,@.RepParam)

IF @.chrind > 0

SELECT @.Piece = LEFT(@.RepParam,@.chrind - 1)

ELSE

SELECT @.Piece = @.RepParam

INSERT @.VALUES(Param) VALUES(@.Piece)

SELECT @.RepParam = RIGHT(@.RepParam,LEN(@.RepParam) - @.chrind)

IF LEN(@.RepParam) = 0 BREAK

END

RETURN

END

Good luck!!

|||

My bad... you only need that crazy text parsing function when trying to pass multi value parameters to a stored procedure. If your result set is not generated using a stored procedure, all you need is the SSRS parameter name like this:

SELECT CityName FROM AccountGeography WHERE PostalCode IN (@.PostalCode)

with SSRS 2005 you would only need the additional user function when using a stored procedure as the data set in your report... and in that situation you wouldn't call the function from within SSRS, you would call it from within the sproc itself because SSRS passes the multi value parameter to SQL server as a comma delimted value string ("CountryCode1,CountryCode2,CountryCode3" etc.)

Hierarchy - Finding loops in the data

I've spent hours trying to find the answer to this with no luck. One
of our systems tracks connections between objects. If a user enters an
object that is connected to to another object that is connect to its
parent object, we have problems.
We have many diffent type of objects, so I will try to give a simple
example. If we have a meter "Meter A" that is connected to a tank
"Tank A" and that tank is connected to another meter "Meter B" which is
connected to "Tank B". The problem arises when "Tank B" is connected
to "Meter A"
Meter A <-> Tank A <-> MeterB <--> Tank B <-//-> Meter A
As you can see, there is a "loop" in connectivity. The data is store
as hierarchial data as upstream and downstream attributes. Using
hierarchial queries, I've been able to retrieve the data in a
hierarachial representation, but my goal is to determine if there is a
loop in the data. Just to make it clear, its the same as the
Manager/Employee example seen everywhere except the idea is to create a
process to ensure that no manager reports to his employee.
A sample dataset is provide for your much appreciated assistance.
--DST = DownStreamType
--DID = DownStreamID
--UST = UpstreamType
--UID = UpstreamID
DROP TABLE #Conn
CREATE TABLE #Conn (DST int, DSID int, UST int, UPID int)
--Dataset connects with no loop
INSERT INTO #Conn VALUES (1,1,1,2)
INSERT INTO #Conn VALUES (1,2,1,3)
INSERT INTO #Conn VALUES (1,3,2,1)
INSERT INTO #Conn VALUES (2,1,2,2)
INSERT INTO #Conn VALUES (2,2,2,3)
INSERT INTO #Conn VALUES (2,3,3,1)
--Dataset loops (each record is connected to each other, last
--record connects back to first record.
INSERT INTO #Conn VALUES (1,10,1,20)
INSERT INTO #Conn VALUES (1,20,1,30)
INSERT INTO #Conn VALUES (1,30,2,10)
INSERT INTO #Conn VALUES (2,10,2,20)
INSERT INTO #Conn VALUES (2,20,2,30)
INSERT INTO #Conn VALUES (2,30,1,10)
TIAIf you search the web for sql+cycle+detection+graphs, you'll find some
things
that should help.
Your hierarchy representation is a bit different than most, since you
don't store the nodes anywhere, just the edges, so a "dangling" edge
is harder to spot (usually it would have NULL as the parent or child).
Here's one easy-to-implement solution that might work, though it will
probably be less efficient than other solutions for large data sets.
I also haven't taken the time to verify that it's sound, but I think it is:
repeatedly remove dead-end links from the network/table. If you end
up with nothing, there were no loops. If you end up with a sub-network
having no dead ends, there must be a loop involving the remaining
links.
-- start with all non-dead-end links
select DST, DSID, UST, UPID
into #remnants
from #Conn as C1
where exists (
select * from #Conn C2
where C2.UST = C1.DST
and C2.UPID = C1.DSID
)
while @.@.rowcount <> 0
delete from #remnants
where not exists (
select * from #remnants as R2
where R2.UST = #remnants.DST
and R2.UPID = #remnants.DSID
)
select * from #remnants
go
-- Steve Kass
-- Drew University
-- 0D2B1B93-2E53-448C-B441-61B1C5EF1F40
carmaboy@.gmail.com wrote:

>I've spent hours trying to find the answer to this with no luck. One
>of our systems tracks connections between objects. If a user enters an
>object that is connected to to another object that is connect to its
>parent object, we have problems.
>We have many diffent type of objects, so I will try to give a simple
>example. If we have a meter "Meter A" that is connected to a tank
>"Tank A" and that tank is connected to another meter "Meter B" which is
>connected to "Tank B". The problem arises when "Tank B" is connected
>to "Meter A"
>Meter A <-> Tank A <-> MeterB <--> Tank B <-//-> Meter A
>As you can see, there is a "loop" in connectivity. The data is store
>as hierarchial data as upstream and downstream attributes. Using
>hierarchial queries, I've been able to retrieve the data in a
>hierarachial representation, but my goal is to determine if there is a
>loop in the data. Just to make it clear, its the same as the
>Manager/Employee example seen everywhere except the idea is to create a
>process to ensure that no manager reports to his employee.
>A sample dataset is provide for your much appreciated assistance.
>--DST = DownStreamType
>--DID = DownStreamID
>--UST = UpstreamType
>--UID = UpstreamID
>DROP TABLE #Conn
>CREATE TABLE #Conn (DST int, DSID int, UST int, UPID int)
>--Dataset connects with no loop
>INSERT INTO #Conn VALUES (1,1,1,2)
>INSERT INTO #Conn VALUES (1,2,1,3)
>INSERT INTO #Conn VALUES (1,3,2,1)
>INSERT INTO #Conn VALUES (2,1,2,2)
>INSERT INTO #Conn VALUES (2,2,2,3)
>INSERT INTO #Conn VALUES (2,3,3,1)
>--Dataset loops (each record is connected to each other, last
>--record connects back to first record.
>INSERT INTO #Conn VALUES (1,10,1,20)
>INSERT INTO #Conn VALUES (1,20,1,30)
>INSERT INTO #Conn VALUES (1,30,2,10)
>INSERT INTO #Conn VALUES (2,10,2,20)
>INSERT INTO #Conn VALUES (2,20,2,30)
>INSERT INTO #Conn VALUES (2,30,1,10)
>TIA
>
>|||Hi there,
Right now I have to leave for home. Will post u a solution by tomorrow
evening GMT. I have worked a lot with hierarchical tables, u surely will
enjoy the solution. bye
"carmaboy@.gmail.com" wrote:

> I've spent hours trying to find the answer to this with no luck. One
> of our systems tracks connections between objects. If a user enters an
> object that is connected to to another object that is connect to its
> parent object, we have problems.
> We have many diffent type of objects, so I will try to give a simple
> example. If we have a meter "Meter A" that is connected to a tank
> "Tank A" and that tank is connected to another meter "Meter B" which is
> connected to "Tank B". The problem arises when "Tank B" is connected
> to "Meter A"
> Meter A <-> Tank A <-> MeterB <--> Tank B <-//-> Meter A
> As you can see, there is a "loop" in connectivity. The data is store
> as hierarchial data as upstream and downstream attributes. Using
> hierarchial queries, I've been able to retrieve the data in a
> hierarachial representation, but my goal is to determine if there is a
> loop in the data. Just to make it clear, its the same as the
> Manager/Employee example seen everywhere except the idea is to create a
> process to ensure that no manager reports to his employee.
> A sample dataset is provide for your much appreciated assistance.
> --DST = DownStreamType
> --DID = DownStreamID
> --UST = UpstreamType
> --UID = UpstreamID
> DROP TABLE #Conn
> CREATE TABLE #Conn (DST int, DSID int, UST int, UPID int)
> --Dataset connects with no loop
> INSERT INTO #Conn VALUES (1,1,1,2)
> INSERT INTO #Conn VALUES (1,2,1,3)
> INSERT INTO #Conn VALUES (1,3,2,1)
> INSERT INTO #Conn VALUES (2,1,2,2)
> INSERT INTO #Conn VALUES (2,2,2,3)
> INSERT INTO #Conn VALUES (2,3,3,1)
> --Dataset loops (each record is connected to each other, last
> --record connects back to first record.
> INSERT INTO #Conn VALUES (1,10,1,20)
> INSERT INTO #Conn VALUES (1,20,1,30)
> INSERT INTO #Conn VALUES (1,30,2,10)
> INSERT INTO #Conn VALUES (2,10,2,20)
> INSERT INTO #Conn VALUES (2,20,2,30)
> INSERT INTO #Conn VALUES (2,30,1,10)
> TIA
>|||Thanks GetGoing, I look forward to your answer.
Steve, Thanks for the prompt response. Howerver, I think I did not
make myself clear. I tried your solution and the results were not what
I was looking for. The first dataset sample shows that there is a set
of object connected to each other linearly (if you will). The second
set is circular with object (1,10 - first record) as the downstream
object and again (1,10 - last record) as the upstream object. My goal
is to identify either that (1,10) is causing a loop or simply identify
that there is a loop and where. If I can state this in a different way
using the Emp/Man example, hopefully it would help. If you have 5
employees, each one reporting to the next. (Mary reports to Greg who
reports to Tom who reports to Frank who reports to Sam). I'm looking
for errors that would show (Mary reports to Greg who reports to Tom who
reports to Frank who reports to Sam who reports to Greg). Since Sam is
Greg boss 4 levels down, it incorrect/impossible that Sam should report
to Greg. The reason I need this is that some processing occurs based
on this "linear" flow. If there is a looping of the data, the process
gets stuck rather then ends. Thanks again.|||Based on the illustrations in your post I think I see the source of the
problems. The hierarchical chain is missing one vital element: context.
You say that "Meter A" is connected to "Tank A" which in turn is connected
to "MeterB" which connects to "Tank B" which then connects to "Meter A". All
these connections cannot be in the same context. If "Meter A" and "Meter B"
are gauges, then the context could be described as "Pressure", and if "Tank
A" and "Tank B" are two interconnected tanks (containers), then this is a
different context - let's call it "Containment".
If context is respected, then - logically - the situation really is:
1) the "Pressure" context:
MeterA
<-- Tank A
<-- Tank B
Meter B
<-- Tank A
<-- Tank B
2) the "Containment" context:
Tank A
<-- Tank B
or
Tank B
<-- Tank A
or even
CombinedTanks
<-- Tank A
<-- Tank B
"Mixing apples and pears makes cocktails not a trees."
-- Confucius
Maybe you should do some more reading on trees and hierarchies.
ML|||I appreciate the critique ML. Actually, what your describing is one
aspect of its possible context. What these meters and tanks are doing
is measuring liquids, specifically oil and gas. As oil/gas is pumped
from the ground, its piped to a tank. Howerver, the meter is used to
measure the amount of o&g from a specific point in the ground at
specific time intervals where as the tanks are used to measure the
amount of o&g from the ground from many points (i.e meters). My
example is a simple one, but to illustrate its complexity, here is a
real sincerio. From a well, oil and/or gas is produced which flows
though a pipepine to a tank. Meters are attached to each well to
measure the amount of product is retreived. Many wells can be attached
to a single meter as well as a meter can be attached to other meters.
The pipeline continues to the tank where it stores the o&g and is used
to measure how much has been retreived over all. Many tanks can be
attached to each other as well as the tank can be attached to another
meter before going to another tank. The direction of the production
demonstrates the use of downstream and upstream as an object (Meter,
Well, Tank, Equipment). Each item can be a downstream object or
upstream object depending on how they are connected. I hope this
clears things up a little. My appologies if you are in the O&G
industry and already know this. My intention is to make my request
clear. Thanks.|||I'll assume that the graph you represent is a digraph where the relationship
points from the downstream item to the upstream one (d-->u).
The function I provided (not thoroughly tested with this particular
implementation) traverses the graph starting with an input root, and
constructs a path for each node made of all nodes leading to the current
node.
For example, if you start traversing the graph from node 1-1, when you get
to node 3-1, the path will be:
'.1-1.1-2.1-3.2-1.2-2.2-3.3-1.'
Once you have this path available, detecting a cycle is simple--if the
source node's path already contains the target node id, you have a cycle.
Here's an implementation with a UDF with a couple of tests against your
sample data. I really didn't test it thoroughly, so I hope I didn't confuse
the references to dst, dsid, ust, usid.
-- ddl and sample data
create table conn (dst int, dsid int, ust int, usid int);
-- no cycle
insert into conn values (1,1,1,2);
insert into conn values (1,2,1,3);
insert into conn values (1,3,2,1);
insert into conn values (2,1,2,2);
insert into conn values (2,2,2,3);
insert into conn values (2,3,3,1);
-- cycle
insert into conn values (1,10,1,20);
insert into conn values (1,20,1,30);
insert into conn values (1,30,2,10);
insert into conn values (2,10,2,20);
insert into conn values (2,20,2,30);
insert into conn values (2,30,1,10);
go
create function fn_getsubgraph(@.rootdst as int, @.rootdsid as int)
returns @.subgraph table
(
dst int not null,
dsid int not null,
ust int not null,
usid int not null,
lvl int not null,
path varchar(900) not null,
cycle int not null
)
as
begin
declare @.lvl as int;
set @.lvl = 1;
insert into @.subgraph
select dst, dsid, ust, usid, @.lvl,
-- constract path of nodes leading to current node
-- in the form '.t1-id1.t2-id2. ... .tn-idn.'
'.' + cast(dst as varchar(10)) + '-'
+ cast(dsid as varchar(10)) + '.'
+ cast(ust as varchar(10)) + '-'
+ cast(usid as varchar(10)) + '.' as path,
case when dst = ust and dsid = usid then 1 else 0 end as cycle
from conn
where dst = @.rootdst and dsid = @.rootdsid;
while @.@.rowcount > 0
begin
set @.lvl = @.lvl + 1;
-- insert into @.subgraph next level
insert into @.subgraph
select u.dst, u.dsid, u.ust, u.usid, @.lvl,
d.path
+ cast(u.ust as varchar(10)) + '-'
+ cast(u.usid as varchar(10)) + '.' as path,
-- cycle is detected when source path contains target node
case when d.path like '%.' +
cast(u.ust as varchar(10)) + '-'
+ cast(u.usid as varchar(10)) + '.%'
then 1 else 0 end
from @.subgraph as d
join conn as u
on d.lvl = @.lvl - 1 -- filter prev level only
and u.dst = d.ust and u.dsid = d.usid
and d.cycle = 0; -- don't pursue cyclic paths
end
return;
end
go
select * from fn_getsubgraph(1, 1);
dst dsid ust usid lvl path cycle
-- -- -- -- -- -- --
1 1 1 2 1 .1-1.1-2. 0
1 2 1 3 2 .1-1.1-2.1-3. 0
1 3 2 1 3 .1-1.1-2.1-3.2-1. 0
2 1 2 2 4 .1-1.1-2.1-3.2-1.2-2. 0
2 2 2 3 5 .1-1.1-2.1-3.2-1.2-2.2-3. 0
2 3 3 1 6 .1-1.1-2.1-3.2-1.2-2.2-3.3-1. 0
select * from fn_getsubgraph(1, 10);
dst dsid ust usid lvl path cycle
-- -- -- -- -- -- --
1 10 1 20 1 .1-10.1-20. 0
1 20 1 30 2 .1-10.1-20.1-30. 0
1 30 2 10 3 .1-10.1-20.1-30.2-10. 0
2 10 2 20 4 .1-10.1-20.1-30.2-10.2-20. 0
2 20 2 30 5 .1-10.1-20.1-30.2-10.2-20.2-30. 0
2 30 1 10 6 .1-10.1-20.1-30.2-10.2-20.2-30.1-10. 1
BG, SQL Server MVP
www.SolidQualityLearning.com
<carmaboy@.gmail.com> wrote in message
news:1123166940.315534.214460@.g44g2000cwa.googlegroups.com...
> I've spent hours trying to find the answer to this with no luck. One
> of our systems tracks connections between objects. If a user enters an
> object that is connected to to another object that is connect to its
> parent object, we have problems.
> We have many diffent type of objects, so I will try to give a simple
> example. If we have a meter "Meter A" that is connected to a tank
> "Tank A" and that tank is connected to another meter "Meter B" which is
> connected to "Tank B". The problem arises when "Tank B" is connected
> to "Meter A"
> Meter A <-> Tank A <-> MeterB <--> Tank B <-//-> Meter A
> As you can see, there is a "loop" in connectivity. The data is store
> as hierarchial data as upstream and downstream attributes. Using
> hierarchial queries, I've been able to retrieve the data in a
> hierarachial representation, but my goal is to determine if there is a
> loop in the data. Just to make it clear, its the same as the
> Manager/Employee example seen everywhere except the idea is to create a
> process to ensure that no manager reports to his employee.
> A sample dataset is provide for your much appreciated assistance.
> --DST = DownStreamType
> --DID = DownStreamID
> --UST = UpstreamType
> --UID = UpstreamID
> DROP TABLE #Conn
> CREATE TABLE #Conn (DST int, DSID int, UST int, UPID int)
> --Dataset connects with no loop
> INSERT INTO #Conn VALUES (1,1,1,2)
> INSERT INTO #Conn VALUES (1,2,1,3)
> INSERT INTO #Conn VALUES (1,3,2,1)
> INSERT INTO #Conn VALUES (2,1,2,2)
> INSERT INTO #Conn VALUES (2,2,2,3)
> INSERT INTO #Conn VALUES (2,3,3,1)
> --Dataset loops (each record is connected to each other, last
> --record connects back to first record.
> INSERT INTO #Conn VALUES (1,10,1,20)
> INSERT INTO #Conn VALUES (1,20,1,30)
> INSERT INTO #Conn VALUES (1,30,2,10)
> INSERT INTO #Conn VALUES (2,10,2,20)
> INSERT INTO #Conn VALUES (2,20,2,30)
> INSERT INTO #Conn VALUES (2,30,1,10)
> TIA
>|||There are many different ways to present the results of
loop detection. If what I provided is not "what [you were]
looking for", can you post the result set you want?
My code gives this result:
DST DSID UST UPID
-- -- -- --
1 10 1 20
1 20 1 30
1 30 2 10
2 10 2 20
2 20 2 30
2 30 1 10
You say your goal is "is to identify either that (1,10)
is causing a loop or simply identify that there is a loop
and where."
I've given you a list of connections that participate in a loop. It
doesn't make sense to say that (1,10) is the specific cause, since
nothing distinguishes (1,10) from any of the other nodes in the
loop. Loops have no beginning or end, and one could just as well
say that there is a loop that starts at (2,20) and ends at (2,20),
where the loop is
(2, 20); (2,30); (1,10); (1,20); (1,30); (2,10); (2,20)
If you want to know "where" the loop is, how would such a
question be answered, if not by listing the parts of the loop,
since loops have no distinguished location beyond their being
a set of nodes and edges.
You can list the nodes instead of the edges using my example
with this final query:
select DST as T, DSID as ID
from #remnants
union
select UST, UPID
from #remnants
The result would then be
T ID
-- --
1 10
1 20
1 30
2 10
2 20
2 30
Is that any better? It doesn't give as much information about
how the loop connects these nodes, but it is a list of nodes instead
of a list of connections.
SK
carmaboy@.gmail.com wrote:

>Thanks GetGoing, I look forward to your answer.
>Steve, Thanks for the prompt response. Howerver, I think I did not
>make myself clear. I tried your solution and the results were not what
>I was looking for. The first dataset sample shows that there is a set
>of object connected to each other linearly (if you will). The second
>set is circular with object (1,10 - first record) as the downstream
>object and again (1,10 - last record) as the upstream object. My goal
>is to identify either that (1,10) is causing a loop or simply identify
>that there is a loop and where. If I can state this in a different way
>using the Emp/Man example, hopefully it would help. If you have 5
>employees, each one reporting to the next. (Mary reports to Greg who
>reports to Tom who reports to Frank who reports to Sam). I'm looking
>for errors that would show (Mary reports to Greg who reports to Tom who
>reports to Frank who reports to Sam who reports to Greg). Since Sam is
>Greg boss 4 levels down, it incorrect/impossible that Sam should report
>to Greg. The reason I need this is that some processing occurs based
>on this "linear" flow. If there is a looping of the data, the process
>gets stuck rather then ends. Thanks again.
>
>|||Thanks very much for everyones posts. I think I am well on my way to
getting my results.
SK, thanks again. You make a great point about what creates the loop
and how to identify it. I didn't not include that sincerio in my
thought, but have now. Your help is greatly appeciated.|||As far as I can see your model is based on a doubly-linked list. The positiv
e
thing about it is the fact that it's fairly easy to find connected items, ye
t
it can lead to circular references.
I'd suggest designing the model based on a singly-linked list. This way
circular references can be avoided quite easily.
Maybe something like this:
EquipmentPiece : EquipmentInstance : ConnectedToInstance : Direction :
ConnectionType
If redesigning is out of the question, I believe Itzik has a solution that
will help you detect circular references.
ML

Hierarchical parameters

Hi all,
does anyone know if it is possible within sql server 2000 to create
dependent parameters ? Eg : i have one parameter "City". If a user selects a
ceratin city, i would want to see the other parameter "Street" being filled
with alle the available streets...
I thought i read somewhere that this is possible in sql server 2005, but I'm
not quite sure...
Thanks!Yes you can have cascading parameters... TO do this
1. Create a dataset which populates the second parameter...ie
select Streetname, streetid from mytable where City = @.city
Where city is the name of your city parameter...
2. Then in the Report Parameters window create the Street parameter, and
have it use the new dataset
3. Ensure the Street parameter comes AFTER the CITY parameter in the
parameter list...
Then you can use both City and Streetname in your dataset query to populate
the report...
Hope this helps
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Koen" wrote:
> Hi all,
> does anyone know if it is possible within sql server 2000 to create
> dependent parameters ? Eg : i have one parameter "City". If a user selects a
> ceratin city, i would want to see the other parameter "Street" being filled
> with alle the available streets...
> I thought i read somewhere that this is possible in sql server 2005, but I'm
> not quite sure...
> Thanks!|||Thanks Wayne,
gonna try it out later this day...
Koen
"Wayne Snyder" wrote:
> Yes you can have cascading parameters... TO do this
> 1. Create a dataset which populates the second parameter...ie
> select Streetname, streetid from mytable where City = @.city
> Where city is the name of your city parameter...
> 2. Then in the Report Parameters window create the Street parameter, and
> have it use the new dataset
> 3. Ensure the Street parameter comes AFTER the CITY parameter in the
> parameter list...
> Then you can use both City and Streetname in your dataset query to populate
> the report...
> Hope this helps
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Koen" wrote:
> > Hi all,
> >
> > does anyone know if it is possible within sql server 2000 to create
> > dependent parameters ? Eg : i have one parameter "City". If a user selects a
> > ceratin city, i would want to see the other parameter "Street" being filled
> > with alle the available streets...
> >
> > I thought i read somewhere that this is possible in sql server 2005, but I'm
> > not quite sure...
> >
> > Thanks!

Hiding URL from User

I have a web application written in ASP, not ASP.Net. We set up a page with an iframe that we populate the SSRS reports into. The url of the report is visible through a right click/properties. Is there a way to either disable the right click or hide the url?

you can disable the right click by adding oncontextmenu="return false" in the body tag:

<body oncontextmenu="return false">

Hiding the report parameters when i click on the View report button

hi All,
i am working in SQL reporting services 2005, i have requirement that i
need to show all the report parameters in the report layout and user
can enter or select the values when i click on the view report button,
the report parameters should be hided. i am not sure that we could hide
the report parameters when i click on the view report button.
anyone knows how to implement this. please let me know.
Thank you in advance.
VinodAny idea on the below issue!!!!
thanks
Vinod
Vinod wrote:
> hi All,
>
> i am working in SQL reporting services 2005, i have requirement that i
> need to show all the report parameters in the report layout and user
> can enter or select the values when i click on the view report button,
> the report parameters should be hided. i am not sure that we could hide
> the report parameters when i click on the view report button.
> anyone knows how to implement this. please let me know.
> Thank you in advance.
> Vinod

Hiding the Database list from a certain user\group

Is there a way to limit what user will see in Enterprise Manager? I
would like them to only see there db's and not all them.On Jun 18, 12:59 pm, OVDaddy <d...@.wemhoff.comwrote:

Quote:

Originally Posted by

Is there a way to limit what user will see in Enterprise Manager? I
would like them to only see there db's and not all them.

I have read and see that I am not able to do this.|||OVDaddy (dan@.wemhoff.com) writes:

Quote:

Originally Posted by

On Jun 18, 12:59 pm, OVDaddy <d...@.wemhoff.comwrote:

Quote:

Originally Posted by

>Is there a way to limit what user will see in Enterprise Manager? I
>would like them to only see there db's and not all them.

>
I have read and see that I am not able to do this.

I believe that in EM, you will see only the databases you have access to.

In SQL Server Management Studio that comes with SQL 2005, it's a different
story. Here you cannot hide databases.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||By default users see all databases in Enterprise Manager. The following KB
shows a method that you can use so users can see only databases that they
have access to (the title of the article is a bit off topic but in fact it
does show a good method):
http://support.microsoft.com/default.aspx/kb/889696
For SQL Server Management Studio this is much easier, you can just deny a
login the VIEW ANY DATABASE permission and that will do it.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Plamen Ratchev (Plamen@.SQLStudio.com) writes:

Quote:

Originally Posted by

For SQL Server Management Studio this is much easier, you can just deny a
login the VIEW ANY DATABASE permission and that will do it.

Arguably. The users will only see the databases they *own*. They could
have access to other databases, but they will not show up.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9954ECC668758Yazorman@.127.0.0.1...

Quote:

Originally Posted by

>
Arguably. The users will only see the databases they *own*. They could
have access to other databases, but they will not show up.
>

Yes, but my understanding of the original post is that it asked for
configuration where users can see only their own databases, so this applies
here. This is what I meant. :)

Plamen Ratchev
http://www.SQLStudio.com

Hiding System Tables when connecting with non-microsoft clients

I have a user who would like to connect to a SQL Server database using SAS (a
statistical application) and have the system tables not be displayed
Currently, when the user connects they are shown all of the tables
(including system table). We have inquired on the SAS side and have been
told that it is something that must be on the database side.
Do I need to remove some default permissions? Or send a special connection
string? I know in Ent. Manager, you just clear the show system tables
checkbox - but there is not an option like that when connecting with SAS.
No, it is not something that you can do in the database. You'll have to get
your SAS guys to change their query that retreives the table list. For
example, the following query returns system and user tables, where as the
second one doesn't:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
--AND OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
'isMSShipped') = 0
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
'isMSShipped') = 0
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Mike" <Mics_79@.online.nospam> wrote in message
news:AD6C6A91-7912-429F-90BA-7169CA29B641@.microsoft.com...
I have a user who would like to connect to a SQL Server database using SAS
(a
statistical application) and have the system tables not be displayed
Currently, when the user connects they are shown all of the tables
(including system table). We have inquired on the SAS side and have been
told that it is something that must be on the database side.
Do I need to remove some default permissions? Or send a special connection
string? I know in Ent. Manager, you just clear the show system tables
checkbox - but there is not an option like that when connecting with SAS.

Hiding SQL Server Reporting Services Header

we published some reports and want to give user access to these via report
manager UI to Certain Folders.
Is it possible to hide the header which display SQL Server Reporting
Services, Home, My Subscriptions etc.?
thxHave you tried simply using the ReportServer URL? //SERVERNAME/ReportServer/
Let me know if this helps...
-David.
"nkg" wrote:
> we published some reports and want to give user access to these via report
> manager UI to Certain Folders.
> Is it possible to hide the header which display SQL Server Reporting
> Services, Home, My Subscriptions etc.?
> thx
>
>

Hiding Report Parameter UI - Still Using Parameters for Dynamic Column Sorting

Hello!
We are using a SortBy parameter and a Direction parameter for our report.
We are using dynamic sorting to allow the user to click on a column header
and sort that column. A click checks whether or not it is sorted asc or
desc and if it is, it changes the sorting to the direction it isn't
currently sorting. This functionality works perfect, but we do not want to
allow the users to sort using the parameter ui drop-down boxes. If we
removing the promt from the report designer it gives an error saying that
the parameter is read only and the report errors out. If we try to uncheck
the prompt box in report manager, it gives the same errors. We want to use
these parameters for sorting within the report, but we do not wish to have
the UI there as dropdowns. Is there a way to correct this? We appreciate
your help!UI seems to be confusing. You could try to use my script:
http://blogs.msdn.com/levs/archive/2004/07/20.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"jrennard" <jrennard3@.schneidercorp.com> wrote in message
news:unAnIkUhEHA.3664@.TK2MSFTNGP11.phx.gbl...
> Hello!
> We are using a SortBy parameter and a Direction parameter for our report.
> We are using dynamic sorting to allow the user to click on a column header
> and sort that column. A click checks whether or not it is sorted asc or
> desc and if it is, it changes the sorting to the direction it isn't
> currently sorting. This functionality works perfect, but we do not want
> to
> allow the users to sort using the parameter ui drop-down boxes. If we
> removing the promt from the report designer it gives an error saying that
> the parameter is read only and the report errors out. If we try to
> uncheck
> the prompt box in report manager, it gives the same errors. We want to
> use
> these parameters for sorting within the report, but we do not wish to have
> the UI there as dropdowns. Is there a way to correct this? We appreciate
> your help!
>|||That worked great.
Exactly what I was looking for.
I do have another question. Do you have a script, or know of a script that
will remove duplicate values from a filter UI? We have a bunch of clients
and when we pull in query values for the filter to filter by client it
duplicates clients within the list because on the same query we have
multiple users that are associated with the same client. A script to remove
those duplicates would be very sweet. Let me know what you think. Thanks
again! Great script!
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
news:OBB0FcVhEHA.1276@.TK2MSFTNGP09.phx.gbl...
> UI seems to be confusing. You could try to use my script:
> http://blogs.msdn.com/levs/archive/2004/07/20.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "jrennard" <jrennard3@.schneidercorp.com> wrote in message
> news:unAnIkUhEHA.3664@.TK2MSFTNGP11.phx.gbl...
> > Hello!
> >
> > We are using a SortBy parameter and a Direction parameter for our
report.
> > We are using dynamic sorting to allow the user to click on a column
header
> > and sort that column. A click checks whether or not it is sorted asc or
> > desc and if it is, it changes the sorting to the direction it isn't
> > currently sorting. This functionality works perfect, but we do not want
> > to
> > allow the users to sort using the parameter ui drop-down boxes. If we
> > removing the promt from the report designer it gives an error saying
that
> > the parameter is read only and the report errors out. If we try to
> > uncheck
> > the prompt box in report manager, it gives the same errors. We want to
> > use
> > these parameters for sorting within the report, but we do not wish to
have
> > the UI there as dropdowns. Is there a way to correct this? We
appreciate
> > your help!
> >
> >
>

Hiding Parameters in Report

I'm using a URL to execute reports and I wish to have the parameters not displayed to the user. However, when I either uncheck the Promt User box or delete the prompt string, I get an error stating read only parameters can not be changed.
We have installed SP1 on the server so I thought this issue had been fixed.
TIA for any help.
Dave
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.If you don't want any parameters displayed to the user, add
rc:Parameters=false to the URL.
If you want only some of the parameters displayed, then for those that
should be hidden, make sure "Prompt User" is checked and the prompt string
is empty.
--
This posting is provided "AS IS" with no warranties, and confers no rights
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OA03nKUXEHA.2520@.TK2MSFTNGP12.phx.gbl...
> I'm using a URL to execute reports and I wish to have the parameters not
displayed to the user. However, when I either uncheck the Promt User box or
delete the prompt string, I get an error stating read only parameters can
not be changed.
> We have installed SP1 on the server so I thought this issue had been
fixed.
> TIA for any help.
> Dave
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Hiding Parameters

I have a popup window which shows the report. I don't want user to modify
the parameter of the displayed report.
how can i hide the parameter list.
I am familiar with rc:ToolBar = false while invoking the report how can i
disable parameter from being changed
thxtry setting the parameter(s) to be "Hidden" in the report properties applet
"nkg" wrote:
> I have a popup window which shows the report. I don't want user to modify
> the parameter of the displayed report.
> how can i hide the parameter list.
> I am familiar with rc:ToolBar = false while invoking the report how can i
> disable parameter from being changed
> thx
>
>|||thx
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:41752B0A-DB95-4B08-A3E3-04F51EA97686@.microsoft.com...
> try setting the parameter(s) to be "Hidden" in the report properties
> applet
> "nkg" wrote:
>> I have a popup window which shows the report. I don't want user to modify
>> the parameter of the displayed report.
>> how can i hide the parameter list.
>> I am familiar with rc:ToolBar = false while invoking the report how can i
>> disable parameter from being changed
>> thx
>>

HIding databases that a user does not have permission to

I remember reading a blog post which showed you how to hide databases other than the ones the current user has permission to see. My ISP is getting a lot more SQL 2005 databases on the server and I would like to encourage them to only show me mine in Management Studio rather than the huge list I have to scroll down. Is this possible?

(the blog post *may* have been for SQL 2000 but I'm looking for a SQL 2005 solution)

Yes, there have been some recent discussions about this. You need to revoke the permission 'VIEW ANY DATABASE' from the role PUBLIC (and then grant it to any server principal who should have it but don't get it by default). Credit to Kalen Delaney and Dan Guzman who pointed this out... wrote in message news:b45aadf8-59d9-4531-9ba7-dac473bbcade@.discussions.microsoft.com... >I remember reading a blog post which showed you how to hide databases
> other than the ones the current user has permission to see. My ISP is
> getting a lot more SQL 2005 databases on the server and I would like to
> encourage them to only show me mine in Management Studio rather than the
> huge list I have to scroll down. Is this possible? >
> (the blog post *may* have been for SQL 2000 but I'm looking for a SQL
> 2005 solution) > > >
>

Hiding a subreport

We have a parameter that takes the values of "Yes" or "No" (non-queried). If the user chooses "Yes", we want the subreport to display, but remain hidden on "No".

In the expression for visibility, we have the following:

=IIf(cstr(Parameters!Heading.Value = "Yes"),False,True)

When the report runs we get the following error: "The input string was not in the correct format."

Has anybody seen this before?

Thanks for the information.

Problem solved - the parameter data-type was set to boolean.