@@identitiy always returns a value of 1 from sp

The data is entered correctly but identity of new record is always sent back
as 1 .
Why is this always returning a value of 1 ?
sp:
Create PROCEDURE AddPlayer
@.SQLCMD nvarchar(1000)
AS
BEGIN
EXECUTE sp_executesql @.SQLCMD
END
SELECT SCOPE_IDENTITY()Without answering the original question, let me tell
you that your approach is totally wrong.
Why you have to pass all the sql from the client side
and use the SP just to call sp_executesql ?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"TJS" <nospam@.here.com> wrote in message
news:e3R8CnXHFHA.1172@.TK2MSFTNGP12.phx.gbl...
> The data is entered correctly but identity of new record is always sent
> back as 1 .
> Why is this always returning a value of 1 ?
> sp:
> Create PROCEDURE AddPlayer
> @.SQLCMD nvarchar(1000)
>
> AS
> BEGIN
> EXECUTE sp_executesql @.SQLCMD
> END
> SELECT SCOPE_IDENTITY()
>
>|||A much more important question is why are you using dynamic SQL to
insert a row? Don't do that if you can possibly avoid it. Instead, add
parameters for each column and insert them with a static INSERT
statement.
SCOPE_IDENTITY() won't see the IDENTITY value in this example because
sp_executesql has its own scope. Use @.@.IDENTITY instead, although if
you have a trigger on the table then @.@.IDENTITY will give you the last
value inserted by a trigger.
David Portas
SQL Server MVP
--|||The SQL is generated dynamically, so I just pass it through once developed.
Seemed like a good solution.
I tried using "SELECT @.@.IDENTITY" in the procedure but it still returns a
value of 1.
Is there a way to requery the table and get the maximum vaslue of the
identity , within the same procedure and have it return that value ?|||> The SQL is generated dynamically, so I just pass it through once
developed.
> Seemed like a good solution.
Then I suggest that you should do some serious studying of development
best practices in SQL. Dynamic SQL is something to be avoided unless
you have exceptional cause to need it. There are good reasons for this.
The following article discusses some of them:
http://www.sommarskog.se/dynamic_sql.html

> Is there a way to requery the table and get the maximum vaslue of the

> identity , within the same procedure and have it return that value ?
You should always be able to retrieve the IDENTITY value after an
INSERT by using an alternate key of the table because IDENTITY should
never be the only key. Having said that, the @.@.IDENTITY method should
work in the scenario you have given so if you need more help please
post some code that will reproduce the problem for us: A CREATE TABLE
statement and some code to call the SP.
David Portas
SQL Server MVP
--|||here is the revised code
sp:
Create PROCEDURE AddPlayer
@.SQLCMD nvarchar(1000)
AS
BEGIN
EXECUTE sp_executesql @.SQLCMD
END
SELECT @.@.IDENTITY|||It works for me:
CREATE TABLE T1 (x INTEGER IDENTITY PRIMARY KEY, z INTEGER NOT NULL
UNIQUE)
EXEC AddPlayer 'INSERT INTO T1 (z) VALUES (1)'
EXEC AddPlayer 'INSERT INTO T1 (z) VALUES (2)'
Result:
(1 row(s) affected)
---
1
(1 row(s) affected)
(1 row(s) affected)
---
2
(1 row(s) affected)
David Portas
SQL Server MVP
--|||Did you check to see if there is a trigger on this table?
David Portas
SQL Server MVP
--|||no triggers
I don't see where in your sp the identity being returned ?
what did you use to return the last identity
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109627060.294495.297220@.o13g2000cwo.googlegroups.com...
> Did you check to see if there is a trigger on this table?
> --
> David Portas
> SQL Server MVP
> --
>|||I was calling your SP:
EXEC AddPlayer 'INSERT INTO T1 (z) VALUES (1)'
If you get a different result then please post some complete code that
we can actually run, including the CREATE TABLE statement and INSERT
statement(s).
David Portas
SQL Server MVP
--

@@ERROR Handling

To All Gurus:
I am trying to log some information to another table when @.@.Error returns an
error. Below is the sample code to see how @.@.error works. In this sample
code, I am purposely failing INSERT INTO TEST command and want to log
informaiton in TestLog. Even though there is a error but the insert to
TestLog never works. What is the catch here ?
/* Create Sample Tables */
Create Table Test (ID Smallint)
Create Table TestLog (ID Smallint)
/*Fail the Insert statement */
INSERT INTO TEST Values (10, 20)
/*This insert should work because error exists. */
IF @.@.Error <> 0
BEGIN
INSERT INTO TESTLOG Values (10, 20)
END
/* No Records are found*/
Select * from testlog
If I run this code in pieces one by one, the code works. When I highlight
the entire code and run it, it never makes it to TestLog table.
Thanks in advance.Sorry, the second Insert statment was
INSERT INTO TestLog Values (10)
"Mark" wrote:

> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||Mark,
See Erland's articles:
http://www.sommarskog.se/error-handling-I.html
and
http://www.sommarskog.se/error-handling-II.html
HTH
Jerry
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:87927DFA-D785-42B2-BF22-972996A8F852@.microsoft.com...
> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
> an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||I read the article but it still didint make it clear as to why Insert into
TestLog never happened when the entire code was highlighted and executed.
Any comments on that one ?
"Mark" wrote:

> To All Gurus:
> I am trying to log some information to another table when @.@.Error returns
an
> error. Below is the sample code to see how @.@.error works. In this sample
> code, I am purposely failing INSERT INTO TEST command and want to log
> informaiton in TestLog. Even though there is a error but the insert to
> TestLog never works. What is the catch here ?
> /* Create Sample Tables */
> Create Table Test (ID Smallint)
> Create Table TestLog (ID Smallint)
> /*Fail the Insert statement */
> INSERT INTO TEST Values (10, 20)
> /*This insert should work because error exists. */
> IF @.@.Error <> 0
> BEGIN
> INSERT INTO TESTLOG Values (10, 20)
> END
> /* No Records are found*/
> Select * from testlog
> If I run this code in pieces one by one, the code works. When I highlight
> the entire code and run it, it never makes it to TestLog table.
> Thanks in advance.
>
>|||The insert failure aborts the batch, not the statement. If you put GO
between the statements, it should work. However you cannot use a
multi-batch technique inside of an object (e.g. stored procedure).
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>I read the article but it still didint make it clear as to why Insert into
> TestLog never happened when the entire code was highlighted and executed.
> Any comments on that one ?
> "Mark" wrote:
>|||Mark,
I think it has to do with the type of error you're creating. If you add a
CHECK constraint to Test and perform your insert and it violates the insert
then the value IS inserted into the TestLog table.
HTH
Jerry
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>I read the article but it still didint make it clear as to why Insert into
> TestLog never happened when the entire code was highlighted and executed.
> Any comments on that one ?
> "Mark" wrote:
>|||Thanks to both Jerry and Aaron. You are right, it has to do with what kind o
f
insert error it is. It either rollsback the batch or logs the info based on
the type of error. I created two scenarions and in one case it works and in
other one, it doesnt.
Once again thanks to both
"Jerry Spivey" wrote:

> Mark,
> I think it has to do with the type of error you're creating. If you add a
> CHECK constraint to Test and perform your insert and it violates the inser
t
> then the value IS inserted into the TestLog table.
> HTH
> Jerry
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:52B1E1E2-2422-4728-8447-FA4CB25F576E@.microsoft.com...
>
>|||Mark (Mark@.discussions.microsoft.com) writes:
> Thanks to both Jerry and Aaron. You are right, it has to do with what
> kind of insert error it is. It either rollsback the batch or logs the
> info based on the type of error. I created two scenarions and in one
> case it works and in other one, it doesnt.
As noted in my article, an error can lead to termination on three
different levels:
1) Statement
2) Scope
3) Bacth.
Your error was of the second kind. Scope-aborting errors are, as far as
I know, always compilation errors. What is tricky, is that due to
deferred named resolution, compilation errors can happen at run-time.
Consider:
Create Table Test (ID Smallint)
Create Table TestLog (ID Smallint)
--go
print 'Hello!'
/*Fail the Insert statement */
INSERT INTO Test Values (10, 20)
/*This insert should work because error exists. */
IF @.@.Error <> 0
BEGIN
INSERT INTO TestLog Values (10)
END
go
SELECT * FROM TestLog
This script will print Hello!, but if you uncomment the go, it will not.
This is because in the script as it stands, Test does not exist, so SQL
Server defers compilation of that statement. If you uncomment the go,
the tables exists when the batch is compiled, and thus you get the error
directly, so execution never starts.
Finally, note that if you write:
INSERT INTO Test(ID) Values(10, 20)
the batch always fails to compile, as SQL Server does not need to know
the table definition. Incidently, most people agree that INSERT without
a column list is not good practice.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

@@connections increases by 2

Running SQL Server locally for testing purpose
Running select @.@.connections returns 21
Opened a new window in QA
Running select @.@.connections returns 23
I was assuming it to return 22.
Why does it increase it by 2. I have done this many times by opening just
window and nobody else is connected..so why does it increase it by 2 instead
of 1 ?The @.@.connections global variable holds returns the number of connections,
or attempted connections, since SQL ServerT was last started, not the number
of current connections. If you use Profiler, you can trace Query Analyzer -
you can find out that QA makes login, checks some things, makes logout and
then makes login again. This way QA makes 2 connecions for a session.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OzWDlyzbDHA.2024@.TK2MSFTNGP12.phx.gbl...
> Running SQL Server locally for testing purpose
> Running select @.@.connections returns 21
> Opened a new window in QA
> Running select @.@.connections returns 23
> I was assuming it to return 22.
> Why does it increase it by 2. I have done this many times by opening just
> window and nobody else is connected..so why does it increase it by 2
instead
> of 1 ?
>