| Email: chair3@sina.com I am so frustrated by the generation of Microsoft SQL scripts ...... I always thought it was a problem with my program or a mistake in my operations,
In addition, the customer service staff repeatedly complained about my program bug. After multiple tests, it turned out that Microsoft had caused the fault ...... SQL server script generation has many vulnerabilities. The scripts generated by SQL Server often run incorrectly. The following is an example: 1. SQL code is not generated based on the dependency of sysdenpends, but based on the "Priority" (Ha, the so-called priority.
For example, he thinks that the view has a higher priority than the function.
Then, I wrote the following test program to form the following dependency: fnt1 <-- vwt1 <-- fnt2
That is, view vwt1 is in the middle of the dependency.
------------------------------------
Create Function fnt1 ()
Returns integer
As
Begin
Return 123
End
Go Create view vwt1
As
Select AA = DBO. fnt1 () Go Create Function fnt2 ()
Returns table
As
Return (select * From vwt1)
Go
-------------------------------------
After running to the database, use enterprise to generate SQL code. (The options are different. I did not select a database or a user)
Bytes -----------------------------------------------------------------------------------
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [fnt1] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [DBO]. [fnt1]
Go If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [fnt2] ') and xtype in (n'fn', n'if', n'tf '))
Drop function [DBO]. [fnt2]
Go If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [vwt1] ') and objectproperty (ID, n'isview') = 1)
Drop view [DBO]. [vwt1]
Go Set quoted_identifier on
Go
Set ansi_nulls on
Go Create view vwt1
As
Select AA = DBO. fnt1 () Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go Set quoted_identifier on
Go
Set ansi_nulls on
Go Create Function fnt1 ()
Returns integer
As
Begin
Return 123
End Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go Set quoted_identifier on
Go
Set ansi_nulls on
Go Create Function fnt2 ()
Returns table
As
Return (select * From vwt1) Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go
-----------------------------------------------------------------------------
You can see at a glance that creating a view is better than creating a function. Instead of establishing a dependency ......
Undoubtedly, the following error will be returned: (this error is really serious! It hurts me ......)
---------------------------------------------------
Server: Message 208, level 16, status 1, process vwt1, line 4
The object name 'dbo. fnt1' is invalid.
Server: Message 208, level 16, status 1, process fnt2, Row 5
The object name 'vp1' is invalid.
---------------------------------------------------
2. Job script. I will not talk about this. The bug is not very serious, mainly because of the problem of the Chinese "--" annotator. I have not tested the English version, but I guess this bug should not exist.
You can try it. 3. There is another SP problem. I have read the essence of spgetidstr and spanalysestrlist. The relationship is that the latter depends on the former. However, spgetidstr does not call any tables.
Therefore, every time you run a script generated by SQL Server, the following information is always reported ):
------------------------------------------------------------------------
Spgetidstr does not exist and dependency cannot be established in sysdepends. The Stored Procedure spanalysestrlist is still created.
--------------------------------------------------------------------------
No matter whether I manually modify the creation sequence or something, the script generated with it is wrong. Haha, this is a breakthrough for Microsoft!
Here, let's take a look at the third bug and look at my test program below:
(Principle: When the SP does not have a dependency on a table or view or other database objects, the dependency cannot be established when the SP is referenced by another sp)
Dependency: spb1 <--- spa1
------------------------------------------------------------------------
Create proc spb1
As
Return 11 Go Create proc spa1
As
Begin
Declare @ I int
Exec @ I = spb1
Return @ I * 2
End
Go
-------------------------------------------------------------------------
The generated script is:
-------------------------------------------------------------------------
If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [spa1] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [spa1]
Go If exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [spb1] ') and objectproperty (ID, n' isprocedure') = 1)
Drop procedure [DBO]. [spb1]
Go Set quoted_identifier on
Go
Set ansi_nulls on
Go Create proc spa1
As
Begin
Declare @ I int
Exec @ I = spb1
Return @ I * 2
End Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go Set quoted_identifier on
Go
Set ansi_nulls on
Go Create proc spb1
As
Return 11 Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go -------------------------------------------------------------------------
Hey. This time we should understand why I intentionally named spa1 and spb1. If there is no dependency, A is naturally at the top of B. Do you think Microsoft is doing this?
I found that Microsoft engineers are sometimes stupid ...... :), You can see that spa1 is built before spb1, rather than its actual relationship (she didn't write it in sysdepends at all)
Fortunately, this bug is not very big. How to say SP is still set up. If you are interested, try something else, such as SP, FN, SP group, etc. I'm lazy :). |
