Showing posts with label private. Show all posts
Showing posts with label private. Show all posts

Monday, March 19, 2012

CLR user defined aggregate function (SS2005)

Hello,
In a user defined aggregate in Visual Studio 2005 with SQL Server 2005, I
declared a private variable in the struct and initialized in the Init "event
"
of the aggregate.
In the Terminate "event" it turned out that the variable was out of scope
(null).
My first thought was that this happened as a result of reads and writes of
intermediate results of the aggregate function, where (in my case) the
private variable was not explicitly saved.
But this idea is not consistent with the Merge "event" that also uses the
private variable and there it never is out of scope.
Who can explain?
Thx.examnotes <Axford@.discussions.microsoft.com> wrote in
news:B7150A1D-2067-4377-8483-090BB56A7023@.microsoft.com:

> In a user defined aggregate in Visual Studio 2005 with SQL Server
> 2005, I declared a private variable in the struct and initialized in
> the Init "event" of the aggregate.
> In the Terminate "event" it turned out that the variable was out of
> scope (null).
> My first thought was that this happened as a result of reads and
> writes of intermediate results of the aggregate function, where (in my
> case) the private variable was not explicitly saved.
> But this idea is not consistent with the Merge "event" that also uses
> the private variable and there it never is out of scope.
How do you establish that the variable is not null in the Merge method?
In other words, are you certain that the Merge is being called. SQL will
not call Merge unless it creates a new instance of your aggregate and
runs that instance in parallell over a group in your accumulation.
Anyway, can you please post the code for your aggregate and we'll have a
look.
Also, it'd probably be better is you posted this in the sqlserver.clr
group which deals explicitly with SQLCLR issues. I will x-post this
reply to there.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Sorry it was the Accumulate event where the private variable was in scope. S
o
probably the Merge event will set it to null also. Can I enforce thecall to
Merge in some way?
I cannot find a group sqlserver.clr.
Thanks for the reply.
"Niels Berglund" wrote:

> examnotes <Axford@.discussions.microsoft.com> wrote in
> news:B7150A1D-2067-4377-8483-090BB56A7023@.microsoft.com:
>
>
> How do you establish that the variable is not null in the Merge method?
> In other words, are you certain that the Merge is being called. SQL will
> not call Merge unless it creates a new instance of your aggregate and
> runs that instance in parallell over a group in your accumulation.
> Anyway, can you please post the code for your aggregate and we'll have a
> look.
> Also, it'd probably be better is you posted this in the sqlserver.clr
> group which deals explicitly with SQLCLR issues. I will x-post this
> reply to there.
>
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>|||examnotes <Axford@.discussions.microsoft.com> wrote in
news:FC2366AE-AFFD-4AD9-8E03-680568F1C497@.microsoft.com:

> Sorry it was the Accumulate event where the private variable was in
> scope. So probably the Merge event will set it to null also. Can I
> enforce thecall to Merge in some way?
OK, so in Accumulate the variable is OK, but in Terminate it is null -
is that correct? If so, please post the code for your UDA. AFAIK, you
can not force a call for Merge.

> I cannot find a group sqlserver.clr.
Do a refresh of your newsgroup list - that should make it available to
you (microsoft.public.sqlserver.clr).
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Here you go with the code (it is from Microsoft samples):
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
Format.UserDefined,MaxByteSize=8000, IsInvariantToNulls=true,
IsInvariantToDuplicates = false, IsInvariantToOrder=true,
IsNullIfEmpty=true)]
public struct Concatenate : IBinarySerialize
{
private string delimeter;
private StringBuilder intermediateResult;
public void Init()
{
delimeter = "-";
intermediateResult = new StringBuilder();
}
public void Accumulate(SqlString value)
{
if (value.IsNull)
return;
intermediateResult.Append(value.Value).Append(delimeter);
}
public void Merge(Concatenate other)
{
intermediateResult.Append(other.intermediateResult).Append(delimeter);
}
public SqlString Terminate()
{
string output = string.Empty;
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0,
this.intermediateResult.Length - 1);
}
return new SqlString(output);
}
#region IBinarySerialize Members
void IBinarySerialize.Read(System.IO.BinaryReader r)
{
intermediateResult = new StringBuilder(r.ToString());
}
void IBinarySerialize.Write(System.IO.BinaryWriter w)
{
w.Write(intermediateResult.ToString());
}
#endregion
}
"Niels Berglund" wrote:

