关于xp_cmdshell的问题，真是不解呀？？？

[xiawen]

关于xp_cmdshell的问题，真是不解呀？？？

sqlserver实现导入文件时，用如下命令
EXEC master..xp_cmdshell 'bcp "txpep..temp_pep" in C:\temp_pep.txt -c -S -U -P'

在查询分析器中执行N正常
将此语句写在存储过程中，用PB来用就没有结果了，死活都不出来，真是不解
可气的是还造成死锁

不知道是不是我的方法用错了，还请哪位高手能指点下，感激不尽....!!!

[lzheng2001]

看看sqlcode及sqlerrtext返回什么?总有个提示吧

[xiawen]

返回不了值，已经死锁了

以下是死锁的信息：
-------------------------------------------------------------------------------------
引起数据库死锁的是: 65进程号,其执行的SQL语法如下

EventType Parameters EventInfo
Language Event 0 DECLARE @P005 varchar(8000) SELECT @P005 = '' execute pro_txttosql @as_user = 'admin', @as_dept = '001', @as_table = '', @as_where = '', @as_info = @P005 output SELECT @P005

进程号SPID：66被进程号SPID：65阻塞,其当前进程执行的SQL语法如下

EventType Parameters EventInfo

Language Event 0 SET FMTONLY ON select * from txpep..temp_pep SET FMTONLY OFF
-------------------------------------------------------------------

[xiawen]

lzheng2001(１加１＝０) 多多指点吓

[xiawen]

多次调试，出错的只是那一条语句，当屏蔽掉，程序可以正常运行，
否则，运行那条语句时，数据库必死锁，程序也死了，完全得不到返回值

在后台可以查到死锁的信息，在上面有给出
当kill掉引起死锁的进程后，程序能正常运行，这样就没有想要得到的结果了

真是不解？？？？？？

[lzheng2001]

1.把你的PB语句贴出来看看

2.EXEC master..xp_cmdshell 我以前试过把它放到存储过程内,再用pb调用,可以达到效果,你试试吧

[xiawen]

//************************************************************
//以下是PB调用语句
//************************************************************
ls_user_no='admin'
ls_dept_no='001'
ls_table_name='temp_pep'
ls_where=''

SetPointer(HourGlass!)
Declare pro_ftp_txttosql Procedure For opro_ftp_txttosql
@as_user_no=:ls_user_no, //用户
@as_dept_no=:ls_dept_no, //部门
@as_table_name=:ls_table_name, //表名
@as_where=:ls_where, //where条件(没有用到)
@as_info=:ls_info output; //返回信息
Execute pro_ftp_txttosql;
if sqlca.sqlcode=-1 then
ls_err=sqlca.sqlerrtext
rollback;
mle_info.ReplaceText("--数据导出失败.存储过程执行错误."+ls_err+"~r~n")
return
else
fetch pro_ftp_txttosql into :ls_info;
close pro_ftp_txttosql;
if sqlca.of_commit()<>0 then
mle_info.ReplaceText("--数据导出失败.数据库提交出错!"+"~r~n")
sqlca.of_rollback()
return
end if
end if

[xiawen]

//************************************************************
//以下是存储过程(有两个存储过程,第一个调用第二个)
//************************************************************
/*-----------------------------------------------------------------------------
名称<Script Name>: pro_ftp_txttosql
功能描述<Description>: 数据导入导出
访问方式<Usage>: 公共的
访问参数<param>:
返回值<Return Code>: N/A
设计者<Author>: xiawen
创建日期<Date Create>： 2005/04/06
版本:<Version>: 1.0
变动说明<Revision History>:
-----------------------------------------------------------------------------*/
CREATE proc pro_ftp_txttosql
@as_user_no varchar(20), --用户
@as_dept_no varchar(20), --部门
@as_table_name varchar(100), --表名
@as_where varchar(8000), --where条件
@as_info varchar(8000) output --返回信息
AS
Begin

--on/off
Set Nocount Off

