|
Hi guys,
I am looking for the best ways to create an api which will monitor the database table row version.
if this api found any row update manually (i.e., directly login to SQL server) than it will send an acknowledgement with the following information:
(1) LogIn detail of the data server.
(2) Access database name with datatime.
(3) Affected table name, row version and row number as well.
I will be glad for your any suggestion / ides on that.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
I currently use
HOST_NAME() and
APP_NAME() in a trigger for insert, update and delete to monitor for anything that wasn't supposed to happen. You can then include any of the detail from INSERTED and DELETED tables as well as connection info.
HTH
|
|
|
|
|
Hi,
Thanks for the replay, well if we consider the scenario given below:
if any user:
1. logIn to the database -> open the table in design mode--> block the trigger.
2. Update a single column value.
3. Unblock the trigger - > Save the table.
I am thinking on how could I detect the changes from my C# application.
Thanks once again for your valuable input.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
I think your app would need to hold a copy of the table and constantly compare, and I doubt that's worth the trouble, so don't bother trying.
You can't protect against a malicious authorized user. There are also times when you legitimately want to correct some data and make it appear to have always been correct.
In my opinion, an app should never hold onto data for longer than it needs it; when it needs the data again it should query it again.
|
|
|
|
|
Doing a trace isn't the same as an intruder-protection, and those are both different from versioning a row. I'd suggest you create a fingerprint for the data.
What are you trying to achieve/prevent exactly?
Bastard Programmer from Hell 
|
|
|
|
|
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.
|
|
|
|
|
|
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
|
|
|
|
|
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. 
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]
|
|
|
|
|
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?
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
|
yousefshokati wrote: Hi And tanks for your answer
You're welcome
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
Bastard Programmer from Hell
|
|
|
|
|
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.
|
|
|
|
|
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,
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
|
|
|
|
|
just put
colName = Calculated Value
and you are good to go
|
|
|
|
|
just change the query like this..
SELECT
s.StudentId, s.FirstName,s.SurName,
( 'Percentage - ' + ((s.ObtMarks) / (s.TotalMarks)) * 100) as 'Percentage'
hope it works..
with regards
Karthik Harve
|
|
|
|
|
can be a useful keyword.
|
|
|
|
|
Provided you don't misspell it by adding an extra 's'
|
|
|
|
|
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
|
|
|
|
|
Hi,
Check the following Script,
It will be use full for ur Query..........
SET NOCOUNT ON
DECLARE @Employee TABLE(ID INT , EmpName VARCHAR(40))
DECLARE @I INT, @Count INT, @Temp VARCHAR(50)
SET @I = 1
INSERT INTO @Employee(ID,EmpName)
SELECT 123,'Prabu'
UNION ALL SELECT 234, 'Raja'
UNION ALL SELECT 236, 'Kartik'
UNION ALL SELECT 1234, 'Venkat'
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
|
|
|
|
|
And the following doesn't do it?
select PersonelCode , MyFunction(PersonelCode), ...
from PersonnelTable
|
|
|
|
|
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
|
|
|
|
|
|
How about
SELECT PersonelCode ,MyFunction(PersonelCode), ... FROM PersonnelTable
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
