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 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.

CLR Stored Proc Queue activation help

I set up a queue to activate a clr stored procedure upon receiving a message and nothing is happening. I have tried everything listed in this topic:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=200684&SiteID=1

except for saying "Recieve Top(0)" which didn't make any sense to me. I have set the database to trustworthy (all of this is taking place within a single database on a local server). There are messages on the queue, and I have the queue activation set to max_queue_readers=2, procedure_name=StoredProcedure1, execute as owner. I tried execute as self and that didn't work either. I signed the assembly that contains StoredProcedure1 and the assembly that it references. The only thing that appears in the sql error log is this (I trimmed off the timestamp):

AppDomain 15 (TriggerTest.dbo[runtime].14) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations.
AppDomain 15 (TriggerTest.dbo[runtime].14) unloaded.
AppDomain 18 (TriggerTest.dbo[runtime].17) created.

If I call the stored procedure manually it works just fine

I also tried signing the procedure (the t-sql stored proc that calls the clr proc) as described here:

http://blogs.msdn.com/remusrusanu/archive/2006/03/01/541882.aspx

Still no luck... The proc is never activated. The message sits in the queue until I manually retrieve it.

|||

If you issue an EXECUTE AS USER = 'nameofqueueowneruser'; followed by mannualy launching the procedure, do you get an error or is the procedure running fine?

Thanks,
~ Remus

|||The procedure runs fine when executed as you stated above.|||

Are you sure the queue activation is actually enabled? Activation can be set up (procedure name, max_queue_readers, execute as user) but the activation itself might be disabled. sys.service_queues.is_activation_enabled column will show this. If is disabled, then running ALTER QUEUE [queuename] WITH ACTIVATION (STATUS = ON) will enabled it. Note that the clause WITH ACTIVATION (STATUS = ON/OFF) is different from WITH STATUS = ON/OFF.

HTH,
~ Remus

|||Ok, apparently the procedure was running, it just wasn't retrieving the message from the queue. I changed the procedure to simply insert a row into a table and it did do that. Also, I set a breakpoint in the procedure, but VS2005 never stops. Now I need to figure out why it won't retrieve the message. Thanks for you help|||

How are you attempting to debug from Visual Studio?

For debugging, you need to attach to the sqlservr.exe process using remote debugger. We have tested that this works even for internally activated procedures.

Rushi

CLR Stored proc 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 there.
Anyone know what's going on?Hello Quimbly,

> 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.
Can you be a bit more specific about what you're doing?
Is your CLR proc calling another CLR proc without in parameter passed as
ref out out? How are you executing the other stored procedure.
Note: a better newsgroup for this would be Micorosoft.Public.SqlServer.CLR,
I've copied this there.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||> Can you be a bit more specific about what you're doing?
The top-level CLR proc calls a regular static method in the SQL CLR project.
This static method makes a call to another SqlProcedure (static method
tagged with [Microsoft.SqlServer.Server.SqlProcedure] attribute).
The static method is calling the lower-level CLR proc as follows:
string sqlString = "LC_SP_ScheduleEvents_SelectForDate";
SqlCommand command = new SqlCommand(sqlString, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@.P_LuminaireID", luminaireID);
command.Parameters.AddWithValue("@.P_TargetDate", usageDate);
command.Parameters.AddWithValue("@.P_TargetDateStartInUTC", startTimeUTC);
command.Parameters.AddWithValue("@.P_TargetDateEndInUTC", endTimeUTC);
SqlDataReader reader = command.ExecuteReader();
//...
Inside the LC_SP_ScheduleEvents_SelectForDate proc, it times out when it's
piping it's results back:
I.e.:
...
for (int i = 0; i < dr.Length; i++)
{
// send one event
eventRow = new SqlDataRecord(eventRowMetaData);
eventRow.SetSqlBoolean(0, bool.Parse(dr[i]["IsLampOn"].ToString()));
eventRow.SetSqlDateTime(1,
DateTime.Parse(dr[i]["ActualEventTime"].ToString()));
eventRow.SetInt32(2, int.Parse(dr[i]["DimmingLevel"].ToString()));
SqlContext.Pipe.SendResultsRow(eventRow);
}
}
SqlContext.Pipe.SendResultsEnd();|||Hello Quimbly,
I know this seems lame, but I suspect you have a reference type issue here.
Try assigning IsLampOn, ActualEventTime and DimmingLevel to local value type
s,
then sets those into the DataRecord.
Its low hanging fruit to solve first.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

CLR Stored proc 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 there.
Anyone know what's going on?
Hello Quimbly,

> 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.
Can you be a bit more specific about what you're doing?
Is your CLR proc calling another CLR proc without in parameter passed as
ref out out? How are you executing the other stored procedure.
Note: a better newsgroup for this would be Micorosoft.Public.SqlServer.CLR,
I've copied this there.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||> Can you be a bit more specific about what you're doing?
The top-level CLR proc calls a regular static method in the SQL CLR project.
This static method makes a call to another SqlProcedure (static method
tagged with [Microsoft.SqlServer.Server.SqlProcedure] attribute).
The static method is calling the lower-level CLR proc as follows:
string sqlString = "LC_SP_ScheduleEvents_SelectForDate";
SqlCommand command = new SqlCommand(sqlString, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@.P_LuminaireID", luminaireID);
command.Parameters.AddWithValue("@.P_TargetDate", usageDate);
command.Parameters.AddWithValue("@.P_TargetDateStar tInUTC", startTimeUTC);
command.Parameters.AddWithValue("@.P_TargetDateEndI nUTC", endTimeUTC);
SqlDataReader reader = command.ExecuteReader();
//...
Inside the LC_SP_ScheduleEvents_SelectForDate proc, it times out when it's
piping it's results back:
I.e.:
...
for (int i = 0; i < dr.Length; i++)
{
// send one event
eventRow = new SqlDataRecord(eventRowMetaData);
eventRow.SetSqlBoolean(0, bool.Parse(dr[i]["IsLampOn"].ToString()));
eventRow.SetSqlDateTime(1,
DateTime.Parse(dr[i]["ActualEventTime"].ToString()));
eventRow.SetInt32(2, int.Parse(dr[i]["DimmingLevel"].ToString()));
SqlContext.Pipe.SendResultsRow(eventRow);
}
}
SqlContext.Pipe.SendResultsEnd();
|||Hello Quimbly,
I know this seems lame, but I suspect you have a reference type issue here.
Try assigning IsLampOn, ActualEventTime and DimmingLevel to local value types,
then sets those into the DataRecord.
Its low hanging fruit to solve first.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

CLR Stored proc 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 there.
Anyone know what's going on?Hello Quimbly,
> 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.
Can you be a bit more specific about what you're doing?
Is your CLR proc calling another CLR proc without in parameter passed as
ref out out? How are you executing the other stored procedure.
Note: a better newsgroup for this would be Micorosoft.Public.SqlServer.CLR,
I've copied this there.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||> Can you be a bit more specific about what you're doing?
The top-level CLR proc calls a regular static method in the SQL CLR project.
This static method makes a call to another SqlProcedure (static method
tagged with [Microsoft.SqlServer.Server.SqlProcedure] attribute).
The static method is calling the lower-level CLR proc as follows:
string sqlString = "LC_SP_ScheduleEvents_SelectForDate";
SqlCommand command = new SqlCommand(sqlString, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@.P_LuminaireID", luminaireID);
command.Parameters.AddWithValue("@.P_TargetDate", usageDate);
command.Parameters.AddWithValue("@.P_TargetDateStartInUTC", startTimeUTC);
command.Parameters.AddWithValue("@.P_TargetDateEndInUTC", endTimeUTC);
SqlDataReader reader = command.ExecuteReader();
//...
Inside the LC_SP_ScheduleEvents_SelectForDate proc, it times out when it's
piping it's results back:
I.e.:
...
for (int i = 0; i < dr.Length; i++)
{
// send one event
eventRow = new SqlDataRecord(eventRowMetaData);
eventRow.SetSqlBoolean(0, bool.Parse(dr[i]["IsLampOn"].ToString()));
eventRow.SetSqlDateTime(1,
DateTime.Parse(dr[i]["ActualEventTime"].ToString()));
eventRow.SetInt32(2, int.Parse(dr[i]["DimmingLevel"].ToString()));
SqlContext.Pipe.SendResultsRow(eventRow);
}
}
SqlContext.Pipe.SendResultsEnd();

CLR SP debugging problem

Hi All,

Could you please help me?

I have a problem with T-SQL stored procedure, which call the CLR Stored Procedure, and when it runs into it my Visual Studio gets berserk, looping like crazy, eating 100% CPU and gobling off more and more memory. The Visual Studio out into the the Output window the following message over and over again:

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.

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.

The only way to stop it is by killing the Visual Studio process. Then I have to stop, or sometimes even kill the SQL server to bring the CPU down from 100%.

What should I do to avoid this problem? Thank you very much for the help.

Does this problem reproduce consistently? This is likely to be a known issue with debugging while in parallel execution. Can you try setting MAXDOP(1) and see if that solves the problem?

If not, can you paste the code you're using that's hitting this?

Thanks,

Steven

|||

The problem was in the test data. My CLR procedure processes regular expressions. The pattern for regular expression has been written incorrectly and because of it there was an error in debug process. After I have corrected a pattern of regular expression a problem have left.

Thank you for the consideration,

Sveta