• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 696
  • Last Modified:

output clause sqlsrv

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
talonsblade
Asked:
talonsblade
  • 3
  • 2
1 Solution
 
talonsbladeAuthor Commented:
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
 
BrandonGalderisiCommented:
after your insert, execute:

select scope_identity()

That will give you the identity value of the inserted record.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
talonsbladeAuthor Commented:
BraindonGalderisi   you must have been typing your reply while i was typing my quetions.  please refer to my last comment
0
 
BrandonGalderisiCommented:
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
 
talonsbladeAuthor Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now