problem returning IDENTITY

Hi all,
I have a sp where I only do an insert, and am trying to return the
identity value created. Here's what my sp resemble :
...
AS
SET NOCOUNT ON
-- do the insert
DECLARE @.ret
SELECT @.ret = SCOPE_IDENTITY()
RETURN @.ret
I tried to return directly SCOPE_IDENTITY(), I also tried to change
SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
don't know what to do anymore. A few times it returned always 1 and at
other times, it was always returning -1. This depended on what options
I've tried, I don't remember what situation returned what value...but
for now, written as above, it is returning -1.
thanks for your help!
ibiza wrote:

> Hi all,
> I have a sp where I only do an insert, and am trying to return the
> identity value created. Here's what my sp resemble :
> ...
> AS
> SET NOCOUNT ON
> -- do the insert
> DECLARE @.ret
> SELECT @.ret = SCOPE_IDENTITY()
> RETURN @.ret
> I tried to return directly SCOPE_IDENTITY(), I also tried to change
> SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
> don't know what to do anymore. A few times it returned always 1 and at
> other times, it was always returning -1. This depended on what options
> I've tried, I don't remember what situation returned what value...but
> for now, written as above, it is returning -1.
> thanks for your help!
It isn't a good idea to use RETURN to return data from a proc. Use
RETURN for error status only: zero = OK, non-zero = error. To return
other values use an output parameter or a result set:
CREATE PROC usp_x
(@.param1 INTEGER, @.ret INTEGER OUTPUT)
AS
SET NOCOUNT ON;
SET @.ret = 123;
RETURN
GO
DECLARE @.r INTEGER;
EXEC usp_x @.param1 = 1, @.ret = @.r OUTPUT;
SELECT @.r;
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
|||thank you very much for your reply. Well, it does work with an output
parameter!
And I will take note of your remark for my upcoming posts.
Thanks again!

problem returning IDENTITY

Hi all,
I have a sp where I only do an insert, and am trying to return the
identity value created. Here's what my sp resemble :
...
AS
SET NOCOUNT ON
-- do the insert
DECLARE @.ret
SELECT @.ret = SCOPE_IDENTITY()
RETURN @.ret
I tried to return directly SCOPE_IDENTITY(), I also tried to change
SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
don't know what to do anymore. A few times it returned always 1 and at
other times, it was always returning -1. This depended on what options
I've tried, I don't remember what situation returned what value...but
for now, written as above, it is returning -1.
thanks for your help!ibiza wrote:
> Hi all,
> I have a sp where I only do an insert, and am trying to return the
> identity value created. Here's what my sp resemble :
> ...
> AS
> SET NOCOUNT ON
> -- do the insert
> DECLARE @.ret
> SELECT @.ret = SCOPE_IDENTITY()
> RETURN @.ret
> I tried to return directly SCOPE_IDENTITY(), I also tried to change
> SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
> don't know what to do anymore. A few times it returned always 1 and at
> other times, it was always returning -1. This depended on what options
> I've tried, I don't remember what situation returned what value...but
> for now, written as above, it is returning -1.
> thanks for your help!
It isn't a good idea to use RETURN to return data from a proc. Use
RETURN for error status only: zero = OK, non-zero = error. To return
other values use an output parameter or a result set:
CREATE PROC usp_x
(@.param1 INTEGER, @.ret INTEGER OUTPUT)
AS
SET NOCOUNT ON;
SET @.ret = 123;
RETURN
GO
DECLARE @.r INTEGER;
EXEC usp_x @.param1 = 1, @.ret = @.r OUTPUT;
SELECT @.r;
--
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
--|||thank you very much for your reply. Well, it does work with an output
parameter!
And I will take note of your remark for my upcoming posts.
Thanks again! :)

problem returning IDENTITY

Hi all,
I have a sp where I only do an insert, and am trying to return the
identity value created. Here's what my sp resemble :
...
AS
SET NOCOUNT ON
-- do the insert
DECLARE @.ret
SELECT @.ret = SCOPE_IDENTITY()
RETURN @.ret
I tried to return directly SCOPE_IDENTITY(), I also tried to change
SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
don't know what to do anymore. A few times it returned always 1 and at
other times, it was always returning -1. This depended on what options
I've tried, I don't remember what situation returned what value...but
for now, written as above, it is returning -1.
thanks for your help!ibiza wrote:

