String str="use master " +
"go "+
"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) " +
"drop procedure [dbo].[p_killspid] " +
"go "+
"create proc p_killspid "
"@dbname sysname " +
"as " +
"declare @s nvarchar(1000) " +
"declare tb cursor local for " +
"select s='kill '+cast(spid as varchar) " +
"from master..sysprocesses " +
"where dbid=db_id(@dbname) " +
"open tb " +
"fetch next from tb into @s " +
"while @@fetch_status=0 " +
"begin " +
"exec(@s) " +
"fetch next from tb into @s " +
"end " +
"close tb "
"deallocate tb "+
"go ";
总是出现如下错误:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]第 1 行: 'go' 附近有语法错误。
"go "+
"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) " +
"drop procedure [dbo].[p_killspid] " +
"go "+
"create proc p_killspid "
"@dbname sysname " +
"as " +
"declare @s nvarchar(1000) " +
"declare tb cursor local for " +
"select s='kill '+cast(spid as varchar) " +
"from master..sysprocesses " +
"where dbid=db_id(@dbname) " +
"open tb " +
"fetch next from tb into @s " +
"while @@fetch_status=0 " +
"begin " +
"exec(@s) " +
"fetch next from tb into @s " +
"end " +
"close tb "
"deallocate tb "+
"go ";
总是出现如下错误:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]第 1 行: 'go' 附近有语法错误。
"go "+
"create proc p_killspid "
"drop procedure [dbo].[p_killspid] go create proc p_killspid
这条语句拼的对吗,你好好看看..
第六行少了一个+号
"create proc p_killspid "
"@dbname sysname " +
在Java里就被喀嚓了咩.
StringBuffer sql = new StringBuffer();
sql.append("use master ");
sql.append("go ");
sql.append("create proc p_killspid ");
...
...
最后直接使用sql.toString();
至少比+号直观.
应该~~是字符串~~
ms拼接问题~~保证一行一行拼接~
试下
"use master" +
"go"+
"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)" +
"drop procedure [dbo].[p_killspid] GO" +
"create proc p_killspid" +
"@dbname sysname" +
"as " +
"declare @s nvarchar(1000)" +
"declare tb cursor local for" +
"select s='kill '+cast(spid as varchar)" +
"from master..sysprocesses " +
"where dbid=db_id(@dbname)" +
"open tb " +
"fetch next from tb into @s" +
"while @@fetch_status=0" +
"begin" +
"exec(@s)" +
"fetch next from tb into @s" +
"end" +
"close tb" +
"deallocate tb" +
"go";
总是说java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]未能在 sysdatabases 中找到数据库 'mastergoif' 所对应的条目。没有找到具有该名称的条目。请确保正确地输入了名称。
我又在每个分号后加了个空格 又出现java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]第 1 行: 'go' 附近有语法错误。
use master
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_killspid]
GO
create proc p_killspid
@dbname sysname
as
declare @s nvarchar(1000)
declare tb cursor local for
select s='kill '+cast(spid as varchar)
from master..sysprocesses
where dbid=db_id(@dbname)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
go
exec p_killspid 'Chaoshi'
RESTORE DATABASE Chaoshi FROM disk='E:\毕业设计\CHAOSHI\444.mdf'
StringBuffer sql = new StringBuffer();
sql.append("use master \n");
sql.append("go \n");
sql.append("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) \n");
sql.append("drop procedure [dbo].[p_killspid] \n");
sql.append("GO \n");
sql.append("create proc p_killspid \n");
sql.append("@dbname sysname \n");
sql.append("as \n");
sql.append("declare @s nvarchar(1000) \n");
sql.append("declare tb cursor local for \n");
sql.append("select s='kill '+cast(spid as varchar) \n");
sql.append("from master..sysprocesses \n");
sql.append("where dbid=db_id(@dbname) \n");
sql.append("open tb \n");
sql.append("fetch next from tb into @s \n");
sql.append("while @@fetch_status=0 \n");
sql.append("begin \n");
sql.append("exec(@s) \n");
sql.append("fetch next from tb into @s \n");
sql.append("end \n");
sql.append("close tb \n");
sql.append("deallocate tb \n");
sql.append("go \n");
sql.append("exec p_killspid 'Chaoshi' \n");
sql.append("RESTORE DATABASE Chaoshi FROM disk='E:\\毕业设计\\CHAOSHI\\444.mdf'\n");
又出现这样的问题:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]'CREATE PROCEDURE' 必须是批查询中的第一条语句。
猜测,很久不用MS Sql了手头也没有
drop procedure [dbo].[killspid]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOcreate proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500),@temp varchar(1000)
declare @spid int
set @sql='declare getspid cursor for
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status =0
begin
set @temp='kill '+rtrim(@spid)
exec(@temp)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
endGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
还有一点需要说明一下,你上面是用到了大量的字符串相加的动作,建议频繁操作字符串相加动作的话,使用StringBuffer,或者StringBuilder把最后的结果再toString就好,这样会效率很高
祝你成功.....