Insert to table fails when using case sensitive collation

[bealer]

Hi Adam, thanks for the proc, use it all the time! Bug found: on an instance using case sensitive collation the results column order for "CPU" is incorrect, causing the insert statement to fail when returning results to a table. This happens because "CPU" is in upper case everywhere except for @output_column_list. To fix either change "CPU" to lower case everywhere (to match the other column names) or force the case or collation in the ordered_columns CTE. Cheers!

[erikdarlingdata]

@bealer hey there, can you provide some minimal steps to reproduce? I do all my testing on a case sensitive instance, and I'm able to insert into tables just fine.

Can you also provide some basic script information, like the script version/date?

Thanks, Erik

[bealer]

Hi Eric,

Thanks for getting back to me, and thanks again for this exceedingly useful proc

Who Is Active? v12.00 (2021-11-10)

If you create your output table using the @schema definition generated by the same instance you won’t get an error, because the columns will be in the same order in both the CREATE TABLE and the INSERT INTO statements (which is a good thing, obviously 😊).

The problem arises for us because we are using a permanent table created using a @schema definition previously generated by a case insensitive instance, and the column order is different from the CS instance.

To observe the phenomenon, please compare the @schema definitions returned by the following call params on a CI and a CS a instance:

DECLARE @schema VARCHAR(4000)

EXEC EXP_REINDEX_JT.dbo.sp_WhoIsActive @get_transaction_info = 1, @get_plans = 1, @show_system_spids = 1, @show_sleeping_spids = 2, @get_locks = 1, @find_block_leaders = 1, @return_schema = 1, @schema = @schema OUTPUT

print @schema

My results (both instances SQL Server 2019):

--SQL_Latin1_General_CP1_CI_AS CREATE TABLE ( [dd hh:mm:ss.mss] varchar(8000) NULL,[session_id] smallint NOT NULL,[sql_text] xml NULL,[login_name] nvarchar(128) NOT NULL,[wait_info] nvarchar(4000) NULL,[tran_log_writes] nvarchar(4000) NULL,[CPU] varchar(30) NULL,[tempdb_allocations] varchar(30) NULL,[tempdb_current] varchar(30) NULL,[blocking_session_id] smallint NULL,[blocked_session_count] varchar(30) NULL,[reads] varchar(30) NULL,[writes] varchar(30) NULL,[physical_reads] varchar(30) NULL,[query_plan] xml NULL,[locks] xml NULL,[used_memory] varchar(30) NULL,[status] varchar(30) NOT NULL,[tran_start_time] datetime NULL,[open_tran_count] varchar(30) NULL,[percent_complete] varchar(30) NULL,[host_name] nvarchar(128) NULL,[database_name] nvarchar(128) NULL,[program_name] nvarchar(128) NULL,[start_time] datetime NOT NULL,[login_time] datetime NULL,[request_id] int NULL,[collection_time] datetime NOT NULL)

--SQL_Latin1_General_CP1_CS_AS CREATE TABLE ( [dd hh:mm:ss.mss] varchar(8000) NULL,[session_id] smallint NOT NULL,[sql_text] xml NULL,[login_name] nvarchar(128) NOT NULL,[wait_info] nvarchar(4000) NULL,[tran_log_writes] nvarchar(4000) NULL,[tempdb_allocations] varchar(30) NULL,[tempdb_current] varchar(30) NULL,[blocking_session_id] smallint NULL,[blocked_session_count] varchar(30) NULL,[reads] varchar(30) NULL,[writes] varchar(30) NULL,[physical_reads] varchar(30) NULL,[query_plan] xml NULL,[locks] xml NULL,[CPU] varchar(30) NULL,[used_memory] varchar(30) NULL,[status] varchar(30) NOT NULL,[tran_start_time] datetime NULL,[implicit_tran] nvarchar(3) NULL,[open_tran_count] varchar(30) NULL,[percent_complete] varchar(30) NULL,[host_name] nvarchar(128) NULL,[database_name] nvarchar(128) NULL,[program_name] nvarchar(128) NULL,[start_time] datetime NOT NULL,[login_time] datetime NULL,[request_id] int NULL,[collection_time] datetime NOT NULL);

The [CPU] column comes in at seventh place on the CI instance but sixteenth on the CS instance. The other columns are the same.

I’ve fixed the problem temporarily on our CS server by changing CPU to lower case everywhere in the proc. I understand you will of course have to maintain backward compatibility on the output side (for case sensitive instances!) so we will roll out the new version if you decide to fix this.

Hope this is clear! Just let me know if you need any further details.

Many thanks

Best regards, Richard

De : Erik Darling @.> Envoyé : jeudi 31 octobre 2024 19:21 À : amachanic/sp_whoisactive @.> Cc : Richard BEALE (Ext) @.>; Mention @.> Objet : Re: [amachanic/sp_whoisactive] Insert to table fails when using case sensitive collation (Issue #123)

@bealerhttps://urldefense.com/v3/__https:/github.com/bealer__;!!KmNDdzRp!y_iIP-PxdFdzdqH1bjxtZ_yoX3iTzC8jDHmnCG2keGxChC_cElHgj4lM13L1baLZnZSyAFtowjZPyWK6SJ9ffinSOj4UIko$ hey there, can you provide some minimal steps to reproduce? I do all my testing on a case sensitive instance, and I'm able to insert into tables just fine.

Can you also provide some basic script information, like the script version/date?

Thanks, Erik

— Reply to this email directly, view it on GitHubhttps://urldefense.com/v3/__https:/github.com/amachanic/sp_whoisactive/issues/123*issuecomment-2450547171__;Iw!!KmNDdzRp!y_iIP-PxdFdzdqH1bjxtZ_yoX3iTzC8jDHmnCG2keGxChC_cElHgj4lM13L1baLZnZSyAFtowjZPyWK6SJ9ffinSDr_hluY$, or unsubscribehttps://urldefense.com/v3/__https:/github.com/notifications/unsubscribe-auth/BMSKTTBI5H4GDBX45HJ4VKDZ6JYHFAVCNFSM6AAAAABQ6VZMEKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJQGU2DOMJXGE__;!!KmNDdzRp!y_iIP-PxdFdzdqH1bjxtZ_yoX3iTzC8jDHmnCG2keGxChC_cElHgj4lM13L1baLZnZSyAFtowjZPyWK6SJ9ffinSo7aC-JM$. You are receiving this because you were mentioned.Message ID: @.**@.>>

---

This message and its attachments are intended solely for the addressee(s) and are confidential. If you receive this message in error please delete it and immediately notify the sender. Any use of this message and/or its contents by anyone other than an addressee and any further distribution or disclosure of its contents, either in whole or in part, is prohibited, except with the prior written approval of the sender. This message has no contractual value and in no way represents a commitment of Hermès International or any of its subsidiaries. This message has been transmitted over the Internet and Hermès International (and its subsidiaries) cannot therefore guarantee its integrity nor that it will be received and read only by the addressee(s). In no event shall Hermès International (and its subsidiaries) be liable for any modifications to the message that may occur during its transmission, nor for any unauthorized use of its contents by anyone other than the addressee(s). pod

---