sqlserver的存盤程序在資料庫中執行無誤，
但python3通過pyodbc和pymssql呼叫存盤程序時，資料庫會被鎖住，
需要去任務管理器中關閉bcp行程
uj5u.com熱心網友回復：
你提供的資訊太籠統， 不清晰。你先在資料庫添加下面的存盤程序：
在資料庫鎖住的時候， 立即執行一下， 把阻塞相關的 sql 貼出來。
if OBJECT_ID('[Proc_DBA_BlockingWarning]') is not null
drop proc [Proc_DBA_BlockingWarning]
go
-- =============================================
-- Author: yenange
-- Create date: 2014-11-18
-- Description: 阻塞預警
-- =============================================
CREATE PROCEDURE [dbo].[Proc_DBA_BlockingWarning]
@BlockingWarning INT = 10 --被阻塞的會話數大于@BlockingWarning就預警
AS
BEGIN
SET NOCOUNT ON
--1. 定義表變數，并將阻塞和被阻塞的資料放入表變數
DECLARE @t TABLE (
SPID SMALLINT,
DBName NVARCHAR(128),
Remark NCHAR(3),
[ProgramName] nchar(128),
[LoginName] nchar(128),
HostName nchar(128),
[Status] nchar(30),
BlockedBy SMALLINT,
LoginTime DATETIME,
QUERY nvarchar(max)
)
INSERT INTO @t (
SPID,
DBName,
Remark,
[ProgramName],
[LoginName],
HostName,
[Status],
BlockedBy,
LoginTime,
QUERY
)
SELECT
SPID=p.spid,
DBName = convert(VARCHAR(20),d.name),
Remark = CASE WHEN p.blocked>0 THEN '被阻塞' else '阻塞源' end,
ProgramName = program_name,
LoginName = convert(CHAR(20),l.name),
HostName = convert(CHAR(20),hostname),
Status = p.status,
BlockedBy = p.blocked,
LoginTime = login_time,
QUERY = TEXT
FROM MASTER.dbo.sysprocesses p
LEFT JOIN MASTER.dbo.sysdatabases d
ON p.dbid = d.dbid
LEFT JOIN MASTER.dbo.syslogins l
ON p.sid = l.sid
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE (p.blocked = 0
AND EXISTS (SELECT 1
FROM MASTER.dbo.sysprocesses p1
WHERE p1.blocked = p.spid))
OR (p.blocked>0)
--2. 如果被阻塞的數量大于設定引數，則
DECLARE @Warning INT
IF (SELECT COUNT(1) FROM @t WHERE BlockedBy>0)>=@BlockingWarning
BEGIN
SET @Warning = 1
END
ELSE
BEGIN
SET @Warning = 0
END
SELECT @Warning AS Warning,
SPID,
DBName,
Remark,
[ProgramName],
[LoginName],
HostName,
[Status],
BlockedBy,
LoginTime,
QUERY
FROM @t
SET NOCOUNT OFF
END
go
uj5u.com熱心網友回復：
uj5u.com熱心網友回復：
bcp感覺一直卡住了uj5u.com熱心網友回復：
但查詢陳述句中直接執行存盤程序可以成功轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/226747.html
標籤：應用實例
上一篇：SQL如何對文本進行分裂分行