> Hi all,
> I have a sp where I only do an insert, and am trying to return the
> identity value created. Here's what my sp resemble :
> ...
> AS
> SET NOCOUNT ON
> -- do the insert
> DECLARE @.ret
> SELECT @.ret = SCOPE_IDENTITY()
> RETURN @.ret
> I tried to return directly SCOPE_IDENTITY(), I also tried to change
> SCOPE_IDENTITY() with @.@.IDENTITY, with and without SET NOCOUNT ON...I
> don't know what to do anymore. A few times it returned always 1 and at
> other times, it was always returning -1. This depended on what options
> I've tried, I don't remember what situation returned what value...but
> for now, written as above, it is returning -1.
> thanks for your help!
It isn't a good idea to use RETURN to return data from a proc. Use
RETURN for error status only: zero = OK, non-zero = error. To return
other values use an output parameter or a result set:
CREATE PROC usp_x
(@.param1 INTEGER, @.ret INTEGER OUTPUT)
AS
SET NOCOUNT ON;
SET @.ret = 123;
RETURN
GO
DECLARE @.r INTEGER;
EXEC usp_x @.param1 = 1, @.ret = @.r OUTPUT;
SELECT @.r;
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
--|||thank you very much for your reply. Well, it does work with an output
parameter!
And I will take note of your remark for my upcoming posts.
Thanks again!

Problem returning a timestamp column inside an TSQL Transaction

I cannot manage to fetch the new timestamp value inside a TSQL Transaction. I have tried to Select "@.LastChanged" before committing the transaction and after committing the transaction. A TimestampCheck variable is used to get the timestamp value of the Custom Business Object. It is checked against the row updating to see if they match. If they do, the Update begins as a Transaction. I send @.LastChanged (timestamp) and an InputOutput param, But I also have the same problem sending in a dedicated timestamp param ("@.NewLastChanged"):

1select @.TimestampCheck = LastChangedfrom ADD_Addresswhere AddressId=@.AddressId23if @.TimestampCheckisnull4begin5RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1)-- AddressId does not exist.6return -17end8 else if @.TimestampCheck <> @.LastChanged9begin10RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1)11return -112end131415Begin Tran Address1617Update ADD_Address18set StreetNumber= @.StreetNumber, AddressLine1=@.AddressLine1, StreetTypeId=@.StreetTypeId, AddressLine2=@.AddressLine2, AddressLine3=@.AddressLine3, CityId=@.CityId, StateProvidenceId=@.StateProvidenceId, ZipCode=@.ZipCode, CreateId=@.CreateId, CreateDate=@.CreateDate19where AddressId= @.AddressId2021select @.error_code =@.@.ERROR, @.AddressId= scope_identity()2223if @.error_code = 024begin25commit tran Address2627select @.LastChanged = LastChanged28from ADD_Address29where AddressId = @.AddressId3031if @.LastChangedisnull32begin33RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1)34return -135end36if @.LastChanged = @.TimestampCheck37begin38RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1)39return -140end41return 0

I do not have this problem if I do not use a TSQL Transaction. Is there a way to capture the new timestamp inside a Transaction, or have I missed something?

Thank you,

jspurlin 

No need for a transaction for that really. Just go about it a different way:

Update {fields} FROM {table} WHEREAddressID=@.AddressID ANDLastChanged=@.LastChanged

Then get the number of records affected, and the error codes.

if there were no errors, and no rows were affected then you can either raise a generic error ('record changed or does not exist'), or you can go and look and see which of the two (AddressID,LastChanged) didn't exist, although there is the possability the data may change between the update and when you go and try to figure out why it failed, in which case you may get an incorrect error message (Saying it didn't exist, when it was only changed or saying it was changed when it didn't exist).

Problem Retrieving SCOPE_IDENTITY

A couple of Web applications in different SQL Server 2000 databases use SCOPE_IDENTITY to retrieve the key value of a record that was just inserted. It works--most of the time. However, from time to time the identity value is not retrieved. Evidence suggests that in these cases, a null value is being retrieved. This has forced me to come up with less-than-ideal workarounds for the missing identity value.

Does anyone have any idea why SCOPE_IDENTITY sometimes fails to retrieve the identity value and transmit it back to the Web page? Could a network issue cause the problem? Is there anything I can do other than rewrite the apps to use a different algorithm than using SCOPE_IDENTITY? Thanks.

I am not aware of any issues with SCOPE_IDENTITY(); this might be an application / connection issue and not a problem with SCOPE_IDENTITY(). I am certainly interested in the outcome of this. Can somebody please check me on this?|||

