Read/Only access to Northwind database

Hi;
This is one of those things I want to be 100% sure to get right because we
are opening this user to the world.
How do I create a user in SqlServer that has read/only access to Northwind.
No access to any other database and only R/O access to Northwind?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
see the "too much free time video" - http://www.windwardreports.com/film.htmTry:
1) Create a login
2) Add the login as a user to the Northwind database
3) Add the user to the db_datareader fixed database role
Be sure that you remove the guest user, if present.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:6F943F7E-D5A6-495F-8EAD-EE3199777575@.microsoft.com...
Hi;
This is one of those things I want to be 100% sure to get right because we
are opening this user to the world.
How do I create a user in SqlServer that has read/only access to Northwind.
No access to any other database and only R/O access to Northwind?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
see the "too much free time video" - http://www.windwardreports.com/film.htm|||Hi;
I had it set to public & db_datareader because it won't let me deselect
public. Is this correct or am I missing something?
Also, what do you mean by "fixed database"?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Tom Moreau" wrote:

> Try:
> 1) Create a login
> 2) Add the login as a user to the Northwind database
> 3) Add the user to the db_datareader fixed database role
> Be sure that you remove the guest user, if present.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:6F943F7E-D5A6-495F-8EAD-EE3199777575@.microsoft.com...
> Hi;
> This is one of those things I want to be 100% sure to get right because we
> are opening this user to the world.
> How do I create a user in SqlServer that has read/only access to Northwind
.
> No access to any other database and only R/O access to Northwind?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> see the "too much free time video" - [url]http://www.windwardreports.com/film.htm[/ur
l]
>
>|||Hi Dave,
I think you've done it right and haven't miseed anything. The public role
is a special database role to which every database
user belongs. The public role contain default access permissionsfor any
user who can access the database. This database role cannot
be dropped.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial
response from the community or a Microsoft Support Engineer within 1
business day is
acceptable. Please note that each follow up response may take approximately
2 business days
as the support professional working with you may need further investigation
to reach the
most efficient resolution. The offering is not appropriate for situations
that require
urgent, real-time or phone-based interactions or complex project analysis
and dump analysis
issues. Issues of this nature are best handled working with a dedicated
Microsoft Support
Engineer by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||A "fixed database role" is one that is there by default and cannot be
removed. There are also fixed server roles. You can create your own
database roles, too. However, you cannot create server roles.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:C32D630F-14E6-4A85-96CF-171DCD4D1A48@.microsoft.com...
Hi;
I had it set to public & db_datareader because it won't let me deselect
public. Is this correct or am I missing something?
Also, what do you mean by "fixed database"?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Tom Moreau" wrote:

> Try:
> 1) Create a login
> 2) Add the login as a user to the Northwind database
> 3) Add the user to the db_datareader fixed database role
> Be sure that you remove the guest user, if present.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:6F943F7E-D5A6-495F-8EAD-EE3199777575@.microsoft.com...
> Hi;
> This is one of those things I want to be 100% sure to get right because we
> are opening this user to the world.
> How do I create a user in SqlServer that has read/only access to
> Northwind.
> No access to any other database and only R/O access to Northwind?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> see the "too much free time video" -
> http://www.windwardreports.com/film.htm
>
>

Read Write Variable Cannot access before PostExecute

I have a for each loop on a directory of files, each file has to be imported with a unique surrogate key added. For this I am selecting the max id that exists in the target table and assigning that value to a variable.

Within a script transformation I am copying this value to a variable declared in a script task, and incrementing it on each row processed. Obviously I now want to write this value back out to the higher scoped variable so it is available for the next file.

If I make the variable read write it is not even available for reading until the PostExecute. Is this correct or have I missed something?

To work round this I have created a second higher scoped variable that I can write to in the PostExecute and the other variable is passed in as read only and added another script task to update the variable values.Philip is a colleague of mine and we've just been taking a look at this.

The workaround is to use a multiflatfile adapter because the metadata of the files is identical.

Philip's requirement to be able to read a ReadWriteVariable in PreExecute() is, I feel, a valid one. Is there a reason that this cannot be done?

-Jamie|||You can actually access write-able variables anywhere you want just not the ones on the read/write list. The component has a VariableDispenser that you can lock variables for read and/or write and use them as you will. The limitation we place is only for the ones you specify in the ReadWriteVariables line and this was done to keep locking to a minimum. If we gave access during row processing then because we don't know the usage we would need to keep the variable locked during the entire ProcessInput call. If some other transform needed this variable as well then we have concurrency issues. This way the user can lock variables for write but has to do it explicitly so that it can be unlocked explicitly as well and hopefully the locking time can be kept to a minimum since the script author is controlling the locking.

HTH,
Matt

Read only User on data base

I'm sure this is a really easy one but I want to create a user with read only access on a sql 2000 database using SQL authentication.

I'm building a little pilot DB system and I have a trainee developer who I want to built me some queries for crystal reports. But I dont want them to be able to amend/delete data or tables etc.

I've created the user but how to I set it to read only. Also will the user still have read only rights if the user has access to the database via enterprize manager/Query analysier.

thanks,Grant the user as db_datareader. He or she will can only read data and uses EM and QA.|||I've Granted the user the db_datareader permission and within EM everything is ok the user has read only access but within QA, logging in with the correct SQL Ath. user name and password he still has permission to Drop tables via the object browser -- very strange or have I done something wrong !|||Is the id in a particular role?

How many roles do you have?|||The User has been created within the Public and DB_datareader database role only.

Do I need to add him to another db role or server role?|||Nope. Nothing wrong. You just have to change the password on the account he is using in Query Analyzer. Basically, this user has access to two separate accounts. One with the permissions you want him to have (NT Authenticated), and a second (the SQL authenticated one) with permissions you do not want him to have.

Naturally, before you go changing passwords, you have to ask where the other (SQL authenticated) account is used (maybe in some application that needs to update data?). In this case, changing the password could be prohibitive, and you may have to resort to asking the user to stop using that account. Ahh, politics.

Read only permissions for report writer

