August 11, 2020 at 1:36 am
I have a count variable incrementing and printing out. I have a log for every catch and every try. Nothing is unexpected except the number of items (rows) created in the destination database. I will test one of these non-existent rows with the relevant parameters to see if I can get an error at source.
August 11, 2020 at 5:03 am
It's hard to be specific with an advice without knowing how the procedure is built.
but generally speaking - you can include any value of any variable you're interested in while debugging into those raiserror messages. You may have the message before population and immediately after the row population. "After" statement may contain the number of added records.
This way you can see which particular variable(s) values caused missing rows.
You can make those messages as detailed as you need for your debugging. I never felt a need for debug functionality - the messages tell me everything I may possibly want to know, including flow control.
_____________
Code for TallyGenerator
August 11, 2020 at 8:22 pm
ok it is solved and posted here for any interest.
Seems it comes down to my inexperience with cursors. I'd normally avoid them due to the bad press but at times I find them a necessary evil. With that said.....
So to recap, there were 57 rows of data with only 31 iterations of the cursor and no errors. To confuse me even more the last line of the cursor, which flags a row (in the source data) as processed updated 55 rows (2 rows had data errors).
So, it was my assumption that when you did a select, with a where clause, for a cursor you got a snapshot of the data or a temp table in memory of sorts. But it appears that for each iteration the source data was rechecked.
The last line of code was to update the row as processed where column X = Variable X. Seems this test data had duplication so column X may appear 3 times and all 3 rows would get flagged. Then rightfully that row was not processed. Hence the mismatch in the initial select and the number of iterations. Seems I inadvertently added a safety net to deal with duplicate values :). Anyways mystery solved.
A quick refresh of the cursor doco reminds me of all the available keywords that can alter a cursors behaviour.
August 11, 2020 at 8:53 pm
I missed debugging in SSMS for a short while although I didn't miss the number of times debugging would crash SSMS. I think I'm actually happier with my current method for debugging stored procedures.
Stored procedure:
CREATE OR ALTER PROCEDURE dbo.GetObjectTypeFromSysObjects (
@ObjectType CHAR(2)
)
AS
BEGIN
/*
Object types:
TT IF SN U SQ SO F PC C D P V S TR K IT TF FN
*/
SELECT
sc.name,
s.name,
s.type,
s.type_desc
FROM sys.objects AS s
INNER JOIN sys.schemas AS sc ON s.schema_id = sc.schema_id
WHERE s.type = @ObjectType
ORDER BY sc.name, s.name;
END;
I'll just open the SP in SSMS and comment out a couple lines, add a DECLARE and default values and RAISERROR() as Sergiy mentioned
--CREATE OR ALTER PROCEDURE dbo.GetObjectTypeFromSysObjects (
DECLARE
@ObjectType CHAR(2) = 'P'
--)
--AS
BEGIN
/*
Object types:
TT IF SN U SQ SO F PC C D P V S TR K IT TF FN
*/
-- Use RAISERROR(). PRINT will not display until the batch finishes.
-- RAISERROR() displays immediately using WITH NOWAIT
RAISERROR(N'Object type = %s', 0, 1, @ObjectType) WITH NOWAIT;
SELECT
sc.name,
s.name,
s.type,
s.type_desc
FROM sys.objects AS s
INNER JOIN sys.schemas AS sc ON s.schema_id = sc.schema_id
WHERE s.type = @ObjectType
ORDER BY sc.name, s.name;
END;
RAISERROR() WITH NOWAIT will output to the message window as soon as that line is encountered unlike PRINT which will wait for the batch to complete. I scatter these throughout to check state and usually that is enough to tell me what is going on.
August 11, 2020 at 8:58 pm
I missed debugging in SSMS for a short while although I didn't miss the number of times debugging would crash SSMS. I think I'm actually happier with my current method for debugging stored procedures.
...
Did you see my post? Debugging is still available in VS, should you decide you'd like to use it.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 11, 2020 at 9:02 pm
Tom Uellner wrote:I missed debugging in SSMS for a short while although I didn't miss the number of times debugging would crash SSMS. I think I'm actually happier with my current method for debugging stored procedures.
...
Did you see my post? Debugging is still available in VS, should you decide you'd like to use it.
I did Phil, thank you. I am very rarely in Visual Studio anymore but it's good to have the option. Just giving the OP another option.
August 12, 2020 at 8:05 pm
I think you can get that behavior with one of those keywords right?
INSENSITIVE
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications. When ISO syntax is used, if INSENSITIVE is omitted, committed deletes and updates made to the underlying tables (by any user) are reflected in subsequent fetches.
Just reading about your solution out of curiousity thanks for posting what actually happened!
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply