Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The OUPUT Command


The OUPUT Command

Author
Message
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6275 Visits: 1407
Nice description....... very useful oneSmile



Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7630 Visits: 18043
Vijay - I think that if you were to try to execute that as a recordset - you would get data back, just as if you had a "straight" SELECT statement in there.

Hard to tell what's going on now, though - the code you posted don't actually execute the ADO command. In other words - in "traditional" ADO - you'd instantiate a recordset object and execute the command into it, thus giving you access to the rows being OUTPUT.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1499
vijay_download (11/1/2007)
Hi,

I m NOT using storedprocedure. I m using the ADO command object which will stored the compiled command text. I m passing only parameter value in UPDATE command text. And it doesn't return value or parameter.

How can i do this.

Pls see my command syntex below.


Set rsPending = New ADODB.Command
Set rsPending.ActiveConnection = cnnDefault
rsPending.CommandType = adCmdText

rsPending.CommandText = "UPDATE vTableServerAllocationView _
& " SET EG0_User='varUserid' OUTPUT INSERTED.Sr_No,INSERTED.Doc_No WHERE Sr_No =?"

rsPending.Parameters.Append rsPending.CreateParameter("SN", adDouble, adParamInput)

rsPending.Prepared = True


Vijay,

It doen't appear that you are writing in .NET. Too bad. If I'm wrong then...

I'm sure I'll get a lot of flack for suggesting this, but it works, and under the proper circumstances, there is nothing at all wrong with it. Forget the parameter. If your code returns a record in your query window, then retrieve that record with a DataReader. Just replace the code after setting the CommandText property, with the following.

Dim dr As SqlDataReader
dr = rsPending.ExecuteReader
If dr.HasRows = True Then
dr.Read()
'Collect the field data from dr('FieldName' [or ordinal]).ToString for each column.
End If

By the way... GOOD article. GOOD discussion.

Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

FVC
FVC
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 66
also be aware that using the output command to log/move the deleted data in a log or archive table, that this might affect performance (depending on how much records you process).
Instead of 2 times the related index scans when performing first an insert into the log or archive table and afterwards the delete, the output clause has only 1 time the index scan, but performs a table spool that can cause even more IO.



harij73
harij73
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 133
This is a very simple article which lists the functionality of the output command. However, it would be an excellent article if the following were also included:

a) what happens to the output values given by the output command and where it would be stored
b) limitations
c) performance gain when comparing to triggers

Otherwise a very valuable article, for the beginners to read with.

Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search