是否可以编写脚本来轻松部署clr存储过程？-Isitpossibletowriteascripttoeasilydeployclrstoredprocedures?

1  

Well, PowerShell itself won't give you much in the way of extra help. You don't mention which version of SQL, so I'll be a bit generic.

好吧,PowerShell本身不会给你额外的帮助。你没有提到哪个版本的SQL,所以我会有点通用。

Really, all you need to do is have your script file copy the necessary DLLs for your CLR stored procs - PowerShell can copy files like a maniac, so no problem there. Then you need to execute the CREATE ASSEMBLY statement in SQL. PowerShell natively cannot help with this. If you're using SQL 2008, your script can Add-PSSnapin the SQL cmdlet provider, which will give you a cmdlet capable of executing SQL queries - so that gets you your CREATE statement.

真的,你需要做的就是让你的脚本文件为你的CLR存储过程复制必要的DLL - PowerShell可以像疯子一样复制文件,所以没问题。然后,您需要在SQL中执行CREATE ASSEMBLY语句。 PowerShell本身无法解决这个问题。如果您使用的是SQL 2008,那么您的脚本可以在SQL cmdlet提供程序中添加-PSSnap,这将为您提供一个能够执行SQL查询的cmdlet - 这样就可以获得CREATE语句。

If you don't have 2008, you'll have to write a short .NET routine to create a SqlConnection and SqlCommand - not difficult, and entirely possibly in PowerShell.

如果你没有2008,你将不得不编写一个简短的.NET例程来创建一个SqlConnection和SqlCommand - 并不困难,而且完全可能在PowerShell中。

Next, use the same technique to execute your CREATE PROC statement to actually create the stored proc using the aforementioned assembly.

接下来,使用相同的技术执行CREATE PROC语句,以使用上述程序集实际创建存储过程。

Zat help? Pity there's nothing more automated, true. MS hasn't spent a lot of brainpower simplifying complex SQL deployment stuff of this nature, I don't think, and CLR stuff adds a bit of extra spice since you have to make the assembly available.

扎有帮助吗?可惜没有什么比这更自动化了。 MS没有花费大量的智力来简化这种性质的复杂SQL部署,我不认为,并且CLR的东西增加了一点额外的味道,因为你必须使程序集可用。

1  

One can use techniques described at http://msdn.microsoft.com/en-us/library/ms345099.aspx to generate deployment script from SSMS. At least this addresses the tedious part of generating a ton of CREATE PROCEDURE statements.

可以使用http://msdn.microsoft.com/en-us/library/ms345099.aspx中描述的技术从SSMS生成部署脚本。至少这解决了生成大量CREATE PROCEDURE语句的繁琐部分。

0  

The SQL Server Publishing Wizard can script CLR stored procs as text. Looks like it converts the binary source of CLR stored procs as some kind of hex encoded text.

SQL Server发布向导可以将CLR存储过程脚本编写为文本。看起来它将CLR存储过程的二进制源转换为某种十六进制编码文本。