--变量申明
Declare @ll_rowcount numeric(10,0) --记录之和
Declare @ll_rowcurr numeric(10,0) --当前记录号
Declare @ll_row_ins numeric(10,0) --新增的数量
Declare @ll_row_del numeric(10,0) --删除的数量
Declare @ll_row_ups numeric(10,0) --更新的数量
Declare @pep_no varchar(30) --料号
Declare @ls_err varchar(1000) --错误信息
DECLARE @result int --返回结果值
DECLARE @ls_cmd varchar(1000) --命令集
DECLARE @ls_filebak varchar(100) --备份文件名称
DECLARE @ls_info varchar(100) --信息

DECLARE @ls_curr varchar(1000) --文件目录
DECLARE @ls_currbak varchar(1000) --文件目录备份
DECLARE @ls_filename varchar(100) --文件名称
DECLARE @ls_where varchar(8000) --where条件

--赋初值
Select @ls_curr='C:\Update\',
@ls_currbak='C:\Update\Bak\',
@ls_filename=@as_dept_no+'_'+@as_table_name+'.txt',
@ls_where=@as_where,
@as_info=''

--
Set @ls_filename=@as_dept_no+'_temp_pep.txt'
Exec pro_ftp_txttosql_temp_pep
@ls_curr,@ls_currbak,@ls_filename,@ls_where,@ls_info output
IF (@@error<>0) Return @@error
Set @as_info=@as_info+@ls_info


--返回信息
If len(@as_info)<1
Begin
Set @as_info='执行错误,无任何文件生成'
End

--on/off
Set Nocount Off

End



/*-----------------------------------------------------------------------------
名称<Script Name>: pro_ftp_txttosql_temp_pep
功能描述<Description>: 数据导入导出
访问方式<Usage>: 公共的
访问参数<param>:
返回值<Return Code>: N/A
设计者<Author>: xiawen
创建日期<Date Create>： 2005/04/05
版本:<Version>: 1.0
变动说明<Revision History>:
-----------------------------------------------------------------------------*/
CREATE proc pro_ftp_txttosql_temp_pep
@as_curr varchar(1000), --路径
@as_currbak varchar(1000), --备份路径
@as_filename varchar(100), --文件名
@as_where varchar(8000), --where条件
@as_info varchar(1000) output --返回信息
AS
Begin

--on/off
Set Nocount Off

--变量申明
Declare @ll_rowcount numeric(10,0) --记录之和
Declare @ll_rowcurr numeric(10,0) --当前记录号
Declare @ll_row_ins numeric(10,0) --新增的数量
Declare @ll_row_del numeric(10,0) --删除的数量
Declare @ll_row_ups numeric(10,0) --更新的数量
Declare @pep_no varchar(30) --料号
Declare @ls_err varchar(1000) --错误信息
DECLARE @result int --返回结果值
DECLARE @ls_cmd varchar(1000) --命令集
DECLARE @ls_filebak varchar(100) --备份文件名称

DECLARE @ls_curr varchar(1000) --文件目录
DECLARE @ls_currbak varchar(1000) --文件目录备份
DECLARE @ls_filename varchar(100) --文件名称


--赋初值
Select @ll_rowcurr=0,@ll_row_ins=0,@ll_row_ups=0,@ll_row_del=0
Select @ls_curr=@as_curr,
@ls_currbak=@as_currbak,
@ls_filename=@as_filename


--是否存在升级的文件
Select @ls_cmd='dir '+@ls_curr+@ls_filename
EXEC @result = master..xp_cmdshell @ls_cmd,no_output
IF (@result = 1)
Begin
Set @as_info=' <'+@ls_filename+'>.--执行失败,+
可能原因是没有找到升级所需要的文件'+@ls_curr+@ls_filename
return -1
End

--创建临时表，结构同要导入的表一样
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].-
[ftp_temp_pep]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ftp_temp_pep]
Select top 1 * Into ftp_temp_pep From temp_pep
if (@@error<>0) Return @@error

