Return all months within a range of dates

I currently have a stored procedure that returns a list of dates based on a date range a user enters.

CREATE PROCEDURE sp_GetContactScheduleDates
@.MonthFrom int,
@.YearFrom int,
@.MonthTo int,
@.YearTo int,
@.DaysInMonth int
AS
Select distinct s.ScheduleMonth, s.ScheduleYear
From OnCall_Schedules s
Where CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
 >= CAST(cast(@.MonthFrom as nvarchar) + '/' + cast('01' as nvarchar) + '/' + cast(@.YearFrom as nvarchar) as smalldatetime)
 And CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
 <= CAST(cast(@.MonthTo as nvarchar) + '/' + cast(@.DaysInMonth as nvarchar) + '/' + cast(@.YearTo as nvarchar) as smalldatetime)
Order by s.ScheduleYear, s.ScheduleMonth
GO

However, this only brings back those dates that are in the table. I need to get ALL dates within the range.

For example, the OnCall_Schedules table contains schedules that are saved by the user. If no one has ever saved a schedule at any time in May 2004 and the range of dates entered is January 2004 to June 2004, then May 2004 will not be returned. I need to get back all dates within that range regardless if it has something scheduled or not. How can this be done?

Note - I do not want to set up any dummy records or create a table with valid dates as the user will be allowed to choose any range of dates and we do not want to have to maintain anything.

Can some sort of function be used? What would the code look like?I would create a table variable with one field that will hold the date. The do a loop to populate it. I'd make sure @.startdate and @.enddate have the time stripped off. Not tested, but should work with minor tweaks.

set @.date = @.startdate
set @.x = datediff(d, @.startdate, @.enddate)
set @.y = 0
While @.y <= @.x
 Begin
 insert into @.table (datefield) values (dateadd(d, @.y, @.startdate))
 set @.y = @.y + 1
 End

|||ooo that's a nice loop. :)

Return a Field from a User Function ?

Hello,
I am using SQL Server 2000 and I am wondering if it possible to create a
user fonction that return a field so I can use the return of the function in
a WHERE .
My original query I someting like this:
'=======================================
===
SELECT * FROM Table1
WHERE
case @.Workgroup
WHEN 1 THEN Table1.RouteQuart1
WHEN 2 THEN Table1.RouteQuart2
WHEN 3 THEN Table1.RouteQuart3
END = @.NumRoute
'=======================================
====
I want to create a function to remplace the CASE. This function would return
a field. And My new query would be :
'=======================================
===
SELECT * FROM Table1
WHERE
MyNewUserFunction = @.NumRoute
'=======================================
====
Is there a way to do this ? I the query will be more optimized ' If not
possible how to make my original query the most efficient?
Regards,
Gilles LabelleGilles Labelle,
Write three sps and call them from the main one.
create procedure dbo.p1
@.RouteQuart1 int -- whatever datatype is
as
set nocount on
SELECT c1, c2, ..., cn
FROM dbo.Table1
WHERE RouteQuart1 = @.RouteQuart1
return @.@.error
go
create procedure dbo.p2
@.RouteQuart2 int -- whatever datatype is
as
set nocount on
SELECT c1, c2, ..., cn
FROM dbo.Table1
WHERE RouteQuart2 = @.RouteQuart2
return @.@.error
go
create procedure dbo.p3
@.RouteQuart3 int -- whatever datatype is
as
set nocount on
SELECT c1, c2, ..., cn
FROM dbo.Table1
WHERE RouteQuart3 = @.RouteQuart3
return @.@.error
go
create procedure dbo.p4
@.Workgroup int,
@.NumRoute int
as
set nocount on
declare @.rv int
declare @.error int
if @.Workgroup = 1
begin
exec @.rv = dbo.p1 @.NumRoute
set @.error = isnull(nullif(@.rv, 0), @.@.error)
end
else
begin
if @.Workgroup = 2
begin
exec @.rv = dbo.p2 @.NumRoute
set @.error = isnull(nullif(@.rv, 0), @.@.error)
end
else
begin
if @.Workgroup = 3
begin
exec @.rv = dbo.p3 @.NumRoute
set @.error = isnull(nullif(@.rv, 0), @.@.error)
end
else
begin
-- handle when the value of @.Workgroup is not 1, 2,3
end
end
end
return @.error
go
AMB
"Gilles Labelle" wrote:

> Hello,
> I am using SQL Server 2000 and I am wondering if it possible to create a
> user fonction that return a field so I can use the return of the function
in
> a WHERE .
> My original query I someting like this:
> '=======================================
===
> SELECT * FROM Table1
> WHERE
> case @.Workgroup
> WHEN 1 THEN Table1.RouteQuart1
> WHEN 2 THEN Table1.RouteQuart2
> WHEN 3 THEN Table1.RouteQuart3
> END = @.NumRoute
> '=======================================
====
> I want to create a function to remplace the CASE. This function would retu
rn
> a field. And My new query would be :
> '=======================================
===
> SELECT * FROM Table1
> WHERE
> MyNewUserFunction = @.NumRoute
> '=======================================
====
>
> Is there a way to do this ? I the query will be more optimized ' If not
> possible how to make my original query the most efficient?
>
> Regards,
> Gilles Labelle
>
>
>

Retriving users, roles and roles assigned to each user from the database.

How do I get this information without manually check for
it in enterprise manager. Is there a way to query the
database for the follwing informations below.
1) a list of database users
2) what roles are set up in the database
3) which users are assigned to which roles (i.e. what
privs do the users have)
"Aboki" <hcokoli@.yahoo.com> wrote in message
news:c5d601c47a3d$6847dbb0$a501280a@.phx.gbl...
>
> How do I get this information without manually check for
> it in enterprise manager. Is there a way to query the
> database for the follwing informations below.
> 1) a list of database users
> 2) what roles are set up in the database
> 3) which users are assigned to which roles (i.e. what
> privs do the users have)
1. SELECT * FROM sysusers WHERE issqlrole = 0
2. SELECT * FROM sysusers WHERE issqlrole = 1 OR isapprole = 1
3. exec sp_helprolemember
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Retriving users, roles and roles assigned to each user from the database.

How do I get this information without manually check for
it in enterprise manager. Is there a way to query the
database for the follwing informations below.
1) a list of database users
2) what roles are set up in the database
3) which users are assigned to which roles (i.e. what
privs do the users have)"Aboki" <hcokoli@.yahoo.com> wrote in message
news:c5d601c47a3d$6847dbb0$a501280a@.phx.gbl...
>
> How do I get this information without manually check for
> it in enterprise manager. Is there a way to query the
> database for the follwing informations below.
> 1) a list of database users
> 2) what roles are set up in the database
> 3) which users are assigned to which roles (i.e. what
> privs do the users have)
1. SELECT * FROM sysusers WHERE issqlrole = 0
2. SELECT * FROM sysusers WHERE issqlrole = 1 OR isapprole = 1
3. exec sp_helprolemember
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Retriving users, roles and roles assigned to each user from the database.

How do I get this information without manually check for
it in enterprise manager. Is there a way to query the
database for the follwing informations below.
1) a list of database users
2) what roles are set up in the database
3) which users are assigned to which roles (i.e. what
privs do the users have)"Aboki" <hcokoli@.yahoo.com> wrote in message
news:c5d601c47a3d$6847dbb0$a501280a@.phx.gbl...
>
> How do I get this information without manually check for
> it in enterprise manager. Is there a way to query the
> database for the follwing informations below.
> 1) a list of database users
> 2) what roles are set up in the database
> 3) which users are assigned to which roles (i.e. what
> privs do the users have)
1. SELECT * FROM sysusers WHERE issqlrole = 0
2. SELECT * FROM sysusers WHERE issqlrole = 1 OR isapprole = 1
3. exec sp_helprolemember
HTH
Rick Sawtell
MCT, MCSD, MCDBA

