From: http://www.cnblogs.com/wangjunchao/archive/2010/05/23/1742206.html
1 Public Int Createdb_killproc_proc ()
2 {
3 Int Result = 0 ;
4 String Sqlstr = "" ;
5 // Combined SQL statements
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
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 // Execute SQL statements
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 }
However, the following error occurs during execution:
The following are the scripts that can be used normally in the query Analyzer:Code.
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 " ;
The query analyzer is an SQL client that recognizes go for batch submission. However, go is not an SQL statement and cannot be recognized by the SQL server.Program.
Therefore, the statements with go are divided into multiple SQL statements, and multiple SQL statements are executed to implement database transactions. The Code is as follows:
1 Public Int Createdb_killproc_proc ()
2 {
3 List < String > Strsqls = New List < String > ();
4 Int Result = 0 ;
5 String Sqlstr = "" ;
6 // Combined SQL statements
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
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 // Execute SQL statements
29 Try
30 {
31 Result = Dbhelpersql. executesqltran (strsqls );// This function is used to execute multiple SQL statements to implement database transactions.
32 }
33 Catch (Exception E)
34 {
35 Errstr = E. message;
36 Return - 2 ;
37 }
38 Return Result;
39 }
40