Output clause will return information from or expressions based on each row affected by INSERT, UPDATE, DELETE or MERGE Statement. Output clause has access to inserted and deleted tables. This a very good feature in SQL Server started from 2005.Here I am explaining with a simple example : Inserting a row into Employee Details and displaying the record details with database user who performed this operation using output clause.
--Declaring a Temp table for Displaying who has inserted the data
CREATE TABLE #tmp
(
name VARCHAR(50),
salary FLOAT,
user_updated VARCHAR(50)
)
GO
INSERT INTO dbo.employeedetails
OUTPUT inserted.name,
inserted.salary,
Suser_name()
INTO #tmp
VALUES ('Manu',
45000)
GO
SELECT *
FROM #tmp
RESULT:
Preethy
13 years agoHi varun,
When i executed my procedure to return the SPs from a remote server it errors out like the following…..
The object name ‘xxx.DB_Main.dbo.sys.procedures’
contains more than the maximum number of prefixes. The maximum is 3.
varun
13 years agoHi Preethy,
you can use openrowset function to achieve this.Also please check whether you have access to sys.procedures views.
Example :
SELECT a.*
FROM OPENROWSET(‘SQLNCLI’, ‘Server=Servername;Trusted_Connection=yes;’,
‘SELECT name
sys.procedures
‘) AS a;
Thanks