我写了个存储过程:
CREATE PROCEDURE [dbo].[P_SettlePrc]
--@IP:服务器ip @UID:数据库用户 @PW:数据库密码 @cDBName:数据库名
@IP nvarchar(50), @UID nvarchar(100),@PW nvarchar(100),@cDBName nvarchar(20),
AS
BEGIN SET NOCOUNT ON; declare @Server nvarchar(1000)
declare @SQL nvarchar(1000) Set @Server='''SQLOLEDB'',''DRIVER={SQL Server};SERVER='+@IP+';UID='+@UID+';PWD='+@PW+';'''
set @Server='OPENDATASOURCE('+@Server+').'+@cDBName exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure set @SQL='SELECT * From '+@Server+'.dbo.Vendor'
exec(@SQL) exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure END 在查询分析器里 exec P_SettlePrc 'fengxujian','sa','123','pub' 运行通过
但是vb调用该过程是确提示:
配置选项 'show advanced options' 从 0 改为 1。请运行 RECONFIGURE 语句以安装。
请高手指点,vb中如何调用? 我写的VB代码:
Dim rec As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param As New ADODB.Parameter
If con.State = adStateClosed Then con.Open Sys_DataSourceName cmd.ActiveConnection = con
con.CursorLocation = adUseClient
cmd.CommandText = "P_SettlePrc"
cmd.CommandType = adCmdStoredProc
Set param = cmd.CreateParameter("IP", adChar, adParamInput, 50, "fengxujian")
cmd.Parameters.Append param Set param = cmd.CreateParameter("UID", adChar, adParamInput, 100, "sa")
cmd.Parameters.Append param Set param = cmd.CreateParameter("PW", adChar, adParamInput, 100, "123")
cmd.Parameters.Append param Set param = cmd.CreateParameter("cDBName", adChar, adParamInput, 20, "pub")
cmd.Parameters.Append param
Set rec = cmd.Execute()
CREATE PROCEDURE [dbo].[P_SettlePrc]
--@IP:服务器ip @UID:数据库用户 @PW:数据库密码 @cDBName:数据库名
@IP nvarchar(50), @UID nvarchar(100),@PW nvarchar(100),@cDBName nvarchar(20),
AS
BEGIN SET NOCOUNT ON; declare @Server nvarchar(1000)
declare @SQL nvarchar(1000) Set @Server='''SQLOLEDB'',''DRIVER={SQL Server};SERVER='+@IP+';UID='+@UID+';PWD='+@PW+';'''
set @Server='OPENDATASOURCE('+@Server+').'+@cDBName exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure set @SQL='SELECT * From '+@Server+'.dbo.Vendor'
exec(@SQL) exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure END 在查询分析器里 exec P_SettlePrc 'fengxujian','sa','123','pub' 运行通过
但是vb调用该过程是确提示:
配置选项 'show advanced options' 从 0 改为 1。请运行 RECONFIGURE 语句以安装。
请高手指点,vb中如何调用? 我写的VB代码:
Dim rec As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim param As New ADODB.Parameter
If con.State = adStateClosed Then con.Open Sys_DataSourceName cmd.ActiveConnection = con
con.CursorLocation = adUseClient
cmd.CommandText = "P_SettlePrc"
cmd.CommandType = adCmdStoredProc
Set param = cmd.CreateParameter("IP", adChar, adParamInput, 50, "fengxujian")
cmd.Parameters.Append param Set param = cmd.CreateParameter("UID", adChar, adParamInput, 100, "sa")
cmd.Parameters.Append param Set param = cmd.CreateParameter("PW", adChar, adParamInput, 100, "123")
cmd.Parameters.Append param Set param = cmd.CreateParameter("cDBName", adChar, adParamInput, 20, "pub")
cmd.Parameters.Append param
Set rec = cmd.Execute()
过程里的
SET NOCOUNT ON
该为
SET NOCOUNT OFF
晕死,手误。