reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go
alter PROC p_exporttb22 --
@tbname sysname , --要导出的表名
@path NVARCHAR(1000) , --文件存放目录--绝对地址要是服务器上的位置,并且要避开根目录,数据保存在服务器上。
@fname NVARCHAR(250) = '' , --文件名,默认为表名
@over BIT = 0 --是否覆盖已经存在的文件,如果不覆盖,则直接追加
AS
DECLARE @err INT ,
@src NVARCHAR(255) ,
@desc NVARCHAR(255) ,
@out INT
DECLARE @obj INT ,
@constr NVARCHAR(1000) ,
@sql VARCHAR(8000) ,
@fdlist VARCHAR(8000),
@fdlistUpper VARCHAR(8000) --insert dbf文件时 字段要大写,故提前准备
--参数检测
IF ISNULL(@fname, '') = ''
SET @fname = @tbname + '.dbf'
--检查文件是否已经存在
IF RIGHT(@path, 1) <> '/'
SET @path = @path + '/'
CREATE TABLE #tb ( a BIT, b BIT, c BIT )
SET @sql = @path + @fname
INSERT INTO #tb
EXEC master..xp_fileexist @sql
IF EXISTS ( SELECT 1
FROM #tb
WHERE a = 1 )
IF @over = 1
BEGIN
SET @sql = 'del ' + @sql
EXEC master..xp_cmdshell @sql, no_output
END
ELSE
SET @over = 0
ELSE
SET @over = 1
--数据库创建语句
SET @sql = @path + @fname
SET @constr = 'Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties="dBASE 5.0;'
--如果不是64位系统,就不能用“Microsoft.ACE.OLEDB.12.0”,改为"Microsoft.Jet.OLEDB.4.0",未测试32的情况,
--下同。具体细节可以参考:http://www.connectionstrings.com/dbf-foxpro/
+ ';HDR=NO;DATABASE=' + @path + '"'
--连接数据库
EXEC @err= sp_OACreate 'adodb.connection', @obj OUT --sp_OACreate
IF @err <> 0
GOTO lberr
EXEC @err= sp_OAMethod @obj, 'open', NULL, @constr
IF @err <> 0
GOTO lberr
--创建表的SQL
SELECT @sql = '' ,
@fdlist = ''
SELECT @fdlist = @fdlist + ',' + a.name ,
@sql = @sql + ',[' + a.name + '] '
+ CASE WHEN b.name IN ( 'char', 'nchar', 'varchar', 'nvarchar' )
THEN 'text(' + CAST(CASE WHEN a.length > 250 THEN 250
ELSE a.length
END AS VARCHAR) + ')'
WHEN b.name IN ( 'tynyint', 'int', 'bigint', 'tinyint' )
THEN 'int'
WHEN b.name IN ( 'smalldatetime', 'datetime' )
THEN 'datetime'
WHEN b.name IN ( 'money', 'smallmoney' ) THEN 'money' WHEN b.name IN ( 'image' ) THEN 'GENERAL'
ELSE b.name
END
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = b.xusertype
WHERE b.name NOT IN ( --'image',
'text', 'uniqueidentifier', 'sql_variant',
'ntext', 'varbinary', 'binary', 'timestamp' )
AND OBJECT_ID(@tbname) = id
SELECT @sql = 'create table [' + @fname + '](' + SUBSTRING(@sql, 2, 8000)
+ ')' ,
@fdlist = SUBSTRING(@fdlist, 2, 8000)
IF @over = 1
BEGIN
EXEC @err= sp_OAMethod @obj, 'execute', @out OUT, @sql
IF @err <> 0
GOTO lberr
END
EXEC @err= sp_OADestroy @obj
SET @sql = 'openrowset(''Microsoft.ACE.OLEDB.12.0'',''dBase 5.0;DATABASE='
+ @path + ''',''select * from [' + @fname + ']'')'
SET @fdlistUpper=UPPER(@fdlist) --insert dbf文件时 字段要大写,故提前准备
--导入数据
--EXEC('insert into '+@sql+'('+@fdlist+' ) select '+@fdlist+' from '+@tbname )
EXEC('insert into '+@sql+'('+@fdlistUpper+' ) select '+@fdlist+' from '+@tbname )
RETURN
lberr:
EXEC sp_OAGetErrorInfo 0, @src OUT, @desc OUT
lbexit:
SELECT CAST(@err AS VARBINARY(4)) AS 错误号 ,
@src AS 错误源 ,
@desc AS 错误描述
SELECT @sql ,
@constr ,
@fdlist
GO