CLR Stored procedure without visual studio?

Question

Post reply

CLR Stored procedure without visual studio?

brijbmishra

SSC Enthusiast

Points: 185
More actions
November 28, 2008 at 11:45 pm

#383483

Can we develop a CLR based Stored Procedure without using Visual Studio?

Viewing 15 posts - 1 through 15 (of 19 total)

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

Tom257 SSChampion Points: 11543 More actions · Answer 1

Yes, but you will still need the .NET command line compiler to create an assembly dll from your source.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You could write the code in notepad and compile with the command-line C# or VB compilers (which are included with the .net framework). It's a lot of work though.

Does anyone know if Visual Studio Express allows database projects?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

What is it that you think you need a CLR for that can't be done in T-SQL? In other words, what is the CLR that you want to write supposed to do?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

GilaMonster (11/29/2008)
Does anyone know if Visual Studio Express allows database projects?

Gail,

The Visual Studio Express Editions don't include database projects in them, at least the last time I checked them out they didn't, but you can still develop assemblies in the express editions of Visual Studio that can be used in SQL Server if you manually write the CREATE ASSEMBLY DLL and CREATE FUNCTION/PROCEDURE/TRIGGER/AGGREGATE DDL.

You have to be very careful when creating CLR objects for use in SQL Server when you aren't using a Visual Studio database project. The project forces you to stick to approved assemblies for the most part, whereas, a standard library project will let you develop objects that use classes not allowed for use in SQLCLR.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

brijbmishra (11/28/2008)
Can we develop a CLR based Stored Procedure without using Visual Studio?

What do you intend the CLR to do?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Jonathan Kehayias (11/29/2008)
GilaMonster (11/29/2008)
Does anyone know if Visual Studio Express allows database projects?
Gail,
The Visual Studio Express Editions don't include database projects in them, at least the last time I checked them out they didn't, but you can still develop assemblies in the express editions of Visual Studio that can be used in SQL Server if you manually write the CREATE ASSEMBLY DLL and CREATE FUNCTION/PROCEDURE/TRIGGER/AGGREGATE DDL.
You have to be very careful when creating CLR objects for use in SQL Server when you aren't using a Visual Studio database project. The project forces you to stick to approved assemblies for the most part, whereas, a standard library project will let you develop objects that use classes not allowed for use in SQLCLR.

Thanks. Good to know.

I assume the list of approved assemblies exists somewhere? Books Online? MSDN?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

GilaMonster (11/29/2008)
You could write the code in notepad and compile with the command-line C# or VB compilers (which are included with the .net framework). It's a lot of work though.
Does anyone know if Visual Studio Express allows database projects?

As far as I know - No. You need pro or better.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Great information, one and all... but I still want to know what the CLR(s) the op wants to write do. We all know that too many people give up on T-SQL way too early and that most CLR solutions are easily beat for performance and scalability by some thoughtful set-based T-SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

GilaMonster (11/30/2008)
I assume the list of approved assemblies exists somewhere? Books Online? MSDN?

Gail,

The approved assembly list is in the Books Online at:

http://msdn.microsoft.com/en-us/library/ms403279.aspx

There are also some coding model restrictions that you have to pay atrtention to if you use the express editions to ensure that you don't code yourself into an unusable assembly, though not as likely:

http://msdn.microsoft.com/en-us/library/ms403273.aspx

You can load non-approved assemblies, but to do so requires that you first set the database trustworthy bit to on, and then grant unsafe access assembly rights to the owner, at which point you need to look at what you are doing and consider that it doesn't belong in SQL Server at all, but probably a middle tier, or scaled out Biztalk implementation.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Jeff Moden (11/30/2008)
I still want to know what the CLR(s) the op wants to write do. We all know that too many people give up on T-SQL way too early and that most CLR solutions are easily beat for performance and scalability by some thoughtful set-based T-SQL.

The OP will probably be back next week. Remember it is a weekend, and not everyone posts to work-related forums over the weekend.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Jonathan Kehayias (11/30/2008)
The approved assembly list is in the Books Online at:
http://msdn.microsoft.com/en-us/library/ms403279.aspx

Thanks. I'm more asking out of curiosity. I don't tend to write CLR.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

GilaMonster (11/30/2008)
Jeff Moden (11/30/2008)
I still want to know what the CLR(s) the op wants to write do. We all know that too many people give up on T-SQL way too early and that most CLR solutions are easily beat for performance and scalability by some thoughtful set-based T-SQL.
The OP will probably be back next week. Remember it is a weekend, and not everyone posts to work-related forums over the weekend.

Heh... yep... I know that... just wanted to try to keep up with all the folks posting ways to do CLR's. Want the op to know I'm serious.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

GilaMonster (11/30/2008)
Jonathan Kehayias (11/30/2008)
The approved assembly list is in the Books Online at:
http://msdn.microsoft.com/en-us/library/ms403279.aspx
Thanks. I'm more asking out of curiosity. I don't tend to write CLR.

I have yet to write a single thing in SQL CLR that has made it into a production server of mine, and I have written alot of CLR code trying to figure out problems I see on the various forums. Most of it is misplaced, but there are some areas that it actually makes sense to put code in CLR, they just don't generally fit in most common database development.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

Jonathan Kehayias (11/30/2008)
Most of it is misplaced, but there are some areas that it actually makes sense to put code in CLR, they just don't generally fit in most common database development.

What area's, Jonathan? That's not a challenge... just looking to get smarter. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)