Problems grasping ado.net - coming from asp background

Hi, i've downloaded a membership and roles provider but i am very new to ado.net. I have come fromasp background. Here's mycode:

public override void AddUsersToRoles(string[] usernames, string[] rolenames) {
// Validate arguments
foreach (string rolename in rolenames) if (!this.RoleExists(rolename)) throw new ProviderException("Role name not found");
foreach (string username in usernames) {
if (username.IndexOf(',') > 0) throw new ArgumentException("User names cannot contain commas.");
foreach (string rolename in rolenames) {
if (IsUserInRole(username, rolename)) throw new ProviderException("User is already in role.");
}
}

SqlConnection db = this.OpenDatabase();
SqlCommand cmd = new SqlCommand("INSERT INTO UsersInRoles (UserName, RoleName) VALUES (@.UserName, @.RoleName)", db);
cmd.Parameters.Add("@.UserName", SqlDbType.VarChar, 100);
cmd.Parameters.Add("@.RoleName", SqlDbType.VarChar, 100);
SqlTransaction tran = null;

try {
tran = db.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames) {
foreach (string rolename in rolenames) {
cmd.Parameters["@.UserName"].Value = username;
cmd.Parameters["@.RoleName"].Value = rolename;
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch {
tran.Rollback();
throw;
}
finally {
db.Close();
}
}

private SqlConnection OpenDatabase() {
SqlConnection DB = new SqlConnection(this.connectionString);
DB.Open();
return DB;
}

The problem i have is that the table structure they provide is to have:

UsersInRoles
- UserName (foreign key to users table)
- RoleName (foreign key to roles table)

but the table structure i have is:

UsersInRoles
- UserID (foreign key to users table)
- RoleID (foreign key to roles table)

So what i need to do is lookup the UserID and RoleID from the appropriate tables based on the UserName and RoleName before doing the insert. However i am not familiar with the new syntax. I'm sure i could bodge something together but i assume this new syntax is something to do with running all the insert statements in one (transaction) so it doesn't have to keep taking round trips back to sqlserver.

Appreciate if someone could show me how this could be done. Thanks

If I understand you correctly, this issue does not differ from ASP to ASP.NET. You need to get UserName(RoleName) based on UserID(RoleID) and then insert the values into UsersInRoles table, right? If so, you can create a stored procedure like this:

CREATE PROCEDURE sp_InsUsersInRoles @.RoleID INT
AS
INSERT INTO UsersInRoles (UserName, RoleName)
SELECT UserName, RoleName
FROM Users join Roles ON Users.RoleID=Roles.RoleID
WHERERoles.RoleID=@.RoleID

Then you can easily call the stored procedure, just remember to set the SqlCommand.CommandType to StoredProcedure, and add parameter for it.

|||

Change:

SqlCommand cmd = new SqlCommand("INSERT INTO UsersInRoles (UserName, RoleName) VALUES (@.UserName, @.RoleName)", db);

to:

SqlCommand cmd = new SqlCommand("INSERT INTO UsersInRoles (UserID, RoleID)SELECT (SELECT UserIDFROM UsersWHERE Username=@.UserName),RoleIDFROM RolesWHERE RoleName=@.RoleName", db);

|||

Hi Motley worked a treat. However i also tried doing:

SqlConnection db = this.OpenDatabase();
SqlCommand cmd = new SqlCommand("INSERT INTO UserRoles (UserName, RoleName) VALUES (@.UserId, @.RoleId)", db);
cmd.Parameters.Add("@.UserId", SqlDbType.Int);
cmd.Parameters.Add("@.RoleId", SqlDbType.Int);
SqlTransaction tran = null;

try {
tran = db.BeginTransaction();
cmd.Transaction = tran;
foreach (string userName in userNames) {
SqlCommand cmd2 = new SqlCommand("SELECT UserId FROM Users WHERE UserName = @.UserName", db);
cmd2.Parameters.Add("@.UserName", SqlDbType.VarChar, 100).Value = userName;
int userId = (int)cmd2.ExecuteScalar();

foreach (string roleName in roleNames) {
SqlCommand cmd3 = new SqlCommand("SELECT RoleId FROM Roles WHERE RoleName = @.RoleName", db);
cmd3.Parameters.Add("@.RoleName", SqlDbType.VarChar, 100).Value = roleName;
int roleId = (int)cmd3.ExecuteScalar();

cmd.Parameters["@.UserId"].Value = userId;
cmd.Parameters["@.RoleId"].Value = roleId;
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch {
tran.Rollback();
throw;
}
finally {
db.Close();
}

but it didn't work.

Any ideas what i did wrong. Cheers

|||

Is RoleName unique within Roles? If you are using the default asp.net membership database structure, then it is not. RoleName is only unique within an Application, and hence may appear in Roles multiple times for different applications. If you have multiple application support, then you will need to supply the application name or application id to your roleid lookup.

My code in this case will add the user to the RoleName of all applications in which the role appears. The same problem is true for UserName as well if you are using multiple applications, but my code will fail in that case as well.

Otherwise I see nothing wrong with your code.

|||

On the other hand, it could be transaction related. I don't normally use the transaction support in ASP.NET. I prefer to use SQL transactions for all my work since I tend to create large batches within my SQL, and wrap that rather than wrapping many SqlCommands. In any case, you might try adding the cmd2 and cmd3 to the transaction as well.

It would also help if you posted what error you are getting rather than just "It didn't work".

|||

Hi, i can't see how to track the error, i'm using visual studio web developer edition. I tried using the built in asp.net config tool to manage my users but when i put a break point in my code it does nothing. However to test if it was reaching this part of the code i did a dummy insert statement and it worked.

I also tried putting an exception on the catch part and doing console.writeline(ex.Message) but that didn't do anything either.

Appreciate your help once more. Thanks

Problems getting information out of a SQLDataSource

I'm working in Visual Studio, I've added a SQLDataSource to the webpage which links to a SQL server database via a DSN (created with the wizard) and it brings back data from a stored procedure with no problems.

I want to access the information from this SQLDataSource in my code and can't seem to get anything from it.

help would be most appreciated.

What do you want to do with the data? Put it in a Gridview? Datalist? Here is a article with a GridView accessing a datasource.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/GridViewEx02.asp

|||I actually want to put the information into a text field rather than a datagrid.|||gave up on that and did it the easy way, all code rather than using the wizards.|||

How would you want to access the information from the SqlDataSource? If you just want to access the database retrieved from database by the Select command, you can do something like this:

SqlDataSource sds = new SqlDataSource(conn.ConnectionString, cmd.CommandText);

DataSourceSelectArguments dssa = new DataSourceSelectArguments();

dssa.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount);

DataView dv = (DataView)sds.Select(dssa);

problem with updating identity in transactional replication

Hey. I've a few transactional publications. It fails with this error. When I
looked at the command, it's trying to insert a null in identity column. Why
does it do that? As of now, I've commented the update for the identity column
in the sp_msupd_logdevicebeacon. But now, if somebody tries to update the
identity column, what would happen? The reason I need the identity property
on subscriber side is because these replicated tables will be horizontally
partitioned and replicated to another server. And I'm hoping to use
Transactional Replication for that. Please let me know what should be done
and what's the ideal. thank you.
Cannot update identity column 'DeviceBeaconID'.
(Source: XIAN\XIAN2 (Data source); Error number: 8102)
{CALL sp_MSupd_logDeviceBeacon
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,2005-11-29 15:43:34.000,2005-11-29
15:44:00.663,NULL,NULL,2005-11-29 15:43:00,4004089,0x8009)}
Transaction sequence number and command ID of last execution batch are
0x002BA62A00000E7E000100000000 and 1.
This looks like you update proc as opposed to your insert proc.
Take the proc and open it up in a text editor. In the bottom half of the
proc comment out the part where it updates the identity column.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:728B16DD-98F6-41F1-925F-53C6684FD831@.microsoft.com...
> Hey. I've a few transactional publications. It fails with this error. When
> I
> looked at the command, it's trying to insert a null in identity column.
> Why
> does it do that? As of now, I've commented the update for the identity
> column
> in the sp_msupd_logdevicebeacon. But now, if somebody tries to update the
> identity column, what would happen? The reason I need the identity
> property
> on subscriber side is because these replicated tables will be horizontally
> partitioned and replicated to another server. And I'm hoping to use
> Transactional Replication for that. Please let me know what should be done
> and what's the ideal. thank you.
>
> Cannot update identity column 'DeviceBeaconID'.
> (Source: XIAN\XIAN2 (Data source); Error number: 8102)
>
> {CALL sp_MSupd_logDeviceBeacon
> (NULL,NULL,NULL,NULL,NULL,NULL,NULL,2005-11-29 15:43:34.000,2005-11-29
> 15:44:00.663,NULL,NULL,2005-11-29 15:43:00,4004089,0x8009)}
> Transaction sequence number and command ID of last execution batch are
> 0x002BA62A00000E7E000100000000 and 1.
|||Yes, I'm sorry. I used the wrong word. It's trying to update the Ident column
with a 'NULL' value. Can you explain what this statement does?
update "datObjects" set
"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end
I think this is the line you have asked me to comment out. In my case, I
checked the @.bitmap variable and I found it to be '0x8009' when it does a
substring, it'll be case 0, right? so, it'll go to the else statement,
right?
Now, the question is why does the sp try to insert a null? Is it because no
modifications were made to it?
Thank you for your help.
Tejas
|||It should not be trying to do a null update. I am really confused here
however, you persist in talking about inserts, for the life of me it should
be an update. Or perhaps you are that pesky Paul Ibison in disguise trying
to push me over the edge?
The @.bitmap dictates which columns are to be updated. It looks like for your
bitmask the else will be used which means that the identity value will be
updated to the same value. A Null is passed due to the call type you are
using MCALL IIRC. As the identity value is not updated on the publisher no
value is passed - i.e. a NULL is passed. If the identity column was updated
(if this is possible) a value would be passed here instead of the null.
I think your update proc portion should look like this
update "datObjects" set
-- "ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
-- "ObjectTypeID" end
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:2D828E4F-AC1A-4875-83CB-85B58FC80A73@.microsoft.com...
> Yes, I'm sorry. I used the wrong word. It's trying to update the Ident
> column
> with a 'NULL' value. Can you explain what this statement does?
> update "datObjects" set
> "ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "ObjectTypeID" end
> I think this is the line you have asked me to comment out. In my case, I
> checked the @.bitmap variable and I found it to be '0x8009' when it does a
> substring, it'll be case 0, right? so, it'll go to the else statement,
> right?
> Now, the question is why does the sp try to insert a null? Is it because
> no
> modifications were made to it?
> Thank you for your help.
> Tejas
|||lol, no it's not Paul. And I rectified it to update in my 2nd post. I'm sorry
again for using the word insert in my first post.Thank you very much for your
help. This was what I was actually looking for. Thank you.
|||Ok, thanks Paul.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:50263252-D63C-448A-8A89-470CABA983EA@.microsoft.com...
> lol, no it's not Paul. And I rectified it to update in my 2nd post. I'm
> sorry
> again for using the word insert in my first post.Thank you very much for
> your
> help. This was what I was actually looking for. Thank you.
|||Hey Hilary. I could not do what you had asked. I'm pasting the whole
sp_msupd_datobjects sp here. Then I'll tell you what the problem is.
Below, objectId is the identity column. But it doesn't exist in the else part.
I've pasted the sp as is. Made no modifications to it. So, if i comment the
objectid in the if part it works fine... Is it normal to be not there in the
else part? because that's where u asked me to comment it out.
-----
CREATE procedure "sp_MSupd_datObjects"
@.c1 bigint,@.c2 tinyint,@.c3 int,@.c4 varchar(255),@.c5 int,@.c6 bigint,@.c7
bigint,@.c8 smallint,@.c9 bit,@.c10 datetime,@.c11 datetime,@.c12 datetime,@.c13
bigint,@.c14 int,@.c15 int,@.c16 uniqueidentifier,@.pkc1 bigint
,@.bitmap binary(3)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update "datObjects" set
"ObjectID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else "ObjectID"
end
,"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end
,"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID" end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "datObjects" set
"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end,
"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID" end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
GO
|||Ok Paul, I think this is it
CREATE procedure "sp_MSupd_datObjects"
@.c1 bigint,@.c2 tinyint,@.c3 int,@.c4 varchar(255),@.c5 int,@.c6 bigint,@.c7
bigint,@.c8 smallint,@.c9 bit,@.c10 datetime,@.c11 datetime,@.c12 datetime,@.c13
bigint,@.c14 int,@.c15 int,@.c16 uniqueidentifier,@.pkc1 bigint
,@.bitmap binary(3)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update "datObjects" set
--on the safe side I will do this too
--"ObjectID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
"ObjectID"
--end
--,
"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
"ObjectTypeID" end
,"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update "datObjects" set
--"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
--"ObjectTypeID" end,
"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
"ObjectSubTypeID" end
,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
"ObjectName" end
,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
else "ObjectNameStringID" end
,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
"ParentObjectID" end
,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
"OwnerObjectID" end
,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
"IsDeleted" end
,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
"IsEnabled" end
,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
"DateCreated" end
,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
"DateModified" end
,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
"DateDeleted" end
,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
else "ModifiersObjectID" end
,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
"PathDepth" end
,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
end
,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
"Rowguid" end
where "ObjectID" = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
GO
>
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:5FA239EC-5A40-4D2F-AB2F-570BF4B6FAF9@.microsoft.com...
> Hey Hilary. I could not do what you had asked. I'm pasting the whole
> sp_msupd_datobjects sp here. Then I'll tell you what the problem is.
> Below, objectId is the identity column. But it doesn't exist in the else
> part.
> I've pasted the sp as is. Made no modifications to it. So, if i comment
> the
> objectid in the if part it works fine... Is it normal to be not there in
> the
> else part? because that's where u asked me to comment it out.
> -----
> CREATE procedure "sp_MSupd_datObjects"
> @.c1 bigint,@.c2 tinyint,@.c3 int,@.c4 varchar(255),@.c5 int,@.c6 bigint,@.c7
> bigint,@.c8 smallint,@.c9 bit,@.c10 datetime,@.c11 datetime,@.c12 datetime,@.c13
> bigint,@.c14 int,@.c15 int,@.c16 uniqueidentifier,@.pkc1 bigint
> ,@.bitmap binary(3)
> as
> if substring(@.bitmap,1,1) & 1 = 1
> begin
> update "datObjects" set
> "ObjectID" = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
> "ObjectID"
> end
> ,"ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "ObjectTypeID" end
> ,"ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
> "ObjectSubTypeID" end
> ,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "ObjectName" end
> ,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "ObjectNameStringID" end
> ,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "ParentObjectID" end
> ,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "OwnerObjectID" end
> ,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
> "IsDeleted" end
> ,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
> "IsEnabled" end
> ,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "DateCreated" end
> ,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
> "DateModified" end
> ,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
> "DateDeleted" end
> ,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
> else "ModifiersObjectID" end
> ,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
> "PathDepth" end
> ,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
> end
> ,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
> "Rowguid" end
> where "ObjectID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> else
> begin
> update "datObjects" set
> "ObjectTypeID" = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
> "ObjectTypeID" end,
> "ObjectSubTypeID" = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
> "ObjectSubTypeID" end
> ,"ObjectName" = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
> "ObjectName" end
> ,"ObjectNameStringID" = case substring(@.bitmap,1,1) & 16 when 16 then @.c5
> else "ObjectNameStringID" end
> ,"ParentObjectID" = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
> "ParentObjectID" end
> ,"OwnerObjectID" = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else
> "OwnerObjectID" end
> ,"IsDeleted" = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
> "IsDeleted" end
> ,"IsEnabled" = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else
> "IsEnabled" end
> ,"DateCreated" = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else
> "DateCreated" end
> ,"DateModified" = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
> "DateModified" end
> ,"DateDeleted" = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
> "DateDeleted" end
> ,"ModifiersObjectID" = case substring(@.bitmap,2,1) & 16 when 16 then @.c13
> else "ModifiersObjectID" end
> ,"PathDepth" = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
> "PathDepth" end
> ,"OldID" = case substring(@.bitmap,2,1) & 64 when 64 then @.c15 else "OldID"
> end
> ,"Rowguid" = case substring(@.bitmap,2,1) & 128 when 128 then @.c16 else
> "Rowguid" end
> where "ObjectID" = @.pkc1
> if @.@.rowcount = 0
> if @.@.microsoftversion>0x07320000
> exec sp_MSreplraiserror 20598
> end
> GO
>
|||Just wanted to know if you noticed that the commented lines in the sp are two
different fields...
In the if, it's objectid
in the else, it's objectTypeID.
These are two different columns with no correlation...
Is the commenting still correct?
Just want to be sure.
Thank you,
TEJAS
(not Paul)
|||Oops, yes you are correct. I should have commented out the identity column -
its ObjectTypeID right?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:561E41D4-279E-4FF3-B976-8F75A2AA7689@.microsoft.com...
> Just wanted to know if you noticed that the commented lines in the sp are
> two
> different fields...
> In the if, it's objectid
> in the else, it's objectTypeID.
> These are two different columns with no correlation...
> Is the commenting still correct?
> Just want to be sure.
> Thank you,
> TEJAS
> (not Paul)