1
public
int
CreateDB_KillProc_proc()
2 {
3 int result = 0 ;
4 string SqlStr = "" ;
5 // 组合Sql语句
6 SqlStr += @" USE [RadarDataBase]
7 GO
8 SET ANSI_NULLS ON
9 GO
10 SET QUOTED_IDENTIFIER ON
11 GO
12 create proc [dbo].[P_KillConnections]
13 @dbname varchar(200)
14 as
15 declare @sql nvarchar(500)
16 declare @spid nvarchar(20)
17 declare #tb cursor for
18 select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
19 open #tb
20 fetch next from #tb into @spid
21 while @@fetch_status=0
22 begin
23 exec('kill '+@spid)
24 fetch next from #tb into @spid
25 end close #tb deallocate #tb " ;
26
27 // 执行Sql语句
28 try
29 {
30 result = DbHelperSQL.ExecuteSql(SqlStr);
31 }
32 catch (Exception e)
33 {
34 ErrStr = e.Message;
35 return - 2 ;
36 }
37 return result;
38 }
2 {
3 int result = 0 ;
4 string SqlStr = "" ;
5 // 组合Sql语句
6 SqlStr += @" USE [RadarDataBase]
7 GO
8 SET ANSI_NULLS ON
9 GO
10 SET QUOTED_IDENTIFIER ON
11 GO
12 create proc [dbo].[P_KillConnections]
13 @dbname varchar(200)
14 as
15 declare @sql nvarchar(500)
16 declare @spid nvarchar(20)
17 declare #tb cursor for
18 select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
19 open #tb
20 fetch next from #tb into @spid
21 while @@fetch_status=0
22 begin
23 exec('kill '+@spid)
24 fetch next from #tb into @spid
25 end close #tb deallocate #tb " ;
26
27 // 执行Sql语句
28 try
29 {
30 result = DbHelperSQL.ExecuteSql(SqlStr);
31 }
32 catch (Exception e)
33 {
34 ErrStr = e.Message;
35 return - 2 ;
36 }
37 return result;
38 }
但执行过程中出现以下错误
以下是我在查询分析器中能正常使用的脚本代码。
1
USE [RadarDataBase]
2 GO
3 SET ANSI_NULLS ON
4 GO
5 SET QUOTED_IDENTIFIER ON
6 GO
7 create proc [dbo].[P_KillConnections]
8 @dbname varchar( 200 )
9 as
10 declare @sql nvarchar( 500 )
11 declare @spid nvarchar( 20 )
12 declare #tb cursor for
13 select spid = cast(spid as varchar( 20 )) from master..sysprocesses where dbid = db_id(@dbname)
14 open #tb
15 fetch next from #tb into @spid
16 while @@fetch_status = 0
17 begin
18 exec( ' kill '+@spid)
19 fetch next from #tb into @spid
20 end close #tb deallocate #tb " ;
2 GO
3 SET ANSI_NULLS ON
4 GO
5 SET QUOTED_IDENTIFIER ON
6 GO
7 create proc [dbo].[P_KillConnections]
8 @dbname varchar( 200 )
9 as
10 declare @sql nvarchar( 500 )
11 declare @spid nvarchar( 20 )
12 declare #tb cursor for
13 select spid = cast(spid as varchar( 20 )) from master..sysprocesses where dbid = db_id(@dbname)
14 open #tb
15 fetch next from #tb into @spid
16 while @@fetch_status = 0
17 begin
18 exec( ' kill '+@spid)
19 fetch next from #tb into @spid
20 end close #tb deallocate #tb " ;
查询分析器是sql客户端,它可以识别go用来分批提交,但go不是sql语句,sql服务器不能识别所以不能用在程序中。
所以将带有GO的语句分成多条sql语句,执行多条SQL语句,实现数据库事务,代码如下:
1
public
int
CreateDB_KillProc_proc()
2 {
3 List < string > strSqls = new List < string > ();
4 int result = 0 ;
5 string SqlStr = "" ;
6 // 组合Sql语句
7 SqlStr += " USE [RadarDataBase] " ;
8 strSqls.Add(SqlStr);
9 SqlStr = " SET ANSI_NULLS ON " ;
10 strSqls.Add(SqlStr);
11 SqlStr = " SET QUOTED_IDENTIFIER ON " ;
12 strSqls.Add(SqlStr);
13 SqlStr = @" create proc [dbo].[P_KillConnections]
14 @dbname varchar(200)
15 as
16 declare @sql nvarchar(500)
17 declare @spid nvarchar(20)
18 declare #tb cursor for
19 select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
20 open #tb
21 fetch next from #tb into @spid
22 while @@fetch_status=0
23 begin
24 exec('kill '+@spid)
25 fetch next from #tb into @spid
26 end close #tb deallocate #tb " ;
27 strSqls.Add(SqlStr);
28 // 执行Sql语句
29 try
30 {
31 result = DbHelperSQL.ExecuteSqlTran(strSqls);// 这个函数功能是执行多条sql语句实现数据库事务
32 }
33 catch (Exception e)
34 {
35 ErrStr = e.Message;
36 return - 2 ;
37 }
38 return result;
39 }
40
2 {
3 List < string > strSqls = new List < string > ();
4 int result = 0 ;
5 string SqlStr = "" ;
6 // 组合Sql语句
7 SqlStr += " USE [RadarDataBase] " ;
8 strSqls.Add(SqlStr);
9 SqlStr = " SET ANSI_NULLS ON " ;
10 strSqls.Add(SqlStr);
11 SqlStr = " SET QUOTED_IDENTIFIER ON " ;
12 strSqls.Add(SqlStr);
13 SqlStr = @" create proc [dbo].[P_KillConnections]
14 @dbname varchar(200)
15 as
16 declare @sql nvarchar(500)
17 declare @spid nvarchar(20)
18 declare #tb cursor for
19 select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
20 open #tb
21 fetch next from #tb into @spid
22 while @@fetch_status=0
23 begin
24 exec('kill '+@spid)
25 fetch next from #tb into @spid
26 end close #tb deallocate #tb " ;
27 strSqls.Add(SqlStr);
28 // 执行Sql语句
29 try
30 {
31 result = DbHelperSQL.ExecuteSqlTran(strSqls);// 这个函数功能是执行多条sql语句实现数据库事务
32 }
33 catch (Exception e)
34 {
35 ErrStr = e.Message;
36 return - 2 ;
37 }
38 return result;
39 }
40