Cluster and User Defined Functions

Has anyone had difficulty with UDF's in a clustered environment? We have
some DTS code that references a user defined function. When we run the DTS
package on non-clustered servers, the DTS package executes in several
minutes. However, when we run the same code against the database in a
clustered environment, the package never finishes. Watching the proc
monitor shows fairly decent server activity, but just never returns.
Anyone?
Thanks
Rob Heyman
I'm not sure what the UDF could be doing that will only cause problems in a
cluster so maybe that is not the real issue. Have you tried tracing the
execution of the package under Profiler to see where the package execution
gets "stuck"? You should also check for blocking when the package is
running. Please see the following article for more info:
271509 INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
Farooq Mahmud [MS SQL Support]
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

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

CLR User Defined Aggregate Function

I am trying to modify the C# example given in the "Invoking CLR User-Defined Aggregate Functions" in SQL Server 2005 Books Online. I want to add a parameter that is used as the list delimeter, instead of the hard coded comma that is used. I am not a C# programmer and don't know where to begin. Please help!

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;

/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}

/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
if (value.IsNull)
{
return;
}

this.intermediateResult.Append(value.Value).Append(','); /// I want to change to comma to a variable
}

/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
this.intermediateResult.Append(other.intermediateResult);
}

/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = string.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
}

return new SqlString(output);
}

public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}

public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}

Hi Rob,

Unfortunately, a current limitation of our UDAs is that they can only take one parameter: the single parameter being aggregated. This precludes a few scenarios, including an additional parameter that is constant for the life of the aggregate (your situation) as well as a second aggregated parameter.

The usual workaround we recommend for folks who want to aggregate over two variables is to wrap them using either XML or a UDT and then unwrap them inside of the aggregate. This could work in your situation as welljust hold one of the variables constantbut is a particularly ugly hack.

Cheers,

-Isaac

|||Hi Isaac,

Since we're considering ugly hacks, I had an idea for another workaround: could I create a table variable, and access it from within my UDA?

E.g., in the following procedure, I create a temp table with params, which my UDA will expect to find in scope when it runs. I have no idea if this would work at all...

CREATE PROCEDURE foo
AS
DECLARE @.settings table (
param1 int,
param2 char
)

SELECT lastName, dbo.myConcatUDA(firstName)
FROM people
GROUP BY lastName

GO

CLR User Defined Aggregate Function

I am trying to modify the C# example given in the "Invoking CLR User-Defined Aggregate Functions" in SQL Server 2005 Books Online. I want to add a parameter that is used as the list delimeter, instead of the hard coded comma that is used. I am not a C# programmer and don't know where to begin. Please help!

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;

/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}

/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
if (value.IsNull)
{
return;
}

this.intermediateResult.Append(value.Value).Append(','); /// I want to change to comma to a variable
}

/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
this.intermediateResult.Append(other.intermediateResult);
}

/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = string.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
}

return new SqlString(output);
}

public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}

public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}

Hi Rob,

Unfortunately, a current limitation of our UDAs is that they can only take one parameter: the single parameter being aggregated. This precludes a few scenarios, including an additional parameter that is constant for the life of the aggregate (your situation) as well as a second aggregated parameter.

The usual workaround we recommend for folks who want to aggregate over two variables is to wrap them using either XML or a UDT and then unwrap them inside of the aggregate. This could work in your situation as welljust hold one of the variables constantbut is a particularly ugly hack.

Cheers,

-Isaac

|||Hi Isaac,

Since we're considering ugly hacks, I had an idea for another workaround: could I create a table variable, and access it from within my UDA?

E.g., in the following procedure, I create a temp table with params, which my UDA will expect to find in scope when it runs. I have no idea if this would work at all...

CREATE PROCEDURE foo
AS
DECLARE @.settings table (
param1 int,
param2 char
)

SELECT lastName, dbo.myConcatUDA(firstName)
FROM people
GROUP BY lastName

