Error 8162

[slatet]

We are getting an error 8162 when trying to insert a record. I understand the reasoning for this, having read about it. But we still do not understand where the problem lies. The error actually states that "TaskExternalTaskID" is the problem.

Here is the .net code calling it:
With cmd
.CommandType = adCmdStoredProc
.CommandText = "InsertTask"

.Parameters.Append(.CreateParameter("TaskSITEID", adVarChar, adParamInput, 10, sSiteID))
.Parameters.Append(.CreateParameter("TaskRootTaskID", adBigInt, adParamInput, , iTaskRootTaskID))
.Parameters.Append(.CreateParameter("TaskSortPath", adVarChar, adParamInput, 1000, sTaskSortPath))
.Parameters.Append(.CreateParameter("TaskType", adInteger, adParamInput, , iTaskType))
.Parameters.Append(.CreateParameter("TaskClassification", adVarChar, adParamInput, 50, sTaskClassification))
.Parameters.Append(.CreateParameter("TaskParent", adBigInt, adParamInput, , iParentTask))
.Parameters.Append(.CreateParameter("TaskDateIssued", adDBDate, adParamInput, , sDateIssued))
.Parameters.Append(.CreateParameter("TaskTimeIssued", adDBDate, adParamInput, , sTimeIssued))
.Parameters.Append(.CreateParameter("TaskDaysToDue", adInteger, adParamInput, , iDaysToDue))
.Parameters.Append(.CreateParameter("TaskDateDue", adDBDate, adParamInput, , sDateDue))
.Parameters.Append(.CreateParameter("TaskDaysToReminder", adInteger, adParamInput, , iDaysToReminder))
.Parameters.Append(.CreateParameter("TaskReminderDate", adDBDate, adParamInput, , sReminderDate))
.Parameters.Append(.CreateParameter("TaskSendReminder", adInteger, adParamInput, , iSendReminder))
.Parameters.Append(.CreateParameter("TaskOverdueNotice", adInteger, adParamInput, , iOverdueNotice))
.Parameters.Append(.CreateParameter("TaskSourceOffice", adVarChar, adParamInput, 30, sSourceOffice))
.Parameters.Append(.CreateParameter("TaskExternalDueDate", adDBDate, adParamInput, , sExternalDueDate))
.Parameters.Append(.CreateParameter("TaskOriginator", adInteger, adParamInput, , iOriginatorID))
.Parameters.Append(.CreateParameter("TaskRootOriginator", adInteger, adParamInput, , iRootOriginator))
.Parameters.Append(.CreateParameter("TaskAuthor", adInteger, adParamInput, , iAuthorID))
.Parameters.Append(.CreateParameter("TaskSubject", adVarChar, adParamInput, 200, sSubject))
.Parameters.Append(.CreateParameter("TaskRemarks", adVarChar, adParamInput, 4000, sRemarks))
.Parameters.Append(.CreateParameter("TaskHardCopy", adInteger, adParamInput, , iHardCopy))
.Parameters.Append(.CreateParameter("TaskRouteType", adInteger, adParamInput, , iRouteType))
.Parameters.Append(.CreateParameter("TaskHasAttachments", adInteger, adParamInput, , iHasAttachments))
.Parameters.Append(.CreateParameter("TaskAttachmentsID", adVarChar, adParamInput, 50, sTaskAttachmentsID))
.Parameters.Append(.CreateParameter("TaskStatus", adInteger, adParamInput, , iTaskStatus))
.Parameters.Append(.CreateParameter("TaskArchived", adInteger, adParamInput, , iArchived))
.Parameters.Append(.CreateParameter("TaskExternalTaskID", adVarChar, adParamInput, 30, sExternalTaskID))
.Parameters.Append(.CreateParameter("TaskID", adBigInt, adParamOutput))

.Execute()


and here is the beginning of the stored procedure showing where the ouput is declared:

CREATE PROCEDURE dbo.InsertTask (@TaskSITEID varchar(10), @TaskRootTaskID bigint, @TaskSortPath varchar(1000), @TaskType int, @TaskClassification varchar(50), @TaskParent bigint, @TaskDateIssued datetime, @TaskTimeIssued datetime, @TaskDaysToDue int, @TaskDateDue datetime, @TaskDaysToReminder int, @TaskReminderDate datetime, @TaskSendReminder int, @TaskOverdueNotice int, @TaskSourceOffice varchar(30), @TaskExternalDueDate datetime, @TaskOriginator int, @TaskRootOriginator int, @TaskAuthor int, @TaskSubject varchar(200), @TaskRemarks varchar(4000), @TaskHardCopy int, @TaskRouteType int, @TaskHasAttachments int, @TaskAttachmentsID varchar(50), @TaskStatus int, @TaskArchived int, @TaskExternalTaskID varchar(30), @TaskID bigint OUTPUT) AS ......

Can anyone help?

 

[donutman]

This is the SQL Server forum not VB. If you are going to ask the question here, then don't give us ... for the SP you are using.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[slatet]

The research I have done strongly suggests that this is a SQL problem for I have found this exact error in other languages. And if you really want the whole thing:

CREATE PROCEDURE dbo.InsertTask (@TaskSITEID varchar(10), @TaskRootTaskID bigint, @TaskSortPath varchar(1000), @TaskType int, @TaskClassification varchar(50), @TaskParent bigint, @TaskDateIssued datetime, @TaskTimeIssued datetime, @TaskDaysToDue int, @TaskDateDue datetime, @TaskDaysToReminder int, @TaskReminderDate datetime, @TaskSendReminder int, @TaskOverdueNotice int, @TaskSourceOffice varchar(30), @TaskExternalDueDate datetime, @TaskOriginator int, @TaskRootOriginator int, @TaskAuthor int, @TaskSubject varchar(200), @TaskRemarks varchar(4000), @TaskHardCopy int, @TaskRouteType int, @TaskHasAttachments int, @TaskAttachmentsID varchar(50), @TaskStatus int, @TaskArchived int, @TaskExternalTaskID varchar(30), @TaskID bigint OUTPUT) AS SET IDENTITY_INSERT Task ON BEGIN set nocount on DECLARE @rc int, @errtype int, @errno int, @errmsg varchar(255), @LOCALERR int, @REMOTERR int, @SUCCESS int, @FAIL int, @NOW datetime, @TRUE bit, @FALSE bit SELECT @errtype = NULL, @LOCALERR = 0, @REMOTERR = 1, @SUCCESS = 0, @FAIL = -100, @TRUE = 2, @FALSE = 1 IF NOT EXISTS (
SELECT SectionGUID FROM Section WHERE SectionID = @TaskOriginator AND SectionSITEID = @TaskSiteID) BEGIN SELECT @errtype = @LOCALERR, @errno = 100186 GOTO cleanup END IF NOT EXISTS ( SELECT AppUserGUID FROM AppUser WHERE AppUserID = @TaskAuthor AND AppUserSITEID = @TaskSiteID ) BEGIN SELECT @errtype = @LOCALERR, @errno = 100120 GOTO cleanup END IF NOT EXISTS (SELECT TaskStatusDisplay FROM TaskStatus WHERE TaskStatusID = @TaskStatus) BEGIN SELECT @errtype = @LOCALERR, @errno = 100008 GOTO cleanup END DECLARE @Year varchar(4) DECLARE @DayOfYear varchar(3)
DECLARE @GeneratedTask varchar(11)
DECLARE @GeneratedTaskID bigint DECLARE @GeneratedTaskCountID bigint
DECLARE @GeneratedTaskCount varchar(5)
SET @Year = Convert(varchar(4), datepart(yyyy, getDate()))
SET @DayOfYear = Convert(varchar(3), datepart(y, getDate()))
IF Len(@DayOfYear) = 1
BEGIN
SET @DayOfYear = '00' + @DayOfYear
END ELSE IF Len(@DayOfYear) = 2
BEGIN SET @DayOfYear = '0' + @DayOfYear
END SET Exec @GeneratedTaskCountID = GetTaskerDailyCount
SET @GeneratedTaskCount = CONVERT(varchar(5), @GeneratedTaskCountID)

