MSDE (SQL Server 2000) does not return @@Identity

Hi All,
I'm creating an appplication with some stored procedures. The stored
procedure needs to return the @.@.Identity of the table's new record. The
function has no errors and compiles without any warnings. The function
does work fine some times (returning value). But most of the calls are
failing and the function does not return proper value.
CREATE PROCEDURE CreateNewCourse
@.CourseName NVARCHAR(256),
@.Duration Int,
@.Contents NVarChar(500),
AS
DECLARE @.Identity int
INSERT into CourseMaster
(
CourseName,Contents,
Duration
)
VALUES
(
@.CourseName,
@.Contents,
@.Duration
)
SET @.Identity = SCOPE_IDENTITY()
return @.Identity
Any suggestions?
TIA.Hi
Can you show us how you call the SP ?
To reproduce your problem please post DDL+ sample data?
"Anbu" <t_anbazhagan2001@.yahoo.co.in> wrote in message
news:1143606553.333820.230400@.t31g2000cwb.googlegroups.com...
> Hi All,
> I'm creating an appplication with some stored procedures. The stored
> procedure needs to return the @.@.Identity of the table's new record. The
> function has no errors and compiles without any warnings. The function
> does work fine some times (returning value). But most of the calls are
> failing and the function does not return proper value.
> CREATE PROCEDURE CreateNewCourse
> @.CourseName NVARCHAR(256),
> @.Duration Int,
> @.Contents NVarChar(500),
> AS
> DECLARE @.Identity int
> INSERT into CourseMaster
> (
> CourseName,Contents,
> Duration
> )
> VALUES
> (
> @.CourseName,
> @.Contents,
> @.Duration
> )
>
> SET @.Identity = SCOPE_IDENTITY()
> return @.Identity
> Any suggestions?
> TIA.
>|||I'm trying to execute the function from Visual Studio .NET IDE (through
Database Explorer). Normally it will return the output in the "output"
window of VS .NET IDE.|||"Anbu" <t_anbazhagan2001@.yahoo.co.in> wrote in message
news:1143613779.111998.71180@.g10g2000cwb.googlegroups.com...
> I'm trying to execute the function from Visual Studio .NET IDE (through
> Database Explorer). Normally it will return the output in the "output"
> window of VS .NET IDE.
>
Don't use RETURN to ouptut results. Use OUTPUT parameters and keep RETURN
for what it is intended, i.e. error status. Make sure you set NOCOUNT ON.
Try:
CREATE PROCEDURE CreateNewCourse
@.CourseName NVARCHAR(256),
@.Duration Int,
@.Contents NVarChar(500),
@.Identity INT OUTPUT
AS
SET NOCOUNT ON
DECLARE @.result INT
INSERT into CourseMaster
(
CourseName,Contents,
Duration
)
VALUES
(
@.CourseName,
@.Contents,
@.Duration
)
SET @.result =@.@.ERROR
IF @.result > 0
BEGIN
/* Error handling? */
EXEC usp_error_handler @.result, ... ;
END
SET @.Identity = SCOPE_IDENTITY()
RETURN @.result
GO
DECLARE @.i INT
EXEC CreateNewCourse
@.CourseName = '',
@.Duration = 0,
@.Contents = '',
@.Identity = @.i OUTPUT
SELECT @.i
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||It seem's it's a problem with the IDE and MSDE connection, or the way
the IDE returns the data from MSDE. I could run the same Stored Proc
from an application and retrieve the results in the return value.
Thanks,|||Does the table have an identity column?
"Anbu" <t_anbazhagan2001@.yahoo.co.in> wrote in message
news:1143606553.333820.230400@.t31g2000cwb.googlegroups.com...
> Hi All,
> I'm creating an appplication with some stored procedures. The stored
> procedure needs to return the @.@.Identity of the table's new record. The
> function has no errors and compiles without any warnings. The function
> does work fine some times (returning value). But most of the calls are
> failing and the function does not return proper value.
> CREATE PROCEDURE CreateNewCourse
> @.CourseName NVARCHAR(256),
> @.Duration Int,
> @.Contents NVarChar(500),
> AS
> DECLARE @.Identity int
> INSERT into CourseMaster
> (
> CourseName,Contents,
> Duration
> )
> VALUES
> (
> @.CourseName,
> @.Contents,
> @.Duration
> )
>
> SET @.Identity = SCOPE_IDENTITY()
> return @.Identity
> Any suggestions?
> TIA.
>

