excessive locking and blocking

At the last night school. there was mention of some
documentation and methods for monitoring Blocking and
locking. Please could someone point me in the direction
of these. They might have been on the SQL Server
Magazine site.
My problem is when an end of day process runs on some
occassions it will run in 1:30 and other days for some
reason it can take upto 7 hours. The workload is faily
constant. I am trying to figure out what is different.
I checked with profiler and none of the sql takes an
overly excessive time to complete. CPU is okay. However
there is some deadlocking and when I used Spotlight which
is a SQL monitoring tool the locking and blocking was
excessive. Infact at times it was registering upto
500,000 locks.
Does anyone have any suggestions of how I can monitor and
filter the locks to see what is blocking what.
Thanks Jamie...
Jamie
<http://support.microsoft.com/directo...;EN-US;Q224453>--
-- INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems (Q224453)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4f101c44ec5$99f6d1f0$a601280a@.phx.gbl...
> At the last night school. there was mention of some
> documentation and methods for monitoring Blocking and
> locking. Please could someone point me in the direction
> of these. They might have been on the SQL Server
> Magazine site.
> My problem is when an end of day process runs on some
> occassions it will run in 1:30 and other days for some
> reason it can take upto 7 hours. The workload is faily
> constant. I am trying to figure out what is different.
> I checked with profiler and none of the sql takes an
> overly excessive time to complete. CPU is okay. However
> there is some deadlocking and when I used Spotlight which
> is a SQL monitoring tool the locking and blocking was
> excessive. Infact at times it was registering upto
> 500,000 locks.
> Does anyone have any suggestions of how I can monitor and
> filter the locks to see what is blocking what.
> Thanks Jamie...
>
|||Jamie,
'INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems'
http://support.microsoft.com/?id=224453
'HOW TO: Troubleshoot Application Performance with SQL Server'
http://support.microsoft.com/?id=224587
'INF: How to Monitor SQL Server 7.0 Blocking'
http://support.microsoft.com/?id=251004
'INF: How to Monitor SQL Server 2000 Blocking'
http://support.microsoft.com/?id=271509
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4f101c44ec5$99f6d1f0$a601280a@.phx.gbl...
> At the last night school. there was mention of some
> documentation and methods for monitoring Blocking and
> locking. Please could someone point me in the direction
> of these. They might have been on the SQL Server
> Magazine site.
> My problem is when an end of day process runs on some
> occassions it will run in 1:30 and other days for some
> reason it can take upto 7 hours. The workload is faily
> constant. I am trying to figure out what is different.
> I checked with profiler and none of the sql takes an
> overly excessive time to complete. CPU is okay. However
> there is some deadlocking and when I used Spotlight which
> is a SQL monitoring tool the locking and blocking was
> excessive. Infact at times it was registering upto
> 500,000 locks.
> Does anyone have any suggestions of how I can monitor and
> filter the locks to see what is blocking what.
> Thanks Jamie...
>

excessive locking and blocking

At the last night school. there was mention of some
documentation and methods for monitoring Blocking and
locking. Please could someone point me in the direction
of these. They might have been on the SQL Server
Magazine site.
My problem is when an end of day process runs on some
occassions it will run in 1:30 and other days for some
reason it can take upto 7 hours. The workload is faily
constant. I am trying to figure out what is different.
I checked with profiler and none of the sql takes an
overly excessive time to complete. CPU is okay. However
there is some deadlocking and when I used Spotlight which
is a SQL monitoring tool the locking and blocking was
excessive. Infact at times it was registering upto
500,000 locks.
Does anyone have any suggestions of how I can monitor and
filter the locks to see what is blocking what.
Thanks Jamie...Jamie
<http://support.microsoft.com/direct...B;EN-US;Q224453>--
-- INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems (Q224453)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4f101c44ec5$99f6d1f0$a601280a@.phx
.gbl...
> At the last night school. there was mention of some
> documentation and methods for monitoring Blocking and
> locking. Please could someone point me in the direction
> of these. They might have been on the SQL Server
> Magazine site.
> My problem is when an end of day process runs on some
> occassions it will run in 1:30 and other days for some
> reason it can take upto 7 hours. The workload is faily
> constant. I am trying to figure out what is different.
> I checked with profiler and none of the sql takes an
> overly excessive time to complete. CPU is okay. However
> there is some deadlocking and when I used Spotlight which
> is a SQL monitoring tool the locking and blocking was
> excessive. Infact at times it was registering upto
> 500,000 locks.
> Does anyone have any suggestions of how I can monitor and
> filter the locks to see what is blocking what.
> Thanks Jamie...
>|||Jamie,
'INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems'
http://support.microsoft.com/?id=224453
'HOW TO: Troubleshoot Application Performance with SQL Server'
http://support.microsoft.com/?id=224587
'INF: How to Monitor SQL Server 7.0 Blocking'
http://support.microsoft.com/?id=251004
'INF: How to Monitor SQL Server 2000 Blocking'
http://support.microsoft.com/?id=271509
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4f101c44ec5$99f6d1f0$a601280a@.phx
.gbl...
> At the last night school. there was mention of some
> documentation and methods for monitoring Blocking and
> locking. Please could someone point me in the direction
> of these. They might have been on the SQL Server
> Magazine site.
> My problem is when an end of day process runs on some
> occassions it will run in 1:30 and other days for some
> reason it can take upto 7 hours. The workload is faily
> constant. I am trying to figure out what is different.
> I checked with profiler and none of the sql takes an
> overly excessive time to complete. CPU is okay. However
> there is some deadlocking and when I used Spotlight which
> is a SQL monitoring tool the locking and blocking was
> excessive. Infact at times it was registering upto
> 500,000 locks.
> Does anyone have any suggestions of how I can monitor and
> filter the locks to see what is blocking what.
> Thanks Jamie...
>

excessive locking and blocking

At the last night school. there was mention of some
documentation and methods for monitoring Blocking and
locking. Please could someone point me in the direction
of these. They might have been on the SQL Server
Magazine site.
My problem is when an end of day process runs on some
occassions it will run in 1:30 and other days for some
reason it can take upto 7 hours. The workload is faily
constant. I am trying to figure out what is different.
I checked with profiler and none of the sql takes an
overly excessive time to complete. CPU is okay. However
there is some deadlocking and when I used Spotlight which
is a SQL monitoring tool the locking and blocking was
excessive. Infact at times it was registering upto
500,000 locks.
Does anyone have any suggestions of how I can monitor and
filter the locks to see what is blocking what.
Thanks Jamie...Jamie
<http://support.microsoft.com/directory/article.asp?ID=KB;EN-US;Q224453>--
-- INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking
Problems (Q224453)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4f101c44ec5$99f6d1f0$a601280a@.phx.gbl...
> At the last night school. there was mention of some
> documentation and methods for monitoring Blocking and
> locking. Please could someone point me in the direction
> of these. They might have been on the SQL Server
> Magazine site.
> My problem is when an end of day process runs on some
> occassions it will run in 1:30 and other days for some
> reason it can take upto 7 hours. The workload is faily
> constant. I am trying to figure out what is different.
> I checked with profiler and none of the sql takes an
> overly excessive time to complete. CPU is okay. However
> there is some deadlocking and when I used Spotlight which
> is a SQL monitoring tool the locking and blocking was
> excessive. Infact at times it was registering upto
> 500,000 locks.
> Does anyone have any suggestions of how I can monitor and
> filter the locks to see what is blocking what.
> Thanks Jamie...
>|||Jamie,
'INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems'
http://support.microsoft.com/?id=224453
'HOW TO: Troubleshoot Application Performance with SQL Server'
http://support.microsoft.com/?id=224587
'INF: How to Monitor SQL Server 7.0 Blocking'
http://support.microsoft.com/?id=251004
'INF: How to Monitor SQL Server 2000 Blocking'
http://support.microsoft.com/?id=271509
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:1a4f101c44ec5$99f6d1f0$a601280a@.phx.gbl...
> At the last night school. there was mention of some
> documentation and methods for monitoring Blocking and
> locking. Please could someone point me in the direction
> of these. They might have been on the SQL Server
> Magazine site.
> My problem is when an end of day process runs on some
> occassions it will run in 1:30 and other days for some
> reason it can take upto 7 hours. The workload is faily
> constant. I am trying to figure out what is different.
> I checked with profiler and none of the sql takes an
> overly excessive time to complete. CPU is okay. However
> there is some deadlocking and when I used Spotlight which
> is a SQL monitoring tool the locking and blocking was
> excessive. Infact at times it was registering upto
> 500,000 locks.
> Does anyone have any suggestions of how I can monitor and
> filter the locks to see what is blocking what.
> Thanks Jamie...
>

Exceptions calling basic CLR stored procs...

OK to get straight to the point, is it possible to use CLR stored procs from
SQLExpress instance or from an MDF external database file ?
If the answer is "no and no" then there's no need to read on, otherwise
please read on....
I have a very simple stored proc...
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetCustomer(int customerid)
{
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand("Select * from Customer
where CustomerID = " + customerid);
SqlContext.Pipe.ExecuteAndSend(command);
}
}
I have attempted to call this from within a database within SQLExpress and
also from within an MDF external database file with results as follows....
--SQLEXPRESS
CASE----
I've switched on CLR for SQLExpress by going to the SQL Server 2005 Surface
Area Configuration selecting CLR Integration and checking "Enable CLR
integration".
When I attempt to call this stored proc within a database inside my
SQLExpress instance I get...
System.Data.SqlClient.SqlException was caught
Message="A .NET Framework error occurred during execution of user defined
routine or aggregate 'GetCustomer': \r\nSystem.InvalidOperationException:
The context connection is already in
use.\r\nSystem.InvalidOperationException: \r\n at
System.Data.SqlClient.SqlInternalConnectionSmi.Activate()\r\n at
System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnectio
nString
options, Object providerInfo, DbConnection owningConnection)\r\n at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOpti
ons
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)\r\n at
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbCon
nection
owningConnection, DbConnectionPoolGroup poolGroup)\r\n at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)\r\n at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)\r\n at
System.Data.SqlClient.SqlConnection.Open()\r\n at
Microsoft.SqlServer.Server.SqlPipe.ExecuteAndSend(SqlCommand command)\r\n
at StoredProcedures.GetCustomer(String customerid)\r\n."
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=16
LineNumber=0
Number=6522
Procedure="GetCustomer"
Server=".\\SQLExpress"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bjec
t
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader
ds, RunBehavior runBehavior, String resetOptionsString)
at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior, String method)
at
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
behavior)
at
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBeh
avior
behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)
in
E:\DEV.NET2. 0\Test\Windows\WindowsApplication1\Windo
wsApplication1\Form1.cs:
line
35
I also get this same exception when I EXEC the proc from within a Management
Studio query window or from my test windows application.
I have no problems calling
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetCustomer(int customerid)
{
SqlContext.Pipe..Pipe.Send("Hello world!" + customerid + "\n");
}
--END SQLEXPRESS
CASE----
--STANDALONE MDF
CASE----
When I attempt to call it from a standalone MDF file I get...
System.Data.SqlClient.SqlException was caught
Message="Execution of user code in the .NET Framework is disabled. Enable
\"clr enabled\" configuration option."
Source=".Net SqlClient Data Provider"
ErrorCode=-2146232060
Class=16
LineNumber=1
Number=6263
Procedure=""
Server="\\\\.\\pipe\\13D536B9-DDF4-45\\tsql\\query"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bjec
t
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader
ds, RunBehavior runBehavior, String resetOptionsString)
at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior, String method)
at
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
behavior)
at
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBeh
avior
behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)
in
E:\DEV.NET2. 0\Test\Windows\WindowsApplication1\Windo
wsApplication1\Form1.cs:
line
37
It's pretty clear I need to switch on CLR integration for my MDF but how ?
--END STANDALONE MDF
CASE----
Has anyone else encountered these problems ?
Do you have a solution ?
Thanks in advance...
MichaelWhen you use User Instance = True, you get a new (virgin) instance of SQL
Server Express. This does not have the CLR enabled which means your
application will have to enable it (at least once).
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Michael Lang" <mickblang@.keinSpam*hotmail.com> wrote in message
news:e$eBxqjNGHA.1312@.TK2MSFTNGP09.phx.gbl...
> OK to get straight to the point, is it possible to use CLR stored procs
> from SQLExpress instance or from an MDF external database file ?
> If the answer is "no and no" then there's no need to read on, otherwise
> please read on....
> I have a very simple stored proc...
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void GetCustomer(int customerid)
> {
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand command = new SqlCommand("Select * from Customer
> where CustomerID = " + customerid);
> SqlContext.Pipe.ExecuteAndSend(command);
> }
> }
> I have attempted to call this from within a database within SQLExpress and
> also from within an MDF external database file with results as
> follows....
> --SQLEXPRESS
> CASE----
> I've switched on CLR for SQLExpress by going to the SQL Server 2005
> Surface Area Configuration selecting CLR Integration and checking "Enable
> CLR integration".
> When I attempt to call this stored proc within a database inside my
> SQLExpress instance I get...
> System.Data.SqlClient.SqlException was caught
> Message="A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetCustomer': \r\nSystem.InvalidOperationException:
> The context connection is already in
> use.\r\nSystem.InvalidOperationException: \r\n at
> System.Data.SqlClient.SqlInternalConnectionSmi.Activate()\r\n at
> System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnect
ionString
> options, Object providerInfo, DbConnection owningConnection)\r\n at
> System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOp
tions
> options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
> owningConnection)\r\n at
> System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbC
onnection
> owningConnection, DbConnectionPoolGroup poolGroup)\r\n at
> System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> owningConnection)\r\n at
> System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> outerConnection, DbConnectionFactory connectionFactory)\r\n at
> System.Data.SqlClient.SqlConnection.Open()\r\n at
> Microsoft.SqlServer.Server.SqlPipe.ExecuteAndSend(SqlCommand command)\r\n
> at StoredProcedures.GetCustomer(String customerid)\r\n."
> Source=".Net SqlClient Data Provider"
> ErrorCode=-2146232060
> Class=16
> LineNumber=0
> Number=6522
> Procedure="GetCustomer"
> Server=".\\SQLExpress"
> State=1
> StackTrace:
> at System.Data.SqlClient.SqlConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bj
ect
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
> at System.Data.SqlClient.SqlDataReader.get_MetaData()
> at
> System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
> RunBehavior runBehavior, String resetOptionsString)
> at
> System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
> DbAsyncResult result)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior, String method)
> at
> System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
> behavior)
> at
> System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandB
ehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
> srcTable, IDbCommand command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)
> in
> E:\DEV.NET2. 0\Test\Windows\WindowsApplication1\Windo
wsApplication1\Form1.c
s:line
> 35
>
> I also get this same exception when I EXEC the proc from within a
> Management Studio query window or from my test windows application.
> I have no problems calling
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void GetCustomer(int customerid)
> {
> SqlContext.Pipe..Pipe.Send("Hello world!" + customerid + "\n");
> }
>
> --END SQLEXPRESS
> CASE----
> --STANDALONE MDF
> CASE----
> When I attempt to call it from a standalone MDF file I get...
> System.Data.SqlClient.SqlException was caught
> Message="Execution of user code in the .NET Framework is disabled. Enable
> \"clr enabled\" configuration option."
> Source=".Net SqlClient Data Provider"
> ErrorCode=-2146232060
> Class=16
> LineNumber=1
> Number=6263
> Procedure=""
> Server="\\\\.\\pipe\\13D536B9-DDF4-45\\tsql\\query"
> State=1
> StackTrace:
> at System.Data.SqlClient.SqlConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bj
ect
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
> at System.Data.SqlClient.SqlDataReader.get_MetaData()
> at
> System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
> RunBehavior runBehavior, String resetOptionsString)
> at
> System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
> DbAsyncResult result)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior, String method)
> at
> System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
> behavior)
> at
> System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandB
ehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
> srcTable, IDbCommand command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)
> in
> E:\DEV.NET2. 0\Test\Windows\WindowsApplication1\Windo
wsApplication1\Form1.c
s:line
> 37
>
> It's pretty clear I need to switch on CLR integration for my MDF but how ?
> --END STANDALONE MDF
> CASE----
> Has anyone else encountered these problems ?
> Do you have a solution ?
> Thanks in advance...
> Michael
>
>|||Try setting the Command object's connection property before ExecuteAndSend:
command.Connection = connection;
Hope this helps.
Dan Guzman
SQL Server MVP
"Michael Lang" <mickblang@.keinSpam*hotmail.com> wrote in message
news:e$eBxqjNGHA.1312@.TK2MSFTNGP09.phx.gbl...
> OK to get straight to the point, is it possible to use CLR stored procs
> from SQLExpress instance or from an MDF external database file ?
> If the answer is "no and no" then there's no need to read on, otherwise
> please read on....
> I have a very simple stored proc...
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void GetCustomer(int customerid)
> {
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand command = new SqlCommand("Select * from Customer
> where CustomerID = " + customerid);
> SqlContext.Pipe.ExecuteAndSend(command);
> }
> }
> I have attempted to call this from within a database within SQLExpress and
> also from within an MDF external database file with results as
> follows....
> --SQLEXPRESS
> CASE----
> I've switched on CLR for SQLExpress by going to the SQL Server 2005
> Surface Area Configuration selecting CLR Integration and checking "Enable
> CLR integration".
> When I attempt to call this stored proc within a database inside my
> SQLExpress instance I get...
> System.Data.SqlClient.SqlException was caught
> Message="A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetCustomer': \r\nSystem.InvalidOperationException:
> The context connection is already in
> use.\r\nSystem.InvalidOperationException: \r\n at
> System.Data.SqlClient.SqlInternalConnectionSmi.Activate()\r\n at
> System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnect
ionString
> options, Object providerInfo, DbConnection owningConnection)\r\n at
> System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOp
tions
> options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
> owningConnection)\r\n at
> System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbC
onnection
> owningConnection, DbConnectionPoolGroup poolGroup)\r\n at
> System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
> owningConnection)\r\n at
> System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
> outerConnection, DbConnectionFactory connectionFactory)\r\n at
> System.Data.SqlClient.SqlConnection.Open()\r\n at
> Microsoft.SqlServer.Server.SqlPipe.ExecuteAndSend(SqlCommand command)\r\n
> at StoredProcedures.GetCustomer(String customerid)\r\n."
> Source=".Net SqlClient Data Provider"
> ErrorCode=-2146232060
> Class=16
> LineNumber=0
> Number=6522
> Procedure="GetCustomer"
> Server=".\\SQLExpress"
> State=1
> StackTrace:
> at System.Data.SqlClient.SqlConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bj
ect
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
> at System.Data.SqlClient.SqlDataReader.get_MetaData()
> at
> System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
> RunBehavior runBehavior, String resetOptionsString)
> at
> System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
> DbAsyncResult result)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior, String method)
> at
> System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
> behavior)
> at
> System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandB
ehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
> srcTable, IDbCommand command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)
> in
> E:\DEV.NET2. 0\Test\Windows\WindowsApplication1\Windo
wsApplication1\Form1.c
s:line
> 35
>
> I also get this same exception when I EXEC the proc from within a
> Management Studio query window or from my test windows application.
> I have no problems calling
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void GetCustomer(int customerid)
> {
> SqlContext.Pipe..Pipe.Send("Hello world!" + customerid + "\n");
> }
>
> --END SQLEXPRESS
> CASE----
> --STANDALONE MDF
> CASE----
> When I attempt to call it from a standalone MDF file I get...
> System.Data.SqlClient.SqlException was caught
> Message="Execution of user code in the .NET Framework is disabled. Enable
> \"clr enabled\" configuration option."
> Source=".Net SqlClient Data Provider"
> ErrorCode=-2146232060
> Class=16
> LineNumber=1
> Number=6263
> Procedure=""
> Server="\\\\.\\pipe\\13D536B9-DDF4-45\\tsql\\query"
> State=1
> StackTrace:
> at System.Data.SqlClient.SqlConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bj
ect
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
> at System.Data.SqlClient.SqlDataReader.get_MetaData()
> at
> System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
> RunBehavior runBehavior, String resetOptionsString)
> at
> System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
> DbAsyncResult result)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior, String method)
> at
> System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
> behavior)
> at
> System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandB
ehavior
> behavior)
> at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset,
> DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String
> srcTable, IDbCommand command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
> at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)
> in
> E:\DEV.NET2. 0\Test\Windows\WindowsApplication1\Windo
wsApplication1\Form1.c
s:line
> 37
>
> It's pretty clear I need to switch on CLR integration for my MDF but how ?
> --END STANDALONE MDF
> CASE----
> Has anyone else encountered these problems ?
> Do you have a solution ?
> Thanks in advance...
> Michael
>
>|||Thanks...
This fixed the prob with using SQLExpress ... I did have a break point in
the stored proc, I've only just realised it wasn't hitting it for some
reason but thats another issue.
I'm still at a loss though how I enable the CLR for an MDF file.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23pyO4GkNGHA.536@.TK2MSFTNGP09.phx.gbl...
> Try setting the Command object's connection property before
> ExecuteAndSend:
> command.Connection = connection;
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Michael Lang" <mickblang@.keinSpam*hotmail.com> wrote in message
> news:e$eBxqjNGHA.1312@.TK2MSFTNGP09.phx.gbl...
>|||Hey thanks for the reply...
I did have CLR enabled for SQLExpress. I don't know of anyway to turn it on
for an MDF. I know you can set it in code for SQLExpress using...
EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
EXEC sp_configure 'show advanced options' , '1';
go
But correct me if I'm mistaken but these system procs rnt available in an
MDF. So does this mean T-SQL only for MDF database's (not hosted by an
instance of SQLServer) ?
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:eQQPd9jNGHA.3360@.TK2MSFTNGP15.phx.gbl...
> When you use User Instance = True, you get a new (virgin) instance of SQL
> Server Express. This does not have the CLR enabled which means your
> application will have to enable it (at least once).
> hth
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> __________________________________
> "Michael Lang" <mickblang@.keinSpam*hotmail.com> wrote in message
> news:e$eBxqjNGHA.1312@.TK2MSFTNGP09.phx.gbl...
>|||> I'm still at a loss though how I enable the CLR for an MDF file.
You need to enable CLR at the *instance* level, not the database level. If
you are using a user instance as Bill suggested, you'll need to enable it
during initialization of your app. This is because SQL Server Express
creates a new instance from the SQL Express template files each time you
connect with 'User Instance=true'. After connecting:
SqlCommand command = new SqlCommand(
"EXEC sp_configure 'clr enabled', 1 RECONFIGURE",
connection);
command.ExecuteNonQuery();
Hope this helps.
Dan Guzman
SQL Server MVP
"Michael Lang" <mickblang@.keinSpam*hotmail.com> wrote in message
news:Ogxd3YoNGHA.3864@.TK2MSFTNGP10.phx.gbl...
> Thanks...
> This fixed the prob with using SQLExpress ... I did have a break point in
> the stored proc, I've only just realised it wasn't hitting it for some
> reason but thats another issue.
> I'm still at a loss though how I enable the CLR for an MDF file.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23pyO4GkNGHA.536@.TK2MSFTNGP09.phx.gbl...
>|||Thanks...
This did work. I must say I find it quite strange this needs to be done in
code, when it's really a one off administrative setting.
I did actually try to call this stored proc from a query window within
VS.NET. It seems whenever I use EXEC from a query window in VS.NET (go to
server explorer, right click a Data Connection select "New Query") I get an
error "The EXEC SQL construct or statement is not supported.". Strangely
enough it does seem to continue on and call the stored proc anyhow, results
do appear for the proc. In the case of sp_configure however it does seem to
have failed... I mistakenly mistook this failure as an indication that
this system proc wasn't actually available within the "User Instance"
database file.
As you might tell I'm new to this, I'm finding that the errors and behavior
of the tools, don't seem to be quite as intuitive as they were with VS
2002/2003...
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:u5PGWruNGHA.524@.TK2MSFTNGP09.phx.gbl...
> You need to enable CLR at the *instance* level, not the database level.
> If you are using a user instance as Bill suggested, you'll need to enable
> it during initialization of your app. This is because SQL Server Express
> creates a new instance from the SQL Express template files each time you
> connect with 'User Instance=true'. After connecting:
> SqlCommand command = new SqlCommand(
> "EXEC sp_configure 'clr enabled', 1 RECONFIGURE",
> connection);
> command.ExecuteNonQuery();
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Michael Lang" <mickblang@.keinSpam*hotmail.com> wrote in message
> news:Ogxd3YoNGHA.3864@.TK2MSFTNGP10.phx.gbl...
>|||You're right. It is a bit goofy (that's a technical term I picked up in
Orlando). The new feature (User Instances) has a few wrinkles that they're
working out. The fact that you're allowed to do this at all (it's an admin
task) is testament to the way the user instance of SS is created and owned.
The other question you (and everyone) need to ask is do I have a problem
that MUST be solved by a CLR executable. There are few good reasons to use
them... just be aware of these issues before going crazy with the new shiny
feature.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Michael Lang" <mickblang@.keinSpam*hotmail.com> wrote in message
news:%23y4Fm7uNGHA.428@.tk2msftngp13.phx.gbl...
> Thanks...
> This did work. I must say I find it quite strange this needs to be done
> in code, when it's really a one off administrative setting.
> I did actually try to call this stored proc from a query window within
> VS.NET. It seems whenever I use EXEC from a query window in VS.NET (go to
> server explorer, right click a Data Connection select "New Query") I get
> an error "The EXEC SQL construct or statement is not supported.".
> Strangely enough it does seem to continue on and call the stored proc
> anyhow, results do appear for the proc. In the case of sp_configure
> however it does seem to have failed... I mistakenly mistook this failure
> as an indication that this system proc wasn't actually available within
> the "User Instance" database file.
> As you might tell I'm new to this, I'm finding that the errors and
> behavior of the tools, don't seem to be quite as intuitive as they were
> with VS 2002/2003...
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:u5PGWruNGHA.524@.TK2MSFTNGP09.phx.gbl...
>

