August 6, 2005 at 11:20 am
hi...
i have a stored proc that has a number of inserts and the first one is failing without error (@@error = 0) and the rest continue just fine. if cut and paste this stored proc into query analyzer and set the one variable that gets passed to this procedure manually, all statements execute as expected.
any ideas what could cause this type of behavior?
thanks...
August 6, 2005 at 12:54 pm
Nabeel would you please post your DDL for your tables and the code for the Sp. Thanks
Mike
August 6, 2005 at 1:22 pm
hi mike,
thanks for responding... i've tried swapping the first two INSERTS earlier, didn't help. i just tried dropping the table and recreating it, that didn't help either. i think there's something wrong with that particular INSERT but it's also not generating an error. here's the latest DDL for the table and the sp code is below that.
thanks again...
CREATE TABLE [dbo].[users] (
[custnmbr] [int] NOT NULL ,
[username] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[password] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[type] [smallint] NOT NULL ,
[accountStatus] [smallint] NOT NULL ,
[statusUpdated] [datetime] NOT NULL ,
[lastLogon] [datetime] NOT NULL ,
[parent] [int] NOT NULL ,
[signupDate] [datetime] NOT NULL ,
[nextBillDate] [datetime] NOT NULL ,
[billingPlan] [smallint] NOT NULL ,
[newPlan] [smallint] NOT NULL ,
[billTo] [int] NOT NULL ,
[lowBal] [decimal](10, 4) NOT NULL ,
[ceiling] [decimal](10, 4) NOT NULL ,
[balance] [decimal](10, 4) NOT NULL ,
[autoRecharge] [tinyint] NOT NULL ,
[callBackNumber] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[timezone] [smallint] NOT NULL ,
[quota] [int] NOT NULL ,
[question] [smallint] NOT NULL ,
[answer] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[users] WITH NOCHECK ADD
CONSTRAINT [DF_users_accountStatus] DEFAULT (0) FOR [accountStatus],
CONSTRAINT [DF_users_statusUpdated] DEFAULT (getdate()) FOR [statusUpdated],
CONSTRAINT [DF_users_lastLogon] DEFAULT (getdate()) FOR [lastLogon],
CONSTRAINT [DF_users_signupDate] DEFAULT (getdate()) FOR [signupDate],
CONSTRAINT [DF_users_nextBillDate] DEFAULT (getdate()) FOR [nextBillDate],
CONSTRAINT [DF_users_lowBal] DEFAULT (0) FOR [lowBal],
CONSTRAINT [DF_users_ceiling] DEFAULT (50) FOR [ceiling],
CONSTRAINT [DF_users_balance] DEFAULT (0) FOR [balance],
CONSTRAINT [DF_users_autoRecharge] DEFAULT (1) FOR [autoRecharge],
CONSTRAINT [DF_users_callBackNumber] DEFAULT ('') FOR [callBackNumber]
CREATE PROCEDURE dbo.sp_createUser (@signupID int, @clientID int output)
AS
--SET XACT_ABORT ON
DECLARE @lowBal DECIMAL(10, 4)
INSERT INTO usersID (generated) VALUES (GETDATE())
SET @clientID = @@identity
SELECT @lowBal = amount FROM unitValues u, plans p WHERE firstUnitValue = u.ID AND itemID = '4050-9910-0000' AND planID = (SELECT billingPlan FROM signupInfo WHERE [ID] = @signupID)
INSERT INTO usersInfo -- (custnmbr, custname, custclas, cntcprsn, adrscode, address1, address2, address3, country, city, state, zip, phone1, phone2, phone3, fax, crcardid, crcrdnum, ccrdxpdt, send_email_statements)
SELECT @clientID, custname, custclas, cntcprsn, '001', address1, address2, address3, country, city, state, zip, phone1, phone2, '', fax, '', '', getdate(), 1
FROM signupInfo where [ID] = @signupID
insert into cachelog (result) values ('past usersInfo') -- i was using this for debugging
-- the one below is failing
INSERT INTO users (custnmbr, username, password, type, parent, billTo, billingPlan, newPlan, lowBal, timezone, quota, question, answer)
SELECT @clientID, username, [password], type, parentID, @clientID, billingPlan, billingPlan, @lowBal, timezone, 100, question, answer FROM signupInfo WHERE [ID] = @signupID
insert into cachelog (result) values ('past users') -- i was using this for debugging
UPDATE signupInfo SET custnmbr = @clientID WHERE [ID] = @signupID
UPDATE cart SET clientID = @clientID WHERE clientID = @signupID
INSERT INTO callwords.dbo.userPrefs (clientID) VALUES (@clientID)
INSERT INTO usersCards (custnmbr, cardIndex, cardholder, ccType, ccNumber, ccExpMonth, ccExpYear)
SELECT @clientID, '001', cardname, crcardid, crcrdnum, ccexpmonth, ccexpyear FROM signupInfo WHERE [ID] = @signupID
August 6, 2005 at 7:12 pm
I would try changing the following if on SQL2000; not sure if it exists on SQL 7.0
SET @clientID = @@identity
to
SET @clientID = SCOPE_IDENTITY( )
Tim S
August 6, 2005 at 7:20 pm
I would add a line below
SELECT @lowBal = amount FROM unitValues u, plans p WHERE firstUnitValue = u.ID AND itemID = '4050-9910-0000' AND planID = (SELECT billingPlan FROM signupInfo WHERE [ID] = @signupID)
OF
IF @lowBal IS NULL SET @lowBal = 0.0 -- Set to Valid Value
Tim S
August 7, 2005 at 1:15 am
i actually did check @lowBal before and it is getting set correctly... if i copy/paste the entire procedure into query analyzer and set @signupID manually, it works just fine... for some reason that one statement does not insert any data, but the rest do.
August 7, 2005 at 2:09 am
Have you checked the value of @signupID to see what is being passed to the sp?
Mike
August 7, 2005 at 9:54 am
but all the other statements execute properly... the statement inserting into table 'usersInfo' is basically the same thing and that doesn't have any problem.
August 7, 2005 at 10:16 am
hardcoding the failing statement with valid values seems to make it work. so i'm going to try adjusting one value at a time to see which one causes it to fail...
i should be able to take it from here... thanks.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply