How can I use SMO within a CLR stored procedure

Hi,

I have a CLR stored procedure that I wish to use SMO.

However this caused a number of problems :

1. Add reference didnt show SMO. So I tracked down a redistributable i.e. SQL Server 2005 Feature pack to install - still didnt show up. However, in a normal .net project they do show up so I erhm hacked the SQL server database project file to manually add the references.

Great have the reference, added the include statements - intellisense working code away - even builds :)

using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Smo;

2. Tried to deploy and came up with the error :

Error 1 Assembly 'microsoft.sqlserver.connectioninfo, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91.' was not found in the SQL catalog.

:(

Shot down in flames..

Would very much appreciate if someone could point me in the right direction to utilising SMO or point out where I am going wrong - other than dont hack the project file :)

Note the reason why I wish to use SMO is so I can read sql files from disk and execute them in the database i.e. create stored procedures etc.

The standard mechanism I have found i.e. a server connection and using ExectueNonQuery fails as the script files use batch seperators in the text i.e. 'GO'.

I have also tried using xp_cmdshell but any error reporting is virtually non existant other than parsing the output which will be truly horrific.

Open to suggestions :)SMO is not supported inside SQL Server. Normally what you can do in a scenario when a system dll (like SMO) isn't supported, is to manually catalog the dll in the database (that way it would show up in th VS SQL Server project when you try to reference it) and be able to use it.

In the case of SMO however it won't work period.

Niels|||Ah, thanks Niels.|||

Hi,

(warning: CLR newbie)

I am trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."

So how do you "manually catalog the dll in the database" so that you can successfully deploy? Are you refering to the "CREATE ASSESSMBLY" command?

Any help is greatly appreciated.

Rocco

|||

Rocco Mastrangelo wrote:

Hi,

(warning: CLR newbie)

I am trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."

So how do you "manually catalog the dll in the database" so that you can successfully deploy? Are you refering to the "CREATE ASSESSMBLY" command?

Yes, you have to run CREATE ASSEMBLY. Be aware that you probably have to create the assembly with a permission set of UNSAFE. This in turn requires certain permissions/settings in the database. See this thread for more info.

Niels