Adding a .NET Reference to a Database Project

Question

Adding a .NET Reference to a Database Project

planetmatt

SSCrazy

Points: 2207
More actions
July 16, 2008 at 9:09 am

#210008

I have a Forms app which uses these .net references:
Microsoft.SqlServer.Management.Smo
Microsoft.SqlServer.Management.Common
I am trying to convert it to a CLR SP but after creating a new Database Project in VS2005, when I go to Add Reference, the .NET tab is not visible to select these references.
Is there any way to include these references in a Database Project? If not, how can I access the MSDB jobs and script them out using another method?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

You can not use SMO inside of SQLCLR. Seems counter intuitive, but it has to do with SMO not supporting partially trusted callers and using special type of connection result. Bobb Beauchemin blogs about this here:

http://www.sqlskills.com/blogs/bobb/2007/03/13/TwoThingsYouCantDoInSQLCLR.aspx

planetmatt SSCrazy Points: 2207 More actions · Answer 2

Thanks for the clarification. So is there any other way to properly script out an MSDB job to a text .sql file? I don't want to have to read though the sysjobs table. I basically want to progmatically reproduce the behavior of the right click, script object feature in management studio for multiple jobs.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

I guess to me and probably most people the question back to you would be why do you want to do this from TSQL? This isn't a task that would generally be done internal to SQL Server.

planetmatt SSCrazy Points: 2207 More actions · Answer 4

What I have done in my .net app is script out sql jobs from a SQL2000 server. The code changes all calls to ISQL to SQLCommand, change calls to CDOSysmail to DatabaseMail, and does a few other changes like owner and email operator changes. The code then imports the job onto a SQL2005 server.

I wanted it integrated into SQL server so I could have a proc that I could pass a job name to as a parameter or include in a batch query and my job would be migrated. I wanted to use TSQL to control which jobs get migrated.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

You can do this from TSQL by building your app as a Console Application that accepts CommandLine arguments, and then calling that app from xp_cmdshell inside a stored procedure with the needed arguements. Of course this isn't a recommended practice, but you could do it that way, and have control over it from TSQL.