Solved

output clause sqlsrv

Posted on 2010-09-17
6
672 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
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

 

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 500 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

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
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 count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

717 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