#1266 Weird SQL locking unrelated queries coming from Squirrel

[Joseph Ercole]

Hi,

I'm finding the following SQL to be coming from squirrel and locking other DB's. Our system is all Microsoft SQL databases. I've disabled the MS SQL plugin because I'm guessing it is coming from that as the system tables are proprietary to MS SQL.

Any ideas where this might be coming from or how to disable it (if not the MS SQL plugin?).

Thanks in advance!,
Joe

<?query --
UPDATE s
                    SET
                        s.sql_text =
                        (
                            SELECT
                                REPLACE
                                (
                                    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                                        N'--' + NCHAR(13) + NCHAR(10) +
                                        CASE 
                                            WHEN @get_full_inner_text = 1 THEN est.text
                                            WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN est.text
                                            WHEN SUBSTRING(est.text, (@statement_start_offset/2), 2) LIKE N'[a-zA-Z0-9][a-zA-Z0-9]' THEN est.text
                                            ELSE
                                                CASE
                                                    WHEN @statement_start_offset > 0 THEN
                                                        SUBSTRING
                                                        (
                                                            est.text,
                                                            ((@statement_start_offset/2) + 1),
                                                            (
                                                                CASE
                                                                    WHEN @statement_end_offset = -1 THEN 2147483647
                                                                    ELSE ((@statement_end_offset - @statement_start_offset)/2) + 1
                                                                END
                                                            )
                                                        )
                                                    ELSE RTRIM(LTRIM(est.text))
                                                END
                                        END +
                                        NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2,
                                        NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
                                        NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
                                        NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
                                    NCHAR(0),
                                    N''
                                ) AS [processing-instruction(query)]
                            FOR XML
                                PATH(''),
                                TYPE
                        ),
                        s.statement_start_offset = 
                            CASE 
                                WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN 0
                                WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN 0
                                ELSE @statement_start_offset
                            END,
                        s.statement_end_offset = 
                            CASE 
                                WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN -1
                                WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN -1
                                ELSE @statement_end_offset
                            END
                    FROM 
                        #sessions AS s,
                        (
                            SELECT TOP(1)
                                text
                            FROM
                            (
                                SELECT 
                                    text, 
                                    0 AS row_num
                                FROM sys.dm_exec_sql_text(@sql_handle)

                                UNION ALL

                                SELECT 
                                    NULL,
                                    1 AS row_num
                            ) AS est0
                            ORDER BY
                                row_num
                        ) AS est
                    WHERE 
                        s.session_id = @session_id
                        AND s.request_id = @request_id
                        AND s.recursion = 1
                    OPTION (KEEPFIXED PLAN)
--?>

[Gerd Wagner]

No, this SQL isn't executed by SQuirreL.
I guess it's generated by your JDBC driver.

[Joseph Ercole]

Thanks. I haven't seen it since I turned off the MS SQL pluggin, but that could just be coincidence. I'll be watching for it.

I'm hoping something like that isn't coming from the JDBC driver but you never know.

[wael hamood mohammed melhi]

May be You are Use Sp_WhoIsActive Procedure or sp_blitzWhoIs

[Gerd Wagner]

Sorry, I can't reproduce your problem. I checked the following:

• I couldn't find any trace of the SQL you posted in SQuirreL's source code.
• With the MS SQL Plugin installed I couldn't find the SQL you posted by

SELECT t.[text], s.last_execution_time
FROM sys.dm_exec_cached_plans AS p
INNER JOIN sys.dm_exec_query_stats AS s ON p.plan_handle = s.plan_handle
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
ORDER BY s.last_execution_time DESC;

[Joseph Ercole]

Hi. Respectfully, this was from 6 years ago so I'm guessing whatever the
issue was was solved in the MS SQL driver I was using or something like
that.

Probably can close this ticket.

On Sat, Nov 26, 2022, 2:09 PM Gerd Wagner gerdwagner@users.sourceforge.net
wrote:

Sorry, I can't reproduce your problem. I checked the following:
- I couldn't find any trace of the SQL you posted in SQuirreL's source
code.
- With the MS SQL Plugin installed I couldn't find the SQL you posted by

SELECT t.[text], s.last_execution_timeFROM sys.dm_exec_cached_plans AS pINNER JOIN sys.dm_exec_query_stats AS s ON p.plan_handle = s.plan_handleCROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS tORDER BY s.last_execution_time DESC;

---

• [bugs:#1266] https://sourceforge.net/p/squirrel-sql/bugs/1266/ Weird
  SQL locking unrelated queries coming from Squirrel*

Status: open
Group: SQuirreL
Created: Fri Aug 26, 2016 03:19 PM UTC by Joseph Ercole
Last Updated: Mon Nov 21, 2022 06:08 PM UTC
Owner: nobody

Hi,

I'm finding the following SQL to be coming from squirrel and locking other
DB's. Our system is all Microsoft SQL databases. I've disabled the MS SQL
plugin because I'm guessing it is coming from that as the system tables are
proprietary to MS SQL.

Any ideas where this might be coming from or how to disable it (if not the
MS SQL plugin?).

Thanks in advance!,
Joe

<?query --UPDATE s
SET
s.sql_text =
(
SELECT
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
N'--' + NCHAR(13) + NCHAR(10) +
CASE
WHEN @get_full_inner_text = 1 THEN est.text
WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN est.text
WHEN SUBSTRING(est.text, (@statement_start_offset/2), 2) LIKE N'[a-zA-Z0-9][a-zA-Z0-9]' THEN est.text
ELSE
CASE
WHEN @statement_start_offset > 0 THEN
SUBSTRING
(
est.text,
((@statement_start_offset/2) + 1),
(
CASE
WHEN @statement_end_offset = -1 THEN 2147483647
ELSE ((@statement_end_offset - @statement_start_offset)/2) + 1
END
)
)
ELSE RTRIM(LTRIM(est.text))
END
END +
NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2,
NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'),
NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),
NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'),
NCHAR(0),
N''
) AS [processing-instruction(query)]
FOR XML
PATH(''),
TYPE
),
s.statement_start_offset =
CASE
WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN 0
WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN 0
ELSE @statement_start_offset
END,
s.statement_end_offset =
CASE
WHEN LEN(est.text) < (@statement_end_offset / 2) + 1 THEN -1
WHEN SUBSTRING(CONVERT(VARCHAR(MAX), est.text), (@statement_start_offset/2), 2) LIKE '[a-zA-Z0-9][a-zA-Z0-9]' THEN -1
ELSE @statement_end_offset
END
FROM
#sessions AS s,
(
SELECT TOP(1)
text
FROM
(
SELECT
text,
0 AS row_num
FROM sys.dm_exec_sql_text(@sql_handle)

                            UNION ALL

                            SELECT
                                NULL,
                                1 AS row_num
                        ) AS est0
                        ORDER BY
                            row_num
                    ) AS est
                WHERE
                    s.session_id = @session_id
                    AND s.request_id = @request_id
                    AND s.recursion = 1
                OPTION (KEEPFIXED PLAN)--?>

---

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/squirrel-sql/bugs/1266/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/

[Gerd Wagner]

Closed on user request.