Creating a table with a dual primary key

This question may be a little complicated.

I am building a DTS Package that is moving data from our webstore (written in house) to a Warehouse Management System(WMS - Turnkey) and I've encountered a problem. both pieces of software have an orders table and an Ordered_Items table, related by the order_ID (makes sense so far). Here is the problem. The primary key on the webstore's Ordered_Items table is a single column (basically an Identity variable), while the primary key on the WMS's Ordered_Items table is a dual column primary key, between the Order_ID and the Order_LineID, so the data should be stored like:

OrderID Order_LineID
1 1
2 1
2 2
2 3
3 1
3 2
4 1

Get the Idea? So I have to create this new Order_LineID column. How can I accomplish this with a SQL statement?

Thanks!!!!!Does it matter that the destination needs to start at 1 and be sequential? If not, just use the Identity value. Probably too simple.

If you do it in a cursor, you could use a counter and move each record one at a time and use your counter to create the line number.

You could create the line number field in your source and pre-populate it with a process to loop through and assign the line numbers just prior to dumping it to the destination.

Doing it within one SQL statement to move it from one to the other might be impossible.|||Yes, you can create primary key that consists of two columns, or composite primary key.
It is not possible to create dual promary key in SQL server. You may create multiple uniqe index/constraints. But for one talbe, there is only one primary key.|||It is not quite clear what you exactly want to do.

Do you want to add a new column for an existing table as a primary key or is it a whole new table?|||I think we are on the right track (sort of).

I my example, the Order ID's are already assigned (they basically use an Identity Key).