If you are using embedded SQL in your application it might be worth placing this logic into a stored procedure and calling that from your application. That should avoid any comms problems as the procedure will run or not run as a single call (and not have a problem between statements in the operation).

|||

Yes, the web app uses embedded SQL in classic ASP. The application was written in classic ASP and there has never been a good reason to rewrite it. The web app is the only application that performs DML on the table--there are no separate triggers or other ways into the table.

How could an embedded SQL statement in a single Web page cause scope problems? One Web page consulted during the research on this problem said this situation should be treated as a single scope.

I will probably try the stored procedure method. But I am curious as to why all sources practically demand that SCOPE_IDENTITY be used within a stored procedure when it is allowed to work in other situations.

Thanks for the input.

problem passing parameter to crystal from .net

I do this:

' Set the name of the parameter to modify.
paramField.ParameterFieldName = "P1"
' Set a value to the parameter.
paramValue.Value = strTemp
paramField.CurrentValues.Add(paramValue)
paramField.DefaultValues.Add(paramValue)
' Add the parameter to the ParameterFields collection.
paramFields.Add(paramField)
CrystalReportViewer.ParameterFieldInfo = paramFields
strReportSource = strReportSource & "Transaction For Specified Account.rpt"
CrystalReportViewer.ReportSource = strReportSource

But I get "Load reort failed".

Any suggestions...

Thanks....hi njuser,

if you get "load report failed" you do not have a problem with passing parameters, the report generator doesn't find the report!

when you call reportDocument.Load() it needs the path of the report like this: ( use the full path of the report-file )

"c:\\inetpub\\wwwroot\\reports\\myreport.rpt"

important are the "\\", the reportdocument class don't accept a single "\".

if you have problems with passing parameters you get a message like:
"current parameter-value missing"

cu,
tomcat

Problem passing parameter into remote stored proc

I'm having a problem passing a parameter value into a stored procedure
that I am running on a remote (linked) server, and am receiving a DTC
error because of it.
I have a stored procedure that brings in a variable (@.CustID int). I
later pass that parameter to another stored procedure. The code looks
like this...
EXEC LinkedServer.dbname.dbo.spname @.CustID
When I run that, I get this error...
Server: Msg 7391, Level 16, State 1, Procedure spname, Line 394
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
However, if I hard-code the parameter, it works:
EXEC LinkedServer.dbname.dbo.spname 1234 -- this works.
I can even do this:
DECLARE @.var int
SET @.var = 1234
EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
But if I accept the variable as an input parameter to my stored
procedure, I get the error listed above.
Any ideas?
Thanks in advance for your help...
Zev Steinhardtzev_steinhardt
what happen if you assign the parameter to a variable?
DECLARE @.var int
SET @.var = @.CustID
EXEC LinkedServer.dbname.dbo.spname @.var
...
AMB
"zev_steinhardt" wrote:

> I'm having a problem passing a parameter value into a stored procedure
> that I am running on a remote (linked) server, and am receiving a DTC
> error because of it.
> I have a stored procedure that brings in a variable (@.CustID int). I
> later pass that parameter to another stored procedure. The code looks
> like this...
> EXEC LinkedServer.dbname.dbo.spname @.CustID
> When I run that, I get this error...
> Server: Msg 7391, Level 16, State 1, Procedure spname, Line 394
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> However, if I hard-code the parameter, it works:
> EXEC LinkedServer.dbname.dbo.spname 1234 -- this works.
> I can even do this:
> DECLARE @.var int
> SET @.var = 1234
> EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
> But if I accept the variable as an input parameter to my stored
> procedure, I get the error listed above.
> Any ideas?
> Thanks in advance for your help...
> Zev Steinhardt
>|||Thanks for the reply, Alejandro.
I tried that. It didn't work.
I even tried to trick it into thinking that it's another variable
altogether. I put the variable into a temp table, declared a new
variable, populated it with the value from the temp table and passed it
in. That didn't work either.
Zev Steinhardt|||zev_steinhardt,
Are you executing the remote sp inside a transaction?
AMB
"zev_steinhardt" wrote:

> Thanks for the reply, Alejandro.
> I tried that. It didn't work.
> I even tried to trick it into thinking that it's another variable
> altogether. I put the variable into a temp table, declared a new
> variable, populated it with the value from the temp table and passed it
> in. That didn't work either.
> Zev Steinhardt
>|||Yes. The remote sp is within a transaction.
Zev|||zev_steinhardt,
you are using a distributed one, correct?
begin distributed transaction
exec ...
AMB
"zev_steinhardt" wrote:

> Yes. The remote sp is within a transaction.
> Zev
>|||Alejandro...
Yes, it is a distributed transaction... and I have XACT_ABORT on
Zev|||zev_steinhardt,
When you execute the remote sp using:
DECLARE @.var int
SET @.var = 1234
EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
then you are not executing it using a distributed transaction, that is why
you do not get the error.
See if this helps.
You receive error 7391 when you run a distributed transaction against a
linked server
http://support.microsoft.com/kb/329332/en-us
AMB
"zev_steinhardt" wrote:

> Alejandro...
> Yes, it is a distributed transaction... and I have XACT_ABORT on
> Zev
>

Problem passing a variable into a table-valued function

Hi,

i am encountering a problem in a stored procedure when a pass a variable value into a table-valued function. The table-valued function is named getCurrentDriver and has 1 attribute: car-ID.

The syntax is as follows:

select car.id, car.licenceNumber, car.brand, car.model,
(select driverName from getCurrentDriver(car.id)) as driverName
from car

When I try to compile I get following error on the line of the function:
Incorrect syntax near '.'

The database version is SQL Server 2000 SP3.

What am I doing wrong? Is there a workaround for this error?select car.id, car.licenceNumber, car.brand, car.model,
dbo.getCurrentDriver(car.id) as driverName
from car|||[sniped]

select car.id, car.licenceNumber, car.brand, car.model,
, dbo.getCurrentDriver(car.id) as driverName
from car

??|||The problem is that he is putting a table-valued function in the select clause. This is not allowed:

select car.id,
car.licenceNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(car.id)) as driverName
from car

TBP, you need to JOIN to the results of a table function as if it were a table or a view:
Post the code for getCurrentDriver(), and we can help you out. Maybe you should be using a scalar function instead...|||dote. had'nt thought about that.|||also, part of the problem is that "license" is spelled wrong ;)|||Good eye. That would certainly not get past SQL Server 2005's Spell Checker.|||The problem is that he is putting a table-valued function in the select clause. This is not allowed:

select car.id,
car.licenceNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(car.id)) as driverName
from car

TBP, you need to JOIN to the results of a table function as if it were a table or a view:
Post the code for getCurrentDriver(), and we can help you out. Maybe you should be using a scalar function instead...

Hi Blindman,
are you sure you can't use table-defined function in a select clause?
The syntax works when I do this:

declare @.CarID int
select @.CarID = 123

select car.id,
car.licenseNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(@.CarID)) as driverName
from car
where car.id = @.CarID

The function getCurrentDriver is very straightforward and is tested successfully.
It seems to be a bug in SQL Server 2000 but I'm not sure...|||Good eye. That would certainly not get past SQL Server 2005's Spell Checker.
It's certainly fun trying to write SQL for tables whose columns are called "identifer" and "sirname"|||Hi Blindman,
are you sure you can't use table-defined function in a select clause?
The syntax works when I do this:

declare @.CarID int
select @.CarID = 123

select car.id,
car.licenseNumber,
car.brand,
car.model,
(select driverName
from getCurrentDriver(@.CarID)) as driverName
from car
where car.id = @.CarID

The function getCurrentDriver is very straightforward and is tested successfully.
It seems to be a bug in SQL Server 2000 but I'm not sure...
What do you expect to happen if your table function returns more than one record or more than one column? And if it always returns one record and one column, then it is a scalar function and should be defined as such.|||Hasn't this something to do with the missing schema name (owner in SQL 2000) when calling the function? Althought it beats me why the @.CarID example seems to work.

select car.id,
car.licenceNumber,
car.brand,
car.model,
(select driverName
from dbo.getCurrentDriver(car.id)) as driverName
from carsql

Problem On Store Report Parameter in Subscription ( by custom UI)

I wrote a interface on my website about create/update subscription .
when I get the parameter back by using GetReportParameters ()
the value was swap
for example:
I have 8 reportparameter: tcust, fcust, fdnnum, tdnnum, fdept, tdept, fairline, tairline.
all of them are string
when I create the subscription by:
tcust 1
fcust 2
fdnnum 3
tdnnum 4
fdept 5
tdept 6
fairline 7
tairline 8

then I get back that subscription
the value will be :
tcust 2

fcust 3

fdnnum 8

tdnnum 5

fdept 1

tdept 7

fairline 4

