动态SQL的诅咒 [英] The Curse of Dynamic SQL
问题描述
任务：
创建100个左右类似于约定的存储过程脚本
自动从EM生成脚本。我基本上想到了两个使用sp_helptext获取sp内容的a / b
;和b）使用
bcp将此类内容写入（动态）文件。什么问题我真的是
动态sql的诅咒。
光标内的进程：
------ ------------------
exec master..xp_cmdshell''bcp" exec sp_helptext''+ @ spName +''" queryout
''+@spName+''.txt -c -SmyServerName -Usa -PmyPwd''
错误= [Microsoft] [ODBC SQL Server驱动程序]功能序列错误
TIA
Task:
Create 100 or so stored procedure scripts similar to the convention of
Generating Script from EM automatically. I thought of essentially two
things of a) using sp_helptext to get the content of a sp; and b) using
bcp to write such content to a (dynamic) file. What bugs me is really
the curse of dynamic sql.
process inside a cursor:
------------------------
exec master..xp_cmdshell ''bcp "exec sp_helptext ''+@spName+''" queryout
''+@spName+''.txt -c -SmyServerName -Usa -PmyPwd''
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error
TIA
推荐答案
这比帮助你做的更多方式。
http：// www。 sqlservercentral.com/colu...tionscript.asp
This is going to help you MUCH more than doing it that way.
http://www.sqlservercentral.com/colu...tionscript.asp
虽然知道这样一个实用工具很好但它不能为
有问题的任务的目的。我提到了每个单独的sp
，因为其中一些（不是全部）可能会在几个类似的
数据库之间共享。
While it is good to know such a utility tool it does not serve the
purpose of the task in question. I mentioned about each seperate sp
because some of them (not all) might be shared among several similar
databases.
这跟我能得到的差不多：
声明@spName varchar（256）
声明@sql varchar（1024）
声明my_cur游标
从sysobjects中选择名称type =''P''
打开my_cur
从my_cur获取下一个到@spName
而@@ fetch_status = 0
begin
set @sql =''osql -SServerName - UUsername -PPassword -n -h
-dDatabaseName -Q"在声明@tbl_temp表上设置nocount（textvalue
varchar（512））插入@tbl_temp exec sp_helptext' '+ @spName +''从@ tbl_temp中选择
rtrim（textvalue）" -o C：\''+ @spName +''。txt''
print @sql
exec master..xp_cmdshell @sql
从my_cur获取下一个到@spName
结束
关闭my_cur
deallocate my_cur
This is about as close as I can get you:
declare @spName varchar(256)
declare @sql varchar(1024)
declare my_cur cursor for
select name from sysobjects where type = ''P''
open my_cur
fetch next from my_cur into @spName
while @@fetch_status = 0
begin
set @sql = ''osql -SServerName -UUsername -PPassword -n -h
-dDatabaseName -Q "set nocount on declare @tbl_temp table(textvalue
varchar(512)) insert into @tbl_temp exec sp_helptext ''+@spName+'' select
rtrim(textvalue) from @tbl_temp" -o C:\''+@spName+''.txt''
print @sql
exec master..xp_cmdshell @sql
fetch next from my_cur into @spName
end
close my_cur
deallocate my_cur
这篇关于动态SQL的诅咒的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
