CLR vs T-SQL

Hi,

I bought Pro ADO.NET2.0 but could not find a clear description of the optimum use-case for CLR and T-SQL Stored Procedures in general. (although the book is great)

It seems for complex or recursive tasks CLR SP is useful. But would you recommend using CLR SP for basic INSERT, UPDATE, DELETE, SELECT queries?

Thanks,
Bahadir

...very common question these days.

Simple answer lies in the fact that let the each person(in this case CLR/T-SQL engines) do what it knows best!

Say for e.g. if you have lot many string manipulation activities to be accomplished. Now of course, in such a case writing a T-SQL UDF or a SP won't help much in performance gains. On all grounds for such activities, the CLR would be much stronger than the T-SQL engine.

Another example -- consider a scenario where a T-SQL cursor is pretty much evident in your SP logic. Try considering writing a CLR SP instead -- looping etc would be very much optimized when in use with CLR rather than T-SQL.

Does than mean T-SQL is always the 2nd choice ? NO Not at all !!

Please recall that T-SQL engine is primarily a SET basaed operational engine. Therefore as mentioened above, CLR was the best choice for row based operations, while consider fetching multiple rows in a set based patter using various joins or order by ann group by options -- of course, T-SQL is the best choice. Similarly, update of columns hitting several rows -- T-SQL better than CLR SP.

I hope I am able to answer your doubt.

Regd. the books you were ref. to -- try reading Pro SQL Server 2005 (A Press)

and SQL Server Magazine (www.sqlmag.com)

cheers,

Ujwal

|||Thanks, this was the kind of rule-of-thumb answer I was expecting.

Bahadir
|||

well, I don't know how much are you convinced with the answer or do you still have doubts.

anycase, I would like to pass on a pointer to one more thread which I happened to come across very much on similar topic on this very forum try browsing the same as well --Where to use SQLCLR ? Data access is not recommended?

|||

hi bahadir,

i think i discussed those thing in my blogs

kindly check

look for the SQL CLR topic

regards,

joey

|||

Hi Bhadir,

You get some insight from the whitepaper that we published on CLR integrationyou can find it here.

The basic guidelines are that for procedural, computational work, the CLR will outperform T-SQL. For data access, or things that can be written declaratively, or that perform data acces, T-SQL will win. Obviously there's a lot of grey area in this answer, but there's a lot of grey area in practice as well.

Cheers,

-Isaac

|||

Hi there,

check this post http://www.yukonizer.com/dasblogce/PermaLink,guid,59a1fbee-1df3-4f9c-be64-de1c778c198e.aspx

Might help you.

Cheers, -DS

CLR vs T-SQL

Hi,

I bought Pro ADO.NET2.0 but could not find a clear description of the optimum use-case for CLR and T-SQL Stored Procedures in general. (although the book is great)

It seems for complex or recursive tasks CLR SP is useful. But would you recommend using CLR SP for basic INSERT, UPDATE, DELETE, SELECT queries?

Thanks,
Bahadir

...very common question these days.

Simple answer lies in the fact that let the each person(in this case CLR/T-SQL engines) do what it knows best!

Say for e.g. if you have lot many string manipulation activities to be accomplished. Now of course, in such a case writing a T-SQL UDF or a SP won't help much in performance gains. On all grounds for such activities, the CLR would be much stronger than the T-SQL engine.

Another example -- consider a scenario where a T-SQL cursor is pretty much evident in your SP logic. Try considering writing a CLR SP instead -- looping etc would be very much optimized when in use with CLR rather than T-SQL.

Does than mean T-SQL is always the 2nd choice ? NO Not at all !!

Please recall that T-SQL engine is primarily a SET basaed operational engine. Therefore as mentioened above, CLR was the best choice for row based operations, while consider fetching multiple rows in a set based patter using various joins or order by ann group by options -- of course, T-SQL is the best choice. Similarly, update of columns hitting several rows -- T-SQL better than CLR SP.

I hope I am able to answer your doubt.

Regd. the books you were ref. to -- try reading Pro SQL Server 2005 (A Press)

and SQL Server Magazine (www.sqlmag.com)

cheers,

Ujwal

|||Thanks, this was the kind of rule-of-thumb answer I was expecting.

Bahadir|||

well, I don't know how much are you convinced with the answer or do you still have doubts.

anycase, I would like to pass on a pointer to one more thread which I happened to come across very much on similar topic on this very forum try browsing the same as well -- Where to use SQLCLR ? Data access is not recommended?

|||

hi bahadir,

i think i discussed those thing in my blogs

kindly check

look for the SQL CLR topic

regards,

joey

|||

Hi Bhadir,

You get some insight from the whitepaper that we published on CLR integrationyou can find it here.

The basic guidelines are that for procedural, computational work, the CLR will outperform T-SQL. For data access, or things that can be written declaratively, or that perform data acces, T-SQL will win. Obviously there's a lot of grey area in this answer, but there's a lot of grey area in practice as well.

Cheers,

-Isaac

|||

Hi there,

check this post http://www.yukonizer.com/dasblogce/PermaLink,guid,59a1fbee-1df3-4f9c-be64-de1c778c198e.aspx

Might help you.

Cheers, -DS

CLR vs T-SQL

Hi,

I bought Pro ADO.NET2.0 but could not find a clear description of the optimum use-case for CLR and T-SQL Stored Procedures in general. (although the book is great)

It seems for complex or recursive tasks CLR SP is useful. But would you recommend using CLR SP for basic INSERT, UPDATE, DELETE, SELECT queries?

Thanks,
Bahadir

...very common question these days.

Simple answer lies in the fact that let the each person(in this case CLR/T-SQL engines) do what it knows best!

