Re: How Can We Avoid Creatng Closed Pathes in our Design - Database Discussion Boards

Grant permissions to role on another role

Danzy8329-Dec-11 13:27

29-Dec-11 13:27

Hi all! I would like to know the SQL that can grant select on a Database Role to another role.
I want members of one role to view and see members in another role so thet the first role can select members from the second role. I don't know how to do this. Please help.

Re: Grant permissions to role on another role

coded00729-Dec-11 15:23

coded007

29-Dec-11 15:23

you can create login groups and grant permissions. For Granting permissions please check below link http://msdn.microsoft.com/en-us/library/ms186717.aspx[^]

Mani Prabhakar

How Can We Avoid Creatng Closed Pathes in our Design

yousefshokati29-Dec-11 1:15

yousefshokati

29-Dec-11 1:15

Hi .
I have three entities that are related to each other . So these entities have relationships with each other but the problem here is that their relationships finally make a closed path . Would you please tell me what should i do in situations like this ?
I do not know even this is about normalization or not?

Entities :

Media ( MediaID , MediaName , ...,GroupID)
Articles (ArticleID , Title,... ,GroupID)
Groups (GroupID , GroupName ,... )

Article_Media ( MediaID, ArticleID, ...)

thanks
Give me the solution because in update we can not update the records

Re: How Can We Avoid Creatng Closed Pathes in our Design

Chris Meech29-Dec-11 7:04

Chris Meech

29-Dec-11 7:04

The only thing about an update that I can see failing whould be if you are updating one of the ID columns. Assuming that these are keys of some sort, you shouldn't be doing that anyway. If you can provide more information about what fails, that would be useful. Thanks. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Re: How Can We Avoid Creatng Closed Pathes in our Design

SilimSayo29-Dec-11 8:43

SilimSayo

29-Dec-11 8:43

Can you describe in some detail what Media and Articles are and how they relate to each other? Can you also describe what the Groups entity is about?

Re: How Can We Avoid Creatng Closed Pathes in our Design

Eddy Vluggen29-Dec-11 8:54

Eddy Vluggen

29-Dec-11 8:54

You need a group before you can create Media, Articles or a.. group? I hope that GroupID is nullable?

That's the only circular reference, the one in Groups. If the GroupID is nullable, you create one, then Media and Articles.

yousefshokati wrote:
I do not know even this is about normalization or not?

It is. Those rules make you think about your design and gives you a neat way of getting some relevant structure in there. As it's now, it's merely a collection of field-names.

yousefshokati wrote:
Give me the solution

The solution is easy, you need to rethink your design. About what each entity and each attribute are going to represent, and how they relate. Normalization is about getting that structure.

Good luck.

Bastard Programmer from Hell Suspicious | :suss:

Re: How Can We Avoid Creatng Closed Pathes in our Design

yousefshokati30-Dec-11 22:56

yousefshokati

30-Dec-11 22:56

Hi And tanks for your answer .
You say that When we design our database , we should design in a way that we do not have any closed path ?
Am I right?
But if we have 2 different relationship between 2 entities , then what should we do . Consider the following example :

We have MembersTable Which represents the members of teams and we have TeamsTable That represents the Team Entity Now We have to relations between these 2 . The First relation is "Plays For" which means that "Member Plays for team A for example " and the second is "Captain Of" which stands for "Members x is captain of team A" . Now if we update one of these FK s that are in tables for creating the relationships then we will get an error , won't be?

Re: How Can We Avoid Creatng Closed Pathes in our Design

Eddy Vluggen31-Dec-11 4:31

Eddy Vluggen

31-Dec-11 4:31

yousefshokati wrote:
Hi And tanks for your answer

You're welcome Smile | :)

yousefshokati wrote:
You say that When we design our database , we should design in a way that we do not have any closed path ?

Am I right?

I'm saying that you should normalize the design; that will eliminate all weird stuff from the design and give you something you can work with. I know that it's a bit of an investment in terms of time, but it repays itself rather quickly.

yousefshokati wrote:
We have MembersTable Which represents the members of teams and we have TeamsTable That represents the Team Entity Now We have to relations between these 2 . The First relation is "Plays For" which means that "Member Plays for team A for example " and the second is "Captain Of" which stands for "Members x is captain of team A" . Now if we update one of these FK s that are in tables for creating the relationships then we will get an error , won't be?

Yes, you will. Normalization would state that you don't need the second entity.

MembersTable
MemberId
MemberName
IsCaptain

Best wishes for the year to come Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

Sending Column Name From SP to C# App

AmbiguousName28-Dec-11 1:04

AmbiguousName

28-Dec-11 1:04

hello guys... I have this stored proc which gets some values from table but in addition, it calculates some values like %age of result. The question is: how can I send this value <b>WITH</b> a column name like <i>Percantage</i>. Here is the query for stored proc but I dont know what to add

<pre lang="SQL">
CREATE PROCEDURE [dbo].[GetStudents] AS
SELECT
s.StudentId, s.FirstName,s.SurName,
( ((s.ObtMarks) / (s.TotalMarks)) * 100) --this is the value I want to send with column name

FROM Students s

GROUP BY
s.StudentId, s.FirstName,s.SurName
</pre>

-- modified 28-Dec-11 7:30am.

Re: Sending Column Name From SP to C# App

hypermellow28-Dec-11 2:03

hypermellow

28-Dec-11 2:03

Hi,

try giving your calculate column value an alias.
Then you can reference it by name in your c# datatable.

I've modified (but no tested or ran) your procedure.

Hope it helps,

SQL

CREATE PROCEDURE [dbo].[GetStudents] AS
SELECT
s.StudentId, s.FirstName,s.SurName,
( ((s.ObtMarks) / (s.TotalMarks)) * 100) AS PercentageValue

FROM Students s

GROUP BY
s.StudentId, s.FirstName,s.SurName

Re: Sending Column Name From SP to C# App

AmbiguousName28-Dec-11 2:18

AmbiguousName

28-Dec-11 2:18

just put
colName = Calculated Value

and you are good to go Smile | :)

Re: Sending Column Name From SP to C# App

Karthik Harve28-Dec-11 18:44

Karthik Harve

28-Dec-11 18:44

just change the query like this..

SQL

SELECT
s.StudentId, s.FirstName,s.SurName,
( 'Percentage - ' + ((s.ObtMarks) / (s.TotalMarks)) * 100) as 'Percentage'

hope it works..

with regards
Karthik Harve

Luc Pattyn28-Dec-11 23:41

Luc Pattyn

28-Dec-11 23:41

can be a useful keyword.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Re: AS

SilimSayo29-Dec-11 4:44

SilimSayo

29-Dec-11 4:44

Provided you don't misspell it by adding an extra 's' Smile | :)

how to loop over results of a select statement ?

yousefshokati27-Dec-11 19:40

yousefshokati

27-Dec-11 19:40

I have a select query that returns a some rows . i want to send
each of the rows in returned result to a function but i do not know how ?
can you help me
"Note that i do not want to use Cursor "

  select  PersonelCode , ... 
  from    PersonnelTable  

-------------------------------------
and the function Need the PersonelCode.For each row this function should be executed 

Create Function MyFunction ( @PersonelCode  )
begin 
  body of the function 
end

Re: how to loop over results of a select statement ?

gvprabu27-Dec-11 21:25

gvprabu

27-Dec-11 21:25

Hi,

Check the following Script,

It will be use full for ur Query..........

SQL

SET NOCOUNT ON
-- Declaration
DECLARE @Employee TABLE(ID INT , EmpName  VARCHAR(40))
DECLARE @I INT, @Count INT, @Temp VARCHAR(50)

SET @I = 1
-- Data
INSERT INTO @Employee(ID,EmpName)
SELECT 123,'Prabu'
UNION ALL SELECT 234, 'Raja'
UNION ALL SELECT 236, 'Kartik'
UNION ALL SELECT 1234, 'Venkat'

-- TSQL Script
SELECT @Count=COUNT(*) FROM @Employee
PRINT '~~~~~~~~~~~~~~~~~'
PRINT 'Employee Details'
PRINT '~~~~~~~~~~~~~~~~~'
WHILE @I <= @Count
BEGIN
	WITH Emp AS 
	(
		SELECT row_number() OVER ( ORDER BY ID ) 'RowNum', ID, EmpName
		FROM  @Employee
	)
	SELECT  @Temp =CAST(ID AS VARCHAR) +' - '+EmpName 
	FROM Emp
	WHERE RowNum =@I
	PRINT @Temp
	SET	@I=@I+1
END
PRINT '~~~~~~~~~~~~~~~~~'
SET NOCOUNT OFF

Re: how to loop over results of a select statement ?

jschell29-Dec-11 14:39

jschell

29-Dec-11 14:39

And the following doesn't do it?

select PersonelCode , MyFunction(PersonelCode), ...
from PersonnelTable

how to loop over results of a select statement ?

yousefshokati27-Dec-11 19:39

yousefshokati

27-Dec-11 19:39

I have a select query that returns a some rows . i want to send
each of the rows in returned result to a function but i do not know how ?
can you help me

  select  PersonelCode , ... 
  from    PersonnelTable  

-------------------------------------
and the function Need the PersonelCode.For each row this function should be executed 

Create Function MyFunction ( @PersonelCode  )
begin 
  body of the function 
end

Re: how to loop over results of a select statement ?

Shameel27-Dec-11 19:42

Shameel

27-Dec-11 19:42

Use a Cursor[^]

Re: how to loop over results of a select statement ?

PIEBALDconsult28-Dec-11 2:33

PIEBALDconsult

28-Dec-11 2:33

How about

SELECT PersonelCode ,MyFunction(PersonelCode), ... FROM PersonnelTable

Microsoft SQL Server 2008 R2 (between)

Framework .l.27-Dec-11 16:59

Framework .l.

27-Dec-11 16:59

i'm trying to filter by date using query but error.

select s.IdStaff, s.NamaStaff, 
	sum(pd.Qty *
	case s.posisi
		when 'K.Teknisi' then kd.komisiteknisi
		when 'K.Controller' then kd.komisitelemarketing
		when 'K.Manager' then kd.komisicontroller
		when 'K.TeleMarketing' then kd.komisimanager
		when 'K.Cadangan' then kd.komisicadangan
	end) Insentif
from staff s inner join penjualan p on s.idstaff = p.idstaff
	inner join penjualandetail pd on p.idpenjualan = pd.idpenjualan
	inner join komisidetail kd on pd.idbarang = kd.idbarang
	where p.tglpenjualan between 12/1/2011 and 12/30/2011
group by s.idstaff, s.namastaff

error message : Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with int

Re: Microsoft SQL Server 2008 R2 (between)

R. Giskard Reventlov27-Dec-11 17:17

R. Giskard Reventlov

27-Dec-11 17:17

Use quotes around the dates and check that the format of the dates is valid.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Re: Microsoft SQL Server 2008 R2 (between)

Framework .l.27-Dec-11 17:21

Framework .l.

27-Dec-11 17:21

perfect !!
wondering why i always forgot that even after my teacher told me twice.

how about the date format?

is it YYYY/MM/DD or MM/DD/YYYY or else?
thanks before.. Laugh | :laugh: