Error inserting into Table Datatype with Identity Column

Hi
We are using the code below to simulate a cursor using the Table Datatype
but we are getting an error when inserting the records.
Server: Msg 8101, Level 16, State 1, Line 19
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 8101, Level 16, State 1, Line 28
An explicit value for the identity column in table '@.tblImports' can only be
specified when a column list is used and IDENTITY_INSERT is ON.
Any help would be much appreciated
Thanks
B
CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Type
VARCHAR(55))
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @.Output VARCHAR(8000)
DECLARE @.Imports VARCHAR(355)
DECLARE @.Description VARCHAR(355)
DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
VARCHAR(355), RowId INT IDENTITY(1, 1))
DECLARE @.count INT
DECLARE @.iRow INT
SET @.Output = ''
IF @.Type = 'All Items'
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
ORDER BY Usr_Imports.ID
END
ELSE
BEGIN
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
FROM Usr_Imports
INNER JOIN
TaskDB.dbo.DM_imports_friendlyName
ON
Usr_Imports.Import_Document collate database_default =
TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
WHERE EntityRef = @.Client AND MatterNo =
@.OrderNo
ORDER BY Usr_Imports.ID
END
/** Simulate Cursor
****************************************
****************************/
SET @.count = @.@.ROWCOUNT
SET @.iRow = 1
WHILE @.iRow <= @.count
BEGIN
SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
FROM @.tblImports
WHERE RowId = @.iRow
SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
SET @.iRow = @.iRow + 1
END
RETURN @.Output
ENDAlways specify the column list in an INSERT statement:
INSERT @.tblImports (dm_ImportDesc, [Description])
SELECT dm_ImportDesc, [Description]
FROM ...
I recommend that you don't use ORDER BY in the INSERT statement. It is
not necessarily guaranteed that the IDENTITY column will be populated
in the order you specify.
If you explain your requirement I'm sure someone can suggest something
better. Why do concatentation and formatting in the database anyway?
David Portas
SQL Server MVP
--|||Ben
error clearly states that you are trying to update an identity column in the
function( not in cucrsor)
INSERT @.tblImports
SELECT dm_ImportDesc, [Description]
you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
Post DDL
--
Regards
R.D
--Knowledge gets doubled when shared
"Ben" wrote:

> Hi
>
> We are using the code below to simulate a cursor using the Table Datatype
> but we are getting an error when inserting the records.
>
> Server: Msg 8101, Level 16, State 1, Line 19
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
> Server: Msg 8101, Level 16, State 1, Line 28
> An explicit value for the identity column in table '@.tblImports' can only
be
> specified when a column list is used and IDENTITY_INSERT is ON.
>
> Any help would be much appreciated
>
> Thanks
> B
>
> CREATE FUNCTION dbo.fcn_ImportDocs (@.Client VARCHAR(15), @.OrderNo INT, @.Ty
pe
> VARCHAR(55))
> RETURNS VARCHAR(8000) AS
> BEGIN
>
> DECLARE @.Output VARCHAR(8000)
> DECLARE @.Imports VARCHAR(355)
> DECLARE @.Description VARCHAR(355)
> DECLARE @.tblImports TABLE(dm_ImportDesc VARCHAR(355), [Description]
> VARCHAR(355), RowId INT IDENTITY(1, 1))
> DECLARE @.count INT
> DECLARE @.iRow INT
> SET @.Output = ''
>
> IF @.Type = 'All Items'
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> ORDER BY Usr_Imports.ID
> END
> ELSE
> BEGIN
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> FROM Usr_Imports
> INNER JOIN
> TaskDB.dbo.DM_imports_friendlyName
> ON
> Usr_Imports.Import_Document collate database_default =
> TaskDB.dbo.DM_imports_friendlyName.dm_importdesc collate database_default
> WHERE EntityRef = @.Client AND MatterNo
=
> @.OrderNo
> ORDER BY Usr_Imports.ID
> END
>
> /** Simulate Cursor
> ****************************************
****************************/
> SET @.count = @.@.ROWCOUNT
> SET @.iRow = 1
> WHILE @.iRow <= @.count
> BEGIN
> SELECT @.Imports = dm_ImportDesc, @.Description = [Description]
> FROM @.tblImports
> WHERE RowId = @.iRow
>
> SELECT @.Output = @.Output + CHAR(11) + CASE WHEN @.Type = 'All
> Items' THEN @.Description + CHAR(11) + CHAR(11) END + CHAR(11) + CHAR(11)
> SET @.iRow = @.iRow + 1
> END
>
> RETURN @.Output
> END
>
>|||David,
It's with yukon.
http://blogs.msdn.com/sqltips/archi.../20/441053.aspx
-oj
"David Portas"
> I recommend that you don't use ORDER BY in the INSERT statement. It is
> not necessarily guaranteed that the IDENTITY column will be populated
> in the order you specify.|||Thank you both for your input, it works perfectly now.
Regards
B
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:6FEF31BA-887B-49DA-A0DD-2EA4B07C5A71@.microsoft.com...
> Ben
> error clearly states that you are trying to update an identity column in
the
> function( not in cucrsor)
> INSERT @.tblImports
> SELECT dm_ImportDesc, [Description]
> you can't update identity column explicitly unless SET INSERT_IDENTITY ON.
> Post DDL
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Ben" wrote:
>
Datatype
only be
only be
@.Type
database_default
database_default
MatterNo =
[Description]
'All|||> INSERT queries that use SELECT with ORDER BY to populate rows
> guarantees how identity values are computed but not the order
> in which the rows are inserted
Possibly, but the same claim is documented in a KB for 2000 and in that
case it is inaccurate. See the following link for Gert-Jan's repro.
Haven't tested this on 2005 but even if it works I'm not sure how
confident I would be about it, given the history and the kludgy nature
of this "feature".:
http://groups.google.co.uk/group/mi...bfd47d975aca778
Now that we have RECORD_NUMBER() the ORDER BY in an INSERT is
redundant. RECORD_NUMBER() should be preferred IMO.
David Portas
SQL Server MVP
--sql

Error handling in cursor

I have cursor that loops through some records and calls a stored procedure. Sometimes the stored procedure raises an error. When an error is raised the cursor stops looping.

I would like the cursor to loop to the next record and continue executing the stored procedure. How do I implement this functionality? I suspect it has to do with using @.@.Error but I have limited experience with it. Thanks in advance.

Code Snippet

DECLARE PKIDs_Cursor CURSOR FOR

SELECT Id as PKID FROM Table1

OPEN PKIDs_Cursor;

DECLARE @.PKs VARCHAR(20)

FETCH NEXT FROM PKIDs_Cursor INTO @.PKs;

WHILE @.@.FETCH_STATUS = 0

BEGIN

EXEC dbo.spProcessInfo @.PKs, NULL;

FETCH NEXT FROM PKIDs_Cursor INTO @.PKs;

END;

CLOSE PKIDs_Cursor;

DEALLOCATE PKIDs_Cursor;

By default, SQL Server follows “ON ERROR RESUME NEXT”, that means when error occurs (if it is not critical error) it allows continuing to execute the next statement. Considering this statement your loop won’t break. You need not to use the @.@.ERROR here. If you want to force to discontinue the loop then only you need this variable.

Note:

In SQL Server 2000 you can’t suppers the error message thrown by the server. But the execution will continue.

If you use SQL Server 2005 then you can use the TRY..CATCH to suppress the error messages.

If you provide the error message it will be more helpful.

|||

Thank you for the reply Manivannan. I am using SQL Server 2000. The error message is a custom message raised by the stored procedure:

-2 Warning: Mapping failed - No actions taken.

“ON ERROR RESUME NEXT” behavior does not appear to be in effect. Any ideas?|||

What Severity Level you used on your Raiserror? Severity Level might affect the work-flow of your batch.