What I need is some sort of query (or activeX script) that will loop through each record, and assign the Order_Line_ID (By the way, I don't care how many SQL statements this takes, as long as it works).

I am querying the Order_Line table, and sorting it by the Order#, so the table will look like this at first:

LineID-Identity | Order#
1 | 12345
2 | 12345
3 | 12345
4 | 12346
5 | 12347
6 | 12347
7 | 12348

The idea is, an order may have multiple lines (you've probably ordered more than one item at a time from some site online)

The counter needs to start over at 1 when it comes to a new order. For example, if the first three records in the Order_Line table are from Order# 12345, the first three rows would be numbered 1, 2 and 3, respectively (that should make sense). Now the tricky part: If the next Order # is 12346, the counter would start at 1 again, like this.

LineID-Identity | Order# | OrderLine#
1 | 12345 | 1
2 | 12345 | 2
3 | 12345 | 3
4 | 12346 | 1
5 | 12347 | 1
6 | 12347 | 2
7 | 12348 | 1

As you can see, this is a new column, so no data is being removed. How do you do this?|||Within SQL, use a cursor and loop through a sorted list of the line items. Use a "current" and "previous" variable to hold the order ID and a counter to hold the new line number. Create the field (null at first), loop through the cursor and using the logic of comparing the last order ID to the current one, assign the line number from the counter, increment the counter, etc. Look up the cursor options you'll need to use to make the cursor editable.

Creating a custom resolver with VB.NET

Someone posted the question "Can anyone point me towards a source code
listing for a working replication custom resolver written in .Net?" Here is
an example that I have written.
Custom resolvers are created by adding a reference to the Microsoft SQL
Replication Conflict Resolver Library, replrec.dll. Unfortunately, the .NET
type library importer incorrectly defines the buffer parameter of methods
GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to be the
address of an Object. For COM interfaces, which is what replrec.dll is
supposed to be, that implies a COM-VARIANT parameter passing mechanism, but
this is not what Replrec passes back. Replrec is simply passing back the
address of a buffer containing the column value which you have to decode.
So the solution involves correcting the method definitions so that a buffer
address can be passed and using the .NET Marshal routines to create the
buffer and move bytes out of the buffer.
Here are the steps involved, followed by a code example that resolves
conflicts by using the column values from the row with the latest date in
user defined column ModifyDate.
1. Open a Visual Studio .NET 2003 Command Prompt
2. tlbimp "c:\Program Files\Microsoft SQL Server\80\COM\replrec.dll"
/OUT:SQLResolver_import.dll
3. ildasm "SQLResolver_import.dll" /OUT=SQLResolver.il
4. Change line "[out] object& marshal( struct) pvBuffer" for the methods
GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to "[out]
int32 pvBuffer"
5. ilasm SQLResolver.il /OUT=SQLResolver.dll /dll
6. Create a new .NET Windows Control Library project
7. Remove the wizard generated control
8. Use "Add New Item..." to add a new COM class
9. Add reference to SQLResolver.dll created in step #5
Imports System.Text
Imports SQLResolver
Imports System.Runtime.InteropServices
Imports System.Runtime.InteropServices.MarshalAsAttribute
Imports SQLResolver.REPOLE_CHANGE_TYPE
Imports SQLResolver.REPOLE_COLSTATUS_TYPE
<ComClass(ComClass1.ClassId, ComClass1.InterfaceId, ComClass1.EventsId)> _
Public Class ComClass1
Implements SQLResolver.IVBCustomResolver
Private Const MAX_BUFFER_SIZE As Integer = 1048576
Private Const MAX_NAME_LENGTH As Integer = 128
#Region "COM GUIDs"
' These GUIDs provide the COM identity for this class
' and its COM interfaces. If you change them, existing
' clients will no longer be able to access the class.
Public Const ClassId As String = "825818F7-3531-4524-8B07-72343EFDC8AB"
Public Const InterfaceId As String =
"CECFBB8F-584F-4733-9373-B69AFA6F117F"
Public Const EventsId As String = "5D55BA40-A438-4FD4-BA8B-05095DC89948"
#End Region
' A creatable COM class must have a Public Sub New()
' with no parameters, otherwise, the class will not be
' registered in the COM registry and cannot be created
' via CreateObject.
Public Sub New()
MyBase.New()
End Sub
Public Sub GetHandledStates(ByRef ResolverBm As Integer) Implements
IVBCustomResolver.GetHandledStates
ResolverBm = REPOLEUpdateConflicts
End Sub
Public Sub Reconcile(ByVal pRowChange As IReplRowChange, ByVal dwFlags As
Integer, ByVal pvReserved As IReplRowChange) Implements
IVBCustomResolver.Reconcile
Dim cntColumns As Integer
Dim intColumn As Integer
Dim strColumnName As String
Dim strLogMessage As String
Dim WinningData As Object
Dim blnSourceIsWinner As Boolean
Dim ColStatus As SQLResolver.REPOLE_COLSTATUS_TYPE
Dim intBufferLenActual As Integer
Dim intBufferLen As Integer
Dim strDestinationDateTime As String
Dim strSourceDateTime As String
Dim strDestinationUser As String
Dim strSourceUser As String
Dim myBuffer As IntPtr = Marshal.AllocHGlobal(MAX_BUFFER_SIZE)
Dim strMsg As String
'If Not Debugger.IsAttached Then
' Debugger.Launch()
'Else
' Debugger.Break()
'End If
Call pRowChange.GetNumColumns(cntColumns)
For intColumn = 1 To cntColumns
strColumnName = " ".PadRight(MAX_NAME_LENGTH)
' strColumnName.PadRight(OSQL_SYSNAME_SET, Chr(vbNull))
Call pRowChange.GetColumnName(intColumn, strColumnName, MAX_NAME_LENGTH)
' strColumnName.TrimEnd(Chr(vbNull))
strColumnName = strColumnName.TrimEnd()
If (String.Compare(strColumnName, "ModifyDate", True) = 0) Then
pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strDestinationDateTime = ConvertBufferToDateString(myBuffer)
pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strSourceDateTime = ConvertBufferToDateString(myBuffer)
If strSourceDateTime > strDestinationDateTime Then
blnSourceIsWinner = True
Else
blnSourceIsWinner = False
End If
End If
If (String.Compare(strColumnName, "ModifyUser", True) = 0) Then
pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strDestinationUser = ConvertBufferToString(myBuffer, intBufferLenActual)
pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
MAX_BUFFER_SIZE, intBufferLenActual)
strSourceUser = ConvertBufferToString(myBuffer, intBufferLenActual)
End If
Next intColumn
For intColumn = 1 To cntColumns
'Get the column status of each column
pRowChange.GetColumnStatus(intColumn, ColStatus)
' If the column has been updated at both the Publisher and Subscriber
If (ColStatus = REPOLEColumn_UpdatedWithConflict) Then
If blnSourceIsWinner Then
pRowChange.CopyColumnFromSource(intColumn)
End If
' For columns that have been updated without any changes, copy column
values from source
ElseIf (ColStatus = REPOLEColumn_UpdatedNoConflict) Then
pRowChange.CopyColumnFromSource(intColumn)
' For columns that have not been updated - do nothing.
ElseIf (ColStatus = REPOLEColumn_NotUpdated) Then
End If
Next intColumn
' Log conflict and call the UpdateRow method to commit all the column value
changes.
'
If strDestinationDateTime.Length > 0 And strDestinationUser.Length > 0 Then
If blnSourceIsWinner Then
strMsg = "Losing update(s) made by " & strDestinationUser
Else
strMsg = "Losing update(s) made by " & strSourceUser
End If
End If
pRowChange.LogConflict(REPOLE_BOOL.REPOLEBool_TRUE ,
REPOLE_CONFLICT_TYPE.REPOLEConflict_ColumnUpdateCo nflict,
REPOLE_BOOL.REPOLEBool_FALSE, strMsg, REPOLE_BOOL.REPOLEBool_FALSE)
Call pRowChange.UpdateRow()
Marshal.FreeHGlobal(myBuffer)
End Sub
Private Function ConvertBufferToDateString(ByVal p As IntPtr) As String
Dim s As String = String.Empty
Dim i(7) As Short
Dim j As Integer
Marshal.Copy(p, i, 0, i.Length)
s = i(0).ToString '4 digit Year
For j = 1 To i.GetUpperBound(0)
s &= i(j).ToString.PadLeft(2, "0"c)
Next
ConvertBufferToDateString = s
End Function
Private Function ConvertBufferToString(ByVal p As IntPtr, ByVal
intBufferLenActual As Integer) As String
Dim i As Integer
Dim s As String = String.Empty
For i = 0 To intBufferLenActual - 1
s &= Chr(Marshal.ReadByte(p, i))
Next
ConvertBufferToString = s
End Function
Private Sub AppendToLog(ByVal pRowChange As IReplRowChange, ByRef s As
String, ByVal inMsg As String)
s = s & ";" & inMsg
If Len(s) < 50 Then Call pRowChange.LogError(REPOLEAllChanges, s)
End Sub
End Class
u rock man!
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
"Douglas Arterburn" <darterburn@.precisdev.com> wrote in message
news:uzhwyd7iFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Someone posted the question "Can anyone point me towards a source code
> listing for a working replication custom resolver written in .Net?" Here
> is an example that I have written.
> Custom resolvers are created by adding a reference to the Microsoft SQL
> Replication Conflict Resolver Library, replrec.dll. Unfortunately, the
> .NET type library importer incorrectly defines the buffer parameter of
> methods GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to
> be the address of an Object. For COM interfaces, which is what
> replrec.dll is supposed to be, that implies a COM-VARIANT parameter
> passing mechanism, but this is not what Replrec passes back. Replrec is
> simply passing back the address of a buffer containing the column value
> which you have to decode.
> So the solution involves correcting the method definitions so that a
> buffer address can be passed and using the .NET Marshal routines to create
> the buffer and move bytes out of the buffer.
> Here are the steps involved, followed by a code example that resolves
> conflicts by using the column values from the row with the latest date in
> user defined column ModifyDate.
> 1. Open a Visual Studio .NET 2003 Command Prompt
> 2. tlbimp "c:\Program Files\Microsoft SQL Server\80\COM\replrec.dll"
> /OUT:SQLResolver_import.dll
> 3. ildasm "SQLResolver_import.dll" /OUT=SQLResolver.il
> 4. Change line "[out] object& marshal( struct) pvBuffer" for the methods
> GetSourceColumnValue, GetDestinationColumnValue, and SetColumn to "[out]
> int32 pvBuffer"
> 5. ilasm SQLResolver.il /OUT=SQLResolver.dll /dll
> 6. Create a new .NET Windows Control Library project
> 7. Remove the wizard generated control
> 8. Use "Add New Item..." to add a new COM class
> 9. Add reference to SQLResolver.dll created in step #5
> Imports System.Text
> Imports SQLResolver
> Imports System.Runtime.InteropServices
> Imports System.Runtime.InteropServices.MarshalAsAttribute
> Imports SQLResolver.REPOLE_CHANGE_TYPE
> Imports SQLResolver.REPOLE_COLSTATUS_TYPE
> <ComClass(ComClass1.ClassId, ComClass1.InterfaceId, ComClass1.EventsId)> _
> Public Class ComClass1
> Implements SQLResolver.IVBCustomResolver
> Private Const MAX_BUFFER_SIZE As Integer = 1048576
> Private Const MAX_NAME_LENGTH As Integer = 128
> #Region "COM GUIDs"
> ' These GUIDs provide the COM identity for this class
> ' and its COM interfaces. If you change them, existing
> ' clients will no longer be able to access the class.
> Public Const ClassId As String = "825818F7-3531-4524-8B07-72343EFDC8AB"
> Public Const InterfaceId As String =
> "CECFBB8F-584F-4733-9373-B69AFA6F117F"
> Public Const EventsId As String =
> "5D55BA40-A438-4FD4-BA8B-05095DC89948"
> #End Region
> ' A creatable COM class must have a Public Sub New()
> ' with no parameters, otherwise, the class will not be
> ' registered in the COM registry and cannot be created
> ' via CreateObject.
> Public Sub New()
> MyBase.New()
> End Sub
> Public Sub GetHandledStates(ByRef ResolverBm As Integer) Implements
> IVBCustomResolver.GetHandledStates
> ResolverBm = REPOLEUpdateConflicts
> End Sub
> Public Sub Reconcile(ByVal pRowChange As IReplRowChange, ByVal dwFlags As
> Integer, ByVal pvReserved As IReplRowChange) Implements
> IVBCustomResolver.Reconcile
> Dim cntColumns As Integer
> Dim intColumn As Integer
> Dim strColumnName As String
> Dim strLogMessage As String
> Dim WinningData As Object
> Dim blnSourceIsWinner As Boolean
> Dim ColStatus As SQLResolver.REPOLE_COLSTATUS_TYPE
> Dim intBufferLenActual As Integer
> Dim intBufferLen As Integer
> Dim strDestinationDateTime As String
> Dim strSourceDateTime As String
> Dim strDestinationUser As String
> Dim strSourceUser As String
> Dim myBuffer As IntPtr = Marshal.AllocHGlobal(MAX_BUFFER_SIZE)
> Dim strMsg As String
> 'If Not Debugger.IsAttached Then
> ' Debugger.Launch()
> 'Else
> ' Debugger.Break()
> 'End If
> Call pRowChange.GetNumColumns(cntColumns)
> For intColumn = 1 To cntColumns
> strColumnName = " ".PadRight(MAX_NAME_LENGTH)
> ' strColumnName.PadRight(OSQL_SYSNAME_SET, Chr(vbNull))
> Call pRowChange.GetColumnName(intColumn, strColumnName,
> MAX_NAME_LENGTH)
> ' strColumnName.TrimEnd(Chr(vbNull))
> strColumnName = strColumnName.TrimEnd()
> If (String.Compare(strColumnName, "ModifyDate", True) = 0) Then
> pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strDestinationDateTime = ConvertBufferToDateString(myBuffer)
> pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strSourceDateTime = ConvertBufferToDateString(myBuffer)
> If strSourceDateTime > strDestinationDateTime Then
> blnSourceIsWinner = True
> Else
> blnSourceIsWinner = False
> End If
> End If
> If (String.Compare(strColumnName, "ModifyUser", True) = 0) Then
> pRowChange.GetDestinationColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strDestinationUser = ConvertBufferToString(myBuffer, intBufferLenActual)
> pRowChange.GetSourceColumnValue(intColumn, myBuffer.ToInt32,
> MAX_BUFFER_SIZE, intBufferLenActual)
> strSourceUser = ConvertBufferToString(myBuffer, intBufferLenActual)
> End If
> Next intColumn
> For intColumn = 1 To cntColumns
> 'Get the column status of each column
> pRowChange.GetColumnStatus(intColumn, ColStatus)
> ' If the column has been updated at both the Publisher and Subscriber
> If (ColStatus = REPOLEColumn_UpdatedWithConflict) Then
> If blnSourceIsWinner Then
> pRowChange.CopyColumnFromSource(intColumn)
> End If
> ' For columns that have been updated without any changes, copy column
> values from source
> ElseIf (ColStatus = REPOLEColumn_UpdatedNoConflict) Then
> pRowChange.CopyColumnFromSource(intColumn)
> ' For columns that have not been updated - do nothing.
> ElseIf (ColStatus = REPOLEColumn_NotUpdated) Then
> End If
> Next intColumn
> ' Log conflict and call the UpdateRow method to commit all the column
> value changes.
> '
> If strDestinationDateTime.Length > 0 And strDestinationUser.Length > 0
> Then
> If blnSourceIsWinner Then
> strMsg = "Losing update(s) made by " & strDestinationUser
> Else
> strMsg = "Losing update(s) made by " & strSourceUser
> End If
> End If
> pRowChange.LogConflict(REPOLE_BOOL.REPOLEBool_TRUE ,
> REPOLE_CONFLICT_TYPE.REPOLEConflict_ColumnUpdateCo nflict,
> REPOLE_BOOL.REPOLEBool_FALSE, strMsg, REPOLE_BOOL.REPOLEBool_FALSE)
> Call pRowChange.UpdateRow()
> Marshal.FreeHGlobal(myBuffer)
>
> End Sub
> Private Function ConvertBufferToDateString(ByVal p As IntPtr) As String
> Dim s As String = String.Empty
> Dim i(7) As Short
> Dim j As Integer
> Marshal.Copy(p, i, 0, i.Length)
> s = i(0).ToString '4 digit Year
> For j = 1 To i.GetUpperBound(0)
> s &= i(j).ToString.PadLeft(2, "0"c)
> Next
> ConvertBufferToDateString = s
> End Function
> Private Function ConvertBufferToString(ByVal p As IntPtr, ByVal
> intBufferLenActual As Integer) As String
> Dim i As Integer
> Dim s As String = String.Empty
> For i = 0 To intBufferLenActual - 1
> s &= Chr(Marshal.ReadByte(p, i))
> Next
> ConvertBufferToString = s
> End Function
> Private Sub AppendToLog(ByVal pRowChange As IReplRowChange, ByRef s As
> String, ByVal inMsg As String)
> s = s & ";" & inMsg
> If Len(s) < 50 Then Call pRowChange.LogError(REPOLEAllChanges, s)
> End Sub
> End Class
>