Exceptions in Data Flow Scripts

What is the "correct" way of dealing with exceptions in a data flow script component. i.e. am I supposed to catch all exceptions and then set some failure flag? The reason I ask is I've got a script in a dataflow which is occasionally throwing exceptions when trying to convert an empty string to a decimal. Problem was the package locked up and had to be terminated when the exception was thrown (and not caught in the script)?

The only thing which differentiates this package from others I've created is the data flow has a conditional split which creates 2 seperate paths loading 2 seperate SQL tables - the exception is being thrown in a script on one branch which seems to hand the entire flow

PS I have fixed the script so this doesn't happen, i.e. test if the input string is String.Empty and if so set the _IsNull property.

If you are going to go down the road of custom building functions inside a script versus using the derived column or conditional split transformations to look at your data (test your data for correctness), then you'll also have to build in your own error handling as the script component does not support error outputs as part of the data flow.

Phil|||

Hi Phil,

Thanks for the reply, I'm not sure you're answering my question though The problem is that if an exception is thrown in a script and not caught, the package simply locks up - the unhandled exception seems to be blocking - no event handler is called etc. So what I'm asking is how do you flag a failure in a data flow script (if it were a control flow script I would propably create a global catch handler with Dts.TaskResult = Dts.Results.Failure

Dave

|||I think the package should lock up though, as the script component doesn't support the error output stream. So if an error is encountered in a script task, how should the package continue? That is, the error is fatal to the script component.

Some of the other guys will undoubtedly have some more advice when they get in tomorrow morning, especially those that are well versed in script programming.

Sorry, but I won't be of no more help tonight, especially when I'm not sitting in front of SSIS.

Phil|||Does it really lock up, or does the package simply fail?|||

It locks up, i.e. if run from BIDS the scipt component goes yellow when it starts executing, and nothing more occurs.

In other data flows, exceptions do cause the script component (and ultimatly the package) to fail. The different seemingly being that the package has a conditional split and each path of the split appears to be running concurrently when the exception is thrown in the script.

It's interesting that there's no error output from a script - not sure what to make of that - of all my dataflow components, the ones which fail the most are my scripts , esp since I cannot single step through them. Maybe I need to add and error number colume, handle the exception and set the error number in the script and then pass the subsequent flow through a conditional split?

|||

Dave Waterworth wrote:

Maybe I need to add and error number colume, handle the exception and set the error number in the script and then pass the subsequent flow through a conditional split?

Yep, a very valid solution.

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...
>

Exceptions and damaged databases in SQL Express 2005 & C#

Hi,

When a database has become corrupted (for whatever reason), when you try to open a connection to it using the SqlConnection class in C#, will it allow the connection to open? I'm trying to figure out if it will allow the connection to be opened, but will throw an SqlException when a command is sent to the server.

Thanks,

KingyNL

When you try to open a connection, exception will thrown if damaged database is default for current login or specified as "initial catalog" parameter in connection string.

Good luck,

Evergray

--

Words mean nothing...

Exceptions

How do I code an exception to terminate a transaction?
How do I code an exception so that it doesn't terminate the program?Hello,

If you want to stop a transaction via exception handling or with other techniques, use something like this

.
.
.
WHILE cuCursor%FOUND LOOP
EXIT WHEN cuCursor.field = 'exit'
END WHILE;

or

WHILE cuCursor%FOUND LOOP
IF cuCursor.field = 'exit'
GOTO EndLabel
END IF;
END WHILE;

<<EndLabel>>
.
.
.

or

WHILE cuCursor%FOUND LOOP
IF cuCursor.field = 'exit'
THROW endException
END IF;
END WHILE;

If your want to catch a exception use something like this

WHILE cuCursor%FOUND LOOP
BEGIN
nNumber = 'abc';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END WHILE;

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Thank you, it helped

Originally posted by bbk
How do I code an exception to terminate a transaction?
How do I code an exception so that it doesn't terminate the program?

Exception Handling

Hi All

I m new to the sqlserver.In Oracle we can handle exceptions like this

declare
name varchar(20);
begin
select ename into name from emp where eno=&eno;
dbms_output.put_line(name);
exception
when no_data_found then
dbms_output.put_line('No Entry');
end;
/

We will get the message No Entry When corrsponding employee number dosent exists.

In Sqlserver how to handle these things.

Specifically my requirement is with select statement(Like above program)
Waiting for valuable suggestions

Baba

You can do in sqlserver using @.@.Rowcount

select ename into name from emp where eno=&eno

if((select @.@.Rowcount)<= 0)

print 'No data'

The following explains the errorhandling in simple terms

http://www.sqlteam.com/article/handling-errors-in-stored-procedures

|||

In SQL Server this situation never treated as exception, There is a workaround available - here it is,

Code Snippet

Declare @.Sno as int;

Declare @.name as varchar(100)

Set @.Sno=4

Select @.name=name from sysobjects Where id=@.Sno

If @.@.RowCount = 0

Print 'No Entry'

Else

Print @.Name

|||Hi manivannan

I tried like this before only . I got the output

Thank u for u r valuable reply.

Baba

Exception comes from database,

Hi All,

Currently i am working on some web application, and facing some exceptions. Actually I am throwing exception from DB functions in some of cases, and then displaying error.aspx (custom error page). It is working fine.

But when i change language of browser from english to any other language, first time my custom error page comes, but afterwards, following error page comes:

Server error in '/sampletest' application
Exception of Project.module.myException is thrown.

Please help if any idea for same.

Please also let me know whether i have to kept lots of aspx files for different types of exception or messages.

Thanks In Advance
Arnold

If you give a sample of code then its easier to find where issue is...