vb.net下如何调用存储过程,大家帮帮忙!
要还原一个数据库,假设是student.在master数据库上建一个存储过程来关闭这个student数据库的所有用户连接,存储过程代码如下,这是csdn上站友提供的,肯定没有出错。
/*
关闭用户打开的进程处理
*/
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 varchar(200) --要关闭进程的数据库名
as
declare @sql nvarchar(500)
declare @spid nvarchar(20)
declare #tb cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #tb
fetch next from #tb into @spid
while @@fetch_status=0
begin
exec('kill '+@spid)
fetch next from #tb into @spid
end
close #tb
deallocate #tb
go
我在vb.net下用如下代码调用:
Dim sConnectionString As String = _
"data source=localhost;initial catalog=master;integrated security=SSPI;persist security info=False;workstation id=XIER-7Y049H16BQ;packet size=4096"
Dim sqlConn As New SqlConnection(sConnectionString)
Dim myCmd As New SqlCommand()
With myCmd
.Connection = sqlConn
.CommandText = "p_killspid"
.CommandType = CommandType.StoredProcedure
End With
Dim prmSql As SqlParameter = myCmd.Parameters.Add("@dbname", "student")
prmSql.Direction = ParameterDirection.Input
sqlConn.Open()
myCmd.ExecuteNonQuery()
Dim sSQL As String = "restore database student from disk='d:\bb.bak'" //数据库还原语句
Dim cmd As New SqlCommand(sSQL, sqlConn)
cmd.ExecuteNonQuery()
sqlConn.Close()
但是程序一直出错,一直不能还原。大家帮帮忙,到底该怎么修改啊,这是我第一次用这个,所以都不知道怎么办,希望大家热心指导,不胜感激!