I have an msde database (SQL Server 2000) with an web front end. I would
like to create an MS Access Project to allow certain users to create ad hoc
reports.
I have created a new login named Reporter and connected to the msde database
using this user. This user has dbreadonly permissions.
This works to a certain extent in that new reports can be created based on
existing tables. However, I would like the user to be able to create their
own select queries and cannot find a way to manage this. I recieve a
message saying the user needs 'Create Procedure' rights.
Can anyone tell me how I can let Reporter create their own queries but not
add/edit/delete any existing data?
Many thanks
June
hi June,
June Macleod wrote:
> I have an msde database (SQL Server 2000) with an web front end. I
> would like to create an MS Access Project to allow certain users to
> create ad hoc reports.
> I have created a new login named Reporter and connected to the msde
> database using this user. This user has dbreadonly permissions.
> This works to a certain extent in that new reports can be created
> based on existing tables. However, I would like the user to be able
> to create their own select queries and cannot find a way to manage
> this. I recieve a message saying the user needs 'Create Procedure'
> rights.
> Can anyone tell me how I can let Reporter create their own queries
> but not add/edit/delete any existing data?
> Many thanks
> June
if you are required to allow "CREATE PROC" statement you have to grant
membership to ddladmin database role but, with such a permission, Reporter
can even create new tables and of course access/modify their data..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||better,
if you only are required to create procedures, you can grant that specific
permission to Reporte user like
GRANT CREATE PROCEDURE TO [Reporter]
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Read Only permission for ReportingServices.GetPolicies()

I would like for a user to view the groups that have permssion for a
particular folder in SQL Reporting Services that they have access to via the
Web Services by calling ReportingServices.GetPolicies(). I realize
(according to MS's website and trial and error) that the user must have the
"Read Security Policies" right for that folder. The only way I have found
to set this via a Role is to allow "Set security for individual items".
Is there any way to allow a user to only view the security information via
ReportingServices.GetPolicies() without allowing them to change the security
policies?
Any help would be greatly appreciated. Thank you.
Jeremy M. WhiteWell, I found a way to do it, but I'm not really excited about it.
Currently, I am selecting directly from the SQL Reporting services database.
Again, I would prefer to do it through the web services, but I guess this
will suffice. For anyone interested, here is the SQL statement to retrieve
the policy information:
SELECT
U.UserName, U.UserType, R.RoleName, R.Description
FROM
Catalog As C INNER JOIN
PolicyUserRole As PU
ON (C.PolicyID = PU.PolicyID)
INNER JOIN
Roles As R
ON (PU.RoleID = R.RoleID)
INNER JOIN
Users As U
ON (PU.UserID = U.UserID)
WHERE
C.Path = @.ItemName
If anyone is able to determine how to provide read-only access to the
policies via a role, please let me know.
Thanks,
Jeremy
"Jeremy M. White" <jeremy_white@.dart.biz> wrote in message
news:uEkE3mbzEHA.1300@.TK2MSFTNGP14.phx.gbl...
> I would like for a user to view the groups that have permssion for a
> particular folder in SQL Reporting Services that they have access to via
the
> Web Services by calling ReportingServices.GetPolicies(). I realize
> (according to MS's website and trial and error) that the user must have
the
> "Read Security Policies" right for that folder. The only way I have found
> to set this via a Role is to allow "Set security for individual items".
> Is there any way to allow a user to only view the security information via
> ReportingServices.GetPolicies() without allowing them to change the
security
> policies?
> Any help would be greatly appreciated. Thank you.
> Jeremy M. White
>

Read only and access to specific databases

Hello everyone,
Please forgive me as I'm VERY new to SQL Server. I am (in a former and
current life) a Oracle Certified Master DBA, however, I've been given a
new project -- SQL Server 2005 to administrate. Although I'm going
through some material on SQL Server, I do have two questions that
hopefully someone can answer for me.
I first want to make a READ-ONLY user (a user who can only query the
database) but not do any updates, deletes, etc. I added a local group
to the box (and put the users into that local group) and then added
that group to the "master" security users and then added that group to
the specific database I want them to have access to.
My question is, I don't see where I can make that group readonly to
that specific database, and I also don't see where I can give them
access to ONLY that one database. They can so far query the other
remaining 6 databases on the system by using owner.schema.table syntax,
and I don't want them to be able to do that.
I know this is prob. a pretty simple question and task, but I'm stumped
at how SQL Server does this... although i'm sure the solution is as
simple as the question.
Thanks for all your imput and help!
Create a SQL Login or add the users Windows account (or
Windows group) as a login on the SQL Server box. The login
gives the user access to the server but not to the databases
themselves. It's kind of like a grant connect.
For read only access to a particular database, you can then
add that login as a user in the database and add the user to
the built in database role db_datareader. That would give
the user select on the tables. You could additionally add
the user to db_denydatawriter and that would deny their
ability to insert, update, delete on the tables.
As for why you are seeing some variances, permissions are
cumulative. If the user is a member of other groups that
have access to databases, database objects then the user
gets access that way. If the public database role has been
granted access more than the default access, the user would
pick these up as well as all database users are members of
the public role.
The user having access to the other databases you don't
explicitly add the user to could be due to membership in a
windows group that has been given access - so they inherit
access to the database through their group membership. Or
enabling the guest account in that database. If the guest
account is enabled, anyone with a login can access the
database. By default of how the public role works, the user
would have access to anything granted to public. The guest
account can also be granted additional permissions although
this isn't really a good idea. The guest account is always
exists in master and tempdb and can't be removed from those
two databases.
-Sue
On 14 Mar 2006 08:38:17 -0800, "Greg" <gregcpx@.hotmail.com>
wrote:

>Hello everyone,
>Please forgive me as I'm VERY new to SQL Server. I am (in a former and
>current life) a Oracle Certified Master DBA, however, I've been given a
>new project -- SQL Server 2005 to administrate. Although I'm going
>through some material on SQL Server, I do have two questions that
>hopefully someone can answer for me.
>I first want to make a READ-ONLY user (a user who can only query the
>database) but not do any updates, deletes, etc. I added a local group
>to the box (and put the users into that local group) and then added
>that group to the "master" security users and then added that group to
>the specific database I want them to have access to.
>My question is, I don't see where I can make that group readonly to
>that specific database, and I also don't see where I can give them
>access to ONLY that one database. They can so far query the other
>remaining 6 databases on the system by using owner.schema.table syntax,
>and I don't want them to be able to do that.
>I know this is prob. a pretty simple question and task, but I'm stumped
>at how SQL Server does this... although i'm sure the solution is as
>simple as the question.
>Thanks for all your imput and help!

Read only and access to specific databases

Hello everyone,
Please forgive me as I'm VERY new to SQL Server. I am (in a former and
current life) a Oracle Certified Master DBA, however, I've been given a
new project -- SQL Server 2005 to administrate. Although I'm going
through some material on SQL Server, I do have two questions that
hopefully someone can answer for me.
I first want to make a READ-ONLY user (a user who can only query the
database) but not do any updates, deletes, etc. I added a local group
to the box (and put the users into that local group) and then added
that group to the "master" security users and then added that group to
the specific database I want them to have access to.
My question is, I don't see where I can make that group readonly to
that specific database, and I also don't see where I can give them
access to ONLY that one database. They can so far query the other
remaining 6 databases on the system by using owner.schema.table syntax,
and I don't want them to be able to do that.
I know this is prob. a pretty simple question and task, but I'm stumped
at how SQL Server does this... although i'm sure the solution is as
simple as the question.
Thanks for all your imput and help!Create a SQL Login or add the users Windows account (or
Windows group) as a login on the SQL Server box. The login
gives the user access to the server but not to the databases
themselves. It's kind of like a grant connect.
For read only access to a particular database, you can then
add that login as a user in the database and add the user to
the built in database role db_datareader. That would give
the user select on the tables. You could additionally add
the user to db_denydatawriter and that would deny their
ability to insert, update, delete on the tables.
As for why you are seeing some variances, permissions are
cumulative. If the user is a member of other groups that
have access to databases, database objects then the user
gets access that way. If the public database role has been
granted access more than the default access, the user would
pick these up as well as all database users are members of
the public role.
The user having access to the other databases you don't
explicitly add the user to could be due to membership in a
windows group that has been given access - so they inherit
access to the database through their group membership. Or
enabling the guest account in that database. If the guest
account is enabled, anyone with a login can access the
database. By default of how the public role works, the user
would have access to anything granted to public. The guest
account can also be granted additional permissions although
this isn't really a good idea. The guest account is always
exists in master and tempdb and can't be removed from those
two databases.
-Sue
On 14 Mar 2006 08:38:17 -0800, "Greg" <gregcpx@.hotmail.com>
wrote:
>Hello everyone,
>Please forgive me as I'm VERY new to SQL Server. I am (in a former and
>current life) a Oracle Certified Master DBA, however, I've been given a
>new project -- SQL Server 2005 to administrate. Although I'm going
>through some material on SQL Server, I do have two questions that
>hopefully someone can answer for me.
>I first want to make a READ-ONLY user (a user who can only query the
>database) but not do any updates, deletes, etc. I added a local group
>to the box (and put the users into that local group) and then added
>that group to the "master" security users and then added that group to
>the specific database I want them to have access to.
>My question is, I don't see where I can make that group readonly to
>that specific database, and I also don't see where I can give them
>access to ONLY that one database. They can so far query the other
>remaining 6 databases on the system by using owner.schema.table syntax,
>and I don't want them to be able to do that.
>I know this is prob. a pretty simple question and task, but I'm stumped
>at how SQL Server does this... although i'm sure the solution is as
>simple as the question.
>Thanks for all your imput and help!

Read only and access to specific databases

Hello everyone,
Please forgive me as I'm VERY new to SQL Server. I am (in a former and
current life) a Oracle Certified Master DBA, however, I've been given a
new project -- SQL Server 2005 to administrate. Although I'm going
through some material on SQL Server, I do have two questions that
hopefully someone can answer for me.
I first want to make a READ-ONLY user (a user who can only query the
database) but not do any updates, deletes, etc. I added a local group
to the box (and put the users into that local group) and then added
that group to the "master" security users and then added that group to
the specific database I want them to have access to.
My question is, I don't see where I can make that group readonly to
that specific database, and I also don't see where I can give them
access to ONLY that one database. They can so far query the other
remaining 6 databases on the system by using owner.schema.table syntax,
and I don't want them to be able to do that.
I know this is prob. a pretty simple question and task, but I'm stumped
at how SQL Server does this... although i'm sure the solution is as
simple as the question.
Thanks for all your imput and help!Create a SQL Login or add the users Windows account (or
Windows group) as a login on the SQL Server box. The login
gives the user access to the server but not to the databases
themselves. It's kind of like a grant connect.
For read only access to a particular database, you can then
add that login as a user in the database and add the user to
the built in database role db_datareader. That would give
the user select on the tables. You could additionally add
the user to db_denydatawriter and that would deny their
ability to insert, update, delete on the tables.
As for why you are seeing some variances, permissions are
cumulative. If the user is a member of other groups that
have access to databases, database objects then the user
gets access that way. If the public database role has been
granted access more than the default access, the user would
pick these up as well as all database users are members of
the public role.
The user having access to the other databases you don't
explicitly add the user to could be due to membership in a
windows group that has been given access - so they inherit
access to the database through their group membership. Or
enabling the guest account in that database. If the guest
account is enabled, anyone with a login can access the
database. By default of how the public role works, the user
would have access to anything granted to public. The guest
account can also be granted additional permissions although
this isn't really a good idea. The guest account is always
exists in master and tempdb and can't be removed from those
two databases.
-Sue
On 14 Mar 2006 08:38:17 -0800, "Greg" <gregcpx@.hotmail.com>
wrote:

>Hello everyone,
>Please forgive me as I'm VERY new to SQL Server. I am (in a former and
>current life) a Oracle Certified Master DBA, however, I've been given a
>new project -- SQL Server 2005 to administrate. Although I'm going
>through some material on SQL Server, I do have two questions that
>hopefully someone can answer for me.
>I first want to make a READ-ONLY user (a user who can only query the
>database) but not do any updates, deletes, etc. I added a local group
>to the box (and put the users into that local group) and then added
>that group to the "master" security users and then added that group to
>the specific database I want them to have access to.
>My question is, I don't see where I can make that group readonly to
>that specific database, and I also don't see where I can give them
>access to ONLY that one database. They can so far query the other
>remaining 6 databases on the system by using owner.schema.table syntax,
>and I don't want them to be able to do that.
>I know this is prob. a pretty simple question and task, but I'm stumped
>at how SQL Server does this... although i'm sure the solution is as
>simple as the question.
>Thanks for all your imput and help!

Read Only Access to Stored Procedures

We do not allow anyone except DBA's the DBO access permission in our
organization for the "production" environment. The programmers get
DataReader/DataWriter access in production, however, they are wanting
read-only access to the stored procedures.
Does anyone know of a why to give them READ ONLY access to stored procedures
?
Thanks for your help.
ColetteThe only permissions you can grant Users WRT SP's is Execute.
Either a user is allowed to execute a SP or not.
I would seriously re-consider Developer Access to production. Developers
should only be in development, and support staff read only production rights
.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Colette" wrote:

> We do not allow anyone except DBA's the DBO access permission in our
> organization for the "production" environment. The programmers get
> DataReader/DataWriter access in production, however, they are wanting
> read-only access to the stored procedures.
> Does anyone know of a why to give them READ ONLY access to stored procedur
es?
> Thanks for your help.
> Colette

Read one field in every row during SELECT

Hello

I have a table that keeps track of every access to the system. We insert the UserID, TimeOfAccess and TypeOfAccess.

We want to create a report and due to the limitations of Reporting Services for programatically processing, we want to create a temporary table to have the columns already set up for the report with the info that we need.

ORIGINAL TABLE

USERID | TIMEOFACCESS | TYPE OF ACCESS

2323 | 12/15/2007 03:52:54 | CLOCKIN

2323 | 12/15/2007 04:32:54 | CLOCKOUT

2323 | 12/15/2007 05:42:54 | CLOCKIN

2323 | 12/15/2007 07:53:54 | CLOCKOUT

2323 | 12/15/2007 09:18:54 | CLOCKIN

2323 | 12/15/2007 10:24:54 | CLOCKOUT

TEMPORARY TABLE

USERID | CLOCKIN | CLOCKOUT | ELAPSEDTIME

2323 | 03:52:54 | 04:32:54 | 0:40:54

OK. the problem is that I need to read the fields one by one in the SELECT statement so i can insert (and Update) the fields of the temporary table.

I create my temporary table

CREATE #TempTable

(

UserID int,

ClockIN DateTime null,

ClockOUT DateTime null,

ElapsedTime DateTime null,

)

I want to be able to do this in my stored procedure. I can doit in a form but i want to return from my database the datatable already suitable for my report.

Does anyone know how to read in a SELECT statement one field as it's been read?

you can do that using a cursor in your stored procedure.

See: http://www.sqlteam.com/item.asp?ItemID=553

If you dont want to use a cursor, its possible as well:

http://www.sql-server-performance.com/dp_no_cursors.asp

|||

hi,

you can achieve this using a better solution by dong a self join on userid and midnight(dateaccess)

you just have to drop the timepart of the date on the join. you dont even need the temp table.

here's the pseudocode: you just need to imporve this.

select userid, convert( varchar(20),dateaccess,102) as dateaccess, convert( varchar (20),dateaccess,108) as clockin, t2.clockout from table1 t1

where [type of access]='clockin'

join

(select userid, convert( varchar(20),dateaccess,102) as dateaccess,

convert( varchar(20),dateaccess,108) as clockout, from table1

where [type of access]='clockout')

as t2

on t1.userid=t2.userid and t1.dateaccess=t2.dateaccess

regards,

joey

|||

Try selecting the minimum "clockin" and maximum "clockout" per each userid, then calculate the diff in seconds.

Code Snippet

;with cte

as

(

select

userid,

min(case when [type of access] = 'clockin' then [time of access] end) as clockin,

max(case when [type of access] = 'clockout' then [time of access] end) as clockout,

datediff(

seconds,

min(case when [type of access] = 'clockin' then [time of access] end) as clockin,

max(case when [type of access] = 'clockout' then [time of access] end) as clockout

) as elapsed_time_sec

from

dbo.t1

group by

userid

)

select

userid,

clockin,

clockout,

right('00' + ltrim(elapsed_time_sec / 3600), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) / 60), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) % 60), 2)

from

cte;

AMB

|||

None of this solutions actually work.

I don't get the desired result. I think this is something impossible to do in a Stored Procedure wich is very disappointing because is not so difficult to do in a form. But that's what i don't want. There has to be a way... i guess i need to study more the t-sql language. I just can't believe t-sql is so poor.

|||

Try:

Code Snippet

;with cte_1

as

(

select

userid,

[TIME OF ACCESS],

[TYPE OF ACCESS],

row_number() over(partition by userid order by [TIME OF ACCESS]) as rn

from

dbo.t1

),

cte_2

as

(

select

a.userid,

a.[TIME OF ACCESS] as CLOCKIN,

b.[TIME OF ACCESS] as CLOCKOUT,

datediff(second, a.[TIME OF ACCESS], b.[TIME OF ACCESS]) as elapsed_time_sec

from

cte_1 as a

inner join

cte_1 as b

on a.userid = b.userid

and a.rn = b.rn - 1

and a.[TYPE OF ACCESS] = 'CLOCKIN'

and b.[TYPE OF ACCESS] = 'CLOCKOUT'

)

select

userid,

CLOCKIN,

CLOCKOUT,

right('00' + ltrim(elapsed_time_sec / 3600), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) / 60), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) % 60), 2)

from

cte_2

order by

userid,

CLOCKIN;

go

BTW, I wonder why elapsed time for the first two rows is "00:40:54", is they both have same number of seconds. I think it should be "00:40:00".

AMB

|||

This is it.

It works perfectly fine.

Definitely i am gonna buy a couple of T-SQL books. Any suggestion on the best title or publisher?

Jose

|||

I will suggest the serie "Inside SQL Server 2005".

Inside Microsoft SQL Server 2005: T-SQL Querying

http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735623139/ref=pd_bbs_sr_3/002-2026708-7606405?ie=UTF8&s=books&qid=1179781170&sr=1-3

Inside Microsoft SQL Server 2005: T-SQL Programming

http://www.amazon.com/Inside-Microsoft-Server-2005-Pro-Developer/dp/0735621977/ref=pd_sim_b_1/002-2026708-7606405?ie=UTF8&qid=1179781170&sr=1-3

Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine

http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_sim_b_2/002-2026708-7606405?ie=UTF8&qid=1179781170&sr=1-3

AMB

P.S. Your alias exposed you.

PP --> Pepe (Jose)

CUBAN --> from Cuba

Oye chico, vinistes a bailar en casa del trompo.

|||

AMB

P.S. Your alias exposed you.

As one who belives in transparency, and thinks that a lot of folks are stuck in grade school with their childish and 'cutsy' nom de plumes, exactly how this is a problem in this venue?

Granted, anonymity has it purposes and places -but really, are these forums such place to fear exposure?

|||

