May 2, 2002 at 5:14 am
I have this simple update stored proc, when called via VB or anywhere else I get a weird error, the SP Code is;
/* Procedure to update auto record */
CREATE PROCEDURE usp_auto_updaterec
(
@auto_id VarChar(36),
@reg_no VarChar(10),
@make VarChar(20),
@model VarChar(30),
@eng_size decimal,
@lease_from datetime,
@term int,
@milage VarChar,
@lease_until datetime,
@payroll VarChar(15),
@own_fore VarChar(20),
@own_surn VarChar(30),
@own_div VarChar(20),
@own_loc VarChar(15),
@hire_coVarChar(20),
@mod_comments Varchar(50),
@engine_type VarChar(1),
@hands_free Bit,
@archived Bit,
@comments text,
@mgt_grade VarChar (15),
@gde_allowance Money,
@needs_status VarChar (25),
@mth_rentmaint Money,
@xs_milage_rte Money,
@fuel_card Bit,
@cash_alt Money,
@cash_alt_began datetime,
@cash_alt_renew datetime,
@cash_alt_cease datetime,
@trade_down Money,
@add_extras Money,
@ni_no VarChar (9),
@ret_date DateTime
)
AS
BEGIN
Declare @iAutoID UniqueIdentifier
Set @iAutoID = CAST(@auto_id as UniqueIdentifier)
UPDATE auto_main
Setreg_no = @reg_no,
make = @make,
model = @model,
eng_size = @eng_size,
lease_from = @lease_from,
term = @term ,
milage = @milage,
lease_until = @lease_until,
payroll = @payroll,
own_fore = @own_fore,
own_surn = @own_surn,
own_div = @own_div,
own_loc = @own_loc,
hire_co = @hire_co,
mod_comments = @mod_comments,
engine_type = @engine_type,
hands_free = @hands_free,
archived = @archived,
comments = @comments,
mgt_grade = @mgt_grade,
gde_allowance = @gde_allowance,
needs_status = @needs_status,
mth_rentmaint = @mth_rentmaint,
xs_milage_rte = @xs_milage_rte ,
fuel_card = @fuel_card,
cash_alt = @cash_alt,
cash_alt_began = @cash_alt_began,
cash_alt_renew = @cash_alt_renew,
cash_alt_cease = @cash_alt_cease,
trade_down = @trade_down,
add_extras = @add_extras,
ni_no = @ni_no,
ret_date = @ret_date
WHERE auto_id = @iAutoID
END
GO
The error is a stack dump. I checked the MS webiste and there are a few instances of this error message, but they all say to install SP2. I`ve done that, but still the same problem. I`ve tried writing the SP a couple of ways but get errors.
Andy.
May 2, 2002 at 5:34 am
Test with this to see if the same thing happens. I am wondering if something is happening to the auto_id value in the cast blowing it up.
CREATE PROCEDURE usp_auto_test
(
@auto_id VarChar(36)
)
AS
BEGIN
Declare @iAutoID UniqueIdentifier
Set @iAutoID = CAST(@auto_id as UniqueIdentifier)
SELECT *
FROM auto_main
WHERE auto_id = @iAutoID
END
GO
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 2, 2002 at 6:07 am
Gets the error I was originally getting when I didn`t put the UniqueID in a Variable. This is;
Syntax error converting from a character string to uniqueidentifier.
The case function must work on UID`s ok as I did Select CAST('{7B8760F7-5D0C-11D6-B583-00508BF3AE0D}' as UniqueIdentifier) and it works ok??
I`m confused!
Andy.
May 2, 2002 at 6:10 am
Sorry antares, I got rid of the Braces, and now it works fine??
Any other ideas?
Andy.
May 2, 2002 at 6:16 am
Can you post the tables DDL as I want to test here a bit better than just shooting in the dark? Also have you installed any other patches maybe security related that could have back revised an item from SP2 since you last installed SP2?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 2, 2002 at 6:17 am
I also tried;
WHERE CAST(auto_id as VarCHar(36) = @auto_id
Gets the same exception violation AFTER writing the data back ok. I don`t really want to ignore the error in the Application, which I could do, but thats bad programming. 🙂
Andy.
May 2, 2002 at 6:29 am
Have you tried stepping thru in debug on the app to see if there is something specific that happens? Also try adding SET NOCOUNT ON to the SP.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 2, 2002 at 7:26 am
The app`s fine, calling the SP`s in exacly the same way as normal...!
Andy.
May 10, 2002 at 3:32 am
hi,
i have the same problem here - same syntax error! only difference: i haven't installed SP2 yet - i will try now and see if it makes a difference.
tried nocount on - no difference either.
greetings,
kerstin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply