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 the ID of the last record inserted

I would appreciate help with retriving the ID of the last record inserted. Have spent considerable time in forums and google but can't find anything that works for me.

Here is my VB Code

Dim queryString As String = "INSERT INTO [DUALML] ([UseriD], [Company]) VALUES (@.UseriD, @.Company)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_useriD As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_useriD.ParameterName = "@.UseriD"
dbParam_useriD.Value = useriD
dbParam_useriD.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_useriD)
Dim dbParam_company As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_company.ParameterName = "@.Company"
dbParam_company.Value = company
dbParam_company.DbType = System.Data.DbType.[String]
dbCommand.Parameters.Add(dbParam_company)

Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try

Return rowsAffected
End Function

I know from SQL side you can get the inserted row in the insert/update trigger. So maybe you can put the inserted row into a table in trigger, and then get it immediately after the insert command? For example, you define a trigger like this:

declare trigger trg_DUALML on DUALML for insert
as
BEGIN
if object_id('tbl_temp')is not null
drop table tbl_temp
select * into tbl_temp from inserted
end

Then you can query the tbl_temp table to get the ID of the row:

select UserID from tbl_temp

|||

Use StordProcedure.

@.UseerId int,

@.Company Varchar(150)

@.Id (Your column for the new Id) int OUTPUT

INSERT INTO DUALML(UserId,Compnay) VALUES(@.UserId,Company)

SELECT @.id = SCOPE_IDENTITY()

Then, in your application code, put an output parameter to get the new inserted id.

|||Lori_Jay Said:
>>>
declare trigger trg_DUALML on DUALML for insert
as
BEGIN
if object_id('tbl_temp')is not null
drop table tbl_temp
select * into tbl_temp from inserted
end
>>
I hate to say it, but this is actually a VERY bad move. One of the worst I've seen in quite a while - it's the proverbial sledgehammer to kill the nail. (Only this sledgehammer is actually a bulldozer.)

It IS creative, and shows problem solving, etc - but will un-necessarily gum up the DB with artifacts/bloat, is less performant, and can potentially lead to major problems down the road.

SQL Server (and all other RDBMS' out there) provide explicit ways to get this info.

Check in to @.@.IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT() - they all have their own subtle differences/scopes and provide the functionality needed.

Che3358's example is good.|||

thanks for that

I will give it a go

sql

Retriving Position In A Field

Hi All.
Is there a way to retrieve the position of a word, phrase or sign in a field?
For example, Field content is ABCDEFG1239/1002STJ
I would like to get the exact position of / which will be position 12.
Thank you.
Best regardsThe patindex function should work for you here. But, it will only give you the location of the first one. Syntax:
PATINDEX ( '%pattern%' , expression )|||I wonder what he difference is?

SELECT PATINDEX ( '%/%' , 'ABCDEFG1239/1002STJ' )
SELECT CHARINDEX ( '/','ABCDEFG1239/1002STJ' )|||PatIndex() allows SQL Server regular expressions. CharIndex() only allows literals.

-PatP|||Who was that masked man?|||Hello All.

Thank you for your reply. I couldn't thank of you earlier because I was away to Europe on a business trip.

I will try out your solutions today.

Once again. Thank you.

Best regards

retriving gender from database in a radio button

i am storing gender in the database.
i want to retrive it in one of the radiobuttons for male and female already present on the form . how can i?

related radio button should be highlighted while clicking on the button........

Shubhada

Retriving Deleted record from database

Hi friends

I have a bit problem here

Just I want to get back all deleted record of database

How do I perform this task?
If It is possible then plz help me out?

Thanks in Advance

Khan

if you have the backup which was taken before the datas were deleted you can make use of it and restore it any other server and retrieve your records................|||

There is no built-in command to undelete. However, you could use Log Explorer.

http://lumigent.com/products/le_sql.html

Retriving data with same ID into single row

Hi,
I have a problem with retriving data from one table.
Table looks like this:
ID CONTACT EMPLOYEE_ID
And now, one employee can how more than one contact, like this
ID CONTACT EMPLOYEE_ID
15 e-mail 553
16 phone 553
..and so on.
How can I retrive this contacts, for same employee into single row, and divi
ded with 'coma'?
Now a can retrive them but I recieve multiple rows?
Thanks for helpHi
Do such reports on the client side.
It you want to do that by T-SQL be aware that it is not relyable solution
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @.id int)
returns varchar(100)
as
begin
declare @.w varchar(100)
set @.w=''
select @.w=@.w+coalesce(t,'')+',' from w where id=@.id
return @.w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"s3v3n" <s3v3n.1yuclt@.mail.codecomments.com> wrote in message
news:s3v3n.1yuclt@.mail.codecomments.com...
> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ..and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>|||Check this good article with source code "Returning a Comma-Delimited List o
f
Related Records"
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"s3v3n" wrote:
> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ...and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>|||[Based on the 4guysrolla article .. ]
Create table empTest
(
[ID] int identity,
Contact varchar(100),
Employee_Id int
)
Go
Insert into empTest values ( 'abc@.email.com', 554)
Insert into empTest values ( '090909090', 554)
Go
Create function dbo.udf_GetEmpDetails(@.EmpID int)
Returns Varchar(1000) as
BEGIN
DECLARE @.Contact varchar(1000)
SELECT @.Contact = COALESCE(@.Contact + ', ', '') + s.Contact
FROM empTest s
WHERE s.EMPLOYEE_ID = @.EmpID
RETURN @.Contact
END
Go
Select distinct Employee_ID,
dbo.udf_GetEmpDetails(EMPLOYEE_ID) as ListOfContacts
From empTest f
Go
Hope this helps!
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"s3v3n" wrote:

