June 26, 2012 at 8:29 am
Hi,
I am using SQL Server 2008 R2
I hav this code
DECLARE @Document INT
INSERT INTO C_Documents
(DocumentSectionID,FullPath,FileName,FileTitle,Description,UserID,DateCreated,TimeStamp)
OUTPUT INSERTED.DocumentID INTO @Document
VALUES (@DocumentSectionID,@FullPath,@FileName,@FileTitle,@Description,@UserId, GETDATE(),GETDATE())
--SET @Document = IDENT_CURRENT('C_Documents')
INSERT INTO F_ProjectDocument(ProjectID,RoundID,DocumentID,DocumentTypeID,FFPUploadID,UploadOn,UploadBy,DateCreated,UserID,TimeStamp)
VALUES (@P,@R,@Document,@DocumentTypeID,@FFPUploadID,GETDATE(),@UserId,GETDATE(),@UserId,GETDATE())
This is not working on SQL Server 2005, not on SQL Server 2008 R2.
how can I do this on SQL Server 2008 R2.
June 26, 2012 at 8:33 am
P.S. IDENT_CURRENT() , Scope_Identity() , @@Identity work wrong when several people work parallel.
June 26, 2012 at 8:42 am
What do you mean by not working?
Where did you declare your variables?
June 26, 2012 at 9:02 am
Sorry I had to say working only on SQL Server 2005:-D I want something like it to work on sql server 2008 R2.
This is a part of SP, I pass variables by SP except @document.
The error is 'Must declare the table variable "@Document".'
June 26, 2012 at 9:13 am
anush_tar (6/26/2012)
P.S. IDENT_CURRENT() , Scope_Identity() , @@Identity work wrong when several people work parallel.
What do you mean they work wrong??? The don't work wrong but each of them does have their own subtleties to understand. IDENT_CURRENT() is most likely not what you want for concurrency but Scope_Identity() and @@Identity both have their uses. Seems like Scope_Identity() should work just fine here.
http://msdn.microsoft.com/en-us/library/ms187342.aspx
http://msdn.microsoft.com/en-us/library/ms190315.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2012 at 9:16 am
Try the following code snippet based on what you originally posted:
DECLARE @Document TABLE (DocumentID INT);
INSERT INTO C_Documents (
DocumentSectionID,
FullPath,
FileName,
FileTitle,
Description,
UserID,
DateCreated,
TimeStamp
)
OUTPUT INSERTED.DocumentID INTO @Document(DocumentID)
VALUES (
@DocumentSectionID,
@FullPath,
@FileName,
@FileTitle,
@Description,
GETDATE(),
GETDATE()
);
INSERT INTO F_ProjectDocument(
ProjectID,
RoundID,
DocumentID,
DocumentTypeID,
FFPUploadID,
UploadOn,
UploadBy,
DateCreated,
UserID,
TimeStamp
)
SELECT
--VALUES (
@P,
@r,
D.DocumentID,
@DocumentTypeID,
@FFPUploadID,
GETDATE(),
GETDATE(),
GETDATE()
--)
FROM
@Document d;
June 26, 2012 at 6:34 pm
anush_tar (6/26/2012)
Sorry I had to say working only on SQL Server 2005:-D I want something like it to work on sql server 2008 R2.This is a part of SP, I pass variables by SP except @document.
The error is 'Must declare the table variable "@Document".'
The problem is @Document after INTO assumes that @Document is a table variable and not an INT.
OUTPUT INSERTED.DocumentID INTO @Document
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 26, 2012 at 7:39 pm
Made a slight change to my code. I noticed I forgot to comment a closing paren.
June 26, 2012 at 11:56 pm
Sean Lange:
I have After Trigger on table, that's why I can't use SCOPE_IDENTITY() or @@IDENTITY. IDENT_CURRENT is not limited by scope and session.
That's why when 2 or more people call the same SP at the same time it mix last inserted values.
June 27, 2012 at 12:09 am
The only time an after trigger may cause trouble with picking up identity values is if inside the after trigger you are inserting into another table (lets call it B) that also has an identity column.
If that is the case than @@identity does return the "wrong" value in as much as it gives you the most recent identity for the table "B".
However using scope_identity() is fine, it goves you the last identity for the original table.
Mike
June 27, 2012 at 12:23 am
anush_tar (6/26/2012)
Sean Lange:I have After Trigger on table, that's why I can't use SCOPE_IDENTITY() or @@IDENTITY. IDENT_CURRENT is not limited by scope and session.
That's why when 2 or more people call the same SP at the same time it mix last inserted values.
Have you tried the code I provided?
June 27, 2012 at 12:25 am
Sean Lange (6/26/2012)
anush_tar (6/26/2012)
P.S. IDENT_CURRENT() , Scope_Identity() , @@Identity work wrong when several people work parallel.What do you mean they work wrong??? The don't work wrong but each of them does have their own subtleties to understand. IDENT_CURRENT() is most likely not what you want for concurrency but Scope_Identity() and @@Identity both have their uses. Seems like Scope_Identity() should work just fine here.
It works, thank you very mush 🙂
June 27, 2012 at 12:53 am
Lynn Pettis (6/27/2012)
anush_tar (6/26/2012)
Sean Lange:I have After Trigger on table, that's why I can't use SCOPE_IDENTITY() or @@IDENTITY. IDENT_CURRENT is not limited by scope and session.
That's why when 2 or more people call the same SP at the same time it mix last inserted values.
Have you tried the code I provided?
Yes I tried, and it works, thank you 🙂 By mistake I posted this comment to another user 😀
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply