how to treat truncations as a warning

I'm using SSIS to migrate data from one system to another. This is a usual extract, transform, cleanse and load type task.

The error handling is critical to get right. E.g. truncation of data on one column should stop that row being loaded but for other columns I might be happy to carry on loading the row but record a warning.

I'm finding the error disposition a bit limiting. I really feel the need for an 'Issue Warning' disposition which will act the same way as 'Ignore Error' in that the row continues being processed but will in addition copy a row to a warning output so that I can write a message to a log file for someone to manually investigate and correct that item of data post the conversion. Alternatively it would be useful to specify a severity (at a column level) when redirecting error output. This way I can put logic into a downstream component which would treat the error row differently depending on the severity of the error.

Am I missing a trick?

There's no built-in switch for enabling this behavior, but you can accomplish it with creative use of error redirection. I use error redirection or a conditional split to identify rows that either cause warnings or errors, flag them appropriately, then send the errors to a logging table. I send the warnings to a multicast that outputs the rows to both a logging table and to a Union All to put them back into the main flow.|||this will work for me, though it's a shame there isn't a built-in feature...|||

Nick Corrie wrote:

this will work for me, though it's a shame there isn't a built-in feature...

Nick,

That could be a good suggestion to make; you can post it at the connect site: http://connect.microsoft.com/VisualStudio/Feedback

How to trap xp_cmdshell dtsrun error messages

Hi Here is my store procedure. The below code is not giving error at all when dts fails.
Can any one please help me how to trap xp_cmdshell dtsrun error messages
CREATE PROCEDURE [dbo].[usp_VRSURVEYComments] AS
exec USP_ReadVRUDirectory
set nocount on
DECLARE @.filename Char(20),@.cmdstring varchar(150) ,@.Error int
DECLARE VRUCFileListCursor CURSOR Fast_FORWARD FOR
SELECT filelist FROM TBLVRUDir where filelist like 'V%.CMM' order by filelist
OPEN VRUCFileListCursor
FETCH NEXT FROM VRUCFileListCursor
INTO @.filename
WHILE @.@.FETCH_STATUS = 0
BEGIN
truncate table VRUSURVEYComments
set @.cmdstring = 'copy e:\ftproot\vru\'+@.filename +' e:\ftproot\vru\VRUComments.txt'
exec master..xp_cmdshell @.cmdstring
exec master..xp_cmdshell 'dtsrun /S WPCCP005DS /E /N VRUSurveyComments'
SELECT @.Error = @.@.ERROR
IF @.Error <> 0
BEGIN
Print @.Error
END
UPDATE VRUSURVEYComments SET [Filename] = @.filename -- where [Filename] is null
update VRUSURVEYComments set cur= 'T'
exec master..xp_cmdshell 'dtsrun /S WPCCP005DS /E /N VRUSurveyCommentsExport'
FETCH NEXT FROM VRUCFileListCursor
INTO @.filename
END
CLOSE VRUCFileListCursor
DEALLOCATE VRUCFileListCursor
Hi
Rather than using xp_cmdshell to rename the file try using something like
the following to change the name of the source file. Using the FSO you can
check errors more easily!
http://www.sqldts.com/default.aspx?200
http://www.sqldts.com/default.aspx?246
You should also check the return value from xp_cmdshell e.g
DECLARE @.cmd sysname, @.var sysname, @.stat int
SET @.var = 'Hello world'
SET @.cmd = 'echo ' + @.var + ' > G:\var_out.txt'
EXEC @.stat = master..xp_cmdshell @.cmd
PRINT '@.stat= ' + CONVERT(varchar,@.stat)
John
"Admin" <admin@.emoneylinks.com> wrote in message
news:12AE78DE-091E-4E43-BEAA-639F1EAA6E4C@.microsoft.com...
> Hi Here is my store procedure. The below code is not giving error at all
when dts fails.
> Can any one please help me how to trap xp_cmdshell dtsrun error messages
> CREATE PROCEDURE [dbo].[usp_VRSURVEYComments] AS
> exec USP_ReadVRUDirectory
> set nocount on
> DECLARE @.filename Char(20),@.cmdstring varchar(150) ,@.Error int
> DECLARE VRUCFileListCursor CURSOR Fast_FORWARD FOR
> SELECT filelist FROM TBLVRUDir where filelist like 'V%.CMM' order by
filelist
> OPEN VRUCFileListCursor
> FETCH NEXT FROM VRUCFileListCursor
> INTO @.filename
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> truncate table VRUSURVEYComments
> set @.cmdstring = 'copy e:\ftproot\vru\'+@.filename +'
e:\ftproot\vru\VRUComments.txt'
> exec master..xp_cmdshell @.cmdstring
> exec master..xp_cmdshell 'dtsrun /S
WPCCP005DS /E /N VRUSurveyComments'
>
> SELECT @.Error = @.@.ERROR
> IF @.Error <> 0
> BEGIN
> Print @.Error
> END
>
> UPDATE VRUSURVEYComments SET [Filename] = @.filename --
where [Filename] is null
> update VRUSURVEYComments set cur= 'T'
> exec master..xp_cmdshell 'dtsrun /S WPCCP005DS /E /N
VRUSurveyCommentsExport'
> FETCH NEXT FROM VRUCFileListCursor
> INTO @.filename
> END
> CLOSE VRUCFileListCursor
> DEALLOCATE VRUCFileListCursor
>
>