EXCEPTION_ACCESS_VIOLATION within SQL Server when running SSIS package

Hi, I'm not sure if this is the right forum, so please point me in the right direction if it isn't.

I'm getting an EXCEPTION_ACCESS_VIOLATION and a minidump in the SQL server logs when using the OLE DB Provider for SQL Server.

The error occurs when I run an SSIS package non-interactively (using Package.Execute). The odd thing is that the package works fine when I run it from within Visual Studio. Also, this same bit of code was working fine a couple of weeks ago (it's part of my unit tests, it just started failing when I added a new, unrelated SSIS package to the project). The error occurs during the validation phase.

Here's the first part of the stack dump, which just shows that the provider is executing a very simple statement (SELECT * FROM [table-name]):

Code Snippet

* BEGIN STACK DUMP:
* 05/14/07 10:42:34 spid 56
*
*
* Exception Address = 01CBBF90 Module(sqlservr+00CBBF90)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 0000001C
* Input Buffer 108 bytes -
* ?? & ?> ff ff 02 00 00 00 00 01 26 04 00 00 00 e7 3e 00 09 04
* 2> s e l e c t 00 01 32 3e 00 73 00 65 00 6c 00 65 00 63 00 74 00 20
* * f r o m [ d 00 2a 00 20 00 66 00 72 00 6f 00 6d 00 20 00 5b 00 64
* b o ] . [ P x p T 00 62 00 6f 00 5d 00 2e 00 5b 00 50 00 78 00 70 00 54
* a r g e t ] & 00 61 00 72 00 67 00 65 00 74 00 5d 00 00 01 26 04 04
* & & 02 80 03 00 00 01 26 04 04 04 80 04 00 00 01 26 04 00
*

I presume this is something for the SQL Server team; how should I go about getting this resolved?

Cheers Rich

To report this you need to open a ticket with Microsoft PSS. Your support options can be found here https://support.microsoft.com/common/international.aspx?rdpath=1&gprid=2855.

Exception handling in Triggers.

Can anybody point me to a good text about
exception handling in triggers (errors and rollbacks) ?
From 'Inside' :
ROLLBACK (because of a fatal error or an
explicit ROLLBACK command), the entire batch is aborted.
As I read this :
From within a trigger any FK or relational constraint
violation results in a fatal error aborting the complete
batch (and complete transaction).
Is this correct ?
What if there was no transaction start ?
In the QA, does the QA supply a transaction
(increase the transaction count) if no transaction
was started ?
When sending several statements, it appears
that all statements are rolled back. (Are handled
as one transaction).
Does something similar happen when doing
similar statements from application programs ?
Thanks for your attention,
ben brugman.
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23VPXOAQkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> As I read this :
> From within a trigger any FK or relational constraint
> violation results in a fatal error aborting the complete
> batch (and complete transaction).
> Is this correct ?
Not just a constraint violation; ANY error will abort (and consequently
rollback) the entire batch.

> What if there was no transaction start ?
Every DML operation in SQL Server uses a transaction, even if you don't
explicitly request it.
So:
UPDATE Tbl
SET Val = 0
WHERE SomeCol = 1
will use a transaction. That way, if there are two rows where SomeCol =
1, and the first was successful but the second had an error, the entire
batch will roll back to the initial state.

> When sending several statements, it appears
> that all statements are rolled back. (Are handled
> as one transaction).
Do you have IMPLICIT_TRANSACTIONS turned on for your session?
This option causes SQL Server to treat multistatement batches as if they
were wrapped in a transaction. But it's not turned on by default in Query
Analyzer or any connection library that I know of...

Exception handling in Triggers.

Can anybody point me to a good text about
exception handling in triggers (errors and rollbacks) ?
From 'Inside' :
ROLLBACK (because of a fatal error or an
explicit ROLLBACK command), the entire batch is aborted.
As I read this :
From within a trigger any FK or relational constraint
violation results in a fatal error aborting the complete
batch (and complete transaction).
Is this correct ?
What if there was no transaction start ?
In the QA, does the QA supply a transaction
(increase the transaction count) if no transaction
was started ?
When sending several statements, it appears
that all statements are rolled back. (Are handled
as one transaction).
Does something similar happen when doing
similar statements from application programs ?
Thanks for your attention,
ben brugman."ben brugman" <ben@.niethier.nl> wrote in message
news:%23VPXOAQkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> As I read this :
> From within a trigger any FK or relational constraint
> violation results in a fatal error aborting the complete
> batch (and complete transaction).
> Is this correct ?
Not just a constraint violation; ANY error will abort (and consequently
rollback) the entire batch.
> What if there was no transaction start ?
Every DML operation in SQL Server uses a transaction, even if you don't
explicitly request it.
So:
UPDATE Tbl
SET Val = 0
WHERE SomeCol = 1
will use a transaction. That way, if there are two rows where SomeCol =1, and the first was successful but the second had an error, the entire
batch will roll back to the initial state.
> When sending several statements, it appears
> that all statements are rolled back. (Are handled
> as one transaction).
Do you have IMPLICIT_TRANSACTIONS turned on for your session?
This option causes SQL Server to treat multistatement batches as if they
were wrapped in a transaction. But it's not turned on by default in Query
Analyzer or any connection library that I know of...|||If we can get away from the trigger for a second.
One of the least documented aspects on how transactions
work are to do with the process id that its running from.
So if you open up QA do transaction mock statement, open
up another window in QA and do rollback you will get an
error, why, becasue the process in the second window is
different to the first.
The reason why this is important is that a rollback as you
found out will effect EVERY transaction in that process
even though it maybe nested. Try this yourself, you will
get some very interesting answers.
So to get back to your question.
SQL Server will automatically rollback transactions if the
error is serious enough.
If there is no transaction then there will be no
transaction count, there is a handy little global variable
called @.@.TRANCOUNT you may want to look at. So if that is
set to anything but a 0 it will automatically rollback the
transaction irrespective on whether it is done in trigger
or something else.
From application programs then it depends on the error. If
you had a connection object and you application (not SQL)
failed then it would be up to your application to repair
the DB by sending it up a rollback.
If you application using the same connection as the begin
transaction sent some SQL that caused an error then yes it
would automatically roll back.
Anyway that it. I sugest you have a play on QA.
If you have any questions then don't hesitate to email me
on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
busy tonight)
Peter
"You can always count on Americans to do the right thing -
after they've tried everything else."
Winston Churchill
>--Original Message--
>Can anybody point me to a good text about
>exception handling in triggers (errors and rollbacks) ?
>From 'Inside' :
>ROLLBACK (because of a fatal error or an
>explicit ROLLBACK command), the entire batch is aborted.
>
>As I read this :
>From within a trigger any FK or relational constraint
>violation results in a fatal error aborting the complete
>batch (and complete transaction).
>Is this correct ?
>What if there was no transaction start ?
>In the QA, does the QA supply a transaction
>(increase the transaction count) if no transaction
>was started ?
>When sending several statements, it appears
>that all statements are rolled back. (Are handled
>as one transaction).
>Does something similar happen when doing
>similar statements from application programs ?
>Thanks for your attention,
>ben brugman.
>
>.
>|||My email is
stbraslenscap@.lenscaphiscom.nl
(Both lenscaps should be removed).
I tried to reply to your mail, but that bounced,
so now in the thread.
thanks in advance,
ben brugman
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:4ff601c49104$6e437d70$a301280a@.phx.gbl...
> If we can get away from the trigger for a second.
> One of the least documented aspects on how transactions
> work are to do with the process id that its running from.
> So if you open up QA do transaction mock statement, open
> up another window in QA and do rollback you will get an
> error, why, becasue the process in the second window is
> different to the first.
> The reason why this is important is that a rollback as you
> found out will effect EVERY transaction in that process
> even though it maybe nested. Try this yourself, you will
> get some very interesting answers.
> So to get back to your question.
> SQL Server will automatically rollback transactions if the
> error is serious enough.
> If there is no transaction then there will be no
> transaction count, there is a handy little global variable
> called @.@.TRANCOUNT you may want to look at. So if that is
> set to anything but a 0 it will automatically rollback the
> transaction irrespective on whether it is done in trigger
> or something else.
> From application programs then it depends on the error. If
> you had a connection object and you application (not SQL)
> failed then it would be up to your application to repair
> the DB by sending it up a rollback.
> If you application using the same connection as the begin
> transaction sent some SQL that caused an error then yes it
> would automatically roll back.
> Anyway that it. I sugest you have a play on QA.
> If you have any questions then don't hesitate to email me
> on peternolan67REMOVETHIS@.hotmail.com (though I am a bit
> busy tonight)
> Peter
> "You can always count on Americans to do the right thing -
> after they've tried everything else."
> Winston Churchill
>
>
> >--Original Message--
> >Can anybody point me to a good text about
> >exception handling in triggers (errors and rollbacks) ?
> >
> >From 'Inside' :
> >ROLLBACK (because of a fatal error or an
> >explicit ROLLBACK command), the entire batch is aborted.
> >
> >
> >As I read this :
> >From within a trigger any FK or relational constraint
> >violation results in a fatal error aborting the complete
> >batch (and complete transaction).
> >Is this correct ?
> >
> >What if there was no transaction start ?
> >In the QA, does the QA supply a transaction
> >(increase the transaction count) if no transaction
> >was started ?
> >When sending several statements, it appears
> >that all statements are rolled back. (Are handled
> >as one transaction).
> >Does something similar happen when doing
> >similar statements from application programs ?
> >
> >Thanks for your attention,
> >
> >ben brugman.
> >
> >
> >.
> >