Say for e.g. if you have lot many string manipulation activities to be accomplished. Now of course, in such a case writing a T-SQL UDF or a SP won't help much in performance gains. On all grounds for such activities, the CLR would be much stronger than the T-SQL engine.

Another example -- consider a scenario where a T-SQL cursor is pretty much evident in your SP logic. Try considering writing a CLR SP instead -- looping etc would be very much optimized when in use with CLR rather than T-SQL.

Does than mean T-SQL is always the 2nd choice ? NO Not at all !!

Please recall that T-SQL engine is primarily a SET basaed operational engine. Therefore as mentioened above, CLR was the best choice for row based operations, while consider fetching multiple rows in a set based patter using various joins or order by ann group by options -- of course, T-SQL is the best choice. Similarly, update of columns hitting several rows -- T-SQL better than CLR SP.

I hope I am able to answer your doubt.

Regd. the books you were ref. to -- try reading Pro SQL Server 2005 (A Press)

and SQL Server Magazine (www.sqlmag.com)

cheers,

Ujwal

|||Thanks, this was the kind of rule-of-thumb answer I was expecting.

Bahadir|||

well, I don't know how much are you convinced with the answer or do you still have doubts.

anycase, I would like to pass on a pointer to one more thread which I happened to come across very much on similar topic on this very forum try browsing the same as well -- Where to use SQLCLR ? Data access is not recommended?

|||

hi bahadir,

i think i discussed those thing in my blogs

kindly check

look for the SQL CLR topic

regards,

joey

|||

Hi Bhadir,

You get some insight from the whitepaper that we published on CLR integrationyou can find it here.

The basic guidelines are that for procedural, computational work, the CLR will outperform T-SQL. For data access, or things that can be written declaratively, or that perform data acces, T-SQL will win. Obviously there's a lot of grey area in this answer, but there's a lot of grey area in practice as well.

Cheers,

-Isaac

|||

Hi there,

check this post http://www.yukonizer.com/dasblogce/PermaLink,guid,59a1fbee-1df3-4f9c-be64-de1c778c198e.aspx

Might help you.

Cheers, -DS

CLR UDTs bigger than 8000 bytes

Hi there,
Say I wanted to create a "compound" type, purely to pass as a parameter
to a stored procedure, but one of the components was NTEXT or
NVARCHAR(MAX). Say it was something like:
- Author NVARCHAR(256)
- IP address VARCHAR(16)
- Comment NVARCHAR(MAX)
How would I go about doing this? My code works fine if the UDT package
is less than 8000 bytes, but fails if it's greater than that (which is
understandable since I had to put MaxByteSize = 8000 into the UDT
definition). If the package is too big, I get an
IndexOutOfRangeException in
System.Data.SqlClient.TdsParser.TdsExecuteRPC().
Is the 8000-byte limit a "hard" limit that I can't work around? As I
mentioned, this is purely to pass as a parameter to a stored procedure,
it'll never be used as a column in a table.
Or am I misunderstanding something and I don't even need to make this a
real UDT since it won't ever be stored as a column? Is there a way for
me to pass a regular .NET object to a CLR stored procedure?
(This example is very simplistic, I know - I could just pass three
individual parameters instead of one compound one in this case. The
actual situation is more complicated though.)
Many thanks,
GeoffGeoff (opinionatedg@.gmail.com) writes:
> Say I wanted to create a "compound" type, purely to pass as a parameter
> to a stored procedure, but one of the components was NTEXT or
> NVARCHAR(MAX). Say it was something like:
> - Author NVARCHAR(256)
> - IP address VARCHAR(16)
> - Comment NVARCHAR(MAX)
> How would I go about doing this? My code works fine if the UDT package
> is less than 8000 bytes, but fails if it's greater than that (which is
> understandable since I had to put MaxByteSize = 8000 into the UDT
> definition). If the package is too big, I get an
> IndexOutOfRangeException in
> System.Data.SqlClient.TdsParser.TdsExecuteRPC().
> Is the 8000-byte limit a "hard" limit that I can't work around?
That's right.

> Or am I misunderstanding something and I don't even need to make this a
> real UDT since it won't ever be stored as a column? Is there a way for
> me to pass a regular .NET object to a CLR stored procedure?
Check out the current thread "DataRow in a CLR Stored Procedure". There
are some suggestions on binary serialization.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
thanks for that - at least I know I'm not missing something simple at
the UDT/parameter level.
I've checked out the thread "DataRow in a CLR Stored Procedure" as you
suggested - interesting stuff. It would just mean serializing the
values manually before the call, rather than the serialization
happening as part of the call... The objects themselves wouldn't then
need to be UDTs, so there'd be no limit. Cool.
Many thanks,
Geoff

CLR test script SELECT returns no row data

Hi,

The test.sql scripts I write to test CLR stored procedures run successfully, but when I want to display the resulting data in the database with a simple "SELECT * from Employee"

I get the result as:
Name Address
- -
No rows affected.
(1 row(s) returned)

But not the actual row is displayed whereas I would expect to see something like:

Name Address
- -
John Doe
No rows affected.
(1 row(s) returned)

I have another database project where doing the same thing displays the row information but there doesn't seem to be a lot different between the two.

Why no results in first case?

Thanks,
Bahadir
You maybe still have the transaction open and uncommitted, thats why you don′t see the actual row.

HTH; Jens SUessmeyer.

http://www.sqlserver2005.de

CLR Stored Procedures & VS2005 ASP.NET designer support?

I have successfully created CLR stored procures using c# and SQL Server 2005. Now I want to use the CLR stored procedures with some ASP.NET controls. I can add the CLR stored procedure to the dataset designer and have it add a new table adapter but it does not list the columns in the designer view. This forces me to code the column references manually into the controls (tried: DropDownList, GridView).