good point. I am not affraid of exposure. As a matter of fact, most of the people online knows me by that name. ppcuban@.{most famous emails}.com are my address, the domain ppcuban.com also... hahaha. So, basically everybody call me ppcuban as a nickname even out of internet.

AMB? Tu eres cubano?

|||

Jose,

Nacido y criado en la Vibora. Emigre hacia los Estados Unidos de america hace 9 anios. Vivi por un tiempo en Miami y ahora radico en Carolina del Sur (No pastelitos de guayava y mucho menos masareal).

Saludos,

Alejandro Mesa

read from backup files

hi,
is there any SP or Tsql statment can read the logical
files name from backup files.
I know i can use RESTORE FILELISTONLY but i need to access
the files name and use them on a script.
regards,
AymanFrom what script do you want to access this information? Whatever you use,
you will have to make a connection to SQL Server, to run RESTORE
FILELISTONLY command.--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"ayman" <ayman.massri@.ihorizons.com> wrote in message
news:a1cf01c3ebfe$cb13d140$a101280a@.phx.gbl...
hi,
is there any SP or Tsql statment can read the logical
files name from backup files.
I know i can use RESTORE FILELISTONLY but i need to access
the files name and use them on a script.
regards,
Ayman

Read autonumbering field from db

Situation: We are blank new to SQL server (previosuly access) and we need to
convert to sql server quickly. Consequently this is maybe not a very wice
question - but it is SO important to us :-)
**
this works in access - but not on sql server:
Tabel contains 2 fields:
id = autonumbering
text = textfield
****
Set Con = CreateObject(”ADODB.Connection
Con.open "PROVIDER=SQLOLEDB;DATA
SOURCE=192.168.2xx. xx;UID=xxxxxx;PWD=xxxxxxxx;DATABASE=xxxx
xx"
Set RS=CreateObject(”ADODB.RecordSet”)
RS.Open ”Select * FROM test”, 1, 3
RS.AddNew
RS(”Text”) = ”test tekst”
RS.Update
Response.write RS(”text”) & ”was given id=” & RS(”id”)
RS.Close
Con.Close
’*************************************
********************
this should output the given id number - which is actually IN the database.
But in the output from above - it is blank ...
Thanx in advance :-)Take a look at the @.@.IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT functions fo
r
SQL server in Books Online.
"Jensgjerloev" wrote:

> Situation: We are blank new to SQL server (previosuly access) and we need
to
> convert to sql server quickly. Consequently this is maybe not a very wice
> question - but it is SO important to us :-)
> **
> this works in access - but not on sql server:
> Tabel contains 2 fields:
> id = autonumbering
> text = textfield
> ****
> Set Con = CreateObject(”ADODB.Connection
> Con.open "PROVIDER=SQLOLEDB;DATA
> SOURCE=192.168.2xx. xx;UID=xxxxxx;PWD=xxxxxxxx;DATABASE=xxxx
xx"
> Set RS=CreateObject(”ADODB.RecordSet”)
> RS.Open ”Select * FROM test”, 1, 3
> RS.AddNew
> RS(”Text”) = ”test tekst”
> RS.Update
> Response.write RS(”text”) & ”was given id=” & RS(”id”)
> RS.Close
> Con.Close
> ’*************************************
********************
> this should output the given id number - which is actually IN the database
.
> But in the output from above - it is blank ...
> Thanx in advance :-)
>|||THANKs Mark - will do saturday morning.
Right now and here: Is there a command we need to add ?
Best regards
Jens
"Mark Williams" wrote:
> Take a look at the @.@.IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT functions
for
> SQL server in Books Online.
> --
>
> "Jensgjerloev" wrote:
>|||THANKX Mark - it's working now - thanks to your help :-))
Best Regards
Jens
"Mark Williams" wrote:
> Take a look at the @.@.IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT functions
for
> SQL server in Books Online.
> --
>
> "Jensgjerloev" wrote:
>

READ access to sql server activity monitor and sql server JOB acti

HI All,
Due to sox audit complaiance issues, we cannot give full read write access
to our production sql server 2005 databases to our developers.
But our developers want to have atleast read access to sql server activity
monitor and sql server JOB activity monitor.
How can I give only read access to those two monitors?
If I give read access to MSDB database then it will work?
I think only sysadmin role has access to those monitors right?
Please help on how to give read olny access to those monitors in sql server
2005.
Regards
Kumar
You just need to give them ProcessAdmin Server Role and in UserMapping
for MSDB give them SQLAgentReaderRole and SQLAgentUserRole
HTH,
Dinesh

READ access to sql server activity monitor and sql server JOB acti

HI All,
Due to sox audit complaiance issues, we cannot give full read write access
to our production sql server 2005 databases to our developers.
But our developers want to have atleast read access to sql server activity
monitor and sql server JOB activity monitor.
How can I give only read access to those two monitors?
If I give read access to MSDB database then it will work?
I think only sysadmin role has access to those monitors right?
Please help on how to give read olny access to those monitors in sql server
2005.
Regards
KumarYou just need to give them ProcessAdmin Server Role and in UserMapping
for MSDB give them SQLAgentReaderRole and SQLAgentUserRole
HTH,
Dineshsql

READ access to sql server activity monitor and sql server JOB acti

HI All,
Due to sox audit complaiance issues, we cannot give full read write access
to our production sql server 2005 databases to our developers.
But our developers want to have atleast read access to sql server activity
monitor and sql server JOB activity monitor.
How can I give only read access to those two monitors?
If I give read access to MSDB database then it will work?
I think only sysadmin role has access to those monitors right?
Please help on how to give read olny access to those monitors in sql server
2005.
Regards
KumarYou just need to give them ProcessAdmin Server Role and in UserMapping
for MSDB give them SQLAgentReaderRole and SQLAgentUserRole
HTH,
Dinesh

Re: How to encrypt a column in SQL Server

Hi everyone,

How can you encrypt a column in SQL Server?? I have a text box in Access when user types in his/her password I would like the field to display something like **** in the text box. The password field is a field in a table on SQL Server.
I'm using AC2K and SQL Server 2K.

Thanks in advance.To make the control show asterisks, set its password attribute.

To encrypt the data, pick an encryption algorithm and implement that algorithm in your Access MDB or data project.

-PatP|||we did this one for her already

http://www.dbforums.com/t1095162.html|||Yeah, I know we've done it before... I think more than once actually.

I just figure that if we consistantly give the same answers, eventually it will help solve the problem. Maybe I should have just posted a link to one of the previous answers, but I was feeling too lazy to go find it just then. I've been kind of busy lately, and that often leads me to take the easy answer instead of doing it up properly, especially if the easy and proper ways both generate the same results!

-PatP|||but it is kind of a waste of my employers hard paid salary to have me redudently goofing off.|||I suppose that's true, but they are rarely discriminating about goofing off. I've never heard of a case where redundant goofing off was treated differently than applied goofing off was.

-PatP|||Owhay ancay ooyay encryptay anay olumnkay innay SQLAY Erversay? Unway ethodmay issay ootay oosyay uhthay amousfay "Igpay Atinlay" gorithmlay. Rytay ittay eforebay ooyay ecideday ootay ostpay uthay amesay amnday estionquay againay.

Okay?

Re: How can you encrypt a column in SQL Server?

Hi everyone,

How can you encrypt a column in SQL Server?? I have a text box in Access when user types in his/her password I would like the field to display something like **** in the text box. The password field is a field in a table on SQL Server.
I'm using AC2K and SQL Server 2K.

Thanks in advance.Pick an encryption method, and handle it on the client. ROT13 is fine for most casual stuff, BlowFish is good for the really hairy stuff.

-PatP|||In access, right click on your textbox and go to properties. on the data tab, put Password in the input mask field.

this how ever is not encryption. There are third party dll available that use the RSA cypher and other methods to encrpyt data with public key encryption and you can write the encrypted data to SQL server but you can not really encrypt a column per se.|||You may want to look into SQLShield, but as it was noted before, - the encryption should be done in the front-end code, so that clear-text passwords are not travelling the network.|||sql server 2k gives you the ability to set permissions on a single column in a table if you wish.
however, you could give users access to the table through a view and not include the pwd column in the view definition.

just an alternative.

RDO DataType query.

A colleague is trying to get some SQL Server data into MS Access, and is querying the table from a VB program.

She opens an rdoconnection to an SQLServer database. When testing recordset.rdocolumns("fieldName").type it gives -9 as the value. This does not relate to any of VB's listed RDO connection types. It also shows the rdocolumns("fieldName").size as being twice what the database shows when viewed through Microsoft Access. What is this datatype?

Any ideas?

I've found various lists for datatype eNums for ADO, but nothing on RDO (as it's obviously obsolete). Anyone out there got any ideas (other than "use something else")?

Thanks in advance

Chris.

export the data from sql server using dts

call the dts from the apps using dts run utility

or

use ado

or use replication

RDLC Object datasource can't get System.Web through webform reportviewer

I am running an RDLC with a object datasource. In the object I try to
access System.Web.Httphandler to get the session, but it is nothing.
The reportviewer running my rdlc from a webform, and it seems as though
there is a setting or something to enable the object datasource access
to the session but I cannot figure it out.
Can someone help?Currently I get this error when it tries to access
System.Web.Httphandler.Current
Microsoft.Reporting.WebForms.AspNetSessionExpiredException|||lotta typos today, I am accessing:
System.Web.HttpContext.Current.Session("PhysicalWebPath")
and Current is Nothing|||This is not too surprising. The web control uses web services. I am not sure
it has a physical web path. What are you trying to determine?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <gfricke@.gmail.com> wrote in message
news:1132681439.261358.193190@.g43g2000cwa.googlegroups.com...
> lotta typos today, I am accessing:
> System.Web.HttpContext.Current.Session("PhysicalWebPath")
> and Current is Nothing
>|||Just trying to grab a session value, so if its a web service that the
web control uses, is there an EnableSession true setting I cause use in
my object datasource like I do in a standard webservice to share the
session?|||Sorry, can't help you there. I have been using the winform control.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <gfricke@.gmail.com> wrote in message
news:1132689249.764262.282850@.f14g2000cwb.googlegroups.com...
> Just trying to grab a session value, so if its a web service that the
> web control uses, is there an EnableSession true setting I cause use in
> my object datasource like I do in a standard webservice to share the
> session?
>|||On a side note, can I see these webservices? Like does it create a
asmx file or something I can connect to in IE for the object
datasources the reportviewer communicates with?|||The webservices are documented and you can use them yourself. Prior to 2005
people would (and still can) roll there own, not using the control. The
previous control (really a sample) used URL integration and so had some
difficulties.
In Books Online look for web services and you will see lots of
documentation.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <gfricke@.gmail.com> wrote in message
news:1132690648.480837.290540@.o13g2000cwo.googlegroups.com...
> On a side note, can I see these webservices? Like does it create a
> asmx file or something I can connect to in IE for the object
> datasources the reportviewer communicates with?
>

RDL Database Location

