@@Identity In Stored Procedure

Jun 28, 2007

Hi,
I have a stored procedure  that insert data into 2 temp tables. The problem that I have is when I insert a data into a first table, how do I get the @@identity value from that table and insert it into the second table?? The following code is what I have:
Create #Temp1
    @StateID Identity,
    @State nvarchar(2),
    @wage money

INSERT INTO #Temp1 (State, wage)

SELECT State, Wage FROM Table1 INNER JOIN Table2 ON Table1.Table1_ID = Table2.Table2_ID

Create #Temp2
    @ID Identity
    @EmployeeID int   
    @StateID Int
    @Field1 Money
    @Field2 Money

INSERT INTO #Temp2 (EmployeeID, StateID, Field1, Field2)

SELECT EmployeeID, StateID, Field1, Field2 FROM SomeTable
 


So, The first part I created a #Temp1 table and insert data into the table. Then after the insert, I want the @@Identity value and insert into the #Temp2 table. This is my first time doing stored procedure, so, I am wondering how do I retrieve the @@identity value and put into the second select statement. Please help.
 
ahTan
 

View 9 Replies


ADVERTISEMENT

Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page

Dec 28, 2005

I have a sub that passes values from my form to my stored procedure.  The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page.  Here's where I'm stuck:    Public Sub InsertOrder()        Conn.Open()        cmd = New SqlCommand("Add_NewOrder", Conn)        cmd.CommandType = CommandType.StoredProcedure        ' pass customer info to stored proc        cmd.Parameters.Add("@FirstName", txtFName.Text)        cmd.Parameters.Add("@LastName", txtLName.Text)        cmd.Parameters.Add("@AddressLine1", txtStreet.Text)        cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue)        cmd.Parameters.Add("@Zip", intZip.Text)        cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text)        cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text)        cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text)        cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text)        cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text)        ' pass order info to stored proc        cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue)        cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue)        cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue)        'Session.Add("FirstName", txtFName.Text)        cmd.ExecuteNonQuery()        cmd = New SqlCommand("Add_EntreeItems", Conn)        cmd.CommandType = CommandType.StoredProcedure        cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc)   <-------------------------        Dim li As ListItem        Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar)        For Each li In chbxl_entrees.Items            If li.Selected Then                p.Value = li.Value                cmd.ExecuteNonQuery()            End If        Next        Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder)  and pass that to my second stored procedure (Add_EntreeItems)

View 9 Replies View Related

Problem With @@IDENTITY In Stored Procedure

Feb 2, 2007

Hello !
I just can't access @@IDENTITY in my requests !Here is my stored procedure : (I removed a few lines for clarity)set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AssignerLicence]
(
@Apprenant int = 0
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @NBLICENCES int
DECLARE @COMMANDEID int
DECLARE @LICENCEID int

SET @NBLICENCES = (SELECT SUM(commande_licences_reste)
FROM Commande
WHERE commande_valide = 1)

IF
@NBLICENCES > 0
BEGIN

UPDATE Commande
SET commande_licences_reste = (commande_licences_reste - 1)
WHERE commande_id =
(SELECT TOP 1 C_min.commande_id
FROM Commande AS C_min
WHERE C_min.commande_licences_reste > 5
ORDER BY C_min.commande_licences_reste ASC,
C_min.commande_date ASC);

SET @COMMANDEID = @@IDENTITY

INSERT INTO Licence (licence_date_debut, commande_id)
VALUES (GETDATE(), @COMMANDEID)

SET @LICENCEID = @@IDENTITY

UPDATE Apprenant
SET licence_id = @LICENCEID
WHERE apprenant_id = @Apprenant

END

END
But it always throws an error saying that I can't insert null into commande_id (basically, I think @@IDENTITY isn't executed at all)
Any idea why it doen't works ?
Thanks !

View 1 Replies View Related

Set Identity Not Working For Stored Procedure

Jan 31, 2008

Hi can someone tell me whats wrong with this stored procedure. All im trying to do is get the publicationID from the publication table in order to use it for an insert statement into another table. The second table PublicaitonFile has the publicaitonID as a foriegn key.
Stored procedure error: cannot insert null into column publicationID, table PublicationFile - its obviously not getting the ID.
ALTER PROCEDURE dbo.StoredProcedureUpdateDocLocField
@publicationID Int=null,@title nvarchar(MAX)=null,@filePath nvarchar(MAX)=null
ASBEGINSET NOCOUNT ON
IF EXISTS (SELECT * FROM Publication WHERE title = @title)SELECT @publicationID = (SELECT publicationID FROM Publication WHERE title = @title)SET @publicationID = @@IDENTITYEND
IF NOT EXISTS(SELECT * FROM PublicationFiles WHERE publicationID = @publicationID)BEGININSERT INTO PublicationFile (publicationID, filePath)VALUES (@publicationID, @filePath)END
 

View 5 Replies View Related

Stored Procedure And Assigning @@IDENTITY Value

Nov 3, 2003

In the following stored procedure I would like to test whether @TopCategoryID is null. If so I would like to insert the @CategoryID value into the @TopCategoryID value. So, once @CategoryID recieves the @@IDENTITY value, how do I enter this same value into @TopCategoryID as well???



CREATE PROCEDURE sp_CT_InsertCategory



@Namevarchar(50),
@Description varchar(250),
@topCategory bit,
@ParentCategoryID int,
@TopCategoryID int,
@CategoryID int OUTPUT
AS
DECLARE @CurrID int


IF @CurrID IS NULL
BEGIN

INSERT
INTO CT_Category
(CategoryName, CategoryDescription, topcategory, parentcategoryid, topcategoryid)
VALUES
(@Name, @Description, @topcategory, @ParentCategoryID, @TopCategoryID)

SET @CategoryID = @@IDENTITY

IF @@ERROR > 0
BEGIN

RAISERROR ('Insert of Category failed', 16, 1)
RETURN 99

END

END
ELSE
BEGIN

SET @CategoryID = -1

END
GO



Thanks in advance

View 9 Replies View Related

Return Identity In Stored Procedure

Mar 23, 1999

I have a stored procedure where I am passing in several strings which are concatenated to form an Insert SQL statement. I then return the identity value from the insert - problem is, the identity value is coming back NULL.

Here is the sp. Any ideas?

CREATE PROCEDURE sp_ExecuteInsert
@pIdentity int OUTPUT,
@pSQL1 varchar(255),
@pSQL2 varchar(255) = NULL,
@pSQL3 varchar(255) = NULL,
@pSQL4 varchar(255) = NULL,
@pSQL5 varchar(255) = NULL,
@pSQL6 varchar(255) = NULL,
@pSQL7 varchar(255) = NULL,
@pSQL8 varchar(255) = NULL
AS

EXECUTE (@pSQL1 + @pSQL2 + @pSQL3 + @pSQL4 + @pSQL5 + @pSQL6 + @pSQL7 + @pSQL8)

SELECT @pIdentity = @@IDENTITY
GO

FYI, the real problem I am trying to solve is simply to return the identity after an insert. I could write a simple insert sp that returns the identity, but my code is written to handle generic situations and build an INSERT statement as needed. So if there are any other ideas of executing an insert statement and getting the identity, please let me know.

Thanks...

View 4 Replies View Related

Stored Procedure Conditionally Using @@Identity

Feb 21, 2006

Im having a play using @@identity

I want to insert some data into a table

Get the AutoID value on the inserted data

then insert some other data into another table
and set its id value to the captured @@identity

My question
I only want to do the second insert only if
@FieldValue2 contains a value ie not empty

below is a basic procedure without conditonal part



Code:

CREATE PROCEDURE conditional_Insert

@FieldID As int,
@FieldValue As Nvarchar(50),
@FieldValue2 As Nvarchar(50)
AS

declare @id int

set nocount on

INSERT INTO Table1(fieldID,fieldvalue)
VALUES( @FieldID, @FieldValue)

set @id = @@identity

INSERT INTO Table2(fieldID2, FieldValue2)
VALUES(@id, @FieldValue2)

select @id
GO

View 2 Replies View Related

IDENTITY Values In A Stored Procedure

Oct 18, 2005

Hi All,
This is my stored procedure

CREATE PROCEDURE testProc AS
BEGIN
CREATE TABLE #tblTest(ID INT NOT NULL IDENTITY, Col1 INT)
INSERT INTO #tblTest(Col1)
SELECT colA FROM tableA ORDER BY colA

END


This is my simple procedure, I wanted to know whether the IDENTITY values created in #tblTest will always be consistent, I mean without losing any number in between. i.e. ID column will have values 1,2,3,4,5.....
or is there any chance of ID column having values like 1,2, 4, 6,7,8....

Please reply...
qa

View 2 Replies View Related

Sql Stored Procedure - With In Cursor Get @@identity Value For Insert And Use That Again

Jun 11, 2008

I have stored procedure which contains follwing part of it. it says syntax when i worte line to get @@identity valuewhen delete that  line command succesful. but i need to get @@identity from the insert statement and assign it to a variable and use it after
any body pls tell me how to get this within a stored prosedure or what is the error of the following code bit.  (#tblSalesOrders is a temporary table which containsset of  records from orginal table )DECLARE @soNo1 INT
 DECLARE @CursorOrders CURSOR
SET @CursorOrders = CURSOR FAST_FORWARD FOR
select fldSoNo from #tblSalesOrders
declare @newSONO1 int OPEN @CursorOrders
FETCH NEXT FROM @CursorOrders INTO @soNo1
WHILE @@FETCH_STATUS = 0
BEGIN
----for each salesorder insert to salesorderline
insert into tblSalesOrders (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) select (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) from #tblSalesOrders where fldSoNo =@soNo1;
 
 set @newSONO1=SCOPE_IDENTITY;
-------in this section again create another cursor for another set of records and insert to a table passing identity value return from the above insert --------------------------
SELECT @intErrorCode = @@ERRORIF (@intErrorCode <> 0) GOTO PROBLEM
FETCH NEXT FROM @CursorOrders INTO @soNo1
END CLOSE @CursorOrders
DEALLOCATE @CursorOrders

View 2 Replies View Related

Identity Column Set Not For Replication And A Stored Procedure

Feb 2, 2015

I have a table, which is being replicated with the identity column set "not for replication" and a stored procedure, which is also replicated (execution) which inserts into this table. When the execution of the stored procedure happens, the replication monitor complains about identity value not being provided.other than removing the stored procedure from replication?

View 0 Replies View Related

Problem With @@identity Return In Stored Procedure Insert.

Nov 24, 2003

I'm having a problem I do an insert into a table but I want to return the value of the identity field of that insert so I can email a confirmation. For some reason this code doesn't work.
Below is the stored procedure I'm calling and below that the code I'm using. What am I doing wrong. The value I have returned is null when it should be a number. Any suggestions. Why does finalMagicNum2 come back null when it should grab the identity field of the inserted record.




CREATE PROCEDURE addMagicRecTest
(
@theSequence int,
@theSubject int,
@theFirstName nvarchar(50)=null
@theLastName nvarchar(75)=null
)



AS

INSERT INTO employees([Sequence],subject,firstname,lastname)
VALUES(@theSequence,@theSubject,@theFirstName,@theLastName)
SELECT @@identity AS finalNum



magicDataConnect = ConfigurationSettings.AppSettings("myDataConnect")
Response.Write(magicDataConnect)
magicCommand = New SqlDataAdapter("addMagicRecTest", magicDataConnect)
magicCommand.ConnectionType = CommandType.StoredProcedure
magicCommand.SelectCommand.CommandType = CommandType.StoredProcedure

' Sequence ID for request
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSequence", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@theSequence").Value = "41833"

' Subject for new Wac Ticket
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSubject", SqlDbType.NVarChar, 8))
magicCommand.SelectCommand.Parameters("@theSubject").Value = "1064"


' First Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theFirstName", SqlDbType.NVarChar, 50))
magicCommand.SelectCommand.Parameters("@theFirstName").Value = orderFirstName

' Last Name Field
magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theLastName", SqlDbType.NVarChar, 75))
magicCommand.SelectCommand.Parameters("@theLastName").Value = orderLastName



DSMagic = new DataSet()
magicCommand.Fill(DSMagic,"employees")

If DSMagic.Tables("_smdba_._telmaste_").Rows.Count > 0 Then
finalMagicNum2 = DSMagic.Tables("_smdba_._telmaste_").Rows(0)("finalMagic").toString
End If





I need finalMagicNum2

View 2 Replies View Related

How Do I Return The Identity From Stored Procedure To Asp.net Code Behind Page?

Jun 2, 2006