I can deal with adding the columns manually for controls using a CLR stored procedure, but I want to make sure I was not missing something, am I?

BTW: TSQL stored procedures work as expected (show columns in the dataset designer)

thanks!

-pieter

Hi,

as metadata can be defined on the fly in a stored procedure by building your rowset one by one and then sending it through the pipe, I don′t really know if there is a way to give the designer a hint via some properties or anything else to sniff into the CLR resultset.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks. Your reply does help.

I just wanted to be sure it was not something I was doing incorrectly. The CLR stored procedures do indeed have a great deal of power available to them.

Let us hope the VS team can find some way of bringing the two closer together in the future.

-pieter

CLR Stored procedure to access OleDb datasource, how?

I tried to write a CLR stored procedure using C# in SQL 2005 to access an
Access
database.
When I use the OleDbConnection class in System.Data, the procedure throws
SecurityException at runtime.
Output as following:
System.Security.SecurityException: Request for the permission of type
'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand,
StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.OleDb.OleDbConnection.PermissionDemand()
at System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection
outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Dbbest.Data.BulkStuff.bulkcopy(String source_oledb_connection_string,
String source_table, String destination_table, Int32 batchSize, Int32
notifyAfter)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.OleDb.OleDbPermission
The Zone of the assembly that failed was:
MyComputerWhen you created the assembly did you set the permission set? If not,
then it is SAFE by default and you will not be able ot get to your
access database. You MAY have to go all the way to UNSAFE to get
there, I have not checked.
CREATE ASSEMBLY SQLCLRTest
FROM 'C:\MyDBApp\SQLCLRTest.dll'
WITH PERMISSION_SET = SAFE
SAFE, EXTERNAL_ACCESS, UNSAFE are the options that you have for this.
Steve

CLR Stored procedure to access Ole datasource, how?

I tried to write a CLR stored procedure using C# in SQL 2005 to access an Access
database.

When I use the OleDbConnection class in System.Data, the procedure throws SecurityException at runtime.
Output as following:

System.Security.SecurityException: Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.OleDb.OleDbConnection.PermissionDemand()
at System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Dbbest.Data.BulkStuff.bulkcopy(String source_oledb_connection_string, String source_table, String destination_table, Int32 batchSize, Int32 notifyAfter)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.OleDb.OleDbPermission
The Zone of the assembly that failed was:
MyComputer

Hello Va1era,

The exception you encountered indicates that the C# assembly containing the stored procedure that you registered has insufficient permissions to create an OleDbConnection object, with respect to the .Net framework Code Access Security (CAS) policies. This probably means that your assembly was registered WITH PERMISSION_SET = SAFE, which is also the default if PERMISSION_SET is unspecified in the CREATE ASSEMBLY DDL statement.

To fix this, you can re-register your assembly WITH PERMISSION_SET = EXTERNAL_ACCESS to gain access to the ADO.Net classes (including OleDbConnection). If you are using Visual Studio, you will have to make this change from SAFE to EXTERNAL_ACCESS in the properties page of your C# database project.

To understand more about the CAS security permission buckets in SQL Server's hosted CLR (SAFE, EXTERNAL_ACCESS and UNSAFE), you can look at this document: http://bordecal.mvps.org/Nicole/SqlClrCas/SqlClrCasSpeculations.htm. Also, here is the MSDN page on CAS and ADO.Net 2.0: http://msdn2.microsoft.com/en-us/library/0x4t63kb.aspx

Hope this helps.

Thanks,
Ravi

CLR Stored Procedure Not Visible

I am trying to do some time series analysis on data that I have. I've read that the best way to do this is to use CLR to write the manipulation code in .Net and then load the assembly into SQL rather than extracting the data, manipulating it and putting it back again.

I'm using crystal reports with visual studio 2003 to do my reporting. The problem I am facing is that the CLR stored procedures I create using SQLExpress are not visible from my SQL explorer built into Visual Studio 2003. I can create stored procedures using standard transact sql and these are visible, but the CLR stored procedures are not.

I've noticed when I browse the stored procedures in my SQL Management Studio that the image of the stored procedure has a padlock shown in the icon, as if they are locked?

Do I have to explicitly enable a security attribute on CLR stored procedures to make them visible?

Any help would be greatly appreciated.

Try posting this question on the CLR forums: .NET Framework inside SQL Server.

Thanks
Laurentiu

|||

Mic,

Did you find an answer to the padlock icon next to the CLR stored procedures.

I am using sql server 2005, and trying to access them in reporting services.

I must be missing something.

I tried setting permissions to no avail.

Thanks

Tim

CLR Stored Procedure Not Visible

I am trying to do some time series analysis on data that I have. I've read that the best way to do this is to use CLR to write the manipulation code in .Net and then load the assembly into SQL rather than extracting the data, manipulating it and putting it back again.

I'm using crystal reports with visual studio 2003 to do my reporting. The problem I am facing is that the CLR stored procedures I create using SQLExpress are not visible from my SQL explorer built into Visual Studio 2003. I can create stored procedures using standard transact sql and these are visible, but the CLR stored procedures are not.

I've noticed when I browse the stored procedures in my SQL Management Studio that the image of the stored procedure has a padlock shown in the icon, as if they are locked?

Do I have to explicitly enable a security attribute on CLR stored procedures to make them visible?

Any help would be greatly appreciated.

Try posting this question on the CLR forums: .NET Framework inside SQL Server.

Thanks
Laurentiu

|||

Mic,

Did you find an answer to the padlock icon next to the CLR stored procedures.

I am using sql server 2005, and trying to access them in reporting services.

I must be missing something.

I tried setting permissions to no avail.

Thanks

Tim

CLR Stored Procedure is timing out

At one seemingly inoccuous step in my CLR stored procedure, execution stops and the query times-out.

I've tried debugging the stored proc by stepping into it from within VS. When I do, I get to the code in question, but then simply get this message:

WARNING: Debugger was accessing T-SQL variables while managed code was not suspended.
Waiting until the access is done to continue T-SQL execution.
Continueing T-SQL execution.

And these messages appear to repeat indefinitely. I'm running SQL Server locally on my machine, but this also happens on out development SQL Server server.

The place in the code it appears to happen is when returning back results from a lower-level CLR stored proc called within the higher-level CLR stored proc -- when piping the result set, I suppose.

I've set MAXDOP to 1. No help.

Anyone know what's going on?

Are the CPU and memory getting taken up more and more along with the message appears to repeat indefinitely?

Here is one thread with the same warning, it may be similar to your case:

http://forums.microsoft.com/technet/showpost.aspx?postid=780559&siteid=17

Suggest to move the thread to the forum .NET Framework inside SQL Server, there you will get rapid and qualified responses.

Thanks for your understanding!

CLR Stored Procedure in Reporting Services

Hi

I have created CLR Stored Procedure and my CLR stored procedures are not appearing in the Stored Procedures drop-down list located on the Data tab within the VS2005 Business Intelligence Development Studio.

After Creating the dll I have registered the dll like that

CREATE ASSEMBLY MY_SP_NAME from 'C:\MY_DLL_PATH\MY_DLL_NAME.dll'
WITH PERMISSION_SET = SAFE


After registering I have deployed the dll :


CREATE PROCEDURE [dbo].[MY_SP_NAME]
@.dbname [nvarchar](4000),
@.varTable [nvarchar](4000),
..............
..............
..............

AS
EXTERNAL NAME [MY_DLL_NAME].[MY_CLASS_NAME].[MY_SP_NAME]
GO
EXEC sys.sp_addextendedproperty @.name=N'AutoDeployed', @.value=N'yes' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFile', @.value=N'EXAMINER_WORKSHEET_F_hmz4_Ex.cs' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFileLine', @.value=10 , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO



The Stored Procedure is deployed successfully to SQL Server 2005, but its not showing in the drop-down list located on the data tab while I am desiging my RDL layout.



All of the non-CLR stored procedures are present. Is there something I need to do to add these CLR stored procedures?

Please please ASAP.

Thanks
Tareqe

Unfortunately, due to an issue with the SQL OLEDB Provider (I think), CLR stored procedures are not returned when the database schema is queried. Instead, on the Data tab change the Command type to Text and use an expression-based query text, e.g.

="EXEC [dbo].[MY_SP_NAME]" & Parameters!dbName.Value & ", " & Parameters!varTable.Value

|||

I had the same issue. To get it to show up in the stored procedure list we created a TSQL Wrapper for the CLR stored Procedure.

wrapper called tsql_my_sp_name looks something like this. then you can pick tsql_my_sp_name from the list in report designer and assign dynamic parameters like any other SP.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

GO

-- =============================================

ALTERPROCEDURE [dbo].[tsql_my_sp_name]

-- Add the parameters for the stored procedure here

@.dbname nvarchar(4000),

@.varTable nvarcar(4000),

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

-- Insert statements for procedure here

EXEC my_sp_name @.dbname, @.varTable

END

CLR Stored Procedure in Reporting Services

Hi

I have created CLR Stored Procedure and my CLR stored procedures are not appearing in the Stored Procedures drop-down list located on the Data tab within the VS2005 Business Intelligence Development Studio.

After Creating the dll I have registered the dll like that

CREATE ASSEMBLY MY_SP_NAME from 'C:\MY_DLL_PATH\MY_DLL_NAME.dll'
WITH PERMISSION_SET = SAFE


After registering I have deployed the dll :


CREATE PROCEDURE [dbo].[MY_SP_NAME]
@.dbname [nvarchar](4000),
@.varTable [nvarchar](4000),
..............
..............
..............

AS
EXTERNAL NAME [MY_DLL_NAME].[MY_CLASS_NAME].[MY_SP_NAME]
GO
EXEC sys.sp_addextendedproperty @.name=N'AutoDeployed', @.value=N'yes' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFile', @.value=N'EXAMINER_WORKSHEET_F_hmz4_Ex.cs' , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO
EXEC sys.sp_addextendedproperty @.name=N'SqlAssemblyFileLine', @.value=10 , @.level0type=N'SCHEMA',@.level0name=N'dbo', @.level1type=N'PROCEDURE',@.level1name=N'MY_SP_NAME'
GO



The Stored Procedure is deployed successfully to SQL Server 2005, but its not showing in the drop-down list located on the data tab while I am desiging my RDL layout.



All of the non-CLR stored procedures are present. Is there something I need to do to add these CLR stored procedures?

Please please ASAP.

Thanks
Tareqe

Unfortunately, due to an issue with the SQL OLEDB Provider (I think), CLR stored procedures are not returned when the database schema is queried. Instead, on the Data tab change the Command type to Text and use an expression-based query text, e.g.

="EXEC [dbo].[MY_SP_NAME]" & Parameters!dbName.Value & ", " & Parameters!varTable.Value

|||

I had the same issue. To get it to show up in the stored procedure list we created a TSQL Wrapper for the CLR stored Procedure.

wrapper called tsql_my_sp_name looks something like this. then you can pick tsql_my_sp_name from the list in report designer and assign dynamic parameters like any other SP.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

