How to enable Optimistic concurrency

I have a number of SqlDataSource objects in my application, which don't have Optimistic Concurrency option enabled. The SDS objects use custom Sql statements so I can no longer select the Advance button to enable Concurrent Concurrency.

How can I enable this option? Is there a designt ime property, and even a run time property that can be set?

The only method we have so far is to create a new SDS, with Optimistic Concurrency switched on, then copy and paste my custom Sql into it and rebind my components..

Any help on this matter is appreciated.

Regards,

Steven

SqlDataSource class dose not have corresponding member for this options, you can check the SqlDataSource element of the SqlDataSource control in which you do not choose customize statement: it seems this option acts like the ConflictDetection property. Here is a section from VS2005 documentation:

Use optimistic concurrency
This element sets how the SqlDataSource control handles conflicts when updating or deleting data. When Use optimistic concurrency is selected, the ConflictDetection property is set to the value CompareAllValues. When Use optimistic concurrency is not selected, the ConflictDetection property is set to the value OverwriteChanges, which is the default value.

|||

Thanks for the reply.

I have checked this property, hoping I could somehow enable this feature but it appears not.

This member is only useful if Optimistic Concurrency has already been set through the design settings.

It seems strange that the option can be set freely in design mode, but the developer has no option to turn this setting back on IF custom Sql has been used on the SDS.

How to drop all objects with permissions for a role

How do I enumerate all the permissions for a role and then drop them? I
would like to do it with a SQL script, not through the enterprise manager.
Thanks in advance.
Valerie HoughHi,
Use the below script. This will give you all the rights a role/user have. To
revoke the rights change the Grant to revoke.
--
DECLARE @.DatabaseUserName [sysname]
SET @.DatabaseUserName = 'Replace_with_ROLE_NAME'
SET NOCOUNT ON
DECLARE
@.errStatement [varchar](8000),
@.msgStatement [varchar](8000),
@.DatabaseUserID [smallint],
@.ServerUserName [sysname],
@.RoleName [varchar](8000),
@.ObjectID [int],
@.ObjectName [varchar](261)
SELECT
@.DatabaseUserID = [sysusers].[uid],
@.ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[si
d]
WHERE [sysusers].[name] = @.DatabaseUserName
IF @.DatabaseUserID IS NULL
BEGIN
SET @.errStatement = 'User ' + @.DatabaseUserName + ' does not exist in ' +
DB_NAME() + CHAR(13) +
'Please provide the name of a current user in ' + DB_NAME() + ' you wish to
script.'
RAISERROR(@.errStatement, 16, 1)
END
ELSE
BEGIN
SET @.msgStatement = '--Security creation script for user ' + @.ServerUserName
+ CHAR(13) +
'--Created At: ' + CONVERT(varchar, GETDATE(), 112) +
REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
'--Created By: ' + SUSER_NAME() + CHAR(13) +
'--Add User To Database' + CHAR(13) +
'USE [' + DB_NAME() + ']' + CHAR(13) +
'EXEC [sp_grantdbaccess]' + CHAR(13) +
CHAR(9) + '@.loginame = ''' + @.ServerUserName + ''',' + CHAR(13) +
CHAR(9) + '@.name_in_db = ''' + @.DatabaseUserName + '''' + CHAR(13) +
'GO' + CHAR(13) +
'--Add User To Roles'
PRINT @.msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @.DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @.RoleName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@.rolename = ''' + @.RoleName + ''',' + CHAR(13) +
CHAR(9) + '@.membername = ''' + @.DatabaseUserName + ''''
PRINT @.msgStatement
FETCH
NEXT
FROM _sysusers
INTO @.RoleName
END
SET @.msgStatement = 'GO' + CHAR(13) +
'--Set Object Specific Permissions'
PRINT @.msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobject
s].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @.DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@.ObjectID,
@.ObjectName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @.Object
ID AND [uid]
= @.DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @.msgStatement = @.msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @.Object
ID AND [uid]
= @.DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @.msgStatement = @.msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @.Object
ID AND [uid]
= @.DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @.msgStatement = @.msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @.Object
ID AND [uid]
= @.DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @.msgStatement = @.msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @.Object
ID AND [uid]
= @.DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @.msgStatement = @.msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @.Object
ID AND [uid]
= @.DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @.msgStatement = @.msgStatement + 'REFERENCES,'
IF LEN(@.msgStatement) > 0
BEGIN
IF RIGHT(@.msgStatement, 1) = ','
SET @.msgStatement = LEFT(@.msgStatement, LEN(@.msgStatement) - 1)
SET @.msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @.msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @.ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @.DatabaseUserName
PRINT @.msgStatement
END
SET @.msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @.Object
ID AND [uid]
= @.DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @.msgStatement = @.msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @.Object
ID AND [uid]
= @.DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @.msgStatement = @.msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @.Object
ID AND [uid]
= @.DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @.msgStatement = @.msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @.Object
ID AND [uid]
= @.DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @.msgStatement = @.msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @.Object
ID AND [uid]
= @.DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @.msgStatement = @.msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @.Object
ID AND [uid]
= @.DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @.msgStatement = @.msgStatement + 'REFERENCES,'
IF LEN(@.msgStatement) > 0
BEGIN
IF RIGHT(@.msgStatement, 1) = ','
SET @.msgStatement = LEFT(@.msgStatement, LEN(@.msgStatement) - 1)
SET @.msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @.msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @.ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @.DatabaseUserName
PRINT @.msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@.ObjectID,
@.ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT 'GO'
END
Thanks
Hari
SQL Server MVP
"Valerie Hough" <sales@.pcTrans.com> wrote in message
news:%23tcDS25wGHA.1888@.TK2MSFTNGP03.phx.gbl...
> How do I enumerate all the permissions for a role and then drop them? I
> would like to do it with a SQL script, not through the enterprise manager.
> Thanks in advance.
> Valerie Hough
>

how to drop 1 users tmp objects

I need to drop a user but the user owns objects, apparently tmp tables, how
can I dlete them?
Thanks in advance!Are you on SQL Server 2000? I assume so because of your symptom.
Find all the objects by querying sysobjects for any objects owned by the
user's userid (dbo.sysusers.uid). Either drop those objects or change their
owner using sp_changeobjectowner.
If the user still will not drop, perhaps it also owns some user-defined
datatypes. Those also need to be dropped.
RLF
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:572AD28D-BABD-4F34-B849-A36C1C1F00C4@.microsoft.com...
>I need to drop a user but the user owns objects, apparently tmp tables, how
> can I dlete them?
> Thanks in advance!|||Are you referring to killing the connection and getting rid of #temp tables?
If so, they should be
removed when the connection is terminated.
If you mean removing the users, and that user owns regular tables in the dat
abase, use DROP TABLE to
get rid of those tables (if that is what you want to do).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:572AD28D-BABD-4F34-B849-A36C1C1F00C4@.microsoft.com...
>I need to drop a user but the user owns objects, apparently tmp tables, how
> can I dlete them?
> Thanks in advance!|||after running the DROP TABLE and getting another error, I am not sure these
objects are tables.
Their naming convention is "TMP_SYSA_1234"
and I am running SQL Server 2K
thanks for your fast responses!|||and there are no connections
"totoro" wrote:

> after running the DROP TABLE and getting another error, I am not sure thes
e
> objects are tables.
> Their naming convention is "TMP_SYSA_1234"
> and I am running SQL Server 2K
> thanks for your fast responses!|||Ok, assuming that you are querying sysobjects to find these, then sysobjects
also contains a code showing you the type of object:
U = user table
P = stored procedure
V = view
et cetera.
You will need to use the proper DROP statement for whatever the object is.
In Enterprise Manager you can go to each of the collections in the database
(Tables, Views, etc) and sort by owner to find any that are not dbo. Then
you can delete them from there.
RLF
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
> after running the DROP TABLE and getting another error, I am not sure
> these
> objects are tables.
> Their naming convention is "TMP_SYSA_1234"
> and I am running SQL Server 2K
> thanks for your fast responses!|||there is a "U"
but the drop table command retrieved an error saying it couldn't find any
table with the name (TMP_SYS_etc) I had in the query, so my sysntax must be
wrong
lurnin a lot tuday!
"Russell Fields" wrote:

> Ok, assuming that you are querying sysobjects to find these, then sysobjec
ts
> also contains a code showing you the type of object:
> U = user table
> P = stored procedure
> V = view
> et cetera.
> You will need to use the proper DROP statement for whatever the object is.
> In Enterprise Manager you can go to each of the collections in the databas
e
> (Tables, Views, etc) and sort by owner to find any that are not dbo. Then
> you can delete them from there.
> RLF
> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
>
>|||Did you include the owner of the object?
DROP TABLE ownername.tblname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:F494E330-47FA-471F-B85C-2DD1ACA811C8@.microsoft.com...[vbcol=seagreen]
> there is a "U"
> but the drop table command retrieved an error saying it couldn't find any
> table with the name (TMP_SYS_etc) I had in the query, so my sysntax must b
e
> wrong
> lurnin a lot tuday!
> "Russell Fields" wrote:
>|||THAT DID IT! Thanks Tibor and Russel, you guys rock!
"Tibor Karaszi" wrote:

> Did you include the owner of the object?
> DROP TABLE ownername.tblname
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> news:F494E330-47FA-471F-B85C-2DD1ACA811C8@.microsoft.com...
>

how to drop 1 users tmp objects

I need to drop a user but the user owns objects, apparently tmp tables, how
can I dlete them?
Thanks in advance!
Are you on SQL Server 2000? I assume so because of your symptom.
Find all the objects by querying sysobjects for any objects owned by the
user's userid (dbo.sysusers.uid). Either drop those objects or change their
owner using sp_changeobjectowner.
If the user still will not drop, perhaps it also owns some user-defined
datatypes. Those also need to be dropped.
RLF
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:572AD28D-BABD-4F34-B849-A36C1C1F00C4@.microsoft.com...
>I need to drop a user but the user owns objects, apparently tmp tables, how
> can I dlete them?
> Thanks in advance!
|||after running the DROP TABLE and getting another error, I am not sure these
objects are tables.
Their naming convention is "TMP_SYSA_1234"
and I am running SQL Server 2K
thanks for your fast responses!
|||and there are no connections
"totoro" wrote:

> after running the DROP TABLE and getting another error, I am not sure these
> objects are tables.
> Their naming convention is "TMP_SYSA_1234"
> and I am running SQL Server 2K
> thanks for your fast responses!
|||Ok, assuming that you are querying sysobjects to find these, then sysobjects
also contains a code showing you the type of object:
U = user table
P = stored procedure
V = view
et cetera.
You will need to use the proper DROP statement for whatever the object is.
In Enterprise Manager you can go to each of the collections in the database
(Tables, Views, etc) and sort by owner to find any that are not dbo. Then
you can delete them from there.
RLF
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
> after running the DROP TABLE and getting another error, I am not sure
> these
> objects are tables.
> Their naming convention is "TMP_SYSA_1234"
> and I am running SQL Server 2K
> thanks for your fast responses!
|||there is a "U"
but the drop table command retrieved an error saying it couldn't find any
table with the name (TMP_SYS_etc) I had in the query, so my sysntax must be
wrong
lurnin a lot tuday!
"Russell Fields" wrote:

> Ok, assuming that you are querying sysobjects to find these, then sysobjects
> also contains a code showing you the type of object:
> U = user table
> P = stored procedure
> V = view
> et cetera.
> You will need to use the proper DROP statement for whatever the object is.
> In Enterprise Manager you can go to each of the collections in the database
> (Tables, Views, etc) and sort by owner to find any that are not dbo. Then
> you can delete them from there.
> RLF
> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
>
>
|||THAT DID IT! Thanks Tibor and Russel, you guys rock!
"Tibor Karaszi" wrote:

> Did you include the owner of the object?
> DROP TABLE ownername.tblname
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> news:F494E330-47FA-471F-B85C-2DD1ACA811C8@.microsoft.com...
>

how to drop 1 users tmp objects

I need to drop a user but the user owns objects, apparently tmp tables, how
can I dlete them?
Thanks in advance!Are you on SQL Server 2000? I assume so because of your symptom.
Find all the objects by querying sysobjects for any objects owned by the
user's userid (dbo.sysusers.uid). Either drop those objects or change their
owner using sp_changeobjectowner.
If the user still will not drop, perhaps it also owns some user-defined
datatypes. Those also need to be dropped.
RLF
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:572AD28D-BABD-4F34-B849-A36C1C1F00C4@.microsoft.com...
>I need to drop a user but the user owns objects, apparently tmp tables, how
> can I dlete them?
> Thanks in advance!|||Are you referring to killing the connection and getting rid of #temp tables? If so, they should be
removed when the connection is terminated.
If you mean removing the users, and that user owns regular tables in the database, use DROP TABLE to
get rid of those tables (if that is what you want to do).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:572AD28D-BABD-4F34-B849-A36C1C1F00C4@.microsoft.com...
>I need to drop a user but the user owns objects, apparently tmp tables, how
> can I dlete them?
> Thanks in advance!|||after running the DROP TABLE and getting another error, I am not sure these
objects are tables.
Their naming convention is "TMP_SYSA_1234"
and I am running SQL Server 2K
thanks for your fast responses!|||and there are no connections
"totoro" wrote:
> after running the DROP TABLE and getting another error, I am not sure these
> objects are tables.
> Their naming convention is "TMP_SYSA_1234"
> and I am running SQL Server 2K
> thanks for your fast responses!|||Ok, assuming that you are querying sysobjects to find these, then sysobjects
also contains a code showing you the type of object:
U = user table
P = stored procedure
V = view
et cetera.
You will need to use the proper DROP statement for whatever the object is.
In Enterprise Manager you can go to each of the collections in the database
(Tables, Views, etc) and sort by owner to find any that are not dbo. Then
you can delete them from there.
RLF
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
> after running the DROP TABLE and getting another error, I am not sure
> these
> objects are tables.
> Their naming convention is "TMP_SYSA_1234"
> and I am running SQL Server 2K
> thanks for your fast responses!|||there is a "U"
but the drop table command retrieved an error saying it couldn't find any
table with the name (TMP_SYS_etc) I had in the query, so my sysntax must be
wrong
lurnin a lot tuday!
"Russell Fields" wrote:
> Ok, assuming that you are querying sysobjects to find these, then sysobjects
> also contains a code showing you the type of object:
> U = user table
> P = stored procedure
> V = view
> et cetera.
> You will need to use the proper DROP statement for whatever the object is.
> In Enterprise Manager you can go to each of the collections in the database
> (Tables, Views, etc) and sort by owner to find any that are not dbo. Then
> you can delete them from there.
> RLF
> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
> > after running the DROP TABLE and getting another error, I am not sure
> > these
> > objects are tables.
> > Their naming convention is "TMP_SYSA_1234"
> > and I am running SQL Server 2K
> >
> > thanks for your fast responses!
>
>|||Did you include the owner of the object?
DROP TABLE ownername.tblname
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:F494E330-47FA-471F-B85C-2DD1ACA811C8@.microsoft.com...
> there is a "U"
> but the drop table command retrieved an error saying it couldn't find any
> table with the name (TMP_SYS_etc) I had in the query, so my sysntax must be
> wrong
> lurnin a lot tuday!
> "Russell Fields" wrote:
>> Ok, assuming that you are querying sysobjects to find these, then sysobjects
>> also contains a code showing you the type of object:
>> U = user table
>> P = stored procedure
>> V = view
>> et cetera.
>> You will need to use the proper DROP statement for whatever the object is.
>> In Enterprise Manager you can go to each of the collections in the database
>> (Tables, Views, etc) and sort by owner to find any that are not dbo. Then
>> you can delete them from there.
>> RLF
>> "totoro" <totoro@.discussions.microsoft.com> wrote in message
>> news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
>> > after running the DROP TABLE and getting another error, I am not sure
>> > these
>> > objects are tables.
>> > Their naming convention is "TMP_SYSA_1234"
>> > and I am running SQL Server 2K
>> >
>> > thanks for your fast responses!
>>|||THAT DID IT! Thanks Tibor and Russel, you guys rock!
"Tibor Karaszi" wrote:
> Did you include the owner of the object?
> DROP TABLE ownername.tblname
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> news:F494E330-47FA-471F-B85C-2DD1ACA811C8@.microsoft.com...
> > there is a "U"
> >
> > but the drop table command retrieved an error saying it couldn't find any
> > table with the name (TMP_SYS_etc) I had in the query, so my sysntax must be
> > wrong
> >
> > lurnin a lot tuday!
> >
> > "Russell Fields" wrote:
> >
> >> Ok, assuming that you are querying sysobjects to find these, then sysobjects
> >> also contains a code showing you the type of object:
> >> U = user table
> >> P = stored procedure
> >> V = view
> >> et cetera.
> >>
> >> You will need to use the proper DROP statement for whatever the object is.
> >>
> >> In Enterprise Manager you can go to each of the collections in the database
> >> (Tables, Views, etc) and sort by owner to find any that are not dbo. Then
> >> you can delete them from there.
> >>
> >> RLF
> >> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> >> news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
> >> > after running the DROP TABLE and getting another error, I am not sure
> >> > these
> >> > objects are tables.
> >> > Their naming convention is "TMP_SYSA_1234"
> >> > and I am running SQL Server 2K
> >> >
> >> > thanks for your fast responses!
> >>
> >>
> >>
>