SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software

Development Forums for Programmer, Administrators and Users

Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.

The OUTPUT clause cannot be used in an INSERT...EXEC statement

Last post 01-28-2010, 8:09 AM by eralper. 2 replies.
Sort Posts:
  •  01-11-2010, 3:52 PM 20098

    The OUTPUT clause cannot be used in an INSERT...EXEC statement

    Hello t-sql developers,
    If you know the OUTPUT clause enhancement introduced with MS SQL Server 2005 T-SQL improvements, you might probably using t-sql output clause statements in your insert, update, or delete statements.

    While I was working on a t-sql convert function, I had tried to make it work the following sql output clause statement.

    INSERT INTO #tt(datestring)
      OUTPUT INSERTED.id INTO @id(id)
    exec sp_executesql @sql;

    Unfortunately I had the following t-sql exception after the above output clause with insert statement.

    Msg 483, Level 16, State 2, Line 25
    The OUTPUT clause cannot be used in an INSERT...EXEC statement.


    So I had find the solution back in the identity functions.
    I preferred to use IDENT_CURRENT instead of using the value of auto identity column that I used in the insert ... output clause

    SELECT IDENT_CURRENT;

  •  01-11-2010, 3:54 PM 20099 in reply to 20098

    Re: The OUTPUT clause cannot be used in an INSERT...EXEC statement

    Hello,
    By the way for sql developers not yet worked with T-SQL Output Clause, you can refer to sql tutorial MS SQL Server 2005 T-SQL OUTPUT Clause Sample Code with Insert, Update, Delete Statements for t-sql examples.
  •  01-28-2010, 8:09 AM 20850 in reply to 20099

    Re: The OUTPUT clause cannot be used in an INSERT...EXEC statement

    Hello,

    I got the same error this time in another application.

    Msg 483, Level 16, State 2, Line 42
    The OUTPUT clause cannot be used in an INSERT...EXEC statement.


    I used an additional temporary table in order to avoid OUTPUT Clause with INSERT...EXEC statement.
    Here how I solved this sql exception :


    OPEN _cursor
    FETCH NEXT FROM _cursor INTO @ServerName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    EXEC master.dbo.sp_addlinkedserver @server = @ServerName, @srvproduct=N'SQL Server'
    SET @sql = N'SELECT * FROM [' + @ServerName + '].msdb.dbo.sysmail_profile'

    INSERT INTO #sysmail_profile2 EXEC sp_ExecuteSQL @sql

    INSERT INTO #sysmail_profile SELECT @ServerName, * FROM #sysmail_profile2
    TRUNCATE TABLE #sysmail_profile2

    EXEC master.dbo.sp_dropserver @server=@ServerName, @droplogins='droplogins'

    FETCH NEXT FROM _cursor INTO @ServerName
    END
View as RSS news feed in XML
Copyright © 2004 - 2020 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems