Caller Identity

Jul 23, 2005

I am using SQL Server 2000 and SQL authentication in a web
appliacation. All data access is done via single SQL Server account.
In my front end I am using forms authentication. Is there a way to
retrieve the forms identity (or just a username) from SQL Server?

Thanks,
Lac

View 2 Replies


ADVERTISEMENT

How To Determine Sp Caller Current Database?

Aug 23, 2007

When executing a stored procedure that is defined in another database, as:

USE db1;

EXEC db2.dbo.sproc;

Is there a way in the stored procedure "sproc" to determine that the caller made the call from db1?

View 1 Replies View Related

Getting The Caller's Machine Name In A Stored Procedure

Jan 15, 2008

Is there any way of obtaining any information about the caller of a stored procedure from within the procedure, specifically machine name or IP address?

I'm looking for a built-in SQL function, rather than passing this information in as a parameter!

Thanks

View 4 Replies View Related

Error Handling And Propagation Of Errors To Caller

May 14, 2008

With the new features of SQL Server 2005 for error handling (TRY...CATCH blocks), how are you propagating errors back to the caller? For example, lets say we have 3 stored procedures:
dbo.usp_UpdateSomeTable1
dbo.usp_UpdateSomeTable2
dbo.usp_UpdateSomeTable3

Let say some application calls dbo.usp_UpdateSomeTable1, in turn dbo.usp_UpdateSomeTable1 calls dbo.usp_UpdateSomeTable2 and in turn dbo.usp_UpdateSomeTable2 calls dbo.usp_UpdateSomeTable3.

Now if dbo.usp_UpdateSomeTable3 generates an error, how do you handle propagating this back to the caller?

I envision encapsulating the contents of each procedure in a TRY...CATCH block like so:

BEGIN TRY
...do some stuff
END TRY
BEGIN CATCH
...handle errors - whether generated from our own RAISERROR statements or by the database engine.
END CATCH

Now my problem is I would like to capture all the error variables and toss them back to the caller and keep sending that information up the stack. So far my attempts have been pretty unreadable and end up being just a cluster of text.

View 1 Replies View Related

Is Trigger Launched By Program Caller Or SQL Server?

Nov 8, 2007

Hi,
I have a question about sql server trigger. For example, I have a trigger for table insert. The way it works is:
A: my program run the insert query, and wait, then the record is inserted, then the trigger is launched, after the trigger job done, my program then return,
or
B: my porgram run the insert query, and wait, then the record is inserted, then my porgram returns, then the trigger is launched. Basically, my program does not have to wait the trigger job done.
Which way is right? or neither one is right.
The problem we are facing right now is, we need to run a pretty big stored procedure and it takes a long time. I am thinking about move some of them to a trigger job, so our porgram don't have to wait for everything done.
Any inputs are welcome. Thanks.

View 2 Replies View Related

How Can I Identify The Caller's Database From A Stored Procedure In Another Database?

Apr 2, 2008

I am wondering if there is a way to create a procedure in a logging database that can identify the source database from which it is invoked. DB_NAME() of course returns the name of the database in which the stored procedure exists. I could pass the database name as a parameter to the proc, just wondering if there is another way.

This is SQL 2005, I did look into the sys.dm_exec views but nothing seems to have a dbid reflecting the calling context.

Thanks,
Mike

View 4 Replies View Related

Last GASP On Insert Row In Table With Identity Field, And Get New Identity Back ?

Jul 9, 2006

While I have learned a lot from this thread I am still basically confused about the issues involved.

.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.

To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.

1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.

2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!

3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?



TIA,



Barkingdog

View 10 Replies View Related

Insert Row In Table With Identity Field, And Get New Identity Back

Jun 30, 2006

I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).

What is the most direct way to do this in SSIS?



TIA,



barkingdog



P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?

View 12 Replies View Related

T-SQL (SS2K8) :: How To Update Identity Column With Identity Value

Jan 25, 2015

I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.

Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?

View 6 Replies View Related

Identity...I Need To Get The Last (or Highest Number In Identity Column)...

Sep 19, 2005

Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint

View 2 Replies View Related

Problem In Using Sqlbulkcopy To Insert Data From Datatable(no Identity Column) Into Sql Server Table Having Identity Column

Jun 19, 2008

Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun

View 6 Replies View Related

How To Use Identity On Non-identity Column (with Concurrence)

Aug 1, 2014

I'm working with a third-party database (SQL Server 2005) and the problem here is the following:

- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).

- The PK of T is an Integer.

Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.

Considering that I can't change the PK, what is the best approach to solve this problem?

To sum up:

* I need to have processes in parallel inserting on T

* I can't change anything on T

* The PK is NOT an Identity

View 4 Replies View Related

Transact SQL :: Alter Non Identity Column To Identity Column

Aug 12, 2009

when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.

View 2 Replies View Related

@@identity

Oct 26, 2006

i have found loads of topics on this but have yet to find one that gives the answer i need.I want to display the last insert id into a label/textbox after the INSERT functionhow do i do this? 

View 18 Replies View Related

@@identity

Feb 29, 2008

I got two tables 
one table has the fields ie table1
orderid   ofd    orderdate   customername
where order id is autonumber
the other table2
orderid   ofd   product id    productname
the problem here is thatif customer purchases 3 product at a time all the 3 products get the same ofd number ........and any 2 customers can have the same ofd number................ now i have to pull the order ID value from table 1 to table 2............ can somebody help with this  i am the front end is asp.net amd the database is done on SQL server management studio

View 1 Replies View Related

@@IDENTITY

Mar 3, 2008

Hi, I was looking through this thread about @@Identity: http://forums.asp.net/p/1039145/1443971.aspx#1443971 I'm still unsure how to ue it. I have an Orders table, a Products table and a Products_Orders table. When I add an order to the Orders table I want the PK OrderID in this table to also  update the FK OrderID in my Products_Orders table. I'm using SQL EXP05 and I'm in C#.

View 1 Replies View Related

Get Next Identity

Mar 11, 2008

Hi  i want to get the naxt identity number how can i get it pls help me very urgent

View 3 Replies View Related

Identity Key

Feb 24, 2004

I know identity key in a table can cause problems when the table is replicated. Should we avoid using identity key altogether, if we don't know in advance whether replication will come into the picture?

Thanks for any advice.

View 2 Replies View Related

Help With @@IDENTITY

Aug 30, 2004

hello,

I've got a problem reading the @@identity in vb.net
I tried it the way below and get the error: Public member 'EOF' on type 'Integer' not found.
(--> means with rsLastIdent)



comm_user = "SET NOCOUNT ON; INSERT INTO user (firstname, lastname, company, emailAddress) VALUES ...); SELECT @@IDENTITY AS Ident;"

comm = new SqlCommand(comm_user, dbConnection)

dbConnection.Open()



Try

rsLastIdent = comm.ExecuteNonQuery()

Catch ex As Exception

Response.Write("Exception:")

Response.Write(ex.ToString)

End Try



if NOT rsLastIdent.EOF then

feed_userID = rsLastIdent.Fields.Item("Ident").Value

end if




The sql-statement is correct - I tried it on SQL Server and got the correct result, so something is wrong with my vb.net code...

Please can anybody help me and tell me how to declare my rsLastIdent or another way to code it in vb.net to get the @@identity?

Thanks a lot!

tim

View 2 Replies View Related

@@identity

Sep 20, 2004

hi,

I was wondering if someone could help me out with this stored procedure I have. I am trying to execute a transaction in one of my sps and am getting pk violations on 'OrderID'.
This where i encounter this error:

SELECT
@OrderID = @@Identity
/* Copy items from given shopping cart to OrdersDetail table for given OrderID*/
INSERT INTO OrderDetails
(
OrderID,
ProductID,
Quantity,
UnitCost
)
SELECT
@OrderID,
ShoppingCart.ProductID,
ShoppingCart.Quantity,
Prices.UnitCost
FROM
ShoppingCart INNER JOIN
Prices ON ShoppingCart.ProductID = Prices.ProductID
WHERE
CartID = @CartID


is there any way to rewrite this statement so that I can put it in the form insert()values(). ?

View 5 Replies View Related

Getting Identity

Jun 17, 2005

HiTrying to get a return value from this code, but only gets a 0. Am using SQLExpress.SqlParameter[] p = new SqlParameter[4];p[0] = new SqlParameter("@a", "aaa");p[1] = new SqlParameter("@b", "bbb");p[2] = new SqlParameter("@c", "ccc");p[3] = new SqlParameter("@d", SqlDbType.Int, 40);p[3].Direction = ParameterDirection.ReturnValue;
string s = @"set nocount on INSERT INTO ABC(A, B, C) VALUES(@a,@b,@c) SELECT scope_identity()";
using(SqlConnection conn = new SqlConnection(this._connection)){   conn.Open();   SqlHelper.ExecuteNonQuery(conn, CommandType.Text, s, p);   int foo = p[3].Value;}

View 4 Replies View Related

Next Available Identity Value

Jul 20, 2005

Hi all,

is there a way in SQL Server for me to get the next available Identity value from an Identity column?

(I don't want to perform an insert, and then get the identity through @@IDENTITY!)

Thanks

Tryst

View 4 Replies View Related

@@Identity C# Help...

Jun 1, 2006

I am trying to follow other examples I have seen on the site, and am still getting the
Must declare the scalar variable "@@INDENTITY".
string sqlAdd = string.Format("INSERT INTO " + siteCode + "_campaign_table (campaign_name, prod_id, type) "
+ "VALUES('{0}', '{1}', '{2}'); SELECT @@INDENTITY", campaignName, prodID, type);
SqlCommand comAdd = new SqlCommand(sqlAdd, con);
comAdd.CommandType = CommandType.Text;
con.Open();
//comAdd.ExecuteNonQuery();
int identity;
identity = Decimal.ToInt32((decimal)comAdd.ExecuteScalar());
lblErrorMessageAdd.Text = identity.ToString();
con.Close();

View 3 Replies View Related

Identity

Nov 5, 2001

Is it possible to use the ALTER TABLE statment when modifying an INT field to be an IDENTITY.


thanks in advance.

View 4 Replies View Related

Using Identity

Jul 11, 2000

I would like to know the best way to select/maintain a sequence number in SQL Server. I've seen locking problems with using the @@identity and was wondering if there is a better way.

Several of our applications have the need to generate a sequence number that is inserted into one or several tables. In one application they have done the following ...
- Created a table with a column defined with identity attribute, for example
TableA
ColA defined as Int with Identity checked
ColB define as char(20)

- In the application, code looks like to get the sequence number:
insert into tableA (ColB, 'anything');
select (@@identity) as sequence from TableA

Then the last 5 positions of sequence are used to insert into another table. Problem with this is that several rows are being created in tableA when only a sequence number is needed. Also, we need to make sure no one else does an insert before the select @@identity.


Another approach I'm thinking about would be to create a one row table that contains an integer field initialized with a value of 1. To select/update the sequence number the code would need to:
set transaction serializable
select number
from tableA UPDLOCK
update tableA
set number = number + 1

How are most people generating a sequence number in SQL Server? In Oracle this would be done by selecting sequence.nextval. For example:
Select sequenceA.nextval from dual;

Is there an equivalent way in SQL Server 7.0? Thanks.

View 2 Replies View Related

Bcp And Identity

Oct 17, 2000

Hi,

Is there a way, I can give access(not dbo or sa) to a person so they can bcp into tables that have identity columns? I want to be able to give permissions ahead so I do not have to bother setting the identity insert on every time he wants to bcp.

Thanks a lot for your help
Sasha

View 3 Replies View Related

@@Identity

Nov 30, 1999

I'm successfully inserting into a db using a stored proc, but I need to replicate the ClientID to 10 other tables. For some reason, this one is escaping me.

After a successful insert, I then use:

SELECT @@IDENTITY ClientID
SELECT @ClientID = @@IDENTITY

INSERT INTO table2
(ClientID)
VALUES(@ClientID)

INSERT INTO table3
(ClientID)
VALUES(@ClientID)

INSERT INTO table4
(ClientID)
VALUES(@ClientID)

The insert ClientID into the other tables is being inserted. I added SET @ClientID = @@IDENTITY and it made no difference.

Thanks for the help.

Alan

View 1 Replies View Related

@@IDENTITY Bug

Sep 2, 1999

Does anyone know if there is bug/problem with the @@identity global variable in SQL Server 7.0?
I have a stored procedure that inserts a row into a table with an identity column and returns (outputs) the value
of the identity column just generated. The SP is called by a Java program. The SP works fine most of the time,
however from time to time it retuns a NULL value! Your comments/suggestions are much appreciated.

View 1 Replies View Related

@@Identity Fix

Apr 18, 2000

Hi everyone,

Can someone explain how I can use another table to hold my @@identity value so I can have the correct value to update other tables?

Thank you very much,
Nathan

View 6 Replies View Related

@@IDENTITY

Mar 9, 2001

I have a table (tblMoney). When I insert a new row into the table, via a standard insert stored procedure, the new row, has the identity column numbered correctly (say 34 for example). At the end of the stored procedure, we return @@IDENTITY for the developers. This will return a much bigger number (1198).

Any idea how these got out of sync, or how they can be fixed??

Thanks!

Trisha

View 3 Replies View Related

Identity Off Via T/SQL?

Jun 24, 2004

Is there a way to turn off the identity from a table via t/sql.

I am not talking about "SET IDENTITY_INSERT tableABC ON" or Off, I want permanantly to take off this identity without through E.M. to manually do it. And I might have up to 8000 tables need to do so.

thanks
-D

View 2 Replies View Related

Identity

Apr 29, 1999

Hi Everybody,
I am working on a project involving SQL Server 6.5 and Site Server.
We need to create a unique sequence number. One option is to use the
identity field of SQL Server. I am just wondering if there is some
object which can be used to get a unique sequence number.
How easy or tough is it to develop such an object?? Do U guyz recommend
using this or working with the identity option itself.
Thanks for any advice

Madhu

View 1 Replies View Related

IDENTITY

Dec 11, 1998

I use field ID as identity and IDENT_SEED change itself suddenly to the starting value.

How can I set it to right value?

Beargie

View 1 Replies View Related







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