In query analyzer I wrote the code below. (Initially minus the debugging
lines.)
I had no problem running the create script. Then I went to debug with the
TSQL Debugger. No matter how I tweaked, the debugger would only insert to the
base table, G_Survey, not the join table.

Bordering on frustration, I ran the stored procedure with an exec statement.
The procedure ran in its entirety updating both tables as expected.

Architecture: SQL 2000 running on Server 2003

Anyone have any ideas what's up?

CREATE PROCEDURE usp_InsertGmacSurveyResponse
    @fName nvarchar(50),
    @lName nvarchar(50),
    @orgName nvarchar(50),
    @phone nvarchar(50),
    @email nvarchar(50),
    @bidCount tinyint,
    @contractCount tinyint,
    @quarter nvarchar(1) = 0,
    @year nvarchar(4),
    @happy int = 0,
    @changeScreen int = 0,
    @fewerMore int = 0,
    @discontinue int = 0,
    @reintroduce int = 0,
    @newRecordID int OUTPUT
AS
    --INSERT BASE SURVEY INFO
    INSERT INTO G_Survey
        (organizationName,
        contactFName,
        contactLName,
        contactPhone,
        contactEmail,
        bidCount,
        contractCount,
        calendarQuarter,
        calendarYear,
        dateCompleted)
    VALUES (@orgName,
        @fName,
        @lName,
        @phone,
        @email,
        @bidCount,
        @contractCount,
        @quarter,
        @year,
        GetDate())
    IF(@@ERROR = 0)--DETERMINE IF INSERT WAS SUCCESSFUL
        BEGIN
PRINT @@ERROR
PRINT @happy
PRINT @changeScreen
PRINT @fewerMore
            SET @newRecordID = (SELECT @@IDENTITY)--RETRIEVE ID FOR NEW ROW
PRINT @newRecordID
            --EVALUATE CHECKBOX VALUES. UPDATE JOIN TABLE APPROPRIATELY.
            IF (@happy > 0)
            BEGIN
PRINT 'WE"RE HAPPY'
                INSERT INTO join_GSurvey_howImprove VALUES (5,4)
                INSERT INTO join_GSurvey_howImprove VALUES (@newRecordID,@happy)
            END
            IF (@changeScreen > 0)
                INSERT INTO join_GSurvey_howImprove VALUES (@newRecordID,@changeScreen)

            IF (@fewerMore > 0)
                INSERT INTO join_GSurvey_howImprove VALUES (@newRecordID,@fewerMore)

            IF (@discontinue > 0)
                INSERT INTO join_GSurvey_howImprove VALUES (@newRecordID,@discontinue)

            IF (@reintroduce > 0)
                INSERT INTO join_GSurvey_howImprove VALUES (@newRecordID,@reintroduce)
        END
    ELSE
        BEGIN
            SET @newRecordID = -1
        END

Try moving the following comment:

Either before the IF or after the BEGIN statement. Of course I'm just
guessing here.


ML

---
http://milambda.blogspot.com/

Thanks for the input, but that didn't change anything.

Show quoteHide quote

Hi, anon

By default, the T-SQL debugger is configured to automatically roll back
the transaction when debugging is finnished. See the second button in
the debugging toolbar, counting from the right side ("Auto Rollback").
However, in case that this button was pressed, it would be expected
that the first insert has been rolled back, too.

Razvan

Thanks. I actually tinkered with that as well. With Auto Rollback selected
the no insert at all was made. So it seems the rollback affected the first
insert. Changing the rollback selection enabled the first insert to be
retained but no others were.

Show quoteHide quote

If you step through the procedure in the debugger, do you get in the
correct branch of the IF-s ? Do you see the results of the PRINT
statements ?

Unrelated to the main issue:
- you should add a SET NOCOUNT ON at the beginning of the procedure (it
prevents a lot of problems when using ADO, but maybe in some other
cases too)
- you can use "SET @newRecordID = @@IDENTITY" (without the subquery)
- you should use "SET @newRecordID = SCOPE_IDENTITY()" so you get the
correct value even if you have a trigger on the G_Survey table which
inserts rows in another table(s) with an identity column.

Razvan

Razvan,

Thanks for the unrelated advice. It's always nice to get something extra. ;-)

I just was back doing some work on the SP, so I thought I'd try your
suggestions. I noticed an odd thing. The buttons that one uses to step into a
proc are grayed out and inactive. Then when I click the button to execute the
deBugger, they blink active and then grey out again.

The print lines do come out. Always did, including the one inside the if
block. What I never get is any of the information like stack trace and the
lists of variable values. Could I possibly have something mis-configured?

To add to all my confusion, all of the inserts now work from a run in the
deBugger. I didn't make any changes that would cause a significant change in
functionality. Essentially added another parameter used in the first insert.

Hmmmm?

Thanks again

Show quoteHide quote

If you cannot step through the procedure and the breakpoints are
ignored, it seems that there is a problem with the debugger. There are
some KB articles related to the T-SQL Debugger:
http://support.microsoft.com/kb/296539
http://support.microsoft.com/kb/317241
http://support.microsoft.com/kb/328151
http://support.microsoft.com/kb/280101
http://support.microsoft.com/kb/817178
http://msdn.microsoft.com/library/en-us/trblsql/tr_servtools_5cfm.asp

The most probable solution is to make sure that SQL Server runs under a
user account (not under the LocalSystem account) and that this user
account has Administrator priviledges. Also, it's best to use the
debugger on the local SQL Server (if you try to use remote debugging,
additional problems may occur if you don't have the same Service Pack
on both computers).

Razvan

Razvan,

Thanks again for your help. The issues might be related to the issues you
mentioned with remote debugging. I'll try it locally and see what happens.
Also thanks for the kb links.

Take care,

Bryan

Show quoteHide quote

Newbie:UNION with SELECT TOP
store image
DB design question.
Building View Dynamically .. Need Help!
Updating a view ... (SQL Sever 2000)