MSDE (SQL Server 2000) does not return @@Identity

Hi All,
First my apologize for cross posting. It's already posted in
sqlserver.programming. But I see this group would also help me.
I'm creating an appplication with some stored procedures. The stored
procedure needs to return the @.@.Identity of the table's new record. The
function has no errors and compiles without any warnings. The function
does work fine some times (returning value). But most of the calls are
failing and the function does not return proper value.
CREATE PROCEDURE CreateNewCourse
@.CourseName NVARCHAR(256),
@.Duration Int,
@.Contents NVarChar(500),
AS
DECLARE @.Identity int
INSERT into CourseMaster
(
CourseName,Contents,
Duration
)
VALUES
(
@.CourseName,
@.Contents,
@.Duration
)
SET @.Identity = SCOPE_IDENTITY()
return @.Identity
Any suggestions?
TIA.
Return identity as an output parameter.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Anbu" <t_anbazhagan2001@.yahoo.co.in> wrote in message
news:1143608634.206122.169410@.e56g2000cwe.googlegr oups.com...
> Hi All,
> First my apologize for cross posting. It's already posted in
> sqlserver.programming. But I see this group would also help me.
> I'm creating an appplication with some stored procedures. The stored
> procedure needs to return the @.@.Identity of the table's new record. The
> function has no errors and compiles without any warnings. The function
> does work fine some times (returning value). But most of the calls are
> failing and the function does not return proper value.
>
> CREATE PROCEDURE CreateNewCourse
> @.CourseName NVARCHAR(256),
> @.Duration Int,
> @.Contents NVarChar(500),
>
> AS
>
> DECLARE @.Identity int
>
> INSERT into CourseMaster
> (
> CourseName,Contents,
> Duration
>
> )
> VALUES
> (
> @.CourseName,
> @.Contents,
> @.Duration
> )
>
> SET @.Identity = SCOPE_IDENTITY()
>
> return @.Identity
>
> Any suggestions?
>
> TIA.
>

msdb.dbo.sp_help_job current_execution_step always returns 0 (unknown)

In SQL Server 2005, unless you belong to the sysadmin server role, executing
msdb.dbo.sp_help_job will always return "0 (unknown)" in the
current_execution_step column of first dataset returned.
Granting the user membership to the SQLAgentOperatorRole,
SQLAgentReaderRole, and SQLAgentUserRole does not make a difference.
Is this fixed in Service Pack 1?
Hello,
Based on my test, this issue is by design and still in SQL Server 2005 SP1.
According to the Books online, only the sysadmin could view the current
step informaton of a job.
sp_help_job (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms186722.aspx
SQL Server Agent Fixed Database Roles
http://msdn2.microsoft.com/en-us/library/ms188283.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti.../default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||I appreciate the response. I've read through those BOL entries before, so
maybe I'm missing something, or I haven't had enough coffee yet, but I can't
seem to find a note indicating that only users with the sysadmin role could
view the current job step.
In the event that this is indeed by design, are there any workarounds that I
can use to retrieve the step information without the user being added to the
sysadmin role?
|||Hello,
I did a deep research on how msdb.dbo.sp_help_job works.
Finnally, I found that msdb.dbo.sp_help_job invoke the system extended
stored procedure master.dbo.xp_sqlagent_enum_jobs and pass the parameter
@.is_sysadmin.
So my conclusion is unless you add the user to the sysadmin role, you could
not retrieve the step information for the job.
You may try to dig into the system stored procedure sp_help_job to find
whether there are any helpful information for you.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.