GO

CLR Table valued functions error

Hi,

I'm trying to create a CLR functions

this is the Sql Function attribute and the FillRowMethod signature

[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read,

FillRowMethodName = "FillRows",IsPrecise=true,

TableDefinition = "SOCIETA nvarchar(55),CLIENTE nvarchar(150),NUMEROCONTRATTO nvarchar(255),FIRMA datetime,CHIUSURA datetime,AUTORIZZATO float"

)]

publicstaticIEnumerable dbf_Create_RiepilogoAccordi(SqlInt32 commessa, SqlInt32 tipo_commessa, SqlInt32 progetto, SqlInt32 DAC, SqlInt32 figura, SqlDateTime dataFatturazioneDa, SqlDateTime dataFatturazioneA)

publicstaticvoid FillRows(Object obj, outSqlString SOCIETA, outSqlString CLIENTE, outSqlString NUMEROCONTRATTO, outSqlDateTime FIRMA, outSqlDateTime CHIUSURA, SqlDouble AUTORIZZATO)

Whe I try to deploy my function, I get the following error:

Error 1 Function signature of "FillRow" method (as designated by SqlFunctionAttribute.FillRowMethodName) does not match SQL declaration for table valued CLR function 'dbf_Create_RiepilogoAccordi' due to column 6. CM.Reports.SIA.RiepilogoAccordi

I get this error whichever combination of name/value I use for column 6

Can someone help me?

Thanks

Marco

I forgot to mark parameter AUTORIZZATO as an out parameter. the signature should be

publicstaticvoid FillRows(Object obj, outSqlString SOCIETA, outSqlString CLIENTE, outSqlString NUMEROCONTRATTO, outSqlDateTime FIRMA, outSqlDateTime CHIUSURA, out SqlDouble AUTORIZZATO)

CLR Table Value Function Insert Into Table Variable

I have a simple clr tvf that splits a string and returns a two column table. When I try and insert the results of this tvf into another table variable I get the error below. Can anyone help me on this one? What a huge letdown if clr tvf cannot be insert into table variables. Inserting into a temp table works fine.

-- BTW I am on the September CTP with VS 2005 RC

Thanks,
Adam

Error Message

Msg 8624, Level 16, State 1, Line 2

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

Below is the SQL I am using to test

declare @.t table(a int, b nvarchar(128))
insert into @.t
select * from dbo.Split('Hello,GoodBye', ',', 1)

Function Definition

CREATE FUNCTION [dbo].[Split](@.value [nvarchar](4000), @.seperator [nvarchar](32) = N',', @.removeEmptyEntries [bit] = 1)
RETURNS TABLE ([Position] [int] NULL,[Value] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Sit.Sql.Cdw].[Sit.Sql.Cdw.Split].[InitMethod]
GO

Source Code

using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;

namespace Sit.Sql.Cdw
{
public class Split
{
public struct Splitter
{
public int pos;
public String value;
}

[SqlFunction(FillRowMethodName= "FillRow", Name="Split", TableDefinition="Position int, Value nvarchar(1000)")]
public static IEnumerable InitMethod(String value, String seperator, bool removeEmptyEntries)
{
Splitter[] sVals;
String[] vals = value.Split( new string[1] { seperator }, (removeEmptyEntries) ?
StringSplitOptions.RemoveEmptyEntries : StringSplitOptions.None);
sVals = new Splitter[vals.Length];
for(int i = 0; i < vals.Length; i++)
{
sVals.pos = i + 1;
sVals.value = vals;
}
return sVals;
}

public static void FillRow(Object obj, out SqlInt32 Position, out SqlChars Value)
{
Splitter s = (Splitter)obj;
Position = new SqlInt32(s.pos);
Value = new SqlChars(s.value);
}
}
}

I just realized my mistake after posting...
I originally called the function with default parameters dbo.Split('Hello,Goodbye', default, default) which produces the error. Substituting the default parameter with an actual value runs fine.

CLR Performance

We had a CLR function that was running in seconds under the April CTP - now it takes over a minute in the new Sep CTP. We've determined that the bottleneck is in the following line:

Dim SqlConn As SqlConnection = New SqlConnection("context connection=true")

Is there a new recommended way to open a connection?

That looks right to me, and I'd be very surprised if it was simply the SqlConnection constructor that was causing this (otherwise everyone would be hitting it).

Can you provide any more information?

Thanks,
-Isaac|||

My fault - it's not the constructor that's the overhead, it's the open command. My test function looks like the following:

<SqlFunction(dataaccess:=DataAccessKind.Read)> _
Public Shared Function PerfTest() As Integer
Dim SqlConn As SqlConnection = New SqlConnection("context connection=true")
SqlConn.Open()
SqlConn.Close()
Return 0
End Function

If I call this function 15,000 times using Apr CTP, it runs in about 5 seconds. Using the latest, Sept CTP, it takes a minute or longer.

|||There was a bug introduced during this time (and fixed after the Sept. CTP) that might have some bearing. Check the memory allocations occuring in the CLR (SELECT single_pages_kb FROM sys.memory_clerks WHERE type = 'MEMORYCLERK_SQLCLR'). We had a problem where Open + Close of the context connection leaked memory, and required additional work to set up again. The equivalent to the connection pool not re-using connections, but with a bit less overhead to re-establish the connection.

One other question: are you seeing this perf difference for functions specificially, or does it also show up for procedures?

~Alazel

Developer
Microsoft Sql Server

This posting is provided "AS IS" with no warranties, and confers no rights.|||- Is there anyway I can obtain the fix to try out?
- I do not see the view "sys.memory_clerks" that you mention in your mail.
- The problem is apparent in the function since I call it over and over in a SELECT statement. I'm not sure about procedures.
|||- Sept CTP is the last SQL Server 2005 CTP. We would RTM soon and the fix would be available in it.
- Its actually sys.dm_os_memory_clerks
- Could you try creating a Stored Procedure with the same code and execute it same number of times as suggested by Alazel?

Thanks,
-Vineet.|||- The single_pages_kb increased from 1088 to 1096 when running the function.
- I created a stored procedure as you requested and I see the same performance problem - it runs in about the same time as the function.

I'm just curious if you have tried to run the PerfTest function (listed earlier in the posting) with the new SQL Server version to make sure the fix you mentioned has truly fixed the problem.
|||I tested running your function 15,000 times on a post-CTP16 build and it executed quickly (2 seconds, actually) as both a UD Function and a UD Stored Procedure.
Here is how I executed it to make sure we're doing an equivalent test:
declare @.i int, @.null int
set @.i = 1
while @.i < 15000
begin
select @.null = dbo.PerfTest()
set @.i = @.i + 1
end|||Yes, you're sample is pretty much what I did to test the UD stored procedure. I just think it would be in Microsoft's interest to send me a patch to test the fix in my environment. Let me know.

CLR out of memory

I've got a CLR function that runs fine on my workstation running SQL
Server Express; however, it throws OOM exceptions on the full SQL
Server 2005.
The function is very simple: it takes an XML file and a stylesheet,
transforms the XML using the stylesheet, and returns the result as a
string. On my workstation, with 2 gb RAM (1gb allocated to SQL Server),
I hardly ever have any problem with it. On the server, with 4gb, the
same function running on the same data terminates with the following
message:
.NET Framework execution was aborted by escalation policy because of
out of memory.
System.Threading.ThreadAbortException: Thread was being aborted.
(...stack follows...)
This happens when input XML data size exceeds ~30-50 mb or so.
I checked performance counters and memory clerks. The problem seems to
be that CLR never grabs enough memory even when it should be available.
I ran the same request on the same data, first on my workstation, then
on the server. On the workstation, once the function was started, CLR
memory utilization went up to about 108 megs, and the function
completed normally. On the server, the CLR memory utilization only went
up to about 75 megs, then the function was terminated.
I checked command line parameters - the -g switch was not used at
either the server or the workstation. Just in case, I added the default
-g256 at the server, to no effect.
So, my question is - what could cause the CLR to not use available
memory? There's something wrong with the server configuration, but I
can't figure out what. Any help would be greatly appreciated!
Below is the function in question.
[SqlFunction(Name="_clrApplyStylesheet",
DataAccess=DataAccessKind.Read)]
public static SqlString _clrApplyStylesheet(SqlXml XmlData,
SqlXml XmlStylesheet)
{
XPathDocument stylesheet, xmlData;
XslCompiledTransform xTransform;
System.Text.StringBuilder sBuilder;
XmlWriter xWriter;
stylesheet = new
XPathDocument(XmlStylesheet.CreateReader());
xmlData = new XPathDocument(XmlData.CreateReader());
sBuilder = new System.Text.StringBuilder();
xWriter = XmlWriter.Create(sBuilder);
xTransform = new XslCompiledTransform();
xTransform.Load(stylesheet);
xTransform.Transform(xmlData, xWriter);
return sBuilder.ToString();
}Hi Abe,
I mentioned this problem briefly here:
http://blogs.msdn.com/sqlclr/archive/2006/03/24/560154.aspx
The issue you are seeing is not because of a lack in physical memory but in
a lack of Virtual Address Space. If you have 1 GB allocated to SQL on your
workstation, then the SQL Buffer Pool will reserve 1 GB of VAS for its use,
leaving 1 GB for all the allocations made outside the buffer pool, including
CLR. However, as you found and contrary to what you expected, on your
server the Buffer Pool is able to use much more memory (depending on if you
are using AWE or /3GB) leaving only the default 256 MB of VAS for everything
else.
Unfortunately, I think your only real options would be to use the -g flag to
reserve more memory for allocations outside the buffer pool or, if possible,
use 64-bit hardware.
Steven
"Abe" <revres_lqs@.yahoo.com> wrote in message
news:1152829807.975377.50710@.35g2000cwc.googlegroups.com...
> I've got a CLR function that runs fine on my workstation running SQL
> Server Express; however, it throws OOM exceptions on the full SQL
> Server 2005.
> The function is very simple: it takes an XML file and a stylesheet,
> transforms the XML using the stylesheet, and returns the result as a
> string. On my workstation, with 2 gb RAM (1gb allocated to SQL Server),
> I hardly ever have any problem with it. On the server, with 4gb, the
> same function running on the same data terminates with the following
> message:
> .NET Framework execution was aborted by escalation policy because of
> out of memory.
> System.Threading.ThreadAbortException: Thread was being aborted.
> (...stack follows...)
> This happens when input XML data size exceeds ~30-50 mb or so.
> I checked performance counters and memory clerks. The problem seems to
> be that CLR never grabs enough memory even when it should be available.
> I ran the same request on the same data, first on my workstation, then
> on the server. On the workstation, once the function was started, CLR
> memory utilization went up to about 108 megs, and the function
> completed normally. On the server, the CLR memory utilization only went
> up to about 75 megs, then the function was terminated.
> I checked command line parameters - the -g switch was not used at
> either the server or the workstation. Just in case, I added the default
> -g256 at the server, to no effect.
> So, my question is - what could cause the CLR to not use available
> memory? There's something wrong with the server configuration, but I
> can't figure out what. Any help would be greatly appreciated!
> Below is the function in question.
> [SqlFunction(Name="_clrApplyStylesheet",
> DataAccess=DataAccessKind.Read)]
> public static SqlString _clrApplyStylesheet(SqlXml XmlData,
> SqlXml XmlStylesheet)
> {
> XPathDocument stylesheet, xmlData;
> XslCompiledTransform xTransform;
> System.Text.StringBuilder sBuilder;
> XmlWriter xWriter;
> stylesheet = new
> XPathDocument(XmlStylesheet.CreateReader());
> xmlData = new XPathDocument(XmlData.CreateReader());
>
> sBuilder = new System.Text.StringBuilder();
> xWriter = XmlWriter.Create(sBuilder);
> xTransform = new XslCompiledTransform();
> xTransform.Load(stylesheet);
> xTransform.Transform(xmlData, xWriter);
> return sBuilder.ToString();
> }
>|||Hi Steven,
Thank you so much - I increased memory allocation with the -g switch,
and it worked!
I wonder why it's such an obscure issue - your article (which I read
even before posting but wasn't sure if it was applicable) seems to be
almost the only one relevant to the issue.
So, when you do use the -g switch: can the Buffer Pool reclaim the
memory from "memtoleave" when it's not required, or are you actually
decreasing the memory available to Buffer Pool at all times? It's a
shared server, and I don't want to slow down everyone just so that my
code could work.
Abe

CLR out of memory

I've got a CLR function that runs fine on my workstation running SQL
Server Express; however, it throws OOM exceptions on the full SQL
Server 2005.
The function is very simple: it takes an XML file and a stylesheet,
transforms the XML using the stylesheet, and returns the result as a
string. On my workstation, with 2 gb RAM (1gb allocated to SQL Server),
I hardly ever have any problem with it. On the server, with 4gb, the
same function running on the same data terminates with the following
message:
.NET Framework execution was aborted by escalation policy because of
out of memory.
System.Threading.ThreadAbortException: Thread was being aborted.
(...stack follows...)
This happens when input XML data size exceeds ~30-50 mb or so.
I checked performance counters and memory clerks. The problem seems to
be that CLR never grabs enough memory even when it should be available.
I ran the same request on the same data, first on my workstation, then
on the server. On the workstation, once the function was started, CLR
memory utilization went up to about 108 megs, and the function
completed normally. On the server, the CLR memory utilization only went
up to about 75 megs, then the function was terminated.
I checked command line parameters - the -g switch was not used at
either the server or the workstation. Just in case, I added the default
-g256 at the server, to no effect.
So, my question is - what could cause the CLR to not use available
memory? There's something wrong with the server configuration, but I
can't figure out what. Any help would be greatly appreciated!
Below is the function in question.
[SqlFunction(Name="_clrApplyStylesheet",
DataAccess=DataAccessKind.Read)]
public static SqlString _clrApplyStylesheet(SqlXml XmlData,
SqlXml XmlStylesheet)
{
XPathDocument stylesheet, xmlData;
XslCompiledTransform xTransform;
System.Text.StringBuilder sBuilder;
XmlWriter xWriter;
stylesheet = new
XPathDocument(XmlStylesheet.CreateReader());
xmlData = new XPathDocument(XmlData.CreateReader());
sBuilder = new System.Text.StringBuilder();
xWriter = XmlWriter.Create(sBuilder);
xTransform = new XslCompiledTransform();
xTransform.Load(stylesheet);
xTransform.Transform(xmlData, xWriter);
return sBuilder.ToString();
}Hi Abe,
I mentioned this problem briefly here:
http://blogs.msdn.com/sqlclr/archiv.../24/560154.aspx
The issue you are seeing is not because of a lack in physical memory but in
a lack of Virtual Address Space. If you have 1 GB allocated to SQL on your
workstation, then the SQL Buffer Pool will reserve 1 GB of VAS for its use,
leaving 1 GB for all the allocations made outside the buffer pool, including
CLR. However, as you found and contrary to what you expected, on your
server the Buffer Pool is able to use much more memory (depending on if you
are using AWE or /3GB) leaving only the default 256 MB of VAS for everything
else.
Unfortunately, I think your only real options would be to use the -g flag to
reserve more memory for allocations outside the buffer pool or, if possible,
use 64-bit hardware.
Steven
"Abe" <revres_lqs@.yahoo.com> wrote in message
news:1152829807.975377.50710@.35g2000cwc.googlegroups.com...
> I've got a CLR function that runs fine on my workstation running SQL
> Server Express; however, it throws OOM exceptions on the full SQL
> Server 2005.
> The function is very simple: it takes an XML file and a stylesheet,
> transforms the XML using the stylesheet, and returns the result as a
> string. On my workstation, with 2 gb RAM (1gb allocated to SQL Server),
> I hardly ever have any problem with it. On the server, with 4gb, the
> same function running on the same data terminates with the following
> message:
> .NET Framework execution was aborted by escalation policy because of
> out of memory.
> System.Threading.ThreadAbortException: Thread was being aborted.
> (...stack follows...)
> This happens when input XML data size exceeds ~30-50 mb or so.
> I checked performance counters and memory clerks. The problem seems to
> be that CLR never grabs enough memory even when it should be available.
> I ran the same request on the same data, first on my workstation, then
> on the server. On the workstation, once the function was started, CLR
> memory utilization went up to about 108 megs, and the function
> completed normally. On the server, the CLR memory utilization only went
> up to about 75 megs, then the function was terminated.
> I checked command line parameters - the -g switch was not used at
> either the server or the workstation. Just in case, I added the default
> -g256 at the server, to no effect.
> So, my question is - what could cause the CLR to not use available
> memory? There's something wrong with the server configuration, but I
> can't figure out what. Any help would be greatly appreciated!
> Below is the function in question.
> [SqlFunction(Name="_clrApplyStylesheet",
> DataAccess=DataAccessKind.Read)]
> public static SqlString _clrApplyStylesheet(SqlXml XmlData,
> SqlXml XmlStylesheet)
> {
> XPathDocument stylesheet, xmlData;
> XslCompiledTransform xTransform;
> System.Text.StringBuilder sBuilder;
> XmlWriter xWriter;
> stylesheet = new
> XPathDocument(XmlStylesheet.CreateReader());
> xmlData = new XPathDocument(XmlData.CreateReader());
>
> sBuilder = new System.Text.StringBuilder();
> xWriter = XmlWriter.Create(sBuilder);
> xTransform = new XslCompiledTransform();
> xTransform.Load(stylesheet);
> xTransform.Transform(xmlData, xWriter);
> return sBuilder.ToString();
> }
>|||Hi Steven,
Thank you so much - I increased memory allocation with the -g switch,
and it worked!
I wonder why it's such an obscure issue - your article (which I read
even before posting but wasn't sure if it was applicable) seems to be
almost the only one relevant to the issue.
So, when you do use the -g switch: can the Buffer Pool reclaim the
memory from "memtoleave" when it's not required, or are you actually
decreasing the memory available to Buffer Pool at all times? It's a
shared server, and I don't want to slow down everyone just so that my
code could work.
Abe