how do i return the identity from stored procedure to asp.net code behind page?
CREATE PROCEDURE [dbo].[myInsert] ( @Name varchar(35),   @LastIdentityNumber int output)AS insert into table1 (name) values (@name)
DECLARE @IdentityNumber intSET @IdentityNumber = SCOPE_IDENTITY()SELECT @IdentityNumber as LastIdentityNumber
code behind:
public void _Insert(
string _Name,
{
DbCommand dbCommand = db.GetStoredProcCommand("name_Insert");db.AddInParameter(dbCommand, "name", DbType.String, _userId);
db.AddParameter(dbCommand, "@IdentityNumber", DbType.Int32, ParameterDirection.Output, "", DataRowVersion.Current, null);
db.ExecuteNonQuery(dbCommand);
int a = (int)db.GetParameterValue(dbCommand,"@IdentityNumber");
whats wrong with to the above code?
 

View 2 Replies View Related

@@Identity Being Over-written By Insert Trigger In Stored Procedure.

Oct 6, 2004

Hi All

I have a problem with an existing stored procedure that is used to insert a new entry to a table (using an Insert statement).

I have been using the @@Identity global variable to return the identity column (id column) back to the calling routine. This has worked fine for years until recently an ‘after insert Trigger’ has been added to the table being updated.

Now the @@Identity is returning the identity value of the trigger that was called instead of the original table insert.

Does anyone know how I can code around this issue (without using a select statement within my stored proc, as these have been known to cause locks in the past).

Thank in advance.

Eamon.

View 2 Replies View Related

Problem With Stored Procedure And Retrieving Inserted Identity Value

Jul 11, 2006

Hello!I use a procedure to insert a new row into a table with an identitycolumn. The procedure has an output parameter which gives me theinserted identity value. This worked well for a long time. Now theidentity value is over 700.000 and I get errors whiles retrieving theinserted identitiy value. If I delete rows and reset the identityeverything works well again. So I think it is a data type problem.My Procedure:create procedure InsertProduct@NEWID int outputasbeginset nocount oninsert into PRODUCT(D_CREATED)values(getdate()+'')set nocount offselect @NEWID = @@IDENTITYendMy C# code:SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);comm.CommandType = CommandType.StoredProcedure;comm.Parameters.Add(new SqlParameter("@NEWID",System.Data.SqlDbType.Int)).Direction =System.Data.ParameterDirection.Output;try{SqlDataReader sqlRead = comm.ExecuteReader();object o = comm.Parameters["@NEWID"].Value;//...}catch ( Exception ex ){throw ex;}The object o is alwaya System.DbNull. I also tried to use bigint.Any hints are welcomeCiaoSusanne

View 3 Replies View Related

Replicating Tables With An Identity Column Fails Even With Custom Stored Procedure

Mar 28, 1999

When replicating a table which has an identity column I get the error: "Procedure cp_insert_tblname expects parameter @C1, which was not supplied.". The stored procedure appears to be called without any parameters so my insert stored procedure does not work. I know I'm missing something basic here!! Do I have to add the field names when telling replication to use a custom stored procedure. If not, how do arguments get passed to my SP, as globals somehow?

Any info greatly appreciated!!
Thanks,
Jay

View 1 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.

Sep 19, 2006

I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.

How do I do that? Articles, code samples, etc???

View 1 Replies View Related

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

Sep 26, 2014

I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure

at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT

I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT

View 3 Replies View Related

System Stored Procedure Call From Within My Database Stored Procedure

Mar 28, 2007

I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.

How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?

Thanks in advance

View 9 Replies View Related

Ad Hoc Query Vs Stored Procedure Performance Vs DTS Execution Of Stored Procedure

Jan 23, 2008



Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio

What could explain this?

Obviously,

All three scenarios are executed against the same database and hit the exact same tables and indices.

Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).

The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes

View 1 Replies View Related

Get Current Identity(Procedure)

Jul 20, 2005

Hi everyone,How can I retrieve the current identity used in a procedure from atrigger.What I am trying to do is:The procedure takes a @username (nvarchar) as a parameter, and theprocedure identifies the @username's identity and tries to delete theidentity's row. An Instead Of trigger fires, and updates the specificrow.But how can the trigger identify the targeted row?Which built-in function can I use?Thanksgardmt

View 3 Replies View Related

Procedure Returns Wrong @@Identity

Jan 15, 2004

Hi,

I have a procedure that inserts a record to a table and return the @@Identity as an output parameter.

This table has a trigger that is triggered when a record is inserted and add a new record to other table.

The problem is that the @@Identity returned is totally wrong.

Any ideas?

Thanx in advance.

View 3 Replies View Related

User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net

Sep 13, 2007

Hi all,



I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.



Whenever I tried to right click stored procedure and select step into store procedure> i get following error



"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"



I am not sure what needs to be done on sql server side



We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.

Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?



Please advise..

Thank You

View 3 Replies View Related

Where Is The Identity Stored?

Jan 24, 2000

Where is the identity stored for a column? Need to know where the value is stored that SQL looks at to increment an Identity Column.

Thanks for your time.

View 1 Replies View Related

@@IDENTITY (without Stored Procedures)

Mar 6, 2008

Hi guys, I found this page which explains how to get hold of an @@IDENTITY/the last identity column added from your database without using a stored procedure (I haven't been using these).
http://blog.developers.ie/cgreen/archive/2007/08/20/sqldatasource-getting-identity-after-insert.aspx
 Using that as a basis, I have been using the following but nothing is showing in the label that should return the latest identity. Any ideas what I'm doing wrong? I can't figure out why it's not returning anything (the data is going into Orders table fine).
InsertCommand="INSERT INTO [Orders] (...) VALUES (...); SELECT @OrderID = @@Identity"
(Where the OrderID is the PK IDENTITY (auto inc) column of "Orders" table)
my insert command:
<InsertCommands>
            <asp:Parameter Direction="Output" Name="OrderID" Size="4" Type="Int16" />
</InsertCommands>
 
Then I click a button to update the orders table, and ideally I'd see the updated OrderID column appear in the label4 below, but I don't :(
 
C# Code:
    protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)    {       object newId = e.Command.Parameters["@OrderID"].Value;       Label4.Text = newId.ToString();
    }

View 2 Replies View Related

Where Is The Identity Property Stored?

Oct 20, 2006

I assume that the identity property is stored within a given database, so there should be no need to run dbcc checkident after restoring from a backup. Is this a correct assumption?

View 1 Replies View Related

Is The Transaction Context Available Within A 'called' Stored Procedure For A Transaction That Was Started In Parent Stored Procedure?

Mar 31, 2008

I have  a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
 Any help on this would be appreciated.

View 1 Replies View Related

Returning @@IDENTITY FROM Stored Proc

Nov 15, 2005

I am trying to get the identity of an inserted record using this SP:

<code>
ALTER PROCEDURE acereal_Admin.AuctionInsertCommand
(
    @AuctionID Int OUTPUT,
    @StartDate char(255),
    @StartTime char(255),
    @Location char(255),
    @Title char(255),
    @Description char(255),
    @Images char(255)
)
AS
INSERT INTO Auctions
(
    StartDate,
    StartTime,
    Location,
    Title,
    Description,
    Images
)
VALUES
(
    @StartDate,
    @StartTime,
    @Location,
    @Title,
    @Description,
    @Images
)

SELECT     AuctionID AS ID
FROM         Auctions
WHERE     (AuctionID = @@IDENTITY)
</code>

Then, I am using this class for a file called dataaccess.cs to return the ID:

<code>
public string SaveImageName(string ImageName, string AuctionID)
        {
            SqlConnection
sqlConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

           
this.newSqlCommand = new
System.Data.SqlClient.SqlCommand("[AuctionImageSave]", sqlConnection);
           
this.newSqlCommand.CommandType =
System.Data.CommandType.StoredProcedure;

            SqlParameter
paramImagePath = new System.Data.SqlClient.SqlParameter("@ImagePath",
System.Data.SqlDbType.Char, 255);
            paramImagePath.Value = ImageName;
            this.newSqlCommand.Parameters.Add(paramImagePath);

            SqlParameter
paramAuctionID = new System.Data.SqlClient.SqlParameter("@AuctionID",
System.Data.SqlDbType.Int, 4);
            paramAuctionID.Value = AuctionID;
            this.newSqlCommand.Parameters.Add(paramAuctionID);

            SqlParameter
paramImageID = new System.Data.SqlClient.SqlParameter("@ImageID",
System.Data.SqlDbType.Int, 4);
            paramImageID.Direction = ParameterDirection.Output;
            this.newSqlCommand.Parameters.Add(paramImageID);

            //Return SqlDataReader Struct
            this.newSqlCommand.Connection.Open();
            this.newSqlCommand.ExecuteNonQuery();
            this.newSqlCommand.Connection.Close();

            int returnID = (int)paramImageID.Value;

            return returnID.ToString();
        }
</code>

The above code returns null.

Can anyone tell me what I am doing wrong?

THanks, Justin.

View 2 Replies View Related

Calling Stored Procedure Fromanother Stored Procedure

Oct 10, 2006

Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames

View 16 Replies View Related

Use Resultset Returned From A Stored Procedure In Another Stored Procedure

Nov 15, 2006

I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system.
I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible?
 Thanks,
Kevin

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved