Cluster Issue

Hello,
I have a Wndows 2003 Sql 2005 Cluster consisting of two servers, on Failover
from node2 to node1 the application user is unable to login until the sql
server is restarted, on failover all the resourses come online but the user
is unable to access the database and if I look in SQl Server management
studio I am unable to see the properties of the DB, if I initiate a failure
of SQL server in the cluster I am still unable to access the DB but if I
restart SQL server in management studio the database comes online.
Also when I do a failover to node2, it shows all resourses are online but
the application cannot access the DB like it is not there, but I can access
the Quorum and the shared DB drive.
Thanks in advance,
Bob Smith
Definitely a problem. Check the Application log on either node (Event
logging on a cluster is cluster-wide to each node) to see if SQL is
complaining about starting up.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Bob Smith" <BobSmith@.discussions.microsoft.com> wrote in message
news:F586AE13-D88E-4893-A6A3-0465392C13F2@.microsoft.com...
> Hello,
> I have a Wndows 2003 Sql 2005 Cluster consisting of two servers, on
> Failover
> from node2 to node1 the application user is unable to login until the sql
> server is restarted, on failover all the resourses come online but the
> user
> is unable to access the database and if I look in SQl Server management
> studio I am unable to see the properties of the DB, if I initiate a
> failure
> of SQL server in the cluster I am still unable to access the DB but if I
> restart SQL server in management studio the database comes online.
> Also when I do a failover to node2, it shows all resourses are online but
> the application cannot access the DB like it is not there, but I can
> access
> the Quorum and the shared DB drive.
> Thanks in advance,
> Bob Smith
|||Hi Geoff,
Thanks for the reply, the only thing I can see is logwriter errors, which I
googled and found more questions than answers, let me ask this: Can the
Quroum and the physical disk be in different groups or should the be in the
same, mine is set the the Quorum is in the Cluster group and the Physical
disk is in group 0. By the way I see these errors on both nodes. What is best
practice for the cluster group, what resourses should exist in it: Cluster
IP, Cluster Name, MSDTC -?, Quorum -?
Thanks again,
Bob Smith
"Geoff N. Hiten" wrote:

> Definitely a problem. Check the Application log on either node (Event
> logging on a cluster is cluster-wide to each node) to see if SQL is
> complaining about starting up.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Bob Smith" <BobSmith@.discussions.microsoft.com> wrote in message
> news:F586AE13-D88E-4893-A6A3-0465392C13F2@.microsoft.com...
>
|||Quorum must be in the cluster group. It is the reason the group exists.
The Quorum disk prevents split-brain problems. The Cluster group should
also have an IP Address and a Network Name Resource.
You can put the MSDTC resource in there too, but it can cause problems on
some very high volume systems.
All SQL resources should be in a different group. Note that disks are
physical resources. Logical OS partitions of the same disk cannot be split
into multiple groups.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Bob Smith" <BobSmith@.discussions.microsoft.com> wrote in message
news:1BD77D34-DC9D-4CB6-8325-9D32F50C9B58@.microsoft.com...[vbcol=seagreen]
> Hi Geoff,
> Thanks for the reply, the only thing I can see is logwriter errors, which
> I
> googled and found more questions than answers, let me ask this: Can the
> Quroum and the physical disk be in different groups or should the be in
> the
> same, mine is set the the Quorum is in the Cluster group and the Physical
> disk is in group 0. By the way I see these errors on both nodes. What is
> best
> practice for the cluster group, what resourses should exist in it: Cluster
> IP, Cluster Name, MSDTC -?, Quorum -?
> Thanks again,
> Bob Smith
> "Geoff N. Hiten" wrote:
|||Geoff,
Thanks, that is how it is setup Cluster group (Cluster IP, Cluster Name,
Disk Q and MSDTC) all other resourses are in another group, I am still
confused as too why SQL says it is online but the user/app cannot connect,
by restarting SQL server in studion management it comes online.
Thanks again,
Bob Smith
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23VBACR5VIHA.4140@.TK2MSFTNGP04.phx.gbl...
> Quorum must be in the cluster group. It is the reason the group exists.
> The Quorum disk prevents split-brain problems. The Cluster group should
> also have an IP Address and a Network Name Resource.
> You can put the MSDTC resource in there too, but it can cause problems on
> some very high volume systems.
> All SQL resources should be in a different group. Note that disks are
> physical resources. Logical OS partitions of the same disk cannot be
> split into multiple groups.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
>
> "Bob Smith" <BobSmith@.discussions.microsoft.com> wrote in message
> news:1BD77D34-DC9D-4CB6-8325-9D32F50C9B58@.microsoft.com...
>
|||Maybe an IP address conflict or a dependency error. Again, the application
log should say something.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Bob Smith" <bob@.neconsulting.net> wrote in message
news:4E7A31CB-2900-4ED3-A7A9-1B0800B2128C@.microsoft.com...
> Geoff,
> Thanks, that is how it is setup Cluster group (Cluster IP, Cluster Name,
> Disk Q and MSDTC) all other resourses are in another group, I am still
> confused as too why SQL says it is online but the user/app cannot connect,
> by restarting SQL server in studion management it comes online.
> Thanks again,
> Bob Smith
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23VBACR5VIHA.4140@.TK2MSFTNGP04.phx.gbl...
>

Cluster Failover

We have a 3rd party vendor application that runs on IIS and connects to a
back end SQL Cluster. Sometimes a user will try and run a commission report
and the SQL server takes a big hit on the CPU's (Dual 2.0 Ghz Xeon's) and I
will get a error in MOM 2005 that the "Server Performance Thresholds
SQLSERVR Process > 90% CPU for 15 minutes" Of course the end user will try
and run it two or three more times taking up more resources.
If I do a fail over to the other SQL server in the cluster the CPU's will
drop down to their normal ranges and everything works fine.
My question is when performing the "failover" that 10 - 15 seconds it takes
to do this what happens to any data that is trying to write to the database?
Is it lost? And can this cause corruption in the database itself?
Thanks!
Failovers don't cause corruption. Rather, any active transactions are
rolled back when the backup node takes over from the primary node.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Scopus69" <Scopus69@.nospam.postalias> wrote in message
news:DF8080ED-0DF2-42C7-B1DA-4D9375D0B2EA@.microsoft.com...
We have a 3rd party vendor application that runs on IIS and connects to a
back end SQL Cluster. Sometimes a user will try and run a commission report
and the SQL server takes a big hit on the CPU's (Dual 2.0 Ghz Xeon's) and I
will get a error in MOM 2005 that the "Server Performance Thresholds
SQLSERVR Process > 90% CPU for 15 minutes" Of course the end user will try
and run it two or three more times taking up more resources.
If I do a fail over to the other SQL server in the cluster the CPU's will
drop down to their normal ranges and everything works fine.
My question is when performing the "failover" that 10 - 15 seconds it takes
to do this what happens to any data that is trying to write to the database?
Is it lost? And can this cause corruption in the database itself?
Thanks!
|||When you mean "rolled back" the transactions are basically held in cache
until the backup server has taken complete control? Is that correct?
One user received the following error during failover in their web session:
[DBNETLIB][ConnectionRead (recv()).]General network error. Check your
network documentation. in Microsoft OLE DB Provider for SQL Server
Is this a concern or only the period before the failover is complete?
Thanks!
"Tom Moreau" wrote:

> Failovers don't cause corruption. Rather, any active transactions are
> rolled back when the backup node takes over from the primary node.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Scopus69" <Scopus69@.nospam.postalias> wrote in message
> news:DF8080ED-0DF2-42C7-B1DA-4D9375D0B2EA@.microsoft.com...
> We have a 3rd party vendor application that runs on IIS and connects to a
> back end SQL Cluster. Sometimes a user will try and run a commission report
> and the SQL server takes a big hit on the CPU's (Dual 2.0 Ghz Xeon's) and I
> will get a error in MOM 2005 that the "Server Performance Thresholds
> SQLSERVR Process > 90% CPU for 15 minutes" Of course the end user will try
> and run it two or three more times taking up more resources.
> If I do a fail over to the other SQL server in the cluster the CPU's will
> drop down to their normal ranges and everything works fine.
> My question is when performing the "failover" that 10 - 15 seconds it takes
> to do this what happens to any data that is trying to write to the database?
> Is it lost? And can this cause corruption in the database itself?
> Thanks!
>
>
|||Not exactly. Anything in cache evaporates. Transactions are written to the
transaction log. If a COMMIT record is not written, then when SQL Server
comes up, all of the work done thus far in that transaction is backed out.
This is true whether you are using a cluster or not.
The network errors go away once SQL Server has come back up again. Clean
apps will try to reconnect.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Scopus69" <Scopus69@.nospam.postalias> wrote in message
news:49B44171-B185-463B-833E-E4190AD923A9@.microsoft.com...
When you mean "rolled back" the transactions are basically held in cache
until the backup server has taken complete control? Is that correct?
One user received the following error during failover in their web session:
[DBNETLIB][ConnectionRead (recv()).]General network error. Check your
network documentation. in Microsoft OLE DB Provider for SQL Server
Is this a concern or only the period before the failover is complete?
Thanks!
"Tom Moreau" wrote:

> Failovers don't cause corruption. Rather, any active transactions are
> rolled back when the backup node takes over from the primary node.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Scopus69" <Scopus69@.nospam.postalias> wrote in message
> news:DF8080ED-0DF2-42C7-B1DA-4D9375D0B2EA@.microsoft.com...
> We have a 3rd party vendor application that runs on IIS and connects to a
> back end SQL Cluster. Sometimes a user will try and run a commission
> report
> and the SQL server takes a big hit on the CPU's (Dual 2.0 Ghz Xeon's) and
> I
> will get a error in MOM 2005 that the "Server Performance Thresholds
> SQLSERVR Process > 90% CPU for 15 minutes" Of course the end user will
> try
> and run it two or three more times taking up more resources.
> If I do a fail over to the other SQL server in the cluster the CPU's will
> drop down to their normal ranges and everything works fine.
> My question is when performing the "failover" that 10 - 15 seconds it
> takes
> to do this what happens to any data that is trying to write to the
> database?
> Is it lost? And can this cause corruption in the database itself?
> Thanks!
>
>

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 problems with security under ADO.NET and SQL Server 2005

Ok I'm on this... in T-SQL Say I have TableA and it has no select
permissions on it for the user... but the user has a stored procedure with
execute permission on it and contents of it is SELECT * from TableA... well
of course this executes! and returns the TableA contents... where doing a
SELECT * Fromt TableA in a stand alone query returns a permission error...
now when I do a similar thing in the new CLR compiled stored procedures
using an ADO.NET Command object... with SELECT * FROM TableA it comes back
with the permission error that I cant select from tablea... why?! if i can
do it in T-SQL why can't I in a CLR stored procedure? which is more secure
to start with then a plain text T-SQL statement! I'm trying to convert some
T-SQL procs to ADO.NET's CLR store procs on SQL Server 2005 but this hitch
kinda put everything im doing at a hault because i cant do anything if i
cant get select permission! and security wise i dont want to give people
select permission on tables... anything to help with this problem? thanks!Short answer:
use EXECUTE AS OWNER clause in your CREATE PROC statement.
Longer answer:
This works by default in TSQL due to ownership chaining -- if the table and
procedure are owned by the same entity, then user permissions are not
checked against the table, just against the procedure. In Sql Server 2005,
there is a new paradigm using the EXECUTE AS clause on various DDL
statements. This allows you to specify that the code should execute with
the credentials of a particular entity, including a couple of dynamic ids
(USER, OWNER, SELF). For better security, .Net procedures execute as USER
by default. Executing as OWNER is the closest match to the TSQL default
ownership chaining.
~Alazel
Alazel Acheson
Software Developer
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian Henry" <nospam@.nospam.com> wrote in message
news:eia7VRZMGHA.2336@.TK2MSFTNGP12.phx.gbl...
> Ok I'm on this... in T-SQL Say I have TableA and it has no select
> permissions on it for the user... but the user has a stored procedure with
> execute permission on it and contents of it is SELECT * from TableA...
> well of course this executes! and returns the TableA contents... where
> doing a SELECT * Fromt TableA in a stand alone query returns a permission
> error... now when I do a similar thing in the new CLR compiled stored
> procedures using an ADO.NET Command object... with SELECT * FROM TableA it
> comes back with the permission error that I cant select from tablea...
> why?! if i can do it in T-SQL why can't I in a CLR stored procedure? which
> is more secure to start with then a plain text T-SQL statement! I'm trying
> to convert some T-SQL procs to ADO.NET's CLR store procs on SQL Server
> 2005 but this hitch kinda put everything im doing at a hault because i
> cant do anything if i cant get select permission! and security wise i dont
> want to give people select permission on tables... anything to help with
> this problem? thanks!
>|||thanks for the clarification!
"Alazel Acheson [MS]" <alazela@.online.microsoft.com> wrote in message
news:%236lVdTbMGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Short answer:
> use EXECUTE AS OWNER clause in your CREATE PROC statement.
> Longer answer:
> This works by default in TSQL due to ownership chaining -- if the table
> and procedure are owned by the same entity, then user permissions are not
> checked against the table, just against the procedure. In Sql Server
> 2005, there is a new paradigm using the EXECUTE AS clause on various DDL
> statements. This allows you to specify that the code should execute with
> the credentials of a particular entity, including a couple of dynamic ids
> (USER, OWNER, SELF). For better security, .Net procedures execute as USER
> by default. Executing as OWNER is the closest match to the TSQL default
> ownership chaining.
> --
> ~Alazel
> Alazel Acheson
> Software Developer
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Brian Henry" <nospam@.nospam.com> wrote in message
> news:eia7VRZMGHA.2336@.TK2MSFTNGP12.phx.gbl...
>

CLR Configuration option problem

Hai,
I develope one application using .Net framework 2.0 and sql server
2005.Some time the following message displayed
Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
configuration option
how to redtify the error
Regards
T.A.Krishna
Hi
"Krishna" wrote:

> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
>
This error normally refers to the 'CLR Integration' setting, which you can
set by using the Surface Area Configuration (SAC) tool. If you choose to set
the option by feature then under Database Engine you can see 'CLR
Integration' and the Enable CLR Integration checked box should be checked.

> how to redtify the error
> Regards
> T.A.Krishna
John
|||Krishna wrote:
> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
CLR integration is disabled by default.
You must change the configuration option:
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
Best regards,
Marcin Guzowski
http://guzowski.info

CLR Configuration option problem

Hai,
I develope one application using .Net framework 2.0 and sql server
2005.Some time the following message displayed
Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
configuration option
how to redtify the error
Regards
T.A.KrishnaHi
"Krishna" wrote:
> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
>
This error normally refers to the 'CLR Integration' setting, which you can
set by using the Surface Area Configuration (SAC) tool. If you choose to set
the option by feature then under Database Engine you can see 'CLR
Integration' and the Enable CLR Integration checked box should be checked.
> how to redtify the error
> Regards
> T.A.Krishna
John|||Krishna wrote:
> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
CLR integration is disabled by default.
You must change the configuration option:
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
Best regards,
Marcin Guzowski
http://guzowski.info

CLR Configuration option problem

Hai,
I develope one application using .Net framework 2.0 and sql server
2005.Some time the following message displayed
Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
configuration option
how to redtify the error
Regards
T.A.KrishnaHi
"Krishna" wrote:

> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
>
This error normally refers to the 'CLR Integration' setting, which you can
set by using the Surface Area Configuration (SAC) tool. If you choose to set
the option by feature then under Database Engine you can see 'CLR
Integration' and the Enable CLR Integration checked box should be checked.

> how to redtify the error
> Regards
> T.A.Krishna
John|||Krishna wrote:
> Hai,
> I develope one application using .Net framework 2.0 and sql server
> 2005.Some time the following message displayed
> Exceution of user code .Net framework is disabled .Enabled 'clr' emabled
> configuration option
CLR integration is disabled by default.
You must change the configuration option:
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
Best regards,
Marcin Guzowski
http://guzowski.info

Client user timeout

Hi,
Used Mssql 5 exp. On win 2003 server
my problem,
client user time out 15 minute by sql server,
Client user inside my program, connection closed,
logout program and relogin program work again,
no network problem, no closed network connection
How can i do,There are several reasons depending upon how the connection process is set
up. For a brief review see: http://vyaskn.tripod.com/watch_your_timeouts.htm
Anith

Client side text handling

I have a text/excel file on client machines. user ( at client side) will be uploading it to Oracle database tables on the server using a client side procedure. I have Oracle client 8.0 loaded on the user/client machines. How can this be achieved. Need something like utl_file. (I am not using oracle Forms or reports so I cannot use text_io)Will SQL Loader not do it? If necessary, you could load into a temporary table and then process in PL/SQL.|||No , It will not serve the purpose as I have to modify & add some contents of the file after some validation. Only then I'll be attaching & sending the same through my client email.

Client Print To Dot Matrix Printer (Urgent)

I'm using ActiveX control to print report in client side, it's great for user. However, I have some reports which need to be printed to dotmatrix printer (Epson LQ 1600K III), they chould not be printed properly, all the fonts are always indouble width and half height.
But if I print those report to my laser printer or print them to dotmatrix printer in VS.NET, it's OK. See as below:
Print on laser priner
******************************
* * *
* * *
* *
* *
* *
******************************
Print on dotmatrix priner
======================================
= = =
= = =
= =
======================================

Is it a microsoft bug? Should I set something in my report or in printer driver (Printer Processer)?
Thanks in advance!

I'm having the same issue, did you get it working? Also wcich ActiveX control are you using?
Thanks
ma_flash

|||

No, I still have this issue, whatever I changed the processer in printer driver, it did not work.
I'm using new ActiveX Control in Reporting Service SP2, it is downloaded from report server when first time user load report. I think it is not consistent with VS.NET, because I can print reports to dotmatrix printer in VS.NET propoerly, but not in ActiveX Control.

|||

Can you kindly share the code of how to stream a report server directly to your local printer?

Thanks, Edmund

Client licensing

My apologies if this is the wrong newsgroup... I couldn't find the most
appropriate one.
Does a client access license allow a user to access any # of SQL Servers
deployed in my enterprise or do I need a CAL / user / server?
Thx.
Hi,
try following:
http://www.microsoft.com/sql/howtobuy/default.mspx#EEAA
Danijel Novak
"Drew" <nodirectemails@.nospam.com> wrote in message
news:%23UodxSRBGHA.2912@.tk2msftngp13.phx.gbl...
> My apologies if this is the wrong newsgroup... I couldn't find the most
> appropriate one.
> Does a client access license allow a user to access any # of SQL Servers
> deployed in my enterprise or do I need a CAL / user / server?
> Thx.
>
|||Thanks!
"Danijel Novak" <danijel.novak@.triera.net> wrote in message
news:ejmYUqTBGHA.3352@.TK2MSFTNGP09.phx.gbl...
> Hi,
> try following:
> http://www.microsoft.com/sql/howtobuy/default.mspx#EEAA
> --
> Danijel Novak
>
> "Drew" <nodirectemails@.nospam.com> wrote in message
> news:%23UodxSRBGHA.2912@.tk2msftngp13.phx.gbl...
>

Client app needs to know when data has changed

Hi all,
I am developing a multi user application using Delphi. Currently I am using
a database engine that is terrible. I am considering switching over to SQL
Server. One of the features that I could really use is for the client
applications to be notified when data changed. That is, if one client adds,
deletes or modifies a record, I need the other clients to be notified of
this. Currently, when a client makes a change, I alter a record in a
"changes" table. The other clients poll this table every 10 seconds to
determine if a change has been made. I want to get away from this
poling...
Can SQL Server accomplish this? If yes, which edition?
Thank you
--
Joseph I. Ceasar
CLS Computer SolutionsAn aspect of the new ADO.NET v2.0 is "Query Notifications" and this is fully
implemented by SQLServer-2005 (due to ship w/b 7 Nov 2005). More details on
msdn.microsoft.com/SQL/2005/dataaccess/default.aspx?pull=/library/en-us/dnvs
05/html/querynotification.asp
There is a good CTP (effectively Beta-4) to get early experience on at MS
site msdn.microsoft.com/SQL/2005/default.aspx
HTH
Dick
"Joseph I. Ceasar" wrote:

> Hi all,
> I am developing a multi user application using Delphi. Currently I am usi
ng
> a database engine that is terrible. I am considering switching over to SQ
L
> Server. One of the features that I could really use is for the client
> applications to be notified when data changed. That is, if one client add
s,
> deletes or modifies a record, I need the other clients to be notified of
> this. Currently, when a client makes a change, I alter a record in a
> "changes" table. The other clients poll this table every 10 seconds to
> determine if a change has been made. I want to get away from this
> poling...
> Can SQL Server accomplish this? If yes, which edition?
> Thank you
>
> --
> --
> Joseph I. Ceasar
> CLS Computer Solutions
>
>|||Very interesting.....
I check out that document. It does say that this technique should not be
used for data that changes very frequently. Does anyone have any idea of
what technique to use for data that does change frequently?
"Dick in UK" <Dick in UK@.discussions.microsoft.com> wrote in message
news:F9170DEA-6FC4-4CFB-82FA-7180106BD34D@.microsoft.com...
> An aspect of the new ADO.NET v2.0 is "Query Notifications" and this is
> fully
> implemented by SQLServer-2005 (due to ship w/b 7 Nov 2005). More details
> on
> msdn.microsoft.com/SQL/2005/dataaccess/default.aspx?pull=/library/en-us/dn
vs05/html/querynotification.asp
> There is a good CTP (effectively Beta-4) to get early experience on at MS
> site msdn.microsoft.com/SQL/2005/default.aspx
> HTH
> Dick
> "Joseph I. Ceasar" wrote:
>|||You can't eat the cake and have it. If you want to be notified, it would be
hard to write your own
with lower resource consumption than Query Notifications (based on the smart
technique that QN
uses). If that is to steep for you (too frequent data changes, use a polling
technique. A polling
technique can show stale data, which is why it *can* be less resource intens
ive in a highly changing
environment.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
news:O%23c7aoDxFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Very interesting.....
> I check out that document. It does say that this technique should not be
used for data that
> changes very frequently. Does anyone have any idea of what technique to u
se for data that does
> change frequently?
> "Dick in UK" <Dick in UK@.discussions.microsoft.com> wrote in message
> news:F9170DEA-6FC4-4CFB-82FA-7180106BD34D@.microsoft.com...
>

Client app needs to know when data has changed

Hi all,
I am developing a multi user application using Delphi. Currently I am using
a database engine that is terrible. I am considering switching over to SQL
Server. One of the features that I could really use is for the client
applications to be notified when data changed. That is, if one client adds,
deletes or modifies a record, I need the other clients to be notified of
this. Currently, when a client makes a change, I alter a record in a
"changes" table. The other clients poll this table every 10 seconds to
determine if a change has been made. I want to get away from this
poling...
--
Joseph I. Ceasar
CLS Computer SolutionsJoseph,
Consider using a trigger?
HTH
Jerry
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I am developing a multi user application using Delphi. Currently I am
> using
> a database engine that is terrible. I am considering switching over to
> SQL
> Server. One of the features that I could really use is for the client
> applications to be notified when data changed. That is, if one client
> adds,
> deletes or modifies a record, I need the other clients to be notified of
> this. Currently, when a client makes a change, I alter a record in a
> "changes" table. The other clients poll this table every 10 seconds to
> determine if a change has been made. I want to get away from this
> poling...
>
> --
> --
> Joseph I. Ceasar
> CLS Computer Solutions
>|||Look into SQL Notification Services. Sounds like this is what you need.
ML|||Do you really need that? It's been my experience that detecting changes
made by other users when a modification is submitted is sufficient.
When the user presses the OK button, a stored procedure is called to commit
the change. If the row to be updated has changed, then the stored procedure
indicates in its return value that it cannot complete the update and why,
and returns the current values in output parameters. In this way you can
inform the user what changed, possibly by whom (of course, in order to do
that you need to record who made the last change within each row). You also
have access to the user's changes so no information is lost in the process.
I load a tool tip with the user's changes when they're different from what's
been returned by the stored procedure. That way, instead of simply
discarding the user's changes, they're available to the user so that he can
decide to either discard his changes, or rekey and apply them. In short,
the user performs the conflict resolution task instead of producing some
exception report and fixing the problem later.
Polling for changes every 10 seconds or registering an event handler in some
middle-tier component does not eliminate the possibility that a change could
be made by another user between polls or while an earlier event is being
processed. The exception handling mechanism described above must still
exist. I think that it would be really annoying, if not dangerous, for a
user filling out a form to be interrupted and/or to have his changes
overridden because some yahoo down the hall updated the same row. Waiting
until the form is filled out, and giving the user the ability to resolve any
conflicts that occur just makes more sense to me.
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I am developing a multi user application using Delphi. Currently I am
> using
> a database engine that is terrible. I am considering switching over to
> SQL
> Server. One of the features that I could really use is for the client
> applications to be notified when data changed. That is, if one client
> adds,
> deletes or modifies a record, I need the other clients to be notified of
> this. Currently, when a client makes a change, I alter a record in a
> "changes" table. The other clients poll this table every 10 seconds to
> determine if a change has been made. I want to get away from this
> poling...
>
> --
> --
> Joseph I. Ceasar
> CLS Computer Solutions
>|||a trigger and . . .
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eJ9Ipd6wFHA.2252@.TK2MSFTNGP09.phx.gbl...
> Joseph,
> Consider using a trigger?
> HTH
> Jerry
> "Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
> news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.phx.gbl...
>|||another component(s). :-) Depends on the situation and the requirments.
"JT" <someone@.microsoft.com> wrote in message
news:uvk6b16wFHA.2652@.TK2MSFTNGP14.phx.gbl...
>a trigger and . . .
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eJ9Ipd6wFHA.2252@.TK2MSFTNGP09.phx.gbl...
>|||When you say that your database engine is terrible, does this mean that you
are wanting to migrate from Oracle or MySQL to SQL Server?
Also, explain a little more about the application and how it would use the
information that data has been changed by another user. If your client is
using connected ADO recordsets, then perhaps a dynamic cursor?
http://msdn.microsoft.com/library/d...perty_oledb.asp
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I am developing a multi user application using Delphi. Currently I am
> using
> a database engine that is terrible. I am considering switching over to
> SQL
> Server. One of the features that I could really use is for the client
> applications to be notified when data changed. That is, if one client
> adds,
> deletes or modifies a record, I need the other clients to be notified of
> this. Currently, when a client makes a change, I alter a record in a
> "changes" table. The other clients poll this table every 10 seconds to
> determine if a change has been made. I want to get away from this
> poling...
>
> --
> --
> Joseph I. Ceasar
> CLS Computer Solutions
>|||Normally I would agree with this procedure. The specific case that I am
dealing with needs the info "Pushed" to the client. It's a scheduling
application. If a user creates an appointment, I need all the other users
to see that new appointment. Informing a user that a certain time-block is
taken is of no use, since I allow double booking. There are only 2
solutions here. Poling or being notified that something changed.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%23pzmrz6wFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Do you really need that? It's been my experience that detecting changes
> made by other users when a modification is submitted is sufficient.
> When the user presses the OK button, a stored procedure is called to
> commit the change. If the row to be updated has changed, then the stored
> procedure indicates in its return value that it cannot complete the update
> and why, and returns the current values in output parameters. In this way
> you can inform the user what changed, possibly by whom (of course, in
> order to do that you need to record who made the last change within each
> row). You also have access to the user's changes so no information is
> lost in the process. I load a tool tip with the user's changes when
> they're different from what's been returned by the stored procedure. That
> way, instead of simply discarding the user's changes, they're available to
> the user so that he can decide to either discard his changes, or rekey and
> apply them. In short, the user performs the conflict resolution task
> instead of producing some exception report and fixing the problem later.
> Polling for changes every 10 seconds or registering an event handler in
> some middle-tier component does not eliminate the possibility that a
> change could be made by another user between polls or while an earlier
> event is being processed. The exception handling mechanism described
> above must still exist. I think that it would be really annoying, if not
> dangerous, for a user filling out a form to be interrupted and/or to have
> his changes overridden because some yahoo down the hall updated the same
> row. Waiting until the form is filled out, and giving the user the
> ability to resolve any conflicts that occur just makes more sense to me.
> "Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
> news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.phx.gbl...
>|||I've never used Notification Services, but that sounds like a place to
start.
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message
news:%23KrUB86wFHA.3180@.TK2MSFTNGP14.phx.gbl...
> Normally I would agree with this procedure. The specific case that I am
> dealing with needs the info "Pushed" to the client. It's a scheduling
> application. If a user creates an appointment, I need all the other users
> to see that new appointment. Informing a user that a certain time-block
> is taken is of no use, since I allow double booking. There are only 2
> solutions here. Poling or being notified that something changed.
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:%23pzmrz6wFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||SQL Server 2005 will have a feature called "Query Notification" (not the sam
e as Notification
Services). You can be notified immediately if a change is made that might af
fect the rows that has
been returned by your SELECT statement (including new rows that qualifies).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joseph I. Ceasar" <jic@.pipeline.com> wrote in message news:eKMgHa6wFHA.3644@.TK2MSFTNGP11.p
hx.gbl...
> Hi all,
> I am developing a multi user application using Delphi. Currently I am usi
ng
> a database engine that is terrible. I am considering switching over to SQ
L
> Server. One of the features that I could really use is for the client
> applications to be notified when data changed. That is, if one client add
s,
> deletes or modifies a record, I need the other clients to be notified of
> this. Currently, when a client makes a change, I alter a record in a
> "changes" table. The other clients poll this table every 10 seconds to
> determine if a change has been made. I want to get away from this
> poling...
>
> --
> --
> Joseph I. Ceasar
> CLS Computer Solutions
>

Clicking on aggregations and displaying transactions?

Greetings SSAS Gurus,

Please please help?

How would I set up my SSAS cube so that when the user clicks on an aggregate, that the transactions that comprise that aggregate, are displayed?

Also, how do I control what is displayed, both in terms of field labels (e.g. labeling the data "Account Balance" instead of "acct_bal_amt", and data values (e.g. seeing "$1,234.56" instead of "1234.56").

Any guidance would be appreciated, even if at a minimum, it consisted of advising me what the topic is called in SSAS so that I can (try:) ) and do the research myself?

In anticipation and appreciation

email: gwithers at jhancock dot com

Greg Withers

In SSAS this concept is called DrillThrough. Depending on whether you use client which support Drillthrough actions (such as Excel 2007) - then it is trivial to define it in the cube. If you are writing the MDX query generation yourself, then take a look at DRILLTHROUGH statement.|||Mosha - Thanks for pointing me in the right direction. I personally prefer being taught to fish, rather than being fed and still not knowing how :)

Clearing Single User Mode/lock

What is the best way to clear Single User mode from SQL Server 2000?

I have tried going to all tasks and selecting detach and clear Connections using this database. I then click cancel and go into the properties and options and clear the Restrict access check box. This seems to work some times but not all the times. I think it is because something else connects to it to it while I am going through the above steps. As a last resort I have detached the database and re-attach it.

What is causing the single user mode to happen in the first place?

I am a little confused, the system is going into single user mode of its own accord and you want to get it back out again? Have you looked at the error log to find out why it is going into Single User?

This will set a database into single user and then back out again;

ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

|||

The error log has the following

DBCC CHECKDB (ANS, repair_fast) executed by zz101zz found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 9 seconds.

I don't see any other entrys or errors.

|||
Does script I gave you bring it out?|||I will try it next time it goes into single user mode. Thanks!|||

One of my coworkers tried the script and batch file but said that he got a message that it couldn't be done when database is already open.

ALTER DATABASE ANS
SET MULTI_USER;
GO

echo off
Echo *** This bat will attempt to Clear Single User Mode on ANS Database ***
echo *** This hasn't been tested yet ***
echo *** If you are unsure CTRL C will get you out ***
Pause
osql -S Server -U -P -i ClearSingleUserModeOn-ANS_Database.sql
Pause

|||Please post the exact error message|||

Exact Message is

Msg 5064, Level 16, State 1, Server OHP03028, Line 1
Changes to the state or options of database 'ANS' cannot be made at this time.
The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Server OHP03028, Line 1
ALTER DATABASE statement failed.

|||So, you should find this user's spid and kill his connection by KILL command, and then you will be able to change the database state.|||

I'm having the same problem... and tried to kill any connection to the database from the activity monitor... but did not find any connection to it...