'Failed to enable constraints' Error on TableAdapter.Fi ll

[abehm]

Hey all,
I'm getting this error only when I have two similar entries in an login_audit table where the only difference is the actionhost and date. The query works fine in sqlserver. just not in the dataset.

What can I do to solve this problem?

The table structure is:

userID(Guid)
username (nvarchar(50))
firstname (nvarchar(50))
lastname(nvarchar(50))
email(nvarchar(MAX))
isAdmin bit
Action (nvarchar(20))
ActionHost(nvarchar(30))
Date datetime
 

The entries:

860B3D0F-D166-4388-82C0-EA5D77C2E231
newuser
New
User
[e-mail]
1
Logged In    
LOCALMACHINE
Apr  3 2008  3:24PM
 
860B3D0F-D166-4388-82C0-EA5D77C2E231
newuser
New
User
[e-mail]
1
Logged In
SERVERNAME
Apr  3 2008  3:23PM
 

And this is the stored procedure:

ALTER PROCEDURE [Security].[User_SelectDepartmentMembersLoginInfo]
    @UserID uniqueidentifier
AS
BEGIN
SELECT DISTINCT [Security].[User].UserID, Username, FirstName, LastName, Email, IsSysAdmin, [Action], ActionHost, CONVERT(varchar(MAX),MAX(Date)) AS LastLoggedIn
FROM [Security].[User] LEFT OUTER JOIN Audit.[Login] ON [Security].[User].UserID = Audit.[Login].UserID LEFT OUTER JOIN [Security].DepartmentMembers ON [Security].[User].UserID = DepartmentMembers.UserID
    WHERE ((@UserID IN (SELECT UserID FROM [Security].[User] WHERE IsSysAdmin = 'True'))
    OR (DepartmentMembers.DepartmentID = (SELECT DepartmentID FROM [Security].DepartmentMembers WHERE UserID = @UserID AND IsDeptAdmin = 'True'))
    AND (([Action] = 'Logged In') OR ([Action] IS NULL)))
    GROUP BY [Security].[User].UserID, Username, FirstName, LastName, Email, IsSysAdmin, [Action], ActionHost
    ORDER BY LastLoggedIn DESC, Username
 
END
 

[ck9663]

Hey all,
I'm getting this error only when I have two similar entries in an login_audit table where the only difference is the actionhost and date. The query works fine in sqlserver. just not in the dataset.

What can I do to solve this problem?

The table structure is:

userID(Guid)
username (nvarchar(50))
firstname (nvarchar(50))
lastname(nvarchar(50))
email(nvarchar(MAX))
isAdmin bit
Action (nvarchar(20))
ActionHost(nvarchar(30))
Date datetime
 

The entries:

860B3D0F-D166-4388-82C0-EA5D77C2E231
newuser
New
User
[e-mail]
1
Logged In    
LOCALMACHINE
Apr  3 2008  3:24PM
 
860B3D0F-D166-4388-82C0-EA5D77C2E231
newuser
New
User
[e-mail]
1
Logged In
SERVERNAME
Apr  3 2008  3:23PM
 

And this is the stored procedure:

ALTER PROCEDURE [Security].[User_SelectDepartmentMembersLoginInfo]
    @UserID uniqueidentifier
AS
BEGIN
SELECT DISTINCT [Security].[User].UserID, Username, FirstName, LastName, Email, IsSysAdmin, [Action], ActionHost, CONVERT(varchar(MAX),MAX(Date)) AS LastLoggedIn
FROM [Security].[User] LEFT OUTER JOIN Audit.[Login] ON [Security].[User].UserID = Audit.[Login].UserID LEFT OUTER JOIN [Security].DepartmentMembers ON [Security].[User].UserID = DepartmentMembers.UserID
    WHERE ((@UserID IN (SELECT UserID FROM [Security].[User] WHERE IsSysAdmin = 'True'))
    OR (DepartmentMembers.DepartmentID = (SELECT DepartmentID FROM [Security].DepartmentMembers WHERE UserID = @UserID AND IsDeptAdmin = 'True'))
    AND (([Action] = 'Logged In') OR ([Action] IS NULL)))
    GROUP BY [Security].[User].UserID, Username, FirstName, LastName, Email, IsSysAdmin, [Action], ActionHost
    ORDER BY LastLoggedIn DESC, Username
 
END
 

Check your table. I think you have a PRIMARY KEY constraint that prevents you from inserting those duplicate rows.

-- CK

[abehm]

Check your table. I think you have a PRIMARY KEY constraint that prevents you from inserting those duplicate rows.

-- CK

lol.

I just saw that too!
I was just about to post to disregard that.
my bad.