Cluster Mirroring

I've read a lot of the information about this topic in MSDN. My boss askme to understand and to let working an example application of Cluster Mirroring. The problem is that i dont understand well yet what is a Cluster and what is the main idea behind it.

If someone really understand and can explain me clearly whats behind it, i will be very grateful. Thanks a lot for the help. ( Sorry for me english :( )

Ok, Basically a cluster is a two-headed monster. Chop one of it's heads off and nothing will happen, it will still carry on chasing you.

In computer terms, the simplest cluster is a two-node cluster (two headed monster) , two physical computers sharing the same name and IP address. If one of the computers making up the cluster fails (chop one head off) the cluster will still carry on functioning (chasing you) on the other computer participating in the cluster.

The general idea behind it is to provide high-availability for your apps by eliminating a single point of failure.

|||

Thanks bobbins!

But i have another question ... when you have a mirror database with a witness, you also provide high-availability. Because if something happen the mirror will continue chasing you .. Why is much better the cluster ? Thanks a lot again

|||

There are many discussions/articles on Clustering vs Mirroring vs Replication vs Log Shipping

In short, I think clustering is still a better solution for high-availability, PROVIDED that you do have a SAN

We're using mirroring now until we can get a SAN in, then we'll move to clustering

sqlsql

CLR User-defined aggregate support Java/J#?

I want to write a Java User-defined aggregate (UDA). Shall I use J#?

I converted the C# example given in books online to J#. I am getting this error (as well as whole bunch of warning when I create the assembly.)

Msg 6558, Level 16, State 1, Line 1

CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification due to method 'Init'.

Msg 6597, Level 16, State 2, Line 1

CREATE AGGREGATE failed.

btw, I have the use unsafe assembly options when creating the assembly otherwise I get this error:

Msg 6265, Level 16, State 1, Line 1

CREATE ASSEMBLY failed because type "com.ms.vjsharp.cor.COMUtils" in safe assembly "vjscor" has a pinvokeimpl method. P/Invoke is not allowed in safe assemblies.

Warning: The Microsoft .Net frameworks assembly 'vjscor, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.

From the warning, I can tell the J# UDA is not tested.

Can someone confirm whether J# UDA is supported or not?

Thanks!

-

Here is my code:

ALTER DATABASE MEDIO set TRUSTWORTHY ON

CREATE ASSEMBLY MyAgg FROM 'C:\code\console\PriceUDA\obj\Debug\PriceUDA.dll' WITH PERMISSION_SET = unsafe

CREATE AGGREGATE MyAgg (@.input nvarchar(200)) RETURNS nvarchar(max) EXTERNAL NAME MyAgg.Concatenate

GO

import System.*;

import System.Data.*;

import Microsoft.SqlServer.Server.*;

import System.Data.SqlTypes.*;

import System.IO.*;

import System.Text.*;

/** @.attribute Serializable() */

/** @.attribute 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 implements 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.get_IsNull())

{

return;

}

this.intermediateResult.Append(value.get_Value()).Append(',');

}

/// <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.get_Length() > 0)

{

output = this.intermediateResult.ToString(0, this.intermediateResult.get_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());

}

}

No responses... Has anyone else been able to get this to work?|||

Hi Chang!

In SQL Server 2005 we support C#, Visual Basic and managed C++ with SQL CLR.

We haven't tested integration with other languages. However, I can imagine that other languages' vendors have tested their languages with SQL CLR, or are going to do this. In this case, they may have published their recommendations on how to use their languages' features with SQL CLR, what problems you may have and so on.

I'm not aware of such recommendations for J#.

|||Vadim -- We are asking about Microsoft J#, not some 3rd party tool.

Microsoft Visual J# is a .net language that uses the CLR. I've seen dozens of quotes that say it 'should work', but I have yet to find anybody who actually got it to work.

I have a bunch of database-neutral java stored procedures, that I recently ported to work with mssql, that I now need to implement as CLR stored procedures. I'd *really* like to port my java to j# using .net, as opposed to porting to c# (Yes, I know about JLCA, I'm working my way throught that now). Unfortunately, Visual Studio 2005 in J# mode has no support for SQL Server, and every document I find on the internet only talks about using VS to generate SQL code. So far, I have not found 1 single example that shows J# running as a CLR stored proc, or what you might have to do to get it to work.|||

Hi!

You are right; I should have phrased this better. Anyway, due to a variety of reasons we haven’t put significant effort into supporting J#. According to my knowledge, you have chances that functions and procedures will work, but you most probably will have problems with user-defined types and aggregates. Have you tried to wrap your J# logic into, say, C# aggregate?

The fact that a language is a .NET language doesn’t automatically mean that it will go well with SQL CLR. The reason is that SQL CLR puts additional restrictions and requirements to the IL code, compared to those necessary just to run IL executable. Not all code generated by .NET compilers satisfies them, not all run-time libraries of these languages satisfy them. I’d guess the way J# compiles classes is not compliant with SQL CLR.

|||

I can't recall the exact problems off the top of my head, but I do recall there being problems with J# in SQL CLR. You may be able to get this working if you register the assembly as UNSAFEan unfortunate move.

I'll see if I can dredge up more.

Cheers,

-Isaac

CLR User-defined aggregate support Java/J#?

I want to write a Java User-defined aggregate (UDA). Shall I use J#?

I converted the C# example given in books online to J#. I am getting this error (as well as whole bunch of warning when I create the assembly.)

Msg 6558, Level 16, State 1, Line 1

CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification due to method 'Init'.

Msg 6597, Level 16, State 2, Line 1

CREATE AGGREGATE failed.

btw, I have the use unsafe assembly options when creating the assembly otherwise I get this error:

Msg 6265, Level 16, State 1, Line 1

CREATE ASSEMBLY failed because type "com.ms.vjsharp.cor.COMUtils" in safe assembly "vjscor" has a pinvokeimpl method. P/Invoke is not allowed in safe assemblies.

Warning: The Microsoft .Net frameworks assembly 'vjscor, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.

From the warning, I can tell the J# UDA is not tested.

Can someone confirm whether J# UDA is supported or not?

Thanks!

-

Here is my code:

ALTER DATABASE MEDIO set TRUSTWORTHY ON

CREATE ASSEMBLY MyAgg FROM 'C:\code\console\PriceUDA\obj\Debug\PriceUDA.dll' WITH PERMISSION_SET = unsafe

CREATE AGGREGATE MyAgg (@.input nvarchar(200)) RETURNS nvarchar(max) EXTERNAL NAME MyAgg.Concatenate

GO

import System.*;

import System.Data.*;

import Microsoft.SqlServer.Server.*;

import System.Data.SqlTypes.*;

import System.IO.*;

import System.Text.*;

/** @.attribute Serializable() */

/** @.attribute 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 implements 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.get_IsNull())

{

return;

}

this.intermediateResult.Append(value.get_Value()).Append(',');

}

/// <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.get_Length() > 0)

{

output = this.intermediateResult.ToString(0, this.intermediateResult.get_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());

}

}

No responses... Has anyone else been able to get this to work?
|||

Hi Chang!

In SQL Server 2005 we support C#, Visual Basic and managed C++ with SQL CLR.

We haven't tested integration with other languages. However, I can imagine that other languages' vendors have tested their languages with SQL CLR, or are going to do this. In this case, they may have published their recommendations on how to use their languages' features with SQL CLR, what problems you may have and so on.

I'm not aware of such recommendations for J#.

|||Vadim -- We are asking about Microsoft J#, not some 3rd party tool.

Microsoft Visual J# is a .net language that uses the CLR. I've seen dozens of quotes that say it 'should work', but I have yet to find anybody who actually got it to work.

I have a bunch of database-neutral java stored procedures, that I recently ported to work with mssql, that I now need to implement as CLR stored procedures. I'd *really* like to port my java to j# using .net, as opposed to porting to c# (Yes, I know about JLCA, I'm working my way throught that now). Unfortunately, Visual Studio 2005 in J# mode has no support for SQL Server, and every document I find on the internet only talks about using VS to generate SQL code. So far, I have not found 1 single example that shows J# running as a CLR stored proc, or what you might have to do to get it to work.

|||

Hi!

You are right; I should have phrased this better. Anyway, due to a variety of reasons we haven’t put significant effort into supporting J#. According to my knowledge, you have chances that functions and procedures will work, but you most probably will have problems with user-defined types and aggregates. Have you tried to wrap your J# logic into, say, C# aggregate?

The fact that a language is a .NET language doesn’t automatically mean that it will go well with SQL CLR. The reason is that SQL CLR puts additional restrictions and requirements to the IL code, compared to those necessary just to run IL executable. Not all code generated by .NET compilers satisfies them, not all run-time libraries of these languages satisfy them. I’d guess the way J# compiles classes is not compliant with SQL CLR.

|||

I can't recall the exact problems off the top of my head, but I do recall there being problems with J# in SQL CLR. You may be able to get this working if you register the assembly as UNSAFEan unfortunate move.

I'll see if I can dredge up more.

Cheers,

-Isaac

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 Security Permissions on a sql 2005 server, service broker

A made a brokermethod just like in the helloworld_clr example in de service
broker example dir in sql 2005. The brokermethod receives a message and trie
s
to deserialize the message body which is a list of customer objects.
But when I try to deserialize I get the following exception.
It’s about something with permissions on an assembly. But I don’t have a
ny
experience with that.
Could someone tell me what I’m doing wrong?
Any help would be greatly appreciated.
CustomerMessage exception:
System.Security.SecurityException:
Request for the permission of type
'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0,
Culture=neutral,
PublicKeyToken=b77a5c561934e089' failed.
at
System.Runtime.Serialization.Formatters.Binary.ObjectReader.CheckSecurity(Pa
rseRecord pr)
at
System.Runtime.Serialization.Formatters.Binary.ObjectReader.ParseObject(Pars
eRecord pr)
at
System.Runtime.Serialization.Formatters.Binary.ObjectReader.Parse(ParseRecor
d
pr)
at
System.Runtime.Serialization.Formatters.Binary.__BinaryParser.ReadObjectWith
MapTyped(BinaryObjectWithMapTyped record)
at
System.Runtime.Serialization.Formatters.Binary.__BinaryParser.ReadObjectWith
MapTyped(BinaryHeaderEnum binaryHeaderEnum)
at System.Runtime.Serialization.Formatters.Binary.__BinaryParser.Run()
at
System.Runtime.Serialization.Formatters.Binary.ObjectReader.Deserialize(Head
erHandler
handler, __BinaryParser serParser, Boolean fCheck, Boolean isCrossAppDomain,
IMethodCallMessage methodCallMessage)
at
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Deserialize(S
tream
serializationStream, HeaderHandler handler, Boolean fCheck, Boolean
isCrossAppDomain, IMethodCallMessage methodCallMessage)
at
System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Deserialize(S
tream serializationStream)
at testservice.services.CustomerService.CustomerMessage(Message msgReceived,
SqlConnection connection, SqlTransaction transaction)
The action that failed was: Demand
The type of the first permission that failed was:
System.Security.Permissions.SecurityPermission
The Zone of the assembly that failed was: MyComputerIs the broker method activated (WITH ACTIVATION (...) on the queue) or are
you invoking it from a user connection?
Activation has certain security restrictions because of the EXECUTE AS
context, see this blog
http://blogs.msdn.com/remusrusanu/a.../12/512085.aspx
Also, what kind of serialization are you using? Inside SQL Server there are
certain restrictions as the ordinary Xml serialization cannot be used, see
this blog http://blogs.msdn.com/sqlclr/archiv.../25/Vineet.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"arn0" <arn0@.discussions.microsoft.com> wrote in message
news:E09E3890-12B6-491E-B990-1BECE84C5F62@.microsoft.com...
>A made a brokermethod just like in the helloworld_clr example in de service
> broker example dir in sql 2005. The brokermethod receives a message and
> tries
> to deserialize the message body which is a list of customer objects.
> But when I try to deserialize I get the following exception.
> It's about something with permissions on an assembly. But I don't have any
> experience with that.
> Could someone tell me what I'm doing wrong?
> Any help would be greatly appreciated.
> CustomerMessage exception:
> System.Security.SecurityException:
> Request for the permission of type
> 'System.Security.Permissions.SecurityPermission, mscorlib,
> Version=2.0.0.0,
> Culture=neutral,
> PublicKeyToken=b77a5c561934e089' failed.
> at
> System.Runtime.Serialization.Formatters.Binary.ObjectReader.CheckSecurity(
ParseRecord
> pr)
> at
> System.Runtime.Serialization.Formatters.Binary.ObjectReader.ParseObject(Pa
rseRecord
> pr)
> at
> System.Runtime.Serialization.Formatters.Binary.ObjectReader.Parse(ParseRec
ord
> pr)
> at
> System.Runtime.Serialization.Formatters.Binary.__BinaryParser.ReadObjectWi
thMapTyped(BinaryObjectWithMapTyped
> record)
> at
> System.Runtime.Serialization.Formatters.Binary.__BinaryParser.ReadObjectWi
thMapTyped(BinaryHeaderEnum
> binaryHeaderEnum)
> at System.Runtime.Serialization.Formatters.Binary.__BinaryParser.Run()
> at
> System.Runtime.Serialization.Formatters.Binary.ObjectReader.Deserialize(He
aderHandler
> handler, __BinaryParser serParser, Boolean fCheck, Boolean
> isCrossAppDomain,
> IMethodCallMessage methodCallMessage)
> at
> System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Deserialize
(Stream
> serializationStream, HeaderHandler handler, Boolean fCheck, Boolean
> isCrossAppDomain, IMethodCallMessage methodCallMessage)
> at
> System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Deserialize
(Stream
> serializationStream)
> at testservice.services.CustomerService.CustomerMessage(Message
> msgReceived,
> SqlConnection connection, SqlTransaction transaction)
> The action that failed was: Demand
> The type of the first permission that failed was:
> System.Security.Permissions.SecurityPermission
> The Zone of the assembly that failed was: MyComputer|||Hello arn0,

> A made a brokermethod just like in the helloworld_clr example in de
> service broker example dir in sql 2005. The brokermethod receives a
> message and tries to deserialize the message body which is a list of
> customer objects.
> But when I try to deserialize I get the following exception.
> It’s about something with permissions on an assembly. But I don’t have
> any
> experience with that.
> Could someone tell me what I’m doing wrong?
> Any help would be greatly appreciated.
> CustomerMessage exception: System.Security.SecurityException: Request
> for the permission of type
> 'System.Security.Permissions.SecurityPermission, mscorlib,
> Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
> failed.
[SNIP]
> testservice.services.CustomerService.CustomerMessage(Message
> msgReceived, SqlConnection connection, SqlTransaction transaction)
> The action that failed was: Demand The type of the first permission
> that failed was: System.Security.Permissions.SecurityPermission The
> Zone of the assembly that failed was: MyComputer
Adding this to the .CLR and .ServiceBroker newsgroups as folks in those grou
ps
might be able to add more.
The example in question doesn't deserialize an object graph, at least from
what I can today. So unless you can post the code in question, we're probabl
y
not going to be help you much. I've done some work with simple serialization
of objects with SQLCLR and haven't run into this yet. Chances are this isn't
an object with the permissions of the assembly. You can change the permissio
n
level of an assembly when you catalog it by adding "with permission_set =
" and one of SAFE, EXTERNAL_ACCESS or UNSAFE. Try unsafe first here, but
I doubt its going to get your much as I'm guessing you're doing something
with CAS that SQLCLR doesn't like.
Oh BTW, are you trying to load the base objects from the GAC?
PS: send messages, not object graphics -- just had to get that out of my
system... :)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||the customer object comes from a assambly I registered this assembly in the
db with this command
create ASSEMBLY CustomerAssembly
FROM 'd:\development\Customer.dll'
with PERMISSION_SET = unsafe
GO
this is the brokermethod code
<BrokerMethod("CustomerMessage")> _
Public Sub CustomerMessage(ByVal msgReceived As Message, ByVal
connection As SqlConnection, ByVal transaction As SqlTransaction)
writeToLog("CustomerMessage start", connection, transaction)
Try
Dim customerArray As List(Of Customer)
Dim bfmt As BinaryFormatter = New BinaryFormatter()
Dim cmd As SqlCommand
msgReceived.Body.Position = 0
customerArray = bfmt.Deserialize(msgReceived.Body)
cmd = connection.CreateCommand
cmd.Transaction = transaction
cmd.CommandText = "INSERT INTO [arnotest].[dbo].[customer]
([name],[lastname]) VALUES(@.name,@.lastname)"
Dim name As IDataParameter = cmd.Parameters.Add("@.name",
SqlDbType.NVarChar)
Dim lastname As IDataParameter = cmd.Parameters.Add("@.name",
SqlDbType.NVarChar)
For Each Item As Customer In customerArray
name.Value = Item.name
lastname.Value = Item.lastname
cmd.ExecuteNonQuery()
Next
Catch ex As Exception
writeToLog(String.Concat("CustomerMessage exception: ",
ex.ToString), connection, transaction)
End Try
End Sub|||Hello arn0,

> the customer object comes from a assambly I registered this assembly
> in the db with this command
> create ASSEMBLY CustomerAssembly
> FROM 'd:\development\Customer.dll'
> with PERMISSION_SET = unsafe
> GO
> this is the brokermethod code
> <BrokerMethod("CustomerMessage")> _
> Public Sub CustomerMessage(ByVal msgReceived As Message, ByVal
> connection As SqlConnection, ByVal transaction As SqlTransaction)
> writeToLog("CustomerMessage start", connection, transaction)
> Try
> Dim customerArray As List(Of Customer)
> Dim bfmt As BinaryFormatter = New BinaryFormatter()
> Dim cmd As SqlCommand
> msgReceived.Body.Position = 0
> customerArray = bfmt.Deserialize(msgReceived.Body)
> cmd = connection.CreateCommand
> cmd.Transaction = transaction
> cmd.CommandText = "INSERT INTO [arnotest].[dbo].[customer]
> ([name],[lastname]) VALUES(@.name,@.lastname)"
> Dim name As IDataParameter = cmd.Parameters.Add("@.name",
> SqlDbType.NVarChar)
> Dim lastname As IDataParameter =
> cmd.Parameters.Add("@.name",
> SqlDbType.NVarChar)
> For Each Item As Customer In customerArray
> name.Value = Item.name
> lastname.Value = Item.lastname
> cmd.ExecuteNonQuery()
> Next
> Catch ex As Exception
> writeToLog(String.Concat("CustomerMessage exception: ",
> ex.ToString), connection, transaction)
> End Try
> End Sub
Okay, so this is very similar (on the surface) to my lightweight example
except that I'm using XML rather than binary serialization. See: [0].
What does the Customer Class look like?
[0]: http://www.sqljunkies.com/WebLog/kt...serialized.aspx
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi arn0,
Sounds like you may be running into "SQL Server doesn't allow dynamic
serializer assemblies" behavior. If you're using "add web reference" you
need to pregenerate the serializer DLL (using sgen.exe) and catalog it
separately. The serializer needs to match the class its "serializing for"
WRT version.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"arn0" <arn0@.discussions.microsoft.com> wrote in message
news:D5AE8A90-EA04-4484-A5E2-EABD04584CFA@.microsoft.com...
> the customer object comes from a assambly I registered this assembly in
> the
> db with this command
> create ASSEMBLY CustomerAssembly
> FROM 'd:\development\Customer.dll'
> with PERMISSION_SET = unsafe
> GO
> this is the brokermethod code
> <BrokerMethod("CustomerMessage")> _
> Public Sub CustomerMessage(ByVal msgReceived As Message, ByVal
> connection As SqlConnection, ByVal transaction As SqlTransaction)
> writeToLog("CustomerMessage start", connection, transaction)
> Try
> Dim customerArray As List(Of Customer)
> Dim bfmt As BinaryFormatter = New BinaryFormatter()
> Dim cmd As SqlCommand
> msgReceived.Body.Position = 0
> customerArray = bfmt.Deserialize(msgReceived.Body)
> cmd = connection.CreateCommand
> cmd.Transaction = transaction
> cmd.CommandText = "INSERT INTO [arnotest].[dbo].[customer]
> ([name],[lastname]) VALUES(@.name,@.lastname)"
> Dim name As IDataParameter = cmd.Parameters.Add("@.name",
> SqlDbType.NVarChar)
> Dim lastname As IDataParameter = cmd.Parameters.Add("@.name",
> SqlDbType.NVarChar)
> For Each Item As Customer In customerArray
> name.Value = Item.name
> lastname.Value = Item.lastname
> cmd.ExecuteNonQuery()
> Next
> Catch ex As Exception
> writeToLog(String.Concat("CustomerMessage exception: ",
> ex.ToString), connection, transaction)
> End Try
> End Sub|||Hello Bob,

> Sounds like you may be running into "SQL Server doesn't allow dynamic
> serializer assemblies" behavior. If you're using "add web reference"
> you need to pregenerate the serializer DLL (using sgen.exe) and
> catalog it separately. The serializer needs to match the class its
> "serializing for" WRT version.
Nope, its not a Web Reference -- first thing I checked. As you can see from
the code provided, he using BinaryFormatter on the instance. But, is SGEN
only XML Serialization specific? If not, this is proably worth doing.
My current thought is that there's a CAS setting in the class (or something
that the class inherits) has a prohibited behavior.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

CLR DDL Triggers with SqlTrigger Attribute?

Does anyone have an example of using the SqlTrigger attribute for a DDL trigger? Cannot seem to locate one?What do you want to do ?

Jens Suessmeyer.

http://www.sqlserver2005.de
|||

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
// what values do you plug into these parms?
// [Microsoft.SqlServer.Server.SqlTrigger (Name="Trigger1", Target="Table1", Event="FOR UPDATE")]
public static void Trigger1()
{
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
}
}

|||

for example...

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
[Microsoft.SqlServer.Server.SqlTrigger (Name="Trigger1", Target="DATABASE", Event="CREATE_ASSEMBLY")]
public static void Trigger1()
{
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
}
}

i can build this assembly and even deploy it via VS but no trigger exists with the name of Trigger1? What gives, tell me what I am doing stupid here guys!

|||further more there are no dependencies listed on the assembly called Trigger1?|||

Target="AdventureWorks"

doesnt work either....I am about to the point of assuming you cannot do this via VS and your only option for deploying managed DDL triggers is manual...

|||This one worked for me :

[Microsoft.SqlServer.Server.SqlTrigger(Name = "ddltrigger", Target = "database",Event = "FOR CREATE_TABLE")]

public static void ddltrigger()

{

// Replace with your own code

SqlContext.Pipe.Send("Trigger FIRED");

}

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

I believe the problem was that I omitted the FOR in my event="" parm...

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
[Microsoft.SqlServer.Server.SqlTrigger(Name = "ddltrigger", Target = "database", Event = "FOR CREATE_TABLE")]
public static void ddltrigger()
{
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
}
}

that code works when I used a new project. Either way you gave me my example. Thread solved!

thx dude. join www.redmondsociety.com we'd love to have u as one of our first members.

|||

BTW...try using this code with autoDeploy...

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
[Microsoft.SqlServer.Server.SqlTrigger(Name = "ddltrigger", Target = "all server", Event = "FOR DDL_SERVER_LEVEL_EVENTS")]
public static void ddltrigger()
{
// Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED");
}
}

error at deployTime: "Server-Level AutoDeployment is not supported"..little tid-bit of knowledge resulting from this.

Closest in Column

What SQL statement do I use to find what is the closest value in a data
set to the value I have.
For example I need to search in a column for the closest value that
exists to what I have:
E.g. my database has
X
1.2
1.3
2.6
1.0
2.5
1.4
1.7
I have 1.5 so I need to a query that return 1.4.
Any help would be appreciated
Thanks
TarryThis may not be very efficient unless you have an index on the column
X, but...
Could you query for 2 numbers, for a given SearchNumber to search for:
A. Largest number smaller than SearchNumber
B. Smallest number larger than SearchNumber
And then select whichever is closer to SearchNumber, A or B.
Basically:
SELECT
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
) T|||Lubdha Khandelwal wrote:
> This may not be very efficient unless you have an index on the column
> X, but...
> Could you query for 2 numbers, for a given SearchNumber to search for:
> A. Largest number smaller than SearchNumber
> B. Smallest number larger than SearchNumber
> And then select whichever is closer to SearchNumber, A or B.
> Basically:
> SELECT
> CASE
> WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> T.Smaller
> ELSE T.Larger
> END
> FROM
> (
> SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> FROM MyTable T1
> FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
> WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
> ) T
Lubha
I think we should remove <= from the query and join condition also
should be changed.
it should be
SELECT
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
) T
Or
SELECT @.SearchNumber - T.Smaller,T.larger - @.SearchNumber,
CASE
WHEN (@.SearchNumber - T.Smaller < T.larger - @.SearchNumber ) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
select (select max(x) from mytable where x < @.searchnumber) smaller,
(select min(x) from mytable where x > @.searchnumber) larger
) T
Regards
Amish Shah|||> SELECT
> CASE
> WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> T.Smaller
> ELSE T.Larger
> END
> FROM
> (
> SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> FROM MyTable T1
> FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
> WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
> ) T
>
But then you're not checking for equality. What if @.SearchNumber itself
exists in column X, shouldn't it return @.SearchNumber?|||Lubdha Khandelwal wrote:
> > SELECT
> > CASE
> > WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> > T.Smaller
> > ELSE T.Larger
> > END
> > FROM
> > (
> > SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> > FROM MyTable T1
> > FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
> > WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
> > ) T
> >
>
> But then you're not checking for equality. What if @.SearchNumber itself
> exists in column X, shouldn't it return @.SearchNumber?
Ok , but for join also you should join it on < not on =
Here is gives null when using = for joins.
create table mytable(x decimal(10,2))
insert into mytable values(1)
insert into mytable values(2)
insert into mytable values(3)
insert into mytable values(4)
insert into mytable values(5)
declare @.searchnumber decimal(10,2)
set @.searchnumber = 2.5
SELECT t.smaller, t.larger,
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
) T
Regards
Amish Shah|||CREATE TABLE #temp (
Value NUMERIC(8, 2)
)
INSERT INTO #temp (Value) VALUES ( 1.2 )
INSERT INTO #temp (Value) VALUES ( 1.3 )
INSERT INTO #temp (Value) VALUES ( 2.6 )
INSERT INTO #temp (Value) VALUES ( 1.0 )
INSERT INTO #temp (Value) VALUES ( 2.5 )
INSERT INTO #temp (Value) VALUES ( 1.4 )
INSERT INTO #temp (Value) VALUES ( 1.7 )
DECLARE @.Target NUMERIC(8, 2)
SELECT @.Target = 1.5
SELECT *
FROM #temp
WHERE CAST(ABS(Value - @.Target) AS NUMERIC(8, 2)) = (SELECT
MIN(CAST(ABS(Value - @.Target) AS NUMERIC(8, 2))) FROM #temp)|||Actually, I came across another way to get the closest in a column...
SELECT TOP 1 *
FROM MyTable
ORDER BY ABS(X - @.SearchNumber) ASC|||Lubdha Khandelwal wrote:
> Actually, I came across another way to get the closest in a column...
> SELECT TOP 1 *
> FROM MyTable
> ORDER BY ABS(X - @.SearchNumber) ASC
Very nice! Painfully simple! Made me slap my forehead twice...

Closest in Column

What SQL statement do I use to find what is the closest value in a data
set to the value I have.
For example I need to search in a column for the closest value that
exists to what I have:
E.g. my database has
X
1.2
1.3
2.6
1.0
2.5
1.4
1.7
I have 1.5 so I need to a query that return 1.4.
Any help would be appreciated
Thanks
TarryThis may not be very efficient unless you have an index on the column
X, but...
Could you query for 2 numbers, for a given SearchNumber to search for:
A. Largest number smaller than SearchNumber
B. Smallest number larger than SearchNumber
And then select whichever is closer to SearchNumber, A or B.
Basically:
SELECT
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
) T|||Lubdha Khandelwal wrote:

> This may not be very efficient unless you have an index on the column
> X, but...
> Could you query for 2 numbers, for a given SearchNumber to search for:
> A. Largest number smaller than SearchNumber
> B. Smallest number larger than SearchNumber
> And then select whichever is closer to SearchNumber, A or B.
> Basically:
> SELECT
> CASE
> WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> T.Smaller
> ELSE T.Larger
> END
> FROM
> (
> SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> FROM MyTable T1
> FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
> WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
> ) T
Lubha
I think we should remove <= from the query and join condition also
should be changed.
it should be
SELECT
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
) T
Or
SELECT @.SearchNumber - T.Smaller,T.larger - @.SearchNumber,
CASE
WHEN (@.SearchNumber - T.Smaller < T.larger - @.SearchNumber ) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
select (select max(x) from mytable where x < @.searchnumber) smaller,
(select min(x) from mytable where x > @.searchnumber) larger
) T
Regards
Amish Shah|||
> SELECT
> CASE
> WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
> T.Smaller
> ELSE T.Larger
> END
> FROM
> (
> SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
> FROM MyTable T1
> FULL OUTER JOIN MyTable T2 ON T1.X < T2.X
> WHERE T1.X < @.SearchNumber AND T2.X > @.SearchNumber
> ) T
>
But then you're not checking for equality. What if @.SearchNumber itself
exists in column X, shouldn't it return @.SearchNumber?|||Lubdha Khandelwal wrote:

>
> But then you're not checking for equality. What if @.SearchNumber itself
> exists in column X, shouldn't it return @.SearchNumber?
Ok , but for join also you should join it on < not on =
Here is gives null when using = for joins.
create table mytable(x decimal(10,2))
insert into mytable values(1)
insert into mytable values(2)
insert into mytable values(3)
insert into mytable values(4)
insert into mytable values(5)
declare @.searchnumber decimal(10,2)
set @.searchnumber = 2.5
SELECT t.smaller, t.larger,
CASE
WHEN (@.SearchNumber - T.Smaller < T.Larger - @.SearchNumber) THEN
T.Smaller
ELSE T.Larger
END
FROM
(
SELECT MAX(T1.X) AS Smaller, MIN(T2.X) AS Larger
FROM MyTable T1
FULL OUTER JOIN MyTable T2 ON T1.X = T2.X
WHERE T1.X <= @.SearchNumber AND T2.X >= @.SearchNumber
) T
Regards
Amish Shah|||CREATE TABLE #temp (
Value NUMERIC(8, 2)
)
INSERT INTO #temp (Value) VALUES ( 1.2 )
INSERT INTO #temp (Value) VALUES ( 1.3 )
INSERT INTO #temp (Value) VALUES ( 2.6 )
INSERT INTO #temp (Value) VALUES ( 1.0 )
INSERT INTO #temp (Value) VALUES ( 2.5 )
INSERT INTO #temp (Value) VALUES ( 1.4 )
INSERT INTO #temp (Value) VALUES ( 1.7 )
DECLARE @.Target NUMERIC(8, 2)
SELECT @.Target = 1.5
SELECT *
FROM #temp
WHERE CAST(ABS(Value - @.Target) AS NUMERIC(8, 2)) = (SELECT
MIN(CAST(ABS(Value - @.Target) AS NUMERIC(8, 2))) FROM #temp)|||Actually, I came across another way to get the closest in a column...
SELECT TOP 1 *
FROM MyTable
ORDER BY ABS(X - @.SearchNumber) ASC|||Lubdha Khandelwal wrote:
> Actually, I came across another way to get the closest in a column...
> SELECT TOP 1 *
> FROM MyTable
> ORDER BY ABS(X - @.SearchNumber) ASC
Very nice! Painfully simple! Made me slap my forehead twice...