value is null remotely - non null locally
ok here is a WIERD issue.
I've NEVER EVER had this issue, I have always done this and it works great both locally and remotely.
I have a table, which is a "customers" table.
I have a stored procedure which takes in parameters (name, address, password etc...) and returns me back, via a parameter declared as output - the customer ID.
I've always done this, and works great.
Now, this works fine locally.
IF I try to run this/do the exact same execution of commands (create a customer) remotely (where the database is stored, so copying everything from local to "over there") it does not work.
I get a DBNULL value back from the parameter, declared as an int output.
IF I copy and paste this Stored procedure Query/command into Query Analyzer and execute it (on the remote connection) and run it, giving it the exact same values as I do, it works perfect and returns me the correct value.
I've even dropped and recreated the stored procedure but makes no difference.
Any ideas why?
here is the proc....
| |
CREATE PROCEDURE [dbo].[DoCreateNewCustomer] (
@theFirstName nvarchar(25),
@theLastName nvarchar(25),
@theAddress nvarchar(50),
@theCity nvarchar(15),
@thePostCode nvarchar(9),
@thePhoneNumber nvarchar(21),
@theMobilePhoneNumber nvarchar(21),
@theIPAddress nvarchar(25),
@thePassword nvarchar(50),
@theEmailAddress nvarchar(30),
@resultint output
)
AS
SET @result = (SELECT [ID] FROM Customers WHERE emailAddress = @theEmailAddress)
IF @result IS NOT NULL
SET @result = -1
ELSE
BEGIN
SET @result = @@IDENTITY
INSERT INTO Customers (
Firstname,
Lastname,
Address,
City,
Postcode,
PhoneNumber,
MobileNumber,
IPAddress,
[Password],
EmailAddress
)
VALUES (
@theFirstName,
@theLastName,
@theAddress,
@theCity,
@thePostCode,
@thePhoneNumber,
@theMobilePhoneNumber,
@theIPAddress,
@thePassword,
@theEmailAddress
)
END
GO
|
Any ideas why it does not work when calling the stored proc from either a web app, or through QA remotely but works fine when I run that command in QA or locally?
Again, if I do:
EXEC DoCreateNewCustomer { params } - the output result it gives me is DBNULL (if on remote connection)
If I do this locally, perfect
If I copy and paste pretty much the SP into QA and execute it on the remote connection, works great, and also locally.
You are trying to get @@IDENTITY value before inserting. And this returns last generated identity value in any scope. So if you performed an insert into any other table with identity column in the same connection before calling the SP @@IDENTITY wll return that value. And if you inserted into a table without any identity column then it will be NULL. If you are using SQL Server 2000 and higher version then you should use SCOPE_IDENTITY() and even then you should query it only after the insert. Otherwise it will return NULL. So change your code to:
insert ....
set @result = SCOPE_IDENTITY()
Also, your SP logic doesn't quite protect against concurrent executions of the SP with the same email address. If that condition happens then you will get duplicate key violation assuming that two customers with same emailaddress cannot exist or you will get duplicates if you don't have any constraints on your table. To correct it, you need to use some serializable mechanism like:
BEGIN TRANSACTION
SET @result = (SELECT [ID] FROM Customers WITH(SERIALIZABLE) WHERE emailAddress = @theEmailAddress)
IF @result IS NOT NULL
SET @result = -1
ELSE
BEGIN
INSERT INTO Customers (
Firstname,
Lastname,
Address,
City,
Postcode,
PhoneNumber,
MobileNumber,
IPAddress,
[Password],
EmailAddress
)
VALUES (
@theFirstName,
@theLastName,
@theAddress,
@theCity,
@thePostCode,
@thePhoneNumber,
@theMobilePhoneNumber,
@theIPAddress,
@thePassword,
@theEmailAddress
)
SET @result = SCOPE_IDENTITY()
END
COMMIT TRANSACTION
Thanks for that! However I did move the @result = @@Identity at the end but that still did not work. As for the email addresses, this was handled in another form, so it should be ok :) However I still don't understand why it works fine locally but not on the remote computer.
I will try your suggestion and see how it goes, but it's still a "brain scratcher" on why it doesnt work fully, like the other SP's, on the remote computer (running SQL 2000)
edit: I've tried your suggestion however its still the same problem.
I am not sure how you are handling it in another form. If the SP gets called concurrently with same email address from the client then it will not work and throw error on the server.
Anyway, did you try the SP with SCOPE_IDENTITY()? It is the safer way. If you have additional triggers on the table that you inserting that insert into other table without identity column then @@IDENTITY will return NULL in the SP. Or the table on the remote computer somehow doesn't have the identity property set on a column. Other than these, I can't think of any other reason. If SCOPE_IDENTITY() also doesn't work then please post a repro script.
Sure.
I did try you suggestion but still failed. (and I am keeping your suggestion and removing mine). There are no triggers set at all, nothing like that.
The wierd thing is if I copy and paste that code into the Query Analyzer and manually give it values, all works well but when calling the SP, it fails and its got me puzzled as I do this stuff almost all the time and works fine.
So this works in QA:
| |
DECLARE @theFirstName nvarchar(25)
SET @theFirstName = 'test'
DECLARE @theLastName nvarchar(25)
SET @theLastName = 'test'
DECLARE @theAddress nvarchar(50)
SET @theAddress = 'test'
DECLARE @theCity nvarchar(15)
SET @theCity = 'test'
DECLARE @thePostCode nvarchar(9)
SET @thePostCode = 'test'
DECLARE @thePhoneNumber nvarchar(21)
SET @thePhoneNumber = 'test'
DECLARE @theMobilePhoneNumber nvarchar(21)
SET @theMobilePhoneNumber = 'test'
DECLARE @theIPAddress nvarchar(25)
SET @theIPAddress = 'test'
DECLARE @thePassword nvarchar(50)
SET @thePassword = 'test'
DECLARE @theEmailAddress nvarchar(30)
SET @theEmailAddress = 'SomeEmail@someEmail.com'
DECLARE @result int
EXEC DoCreateNewCustomer @theFirstName, @theLastName, @theAddress, @theCity, @thePostCode, @thePhoneNumber, @thePhoneNumber, @theMobilePhoneNumber, @theIPAddress, @theEmailAddress, @result
SELECT @result --Returns DBNULL...If we do the following (exact same SP):
SET @result = (SELECT [ID] FROM Customers WITH(SERIALIZABLE) WHERE emailAddress = @theEmailAddress)
IF @result IS NOT NULL
SET @result = -1
ELSE
BEGIN
INSERT INTO Customers (
Firstname,
Lastname,
Address,
City,
Postcode,
PhoneNumber,
MobileNumber,
IPAddress,
[Password],
EmailAddress
)
VALUES (
@theFirstName,
@theLastName,
@theAddress,
@theCity,
@thePostCode,
@thePhoneNumber,
@theMobilePhoneNumber,
@theIPAddress,
@thePassword,
@theEmailAddress
)
SET @result = SCOPE_IDENTITY()
END
SELECT @result --Returns CORRECT value (ID of record)
|
Does this help?
The failure happens when executing the SP but not when we copy/paste the SP into QA and execute it normally as above
I don't think there is anything wrong with the SP itself after the modifications. Your usage in case of the remote scenario seems to be the problem. Also, the code below looks fishy:
EXEC DoCreateNewCustomer @theFirstName, @theLastName, @theAddress, @theCity, @thePostCode, @thePhoneNumber, @thePhoneNumber, @theMobilePhoneNumber, @theIPAddress, @theEmailAddress, @result /* OUTPUT is missing */
SELECT @result
You are not specifying @result as OUTPUT parameter so you will not get the value from the SP. The value of @result in the above code will be whatever it was in the batch. This happens to be NULL for uninitialized variables. So the result is expected based on what you posted. I would check your client code or whatever you are using to call the SP to see if it is specifying the OUTPUT parameter correctly.
:) not quite as in QA you CANNOT declare the "@result" parameter as output (I tried this before a while ago), I get an error if I do, but is fine when you are create/editing an SP, as I have done.
"Cannot use the OUTPUT option in a DECLARE statement"
The client code does have the output parameter correctly, otherwise I would have got an SqlException (C#) but I do not, I made sure of this :-)
I'm really stuck and bazoogled! I do however appreciate your valuable time and help, 2 minds are better than 1
You can only use OUTPUT in the parameter declaration and EXEC call. So when you call your SP you just need to add OUTPUT to the parameters declared as output in the SP:
EXEC DoCreateNewCustomer @theFirstName, @theLastName, @theAddress, @theCity, @thePostCode, @thePhoneNumber, @thePhoneNumber, @theMobilePhoneNumber, @theIPAddress, @theEmailAddress, @result OUTPUT
SELECT @result
Thank-you for this, really appreciated.
it worked. I am very surprised it works as whatever I've done before worked. Thank-you for your valuable time and effort, and a great lesson ;-)
however, since that works in QA, what about the SP itself? I mean, if I execute it, it returns me back DBNULL still, when calling from the client (C# application)
still do not understand why it works locally but not on the remote server
just an update - it works fine now from the client, even though I did not modify the code....wierd!
I applied your code for the SP earlier, didn't work, but now (after a few hours) seems to work!
Now even if I use the old code, it works....
interesting but glad the problem has been solved. My hats off to you Sir!
Also just found out that the hosting company are doing an emergency 48 hour repair....well, nice of them to not notify me by email, would've saved our headache's!