retriving data from 2 views by the value in a textbox

i have a textbox which a user enters a numeric value

i want it to use SqlDataSource and check if the value exists in any of the tables.

in my text box the users would enter starting from '100000' or '200000'

i want it to check the view that starts the # with '100000' and 2ed view starts '200000'

With this i can check in one of the tables and make the selection.

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:imacstestConnectionString %>"

SelectCommand="SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @.ReportNumber)">

<SelectParameters>

<asp:ControlParameterControlID="txtReport"Name="ReportNumber"PropertyName="Text"

Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

How can i make this possible ?

i was thinking putting a second sqldatasource and have that check the second view but how can i make the textbox goto the correct selectcommand ?

You'll need to do a conditional if in your select command:

IF @.ReportNumber > 10000 BEGINSELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @.ReportNumber) END ELSE BEGINSELECT [ReportNumber] FROM [AppraisalSummaryTHEOTHERTABLE] WHERE ([ReportNumber] = @.ReportNumber) END

retrieving user's permissions for each table

Hi ,
Is it possible to get the user's permissions to each table i.e user can
select , delete , insert , update , execute , DRI
what does DRI means and what is it used for ?
and also it it possible to get the permissions up till the column-level ?
what are the tables that these info are kept ?
appreciate ur advise
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via SQLMonster.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via SQLMonster.com" wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1
>|||tk you ppl for ur advice
rdgs
SQLPoet wrote:
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>> Hi ,
>[quoted text clipped - 10 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1

retrieving user's permissions for each table

Hi ,
Is it possible to get the user's permissions to each table i.e user can
select , delete , insert , update , execute , DRI
what does DRI means and what is it used for ?
and also it it possible to get the permissions up till the column-level ?
what are the tables that these info are kept ?
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via droptable.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via droptable.com" wrote:

> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1
>|||To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via droptable.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via droptable.com" wrote:

> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1
>|||tk you ppl for ur advice
rdgs
SQLPoet wrote:[vbcol=seagreen]
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>
>[quoted text clipped - 10 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||tk you ppl for ur advice
rdgs
SQLPoet wrote:[vbcol=seagreen]
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>
>[quoted text clipped - 10 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1

Retrieving User/Role Privileges - How ?

Hi,

I need to read and subsequently modify the privileges (rights) of a certain SQL Server user / role from within a Visual Basic Program.

Modifying seems to be easy using standard statements like GRANT/REVOKE. But what about reading all the rights a user has ?

I have researched SQL-DMO, but didn't find what I'm looking for.

Any idea ?

MikeTry the next command in query analyzer
sp_helprotect null, 'username' to get the rights
and
sp_helpuser to get the roles of an usersql

Retrieving user roles

Since I can't find this information anywhere, I assume I'm about to ask a
pretty stupid question :)
Is there any way in T-SQL to retrieve a list of users who belong to a
particular role? And would this method work if the base method of
authentication was Windows Authentication?
Thanks
Mike.sp_helprolemember <role>
will list out the users within a role.
"Mike Ashton" <MikeAshton@.community.nospam> wrote in message
news:OjTI5PuWFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Since I can't find this information anywhere, I assume I'm about to ask a
> pretty stupid question :)
> Is there any way in T-SQL to retrieve a list of users who belong to a
> particular role? And would this method work if the base method of
> authentication was Windows Authentication?
> Thanks
> Mike.
>|||It's
EXEC sp_helprolemember '<role name>'
for database roles
and
EXEC sp_helpsrvrolemember '<role name>'
for fixed server roles.
Jacco Schalkwijk
SQL Server MVP
"Mike Ashton" <MikeAshton@.community.nospam> wrote in message
news:OjTI5PuWFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Since I can't find this information anywhere, I assume I'm about to ask a
> pretty stupid question :)
> Is there any way in T-SQL to retrieve a list of users who belong to a
> particular role? And would this method work if the base method of
> authentication was Windows Authentication?
> Thanks
> Mike.
>

Retrieving user defined Role name

Is there a System stored procedure that gives me the Role in which a user is in. For example I execute this procedure, give the user as parameter an that gives me back the Role the user is in. It has to be said that this is a user defined role, I got three of them, HR, Employee, Approver.

Greetings,
GodofredoIs it not clear? I just want to retrieve a user defined Role

Greets,
geoff|||I'd use sp_helpuser (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_help_45o2.asp).

-PatP

Retrieving ODBC Settings.

I'm having some issues grabbing ODBC Settings. First off I need to
present a user with the options to select thier DSN, Server, UID, PWD, and
Database settings. I have come up with a way using the SQLBrowseConnect
function to obtain the Server and Database information I need. Therefore, I
have a semi working version of the program.
I just need a way to make the program a little more user
friendly/fool-proof. I know that there is an option in the ODBC settings to
set an default database. I need a way of retrieving this setting and the
default server as well. Lastly I would like to turn a text box I am currently
using for the DSN name into a dropdown box that contains a list of all
available DSNs. What functions are available to help me poll this list? I'm
really just interested in those DSN's listed under System DSNs in the Data
Source (ODBC).
See the below article for the example I used to model my program thrus far:
http://msdn.microsoft.com/library/de...datasource.asp
Let me know if I was not clear in my explains. Any help would be appreciated.
Bill
P.S. I'm cross posted this because I'm not exactly sure where it belongs.
You can retrieve the specific settings for a DSN as well as
the existing DNSs by reading the registry - that's where
they are all stored. Check the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
-Sue
On Mon, 12 Sep 2005 17:22:01 -0700, "Billr17"
<Billr17@.discussions.microsoft.com> wrote:

> I'm having some issues grabbing ODBC Settings. First off I need to
>present a user with the options to select thier DSN, Server, UID, PWD, and
>Database settings. I have come up with a way using the SQLBrowseConnect
>function to obtain the Server and Database information I need. Therefore, I
>have a semi working version of the program.
> I just need a way to make the program a little more user
>friendly/fool-proof. I know that there is an option in the ODBC settings to
>set an default database. I need a way of retrieving this setting and the
>default server as well. Lastly I would like to turn a text box I am currently
>using for the DSN name into a dropdown box that contains a list of all
>available DSNs. What functions are available to help me poll this list? I'm
>really just interested in those DSN's listed under System DSNs in the Data
>Source (ODBC).
>See the below article for the example I used to model my program thrus far:
>http://msdn.microsoft.com/library/de...datasource.asp
>Let me know if I was not clear in my explains. Any help would be appreciated.
>Bill
>P.S. I'm cross posted this because I'm not exactly sure where it belongs.
>
|||Thank you Sue. This looks like what I was searching for.
Bill
"Sue Hoegemeier" wrote:

> You can retrieve the specific settings for a DSN as well as
> the existing DNSs by reading the registry - that's where
> they are all stored. Check the following key:
> HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
> -Sue
> On Mon, 12 Sep 2005 17:22:01 -0700, "Billr17"
> <Billr17@.discussions.microsoft.com> wrote:
>
>
|||Bill,
Simplest way to fill this list is to use 'SQLDataSources' ODBC API call.
B> a text box I am currently using for the DSN name into a dropdown box
B> that contains a list of all available DSNs. What functions are
B> available to help me poll this list? I'm really just interested in
Igor Shekalev, www.sqledit.com - powerful database tools
|||Thank you for the info, that should easily get me what I need for the
dropdown box. Sues post with the registry key allowed me to retrieve the
default settings and set each controls default value appropriately. I was
going to write some code to pull the DSN's from the registry to, but I think
'SQLDataSources' might be safer. :-)
Bill
"Igor Shekalev" wrote:

> Bill,
> Simplest way to fill this list is to use 'SQLDataSources' ODBC API call.
> B> a text box I am currently using for the DSN name into a dropdown box
> B> that contains a list of all available DSNs. What functions are
> B> available to help me poll this list? I'm really just interested in
> Igor Shekalev, www.sqledit.com - powerful database tools
>
>
|||Billr,
You are right , it is really safer. As far I know, 2003 server uses registry
entries for DSN a little differently (may be uses ODBC.INI subkey for some
other cases).
B> Thank you for the info, that should easily get me what I need for the
B> dropdown box. Sues post with the registry key allowed me to retrieve
B> the default settings and set each controls default value
B> appropriately. I was going to write some code to pull the DSN's from
B> the registry to, but I think 'SQLDataSources' might be safer. :-)
B> Bill
Igor Shekalev, www.sqledit.com - powerful database tools
sql

Retrieving ODBC Settings.

I'm having some issues grabbing ODBC Settings. First off I need to
present a user with the options to select thier DSN, Server, UID, PWD, and
Database settings. I have come up with a way using the SQLBrowseConnect
function to obtain the Server and Database information I need. Therefore, I
have a semi working version of the program.
I just need a way to make the program a little more user
friendly/fool-proof. I know that there is an option in the ODBC settings to
set an default database. I need a way of retrieving this setting and the
default server as well. Lastly I would like to turn a text box I am currentl
y
using for the DSN name into a dropdown box that contains a list of all
available DSNs. What functions are available to help me poll this list? I'm
really just interested in those DSN's listed under System DSNs in the Data
Source (ODBC).
See the below article for the example I used to model my program thrus far:
http://msdn.microsoft.com/library/d...edatasource.asp
Let me know if I was not clear in my explains. Any help would be appreciated
.
Bill
P.S. I'm cross posted this because I'm not exactly sure where it belongs.You can retrieve the specific settings for a DSN as well as
the existing DNSs by reading the registry - that's where
they are all stored. Check the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
-Sue
On Mon, 12 Sep 2005 17:22:01 -0700, "Billr17"
<Billr17@.discussions.microsoft.com> wrote:

> I'm having some issues grabbing ODBC Settings. First off I need to
>present a user with the options to select thier DSN, Server, UID, PWD, and
>Database settings. I have come up with a way using the SQLBrowseConnect
>function to obtain the Server and Database information I need. Therefore, I
>have a semi working version of the program.
> I just need a way to make the program a little more user
>friendly/fool-proof. I know that there is an option in the ODBC settings to
>set an default database. I need a way of retrieving this setting and the
>default server as well. Lastly I would like to turn a text box I am current
ly
>using for the DSN name into a dropdown box that contains a list of all
>available DSNs. What functions are available to help me poll this list? I'm
>really just interested in those DSN's listed under System DSNs in the Data
>Source (ODBC).
>See the below article for the example I used to model my program thrus far:
>http://msdn.microsoft.com/library/d...edatasource.asp
>Let me know if I was not clear in my explains. Any help would be appreciate
d.
>Bill
>P.S. I'm cross posted this because I'm not exactly sure where it belongs.
>|||Thank you Sue. This looks like what I was searching for.
Bill
"Sue Hoegemeier" wrote:

> You can retrieve the specific settings for a DSN as well as
> the existing DNSs by reading the registry - that's where
> they are all stored. Check the following key:
> HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI
> -Sue
> On Mon, 12 Sep 2005 17:22:01 -0700, "Billr17"
> <Billr17@.discussions.microsoft.com> wrote:
>
>|||Bill,
Simplest way to fill this list is to use 'SQLDataSources' ODBC API call.
B> a text box I am currently using for the DSN name into a dropdown box
B> that contains a list of all available DSNs. What functions are
B> available to help me poll this list? I'm really just interested in
Igor Shekalev, www.sqledit.com - powerful database tools|||Thank you for the info, that should easily get me what I need for the
dropdown box. Sues post with the registry key allowed me to retrieve the
default settings and set each controls default value appropriately. I was
going to write some code to pull the DSN's from the registry to, but I think
'SQLDataSources' might be safer. :-)
Bill
"Igor Shekalev" wrote:

> Bill,
> Simplest way to fill this list is to use 'SQLDataSources' ODBC API call.
> B> a text box I am currently using for the DSN name into a dropdown box
> B> that contains a list of all available DSNs. What functions are
> B> available to help me poll this list? I'm really just interested in
> Igor Shekalev, www.sqledit.com - powerful database tools
>
>|||Billr,
You are right , it is really safer. As far I know, 2003 server uses registry
entries for DSN a little differently (may be uses ODBC.INI subkey for some
other cases).
B> Thank you for the info, that should easily get me what I need for the
B> dropdown box. Sues post with the registry key allowed me to retrieve
B> the default settings and set each controls default value
B> appropriately. I was going to write some code to pull the DSN's from
B> the registry to, but I think 'SQLDataSources' might be safer. :-)
B> Bill
Igor Shekalev, www.sqledit.com - powerful database tools

Retrieving Login Name?

Hi,
I need to know how to retrieve the login name of the user into a local
variable so that I can insert it into a table.
I want to do something like this:
DECLARE @.USERNAME varchar(50)
DECLARE @.OTHERFIELD varchar(50)
SET @.USERNAME = EXEC sp_who ?or something like this?
INSERT INTO MYTABLE(Username, OtherField)
VALUES(@.USERNAME, @.OTHERFIELD)
Can anyone show me the correct way to do this?
JDJoe Delphi wrote:
> I need to know how to retrieve the login name of the user into a local
> variable so that I can insert it into a table.
> SET @.USERNAME = EXEC sp_who ?or something like this?
select @.USERNAME = SYSTEM_USER
SYSTEM_USER is a built-in system function. You can look up "system
functions" in the SQL help for some other values as well.

Retrieving logged in name for data selection from SQL server

I have a login name being displayed when a user logs in, like so: "Welcome: johndoe23"
This is displayed through the use of the <asp:LoginName> tag.

I want to do a query on the database that only selects data relevant to that userName and wish to
assign that logged in username to a variable so we can use an SQL command similar to:"SELECT something WHERE userID=" + loggedInUserName;

According to MSDN:

By default, theLoginName control displays the name contained in theUser property of thePage class. If theSystem.Web.UI.Page.User.Identity.Name property is empty, the control is not rendered.

**END MSDN**

According to that - the following should work:

sqlStr = "SELECT something WHERE userID=" + Page.User.Identity.Name;

You might need apostrophes - not sure, i use the SqlParameter for everything.

I have never used the built in login features, so give it shot.

|||

We actually progressed to using the guid of the logged in user in order to query the database.

This GUID was a big string of letters. We lost all of this weeks code in a hard drive failure this morning unfortunately and are trying to get back on our feet.

I've found loads about finding the Windows identity of the logged in user, but this is not what we want.

Does anyone know how to retrieve this GUID and assign it to a variable?

Many thanks!

Retrieving info from ReportServer tables