I am now supporting a previously installed copy of RS 2000.
RS was installed on ServerA which the users access via browser to
render the reports. The RDLs however are located in a SQL DB
on ServerB.
After much searching around I finally found the RDL server\DB but this
was after having no luck trying to determine this from the RS config
installed on ServerA. I checked the registry and all the config files under
the \Program Files\Microsoft SQL Server folder. Where is the RDL
server info maintained?
Thanks and Happy New Year to All!!Use WMI. There is an example here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_help_v1_3ddc.asp?frame=true
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:uj74zY0DGHA.3916@.TK2MSFTNGP10.phx.gbl...
>I am now supporting a previously installed copy of RS 2000.
> RS was installed on ServerA which the users access via browser to
> render the reports. The RDLs however are located in a SQL DB
> on ServerB.
> After much searching around I finally found the RDL server\DB but this
> was after having no luck trying to determine this from the RS config
> installed on ServerA. I checked the registry and all the config files
> under
> the \Program Files\Microsoft SQL Server folder. Where is the RDL
> server info maintained?
> Thanks and Happy New Year to All!!
>
>|||I don't see any reference to this any any of the items discussed?
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:O$iSKI7DGHA.620@.TK2MSFTNGP11.phx.gbl...
> Use WMI. There is an example here
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_help_v1_3ddc.asp?frame=true
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:uj74zY0DGHA.3916@.TK2MSFTNGP10.phx.gbl...
>>I am now supporting a previously installed copy of RS 2000.
>> RS was installed on ServerA which the users access via browser to
>> render the reports. The RDLs however are located in a SQL DB
>> on ServerB.
>> After much searching around I finally found the RDL server\DB but this
>> was after having no luck trying to determine this from the RS config
>> installed on ServerA. I checked the registry and all the config files
>> under
>> the \Program Files\Microsoft SQL Server folder. Where is the RDL
>> server info maintained?
>> Thanks and Happy New Year to All!!
>>
>|||If you compile and run the code sample you'll see one of the properties
returned from the MSReportServer_ConfigurationSetting instance is the
DatabaseServerName which is what I believe you were asking about? A cut down
version for that specific property is below
string servername = "servername"; // change this to your RS servername
string WmiNamespace = @."\\" + servername +
@."\root\Microsoft\SqlServer\ReportingServices\v8";
string WmiRSClass = @."\\" + servername +
@."\root\Microsoft\SqlServer\ReportingServices\v8:MSReportServer_ConfigurationSetting";
ManagementClass serverClass;
ManagementScope scope;
scope = new ManagementScope(WmiNamespace);
scope.Connect();
serverClass = new ManagementClass(WmiRSClass);
serverClass.Get();
if (serverClass == null)
throw new Exception("No class found");
ManagementObjectCollection instances = serverClass.GetInstances();
foreach(ManagementObject instance in instances)
{
Console.Out.WriteLine("Instance Detected");
PropertyDataCollection instProps = instance.Properties;
foreach(PropertyData prop in instProps)
{
if(prop.Name == "DatabaseServerName")
{
string name = prop.Name;
object val = prop.Value;
Console.Out.Write("Property Name: " + name);
if (val != null)
Console.Out.WriteLine(" Value: " + val.ToString());
else
Console.Out.WriteLine(" Value: <null>");
}
}
}
Console.WriteLine("");
Console.WriteLine("Press any key to exit....");
Console.ReadLine();
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%23weMuK8DGHA.1976@.TK2MSFTNGP10.phx.gbl...
>I don't see any reference to this any any of the items discussed?
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:O$iSKI7DGHA.620@.TK2MSFTNGP11.phx.gbl...
>> Use WMI. There is an example here
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_help_v1_3ddc.asp?frame=true
>> --
>> HTH,
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>>
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:uj74zY0DGHA.3916@.TK2MSFTNGP10.phx.gbl...
>>I am now supporting a previously installed copy of RS 2000.
>> RS was installed on ServerA which the users access via browser to
>> render the reports. The RDLs however are located in a SQL DB
>> on ServerB.
>> After much searching around I finally found the RDL server\DB but this
>> was after having no luck trying to determine this from the RS config
>> installed on ServerA. I checked the registry and all the config files
>> under
>> the \Program Files\Microsoft SQL Server folder. Where is the RDL
>> server info maintained?
>> Thanks and Happy New Year to All!!
>>
>>
>|||Thanks Jasper, but I an not a .NET programmer. I assumed that this was
exposed at a higher level either in the the GUI tools, config files or
registry. If not, could I impose on you as to how I might go about compiling
this?
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uB7ZEG9DGHA.128@.TK2MSFTNGP10.phx.gbl...
> If you compile and run the code sample you'll see one of the properties
> returned from the MSReportServer_ConfigurationSetting instance is the
> DatabaseServerName which is what I believe you were asking about? A cut
> down version for that specific property is below
> string servername = "servername"; // change this to your RS servername
> string WmiNamespace = @."\\" + servername +
> @."\root\Microsoft\SqlServer\ReportingServices\v8";
> string WmiRSClass = @."\\" + servername +
> @."\root\Microsoft\SqlServer\ReportingServices\v8:MSReportServer_ConfigurationSetting";
> ManagementClass serverClass;
> ManagementScope scope;
> scope = new ManagementScope(WmiNamespace);
> scope.Connect();
> serverClass = new ManagementClass(WmiRSClass);
> serverClass.Get();
> if (serverClass == null)
> throw new Exception("No class found");
> ManagementObjectCollection instances = serverClass.GetInstances();
> foreach(ManagementObject instance in instances)
> {
> Console.Out.WriteLine("Instance Detected");
> PropertyDataCollection instProps = instance.Properties;
> foreach(PropertyData prop in instProps)
> {
> if(prop.Name == "DatabaseServerName")
> {
> string name = prop.Name;
> object val = prop.Value;
> Console.Out.Write("Property Name: " + name);
> if (val != null)
> Console.Out.WriteLine(" Value: " + val.ToString());
> else
> Console.Out.WriteLine(" Value: <null>");
> }
> }
> }
> Console.WriteLine("");
> Console.WriteLine("Press any key to exit....");
> Console.ReadLine();
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:%23weMuK8DGHA.1976@.TK2MSFTNGP10.phx.gbl...
>>I don't see any reference to this any any of the items discussed?
>>
>> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>> news:O$iSKI7DGHA.620@.TK2MSFTNGP11.phx.gbl...
>> Use WMI. There is an example here
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_help_v1_3ddc.asp?frame=true
>> --
>> HTH,
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>>
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:uj74zY0DGHA.3916@.TK2MSFTNGP10.phx.gbl...
>>I am now supporting a previously installed copy of RS 2000.
>> RS was installed on ServerA which the users access via browser to
>> render the reports. The RDLs however are located in a SQL DB
>> on ServerB.
>> After much searching around I finally found the RDL server\DB but this
>> was after having no luck trying to determine this from the RS config
>> installed on ServerA. I checked the registry and all the config files
>> under
>> the \Program Files\Microsoft SQL Server folder. Where is the RDL
>> server info maintained?
>> Thanks and Happy New Year to All!!
>>
>>
>>
>|||Hi Mike,
I have uploaded a sample project to
http://www.sqldbatips.com/samples/code/RSWMIConfig.zip
If you have Visual Studio 2003 installed just open the project, fill in the
servername and press F5 to run it. If you don't have it installed then just
grab the RSWMIConfig.exe out of the \bin\Debug folder. Open a command prompt
to the folder where the exe is and run it supplying the servername as an
argument e.g.
C:\>RSWMIConfig.exe servername
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:ukyqFr$DGHA.3820@.TK2MSFTNGP12.phx.gbl...
> Thanks Jasper, but I an not a .NET programmer. I assumed that this was
> exposed at a higher level either in the the GUI tools, config files or
> registry. If not, could I impose on you as to how I might go about
> compiling this?
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:uB7ZEG9DGHA.128@.TK2MSFTNGP10.phx.gbl...
>> If you compile and run the code sample you'll see one of the properties
>> returned from the MSReportServer_ConfigurationSetting instance is the
>> DatabaseServerName which is what I believe you were asking about? A cut
>> down version for that specific property is below
>> string servername = "servername"; // change this to your RS servername
>> string WmiNamespace = @."\\" + servername +
>> @."\root\Microsoft\SqlServer\ReportingServices\v8";
>> string WmiRSClass = @."\\" + servername +
>> @."\root\Microsoft\SqlServer\ReportingServices\v8:MSReportServer_ConfigurationSetting";
>> ManagementClass serverClass;
>> ManagementScope scope;
>> scope = new ManagementScope(WmiNamespace);
>> scope.Connect();
>> serverClass = new ManagementClass(WmiRSClass);
>> serverClass.Get();
>> if (serverClass == null)
>> throw new Exception("No class found");
>> ManagementObjectCollection instances = serverClass.GetInstances();
>> foreach(ManagementObject instance in instances)
>> {
>> Console.Out.WriteLine("Instance Detected");
>> PropertyDataCollection instProps = instance.Properties;
>> foreach(PropertyData prop in instProps)
>> {
>> if(prop.Name == "DatabaseServerName")
>> {
>> string name = prop.Name;
>> object val = prop.Value;
>> Console.Out.Write("Property Name: " + name);
>> if (val != null)
>> Console.Out.WriteLine(" Value: " + val.ToString());
>> else
>> Console.Out.WriteLine(" Value: <null>");
>> }
>> }
>> }
>> Console.WriteLine("");
>> Console.WriteLine("Press any key to exit....");
>> Console.ReadLine();
>> --
>> HTH,
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>>
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:%23weMuK8DGHA.1976@.TK2MSFTNGP10.phx.gbl...
>>I don't see any reference to this any any of the items discussed?
>>
>> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>> news:O$iSKI7DGHA.620@.TK2MSFTNGP11.phx.gbl...
>> Use WMI. There is an example here
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_help_v1_3ddc.asp?frame=true
>> --
>> HTH,
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>>
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:uj74zY0DGHA.3916@.TK2MSFTNGP10.phx.gbl...
>>I am now supporting a previously installed copy of RS 2000.
>> RS was installed on ServerA which the users access via browser to
>> render the reports. The RDLs however are located in a SQL DB
>> on ServerB.
>> After much searching around I finally found the RDL server\DB but this
>> was after having no luck trying to determine this from the RS config
>> installed on ServerA. I checked the registry and all the config files
>> under
>> the \Program Files\Microsoft SQL Server folder. Where is the RDL
>> server info maintained?
>> Thanks and Happy New Year to All!!
>>
>>
>>
>>
>|||Jasper
Thanks for your help on this but I cannot find
RSWMIConfig.exe anywhere on the machine
hosting RS report manager nor on my machine
which has Visual Studio .Net 2003 installed.
I do have RSConfig.exe but it does not handle that
argument.
Mike
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OiiOBkJEGHA.2648@.TK2MSFTNGP11.phx.gbl...
> Hi Mike,
> I have uploaded a sample project to
> http://www.sqldbatips.com/samples/code/RSWMIConfig.zip
> If you have Visual Studio 2003 installed just open the project, fill in
> the servername and press F5 to run it. If you don't have it installed then
> just grab the RSWMIConfig.exe out of the \bin\Debug folder. Open a command
> prompt to the folder where the exe is and run it supplying the servername
> as an argument e.g.
> C:\>RSWMIConfig.exe servername
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:ukyqFr$DGHA.3820@.TK2MSFTNGP12.phx.gbl...
>> Thanks Jasper, but I an not a .NET programmer. I assumed that this was
>> exposed at a higher level either in the the GUI tools, config files or
>> registry. If not, could I impose on you as to how I might go about
>> compiling this?
>> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>> news:uB7ZEG9DGHA.128@.TK2MSFTNGP10.phx.gbl...
>> If you compile and run the code sample you'll see one of the properties
>> returned from the MSReportServer_ConfigurationSetting instance is the
>> DatabaseServerName which is what I believe you were asking about? A cut
>> down version for that specific property is below
>> string servername = "servername"; // change this to your RS servername
>> string WmiNamespace = @."\\" + servername +
>> @."\root\Microsoft\SqlServer\ReportingServices\v8";
>> string WmiRSClass = @."\\" + servername +
>> @."\root\Microsoft\SqlServer\ReportingServices\v8:MSReportServer_ConfigurationSetting";
>> ManagementClass serverClass;
>> ManagementScope scope;
>> scope = new ManagementScope(WmiNamespace);
>> scope.Connect();
>> serverClass = new ManagementClass(WmiRSClass);
>> serverClass.Get();
>> if (serverClass == null)
>> throw new Exception("No class found");
>> ManagementObjectCollection instances = serverClass.GetInstances();
>> foreach(ManagementObject instance in instances)
>> {
>> Console.Out.WriteLine("Instance Detected");
>> PropertyDataCollection instProps = instance.Properties;
>> foreach(PropertyData prop in instProps)
>> {
>> if(prop.Name == "DatabaseServerName")
>> {
>> string name = prop.Name;
>> object val = prop.Value;
>> Console.Out.Write("Property Name: " + name);
>> if (val != null)
>> Console.Out.WriteLine(" Value: " + val.ToString());
>> else
>> Console.Out.WriteLine(" Value: <null>");
>> }
>> }
>> }
>> Console.WriteLine("");
>> Console.WriteLine("Press any key to exit....");
>> Console.ReadLine();
>> --
>> HTH,
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>>
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:%23weMuK8DGHA.1976@.TK2MSFTNGP10.phx.gbl...
>>I don't see any reference to this any any of the items discussed?
>>
>> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
>> news:O$iSKI7DGHA.620@.TK2MSFTNGP11.phx.gbl...
>> Use WMI. There is an example here
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSUIREF/htm/f1_rsc_help_v1_3ddc.asp?frame=true
>> --
>> HTH,
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>>
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:uj74zY0DGHA.3916@.TK2MSFTNGP10.phx.gbl...
>>I am now supporting a previously installed copy of RS 2000.
>> RS was installed on ServerA which the users access via browser to
>> render the reports. The RDLs however are located in a SQL DB
>> on ServerB.
>> After much searching around I finally found the RDL server\DB but
>> this
>> was after having no luck trying to determine this from the RS config
>> installed on ServerA. I checked the registry and all the config files
>> under
>> the \Program Files\Microsoft SQL Server folder. Where is the RDL
>> server info maintained?
>> Thanks and Happy New Year to All!!
>>
>>
>>
>>
>>
>