?
Solved

output clause sqlsrv

Posted on 2010-09-17
6
Medium Priority
?
703 Views
Last Modified: 2012-05-10
I have a webpage i am working on that tracks absences.  it uses a php page to input records into a mssql 2005 database.  i use the sqlsrv driver as the go between.  

what i need to do is use sqlsrv_query to run an insert statment which uses the output clause to return the id(autonumber) field.  i have been searching for an hour and have not found this.  there are plenty out there about using a stored procedure to return the output but i am not running a stored procedure, i am using sqlsrv_query to run a statment with an output clause.

i need to know if this can be done, and if it can, how would i acess the the returned id field.
0
Comment
Question by:talonsblade
  • 3
  • 2
6 Comments
 
LVL 13

Expert Comment

by:darren-w-
ID: 33701827
0
 

Author Comment

by:talonsblade
ID: 33702039
i have read this but had questions. first off even microsoft admits there is a bug with the SCOPE IDENTITY() function when it has to do with parallel processing, not sure if that would cause problems for me, dont think it will.
here is the website:
http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value 

my second question is that this website is going to be open to al crew leads, what happens if two people try to add a record at the same time, is there a chance that it could return the wrong record id?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33702045
after your insert, execute:

select scope_identity()

That will give you the identity value of the inserted record.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 

Author Comment

by:talonsblade
ID: 33702089
BraindonGalderisi   you must have been typing your reply while i was typing my quetions.  please refer to my last comment
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 33702358
That bug is reported in SQL 2005 SP2.  Have you looked at the workarounds?  In particular, the following...


    Declare @InsertedData table (ID int)
    INSERT INTO Orders (VendorOrderID, StoreID, DateCreated)
        OUTPUT Inserted.ID
        INTO @InsertedData
    VALUES (@VendorOrderID, @StoreID, @DateCreated)
    SELECT ID FROM @InsertedData
0
 

Author Comment

by:talonsblade
ID: 33703237
i got what you suggested working.  this is my test code


$myQuery = "INSERT INTO dbo.AbsEvents (Perno, Category, Title, evtStart, evtEnd, sat, sun, defHours, InActive, Remarks) output inserted.id  SELECT '00000000', CatNum, 'test', '1/1/2009', '1/1/2009', 0, 0, 0,0,'test' FROM dbo.Absence_Category WHERE (EventClass = 'clsVac')";

$result = sqlsrv_query($conn,$myQuery);
if( $result === false)
{
	print_r(sqlsrv_errors());
}
while($myData = sqlsrv_fetch_array($result,SQLSRV_FETCH_ASSOC))
{
	print_r($myData);
}

Open in new window

0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question