|
for each record in X, the amount of numerical feild correspond to sum of records "not more than 900 record" in Z. ie: table Z has more than 900 records (unlimited), but the sum in X is limited to a max number of 900 in Z.
Table Z is subjected to quireis, that accour not more than once a day.
Regards,
0 will always beats the 1.
|
|
|
|
|
hai,
i had wrote one storedprocedure for deleting the current leaves.but when i am debugging i am getting the error as invalid object name.i am posting the code.
USE [HDCHRMSDB]
GO
/****** Object: StoredProcedure [dbo].[apDeleteCurrentYearLeaveSummery] Script Date: 01/04/2011 12:23:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[apDeleteCurrentYearLeaveSummery]
As
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DELETE FROM CurrentYearLeaveSummery Where Year=2011
END
please suggest any answer to avoid that error.urgent for me.
|
|
|
|
|
"Year" could be a reserved word.
If you have a column with that name, use [Year] instead.
Otherwise, it is a function, i.e. Year([My Column]).
|
|
|
|
|
First of all it's a Repost[^].
I think answer you've got of adding dbo.[name of table] may solve your problem.
Rather then posting code in different forum Discuss with people answered you over there.
|
|
|
|
|
Hello,
An index is created with two not null columns of table. The index created is a unique non-clustered index.
When trying to re-index this field, it takes a couple of hours. Also, do indexes work if there are two columns defined in a index?
Thank you and Happy New Year!
|
|
|
|
|
How big is your table?
Yes an index on two columns do work.
|
|
|
|
|
With out index, it become not that efficient when finding help.
The TOC (or bookmarks in pdf) is not so efficient.
What worse is html, I have to return to TOC to relocate.
BTW, index is removed by default in visual studio 2010.
Why index is gone?
|
|
|
|
|
Hi, I've searched and haven't found a relevant answer to this 'simple' problem.
I have 3 tables, one of which is a junction table between the other two:
Users
-----
UserId
UserName
Roles
-----
RoleId
RoleName
UserRoles
---------
UserRoleId
UserId
RoleId
There are FK relationships between UserRoles and the other 2 tables.
Problem statement:
What I want to do is update the user's RoleId in the UserRoles table if I only have the person's RoleName and UserName.
Here's what I'm starting with code-wise (MSSQL):
<br />
update a<br />
set a.RoleId = ?? Set to what?<br />
FROM UserRoles a<br />
inner join Users b on a.UserId = b.UserId<br />
inner join Roles c on a.RoleId=c.RoleId<br />
where c.RoleName= 'Administrator' AND b.UserName='someuser'<br />
Further information:
I'm trying to do this within an update method call within an ASP.NET GridView control. This information may be a bit more than what's needed but I thought I would mention it just in case it may offer a different solution.
Thanks for any assistance in advance.
---
Stan
|
|
|
|
|
Sorry, I'm missing the logic in your question.
This is what I read:
we know the UserName (and hence the UserId)
we know the RoleName (and hence the RoleId)
I see two possibilities:
1. the UserRoles table does not contain a matching UserId,RoleId record; you have to insert one.
2. the UserRoles table does contain a matching UserId,RoleId record. Now what needs to be updated???
Or is it you want the existing UserId,RoleId record to get another value of UserRoleId for some mysterious reason?
|
|
|
|
|
Your last point is what I'd like to find an answer for: I want to UPDATE the RoleId for an EXISTING RoleId,UserId combo.
In short, I want to update the junction table.
|
|
|
|
|
You failed to make it any clearer. This is my only gamble at what it is you might possibly want (not tested!):
update a
set a.RoleId = d.RoleId
FROM UserRoles a
inner join Users b on a.UserId = b.UserId
inner join Roles c on a.RoleId = c.RoleId
inner join Roles d
where c.RoleName= 'Administrator' AND b.UserName='someuser' AND d.RoleName='AnotherRole'
|
|
|
|
|
Thanks for your help. I apologize I hadn't been able to properly characterize my problem. In short: I want to update a junction table (RoleId and UserId) when I only know the UserName and RoleName.
I'll play around with variations of what you provided and search a bit further online. I'll post whatever resolution(s) I come up with.
|
|
|
|
|
I think you're thinking a bit wrong here (Alternatively have I understood you wrong).
There's no real point in updating the UserRoles table. You want to add or remove roles a user have. There's nothing to update really.
So you should rather think along the line of Insert and Delete instead.
For example something similar to:
Insert Into UserRoles (UserID,RoleID)
Values ((Select Userid from Users Where UserName = 'someuser'),(Select RoleID from Roles Where Rolename = 'Administrator')) Or:
Delete From UserRoles
Where Userid = (Select Userid from Users Where UserName = 'someuser')
And Roleid = (Select RoleID from Roles Where Rolename = 'Administrator')
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
OK, thank you, I'll think along the lines of DELETE and INSERT.
The next step is to have this work with an SqlDataSource1_Updating method with my Gridview control in C#.
Thanks to all for your help.
|
|
|
|
|
i'm trying to execute the "sp_databases" proc in sql sever 2008 express as "sa" but it doesn't return any value even though there
are two customer created DBs available. what must be the problem.. any idea about this issue.
thanx in advance.
|
|
|
|
|
what happens if you execute
select * from sys.databases
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
hmm i get all the DBs in the server . at the same time a found some articles saying that there is a permission problem
i used
USE master;
GRANT EXECUTE ON OBJECT::dbo.sp_databases
TO public;
GO
but still get the "Cannot find the object 'sp_databases', because it does not exist or you do not have permission."
message.
and also in the same machine i have another standard instance is installed and running, but there i get the list of DBs with
this sp_databases.
have any idea...
thanx in advance..
|
|
|
|
|
try
exec master..sp_database
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
exec master..sp_databases executes but it shows nothing
|
|
|
|
|
|
sys.database is a system table introduced with SQL 2005 and used in sql 2008
where sp_database is an actual stored procedure that has been present since sql server 2000 I believe
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
I guess I will wake up sooner or later. 
The sp is sys.sp_databases
|
|
|
|
|
oh that something new learnt today it also works executing it on sys.sp_databases too 
As barmey as a sack of badgers
Dude, if I knew what I was doing in life, I'd be rich, retired, dating a supermodel and laughing at the rest of you from the sidelines.
|
|
|
|
|
using RowNum? who can tell me the RowId in last Column? what's effect?
|
|
|
|
|
openboy2010 wrote: using RowNum?
SELECT ROWNUM, T.*
FROM TEST_TABLE T
ORDER BY ROWNUM
openboy2010 wrote: who can tell me the RowId in last Column?
SELECT C.ROWYOURBOAT
FROM
(SELECT COUNT(*) AS ROWCOUNT
FROM TEST_TABLE T
) A,
(SELECT ROWNUM AS ROWCOUNT, ROWID AS ROWYOURBOAT
FROM TEST_TABLE T
ORDER BY ROWNUM
) C
WHERE A.ROWCOUNT = C.ROWCOUNT
openboy2010 wrote: what's effect?
I don't know what your are asking here...
modified on Thursday, December 30, 2010 11:03 AM
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