> Hi,
> I have a problem with retriving data from one table.
> Table looks like this:
> ID CONTACT EMPLOYEE_ID
> And now, one employee can how more than one contact, like this
> ID CONTACT EMPLOYEE_ID
> 15 e-mail 553
> 16 phone 553
> ...and so on.
> How can I retrive this contacts, for same employee into single row, and
> divided with 'coma'?
> Now a can retrive them but I recieve multiple rows?
> Thanks for help
>
> --
> s3v3n
> ---
> Posted via http://www.codecomments.com
> ---
>

Retriving data from SQL text field

I have a text column in my db which stores more than 8000 characters. When I retrieve the values from the column in query analyzer (I have set the output buffer to 8000), it only shows me first 8000 chars only. How do I display all the text from the text field?Use a parameter.|||SQL QA does not allow declaring local variables with text type. I am trying to pull the SQL text filed's all the text. Declaring or converting to varchar will limit it to 8000 characters only.

An example will be very handy..

Thxsql

Retriving data from database to xml

Hi,
I'm using SQL Server 2000 and I'm having some dificulties to produce the
rigth xml document using the FOR XML clause of SELECT.
The SELECT that I'm using is the following:
select 1 as Tag, NULL as Parent, Header.TaxRegistrationNumber as
[Header!1!TaxRegistrationNumber!element], Header.CompanyName as
[Header!1!CompanyName!element], NULL as
[CompanyAddress!2!BuildingNumber!element], NULL as
[CompanyAddress!2!AddressDetail!element], Header.FiscalYear as
[Header!1!FiscalYear!element]
from Header LEFT JOIN CompanyAddress
UNION
select 2, 1, Header.TaxRegistrationNumber, Header.CompanyName,
CompanyAddress.BuildingNumber, CompanyAddress.AddressDetail, Header.FiscalYear
from from Header LEFT JOIN CompanyAddress
FOR XML EXPLICIT
and the xml result is:
<Header>
<TaxRegistrationNumber>123456787</TaxRegistrationNumber>
<CompanyName>dese</CompanyName>
<FiscalYear>2007</FiscalYear>
<CompanyAddress>
<BuildingNumber></BuildingNumber>
<AddressDetail>250 APT 751</AddressDetail>
</CompanyAddress>
</Header>
The problem is with the <FiscalYear> tag that appears before the
<CompanyAddress> tag and I want that the <FiscalYear> tag appears after the
end of the </CompanyAddress> tag, like this:
<Header>
<TaxRegistrationNumber>123456787</TaxRegistrationNumber>
<CompanyName>dese</CompanyName>
<CompanyAddress>
<BuildingNumber></BuildingNumber>
<AddressDetail>250 APT 751</AddressDetail>
</CompanyAddress>
<FiscalYear>2007</FiscalYear>
</Header>
Is there any way to build this kind of xml result using FOR XML clause?
Thanks.
Hello Ferreira,
You will probably need to make a it level 3 value (e.g., [header!3!FiscalYear!Ement)
where 3 is a child of 1.
kt

Retriving data from database to xml

Hi,
I'm using SQL Server 2000 and I'm having some dificulties to produce the
rigth xml document using the FOR XML clause of SELECT.
The SELECT that I'm using is the following:
select 1 as Tag, NULL as Parent, Header.TaxRegistrationNumber as
[Header!1!TaxRegistrationNumber!element]
, Header.CompanyName as
[Header!1!CompanyName!element], NULL as
[CompanyAddress!2!BuildingNumber!element
], NULL as
[CompanyAddress!2!AddressDetail!element]
, Header.FiscalYear as
[Header!1!FiscalYear!element]
from Header LEFT JOIN CompanyAddress
UNION
select 2, 1, Header.TaxRegistrationNumber, Header.CompanyName,
CompanyAddress.BuildingNumber, CompanyAddress.AddressDetail, Header.FiscalYe
ar
from from Header LEFT JOIN CompanyAddress
FOR XML EXPLICIT
and the xml result is:
<Header>
<TaxRegistrationNumber>123456787</TaxRegistrationNumber>
<CompanyName>dese</CompanyName>
<FiscalYear>2007</FiscalYear>
<CompanyAddress>
<BuildingNumber></BuildingNumber>
<AddressDetail>250 APT 751</AddressDetail>
</CompanyAddress>
</Header>
The problem is with the <FiscalYear> tag that appears before the
<CompanyAddress> tag and I want that the <FiscalYear> tag appears after the
end of the </CompanyAddress> tag, like this:
<Header>
<TaxRegistrationNumber>123456787</TaxRegistrationNumber>
<CompanyName>dese</CompanyName>
<CompanyAddress>
<BuildingNumber></BuildingNumber>
<AddressDetail>250 APT 751</AddressDetail>
</CompanyAddress>
<FiscalYear>2007</FiscalYear>
</Header>
Is there any way to build this kind of xml result using FOR XML clause?
Thanks.Hello Ferreira,
You will probably need to make a it level 3 value (e.g., [header!3!FiscalYea
r!Ement)
where 3 is a child of 1.
kt

retriving data from a temporal table

Hi ,
I've created a stored procedure wich creates a temporal table (called
#results) , then i fill the table with data and finally at the end of the
procedure i make a "Select * FROM #Results" .
When i execute the procedure from the query analizer i can get the data
without any problem. But if i try to get the data into a visual basic ADO
recordset it always fails. I think the problem is because i'm using a
temporal table , but i need to use that solution.
If someone could give me one solution to get the data of a temporal table
into a recordset i'd been thankful.
Thanks in advance for you answers and pardon for my bad english.hi
u can use temp table when u call from VB program, but u need to do everythin
g
1. Create Table
2. Insert Data
3. Retrive data
in the same SP. else the data will be deleted / scope is lost
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Jorge Lozano" wrote:

> Hi ,
> I've created a stored procedure wich creates a temporal table (called
> #results) , then i fill the table with data and finally at the end of the
> procedure i make a "Select * FROM #Results" .
> When i execute the procedure from the query analizer i can get the data
> without any problem. But if i try to get the data into a visual basic ADO
> recordset it always fails. I think the problem is because i'm using a
> temporal table , but i need to use that solution.
> If someone could give me one solution to get the data of a temporal table
> into a recordset i'd been thankful.
> Thanks in advance for you answers and pardon for my bad english.|||My guess is that it will work if you add SET NOCOUNT ON in the beginning of
your stored procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jorge Lozano" <JorgeLozano@.discussions.microsoft.com> wrote in message
news:FED3495C-D379-457D-AE02-D9B188B156BC@.microsoft.com...
> Hi ,
> I've created a stored procedure wich creates a temporal table (called
> #results) , then i fill the table with data and finally at the end of the
> procedure i make a "Select * FROM #Results" .
> When i execute the procedure from the query analizer i can get the data
> without any problem. But if i try to get the data into a visual basic ADO
> recordset it always fails. I think the problem is because i'm using a
> temporal table , but i need to use that solution.
> If someone could give me one solution to get the data of a temporal table
> into a recordset i'd been thankful.
> Thanks in advance for you answers and pardon for my bad english.|||It worked Perfect!!!
Thanks a loot Tibor i owe you a very big beer.
"Tibor Karaszi" wrote:

> My guess is that it will work if you add SET NOCOUNT ON in the beginning o
f your stored procedure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jorge Lozano" <JorgeLozano@.discussions.microsoft.com> wrote in message
> news:FED3495C-D379-457D-AE02-D9B188B156BC@.microsoft.com...
>|||Watch out. I'm an excellent beer drinker ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jorge Lozano" <JorgeLozano@.discussions.microsoft.com> wrote in message
news:02F4D367-6A89-49C9-9971-BE5BA4927491@.microsoft.com...
> It worked Perfect!!!
> Thanks a loot Tibor i owe you a very big beer.
> "Tibor Karaszi" wrote:
>

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

Retriving an xml string stored in varchar(max)

I try to retrive an xml portion (<points><point><x>1</x></point></points>) stored in a varchar(max) column, this is my code

dr = cmd.ExecuteReader();_xmlFile = dr.GetSqlString(dr.GetOrdinal("XmlJoin")).ToString();Label1.Text = _xmlFile;

and this is what I get "12"
Maybe I missed something to get the whole XML StringWhat do you mean by getting "12"? It confused me...|||

mehdi_tn:

I try to retrive an xml portion (<points><point><x>1</x></point></points>) stored in a varchar(max) column, this is my code

dr = cmd.ExecuteReader();
_xmlFile = dr.GetSqlString(dr.GetOrdinal("XmlJoin")).ToString();
Label1.Text = _xmlFile;

and this is what I get "12"
Maybe I missed something to get the whole XML String

It seems to me you could do it like this:

_xmlFile = dr["XmlJoin"].ToString();

|||Thanks for answering, In fact I placed the retrived XMl in a label and the label showed "1"
When debugin I founded the whole XML in the variable. The problem was from the label try this :

Label1.Text="<;x>1</x>";// this will show 1

Bizarre this controlsql