<DanJ@discussions.microsoft.com> wrote:

>Thanks for your help. Being a newbie to SQL, can you direct me to a good
>resource to learn how to create a parameterized stored procedure.
>
>Danj
>
>"Mary Chipman [MSFT]" wrote:
>
>> If you want to handle this a row at a time, create a parameterized
>> stored procedure that does all your key creation, etc. Then execute
>> the stored procedure via passthrough query for every row, passing it
>> any parameter info. That way you can handle any runtime errors in the
>> stored procedure, and retrieve success/failure/new key values in
>> output parameters.
>>
>> --Mary
>>
>> On Sun, 20 Mar 2005 07:45:02 -0800, "DanJ"
>> <DanJ@discussions.microsoft.com> wrote:
>>
>> >The reason that I am updating one row at a time is because I need to create a
>> >unique hard key for each record to be used for replication. NewPK is a
>> >function to generate that key. ( rstTgt!RouteUID = NewPK) I tried to do this
>> >with a query. But in a query, the function is only run once and it attempts
>> >to put the same value in each record. Is there a way to get the function to
>> >run for each record during the execution of the query?
>> >
>> >
>> >
>> >"Mary Chipman [MSFT]" wrote:
>> >
>> >> I'd recommend ditching the DAO code, which is the worst possible way
>> >> to perform DML with SQL Server. Create a pass-through query instead
>> >> and call it from your code. SQL Server will then execute the entire
>> >> query as a single batch, instead of a row at a time, which is what you
>> >> are doing when you post updates from a recordset. This will never be
>> >> performant, and is guaranteed to have problems with large batches.
>> >>
>> >> --Mary
>> >>
>> >> On Tue, 15 Mar 2005 08:55:11 -0800, "DanJ"
>> >> <DanJ@discussions.microsoft.com> wrote:
>> >>
>> >> >I have an Access database with a SQL Server back-end. I am new to SQL, so
>> >> >the problem I have been having is a real puzzle to me.
>> >> >
>> >> >I have created an Access query called ‘qryIRCasesUnrouted’, which finds
>> >> >records in a table called ‘IR’ that do not have a matching key in another
>> >> >table called ‘tblRoutingLog’. The query is used to find new Incident Reports
>> >> >(Cases) that haven’t been routed so that I can add them to the Routing List
>> >> >for processing and disposition.
>> >> >
>> >> >I then run the following subroutine in Access to add those records to the
>> >> >‘tblRoutingLog’ table:
>> >> >
>> >> >Public Sub ImportIR()
>> >> >On Error GoTo ErrHandler
>> >> >
>> >> >Dim DB As DATABASE, rstSrc As Recordset, rstTgt As Recordset
>> >> >Dim i As Integer, strSrcSQL As String
>> >> >
>> >> >Set DB = CurrentDb
>> >> >strSrcSQL = "SELECT * FROM qryIRCasesUnrouted WHERE ORI = '" &
>> >> >Forms!Login!ORI & "'"
>> >> >Set rstSrc = DB.OpenRecordset(strSrcSQL, dbOpenDynaset, dbSeeChanges)
>> >> >Set rstTgt = DB.OpenRecordset("tblRoutingLog", dbOpenDynaset, dbSeeChanges)
>> >> >
>> >> >With rstSrc
>> >> > Do Until .EOF
>> >> > rstTgt.AddNew
>> >> > rstTgt!RouteUID = NewPK
>> >> > rstTgt!KeyID = !Case_ID
>> >> > rstTgt!RouteRecipient = !LoginName
>> >> > rstTgt!RecvdDate = Now
>> >> > rstTgt!Pending = True
>> >> > rstTgt!ReportType = "Incident Report"
>> >> > rstTgt!Descrip = !Case_Number
>> >> > rstTgt!Status = "A"
>> >> > rstTgt.Update
>> >> > i = i + 1
>> >> > .MoveNext
>> >> > Loop
>> >> > .Close
>> >> > rstTgt.Close
>> >> >End With
>> >> >
>> >> >ImportExit:
>> >> >Set rstSrc = Nothing
>> >> >Set rstTgt = Nothing
>> >> >Exit Sub
>> >> >
>> >> >ErrHandler:
>> >> >
>> >> >MsgBox "Import failed due to Error # " & err.Number & " - " &
>> >> >err.Description, vbCritical + vbOKOnly, "Import failed"
>> >> >Resume ImportExit
>> >> >End Sub
>> >> >
>> >> >The subroutine simply moves through the ‘qryIRCasesUnrouted’ result set and
>> >> >adds the appropriate data to the ‘tblRoutingLog’ SQL table.
>> >> >
>> >> >In most cases, everything works fine. However, I recently had a customer
>> >> >who had over 5000 records to add and I received the following error:
>> >> >
>> >> >Error # 3155 – ODBC –insert on a linked table ‘tblRoutingLog’ failed
>> >> >
>> >> >This doesn’t occur until it has already added 3601 records to the
>> >> >‘tblRoutingLog’ table. Trying to re-run it the second time to add the rest
>> >> >of the records doesn’t work either.
>> >> >
>> >> >I noticed that if I checked the “Create as Clustered” option for the Primary
>> >> >Key of the tblRoutingLog table in Enterprise Manager, I do not get this error
>> >> >and all the records are added.
>> >> >
>> >> >Can anyone explain to me what the problem is? I’m not confident that I
>> >> >won’t have problems with other clients in the future? What does changing the
>> >> >‘Primary Key to clustered do?
>> >> >
>> >> >PS
>> >> >This error doesn’t occur if I import to an Access table either
>> >>
>> >>
>>
>>