?SQLSERVER，不使用BCP，把查詢結(jié)果輸出為txt文本文件的存儲過程
由于有些服務(wù)器上，數(shù)據(jù)庫由于服務(wù)器安全問題，禁用了cmd_shell 不能用BCP進(jìn)行查詢結(jié)果文本化輸出。
所以特寫了個存儲過程。用于生成文本文件
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
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