tairline 6

are there any order for storing the ReportParameter[] ?
thank youYou should not rely on order. ReportParameter has property Name which can be used to identify the parameter. See sample code at http://msdn2.microsoft.com/en-gb/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.getreportparameters.aspx

Problem matching input username with database

I tried matching the input username with the database. Althoughthe input value is the same as the database, but it doesnt goes intothe if statement to increase the "stat" value. Please advise what wentwrong. Thanks.
protected void btnEnter_Click(object sender, EventArgs e)
{
if (txtUsername.Text.Length > 0)
{
status++;
Label3.Text = "";
}
else
{
Label3.Text = "Please enter a username";
}
if (txtPassword.Text.Length > 0)
{
status++;
Label4.Text = "";
}
else
{
Label4.Text = "Please enter a password";
}

if (status == 2)
{
int stat = 0;
string mySelectQuery = "SELECT * FROM users";
SqlConnection myConnection = new SqlConnection("DataSource=WINSON-COMP;Initial Catalog=winson;Integrated Security=True");
SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);
try
{
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
stat = 0;
string user = (string)myReader["username"];
string pass = (string)myReader["password"];
if (user == txtUsername.Text){
stat++;
}else {
Label3.Text = "Incorrect Username";
}
if (pass == txtPassword.Text){
stat++;
}else{
Label4.Text = "Incorrect Password";
}
if (stat == 2)
{
Server.Transfer("shopping.aspx");
}
}
myReader.Close();
}
finally
{
myConnection.Close();
}
}
}for what you are doing, why would you want to do that way? To check if the user exists you dont need to get back all the rows in the table and match them on the server. You should be *sending* the values to the database and validating over there. Is there any particular reason why you are doing what you are doing?
|||Indeed. A better way would be to create a stored procedure that accepts two parameters, the username and password, and then returns fields if those two are matched. Eg.

CREATE PROCEDURE dbo.CheckLogin
@.Username VarChar(50),
@.Password VarChar(50)
AS
SET NOCOUNT ON

SELECT FirstName, LastName FROM users WHERE username = @.Username AND Password = @.Password
GO

This will return no rows if credentials match and one row if credentials are validated (if you get back more than one row then something is wrong with your database design!)

Problem inserting Now() into a datetime field

Hi

I am trying to insert value retrieved from Now() into a datetime field in my MSDE database, but I am getting the following error, and I have no idea what is going wrong.

Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Here is the code I am using:

Dim user As String = MyContext.User.Identity.Name.ToString
Dim TimeDate As DateTime = Now()
Dim status As String = "Pending"

With SqlOrders.InsertParameters
.Item("UserName").DefaultValue = user
.Item("OrderDate").DefaultValue = TimeDate
.Item("Status").DefaultValue = status
End With
SqlOrders.Insert()

The date is being returned in this format23/03/2006 02:01:52, which is the same format as it should appear in the database.

could anyone please tell me where I am going wrong?
Datetimes don't have a format. Varchar/strings that represent a datetime have a format. Make sure your OrderDate parameter is set to a datetime datatype, and your problem should go away, probably.|||Thankyou, I added .Item("OrderDate").Type = TypeCode.DateTime and it works fine now :)

Problem inserting decimal value into SQL Server 2000

In my VS 2005 windows control I am inserting a record into a table using a proc.

One of the fields "Accuracy" should look like this 66.4, but when I isnert it from the proc itlooks like 66.0. If I bypass my proc and use an inser statement from SQL quey analyzer it look like it should 66.4.

What am I doing wrong in my proc...

CREATE PROCEDURE [dbo].[insMyLameProc]

@.PlayerName nvarchar(255),
@.Score int,
@.Rounds int,
@.Accuracy decimal,
@.CorrectPicks int,
@.IncorrectPicks int

AS

--Insert the new game score
--===============================================================================================
insert into wmTurnTileScores
(PlayerName, Score, Rounds, Accuracy, CorrectPicks, IncorrectPicks)

values (@.PlayerName, @.Score, @.Rounds, @.Accuracy, @.CorrectPicks, @.IncorrectPicks)
--===============================================================================================
GO

@.Accuracy decimal(18,2)

--The 2 gives it 2 decimal places.

|||

rpack79:

@.Accuracy decimal(18,2)

--The 2 gives it 2 decimal places.

This is right, but as a tip: don't use the 18 if you are sure you will not need it. (faster query + save space).

Good luck.

|||

Yup, that worked, thanks