StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Sql Replication Guid Field

Sql Replication Guid Field

Paul Chase — Thu, 04 May 2006 13:32:42 GMT

I am planning on using replication between branch offices and the corporate office. Using replication requires a guid field in each table which is fine, I am even considering using it as my primary keys as each office's data will be consolidated into one db.

The real problem I am having is the column with the Guid type is set to have a default value of newsequentialid() which from what I have read eliminates alot of the index issues with using guids by making the id sequential. How do I configure the BO to pass default to allow sql to generate newid. I know I can generate a guid on the .net side but it does not seem to be sequential.

Thanks

RE: Sql Replication Guid Field

StrataFrame Team — Thu, 04 May 2006 13:32:42 GMT

No, stuff like that isn't obvious... it's just that I've seen that error message enough to know what it is ;)

RE: Sql Replication Guid Field

Paul Chase — Thu, 04 May 2006 13:31:24 GMT

You were correct I had accidently left the renamed pk as identity. Sometimes you look at something too much and miss the obvious,

RE: Sql Replication Guid Field

StrataFrame Team — Thu, 04 May 2006 13:12:52 GMT

What you have is correct. The problem is that one of the columns in your table has an IDENTITY specification on it. Check the columns on your table and make sure that you don't have IDENTITY turned on on any of them.

But yes, you are correct in that when you want to use a GUID pk, you're better off creating your own guids on the client side than trying to retrieve the value from the server.

RE: Sql Replication Guid Field

Paul Chase — Thu, 04 May 2006 11:51:37 GMT

Thanks Ben,

I was kinda hopeful to use guid's as a primary key the more I thought about it. My current PK in an int type that is prefixed by a 3 digit office code. While this approach works it is not nessasarily ideal for a number of reasons.

However googling around I found some examples to "modifiy" a guid to make it sequential by datetime. I have a guid field as my primary key no default value set and rowguid set to no, auto increment turned off on the BO. I am setting the pk_id in the setdefaultvalues method of the bo. I get the following error.

Private Sub SalesmenBO_SetDefaultValues()

Me.sm_pk_id = NewSeqGuid()

End Sub

Cannot insert explicit value for identity column in table 'salesmen' when IDENTITY_INSERT is set to OFF.

What is the correct way to provide my own pk?

Public Shared Function NewSeqGuid() As Guid

Dim laGuid() As Byte = System.Guid.NewGuid.ToByteArray

Dim ldBaseDate As DateTime = New DateTime(1900, 1, 1)

Dim ldNow As DateTime = DateTime.Now

' Get the days and milliseconds which will be used to build the byte string

Dim strucdays As TimeSpan = New TimeSpan((ldNow.Ticks - ldBaseDate.Ticks))

Dim strucmsecs As TimeSpan = New TimeSpan((ldNow.Ticks _

- (New DateTime(ldNow.Year, ldNow.Month, ldNow.Day).Ticks)))

' Convert to a byte array

' Note that SQL Server is accurate to 1/300th of a millisecond so we divide by 3.333333

Dim laDays() As Byte = BitConverter.GetBytes(strucdays.Days)

Dim laSecs() As Byte = BitConverter.GetBytes(CType((strucmsecs.TotalMilliseconds / 3.333333), Long))

' Reverse the bytes to match SQL Servers ordering

Array.Reverse(laDays)

Array.Reverse(laSecs)

' Copy the bytes into the guid

Array.Copy(laDays, (laDays.Length - 2), laGuid, (laGuid.Length - 6), 2)

Array.Copy(laSecs, (laSecs.Length - 4), laGuid, (laGuid.Length - 4), 4)

Return New System.Guid(laGuid)

End Function

RE: Sql Replication Guid Field

StrataFrame Team — Thu, 04 May 2006 10:11:29 GMT

Well, Paul, you're right, and there is no way to retrieve the last created GUID through either the NewID or NewSequencialID methods. We have a pending feature request for the ability to retrieve columns other than the PK from the server. Essentially, you specify the column(s) you want to retrieve from the server after an insert and the BO retrieves them... Looks like you'll have to wait until that feature is implemented to do what you want to do. Then, you can make your Primary Key an IDENTITY integer, and let replication services have it's GUID column that's assigned either by NewID or NewSequencialID and gets retrieved after you insert a new record.

I'll try to get that feature request bumped up and let you know when it's implemented :ermm:

RE: Sql Replication Guid Field

Paul Chase — Thu, 04 May 2006 09:52:22 GMT

Here is a shot from Sql

RE: Sql Replication Guid Field

Paul Chase — Thu, 04 May 2006 09:42:29 GMT

[quote][b]Ben Chase (05/04/2006)[/b][hr]What you'll need to do is leave the business object's PrimaryKeyIsAutoIncremented property to True. This will inform the business object that the server needs to assign the GUID value. Then, just like you said, put the NewSequentialID() function as the default value for a column. When your business object saves the record, it will insert a NULL value for the PK and retrieve the assigned value from the server, just as if it was an IDENTITY column.[/quote]

Ben this doesn't work i receive the following error.

Cannot create INSERT command because PrimaryKeyIsAutoIncremented = True and the PrimaryKeyField is not a valid auto-increment data type.

RE: Sql Replication Guid Field

Paul Chase — Thu, 04 May 2006 08:51:05 GMT

Ok,

I guess that makes my decision on using them for PK's. :)

RE: Sql Replication Guid Field

StrataFrame Team — Thu, 04 May 2006 08:41:17 GMT

No, not really. For the business object to automatically retrieve the value without you needing to requery after saving the new records, it will need to be the PK.

RE: Sql Replication Guid Field

Paul Chase — Thu, 04 May 2006 08:37:47 GMT

Ben currently I am not using it as a Pk field. Sql added the Field when I set up replication. I am considering using it as a Pk however. Is there a way to do this without it being a PK?

RE: Sql Replication Guid Field

StrataFrame Team — Thu, 04 May 2006 08:29:26 GMT

What you'll need to do is leave the business object's PrimaryKeyIsAutoIncremented property to True. This will inform the business object that the server needs to assign the GUID value. Then, just like you said, put the NewSequentialID() function as the default value for a column. When your business object saves the record, it will insert a NULL value for the PK and retrieve the assigned value from the server, just as if it was an IDENTITY column.