clr integration....

sir here are the functions on my test application...

<Microsoft.SqlServer.Server.SqlFunction()> _

Public Shared Function WeekInYear(ByVal dt As DateTime) As Integer

Return DatePart("ww", dt, FirstDayOfWeek.Monday, _

FirstWeekOfYear.FirstFourDays)

End Function

<Microsoft.SqlServer.Server.SqlFunction()> _

Public Shared Function HttpPost(ByVal uri As String, ByVal parameters As String) As String

Try

Dim urlRegEx As New System.Text.RegularExpressions.Regex("http://192.168.1.23:81/.*")

Dim p As New System.Net.WebPermission(System.Net.NetworkAccess.Connect, urlRegEx)

p.Assert()

Dim req As System.Net.WebRequest = System.Net.WebRequest.Create(uri)

req.ContentType = "application/x-www-form-urlencoded;charset=utf-8"

req.Method = "POST"

Try

Dim bytes() As Byte = System.Text.Encoding.UTF8.GetBytes(parameters)

req.ContentLength = bytes.Length

Dim os As System.IO.Stream = req.GetRequestStream

os.Write(bytes, 0, bytes.Length)

'Push it out there

os.Close()

Catch ex As Exception

Throw New Exception(ex.Message)

End Try

Dim resp As System.Net.WebResponse = req.GetResponse

