SQLSERVER，不使用BCP，把查询结果输出为txt文本文件的存储过程
由于有些服务器上，数据库由于服务器安全问题，禁用了cmd_shell 不能用BCP进行查询结果文本化输出。
所以特写了个存储过程。用于生成文本文件
SET
QUOTED_IDENTIFIER
ON
GO
SET
ANSI_NULLS
ON
GO
ALTER
PROCEDURE
stp_ExportDataToTxt
/*
***********************************************************************
* Powered by Tang Ren (R) *
* 2007-7-11 *
***********************************************************************
*/
@sqlstr
nvarchar
(
4000
),
--
查询语句
@path
nvarchar
(
1000
),
--
文件保存文件夹
@fname
nvarchar
(
250
)
--
文件保存名字
as
--
Define variable
declare
@strPath
varchar
(
300
)
--
文件保存的地址
declare
@colsCount
int
declare
@hr
int
DECLARE
@object
int
DECLARE
@src
varchar
(
255
),
@desc
varchar
(
255
)
Declare
@file
int
declare
@sql
varchar
(
1000
)
declare
@tbname
sysname
--
Initialize variable
set
@tbname
=
'
tb_
'
+
convert
(
varchar
(
40
),
newid
())
set
@sql
=
replace
(
@sqlstr
,
'
from
'
,
'
into [
'
+
@tbname
+
'
] from
'
)
set
@strPath
=
''
print
@sql
exec
(
@sql
)
--
Estimate the dir suffix, if it not end in '' then add it.
if
right
(
@path
,
1
)
<>
'
'
set
@path
=
@path
+
'
'
set
@strPath
=
@path
+
@fname
print
@strPath
--
Create FSO Object for file operation.
EXEC
@hr
=
sp_OACreate
'
Scripting.FileSystemObject
'
,
@object
OUT
IF
@hr
<>
0
BEGIN
EXEC
sp_OAGetErrorInfo
@object
,
@src
OUT,
@desc
OUT
SELECT
hr
=
convert
(
varbinary
(
4
),
@hr
), Source
=
@src
, Description
=
@desc
RETURN
END
--
Create File ,if file exist then override it.
EXEC
@hr
=
sp_OAMethod
@object
,
'
CreateTextFile
'
,
@file
OUTPUT ,
@strPath
IF
@hr
<>
0
BEGIN
EXEC
sp_OAGetErrorInfo
@object
RETURN
END
set
@sql
=
'
select * from syscolumns where id=object_id(
'''
+
@tbname
+
'''
)
'
print
object_id
(
@tbname
)
--
Create initial field name with each columns
declare
@name
varchar
(
1000
)
declare
@flag
int
set
@name
=
''
set
@flag
=
0
declare
cur_data
CURSOR
for
select
name
from
syscolumns
where
id
=
object_id
(
@tbname
)
open
cur_data
fetch
next
from
cur_data
into
@name
while
@@fetch_status
=
0
begin
if
@flag
=
1
exec
sp_OAMethod
@file
,
'
Write
'
,
NULL
,
'
,
'
exec
sp_OAMethod
@file
,
'
Write
'
,
NULL
,
@name
set
@flag
=
1
fetch
next
from
cur_data
into
@name
end
close
cur_data
deallocate
cur_data
EXEC
@hr
=
sp_OAMethod
@file
,
'
Close
'
,
NULL
IF
@hr
<>
0
BEGIN
EXEC
sp_OAGetErrorInfo
@object
RETURN
END
set
@sql
=
'
insert into openrowset(
''
microsoft.jet.oledb.4.0
''
,
''
text;hdr=no;database=
'
+
@path
+
'''
,
''
select * from[
'
+
@fname
+
'
]
''
)
'
+
@sqlstr
print
@sql
exec
(
@sql
)
set
@sql
=
'
drop table [
'
+
@tbname
+
'
]
'
print
@sql
exec
(
@sql
)
GO
SET
QUOTED_IDENTIFIER
OFF
GO
SET
ANSI_NULLS
ON
GO