IF Len(@GeneratedTaskCount) = 1
BEGIN
SET @GeneratedTaskCount = '000' + @GeneratedTaskCount
END

ELSE IF Len(@GeneratedTaskCount) = 2
BEGIN
SET @GeneratedTaskCount = '00' + @GeneratedTaskCount
END

ELSE IF Len(@GeneratedTaskCount) =3
BEGIN
SET @GeneratedTaskCount = '0' + @GeneratedTaskCount
END

SET @GeneratedTask = @Year + @DayOfYear + @GeneratedTaskCount
SET @GeneratedTaskID = CONVERT(bigint, @GeneratedTask)
INSERT INTO Task(TaskID, TaskSITEID, TaskRootTaskID, TaskSortPath, TaskType, TaskClassification, TaskParent, TaskDateIssued, TaskTimeIssued, TaskDaysToDue, TaskDateDue, TaskDaysToReminder, TaskReminderDate, TaskSendReminder, TaskOverdueNotice, TaskSourceOffice, TaskExternalDueDate, TaskOriginator, TaskRootOriginator, TaskAuthor, TaskSubject, TaskRemarks, TaskHardCopy, TaskRouteType, TaskAttachmentsID, TaskHasAttachments, TaskStatus, TaskArchived, TaskExternalTaskID)
VALUES(@GeneratedTaskID, @TaskSITEID, @TaskRootTaskID, @TaskSortPath, @TaskType, @TaskClassification, @TaskParent, @TaskDateIssued, @TaskTimeIssued, @TaskDaysToDue, @TaskDateDue, @TaskDaysToReminder, @TaskReminderDate, @TaskSendReminder, @TaskOverdueNotice, @TaskSourceOffice, @TaskExternalDueDate, @TaskOriginator, @TaskRootOriginator, @TaskAuthor, @TaskSubject, @TaskRemarks, @TaskHardCopy, @TaskRouteType, @TaskAttachmentsID, @TaskHasAttachments, @TaskStatus, @TaskArchived, @TaskExternalTaskID)



IF (@@rowcount <> 1 OR @@error <> 0) BEGIN SELECT @errtype = @LOCALERR, @errno = 100152 GOTO cleanup END set @TaskID = SCOPE_IDENTITY () IF ( @@nestlevel < 2 AND @@trancount <> 0 ) BEGIN COMMIT TRAN END cleanup: IF @errtype IS NULL BEGIN RETURN @SUCCESS END IF @errtype = @REMOTERR BEGIN IF ( @@nestlevel < 2 AND @@trancount <> 0 ) BEGIN ROLLBACK TRAN END RETURN @FAIL END IF @errtype = @LOCALERR BEGIN IF ( @@nestlevel < 2 AND @@trancount <> 0 ) BEGIN ROLLBACK TRAN END EXEC @rc = ErrorHandler @errno, @errmsg OUT RAISERROR @errno @errmsg RETURN @FAIL END RETURN @FAIL END SET IDENTITY_INSERT Task OFF

GO

 

[donutman]

Have you tried running that SP in QA (Query Analyzer)?

I know that many error messages aren't very helpful, but to paraphrase them with one word isn't very helpful at all. What is the full error message?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

YOu turn off the identity and then insert a record(why you are doing this makse little sense to me, it defeats the purpose of an identity) but then you try to use scope_identity to get the value of the identity you inserted. Could this be creating your problem?

Questions about posting. See faq183-874

 

[slatet]

I will post the exact message on Monday when I get back to work. But I have not tried it in query analyzer.

To answer the other question about the indentity... this is not my work. We inherited this app and this has been an error all along that I was asked to fix.

And another strange fact, is that it does not always happen.

 

[slatet]

Error 8162 a formal paramenter TaskExternalTaskID was defined as output but the actual parameter not declared output.

 

[donutman]

Well, the error message is clear enough, but I don't see the error that it refers to. Try changing your VB code by commenting out all of the Parameters.append statements and replace the group with just one line:
.Parameters.refresh
Then run the program.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]