How to trap an error from insert ?

When inserting rows from a staging table to a production one, I need to
convert a column of type varchar to type int. Often there're rows that
have junk data in this column and that makes convert() fail. Is it
possible to know that such junk rows exist without getting an error
message ? In other words, is there a way to prevent the insert query
from throwing an error msg, but I still know that it fails ?
thanks,
TamYou can use the ISNUMERIC() to determine whether an
expression is a valid numeric type.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1124509954.485547.268620@.o13g2000cwo.googlegroups.com...
> When inserting rows from a staging table to a production one, I need to
> convert a column of type varchar to type int. Often there're rows that
> have junk data in this column and that makes convert() fail. Is it
> possible to know that such junk rows exist without getting an error
> message ? In other words, is there a way to prevent the insert query
> from throwing an error msg, but I still know that it fails ?
> thanks,
> Tam
>|||I've tried isnumeric() - indeed this was the first thing I did. Yet
strings that have character like 'd', 'e', '.' also pass isnumeric()
but are not convertible to int, and these characters happen quite
commonly in the junk rows in my DB. Any other suggestions ?|||You can check for all digits with:
CASE WHEN
REPLACE(
REPLACE(
.
REPLACE (num '0',''),
1, ''),
.
9, '') = '' THEN CAST(num AS INTEGER) ELSE NULL END|||On 20 Aug 2005 18:30:21 -0700, --CELKO-- wrote:

>You can check for all digits with:
>CASE WHEN
>REPLACE(
> REPLACE(
> ..
> REPLACE (num '0',''),
> 1, ''),
> ..
> 9, '') = '' THEN CAST(num AS INTEGER) ELSE NULL END
Hi Joe,
Never rely on implicit conversion if you don't have to. Use quotes
around 1, 2, ..., 9 as well to prevent conversions.
And of course, this is lots more complicated then encessary:
CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||these are interesting solutions. btw, is there any "try-catch"
structure in sql server as I'm concerned there're cases that exceptions
are not known before hand ?
thanks,
Tam|||>> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
Should be
> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CAST(num AS INTEGER) END
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:kgrgg11r74k425gau3958b7dgjjtcsu9vu@.
4ax.com...
> On 20 Aug 2005 18:30:21 -0700, --CELKO-- wrote:
>
> Hi Joe,
> Never rely on implicit conversion if you don't have to. Use quotes
> around 1, 2, ..., 9 as well to prevent conversions.
> And of course, this is lots more complicated then encessary:
> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep. But in SQL Server 2005
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1124668962.565306.81090@.g43g2000cwa.googlegroups.com...
> these are interesting solutions. btw, is there any "try-catch"
> structure in sql server as I'm concerned there're cases that exceptions
> are not known before hand ?
> thanks,
> Tam
>|||On Mon, 22 Aug 2005 11:06:30 +0530, Roji. P. Thomas wrote:
>Should be
Hi Roji,
You are correct that I made a mistake. But your correction is wrong too
(since it's an exact same copy - you obviously forgot to correct the
mistake before posting).
For others reading this discussion: the correct statement is
(Note how the caret has sneaked one position to the right)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> You are correct that I made a mistake. But your correction is wrong too
> (since it's an exact same copy - you obviously forgot to correct the
> mistake before posting).
:)
In a hurry to correct you asap, I refuse to look at the finer details :)
Infact I have'nt noticed the misplaced caret, but only the mispelled CAST.
Lets keep correcting each other :p
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:m1mkg19d2vmgq7vvus5ncer3di83gdvhut@.
4ax.com...
> On Mon, 22 Aug 2005 11:06:30 +0530, Roji. P. Thomas wrote:
>
> Hi Roji,
>
> You are correct that I made a mistake. But your correction is wrong too
> (since it's an exact same copy - you obviously forgot to correct the
> mistake before posting).
> For others reading this discussion: the correct statement is
>
> (Note how the caret has sneaked one position to the right)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

How to translate varchar into varbinary?

Hi everyone,

We're trying to migrate a varchar field from Sql2k to varbinary in a sql25k through a dtsx package. We get an error which tell us: "data will be lost".

Does anyone have any idea about that?

Thanks for your time and inputs,

Do it in the extract query, T-SQL supports explicit casts between those two types

DECLARE @.v varchar(10)

SET @.v = '0123456789'

SELECT CAST(@.v AS varbinary(10))

How to transfe data from one table to another?

I trying to transfer data from one table to another. And, after many attempts to make it works, I have errors:

Error 1 Validation error. {9C9FAC79-422F-4BE5-9AB4-A09CF49DFDB7}: OLE DB Destination [178]: Failure inserting into the read-only column "ID". testPackage (1).dtsx 0 0
Error 2 Validation error. {9C9FAC79-422F-4BE5-9AB4-A09CF49DFDB7}: OLE DB Destination [178]: Column metadata validation failed. testPackage (1).dtsx 0 0

I trying to move data from one table to another table with exactly same structure. What may be wrong in my code?

And another question. Actually I want to move data from one table to another, and may be differencies in columns between target and source database. How to exclude extra columns during transfer? My code is below:

private void Test()

{

try

{

//this is my code

///////////////////////////////////////////////////////////////////////

Package package = new Package();

MainPipe dataFlow = ((TaskHost)package.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;

//add connectionmanagers to package.

Microsoft.SqlServer.Dts.Runtime.ConnectionManager srcCM = package.Connections.Add("OLEDB");

srcCM.Name = "OLEDBSource";

srcCM.ConnectionString = "Data Source=mxpandreys\\sql2k5;Initial Catalog=Test;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Auto Translate=False;";

Microsoft.SqlServer.Dts.Runtime.ConnectionManager destCM = package.Connections.Add("OLEDB");

destCM.Name = "OLEDBDestination";

destCM.ConnectionString = "Data Source=mxpandreys\\sql2k5;Initial Catalog=testCopy;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Auto Translate=False;";

//create the source component

IDTSComponentMetaData90 srcComponent = dataFlow.ComponentMetaDataCollection.New();

srcComponent.Name = "ComponetOLEDBSource";

srcComponent.ComponentClassID = "DTSAdapter.OLEDBSource.1";

// Get the design time instance of the component and initialize the component

CManagedComponentWrapper srcDesignTime = srcComponent.Instantiate();

srcDesignTime.ProvideComponentProperties();

// Specify the connection manager.

if (srcComponent.RuntimeConnectionCollection.Count > 0)

{

srcComponent.RuntimeConnectionCollection[0].ConnectionManagerID = srcCM.ID;

srcComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);

}

// Set the custom properties.

srcDesignTime.SetComponentProperty("AccessMode", 0);

srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Accounts]");

// Reinitialize the metadata.

srcDesignTime.AcquireConnections(null);

srcDesignTime.ReinitializeMetaData();

srcDesignTime.ReleaseConnections();

// Create the destination component.

IDTSComponentMetaData90 destComponent = dataFlow.ComponentMetaDataCollection.New();

destComponent.Name = "ComponetOLEDBDestination";

destComponent.ComponentClassID = "DTSAdapter.OLEDBDestination.1";

CManagedComponentWrapper destDesignTime = destComponent.Instantiate();

destDesignTime.ProvideComponentProperties();

// Specify the connection manager.

if (destComponent.RuntimeConnectionCollection.Count > 0)

{

destComponent.RuntimeConnectionCollection[0].ConnectionManagerID = destCM.ID;

destComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);

}

// Set the custom properties.

destDesignTime.SetComponentProperty("AccessMode", 0);

destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Accounts]");

destDesignTime.AcquireConnections(null);

destDesignTime.ReinitializeMetaData();

destDesignTime.ReleaseConnections();

// Create the path.

IDTSPath90 path = dataFlow.PathCollection.New();

path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], destComponent.InputCollection[0]);

//map the columns

IDTSInput90 input = destComponent.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

IDTSInputColumn90 vCol = destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);

destDesignTime.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);

}

//DataFlowEvent dataFlowEvent = new DataFlowEvent();

Application appSSIS = new Application();

appSSIS.SaveToXml("c:\\temp\\testPackage.dtsx", package, null);

/*

package.Validate(null, null, null, null);

package.Execute(null, null, null, null, null);

* */

//DataFlowEvent is derived from class DefaultEvent

}

catch (Exception e)

{

throw;

}

}

Sounds like the ID field is an identity column. You need to check the 'Keep Identity' checkbox in the OLE DB Destination editor.

Also, since you transfering data from one table to another on the same server, you could you use tsql, something like this...

set identitiy insert <tablename> on;

insert into TableB (a,b,c,d)

select

a,b,c,d

from

TableA

How to track down this error

