SS100: Create Scripts creates invalid default values for CLR based Stored Procedures - by BetterToday

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


1
0
Sign in
to vote
ID 646499 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 2/23/2011 1:16:51 PM
Access Restriction Public

Description

I've been creating a database SQL script from a SQL Server 2005 database using SQL Server Management Studio 2008.

The database to create the SQL script from uses CLR assemblies.

The created SQL script, however, doesn't correctly provide default values for Stored Procedures calling CLR functions.

Here's an example of a Stored Procedure that has been generated by SQL Server Management Studio 2008:

CREATE PROCEDURE [dbo].[ProcName]
	@Recipients [nvarchar](4000),
	@Sender [nvarchar](4000) = someuser@domain.com,
	@Att_File [nvarchar](4000),
	@Use_Jom [bit] = True,
	@Use_Mime [bit] = False,
	@Use_HTML [bit] = False,
	@Smtp [nvarchar](4000) = mail.domain.com,
	@SmtpPort [int] = 25,
	@SendEncrypted [bit] = False,
WITH EXECUTE AS OWNER
AS
EXTERNAL NAME [Assembly].[Namespace].[Function]
GO


As you can see, invalid default values are generated.

Regards,
Axel Dahmen
www.axeldahmen.de
Sign in to post a comment.
Posted by Microsoft on 3/17/2011 at 11:57 PM
Thanks Axel for confirming that trailing comma isn't a bug in the product. Glad to hear that.
We fix bugs that are found in internal testing apart from the bugs that are raised on connect, that is why you might not have found similar issue on connect.
Please keep the feedback flowing and we will try to address as much as we can in upcoming releases.

Thanks,
Sravanthi
Posted by BetterToday on 3/17/2011 at 5:50 AM
... below it should read "trailing" not "training" ... ;)
Posted by BetterToday on 3/17/2011 at 5:49 AM
Hi Sravanthi,

thanks for replying and for providing the link to the CTP providing a fix for the problem.

I couldn't find a Connect issue dealing with this problem so I created this one, assuming I'm the first to recognize this issue.

Eagle eyes you have! The training comma is indeed my mistake. A left-over from deleting some more training parameters from the original script.

I'm now looking forward to the fix...

Best regards,
Axel Dahmen
www.axeldahmen.de
Posted by Microsoft on 3/16/2011 at 7:27 AM
Hi Axel Dahmen,

Thanks for reporting this issue to Microsoft. I am able to reproduce this issue using SQL Server Management Studio 2008 / R2. We have already fixed this issue of incorrect default values in the next release of SQL Server, you can download CTP of this from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9&displaylang=en

However, I see an extra comma in the script you gave after last parameter, could you please check if that is a typo during your edits or it is another issue with the script generated by SSMS? I am not able to reproduce that problem though in any release of SSMS, hence checking.

Thanks,
Sravanthi