> examnotes <Axford@.discussions.microsoft.com> wrote in
> news:FC2366AE-AFFD-4AD9-8E03-680568F1C497@.microsoft.com:
>
> OK, so in Accumulate the variable is OK, but in Terminate it is null -
> is that correct? If so, please post the code for your UDA. AFAIK, you
> can not force a call for Merge.
>
> Do a refresh of your newsgroup list - that should make it available to
> you (microsoft.public.sqlserver.clr).
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>

Sunday, March 11, 2012

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

Sunday, February 12, 2012

ClickOnce private installation issue

I'm working on a VB.Net 2005 application that uses SQLCE as a backend database.

Following the instructions on how to do a private installation (http://msdn2.microsoft.com/en-us/library/bb219482.aspx) so that admin rights were not needed, I successfully got it working as a ClickOnce deployment. But today during a demo for a client, they ran into this error while installing the latest version from our publishing URL:

"Unable to install or run the application. Application requires that assembly System.Data.SQLServerCe version 9.0.42 .... must be installed in the Global Assembly Cache."

Basically it's saying the user isn't an admin so they can't install. This is the problem I went to great lengths to try to avoid, and is the reason we are using SQL CE in the first place: admin rights are not required to install our application's database. To our knowledge, SQL CE is the only Microsoft product that fits this scenario.

Now, I haven't changed anything in the publishing of the application to my knowledge, and when I check the project prerequisites, the SQLCE engine still isn't in there (as summarized at the above URL). Again following the instructions at the URL above for private installation, the required assemblies are still part of the project's files.

It must be something I did to cause this, but I have no memory of changing anything in regards to this part of the application. Our deadline is in 4 days and I cannot continue development until I get past this issue.

Where do I look to fix this problem? A little help would be greatly appreciated.

It is currently unknown as to what caused this problem and the fix may be a kludge. However, we have made some changes after much agonizing, and the application now installs with a newly-created standard user account on Windows XP. After seeing what we did to fix this issue below, any further comments are still appreciated, since we're working with a db system that's only a few months old, and which is not documented with the same rigor as most Microsoft products.

Upon checking the Prerequisites for our application (at Project Properties | Publish | Prerequisites), we saw that not only was SQL CE not selected as a prerequisite, it was not even listed as something we could deselect. Yet, it was still being configured to be installed into the GAC by the ClickOnce engine. Earlier in the development process, perhaps three weeks ago, it did appear in the prerequisites list, and was explicitly deselected. But now it had... disappeared.

However, upon checking what would be installed at Project Properties | Publish | Application Files, we saw that indeed, System.Data.SQLServerCE.dll was being included in the install, and its Publish Status was set as Prerequisite(Auto). Certainly we did not explicitly make this setting, and we are still unable to explain how it happened. Add to this that it could not be deselected on the Prerequisites tab, and you begin to understand the confusion surrounding this issue. (NOTE: we found this same problem with the Microsoft Visual Studio Report Viewer.)

Our first cut at a fix did not work: we changed the Publish status on each item listed as Prerequisite(Auto) to Exclude. The deployment completed without error, but after installation the application could not find required code to execute correctly, and threw unhandled errors at runtime.

Our second try at a fix is where we are now: we changed all items (except Framework 2.0) formerly listed as Prerequisite(Auto) to Include. Interestingly, those items that would not install as prerequisites without Administrator permissions, would now install without error. The last error to be cleared concerned the data file itself.

During our application deployment, we wanted to copy the .sdf database to the program folder. We reference it in code without a fully qualified path, due to the expectation that the database file will be in the same directory as the application executable. However, after clearing the errors described above, we had one last problem to fix: the database file was not found after installation. Checking, we saw that the ClickOnce engine automatically copies the database file to a different folder than the rest of the application files. So, we returned yet again to the Application Files list at Project Properties | Publish, and found that something Auto was happening with our database as well: its Publish Status was listed as Data File(Auto). Taking yet another wild guess, we changed it to Include. Lo, behold and voila, everything now works.

The very unfortunate issues here are 1) that we don't know why it didn't work in the first place, 2) we don't know what happened to change what we thought were correct settings for publication, and 3) we don't know whay what we did to fix the problems worked. To wit, nothing makes logical sense at this point.

We don't know whether any of the things we went through should be classed as bugs in either ClickOnce, Visual Studio, or anything else. Admittedly we are rather over our heads using several technologies for the first time. However, it needs to be said: at best, the documentation for this deployment scenario is sadly lacking (or just plain wrong). At worst, this deployment scenario is not supported by default, and it takes several arcane spells and a lot of wing of bat and eye of newt due to bugs in the system.

At any rate, I hope this tale of woe may help others who may be doing this for the first time.