Hi,
We recently got an exception in the SOA-solution I'm working on and none of
the developers have seen this error before, and we are not sure where to
begin. We are running against a busy sql server 2005 and as I understand it
logging cannot be turned on. The exception message is as follows.
System.Data.SqlClient.SqlException: The current transaction cannot be
committed and cannot support operations that write to the log file. Roll
back the transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
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.InternalExecuteNonQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
The exception occures running ExecuteNonQuery against one of the stored
procedures in the database (which I'm not allowed to reproduce here). We
cannot identify anything wrong with the procedure.
Do anyone have any idea what a likely cause (or unlikely, yet possible) for
this error? Or ideas on how to track it down.
Thanks,
Morten WennevikOn Sep 26, 11:36 am, "Morten Wennevik" <MortenWenne...@.hotmail.com>
wrote:
> Hi,
> We recently got an exception in the SOA-solution I'm working on and none of
> the developers have seen this error before, and we are not sure where to
> begin. We are running against a busy sql server 2005 and as I understand it
> logging cannot be turned on. The exception message is as follows.
> System.Data.SqlClient.SqlException: The current transaction cannot be
> committed and cannot support operations that write to the log file. Roll
> back the transaction.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> 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.InternalExecuteNonQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> The exception occures running ExecuteNonQuery against one of the stored
> procedures in the database (which I'm not allowed to reproduce here). We
> cannot identify anything wrong with the procedure.
> Do anyone have any idea what a likely cause (or unlikely, yet possible) for
> this error? Or ideas on how to track it down.
> Thanks,
> Morten Wennevik
Morten,
The error is easy to reproduce:
SET XACT_ABORT ON
BEGIN TRAN
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
-- at this time transaction is not committable
COMMIT
END CATCH
/*
Msg 3930, Level 16, State 1, Line 7
The current transaction cannot be committed and cannot support
operations that write to the log file. Roll back the transaction.
*/
if that is your case, you should change your code to:
BEGIN TRAN
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
-- at this time transaction is not committable
IF XACT_STATE() = -1
ROLLBACK
ELSE
COMMIT
END CATCH|||On Sep 26, 6:36 pm, "Morten Wennevik" <MortenWenne...@.hotmail.com>
wrote:
> Hi,
> We recently got an exception in the SOA-solution I'm working on and none =of
> the developers have seen this error before, and we are not sure where to
> begin. We are running against a busy sql server 2005 and as I understand= it
> logging cannot be turned on. The exception message is as follows.
> System.Data.SqlClient.SqlException: The current transaction cannot be
> committed and cannot support operations that write to the log file. Roll
> back the transaction.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateOb=je=ADct
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> 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.InternalExecuteNonQuery(DbAsyncRes=ult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> The exception occures running ExecuteNonQuery against one of the stored
> procedures in the database (which I'm not allowed to reproduce here). We
> cannot identify anything wrong with the procedure.
> Do anyone have any idea what a likely cause (or unlikely, yet possible) f=or
> this error? Or ideas on how to track it down.
> Thanks,
> Morten Wennevik
Check if the transaction did not violate any constraint and that you
did not try to insert wrong data type to one of the columns.|||Hi Alex,
Thanks for your suggestion. What we believe turned out to be the error was
a convert to int where the input parameter was '2007-09-27' instead of
'20070927'. When we got logging turned on, this generated an exception,
which was caught, and the catch block did indeed do RaiseError with an error
severity above 11, but somehow this didn't pass control back to .Net. When
the query much later tried to commit all the jobs it could not, since an
exception had occured. The actual Commit and Rollback is handled at a
higher level than inside the catch block.
Morten
"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:1190825499.191973.201250@.g4g2000hsf.googlegroups.com...
> On Sep 26, 11:36 am, "Morten Wennevik" <MortenWenne...@.hotmail.com>
> wrote:
>> Hi,
>> We recently got an exception in the SOA-solution I'm working on and none
>> of
>> the developers have seen this error before, and we are not sure where to
>> begin. We are running against a busy sql server 2005 and as I understand
>> it
>> logging cannot be turned on. The exception message is as follows.
>> System.Data.SqlClient.SqlException: The current transaction cannot be
>> committed and cannot support operations that write to the log file. Roll
>> back the transaction.
>> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
>> Boolean breakConnection)
>> at
>> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
>> stateObj)
>> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
>> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
>> bulkCopyHandler, TdsParserStateObject stateObj)
>> 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.InternalExecuteNonQuery(DbAsyncResult
>> result, String methodName, Boolean sendToPipe)
>> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
>> The exception occures running ExecuteNonQuery against one of the stored
>> procedures in the database (which I'm not allowed to reproduce here). We
>> cannot identify anything wrong with the procedure.
>> Do anyone have any idea what a likely cause (or unlikely, yet possible)
>> for
>> this error? Or ideas on how to track it down.
>> Thanks,
>> Morten Wennevik
> Morten,
> The error is easy to reproduce:
> SET XACT_ABORT ON
> BEGIN TRAN
> BEGIN TRY
> SELECT 1/0
> END TRY
> BEGIN CATCH
> -- at this time transaction is not committable
> COMMIT
> END CATCH
> /*
> Msg 3930, Level 16, State 1, Line 7
> The current transaction cannot be committed and cannot support
> operations that write to the log file. Roll back the transaction.
> */
> if that is your case, you should change your code to:
> BEGIN TRAN
> BEGIN TRY
> SELECT 1/0
> END TRY
> BEGIN CATCH
> -- at this time transaction is not committable
> IF XACT_STATE() = -1
> ROLLBACK
> ELSE
> COMMIT
> END CATCH
>|||Hi Adi,
Thanks for your suggestion. What we believe turned out to be the error was
a convert to int where the input parameter was '2007-09-27' instead of
'20070927'. When we got logging turned on, this generated an exception,
which was caught, and the catch block did indeed do RaiseError with an error
severity above 11, but somehow this didn't pass control back to .Net. When
the query much later tried to commit all the jobs it could not, since an
exception had occured. The actual Commit and Rollback is handled at a
higher level than inside the catch block.
Morten
"Adi" <adicohn@.hotmail.com> wrote in message
news:1190825538.689369.104420@.d55g2000hsg.googlegroups.com...
On Sep 26, 6:36 pm, "Morten Wennevik" <MortenWenne...@.hotmail.com>
wrote:
> Hi,
> We recently got an exception in the SOA-solution I'm working on and none
> of
> the developers have seen this error before, and we are not sure where to
> begin. We are running against a busy sql server 2005 and as I understand
> it
> logging cannot be turned on. The exception message is as follows.
> System.Data.SqlClient.SqlException: The current transaction cannot be
> committed and cannot support operations that write to the log file. Roll
> back the transaction.
> at System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
> Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObje­ct
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
> SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> 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.InternalExecuteNonQuery(DbAsyncResult
> result, String methodName, Boolean sendToPipe)
> at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
> The exception occures running ExecuteNonQuery against one of the stored
> procedures in the database (which I'm not allowed to reproduce here). We
> cannot identify anything wrong with the procedure.
> Do anyone have any idea what a likely cause (or unlikely, yet possible)
> for
> this error? Or ideas on how to track it down.
> Thanks,
> Morten Wennevik
Check if the transaction did not violate any constraint and that you
did not try to insert wrong data type to one of the columns.

how to throw Custom Error instead of Default SQL Server Error

Hi guys!
i've very basic knowledge of DB as i'm a software developer i dont know
much about the DB other then writing Queries.
now, the problem is, i'm in a situation, where i need to throw a custom
error whenever some specific error occure while deleting the record
from the table.
the Scenrio is, like this:
i've table that has refencial intigrity constraint. it disallow to
delete the parent row if it is refered in the child table twice. this
is prefectly alright. all i want to do is, whenever such situation
occurs i want to throw my custom error instead of the sql server's
default error msg.
any idea,suggestion are most welcome.
Database : Sql Server 2000
thanks,
LuckyIf you are using the insert statement within a stored procedure, you
can use the @.@.ERROR variable and RAISERROR with your custom message.
Regards
Manisha Gandhi
lucky wrote:
> Hi guys!
> i've very basic knowledge of DB as i'm a software developer i dont know
> much about the DB other then writing Queries.
> now, the problem is, i'm in a situation, where i need to throw a custom
> error whenever some specific error occure while deleting the record
> from the table.
> the Scenrio is, like this:
> i've table that has refencial intigrity constraint. it disallow to
> delete the parent row if it is refered in the child table twice. this
> is prefectly alright. all i want to do is, whenever such situation
> occurs i want to throw my custom error instead of the sql server's
> default error msg.
> any idea,suggestion are most welcome.
> Database : Sql Server 2000
> thanks,
> Lucky|||thanks for your replay,
but the situation is, after i implemented, what u suggested, now it
throws 2 errors. one is my custom error and another is default SQL
Server error.
how can i stop sql server to throw it's own an error?
Lucky|||You cannot prevent a raised error from being returned to the client in SQL
2000. SQL 2005 provides a try/catch construct that can meet your
requirement.
Hope this helps.
Dan Guzman
SQL Server MVP
"lucky" <tushar.n.patel@.gmail.com> wrote in message
news:1164629710.750116.166010@.f16g2000cwb.googlegroups.com...
> thanks for your replay,
> but the situation is, after i implemented, what u suggested, now it
> throws 2 errors. one is my custom error and another is default SQL
> Server error.
> how can i stop sql server to throw it's own an error?
> Lucky
>

how to throw Custom Error instead of Default SQL Server Error

Hi guys!
i've very basic knowledge of DB as i'm a software developer i dont know
much about the DB other then writing Queries.
now, the problem is, i'm in a situation, where i need to throw a custom
error whenever some specific error occure while deleting the record
from the table.
the Scenrio is, like this:
i've table that has refencial intigrity constraint. it disallow to
delete the parent row if it is refered in the child table twice. this
is prefectly alright. all i want to do is, whenever such situation
occurs i want to throw my custom error instead of the sql server's
default error msg.
any idea,suggestion are most welcome.
Database : Sql Server 2000
thanks,
Lucky
If you are using the insert statement within a stored procedure, you
can use the @.@.ERROR variable and RAISERROR with your custom message.
Regards
Manisha Gandhi
lucky wrote:
> Hi guys!
> i've very basic knowledge of DB as i'm a software developer i dont know
> much about the DB other then writing Queries.
> now, the problem is, i'm in a situation, where i need to throw a custom
> error whenever some specific error occure while deleting the record
> from the table.
> the Scenrio is, like this:
> i've table that has refencial intigrity constraint. it disallow to
> delete the parent row if it is refered in the child table twice. this
> is prefectly alright. all i want to do is, whenever such situation
> occurs i want to throw my custom error instead of the sql server's
> default error msg.
> any idea,suggestion are most welcome.
> Database : Sql Server 2000
> thanks,
> Lucky
|||thanks for your replay,
but the situation is, after i implemented, what u suggested, now it
throws 2 errors. one is my custom error and another is default SQL
Server error.
how can i stop sql server to throw it's own an error?
Lucky
|||You cannot prevent a raised error from being returned to the client in SQL
2000. SQL 2005 provides a try/catch construct that can meet your
requirement.
Hope this helps.
Dan Guzman
SQL Server MVP
"lucky" <tushar.n.patel@.gmail.com> wrote in message
news:1164629710.750116.166010@.f16g2000cwb.googlegr oups.com...
> thanks for your replay,
> but the situation is, after i implemented, what u suggested, now it
> throws 2 errors. one is my custom error and another is default SQL
> Server error.
> how can i stop sql server to throw it's own an error?
> Lucky
>
sql

how to throw Custom Error instead of Default SQL Server Error

Hi guys!
i've very basic knowledge of DB as i'm a software developer i dont know
much about the DB other then writing Queries.
now, the problem is, i'm in a situation, where i need to throw a custom
error whenever some specific error occure while deleting the record
from the table.
the Scenrio is, like this:
i've table that has refencial intigrity constraint. it disallow to
delete the parent row if it is refered in the child table twice. this
is prefectly alright. all i want to do is, whenever such situation
occurs i want to throw my custom error instead of the sql server's
default error msg.
any idea,suggestion are most welcome.
Database : Sql Server 2000
thanks,
LuckyIf you are using the insert statement within a stored procedure, you
can use the @.@.ERROR variable and RAISERROR with your custom message.
Regards
Manisha Gandhi
lucky wrote:
> Hi guys!
> i've very basic knowledge of DB as i'm a software developer i dont know
> much about the DB other then writing Queries.
> now, the problem is, i'm in a situation, where i need to throw a custom
> error whenever some specific error occure while deleting the record
> from the table.
> the Scenrio is, like this:
> i've table that has refencial intigrity constraint. it disallow to
> delete the parent row if it is refered in the child table twice. this
> is prefectly alright. all i want to do is, whenever such situation
> occurs i want to throw my custom error instead of the sql server's
> default error msg.
> any idea,suggestion are most welcome.
> Database : Sql Server 2000
> thanks,
> Lucky|||thanks for your replay,
but the situation is, after i implemented, what u suggested, now it
throws 2 errors. one is my custom error and another is default SQL
Server error.
how can i stop sql server to throw it's own an error?
Lucky|||You cannot prevent a raised error from being returned to the client in SQL
2000. SQL 2005 provides a try/catch construct that can meet your
requirement.
Hope this helps.
Dan Guzman
SQL Server MVP
"lucky" <tushar.n.patel@.gmail.com> wrote in message
news:1164629710.750116.166010@.f16g2000cwb.googlegroups.com...
> thanks for your replay,
> but the situation is, after i implemented, what u suggested, now it
> throws 2 errors. one is my custom error and another is default SQL
> Server error.
> how can i stop sql server to throw it's own an error?
> Lucky
>

how to surpress a server error msg?

background sql2k on nt5.
when using stored procedure sp_helprotect against a login. I've got the
following msg in query analyzer messages tab.
Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
There are no matching rows on which to report.
is it possible to surpress that msg and just display the Grid tab?
thank you.
steveThat would be a client issue, but I assume that you are using QA and it does
n't have any awareness of any
particular stored procedures; it just displays whatever error comes back. On
e option can be to copy the source
code for the proc and modify it so it doesn't return that error message (I a
ssume that there's a RAISERROR
with that error somewhere in the source code).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message news:%23xdrpj0PEHA.640@.TK2MSFTN
GP12.phx.gbl...
> background sql2k on nt5.
> when using stored procedure sp_helprotect against a login. I've got the
> following msg in query analyzer messages tab.
> Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
> There are no matching rows on which to report.
>
> is it possible to surpress that msg and just display the Grid tab?
> thank you.
>
> steve
>|||well, i'd rather not to change the system stored procedure since that would
mean i have to change that particular sp on all servers!
i'd prefer a solution to control the display.
i was calling the sp thru SQL pass thru in Access and return the query
result to display in an Access report. (the query set, not the error msg)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OExOyt0PEHA.2236@.TK2MSFTNGP10.phx.gbl...
> That would be a client issue, but I assume that you are using QA and it
doesn't have any awareness of any
> particular stored procedures; it just displays whatever error comes back.
One option can be to copy the source
> code for the proc and modify it so it doesn't return that error message (I
assume that there's a RAISERROR
> with that error somewhere in the source code).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:%23xdrpj0PEHA.640@.TK2MSFTNGP12.phx.gbl...
>|||As this is a client issue, I suggest you post the question to an Access grou
p. there might be some option in
Access to suppress error messages...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message news:eiigD40PEHA.3944@.tk2msftng
p13.phx.gbl...
> well, i'd rather not to change the system stored procedure since that woul
d
> mean i have to change that particular sp on all servers!
> i'd prefer a solution to control the display.
> i was calling the sp thru SQL pass thru in Access and return the query
> result to display in an Access report. (the query set, not the error msg)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:OExOyt0PEHA.2236@.TK2MSFTNGP10.phx.gbl...
> doesn't have any awareness of any
> One option can be to copy the source
> assume that there's a RAISERROR
> news:%23xdrpj0PEHA.640@.TK2MSFTNGP12.phx.gbl...
>|||> well, i'd rather not to change the system stored procedure since that
would
> mean i have to change that particular sp on all servers!
What if you found a more serious error in your stored procedure? You should
be setting up your servers so that deploying changes is not so convoluted
and difficult that you find yourself looking for more convenient
workarounds. IMHO.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

How to structure my outer query

I want to select the parent_topic_id from the inner query but i am getting an error though....can someone show me how i should structure these queries please?!?! thank you.....Do I not need to include the Order By or is it in the wrong place?

Msg 1033, Level 15, State 1, Line 25

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Select sti.parent_topic_id

From

(

SELECT sti.parent_topic_id,

st.description_short,

sti.sort_order

FROM sam_topic_items sti

INNER JOIN sam_topic st ON sti.topic_id = st.id

WHERE EXISTS

(

SELECT 1

FROM sam_topic_items sti1

INNER JOIN sam_topic st1 ON sti1.topic_id = st1.id

WHERE st1.type = 'Topic'

AND sti.parent_topic_id = sti1.parent_topic_id

AND sti.sort_order = sti1.sort_order

GROUP BY sti1.parent_topic_id, sti1.sort_order, st1.Type

HAVING COUNT(sti1.sort_order) > 1

)

Order By sti.parent_topic_id

)

Put the [ORDER BY] after the last parenthesis.

|||

I still got "Incorrect Syntax near order"? What am I missing?

Select sti.parent_topic_id

From(

SELECT sti.parent_topic_id,

st.description_short,

sti.sort_order

FROM sam_topic_items sti

INNER JOIN sam_topic st ON sti.topic_id = st.id

WHERE EXISTS

(

SELECT 1

FROM sam_topic_items sti1

INNER JOIN sam_topic st1 ON sti1.topic_id = st1.id

WHERE st1.type = 'Topic'

AND sti.parent_topic_id = sti1.parent_topic_id

AND sti.sort_order = sti1.sort_order

GROUP BY sti1.parent_topic_id, sti1.sort_order, st1.Type

HAVING COUNT(sti1.sort_order) > 1

)

)

Order By sti.parent_topic_id

|||

That was a step forward.

Now the next part of the problem is that when you create a derived table, you have to give it a name. (Recall my remarks in your previous posts about using derived tables?) So add a table name after the last parenthesis and before the [ORDER BY]. (A lot of folks use dt for derived table.)

Also, since the column Parent_Topic_ID is a member of the derived table, you will have to preface the outer query to use the derived table name -NOT [sti].

<ShoulderTap>
As a note: In my opinion, you are jumping ahead too fast, and not taking the time to practice, learn, and understand the help that we are giving you. It is very important to completely understand how to properly use derived tables.
</ShoulderTap>

|||

I changed it to that and it works...I forgot all about the derived tables....opps...I am practicing as I learn....I am just trying to find out how to do things within SQL instead of minipulating SQL through VB.net or C#...You what though? Thank for you help and patience..(Did i spell that right)....

Select dt.parent_topic_id

From

(

SELECT st.id,sti.parent_topic_id,

st.description_short,

sti.sort_order

FROM sam_topic_items sti

INNER JOIN sam_topic st ON sti.topic_id = st.id

WHERE EXISTS

(

SELECT 1

FROM sam_topic_items sti1

INNER JOIN sam_topic st1 ON sti1.topic_id = st1.id

WHERE st1.type = 'Topic'

AND sti.parent_topic_id = sti1.parent_topic_id

AND sti.sort_order = sti1.sort_order

GROUP BY sti1.parent_topic_id, sti1.sort_order, st1.Type

HAVING COUNT(sti1.sort_order) > 1

)

) dt

Order By parent_topic_id

How to stop replication of a database?

When trying to drop a test database I get an error can't delete because it
is being usefor replication. But in the replication monitor there is no
publisher. I removed it. How can I stop this db from being used for
replication and then drop it?
Thks for any help
RD
Hi,
To remove replication, you can use Disable Publishing and Distribution
Wizard in SQL Server Enterprise Manager.
The Disable Publishing and Distribution Wizard in enterprise manager is
written to handle
this situation, and will remove all the replication settings.
To get to the wizard:
- Right click on the Replication folder in the server tree (or any child
node of the Replication folder)
- Select Disable Publishing and Distribution
- Go ahead in clicking next in the Wizard
After the removal, you could try dropping your database
Thanks
Hari
SQL Server MVP
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:%23LRchFGmFHA.1948@.TK2MSFTNGP12.phx.gbl...
> When trying to drop a test database I get an error can't delete because it
> is being usefor replication. But in the replication monitor there is no
> publisher. I removed it. How can I stop this db from being used for
> replication and then drop it?
> Thks for any help
> RD
>
|||Try sp_removedbreplication. See SQL Server Books Online for more
information. Also use sp_dboption to mark the database as not published, if
needed.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:%23LRchFGmFHA.1948@.TK2MSFTNGP12.phx.gbl...
> When trying to drop a test database I get an error can't delete because it
> is being usefor replication. But in the replication monitor there is no
> publisher. I removed it. How can I stop this db from being used for
> replication and then drop it?
> Thks for any help
> RD
>
|||Thanks to you both
RD
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:%23LRchFGmFHA.1948@.TK2MSFTNGP12.phx.gbl...
> When trying to drop a test database I get an error can't delete because it
> is being usefor replication. But in the replication monitor there is no
> publisher. I removed it. How can I stop this db from being used for
> replication and then drop it?
> Thks for any help
> RD
>

How to stop replication of a database?

When trying to drop a test database I get an error can't delete because it
is being usefor replication. But in the replication monitor there is no
publisher. I removed it. How can I stop this db from being used for
replication and then drop it?
Thks for any help
RDHi,
To remove replication, you can use Disable Publishing and Distribution
Wizard in SQL Server Enterprise Manager.
The Disable Publishing and Distribution Wizard in enterprise manager is
written to handle
this situation, and will remove all the replication settings.
To get to the wizard:
- Right click on the Replication folder in the server tree (or any child
node of the Replication folder)
- Select Disable Publishing and Distribution
- Go ahead in clicking next in the Wizard
After the removal, you could try dropping your database
Thanks
Hari
SQL Server MVP
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:%23LRchFGmFHA.1948@.TK2MSFTNGP12.phx.gbl...
> When trying to drop a test database I get an error can't delete because it
> is being usefor replication. But in the replication monitor there is no
> publisher. I removed it. How can I stop this db from being used for
> replication and then drop it?
> Thks for any help
> RD
>|||Try sp_removedbreplication. See SQL Server Books Online for more
information. Also use sp_dboption to mark the database as not published, if
needed.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:%23LRchFGmFHA.1948@.TK2MSFTNGP12.phx.gbl...
> When trying to drop a test database I get an error can't delete because it
> is being usefor replication. But in the replication monitor there is no
> publisher. I removed it. How can I stop this db from being used for
> replication and then drop it?
> Thks for any help
> RD
>|||Thanks to you both
RD
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:%23LRchFGmFHA.1948@.TK2MSFTNGP12.phx.gbl...
> When trying to drop a test database I get an error can't delete because it
> is being usefor replication. But in the replication monitor there is no
> publisher. I removed it. How can I stop this db from being used for
> replication and then drop it?
> Thks for any help
> RD
>

How to stop replication of a database?

When trying to drop a test database I get an error can't delete because it
is being usefor replication. But in the replication monitor there is no
publisher. I removed it. How can I stop this db from being used for
replication and then drop it?
Thks for any help
RDHi,
To remove replication, you can use Disable Publishing and Distribution
Wizard in SQL Server Enterprise Manager.
The Disable Publishing and Distribution Wizard in enterprise manager is
written to handle
this situation, and will remove all the replication settings.
To get to the wizard:
- Right click on the Replication folder in the server tree (or any child
node of the Replication folder)
- Select Disable Publishing and Distribution
- Go ahead in clicking next in the Wizard
After the removal, you could try dropping your database
Thanks
Hari
SQL Server MVP
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:%23LRchFGmFHA.1948@.TK2MSFTNGP12.phx.gbl...
> When trying to drop a test database I get an error can't delete because it
> is being usefor replication. But in the replication monitor there is no
> publisher. I removed it. How can I stop this db from being used for
> replication and then drop it?
> Thks for any help
> RD
>|||Try sp_removedbreplication. See SQL Server Books Online for more
information. Also use sp_dboption to mark the database as not published, if
needed.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:%23LRchFGmFHA.1948@.TK2MSFTNGP12.phx.gbl...
> When trying to drop a test database I get an error can't delete because it
> is being usefor replication. But in the replication monitor there is no
> publisher. I removed it. How can I stop this db from being used for
> replication and then drop it?
> Thks for any help
> RD
>|||Thanks to you both
RD
"Bob" <bdufournosp@.sgiims.com> wrote in message
news:%23LRchFGmFHA.1948@.TK2MSFTNGP12.phx.gbl...
> When trying to drop a test database I get an error can't delete because it
> is being usefor replication. But in the replication monitor there is no
> publisher. I removed it. How can I stop this db from being used for
> replication and then drop it?
> Thks for any help
> RD
>