If resp Is Nothing Then

Return Nothing

End If

Return "pass completed"

Catch ex As Exception

Return ex.Message

Finally

End Try

End Function

the first function works fine, the second function doesn't work, it seems that if i ever use another namespace it requires a security permission.

can you specify why do i recieved this error at runtime?

Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

You have to give the assembly the appropiate permissions in SQL Server. By default only a few assemblies / namespaces are *trusted*. Try to give more access to the assembly by registering the assenblies as UNSAFE or external_Access.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

As Jens says, the most likely issue is that you haven't created the assembly with a permission set that allows you to do web "stuff". For that to work you need to create the assembly with a permission set of EXTERNAL_ACCESS.

Niels

CLR Integration - call function and returning dataset/datatable?

Hi,

I'm very new to Yukon and the CLR integration, but I think I understand the basics. I have worked through the CLR tutorial on MS demo servers in which a function was created in vb.net that sql server could call, returning a string.

One thing I am wondering is if sql server can call a .net function that returns a dataset or a datatable. Would I be able to query directly off of the dataset returned by the function? The reason i ask is because I would like to be able to do this:

Create a function in .net that calls a web service that returns a large query to a datatable or dataset as part of a SSIS package that uploads that query result into our datawarehouse. Web service task in SSIS does not currently work for the web service in question.

I would greatly appreciate any advice.

Regards,

Darrell

Have you taken a look at table-valued functions? These allow you to return a table of data from the CLR, which you could then insert into your database.

Cheers,
-Isaac

CLR function in 2005

If I execute a function in SQL Server 2005 which does a simple query like
SELECT * FROM TableA in an SQL Command object does it require that the
person who executed the CLR stored procedure has select permission on that
object? Or does the CLR proc execute as a DBO? or is it possible to make it
execute as a DBO if not dispite the user who executed it? just security
questions... want to know how to go about writing new procs in the .NET
CLR.. thanks!Same as for TSQL procedures.
The user executing the proc doesn't have to have permissions to the object t
o access, as long as you
don't have a broken ownership chain.
And, you can control user context when creating the proc with the EXECUTE AS
option of the CREATE
PROC command.
However, as soon as you leave the (SQL Server) sandbox, like accessing a fil
e, you need to do
impersonation in your CLR code if you don't want that code to execute with t
he service account that
the SQL Server service is using.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian Henry" <nospam@.nospam.com> wrote in message news:%23qwYGyMMGHA.1532@.TK2MSFTNGP12.phx
.gbl...
> If I execute a function in SQL Server 2005 which does a simple query like
SELECT * FROM TableA in
> an SQL Command object does it require that the person who executed the CL
R stored procedure has
> select permission on that object? Or does the CLR proc execute as a DBO? o
r is it possible to make
> it execute as a DBO if not dispite the user who executed it? just security
questions... want to
> know how to go about writing new procs in the .NET CLR.. thanks!
>|||how would you go about doing impersination to make it look like a CLR
account came from a different account?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejliRQNMGHA.2828@.TK2MSFTNGP12.phx.gbl...
> Same as for TSQL procedures.
> The user executing the proc doesn't have to have permissions to the object
> to access, as long as you don't have a broken ownership chain.
> And, you can control user context when creating the proc with the EXECUTE
> AS option of the CREATE PROC command.
> However, as soon as you leave the (SQL Server) sandbox, like accessing a
> file, you need to do impersonation in your CLR code if you don't want that
> code to execute with the service account that the SQL Server service is
> using.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:%23qwYGyMMGHA.1532@.TK2MSFTNGP12.phx.gbl...
>|||> how would you go about doing impersination to make it look like a CLR account came from a

