?
Solved

output clause sqlsrv

Posted on 2010-09-17
6
Medium Priority
?
678 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 

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 MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A phishing scam that claims a recipient’s credit card details have been “suspended” is the latest trend in spoof emails.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

762 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