change isolation level server wide

Just curious to know how one can change isolation level server wide ? I know
the default is read committed but if i wanted any other isolation level, how
may one do so ? Using SQL 2000You can only change it at session level, using the SET TRANSACTION ISOLATION
LEVEL command.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uxGOCqebEHA.3684@.TK2MSFTNGP09.phx.gbl...
Just curious to know how one can change isolation level server wide ? I know
the default is read committed but if i wanted any other isolation level, how
may one do so ? Using SQL 2000|||Hi Hassan
Unfortunately, there is no way to do this server wide. It must be set in
each connection for which you want to change from the default.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uxGOCqebEHA.3684@.TK2MSFTNGP09.phx.gbl...
> Just curious to know how one can change isolation level server wide ? I
know
> the default is read committed but if i wanted any other isolation level,
how
> may one do so ? Using SQL 2000
>

change isolation level server wide

Just curious to know how one can change isolation level server wide ? I know
the default is read committed but if i wanted any other isolation level, how
may one do so ? Using SQL 2000
Hi Hassan
Unfortunately, there is no way to do this server wide. It must be set in
each connection for which you want to change from the default.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uxGOCqebEHA.3684@.TK2MSFTNGP09.phx.gbl...
> Just curious to know how one can change isolation level server wide ? I
know
> the default is read committed but if i wanted any other isolation level,
how
> may one do so ? Using SQL 2000
>
|||You can only change it at session level, using the SET TRANSACTION ISOLATION
LEVEL command.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uxGOCqebEHA.3684@.TK2MSFTNGP09.phx.gbl...
Just curious to know how one can change isolation level server wide ? I know
the default is read committed but if i wanted any other isolation level, how
may one do so ? Using SQL 2000

change isolation level server wide

Just curious to know how one can change isolation level server wide ? I know
the default is read committed but if i wanted any other isolation level, how
may one do so ? Using SQL 2000Hi Hassan
Unfortunately, there is no way to do this server wide. It must be set in
each connection for which you want to change from the default.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uxGOCqebEHA.3684@.TK2MSFTNGP09.phx.gbl...
> Just curious to know how one can change isolation level server wide ? I
know
> the default is read committed but if i wanted any other isolation level,
how
> may one do so ? Using SQL 2000
>|||You can only change it at session level, using the SET TRANSACTION ISOLATION
LEVEL command.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uxGOCqebEHA.3684@.TK2MSFTNGP09.phx.gbl...
Just curious to know how one can change isolation level server wide ? I know
the default is read committed but if i wanted any other isolation level, how
may one do so ? Using SQL 2000sql

change grouping at runtime

I have a report with a large result set that can be grouped multiple ways.
Is there a way to give a user the option to change the grouping level at
runtime. For example if my data was grouped like this:
A
B
C
C
B
C
C
But instead of seeing the grouping broken up by "B", the user wants to view
the data without the "B" groups. They could have a drop-down list or
something that lets them switch it to be:
A
C
C
C
C
Hope this makes sense and someone has an idea.
--
Patrick StadlerHi,
I have the same functionality in some of my reports and what I end-up doing
is create a grouping parameter for my reports and use this parameter.value to
group my data within my report table ...
HTH,
Eric
"Stads" wrote:
> I have a report with a large result set that can be grouped multiple ways.
> Is there a way to give a user the option to change the grouping level at
> runtime. For example if my data was grouped like this:
> A
> B
> C
> C
> B
> C
> C
> But instead of seeing the grouping broken up by "B", the user wants to view
> the data without the "B" groups. They could have a drop-down list or
> something that lets them switch it to be:
> A
> C
> C
> C
> C
> Hope this makes sense and someone has an idea.
> --
> Patrick Stadlersql

change default value XACT_ABORT at instacle level

Hi,

How can I changed default value for XACT_ABORT at instacle level?

Thanks.

Faheem Ansari

You may use sp_configure to set up. Follow these steps:

exec sp_configure 'show advanced options',1

go

RECONFIGURE'

gp

exec sp_configure 'user options',16384

go

RECONFIGURE

go

Note :

1. 16384 is the option ID for XACT_ABORT (0x4000)

2. You may have already configured some value using sp_configure before, then please make sure you are adding them up. For example, you already set the user option to be 1024 (ANSI_NULL_DFLT_ON), then you need to use

exec sp_configure 'user options',17408

Hope this helps.

Ping

Causing query Timeout at the StoredProc level