--清除临时表数据
Delete From ftp_temp_pep
if (@@error<>0) Return @@error


--导入数据，到所创建的临时表中
Select @ls_cmd=' bcp "txshoes..ftp_temp_pep" in '+@ls_curr+
@ls_filename+' -c -S -U -P'
/=======================================================================/
--该死的引此条语句
Exec master..xp_cmdshell @ls_cmd, No_output
/======================================================================/

--对所导入的数据进行逐条插入到目的表中(按主键)
-- 创建一个有自动增长ID的临时表，用来记录所导入的数量之和
-- 临时表字段:up_type 'U'--有记录只需要更新,'I'--无记录需要新,--
'D'--删除的记录,''--无操作
Create Table #temp_pep(id int IDENTITY(1,1),pep_no varchar(30) not null,
up_type char(1) default '' not null)
Insert Into #temp_pep(pep_no) Select pep_no From ftp_temp_pep
if (@@error<>0) Return @@error

--临时表的记录数
Select @ll_rowcount=count(*) From #temp_pep

--开始进行判断是否有重复
-- 对于料品主档，数据库中有此记录更新(Update)，若无此记录则新增(Insert)\
While @ll_rowcurr<@ll_rowcount
Begin

--ID号增加1
Set @ll_rowcurr=@ll_rowcurr+1

--得到主键值
Select @pep_no=pep_no From #temp_pep Where id=@ll_rowcurr

--若有此记录
If exists(Select * From temp_pep Where pep_no=@pep_no)
Begin
Update #temp_pep Set up_type='U' Where pep_no=@pep_no
if (@@error<>0) Return @@error
Set @ll_row_ups=@ll_row_ups+1
End
--若没有记录
Else
Begin
Update #temp_pep Set up_type='I' Where pep_no=@pep_no
if (@@error<>0) Return @@error
Set @ll_row_ins=@ll_row_ins+1
End

End