Hi,
If I was to query the ReportServer database, can I retrieve info that would
tell me what directories or reports a Group/User has access to? If so, what
tables should I retrieve the data?
Thanks!You need to use the GetPolicies and SetPolicies methods on the web service.
You don't want to do anything directly to the database.
Here's a code snippet giving you an idea of how to use these methods:
private bool AddUserToFolderPolicy(string folder, string user, ref string
errMessage)
{
try
{
//Get the Browser role
Role[]roles = m_ReportingService.ListRoles();
Role browserRole = new Role();
foreach (Role r in roles)
{
if (r.Name == "Browser") browserRole = r;
break;
}
Role[] policyRoles = new Role[1];
policyRoles[0] = new Role();
policyRoles[0] =browserRole;
//Get the current policies of the folder in question
string path = "/" + folder;
bool inheritParent = false;
Policy[] currentPolicies = m_ReportingService.GetPolicies(path, out
inheritParent);
//If the user is currently in the current policy set just return
for(int i=0;i<currentPolicies.Length;i++)
if(currentPolicies[i].GroupUserName == user)
return true;
//Create the new policy array and add the new user
ArrayList arrPolicies = new ArrayList(currentPolicies);
Policy p = new Policy();
p.GroupUserName = user;
p.Roles = policyRoles;
arrPolicies.Add(p);
Policy[] finalPolicies = (Policy[])arrPolicies.ToArray(typeof(Policy));
//Set the policies
m_ReportingService.SetPolicies(path,finalPolicies);
}
catch (Exception e)
{
errMessage = e.Message;
return false;
}
return true;
}
Adrian M.
MCP
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:243F3E14-536F-4438-AAFC-36A814725AFA@.microsoft.com...
> Hi,
> If I was to query the ReportServer database, can I retrieve info that
> would
> tell me what directories or reports a Group/User has access to? If so,
> what
> tables should I retrieve the data?
> Thanks!
>

Retrieving all user rights in SQL 2000/2005

How to retrieve all users (local and domain) in SQL and display there
rights in roles, SUID, database, etc.?Hello,
Take a look into sp_helplogins and sp_helprotect system stored procedures
in books online.
Thanks
Hari
<paul.leistra@.gmail.com> wrote in message
news:1175670638.835844.157430@.p77g2000hsh.googlegroups.com...

> How to retrieve all users (local and domain) in SQL and display there
> rights in roles, SUID, database, etc.?
>

retrieving >1000 records from AD into Crystal

Hello all,

I am having a couple of problems selecting records from Active Directory. What I want to do is create a report that is grouped on a user object field in AD. Our users are not just contained with the 'Users' container, but also in other areas of the directory.

I've come across the problem that AD will only return the first 1000 records when you query it (mentioned here: http://support.businessobjects.com/library/kbase/articles/c2013533.asp). I believe you can get around this by somehow specifying the 'range' property, however I'm not 100% sure how to do this. This is my query as it stands:

Select displayName, ExtensionAttribute3, ExtensionAttribute2,
sAMAccountName, objectClass FROM 'LDAP://dc=blah,dc=blah2,dc=blah3,dc=blah4;;;Range=0-1000;subtree' WHERE objectClass='user'

Whenever I click OK to this I get the error "An invalid directory pathname was passed".

