存储过程,在线等

[hejunheng]

我这里有一个存储过程有个问题,我想先判断一下@filePath（路径）下的文件是否为空,如果为空，就不执行现在的存储过程，确定有数据的情况下才导入数据，请问怎么判断

ALTER proc [dbo].[intsert_membergold] 
@filePath varchar(200)--路径
as
truncate table MemberGold
exec('
BULK INSERT MemberGold
FROM '''+@filePath+'''
WITH
(
   FIELDTERMINATOR = ''|'',
   ROWTERMINATOR = ''\n''
)
')

[xuexuebj]

ALTER proc [dbo].[intsert_membergold]
@filePath varchar(200)--路径
as
if isnull(@filePath,'') <> ''
begin
truncate table MemberGold
exec('
BULK INSERT MemberGold
FROM '''+@filePath+'''
WITH
(
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''\n''
)
')
end

[7761098]

DECLARE
@filePath varchar(200)，
@retval   int,
@param1   int
EXECUTE   @retval=master..xp_fileexist   @filePath,   @param1   OUTPUT  
IF   @param1   =   0
PRINT   'File   does   not   exist! '
ELSE
PRINT   'File   exists! '

[7761098]

create proc p_QueryCheckFile
    @path nvarchar(1000),
    @fname nvarchar(250)
as

--检查文件是否已经存在
if right(@path,1)<>''''''''\'''''''' 
    set @path=@path+''''''''\''''''''


if exists (select * 
            from sysobjects 
                where type=''''''''u''''''''
                    and 
                name = ''''''''temp_xp_fileexist'''''''')
    begin
        drop table temp_xp_fileexist
    end
        
create table temp_xp_fileexist(a bit,b bit,c bit)
declare @sql nvarchar(1000)
set @sql=@path+@fname


insert into temp_xp_fileexist 
    exec master..xp_fileexist @sql

/*
xp_fileexist 返回的三个列,   分别代表

文件已存在   文件是目录   父目录已存在   
  -----       -----       ------   
  0               0               1   
*/

if exists(select 1 from temp_xp_fileexist where a=1)
    --文件已经存在
    begin
         declare @del nvarchar(200)
            select @del = ''''''''del ''''''''+@sql 
           exec  master..xp_cmdshell  @del 
    end
GO

[mydogs520]

ALTER proc [dbo].[intsert_membergold]
@filePath varchar(200)--路径
as
exec('xp_cmdshell ''dir '+@a+'''')
select @@rowcount
if (@@rowcount<=7) --此处值视系统而视 如果是空目录看看是几就可以了
begin
truncate table MemberGold
exec('
BULK INSERT MemberGold
FROM '''+@filePath+'''
WITH
(
FIELDTERMINATOR = ''|'',
ROWTERMINATOR = ''\n''
)
')
end

[mydogs520]

晕倒 把@a换成输入的参数....!-_-
[Quote=引用 4 楼 mydogs520 的回复:]
ALTER proc [dbo].[intsert_membergold]
@filePath varchar(200)--路径
as
exec('xp_cmdshell ''dir '+@a+'''')
select @@rowcount
if (@@rowcount <=7) --此处值视系统而视 如果是空目录看看是几就可以了
begin
truncate table MemberGold
exec('
BULK INSERT MemberGold
FROM '''+@filePath+'''
WITH
(
  FIELDTERMINATOR = ''|'',
  ROWTERMINATOR = ''\n''
)
')
end

[/Quote]

[hejunheng]

[Quote=引用 5 楼 mydogs520 的回复:]
晕倒 把@a换成输入的参数....!-_-
引用 4 楼 mydogs520 的回复:
ALTER proc [dbo].[intsert_membergold]
@filePath varchar(200)--路径
as
exec('xp_cmdshell ''dir '+@a+'''')
select @@rowcount
if (@@rowcount <=7) --此处值视系统而视 如果是空目录看看是几就可以了
begin
truncate table MemberGold
exec('
BULK INSERT MemberGold
FROM '''+@filePath+'''
WITH
(
  FIELDTERMINATOR = ''|'',
  ROWTERMINATOR = ''\n''
)
')
end
[/Quote]
SQL Server 阻止了对组件 'xp_cmdshell' 的 过程'sys.xp_cmdshell' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'xp_cmdshell'。有关启用 'xp_cmdshell' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。

有这个错，请问怎么弄?

[soft_wsx]

EXEC sp_configure 'show advanced options', 1 --
;RECONFIGURE WITH OVERRIDE --
;EXEC sp_configure 'xp_cmdshell', 1 --
;RECONFIGURE WITH OVERRIDE -- 
;EXEC sp_configure  'show advanced options', 0 -- 

[soft_wsx]

exec xp_cmdshell 'ipconfig/all' 

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE  WITH OVERRIDE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE  WITH OVERRIDE
GO
/*
output
 
Windows IP Configuration 
 
        Host Name . . . . . . . . . . . . : YANGXING 
        Primary Dns Suffix  . . . . . . . :  
        Node Type . . . . . . . . . . . . : Unknown 
        IP Routing Enabled. . . . . . . . : No 
        WINS Proxy Enabled. . . . . . . . : No 
 
Ethernet adapter 无线网络连接: 
 
        Media State . . . . . . . . . . . : Media disconnected 
        Description . . . . . . . . . . . : Intel(R) WiFi Link 5100 AGN 
        Physical Address. . . . . . . . . : 00-21-6B-AD-FF-C6 
 
Ethernet adapter 本地连接: 
 
        Connection-specific DNS Suffix  . :  
        Description . . . . . . . . . . . : Intel(R) 82567LM Gigabit Network Connection 
        Physical Address. . . . . . . . . : 00-21-86-FF-18-61 
        Dhcp Enabled. . . . . . . . . . . : No 
        IP Address. . . . . . . . . . . . : 192.168.1.134 
        Subnet Mask . . . . . . . . . . . : 255.255.255.0 
        Default Gateway . . . . . . . . . : 192.168.1.1 
        DNS Servers . . . . . . . . . . . : 222.172.200.68 
 
Ethernet adapter Bluetooth Network: 
 
        Media State . . . . . . . . . . . : Media disconnected 
        Description . . . . . . . . . . . : 蓝牙局域网接入服务器驱动程序 
        Physical Address. . . . . . . . . : 00-23-4D-F2-A1-DC 
NULL
*/

哥哥，你怎么不给分呀！

[hejunheng]

[Quote=引用 7 楼 soft_wsx 的回复:]
SQL codeEXEC sp_configure'show advanced options',1--;RECONFIGUREWITH OVERRIDE--;EXEC sp_configure'xp_cmdshell',1--;RECONFIGUREWITH OVERRIDE--;EXEC sp_configure'show advanced options',0--
[/Quote]
上面的存储过程还是把空，返回了文件的信息过后还是把空文件给导入了,

[hejunheng]

[Quote=引用 7 楼 soft_wsx 的回复:]
SQL codeEXEC sp_configure'show advanced options',1--;RECONFIGUREWITH OVERRIDE--;EXEC sp_configure'xp_cmdshell',1--;RECONFIGUREWITH OVERRIDE--;EXEC sp_configure'show advanced options',0--
[/Quote]
我呆会加分呗，但还是没人帮我把存储过程改对.

[hejunheng]

[Quote=引用 4 楼 mydogs520 的回复:]
ALTER proc [dbo].[intsert_membergold]
@filePath varchar(200)--路径
as
exec('xp_cmdshell ''dir '+@a+'''')
select @@rowcount
if (@@rowcount <=7) --此处值视系统而视 如果是空目录看看是几就可以了
begin
truncate table MemberGold
exec('
BULK INSERT MemberGold
FROM '''+@filePath+'''
WITH
(
  FIELDTERMINATOR = ''|'',
  ROWTERMINATOR = ''\n''
)
')
end

[/Quote]
没有行大哥