GO

-- =============================================

ALTERPROCEDURE [dbo].[tsql_my_sp_name]

-- Add the parameters for the stored procedure here

@.dbname nvarchar(4000),

@.varTable nvarcar(4000),

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

-- Insert statements for procedure here

EXEC my_sp_name @.dbname, @.varTable

END

CLR Stored Procedure Exception Handling

Hi,

1) Is there a way to define a global unhandled exception
handler in managed stored procedures?

Something like we're used to in Winform project:

AppDomain.CurrentDomain.UnhandledException
+= new UnhandledExceptionEventHandler(MyHandler);

(I've tried the latter but get some security exception.)

2) Same question about MS Office CLR unhandled exceptions handler...

3) Also, for WebServices Stored Procedure, is there a way to
customize the SOAP Exception <detail> node?

Thanks
Martin1. This is not possible inside SQL Server. If any exception is not handled, SQL Server catches it and sends an appropriate error message to the user. SQL Server does not allow you to specify your own handler for unhandled exceptions.
2. What is the question here? SQL Server does not install an unhandled exception handler.
3. Through native Web Services in SQL Server 2005, there is no mechanism for the user to control what the SOAP fault contains. The old SQL 2000 ASP.Net Web Services mechanism will continue to allow you to capture the Sql exception in the mid-tier and craft the SOAP fault that goes back to the client.

Thanks,
-Vineet Rao

CLR stored procedure crashing SQL Server

I had the following erroneous code in a SQL Server stored C# procedure:

class P

{

private DateTime? e;

public P(

DateTime? e)

{

this.e= e;

}

public DateTime? E

{

get

{

return E; // correction return e;

}

set

{

E= value; // correction e = value;

}

}

}

Calling the getter of E of the class P creates an infinite number of method calls. This causes the .NET stack overflow. This sometimes caused our SQL crash. Here's the log:

29.3.2007 9:46:08 A fatal error occurred in .NET Framework runtime. The server is shutting down.

29.3.2007 9:46:10 Microsoft SQL Server 2005 - 9.00.2153.00 (X64)

May 9 2006 13:58:37

Copyright (c) 1988-2005 Microsoft Corporation

In my opinion an error in SQL server CLR stored procedure must not be able to crash the whole SQL Server, as it seems to do. Could someone verify this?

What makes finding problems like this problematic is that the only error is like ".NET stack overflow". No pointer to where in the code the error occured. It took hours for me to find the problem

Hi JM_F,

This type of coding error certainly should not cause a shutdown in SQL Server, as SQL should be resiliant to stack overflow issues within the CLR. When I run your repro on my machine (x86) I get the expected behavior:

Msg 6538, Level 16, State 49, Procedure sp, Line 0

.NET Framework execution was aborted because of stack overflow.

It's likely that you are hitting a bug in the CLR. Can you describe your specific repro scenario that causes SQL to crash? You mention that SQL only crashes sometimes when you call this SP, do you know of anything that is different between when it causes SQL to crash, and when it does not?

Can you file this as a bug on connect.microsoft.com with any extra information you have? This will make it easier to investigate. If there is a dump file located in your MSSQL\LOG directory, please include that in the bug or email them to me directly at stevehem at microsoft dot com.

Thanks,

Steven

|||

Thank you for your quick reply.

We have two SQL servers installed on two physical servers.

A: a 64-bit Windows server. SQL Server 2005 SP1. The production server.

B: a 64-bit Windows server. SQL Server 2005 SP1. The test server.

The problem in the C# code causes the error message you sent when I run it on B. I've never managed to reproduce the crash on B. Because A is our production server, I can't try to reproduce the problem there anymore. Yesterday I run the erroneuos code maybe ten of times in A and it crashed two times (that I can see from the server log).

I'll send next week a message to connect.microsoft.com with more detailed information that I don't want to put here in the discussion forum.

I don't know whether this is related to the CLR problem, but yesterday evening and today morning the SQL server A got somehow jammed. For example, we couldn't open Management Studio and open Programmability/Stored procedures. It just didn't open. Also the CLR stored procedures just didn't work, they never seemed to finish. We had to restart the SQL server A (I guess this was done by starting and stopping the windows service, don't know for sure, because I didn't do this). Now we have a new problem with registering CLR stored procedures - both of the following commands produce an error:

CREATE PROCEDURE [dbo].prA AS EXTERNAL NAME a.xxxxxx;

GO

drop ASSEMBLY a;

Msg 6512, Level 16, State 27, Procedure prLoadOptionContracts, Line 1

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131522. You need to restart SQL server to use CLR integration features.

I guess we have to restart the server again?

|||

This is interesting - this error means that the SQL failed to bind to the CLR host on your server. This is usually due to a bad installation of either the CLR or SqlServer, but in very rare cases it can be a result of memory pressure on the machine when the CLR tries to load. So, if after the server restarts the CLR is able to load, that means it was a memory pressure problem, but if it persists then it is an installation one. The DMV sys.dm_clr_properties may also contain some additional information about what state the CLR is in. Is your server running under WOW?

I'm really not sure if these problems are related. You're the only customer I'm aware of that has hit either of them (a search engine check for both of your error messages returns this thread only). So either you are extremely unlucky or there is some underlying cause for the failures.

Steven

|||

Steven Hemingray - MSFT wrote:

The DMV sys.dm_clr_properties may also contain some additional information about what state the CLR is in.

dm_clr_properties actually gives information:

select * from sys.dm_clr_properties

name value

directory

version

state CLR initialization permanently failed

Steven Hemingray - MSFT wrote:

Is your server running under WOW?

I'm not the sysadmin / DBA so I don't actually know. Is there a simple way to check this?