I've been looking at this problem for a while now and I want to get
some other insights.
I have some mysterious "hanging SPIDS" happening on my system from a
web based application if the users press the back button or refresh.
As if the connection pool does not clear the connection properly. I'm
looking for a procedure level solution to this, so options in
sp_configure would be a no go. I've already looked at SET LOCK_TIMEOUT
and QUERY_WAIT options and they don't seem to do what I want. Aside
from going back to my developers I'm stumped.Going back to the developers is where you need to go, otherwise anything you
do will most likely be a hack. Connection pooling is pretty rock solid and
it is most likely issues with how they are coding the app not anything with
the connections or SQL Server.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Pawn" <TheRealPawn@.gmail.com> wrote in message
news:413eb287-0b95-43c0-8898-8fc90057dedb@.i7g2000prf.googlegroups.com...
> I've been looking at this problem for a while now and I want to get
> some other insights.
> I have some mysterious "hanging SPIDS" happening on my system from a
> web based application if the users press the back button or refresh.
> As if the connection pool does not clear the connection properly. I'm
> looking for a procedure level solution to this, so options in
> sp_configure would be a no go. I've already looked at SET LOCK_TIMEOUT
> and QUERY_WAIT options and they don't seem to do what I want. Aside
> from going back to my developers I'm stumped.

Catastrophic Failure, MSDASQL, Linked Servers and MySQL

When executing a query which joins against a large resultset from a linked server, SQL Server reports the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "foo" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "foo".

This is using SQL Server 2005 with SP2, and MyODBC version 3.51.16 (although previous versions also seem to have the same behavior).

Once the error occurs any subsequent query against the linked server (even something as simple as select top 3 * From foo...users) fails with the same error message. Queries using the OpenQuery syntax also fail with this error.

I've tried to determine if there are some error logs that might give me more insight into what's going on, but the above events don't seem to log to the ODBC trace logs. And I can't seem to figure out a way to trace the MSDASQL (i.e., oledb) events.

I initially thought there might be a problem with the MySQL myODBC driver, but the ODBC layer itself does not seem to have a problem. But the following steps indicate otherwise:
1. Create a new Linked Server to MySQL. Same error as above
2. Execute a query against the ODBC DSN directly, using a shell program. This works fine

I'm trying to clearly identify if the problem is with the MySQL driver or with the MSDASQL provider. It would appear that the latter is at fault, given the error message, and the lack of any ODBC logging.

Would anyone have any insights about what might be going on here, and if there is any possible resolution?
Here is a link to the tracing article : http://msdn2.microsoft.com/en-us/library/aa964124.aspx
MSDASQL does not produce sufficient trace information, since it was not extensively instrumented, but you might get some traces from other layers and modules - ODBC driver manager, MSDART, MSDATL3.
The ODBC driver manager contains much more ETW-enabled tracing in Vista than in the previous versions.|||

Hello Cybertoast:

Can you resolve this problem ?

I have the same error.

Thanks a lot

Daniel Hefez

Catastrophic Failure, MSDASQL, Linked Servers and MySQL

When executing a query which joins against a large resultset from a linked server, SQL Server reports the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "foo" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "foo".

This is using SQL Server 2005 with SP2, and MyODBC version 3.51.16 (although previous versions also seem to have the same behavior).

Once the error occurs any subsequent query against the linked server (even something as simple as select top 3 * From foo...users) fails with the same error message. Queries using the OpenQuery syntax also fail with this error.

I've tried to determine if there are some error logs that might give me more insight into what's going on, but the above events don't seem to log to the ODBC trace logs. And I can't seem to figure out a way to trace the MSDASQL (i.e., oledb) events.

I initially thought there might be a problem with the MySQL myODBC driver, but the ODBC layer itself does not seem to have a problem. But the following steps indicate otherwise:
1. Create a new Linked Server to MySQL. Same error as above
2. Execute a query against the ODBC DSN directly, using a shell program. This works fine

I'm trying to clearly identify if the problem is with the MySQL driver or with the MSDASQL provider. It would appear that the latter is at fault, given the error message, and the lack of any ODBC logging.

Would anyone have any insights about what might be going on here, and if there is any possible resolution?
Here is a link to the tracing article : http://msdn2.microsoft.com/en-us/library/aa964124.aspx
MSDASQL does not produce sufficient trace information, since it was not extensively instrumented, but you might get some traces from other layers and modules - ODBC driver manager, MSDART, MSDATL3.
The ODBC driver manager contains much more ETW-enabled tracing in Vista than in the previous versions.|||

Hello Cybertoast:

Can you resolve this problem ?

I have the same error.

Thanks a lot

Daniel Hefez