Exception handling in Triggers.

Can anybody point me to a good text about
exception handling in triggers (errors and rollbacks) ?
From 'Inside' :
ROLLBACK (because of a fatal error or an
explicit ROLLBACK command), the entire batch is aborted.
As I read this :
From within a trigger any FK or relational constraint
violation results in a fatal error aborting the complete
batch (and complete transaction).
Is this correct ?
What if there was no transaction start ?
In the QA, does the QA supply a transaction
(increase the transaction count) if no transaction
was started ?
When sending several statements, it appears
that all statements are rolled back. (Are handled
as one transaction).
Does something similar happen when doing
similar statements from application programs ?
Thanks for your attention,
ben brugman."ben brugman" <ben@.niethier.nl> wrote in message
news:%23VPXOAQkEHA.3724@.TK2MSFTNGP11.phx.gbl...
> As I read this :
> From within a trigger any FK or relational constraint
> violation results in a fatal error aborting the complete
> batch (and complete transaction).
> Is this correct ?
Not just a constraint violation; ANY error will abort (and consequently
rollback) the entire batch.

> What if there was no transaction start ?
Every DML operation in SQL Server uses a transaction, even if you don't
explicitly request it.
So:
UPDATE Tbl
SET Val = 0
WHERE SomeCol = 1
will use a transaction. That way, if there are two rows where SomeCol =
1, and the first was successful but the second had an error, the entire
batch will roll back to the initial state.

> When sending several statements, it appears
> that all statements are rolled back. (Are handled
> as one transaction).
Do you have IMPLICIT_TRANSACTIONS turned on for your session?
This option causes SQL Server to treat multistatement batches as if they
were wrapped in a transaction. But it's not turned on by default in Query
Analyzer or any connection library that I know of...