Cluster Install

We have a Win2003 EE A/P cluster running on a DELL/EMC CX300. There are 2
logical disks defined outside of the quorum. When I install the Virtual
server, I select the parent group of the disks which happens to be the SQL
Server resource group which contains all resources except the clsuter
specific items. When I finish, I can only see one logical disk from within
EM even though both are on the node I am working from . Any thoughts on how
to see multiple logical disks?Starting with a LUN that is a cluster resource. Add the disk resource to
the SQL Resource group if it is not there already. Make the SQL Service
dependent on the additionan disk resource (just like the existing disk.)
You will have to stop SQL to change the dependencies.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D52638EE-3297-4D6E-9971-7B7759DDD7A6@.microsoft.com...
> We have a Win2003 EE A/P cluster running on a DELL/EMC CX300. There are 2
> logical disks defined outside of the quorum. When I install the Virtual
> server, I select the parent group of the disks which happens to be the SQL
> Server resource group which contains all resources except the clsuter
> specific items. When I finish, I can only see one logical disk from
> within
> EM even though both are on the node I am working from . Any thoughts on
> how
> to see multiple logical disks?

Cluster Install

We have a Win2003 EE A/P cluster running on a DELL/EMC CX300. There are 2
logical disks defined outside of the quorum. When I install the Virtual
server, I select the parent group of the disks which happens to be the SQL
Server resource group which contains all resources except the clsuter
specific items. When I finish, I can only see one logical disk from within
EM even though both are on the node I am working from . Any thoughts on how
to see multiple logical disks?Starting with a LUN that is a cluster resource. Add the disk resource to
the SQL Resource group if it is not there already. Make the SQL Service
dependent on the additionan disk resource (just like the existing disk.)
You will have to stop SQL to change the dependencies.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D52638EE-3297-4D6E-9971-7B7759DDD7A6@.microsoft.com...
> We have a Win2003 EE A/P cluster running on a DELL/EMC CX300. There are 2
> logical disks defined outside of the quorum. When I install the Virtual
> server, I select the parent group of the disks which happens to be the SQL
> Server resource group which contains all resources except the clsuter
> specific items. When I finish, I can only see one logical disk from
> within
> EM even though both are on the node I am working from . Any thoughts on
> how
> to see multiple logical disks?

Cluster Install

We have a Win2003 EE A/P cluster running on a DELL/EMC CX300. There are 2
logical disks defined outside of the quorum. When I install the Virtual
server, I select the parent group of the disks which happens to be the SQL
Server resource group which contains all resources except the clsuter
specific items. When I finish, I can only see one logical disk from within
EM even though both are on the node I am working from . Any thoughts on how
to see multiple logical disks?
Starting with a LUN that is a cluster resource. Add the disk resource to
the SQL Resource group if it is not there already. Make the SQL Service
dependent on the additionan disk resource (just like the existing disk.)
You will have to stop SQL to change the dependencies.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:D52638EE-3297-4D6E-9971-7B7759DDD7A6@.microsoft.com...
> We have a Win2003 EE A/P cluster running on a DELL/EMC CX300. There are 2
> logical disks defined outside of the quorum. When I install the Virtual
> server, I select the parent group of the disks which happens to be the SQL
> Server resource group which contains all resources except the clsuter
> specific items. When I finish, I can only see one logical disk from
> within
> EM even though both are on the node I am working from . Any thoughts on
> how
> to see multiple logical disks?
sqlsql

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 TVF Error: Msg 6260, Level 16, State 1, Line 1

Dear All, I always got this error in CLR TVF:

Msg 6260, Level 16, State 1, Line 1
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidOperationException: Invalid attempt to FieldCount when reader is closed.
System.InvalidOperationException:
at System.Data.SqlClient.SqlDataReaderSmi.get_FieldCount()
at System.Data.Common.DbEnumerator.BuildSchemaInfo()
at System.Data.Common.DbEnumerator.MoveNext()

Here is my code:

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Collections;

public static class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "rowfiller",DataAccess=DataAccessKind.Read,TableDefinition = "ActID int, ActName nvarchar(50), ActCreatorID int,ActDesp nvarchar(200),ActCreateDate datetime,ActModifyDate datetime, ActStartDate datetime, ActEndDate datetime, Status int, Cost int")]
public static IEnumerable Func_GetSchCatActivityIDTable(int CatActivityID)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
string sqlstring = "select * from Activity where CatActivityID=@.CatActivityID;";

connection.Open();
SqlCommand command = new SqlCommand(sqlstring, connection);
command.Parameters.AddWithValue("@.CatActivityID", CatActivityID);

return command.ExecuteReader(CommandBehavior.CloseConnection);

}
}

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft Performance","CA1811:AvoidUncalledPrivateCode")]
public static void rowfiller(Object obj,
out SqlInt32 ActID,
out SqlString ActName,
out SqlInt32 ActCreatorID,
out SqlString ActDesp,
out SqlDateTime ActCreateDate,
out SqlDateTime ActModifyDate,
out SqlDateTime ActStartDate,
out SqlDateTime ActEndDate,
out SqlInt32 Status,
out SqlInt32 Cost,
)
{

SqlDataRecord row = (SqlDataRecord)obj;
int column = 0;

ActID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
ActName = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
ActCreatorID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
ActDesp = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
ActCreateDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActModifyDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActStartDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActEndDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
Status = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
Cost = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
}

};

Can anyone tell me what I am doing wrong? Many thanks

.

You can not do data access from a Data Reader in a CLR TVF. Basically, after accessing the first row in the reader the data reader closes down.

Niels
|||Just a correction, as soon as you leave the TVF method the datareader closes down - you won't be able to get even the first row.

Niels
|||

Hi, Niels

Thanks for your responding. Do you have any idea how to get arround this problem, if I do need a table value by query other tables?

|||Use T-SQL!! T-SQL is much, much better than CLR for in-database data access anyway. From your code above I ca not see any reason per se to use CLR (however, there may be more stuff going on than what you show in your code).

Niels

CLR trigger on another schema

Hi,

I'm trying to write a clr trigger. I have a table under a user defined schema, say Myschema.Table1. If I write something like this

[SqlTrigger(Name = "Trigger1", Target = "Myschema.Table1", Event = "FOR INSERT")]
I got this error when deploying my assembly on SQl server:
Cannot find the object "Myschema.Table1" because it does not exist or you do not have permissions

If I move Table1 in the dbo schema the assembly is deployed succesfully
Can someone help me

Thank you

This is a known bug. You will need to manually deploy the trigger to the non-dbo schema.

CLR Defined Types

All read up and ready to create a database project only to find we dont have
"database" as an option for a project type.
Any clues why?
we have SQL 2005 running as well as running SQL reporting services.
ThanksIt's not available in every edition of VS. Only Pro and Team, I believe.
Are you running one of those?
If not, you still have options. Check out Niels Burglund's add-in tools
here:
http://sqljunkies.com/WebLog/nielsb...2/07/17967.aspx
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:C75961DB-0314-4629-B609-DF739B336FD2@.microsoft.com...
> All read up and ready to create a database project only to find we dont
> have
> "database" as an option for a project type.
> Any clues why?
> we have SQL 2005 running as well as running SQL reporting services.
> Thanks|||Pro Yes
Team No
I am thinking it was an option during the installation but whoever installed
it decided we didnt want all the features possible.
"Adam Machanic" wrote:

> It's not available in every edition of VS. Only Pro and Team, I believe.
> Are you running one of those?
> If not, you still have options. Check out Niels Burglund's add-in tools
> here:
> http://sqljunkies.com/WebLog/nielsb...2/07/17967.aspx
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Sean" <Sean@.discussions.microsoft.com> wrote in message
> news:C75961DB-0314-4629-B609-DF739B336FD2@.microsoft.com...
>
>