--更新数据库
If @ll_rowcurr=0
Begin
Set @as_info=' <'+@ls_filename+'>.--执行失败,没有需要更新的数据'
--return -1
End
Else
Begin
--Insert
Insert Into temp_pep
Select * From ftp_temp_pep
Where pep in (Select pep From #temp_pep Where up_type='I')
if (@@error<>0) Return @@error
--Update
Update temp_pep Set
pep_name=a.pep_name,
pep_s=a.pep_s,
pep_en=a.pep_en
From ftp_temp_pep a
Where temp_pep.pep_no=a.pep_no
and a.pep_no in (Select pep_no From #temp_pep Where up_type='U')
if (@@error<>0) Return @@error

End

--删除临时表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].--
[ftp_temp_pep]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Drop table ftp_temp_pep
Drop table #temp_pep

--备份文件
Select @ls_filebak=Convert(varchar(8),getdate(),112)+'_'+@ls_filename
Select @ls_cmd='Copy '+@ls_curr+@ls_filename+' '+@ls_currbak+@ls_filebak
exec master..xp_cmdshell @ls_cmd ,no_output
if (@@error<>0) Return @@error

--删除文件
Select @ls_cmd='del '+@ls_curr+@ls_filename
--exec master..xp_cmdshell @ls_cmd, No_output
if (@@error<>0) Return @@error

--返回信息
Select @as_info=' <'+@ls_filename+'>.--执行成功,共处理记录('+
cast(@ll_rowcurr as varchar)+')条,
新增('+cast(@ll_row_ins as varchar)+')条,
更新('+cast(@ll_row_ups as varchar)+')条,
删除('+cast(@ll_row_del as varchar)+')条'
Return 1

--on/off
Set Nocount Off

End

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

[xiawen]

有没有搞错呀，
怎么这么乱呀,我明明调得好好的

[lzheng2001]

我明天测试一下

[xiawen]

刚测试了，执行"select top 1 * from temp_pep "没有造成死锁

完成这个导入用了两个存储过程：(1).pro_ftp_txttosql (2).pro_ftp_txttosql_temp_pep
在数据库中不存在pro_txttosql此存储过程，没有其它过程对表进行操作

在(2)这个存储过程中用到了三个表:
1.temp_pep --数据库存在的表,也即是最后要从文件导入数据到这个表中
2.ftp_tmep_pep --在(2)中创建的临时表，结构同temp_pep一致，最后会删除
3.#temp_pep --在(2)中创建的临时表，(包括temp_pep主键字段，自增量字段)

不好意思，主题贴的语句不对，后面贴的是实际运行程序
那条引起死锁的语句的用途是把文件导入到存储过程(2)所创建的临时表(ftp_tmep_pep)中

请问你测试有成功吗？

谢谢

[lzheng2001]

我只是看了一下呀,那么长的代码我没时间测试了.
但肯定的是PB运行bcp是没问题的.
如果数据库有多人连接的,建议你先本机运行一下你的程序.

[xiawen]

在本机也试过，同样会有此现象
主要是一时想不到哪里有破绽

[lzheng2001]

导入文件是否本机的文件? 试试把文件改小一点(只剩下几行),试试看是否文件太大的问题
在执行Exec master..xp_cmdshell 前试试加上一句commit语句,如果是本进程引起的阻塞,应该可以解决.

[lzheng2001]

可以肯定的是PB能够成功执行EXEC master..xp_cmdshell 'bcp ...' 这种命令
至于你的SQL为什么引起死锁,要查一下原因.

关键看这个表temp_pep ,你在运行代码前在PB中执行 select top 1 * from temp_pep 这句sql 看是否有死锁?

你的数据库上是否还有 pro_txttosql 这个存储过程? 且这个过程也对表temp_pep 进行操作?

还有一点奇怪:你帖子的主题写的是EXEC master..xp_cmdshell 'bcp "txpep..temp_pep" in C:\temp_pep.txt -c -S -U -P' 这句引起
后面你的贴的是:
Select @ls_cmd=' bcp "txpep..ftp_temp_pep" in '+@ls_curr+
@ls_filename+' -c -S -U -P'
/=======================================================================/
--该死的引此条语句
Exec master..xp_cmdshell @ls_cmd, No_output

怎么两句不同呀,主题上是针对 txpep..temp_pep 这个表,你后面贴的却是"txpep..ftp_temp_pep" 这个表,你是不是贴错了呀!!!

[xiawen]

好的，有劳啦！！！！

环境：
PB9.02 3570
MsSql2000个人版
DataBaseName: txpep
TableName: temp_pep(字段:pep_no varchar(20) not null 主键,
pep_name varchar(50) null,
pep_s varchar(50) null,
pep_en varchar(50) null)

--------------------------------------------------------------------
(在上面有个地方写错了，数据库名有误,现改正如下:)
--导入数据，到所创建的临时表中
Select @ls_cmd=' bcp "txpep..ftp_temp_pep" in '+@ls_curr+
@ls_filename+' -c -S -U -P'
/=======================================================================/
--该死的引此条语句
Exec master..xp_cmdshell @ls_cmd, No_output
/======================================================================/
----------------------------------------------------------------------

所贴出的存储过程基本上是原样,应该能直接运行,
导出文件的语句我也可以发出来，如果需要的话
谢谢啦

[xiawen]

与文件大小没有关系，当只有一行时，也会有死锁
在Exec master..xp_cmdshell之前的语句已经执行成功(用commit)
我想基本是可以断定是此条语句作梗。。。。。

[xiawen]

高人

[xiawen]

我将文件导入到一个与目的表结构一样的临时表中,即表txt-->ftp_temp_pep
这个表也是存储过程中创建的 (Select top 1 * Into ftp_temp_pep From temp_pep)
当我先创建这个表，再来执行这个导入的存储过程时就不会报错了

[xiawen]

已经搞定了，多谢lzheng2001(１加１＝０)

[lzheng2001]

恭喜! 这么晚还工作呀! 请问怎么搞定的呀?