DTS, SQL Server, Show PK value in err message

zpluug · March 6th, 2006, 10:55 AM

Hello,

VB.Net, SQL Server, DTS:
Is it possible to show the value of a primary key field in an error message, to easily correct the error?

I use DTS to transfer data from client apps, that use an Access db, to an online SQL Server which serves as a temporary db, from where data is downloaded to the central SQL Server, by the head office app, using DTS again, where data is treated.

Thanks

jbenson001 · March 6th, 2006, 03:04 PM

Unfortunatly, sql server only displays a generic Primary Key violation error. It will be up to you to find the offending data.

zpluug · March 7th, 2006, 06:30 PM

Thanks jbenson001 for the reply.

I was looking for a suggestion that say, would be something like this:

(In human language!)
If error.pErrorCode = (a number relevant to PK error) Then
msgBox("The duplicate PK field = " & the duplicate ID Value)
End If

I hope there's a way one could do this, because it so happens that for instance, 10,000 records are transferred and there are 50 duplicate PK values, how does one deal with this?
How do you find these records?

Remember, in our situation, there are many clients (lay people) who are transferring data.

Thanks

jbenson001 · March 8th, 2006, 12:50 AM

If you want to do it from .NET, you could import your rows that you want to insert, into a temp dataset. Then you can loop through the rows of the ds, grab the id value and query the table you want to insert it in:

Select Count(*)
From <table you want to insert into>
where IDColumn = <IDColumn from dataset>

This pseudo sql will return a count where the ids match. If the count > 0 then you know you have a duplicate, and you can print out a message.

If you can do this in pure sql then you can do something like:
Select 'This ID Exists Already: ' + convert(varchar(10), t1.ID)
From table1 t1
Inner Join <Destination Table> dt ON t1.ID = dt.ID

Hope this gets you going in the right direction....

Jim

zpluug · March 8th, 2006, 11:56 AM

jbenson001, I appreciate your response.
I just read it and I haven't tried it yet.

It is amazing why this is not possible by say, a property of the error object. How do big guys deal with this?:)

It is a DTS module,like the one you can save when you import and export data in SQL Server 2000.
Of course, I have changed dozens of things and I have manually upgraded it to .Net.
I'll think about your suggestion and see what I can do.
Once again, Thanks

jbenson001 · March 8th, 2006, 12:09 PM

I'm sure the MS can display the actual row in the error message not sure. If they can, not sure why they have not provided it. There are workarounds like I have shown. For now you will have to go through in some manual way and find the data that causes the problem.

zpluug · March 9th, 2006, 07:24 PM

I have been thinking of comparing the rows (IDs) to be sent with the rows in the destination table, before running the actual DTS module.

I had thought of this as a last resort because of the double loop. It seems I'll have to make do with that.
I haven't tested this, I just wrote the code it may contain errors, it's just to give the idea

Code:

    Sub CheckForDuplicateIDs()
        Dim i, j As Integer
        Dim da As SqlDataAdapter
        Dim dsSource As DataSet
        Dim dsDestination As DataSet
        'Initialize the datasets, the da, the conn and fill the datesets.
        '.....................
        '.....................

        For i = 0 To dsSource.Tables("TheTable").Rows.Count - 1
            For j = 0 To dsDestination.Tables("TheTable").Rows.Count - 1
                If dsSource.Tables("TheTable").Rows(i).Item("ID") = dsDestination.Tables("TheTable").Rows(j).Item("ID") Then
                    MsgBox("The ID you entered already exists", MsgBoxStyle.Exclamation, "ID Already Entered")
                    Exit Sub
                End If
            Next
        Next
    End Sub

Thanks anyway for the concern.

Z.

jbenson001 · March 10th, 2006, 12:50 AM

Glad to help .. Let me know how it goes..

JIm