I guess I actually have 2 questions:
1. How do you get the range property to work (i.e. how can I return more than 1000 rows)
2. How can I get the query to search the subtrees of the directory (I think you need to specify the 'subtree' keyword, but again, this isn't working in my query.

Any help would be appreciated!
Cheers,
DanielIf you dont solve the problem search at http://support.businessobjects.com/

Retrieve windows user account

Hi, All
I'm use SQL Server 2005 and want to know is any way can retrieve windows
user account?
I want to retrieve windows user account and insert into the table.
I don't want to use any program lanaguage just want to know SQL 2005 has any
kind of function
or store procedure can support this.
Please give me function or store procedure name and reference.
Thanks for any advice!
Angiangi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/565984cd-60c6-4df7-83ea-
2349b838ccb2.htm
"angi" <angi@.news.microsoft.com> wrote in message
news:OgEkdW0xGHA.2400@.TK2MSFTNGP03.phx.gbl...
> Hi, All
> I'm use SQL Server 2005 and want to know is any way can retrieve windows
> user account?
> I want to retrieve windows user account and insert into the table.
> I don't want to use any program lanaguage just want to know SQL 2005 has
> any kind of function
> or store procedure can support this.
> Please give me function or store procedure name and reference.
> Thanks for any advice!
> Angi
>|||Thanks for Uri.
But it my fault that didn't describe it clearly.
SYSTEM_USER retrive the windows user account who login the SQL Server.
But what I want is not who login to SQL Server and retrive it.
I want is retrive all of windows user account or user name on local server,
on windows server.
Example:
There are 10 windows user accounts on Local Server, such as John, Mary, May
and so on.
They are window server's user and maybe just John has the right to access
SQL Server.
And I want to use some kinds of SP or Function to get John, Mary, May's user
name
(not just login SQL Server's user, not SYSTEM_USER just show
"LOCALHOST\John"),
or some function like LDAP can get Windows user account or user name through
the T-SQL to retrive all of the windows user account or user name.
Such AS:
SELECT xxFn ... AS Username
EXEC xxSp ... AS Username
Username
----
John
Mary
May <-- user's account on windows server (not who login sql server)
...
uh... hope someone know what is my requirement!
Thanks for help!
Angi
"Uri Dimant" <urid@.iscar.co.il> glsD:%236RU9B1xGHA.480@.TK2MSFTNGP06.phx.gbl...[vbc
ol=seagreen]
> angi
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/565984cd-60c6-4df7-83e
a-2349b838ccb2.htm
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:OgEkdW0xGHA.2400@.TK2MSFTNGP03.phx.gbl...
>[/vbcol]

Retrieve windows user account

Hi, All
I'm use SQL Server 2005 and want to know is any way can retrieve windows
user account?
I want to retrieve windows user account and insert into the table.
I don't want to use any program lanaguage just want to know SQL 2005 has any
kind of function
or store procedure can support this.
Please give me function or store procedure name and reference.
Thanks for any advice!
Angiangi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/565984cd-60c6-4df7-83ea-2349b838ccb2.htm
"angi" <angi@.news.microsoft.com> wrote in message
news:OgEkdW0xGHA.2400@.TK2MSFTNGP03.phx.gbl...
> Hi, All
> I'm use SQL Server 2005 and want to know is any way can retrieve windows
> user account?
> I want to retrieve windows user account and insert into the table.
> I don't want to use any program lanaguage just want to know SQL 2005 has
> any kind of function
> or store procedure can support this.
> Please give me function or store procedure name and reference.
> Thanks for any advice!
> Angi
>|||Thanks for Uri.
But it my fault that didn't describe it clearly.
SYSTEM_USER retrive the windows user account who login the SQL Server.
But what I want is not who login to SQL Server and retrive it.
I want is retrive all of windows user account or user name on local server,
on windows server.
Example:
There are 10 windows user accounts on Local Server, such as John, Mary, May
and so on.
They are window server's user and maybe just John has the right to access
SQL Server.
And I want to use some kinds of SP or Function to get John, Mary, May's user
name
(not just login SQL Server's user, not SYSTEM_USER just show
"LOCALHOST\John"),
or some function like LDAP can get Windows user account or user name through
the T-SQL to retrive all of the windows user account or user name.
Such AS:
SELECT xxFn ... AS Username
EXEC xxSp ... AS Username
Username
----
John
Mary
May <-- user's account on windows server (not who login sql server)
...
uh... hope someone know what is my requirement!
Thanks for help!
Angi
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:%236RU9B1xGHA.480@.TK2MSFTNGP06.phx.gbl...
> angi
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/565984cd-60c6-4df7-83ea-2349b838ccb2.htm
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:OgEkdW0xGHA.2400@.TK2MSFTNGP03.phx.gbl...
>> Hi, All
>> I'm use SQL Server 2005 and want to know is any way can retrieve windows
>> user account?
>> I want to retrieve windows user account and insert into the table.
>> I don't want to use any program lanaguage just want to know SQL 2005 has
>> any kind of function
>> or store procedure can support this.
>> Please give me function or store procedure name and reference.
>> Thanks for any advice!
>> Angi
>