> different account?
Impersonation is only applicable for UNSAFE or EXTERNAL_ACCESS and when you
access things outside
SQL Server. You cannot even access data inside SQL Server while impersonatin
g. The sole purpose is
to access, say, a file using the end users windows account instead of the se
rvice account. More info
at:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1495a7af-2248-4cee-afdb-92
69fb3a7774.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brian Henry" <nospam@.nospam.com> wrote in message news:%2369btZOMGHA.2744@.TK2MSFTNGP10.phx
.gbl...
> how would you go about doing impersination to make it look like a CLR acco
unt came from a
> different account?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:ejliRQNMGHA.2828@.TK2MSFTNGP12.phx.gbl...
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:ejliRQNMGHA.2828@.TK2MSFTNGP12.phx.gbl:

> Same as for TSQL procedures.
> The user executing the proc doesn't have to have permissions to the
> object to access, as long as you don't have a broken ownership chain.
>
Actually that is not completely correct. If the statement in the CLR
proc is a SQL statement, like a select or something like that
(CommandType.Text), then the ownership chain breaks. This is like
executing a dynamic SQL statememnt within a T-SQL proc.
However if the call in the CLR proc is a stored proc call to a T-SQL
proc (CommandType.StoredProcedure) then the ownership chain stays
intact, and you only need execute perms on the CLR proc.
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
****************************************
**********|||Tack Niels :-)
That was totally unexpected to me. I just had to try it with a CLR object an
d indeed TSQL code
executed inside a CLR proc is handled similar to dynamic SQL in a TSQL proc.
Seems to be yet another
reason to access the data through a TSQL proc inside a CLR proc...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns976D70AB1234Bnielsbdevelopcom@.20
7.46.248.16...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in news:ejliRQNMGHA.2828@.TK2MSFTNGP12.phx.gbl:
>
> Actually that is not completely correct. If the statement in the CLR
> proc is a SQL statement, like a select or something like that
> (CommandType.Text), then the ownership chain breaks. This is like
> executing a dynamic SQL statememnt within a T-SQL proc.
> However if the call in the CLR proc is a stored proc call to a T-SQL
> proc (CommandType.StoredProcedure) then the ownership chain stays
> intact, and you only need execute perms on the CLR proc.
> 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
> ****************************************
**********|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:OZTLgp7MGHA.3460@.TK2MSFTNGP15.phx.gbl:

> Tack Niels :-)
Varsagod :-)!!

> That was totally unexpected to me. I just had to try it with a CLR
> object and indeed TSQL code executed inside a CLR proc is handled
> similar to dynamic SQL in a TSQL proc. Seems to be yet another reason
> to access the data through a TSQL proc inside a CLR proc...
>
Definitely!!!
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
****************************************
**********

ClR Function errror cant get to lcoal file system

My users complain that they cant run a CLR function. I am told that it cant get access to the local file system. I do not how to code these so from SSIS is there any way to let the users gain access to this. If this is a permission issue what is the lease privilege that I can configure for this to work?

Thanks
AdminAnupHow are they connecting to SQL Server ? If you are using SQL Server authenitcation, they will use the SQL Server account permissions to access the files, if this one does not have any permissions, they will get a Access denied. The same situation if you are using Windows authentication and the individual users do not have access to the files. Did you try to run the function individually ? Which error do you get there if you try to let it run at a user context (and not probably one with administrative priviledges :-) )

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||it is using sql authentication.

Can you let me know which permission does sql authentication need to execute

and also if i change it to be windows does it need any admin privileges|||If you are using SQL Server Authentication, the SQL Server service account SQL Server is running with, will need to have rights on the accessed files / shares. If you are using Windows authentication, the logged in User wil need to have those rights.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||Has the CLR assembly been granted EXTERNAL_ACCESS?|||Yes that was it as it was in Safe mode
Thanks