The CLR stored procedures have been working well inside the SQL server, but there definitely has been other problems with the SQL server installation previously. Some of the issues still are unresolved.

I'll provide you with more details:

Last week, on the same day this "CLR crash" occured the server got somehow "stuck". This happened after these CLR problems. The symptoms were that if I tried to open stored procedures "leaf" in Management Studio, it just didn't open. Additionally the CLR stored procedures didn't seem to finish at all. They consumed CPU, but never finished (or at least we didn't want to wait > 15 minutes for a procedure that normally finishes in < one minute). We had to restart the SQL server procedure. After this restart I get this "you need to restart"-message.

There also are performance / availability problems with the server. I don't know the details of these problems, but I've heard from the others who are using the server that occasionally the server gets jammed "for a while" (15 minutes, let's say) and it doesn't seem to respond to anything. This issue is just for background information, I know there's not a lot you can do with the problem with this ambiguous problem statement. Everything just seems to point at there's something wrong with the SQL Server installation.

|||Today we restarted the SQL server. Now CLR gets initialied:

select * from sys.dm_clr_properties
directory C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\
version v2.0.50727
state CLR is initialized

I installed all the CLR stored procedures, log entry:

Common language runtime (CLR) functionality initialized using CLR version v2.0.50727 from C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\.

I tried to run one of the stored procedures -> SQL server crash with message:"A fatal error occurred in .NET Framework runtime. The server is shutting down."

I filed a bug report (266911) to connect.microsoft.com about the issue. It's private, since I uploaded the server log which may contain sensitive information. Let's hope I get some answers from there.|||

Thanks for filing the bug. Just to confirm, this stored procedure you run is still the erroneous stored procedure with the error causing stack overflow, correct? And with the fixed stored procedure, you do not see the errors? If you continue to have these severe problems on your production server even with the fixed stored procedures, then you should escalate your problem through PSS and that is the only channel that can issue a hotfix.

Steven

|||Actually the stored procedure is now fixed, i.e. it doesn't cause a stack overflow anymore. However, CLR stored procedures seem still crash the server. This has happened once. The error message in the stack dump of the SQL server logs says:"A fatal error occurred in .NET Framework runtime.". I'm not able to try the stored procedure on the server anymore, because it's our production server. I know that the stored procedure as such works OK, because it is run on many times a day on another server without problems.

All this should be said in the bug report a filed.|||

We installed a new server with SQL Server 2005 SP1 (let's call it X) and tried the CRL stored procedures there. The results was the same, the SQL Server immediatelly on X crashed when the stored procedure was run the first time. We updated X to SP2. After that we haven't been able to reproducude the crash on X. After a while we updated the original server to SP2. The problem hasn't occurred there either since the update.

We use ADOMDclient in the CLR stored procedures. The ADOMD DLL has been installed as an assembly in our SQL Server. When we updated the SQL Server to SP2, the ADOMDclient in GAC was updated and we had to update the same version to the assembly in SQL Server (the GAC must have the same version of the library in order the assembly to work inside of SQL Server). I wonder if this problem has something to do with ADOMDClient inside SQL Server....

CLR stored procedure crashing SQL Server

I had the following erroneous code in a SQL Server stored C# procedure:

class P

{

private DateTime? e;

public P(

DateTime? e)

{

this.e= e;

}

public DateTime? E

{

get

{

return E; // correction return e;

}

set

{

E= value; // correction e = value;

}

}

}

Calling the getter of E of the class P creates an infinite number of method calls. This causes the .NET stack overflow. This sometimes caused our SQL crash. Here's the log:

29.3.2007 9:46:08 A fatal error occurred in .NET Framework runtime. The server is shutting down.

29.3.2007 9:46:10 Microsoft SQL Server 2005 - 9.00.2153.00 (X64)

May 9 2006 13:58:37

Copyright (c) 1988-2005 Microsoft Corporation

In my opinion an error in SQL server CLR stored procedure must not be able to crash the whole SQL Server, as it seems to do. Could someone verify this?

What makes finding problems like this problematic is that the only error is like ".NET stack overflow". No pointer to where in the code the error occured. It took hours for me to find the problem

Hi JM_F,

This type of coding error certainly should not cause a shutdown in SQL Server, as SQL should be resiliant to stack overflow issues within the CLR. When I run your repro on my machine (x86) I get the expected behavior:

Msg 6538, Level 16, State 49, Procedure sp, Line 0

.NET Framework execution was aborted because of stack overflow.

It's likely that you are hitting a bug in the CLR. Can you describe your specific repro scenario that causes SQL to crash? You mention that SQL only crashes sometimes when you call this SP, do you know of anything that is different between when it causes SQL to crash, and when it does not?

Can you file this as a bug on connect.microsoft.com with any extra information you have? This will make it easier to investigate. If there is a dump file located in your MSSQL\LOG directory, please include that in the bug or email them to me directly at stevehem at microsoft dot com.

Thanks,

Steven

|||

Thank you for your quick reply.

We have two SQL servers installed on two physical servers.

A: a 64-bit Windows server. SQL Server 2005 SP1. The production server.

B: a 64-bit Windows server. SQL Server 2005 SP1. The test server.

The problem in the C# code causes the error message you sent when I run it on B. I've never managed to reproduce the crash on B. Because A is our production server, I can't try to reproduce the problem there anymore. Yesterday I run the erroneuos code maybe ten of times in A and it crashed two times (that I can see from the server log).

I'll send next week a message to connect.microsoft.com with more detailed information that I don't want to put here in the discussion forum.

I don't know whether this is related to the CLR problem, but yesterday evening and today morning the SQL server A got somehow jammed. For example, we couldn't open Management Studio and open Programmability/Stored procedures. It just didn't open. Also the CLR stored procedures just didn't work, they never seemed to finish. We had to restart the SQL server A (I guess this was done by starting and stopping the windows service, don't know for sure, because I didn't do this). Now we have a new problem with registering CLR stored procedures - both of the following commands produce an error:

CREATE PROCEDURE [dbo].prA AS EXTERNAL NAME a.xxxxxx;

GO

drop ASSEMBLY a;

Msg 6512, Level 16, State 27, Procedure prLoadOptionContracts, Line 1

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131522. You need to restart SQL server to use CLR integration features.

I guess we have to restart the server again?

|||

This is interesting - this error means that the SQL failed to bind to the CLR host on your server. This is usually due to a bad installation of either the CLR or SqlServer, but in very rare cases it can be a result of memory pressure on the machine when the CLR tries to load. So, if after the server restarts the CLR is able to load, that means it was a memory pressure problem, but if it persists then it is an installation one. The DMV sys.dm_clr_properties may also contain some additional information about what state the CLR is in. Is your server running under WOW?

I'm really not sure if these problems are related. You're the only customer I'm aware of that has hit either of them (a search engine check for both of your error messages returns this thread only). So either you are extremely unlucky or there is some underlying cause for the failures.

Steven

|||

Steven Hemingray - MSFT wrote:

The DMV sys.dm_clr_properties may also contain some additional information about what state the CLR is in.

dm_clr_properties actually gives information:

select * from sys.dm_clr_properties

name value

directory

version

state CLR initialization permanently failed

Steven Hemingray - MSFT wrote:

Is your server running under WOW?

I'm not the sysadmin / DBA so I don't actually know. Is there a simple way to check this?

The CLR stored procedures have been working well inside the SQL server, but there definitely has been other problems with the SQL server installation previously. Some of the issues still are unresolved.

I'll provide you with more details:

Last week, on the same day this "CLR crash" occured the server got somehow "stuck". This happened after these CLR problems. The symptoms were that if I tried to open stored procedures "leaf" in Management Studio, it just didn't open. Additionally the CLR stored procedures didn't seem to finish at all. They consumed CPU, but never finished (or at least we didn't want to wait > 15 minutes for a procedure that normally finishes in < one minute). We had to restart the SQL server procedure. After this restart I get this "you need to restart"-message.

There also are performance / availability problems with the server. I don't know the details of these problems, but I've heard from the others who are using the server that occasionally the server gets jammed "for a while" (15 minutes, let's say) and it doesn't seem to respond to anything. This issue is just for background information, I know there's not a lot you can do with the problem with this ambiguous problem statement. Everything just seems to point at there's something wrong with the SQL Server installation.

|||Today we restarted the SQL server. Now CLR gets initialied:

select * from sys.dm_clr_properties
directory C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\
version v2.0.50727
state CLR is initialized

I installed all the CLR stored procedures, log entry:

Common language runtime (CLR) functionality initialized using CLR version v2.0.50727 from C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\.

I tried to run one of the stored procedures -> SQL server crash with message:"A fatal error occurred in .NET Framework runtime. The server is shutting down."

I filed a bug report (266911) to connect.microsoft.com about the issue. It's private, since I uploaded the server log which may contain sensitive information. Let's hope I get some answers from there.|||

Thanks for filing the bug. Just to confirm, this stored procedure you run is still the erroneous stored procedure with the error causing stack overflow, correct? And with the fixed stored procedure, you do not see the errors? If you continue to have these severe problems on your production server even with the fixed stored procedures, then you should escalate your problem through PSS and that is the only channel that can issue a hotfix.

Steven

|||Actually the stored procedure is now fixed, i.e. it doesn't cause a stack overflow anymore. However, CLR stored procedures seem still crash the server. This has happened once. The error message in the stack dump of the SQL server logs says:"A fatal error occurred in .NET Framework runtime.". I'm not able to try the stored procedure on the server anymore, because it's our production server. I know that the stored procedure as such works OK, because it is run on many times a day on another server without problems.

All this should be said in the bug report a filed.|||

We installed a new server with SQL Server 2005 SP1 (let's call it X) and tried the CRL stored procedures there. The results was the same, the SQL Server immediatelly on X crashed when the stored procedure was run the first time. We updated X to SP2. After that we haven't been able to reproducude the crash on X. After a while we updated the original server to SP2. The problem hasn't occurred there either since the update.

We use ADOMDclient in the CLR stored procedures. The ADOMD DLL has been installed as an assembly in our SQL Server. When we updated the SQL Server to SP2, the ADOMDclient in GAC was updated and we had to update the same version to the assembly in SQL Server (the GAC must have the same version of the library in order the assembly to work inside of SQL Server). I wonder if this problem has something to do with ADOMDClient inside SQL Server....

CLR Stored procedure consume webservice

Can you have a CRL stored procedure call a webservice that returns a dataset?

If you download and install the SQL Server samples from http://msdn2.microsoft.com/en-us/sql/aa336343.aspx you'll find there is a sample called CurrencyWebService (typically it installs to systemdrive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\CurrencyWebService). This sample demonstrates exposing the results of invoking a web service in server-side code as a table valued function. The simple web service provided does in fact return a data set. You can read more about this sample at http://msdn2.microsoft.com/en-us/library/ms365190.aspx.

--Bonnie [MSFT]

|||To do this:
Create a class from an existing webservice using wsdl

CLR Stored Procedure "Failed to get permission to execute"

Hello All,
This is variant/continuation of a problem I've discussed on the
sqlserver.programming group (Thanks to Erland for a lot of help over there).
I thought it might benefit me to raise the issue here as well.
In a nutshell:
I have a C# stored procedure (called SProc, in cs.dll, but "imported" as
"csfn") that uses PInvoke to access a function in a C++ dll (called
cpp.dll). The function is __declspec(dllexport)'d. If the C++ dll is
UNMANAGED this now seems to work OK. (Thank again to Erland helping me get
this far). Now, if I add some managed code to the C++ dll (leaving the
unmanaged entry point unchanged, but now compiling with /clr) I get a
"Failed to get permisssion to execute" message as below.
I've tried all the obvious (to me anyway) ways of adding the appropriate
permissions. I've used the "Trust Assembly" node of the .Net 2.0
Configuration tool to give "Full Trust" to both cs.dll AND cpp.dll but it
still fails. I've confirmed that I can call the C++ dll from a c# EXE from
the command line, and that works OK.
I'd really appreciate any help.
/john
Error text follows.
Msg 6522, Level 16, State 1, Procedure csfn, Line 0
A .NET Framework error occurred during execution of user defined routine or
aggregate 'csfn':
System.IO.FileLoadException: Could not load file or assembly 'cpp,
Version=11.0.1.0, Culture=neutral, PublicKeyToken=eb5ebc232de94dcf' or one
of its dependencies. Failed to grant permission to execute. (Exception from
HRESULT: 0x80131418) --> System.Security.Policy.PolicyException: Execution
permission cannot be acquired.
System.Security.Policy.PolicyException:
at System.Security.SecurityManager.ResolvePolicy(Evidence evidence,
PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset,
PermissionSet& denied, Boolean checkExecutionPermission)
at System.Security.SecurityManager.ResolvePolicy(Evidence evidence,
PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset,
PermissionSet& denied, Int32& securitySpecialFlags, Boolean
checkExecutionPermission)
System.IO.FileLoadException:
at DotNetFun.SqlServer.SProc.CPPTest()
at DotNetFun.SqlServer.SProc.CPPTest()
at DotNetFun.SqlServer.SProc.PrintMessage(String Message)
.Hello Eric,
The consensus is (I think), that you can use pInvoke to call an
unmanaged DLL from a managed stored procedure. However, it looks like
calling a MIXED (ie managed and unmanaged) DLL is not going to work. You can
look at the thread I started on the sqlserver.programming group for more
information.
It seems that mixed and/or managed DLLs need to be added to SQL with
CREATE ASSEMBLY, and this fails on a mixed dll, even if set it as UNSAFE.
I'd like to establish if that is expected to work, but I suspect it is
failing for good reason.
/john
"Eric" wrote in message news:200762618736eric_hackett@.kindermorg
an.com...
> Did you find any satisfaction for this? I have a nearly identical problem.
> Using a third party dll, wrapping it with my own dll, and then writing CLR
> stored proc to use wrapped function. Other functions in the stored proc
> dll work. Only the ones that need external resources are choking.
> Please reply here if you get this working.
> EggHeadCafe.com - .NET Developer Portal of Choice
> http://www.eggheadcafe.com

CLR Stored procedure

Can I create a CLR stored procedure in VB or C# to print a file. Please give the code sample

All you would need to do it create a SQL Server project, add a stored procedure class, write your printing code inside there, and then deploy it to the database.

You probably shouldn't be doing this though. Any CLR stored procedures or functions should abide by the same principles as the database, which is to not rely on any resources which may not be there. Communicating with a printer is prone to this type of problem as the network may be down, or the printer may be off or out of paper etc. Therefore it shouldn't really be a stored procedure.

CLR stored procedure

I got a problem with my CLR C# stored procedure. The proc have just one
param VARCHAR(8000) which I define in C# as String. Works fine until string
exceed 4000 bytes .
Instead of String I tryed SqlChars and SqlString - doesn't work.
In this case I get error "MDX statement was expected. An MDX expression was
specified."
Please help."Tim" <Tim@.discussions.microsoft.com> wrote in message
news:81C5DAC4-81EE-4688-92E9-968FA2F75F04@.microsoft.com...
>I got a problem with my CLR C# stored procedure. The proc have just one
> param VARCHAR(8000) which I define in C# as String. Works fine until
> string
> exceed 4000 bytes .
> Instead of String I tryed SqlChars and SqlString - doesn't work.
> In this case I get error "MDX statement was expected. An MDX expression
> was
> specified."
> Please help.
How about VARCHAR(MAX)?
David|||Try it too - same result.
Thanks.
"David Browne" wrote:

> "Tim" <Tim@.discussions.microsoft.com> wrote in message
> news:81C5DAC4-81EE-4688-92E9-968FA2F75F04@.microsoft.com...
> How about VARCHAR(MAX)?
> David
>
>|||This shouldn't be possible, if you tried to create your stored proc with
parameter varchar(8000) it would fail with the following error:
CREATE PROCEDURE for "MyStoredProc" failed because T-SQL and CLR types for
parameter "@.myparameter" do not match.
String parameters to CLR stored procs need to be nvarchar based because CLR
strings are all unicode, so to support strings larger than 4000 characters,
you'll need to use nvarchar(max).
Steven
"Tim" <Tim@.discussions.microsoft.com> wrote in message
news:81C5DAC4-81EE-4688-92E9-968FA2F75F04@.microsoft.com...
>I got a problem with my CLR C# stored procedure. The proc have just one
> param VARCHAR(8000) which I define in C# as String. Works fine until
> string
> exceed 4000 bytes .
> Instead of String I tryed SqlChars and SqlString - doesn't work.
> In this case I get error "MDX statement was expected. An MDX expression
> was
> specified."
> Please help.