<script type="text/javascript">
</script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
SQLServer的 脚本生成有不少漏洞,经常由_art_center>它生成的脚本运行起来会有错误。下面举例说明:
1.并没有根据sysdenpends的依赖关系生成 SQL代码,而是根据所谓的“优先级”来生成。
比如:他认为view的优先级就要比function高。
那么,我写了下面的测试程序,形成如下的依赖关系:fnT1<--vwT1<--fnT2
就是,viewvwT1处于依赖的中间。
_NOBR>_CODE>createfunctionfnT1()
returnsInteger
as
begin
return123
end
go
createviewvwT1
as
selectaa=dbo.fnT1()
go
createfunctionfnT2()
returnstable
as
return(select*fromvwT1)
go_CODE>
_NOBR>
运行到之后,用Enterprise生成 SQL代码。要注意选项不一样,生成的代码会有所不同,在这里我没有选数据库和用户。
_NOBR>_CODE>ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[fnT1]')andxtypein(N'FN',N'IF',N'TF'))
dropfunction[dbo].[fnT1]
GO
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[fnT2]')andxtypein(N'FN',N'IF',N'TF'))
dropfunction[dbo].[fnT2]
GO
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[vwT1]')andOBJECTPROPERTY(id,N'IsView')=1)
dropview[dbo].[vwT1]
GO
SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO
CreateviewvwT1
As
Selectaa=dbo.fnT1()
GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO
CreatefunctionfnT1()
ReturnsInteger
As
begin
Return123
end
GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO
CreatefunctionfnT2()
ReturnsTable
As
Return(Select*FromvwT1)
GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO_CODE>
_NOBR>
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
微软的
SQL
脚本生成令我伤透了心——我一直以为是我的程序上的问题,或者我操作上的失误,并且,客服人员屡屡埋怨我的程序
BUG,多次测试之后,才发现,原来都是微软惹的祸……
SQLServer的 脚本生成有不少漏洞,经常由_art_center>它生成的脚本运行起来会有错误。下面举例说明:
1.并没有根据sysdenpends的依赖关系生成 SQL代码,而是根据所谓的“优先级”来生成。
比如:他认为view的优先级就要比function高。
那么,我写了下面的测试程序,形成如下的依赖关系:fnT1<--vwT1<--fnT2
就是,viewvwT1处于依赖的中间。
_NOBR>_CODE>createfunctionfnT1()
returnsInteger
as
begin
return123
end
go
createviewvwT1
as
selectaa=dbo.fnT1()
go
createfunctionfnT2()
returnstable
as
return(select*fromvwT1)
go_CODE>
_NOBR>
运行到之后,用Enterprise生成 SQL代码。要注意选项不一样,生成的代码会有所不同,在这里我没有选数据库和用户。
_NOBR>_CODE>ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[fnT1]')andxtypein(N'FN',N'IF',N'TF'))
dropfunction[dbo].[fnT1]
GO
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[fnT2]')andxtypein(N'FN',N'IF',N'TF'))
dropfunction[dbo].[fnT2]
GO
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[vwT1]')andOBJECTPROPERTY(id,N'IsView')=1)
dropview[dbo].[vwT1]
GO
SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO
CreateviewvwT1
As
Selectaa=dbo.fnT1()
GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO
CreatefunctionfnT1()
ReturnsInteger
As
begin
Return123
end
GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
SETANSI_NULLSON
GO
CreatefunctionfnT2()
ReturnsTable
As
Return(Select*FromvwT1)
GO
SETQUOTED_IDENTIFIEROFF
GO
SETANSI_NULLSON
GO_CODE>
_NOBR>
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>