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 & SQL2000

I am developing a Crystal Reports App in VS2005 (VB). I was originally working on an XP machine with MSDE and stored procedures where it worked fine.

I then transferred the development to a Win2003 machine with SQL2000 and am now getting the following error;

Failed to open a rowset. Description: 'Get_Calls_By_MLO_Date' expects parameter '@.DT1' which was not supplied. Plus some other error detail.

Here is the SP.

CREATE PROCEDURE Get_Calls_By_MLO_By_Date
@.DT1 datetime,
@.DT2 datetime
AS
SELECT MLO, CallNo, DT, Type FROM Actions
WHERE dt >= @.DT1 AND dt <= @.DT2 AND ActionID=1ORDER BY MLO, DT
GO

If I transfer everything back to the XP machine it works fine.

Any ideas?

Thanks

Terry.

How did you call the stored procedure that causes the error?|||

Hi,

It's called from the Crystal Report object. The function below accepts 2 parameters; 1. The report viewer and 2. (optional) a hashtable with the key/value pairs being the parameter info e.g. Key=@.DT1, Value = 13/01/2006 12:00:01 AM

As I mentioned in the original post, this works fine on the XP box but not the Win2003. The are a number of reports; some accept parameters (all of which appear to fail with the same error) and others that don't, all of which work as expected. Other than the OS, and SQL as opposed to MSDE the only other differerence I can think of is the copy of VS2005.

Thanks.

Private Function LoadReport(ByVal ReportNameAs String, _ByVal ViewerAs CrystalDecisions.Windows.Forms.CrystalReportViewer, _Optional ByVal ParametersAs Hashtable =Nothing)As Boolean Dim rptAs New ReportDocumentDim pvCollAs New CrystalDecisions.Shared.ParameterValues' Objects used to set the proper database connection informationDim tbCurrentAs CrystalDecisions.CrystalReports.Engine.TableDim tliCurrentAs CrystalDecisions.Shared.TableLogOnInfoDim pdValAs New CrystalDecisions.Shared.ParameterDiscreteValueTry Dim appPathAs String = Application.ExecutablePath appPath = appPath.Substring(0, appPath.LastIndexOf("\") + 1)Dim fileExistsAs Boolean = false fileExists = My.Computer.FileSystem.FileExists(appPath &"Reports\" & ReportName)If fileExistsThen rpt.Load(appPath &"Reports\" & ReportName)Else MsgBox("Cannot locate report file", MsgBoxStyle.Critical,"Load Error")     pvColl =Nothing    pdVal =NothingExit Function End If For Each tbCurrentIn rpt.Database.Tables tliCurrent = tbCurrent.LogOnInfoWith tliCurrent.ConnectionInfo .ServerName = ServerName .UserID ="" .Password ="" .DatabaseName = My.Settings.DBName.ToStringEnd With tbCurrent.ApplyLogOnInfo(tliCurrent)Next tbCurrentIf Not IsNothing(Parameters)Then Dim myEnumAs IDictionaryEnumerator = Parameters.GetEnumeratorWhile myEnum.MoveNext pvColl.Clear() pdVal.Value = myEnum.Value pvColl.Add(pdVal) rpt.DataDefinition.ParameterFields(myEnum.Key).ApplyCurrentValues(pvColl)End While End If Viewer.ReportSource = rptCatch ExpAs LoadSaveReportException MsgBox("Report " & ReportName &" not found.", _ MsgBoxStyle.Critical,"Load Report Error")Catch ExpAs Exception MsgBox(Exp.Message, MsgBoxStyle.Critical,"General Error")Finally pvColl =Nothing pdVal =Nothing End Try End Function

|||

Thanks for the help. The problem is a crystal reports one and is detailed here

http://support.businessobjects.com/library/kbase/articles/c2017367.asp

MSDE - Job Scheduling and Stored Procedures

Can MSDE run stored procedures? Does MSDE have the same job scheduling features that SQL Standard and Enterprise Edition have?
SR
Yes and yes.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"SR" <mv2k_2003-news@.yahoo.com> wrote in message news:ALq0d.13990$QJ3.9851@.newssvr21.news.prodigy.c om...
Can MSDE run stored procedures? Does MSDE have the same job scheduling features that SQL Standard and Enterprise Edition have?
SR
|||yes and no.
The SQL Express does not have job scheduler on board.
regards
bav