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

P

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

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.

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

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