开发者

Intellisense and context help for sys stored procedures - bugs or by design?

SQL Server 2008 R2 Dev

Execution in SSMS of:

1)

use AdventureWorksDW;

GO;

sp_cdc_enable_table

'dbo', 'FactInternetSales',

@role_name=NULL,

@supports_net_changes=0

succeeds.

Why does execution of

2)

sp_cdc_enable_table 'dbo', 'FactInternetSales' --, @role_name=NULL, @supports_net_changes=0

gives error:

Msg 201, Level 16, State 4, Procedure sp_cdc开发者_如何转开发_enable_table, Line 0 Procedure or function 'sp_cdc_enable_table' expects parameter '@role_name', which was not supplied.

while Intellisense popup on (having mouse cursor on) sp_cdc_enable_table in SSMS shows:

stored procedure AdventureWorksDW.sys.sp_cdc_enable_table

@source_schema sysname,

@source_name sysname,

@capture_instance sysname = null,

@supports_net_changes bit = null,

@role_name sysname,

@index_name sysname =null,

[continuation truncated by vgv8]

Intellisense and context help for sys stored procedures - bugs or by design?

Does not " = null" imply default value which is used if parameter is omitted?

Why does not ommission of @index_name sysname give the error?

Collateral questions:

How can I copy the text of popup description into buffer (for further pasting)?

Is not "@role_name bit = null" in popup an error (and should have been "@role_name bit = 0, " instead)?

Update:

I corrected the typo in post.

Really I executed

sp_cdc_enable_table 'dbo', 'FactInternetSales'--, @role_name=NULL, @supports_net_changes=0

i.e. both variants are execution of the same script

1) uncommented

2) with comments

Update3:

OK, initially I typed incorrectly the text of popup which I corrected now. So, the question about error is removed, thanks.

From the context of my successful execution of 1) you are supposed to notice that my questions are about popup (but not how to succeed):

  • whether the popup is (in)correct?
    • "@capture_instance sysname = null, should not it be enclosed in brackets [ ] showing that it can be ommitted?
    • "@supports_net_changes bit = null," - should not it be "= 0"?
  • how to copy popup (for example, for reporting a bug in Microsoft Connect)?

Notice that popup also has "@capture_instance = null," which can be completely ommitted but it is not marked this in any way.

Update4:

I included the screenshot.

Well, it is rather context sensitive help.

Intellisense does not work at all on sys.sp_* and popup is not shown if there is a syntax error - whatever MS pretended to accomplish by such "help" since it is necessary to insert full correct statement in order to escape syntax errors and have context sensitive "help"...

Update5:

Then, what is the sense that parameters with default values cannot be dropped?


You are missing a '

sp_cdc_enable_table 'dbo', FactInternetSales' 
                          ^ here

Change it to:

sp_cdc_enable_table 'dbo', 'FactInternetSales' 

And it should fix your problem.


According to the MSDN documentation here, @role_name must be specified.

[ @role_name = ] 'role_name'

Is the name of the database role used to gate access to change data. role_name is sysname and must be specified. If explicitly set to NULL, no gating role is used to limit access to the change data.

If the role currently exists, it is used. If the role does not exist, an attempt is made to create a database role with the specified name. The role name is trimmed of white space at the right of the string before attempting to create the role. If the caller is not authorized to create a role within the database, the stored procedure operation fails.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