hash warning, hash recursion errors

sql2000 sp3.
strange problem here. i've got an extremely complex query written by a
developer. the query works and it comes back in a reasonable amount of
time. however, the query generates a "hash warning/hash recursion"
error when it runs. after reading bol, these errors don't really seem
to be very bad. i decided to investigate it further by restoring the
production db to a test server and try it there. guess what. no hash
warning errors on the test server.
prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
allocated for sql server).
test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
allocated for sql server).
the query runs slower on the test server (as expected) even with little
to no traffic and no hash warnings.
my next guess was that on the prod server, all of sql server's ram was
being used by other objects. i did a dropcleanbuffers and a
freeproccache. query still generates hash warnings.
any ideas as to what would cause the hash warnings on prod server but
not test server?
i can't reboot the prod server and i can't stop sqlserver on prod
server. are there any things i can try in sqlserver to free up
resources other than dropcleanbuffers and freeproccache?Does SQL choose the same execution plan on the production and test server?
It's possible that the plans aren't the same...
also... hashing algorithtms take amount of memory into consideration. So
it's entirely possible that the prod server is making a mistake when it
guesses how much memory will ultimately be available for it.
You might also want to make sure statistics are up to date on prod. Out of
date stats might make the optimizer come up with bad hashing (and other)
decisions...
--
Brian Moran
"chxxx" <chxxx@.dontemailme.com> wrote in message
news:3FBB6FB8.C5FF3195@.dontemailme.com...
> sql2000 sp3.
> strange problem here. i've got an extremely complex query written by a
> developer. the query works and it comes back in a reasonable amount of
> time. however, the query generates a "hash warning/hash recursion"
> error when it runs. after reading bol, these errors don't really seem
> to be very bad. i decided to investigate it further by restoring the
> production db to a test server and try it there. guess what. no hash
> warning errors on the test server.
> prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
> allocated for sql server).
> test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
> allocated for sql server).
> the query runs slower on the test server (as expected) even with little
> to no traffic and no hash warnings.
> my next guess was that on the prod server, all of sql server's ram was
> being used by other objects. i did a dropcleanbuffers and a
> freeproccache. query still generates hash warnings.
> any ideas as to what would cause the hash warnings on prod server but
> not test server?
> i can't reboot the prod server and i can't stop sqlserver on prod
> server. are there any things i can try in sqlserver to free up
> resources other than dropcleanbuffers and freeproccache?
>
>|||I have a bizarre "Hash Warning" performance issue I am trying to
resolve - any help, explanations or thoughts appreciated:
Configuration:
sql2000 sp3 & as2000 sp3
1Gb RAM, Single 1GHz CPU
Note: All queries/testing described below is performed on the same
database on the same server. Statistics are up to date for this
database.
When drilling through on a cube, Analysis Server generates a T-SQL
SELECT and executes it via a call to sp_prepexec.
For a specific drill-through I am testing (see below for actual
T-SQL), this code takes between 28 and 40 seconds to execute
(regardless of caching).
If I copy the exact query (captured via SQL Profiler) into Query
Analyzer, the same query executes in less than 5 seconds - sub-second
on subsequent executions (i.e. with cached data).
The result is the same slow execution using either Analysis Server's
cube browser or a web-based OLAP client application.
I ran SQL Profiler to capture the execution of this code from both
Analysis Server on Drill-Through and Query Analyzer to determine any
differences, and the only differences I can find are:
1) Query Analyzer event is captured as "SQL:BatchCompleted", whereas
the Analysis Server event is captured as "RPC:Completed"
2) No Warning or Error events are generated by Query Analyzer
executing the code, however the Analysis Server-based execution
generated 3 "HASH WARNING" events. The ObjectIDs captured by SQL
Profiler for these 3 Hash Warnings are 1, 12 and 12 again for the
third event, which map to the "sysobjects" and "sysdepends" tables!!
The specific code being executed for this test case is as follows:
================================================================declare @.P1 int
set @.P1=1
exec sp_prepexec @.P1 output, N'@.P1 tinyint,@.P2 char(3),@.P3 char(3),@.P4
char(3),@.P5 char(3),@.P6 char(3),@.P7 char(3),@.P8 char(3),@.P9
char(3),@.P10 char(3),@.P11 char(3),@.P12 char(3),@.P13 char(3),@.P14
char(3),@.P15 char(3),@.P16 char(3),@.P17 char(3),@.P18 char(3),@.P19
char(3),@.P20 char(3),@.P21 char(3),@.P22 char(3),@.P23 char(3),@.P24
char(3),@.P25 char(3),@.P26 char(3),@.P27 char(3),@.P28 char(3),@.P29
char(3),@.P30 char(3),@.P31 char(3),@.P32 char(3),@.P33 char(3),@.P34
char(3),@.P35 char(3),@.P36 char(3),@.P37 char(3),@.P38 char(3),@.P39
char(3),@.P40 char(3),@.P41 char(3),@.P42 char(3),@.P43 char(3),@.P44
char(3),@.P45 char(3),@.P46 char(3),@.P47 char(3),@.P48 char(3),@.P49
char(3),@.P50 char(3),@.P51 char(3),@.P52 char(3),@.P53 char(3),@.P54
char(3),@.P55 char(3),@.P56 char(3),@.P57 char(3),@.P58 char(3),@.P59
char(3),@.P60 varchar(13),@.P61 int,@.P62 int,@.P63 varchar(7)', N'SELECT
"dbo"."mr_Activity"."DetectionDate",
"dbo"."mr_Activity"."ReversalFlag", "dbo"."mr_Activity"."VenueID",
"dbo"."mr_Activity"."ProductCode",
"dbo"."mr_Activity"."ActivityAmount", "dbo"."mr_Venue"."VenueName",
"dbo"."mr_ProductStructure"."ProductHierarchyName",
"dbo"."mr_AccountRef"."XrefNumber", "dbo"."mr_CaseRef"."WIN" FROM
"dbo"."mr_Activity", "dbo"."mr_ActivityReason", "dbo"."mr_Calendar",
"dbo"."mr_ProductStructure", "dbo"."mr_AccountRef",
"dbo"."mr_CaseRef", "dbo"."mr_Venue" WHERE
(mr_ProductStructure.ProductHierarchyID=mr_Activity.ProductCode AND
mr_AccountRef.DataWarehouseAccountID=mr_Activity.DataWarehouseAccountID
AND mr_CaseRef.DataWarehouseCaseID=mr_Activity.DataWarehouseCaseID)
AND (("dbo"."mr_Venue"."VenueID"="dbo"."mr_Activity"."VenueID")) AND
("dbo"."mr_Activity"."ActivityTypeID"=@.P1) AND
("dbo"."mr_Activity"."ProductCode" IN
(@.P2,@.P3,@.P4,@.P5,@.P6,@.P7,@.P8,@.P9,@.P10,@.P11,@.P12,@.P13,@.P14,@.P15,@.P16,@.P17,@.P18,@.P19,@.P20,@.P21,@.P22,@.P23,@.P24,@.P25,@.P26,@.P27,@.P28,@.P29,@.P30,@.P31,@.P32,@.P33,@.P34,@.P35,@.P36,@.P37,@.P38,@.P39,@.P40,@.P41,@.P42,@.P43,@.P44,@.P45,@.P46,@.P47,@.P48,@.P49,@.P50,@.P51,@.P52,@.P53,@.P54,@.P55,@.P56,@.P57,@.P58,@.P59))
AND ("dbo"."mr_ActivityReason"."BusinessType"=@.P60) AND
("dbo"."mr_Activity"."ReasonID"="dbo"."mr_ActivityReason"."ReasonID")
AND ("dbo"."mr_Activity"."ActivityTypeID"="dbo"."mr_ActivityReason"."ActivityTypeID")
AND (( DatePart(year,"dbo"."mr_Calendar"."CalendarDate") * 100) +
DatePart(month,"dbo"."mr_Calendar"."CalendarDate")=@.P61) AND
("dbo"."mr_Calendar"."CalendarDate"="dbo"."mr_Activity"."DetectionDate")
AND (( DatePart(year,"dbo"."mr_Calendar"."CalendarDate") * 10) +
"dbo"."mr_Calendar"."FiscalQtr"=@.P62) AND (''Measure''=@.P63)', 1,
'-36', '-39', '261', '222', '-40', '042', '-42', '259', '221', '-43',
'141', '-45', '030', '025', '026', '027', '028', '029', '-46', '035',
'032', '024', '-47', '282', '283', '284', '285', '286', '287', '-11',
'288', '289', '290', '291', '292', '293', '-11', '294', '295', '296',
'297', '298', '299', '-11', '-11', '-48', '-51', '220', '215', '216',
'217', '218', '219', '-53', '257', '214', '139', '-54', 'New
Business', 200307, 20031, 'Measure'
select @.P1
================================================================
The fact that Hash Warnings are appearing against sysobjects and
sysdepends in the database I find quite bizarre. I suspect these hash
warnings are related to the performance discrepancy betweeen the two
"modes" of execution (1 second vs. 30 seconds).
Any help on resolving (or even explaining) this performance
discrepancy issue is greatly appreciated.
Piquet.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message news:<epmCOPqrDHA.536@.tk2msftngp13.phx.gbl>...
> Does SQL choose the same execution plan on the production and test server?
> It's possible that the plans aren't the same...
> also... hashing algorithtms take amount of memory into consideration. So
> it's entirely possible that the prod server is making a mistake when it
> guesses how much memory will ultimately be available for it.
> You might also want to make sure statistics are up to date on prod. Out of
> date stats might make the optimizer come up with bad hashing (and other)
> decisions...
> --
> Brian Moran
>
> "chxxx" <chxxx@.dontemailme.com> wrote in message
> news:3FBB6FB8.C5FF3195@.dontemailme.com...
> > sql2000 sp3.
> >
> > strange problem here. i've got an extremely complex query written by a
> > developer. the query works and it comes back in a reasonable amount of
> > time. however, the query generates a "hash warning/hash recursion"
> > error when it runs. after reading bol, these errors don't really seem
> > to be very bad. i decided to investigate it further by restoring the
> > production db to a test server and try it there. guess what. no hash
> > warning errors on the test server.
> >
> > prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
> > allocated for sql server).
> > test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
> > allocated for sql server).
> > the query runs slower on the test server (as expected) even with little
> > to no traffic and no hash warnings.
> >
> > my next guess was that on the prod server, all of sql server's ram was
> > being used by other objects. i did a dropcleanbuffers and a
> > freeproccache. query still generates hash warnings.
> >
> > any ideas as to what would cause the hash warnings on prod server but
> > not test server?
> >
> > i can't reboot the prod server and i can't stop sqlserver on prod
> > server. are there any things i can try in sqlserver to free up
> > resources other than dropcleanbuffers and freeproccache?
> >
> >
> >sql

Handling provider-level errors from .Net SqlClient Data Provider

Hi,

After reading this helpful blog entry:

http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

I think this might be a good place to ask the following question.

I am writing the error handling code for my data access layer for a web application. I am using the Enterprise Library Data Access Application Block. Although this supports generic database connections, I realized that I need to handle errors specific to each database type. Microsoft SQL is the only database type I am using for now, so I am using a try...catch (SqlException e).

In testing my code, I intentionally changed the instance name in web.config to a name that does not exist. I get the very popular error 26 - Error Locating Server/Instance Specified. This is returned as a SqlException, but the SqlError.Number property is set to -1.

Am I getting "-1" because the provider hasn't actually connected to SQL yet, so it doesn't have an actual SQL error number? Can I assume that (SqlError.Number == -1) is always a fatal, provider-level connection exception? Will the provider ever use another SqlError.Number of its own? Or do all numbers besides -1 come from the SQL sysmessages table?. Is there a comprehensive list of what exceptions might be raised by the SqlClient provider, including #26?

The reason for all the questions is that in a web application, I want to prevent the end-user from seeing the "real" exception if it has to do with configuration errors. However, maybe there are other errors that the user should see and handle? It's hard to know without a full list of SqlClient provider errors, along with the SqlError.Number that each error maps to.

Thanks and regards,

Mark

I can only answer 1. SqlError.Number corresponds to the SQL Server message id from sys.messages table on the server, so if it is not -1 it indicates an error message coming from the server.

I believe it will be impossible to get an exhaustive list of errors numbers for each error on the server. There will always be newer error messages with new SQL Servers and they number in thousands, check out the sys.messages table on SQL Server 2005.

Thanks

Waseem

|||

Waseem Basheer - MSFT wrote:

I believe it will be impossible to get an exhaustive list of errors numbers for each error on the server. There will always be newer error messages with new SQL Servers and they number in thousands, check out the sys.messages table on SQL Server 2005.

Thanks Waseem. I'm familiar with sysmessages. For messages coming directly from SQL server, sysmessages is all I need.

I am looking for a comprehensive list of SqlClient messages that are NOT in sysmessages, i.e. the errors that are raised directly by the .NET SqlClient provider. Hopefully this is a much more limited number of messages.

Regards,

Mark

|||

Hi, mcbsys

Your question is more specific to .NET Data access, so, I recommend you post your question to the forum where many experts can help you quickly.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1

HTH

Ming.

|||

MING LV wrote:

Your question is more specific to .NET Data access, so, I recommend you post your question to the forum where many experts can help you quickly.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=45&SiteID=1

Good idea, I will try that.

Thanks,

Mark

Handling Errors...

Hello to all,

On my webPage I have Used one SQLDataSource to access DataBase. Now whenever some error occures it shows error page by default. I am not able to catch Errors and tackle in my way...

Furthermore in this new structure of accessing DataBase even I do not know where to write Try... Catch...

Hey mistry,
this is the structure for Try ... Catch ... in SQL

Begin Try
-- e.g.
INSERT INTO a SELECT * FROM b
-- your actual code
End Try
Begin Catch
-- code for error handling
End Catch

Handling errors when using ExecuteNonQuery

Let's say that I have a StringCollection named scripts that contains a bunch of DDL statements I want to execute. So I execute the statements using ExecuteNonQuery as follows:

ExecuteNonQuery(scripts)

If one of the DDL statements contained in the StringCollection returns an error, it means that all subsequent statements will not be executed. Even if I use try catch as follows:

try

{

db.ExecuteNonQuery(scripts);

}

catch (Exception ex)

{

Console.WriteLine("Error while recreating objects. See errorlog for more details\r\n");

tw.Write("Error while recreating objects: {0}\r\n", ex.GetBaseException().ToString());

tw.Flush();

}

Is there any way to keep executing the subsequent statements in the StringCollection even if some of the DDL statements return an error?

If you send them one at a time, handle the error that occurs, then send the next script, you could then continue after an error. DDL errors, however, always make me want to stop, because DML usually depends on the DDL being correct.

|||

Thanks for your answer Allen,

I tried what you suggested but I noticed that none of my stored procs kept their settings. For example:

I script my proc as follows:

if (u.Type == "StoredProcedure")

{

ScriptingOptions so = new ScriptingOptions();

StoredProcedure o = (StoredProcedure)svr.GetSmoObject(u);

so.SchemaQualify = true;

so.SchemaQualifyForeignKeysReferences = true;

so.Permissions = true;

so.ExtendedProperties = true;

so.EnforceScriptingOptions = true;

so.NoCollation = true;

foreach (string os in o.Script(so))

{

sc.Add(os.ToString());

}

}

If I try to execute my statement as follows:

foreach (string s in scripts)

{

db.ExecuteNonQuery(s);

}

Then two statementswill be sent to SQL Server and the ANSI_NULL Settings will not necessarily be maintained:

first statement:

SET ANSI_NULLS ON

Second statement:

CREATE PROCEDURE...

The problem being that if the setting for my second connection is SET ANSI_NULLS OFF then my proc will be created with the wrong settings.

Handling errors when using a Lookup Task

Hi

I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself), I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.

Any help would be appreciated

Thanks

Darrell

Darrell,

You need to configure the error output of the lookup component to redirect the rows that fail the lookup. you can then route the error flow to insert the records.

Frank

|||

Frank

I have already configured the error output of the lookup task to redirect the error rows, however, the task stops after processing the errors of the lookup task because the number of errors exceeds the MaximumErrorCounty, even though I don't want it to stop processing. For the error output I am redirecting the rows to a Derived field task, so that I can add additional fields ready for the SQL task of inserting the information. However, it doesn't even get to execute the derived task because of this problem with the MaximumErrorCount

Any other ideas?

Thanks

Darrell

|||

DarrellMerryweather wrote:

Frank

I have already configured the error output of the lookup task to redirect the error rows, however, the task stops after processing the errors of the lookup task because the number of errors exceeds the MaximumErrorCounty, even though I don't want it to stop processing. For the error output I am redirecting the rows to a Derived field task, so that I can add additional fields ready for the SQL task of inserting the information. However, it doesn't even get to execute the derived task because of this problem with the MaximumErrorCount

Any other ideas?

Thanks

Darrell

Darrell,

I've used this technique on many occasions and trust me - its not affected by MaximumErrorCount. I've diverted millions of rows down the error output of a LOOKUP component when MaximumErrorCount=1 and the data-flow succeeds.

Are you sure there isn't another error occurring somewhere?

-Jamie

P.S. For nomenclature clarity, the toolbox items that appear inside a data-flow ar called components, not tasks!

|||

DarrellMerryweather wrote:

Hi

I am trying to use this painful new SSIS process. I basically need to use a lookup task to check to see whether a record exists or not. If not, then I need to insert the record. However, because this is treated as an error situation (which is stupid in itself),

Why is that stupid? The objective here is to achieve a business requirement - does the specifics of how it is achieved really matter?

DarrellMerryweather wrote:

I get a problem when the number of records not found reach the MaximumErrorCount, and the rest of the package fails. Is there any other method of doing this type of thing, without simply increasing the MaximumErrorCounty to some ludicrous value. I could do this type of thing very very very easily when using DTS packages using the Data Driven Task, it seems so stupid that I can't perform the same kind of task using SSIS.

I promise you this CAN be achieved. Persevere - you'll find the problem eventually

Perhaps check the ForceExecutionResult property.

-Jamie

|||

Guys

I was actually getting an error on the input of the derived field, where it was truncating the value coming in.

Apologies and thanks for the help, the package is now running sucessfully

Thanks again

D

Handling errors returned by SSRS

We are displaying the report in our reporting application but we do not want to display errors from SSRS to the user. We want to handle the errors and display a user friendly message.

How can that be done?. We are making URL access to the report server.

Thanks.

Please help me to understand this better. If you use URL addressability what application layer will handle the error messages? If you use the VS.NET 2005 Report Viewer control, your application can handle the ReportError event.|||

Alright,

So we are using an iframe in our application which we are making a url call to the report server. Hence, if there is a problem like "access" denied, we do not want the iframe to read "SQL Server error" but have some error that shows that the user is interacting with our application. This can only be done if on the report server we could write some generic error page which will always get called anytime ssrs throws an error.

Thanks.

|||Sorry, you are out of lack here. URL addressability is certainly very easy but not that flexible. Same limitations apply as invoking a server-side web page by URL.|||

Hi,

I have my report viewer control and reporterror event to handel all the exception.Do we need to call the reporterror event in my code or automatically it will be called when error occurs?

Thanks,

Ranjan

|||Not sure what you mean by call the event. The event handler will be called for you when the event happens (in this case the report errors out).|||

Hi Teo,

If u have any sample code of how to show a report in reportviewer along with reporterror event and if you can post it here it would be very helpful.

Thanks,

Ranjan

|||

private void reportViewer1_ReportError(object sender, Microsoft.Reporting.WinForms.ReportErrorEventArgs e)

{

// use e.Exception to get to the exception

// set e.Handled to true to prevent the ReportViewer from displaying an error message.

}

More about ReportViewer in this article.

Handling errors returned by SSRS

We are displaying the report in our reporting application but we do not want to display errors from SSRS to the user. We want to handle the errors and display a user friendly message.

How can that be done?. We are making URL access to the report server.

Thanks.

Please help me to understand this better. If you use URL addressability what application layer will handle the error messages? If you use the VS.NET 2005 Report Viewer control, your application can handle the ReportError event.|||

Alright,

So we are using an iframe in our application which we are making a url call to the report server. Hence, if there is a problem like "access" denied, we do not want the iframe to read "SQL Server error" but have some error that shows that the user is interacting with our application. This can only be done if on the report server we could write some generic error page which will always get called anytime ssrs throws an error.

Thanks.

|||Sorry, you are out of lack here. URL addressability is certainly very easy but not that flexible. Same limitations apply as invoking a server-side web page by URL.|||

Hi,

I have my report viewer control and reporterror event to handel all the exception.Do we need to call the reporterror event in my code or automatically it will be called when error occurs?

Thanks,

Ranjan

|||Not sure what you mean by call the event. The event handler will be called for you when the event happens (in this case the report errors out).|||

Hi Teo,

If u have any sample code of how to show a report in reportviewer along with reporterror event and if you can post it here it would be very helpful.

Thanks,

Ranjan

|||

private void reportViewer1_ReportError(object sender, Microsoft.Reporting.WinForms.ReportErrorEventArgs e)

{

// use e.Exception to get to the exception

// set e.Handled to true to prevent the ReportViewer from displaying an error message.

}

More about ReportViewer in this article.

Handling errors in transactional replication

Hello,
I have this problem, I have transactional replication
setup with 2 table as article for a publisher. Consider a
transaction which inserts one row into each of these
table in the publisher database. At subscriber database
one of the commands suceeds and the other fails (lets say
because of duplicate value for a key field).
What I would like is ...
1. Enable the error to be logged to a application table.
2. Have both the commands fail (not just one)
3. Have the replication continue without erroring out
4. Have the erroneous trasction be tried at a later time.
Could help me resolving this sticky problem.
Answers on how to acheive any of the points are welcome
too.
Thanks,
Ramuk
If you have a transaction on the publisher, you may check @.@.error and then
call rollback, but whether you rollback or not the sp is still executed on
the subscriber. This situation is altered (no subscriber call) if you set
the transaction isolation level to serializable. This is important to do
because even if you trap the same error in the transaction on the subscriber
and rollback there, the error is registered and the distribution agent will
fail. SkipErrors would avoid this problem but ideally the call shouldn't be
sent from the publisher to the subscriber if it has already failed once.
Logging of this error could be done from within the transaction using
xp_logevent or simply inserting to a SQL table.
HTH,
Paul
|||Thanks for the response ... though the problem I face is, that the transaction suceeds on the publisher. Its only on the subscriber that it fails.
Any ideas?
-- Paul Ibison wrote: --
If you have a transaction on the publisher, you may check @.@.error and then
call rollback, but whether you rollback or not the sp is still executed on
the subscriber. This situation is altered (no subscriber call) if you set
the transaction isolation level to serializable. This is important to do
because even if you trap the same error in the transaction on the subscriber
and rollback there, the error is registered and the distribution agent will
fail. SkipErrors would avoid this problem but ideally the call shouldn't be
sent from the publisher to the subscriber if it has already failed once.
Logging of this error could be done from within the transaction using
xp_logevent or simply inserting to a SQL table.
HTH,
Paul
|||Ramul,
in theory this shouldn't be possible! If all the articles are replicated
then the same transaction should be applied.
What is the error on the subscriber?
Regards,
Paul
|||Paul,
We are in the design stage ... so the problem is yet theoretical.
The problem is when two commands which are part of the transaction are applied at the subsciber, it could be possible for one of the commands to fail (because of business logic at subscribers end) .
If I were to go the route of using "Continue on Data Consistency Errors" then the failed command will not be applied on the subscriber but the other command will ... which cause an invalid state. If I do not set "Continue on Data Consistency Errors" ... t
hen the replication would stop... that is the crux of the problem.
So an ideal solution would be for both the commands not being applied in case of an error on any one ... the error being logged into an application table (for purposes of monitoring) ... and if possible the transaction marked for retrial later.
Also another question ... can I get the value of the "Transaction sequence number" at the distributer? Can it be passed to the Custom SP's at the subscriber?
Thanks for the help,
Ramuk.
|||Ramuk,
my recollection is that if the 2 data modifications are wrapped in a
transaction which has error-trapping (or set xact_abort on) then the
transaction will be successfully completely rolled back on an error at the
subscriber. I haven't tried this out in all circumstances and AFAIR you'll
still need -SKIPERRORS. Am off home now, but will retest it out some time
tomorrow.
Regards,
Paul

Handling errors in DTS packages

HI!!

I've been working for an year or so with DTS, but it still makes me mad with it's cryptic error messages!!!!

"The task reported failure on execution" is one of the "funny" error messages I retrieve. I've tried with the log option, but error messages stored there are as cryptic as the one shown on the screen!!!!!

Timothy Peterson in "MS SQL Server 2000 DTS" provides code chunks that can be used to "decode" numerical error messages into something readable and understandable, but I really don't realize where should I put that code :( It seems to work only if you are executing packages via Visual Basic, and not using the MMC

That's it, I really do need help with this!!!!!!! I beleive that there's someone out there that had faced and solved this problem !

Thanks in advance
lorenaWhy do you have to use DTS?|||I'm working on an ETL process for a Data Warehouse
Oracle "holds" the source databases and the DW is implemented on SQL Server 2000.


Originally posted by Brett Kaiser
Why do you have to use DTS?

Handling Errors

Is it possible to handle an FK violation error in SQL Server 2000?
IOW, if I encounter this error, is it possible for the code in my proc to
execute alternative code rather than immediately exiting with an error
message?
I can write code to always test for a FK violation before I try to insert,
but is it possible to do the same thing with error handling (i.e., try the
insert straight off and then if I get an error, do something else)?
I believe I can do this with the TRY/CATCH in SQL Server 2005 but I was
wondering if it is possible with the more limited error handling of SQL
Server 2000.
DaveHi, Dave
Yes, it is possible in a stored procedure, but not if the code is in a
trigger or called from a trigger. For example:
USE Northwind
INSERT INTO Territories
(TerritoryID, TerritoryDescription, RegionID)
VALUES (10000, 'Mars', 7)
IF @.@.ERROR<>0 BEGIN
INSERT INTO Region
(RegionID, RegionDescription)
VALUES (7, 'Outer space')
INSERT INTO Territories
(TerritoryID, TerritoryDescription, RegionID)
VALUES (10000, 'Mars', 7)
END
DELETE Territories WHERE RegionID=7
DELETE Region WHERE RegionID=7
However, it's recommended that you check for the FK violation before
the insert, because if you call this from ADO, it is difficult to
handle the error from the client side (it will still be raised on the
client and you don't know if it was handled by the procedure or if it
is a real error that should be logged or reported to the user).
You should also know that there are some errors that cannot be handled
this way in the procedure, because SQL Server aborts the batch when it
encounters errors like conversion errors, for example.
For more informations, see this excellent article by Erland Sommarskog,
SQL Server MVP:
http://www.sommarskog.se/error-handling-I.html
Razvan|||Thank you very much Razvan!
That is indeed an excellent article by Erland that you refernce.
Dave
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1116564456.658264.99430@.g44g2000cwa.googlegroups.com...
> Hi, Dave
> Yes, it is possible in a stored procedure, but not if the code is in a
> trigger or called from a trigger. For example:
> USE Northwind
> INSERT INTO Territories
> (TerritoryID, TerritoryDescription, RegionID)
> VALUES (10000, 'Mars', 7)
> IF @.@.ERROR<>0 BEGIN
> INSERT INTO Region
> (RegionID, RegionDescription)
> VALUES (7, 'Outer space')
> INSERT INTO Territories
> (TerritoryID, TerritoryDescription, RegionID)
> VALUES (10000, 'Mars', 7)
> END
> DELETE Territories WHERE RegionID=7
> DELETE Region WHERE RegionID=7
> However, it's recommended that you check for the FK violation before
> the insert, because if you call this from ADO, it is difficult to
> handle the error from the client side (it will still be raised on the
> client and you don't know if it was handled by the procedure or if it
> is a real error that should be logged or reported to the user).
> You should also know that there are some errors that cannot be handled
> this way in the procedure, because SQL Server aborts the batch when it
> encounters errors like conversion errors, for example.
> For more informations, see this excellent article by Erland Sommarskog,
> SQL Server MVP:
> http://www.sommarskog.se/error-handling-I.html
> Razvan
>|||The "set xact_abort on / off" setting determines whether some exceptions
immediately abort the transaction or continue processing with @.@.error.
"Dave" <dave@.nospam.ru> wrote in message
news:OvTcy4MXFHA.1148@.tk2msftngp13.phx.gbl...
> Is it possible to handle an FK violation error in SQL Server 2000?
> IOW, if I encounter this error, is it possible for the code in my proc to
> execute alternative code rather than immediately exiting with an error
> message?
> I can write code to always test for a FK violation before I try to insert,
> but is it possible to do the same thing with error handling (i.e., try the
> insert straight off and then if I get an error, do something else)?
> I believe I can do this with the TRY/CATCH in SQL Server 2005 but I was
> wondering if it is possible with the more limited error handling of SQL
> Server 2000.
> Dave
>

Handling database connection errors

If my database goes down, i get a popup error with the red x saying can't
connect to database, can't find the cubes, or the cubes are busy, etc...
Is there a way to change the error message to say:
"Report Cubes are being refreshed right now, please try again" in the
popup.
Or point to another page?No this is not possible, unless you implement your own custom data extension
which wraps the original data provider and throws specific exceptions with
your custom error message (which will then show up as inner error message).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cindy Lee" <cindylee@.hotmail.com> wrote in message
news:OfpbodJ3EHA.2804@.TK2MSFTNGP15.phx.gbl...
> If my database goes down, i get a popup error with the red x saying can't
> connect to database, can't find the cubes, or the cubes are busy, etc...
> Is there a way to change the error message to say:
> "Report Cubes are being refreshed right now, please try again" in the
> popup.
> Or point to another page?
>|||Is this an IIS thing?
Easy to do?
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OxvRcpP3EHA.1524@.TK2MSFTNGP09.phx.gbl...
> No this is not possible, unless you implement your own custom data
extension
> which wraps the original data provider and throws specific exceptions with
> your custom error message (which will then show up as inner error
message).
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Cindy Lee" <cindylee@.hotmail.com> wrote in message
> news:OfpbodJ3EHA.2804@.TK2MSFTNGP15.phx.gbl...
> > If my database goes down, i get a popup error with the red x saying
can't
> > connect to database, can't find the cubes, or the cubes are busy,
etc...
> >
> > Is there a way to change the error message to say:
> >
> > "Report Cubes are being refreshed right now, please try again" in the
> > popup.
> >
> > Or point to another page?
> >
> >
>

Handle SQL errors with severity 10

how to handle sql errors with severity less then 10 in .NET

i call to stored procedure which raise error with severity 10 for example
however in c# i can't cach this error (with severiry >= 11 it going to the
catch block in my c# app), also the @.@.ERROR is equal to zero when severity
<= 10

--
Message posted via http://www.sqlmonster.comE B via SQLMonster.com (forum@.SQLMonster.com) writes:
> how to handle sql errors with severity less then 10 in .NET
> i call to stored procedure which raise error with severity 10 for example
> however in c# i can't cach this error (with severiry >= 11 it going to the
> catch block in my c# app), also the @.@.ERROR is equal to zero when severity
><= 10

You set up an InfoMessages event handler. Please see the .Net Framework
reference for details.

In SqlClient 2.0, there is a new property which permits you divert all
errors with severity <= 16, that is all user errors, to the InfoMessage
handler. This actually has a couple of advantages, particulary when
you use ExecuteReader, so there is all reason to get used to InfoMessages.

By the way, a small tidbit: you can never get a message with severity
10 from SQL Server. 10 is always changed to level 0.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||ok i understand., Thank u in andvance.

Learning learning an learning

--
Message posted via http://www.sqlmonster.com

handle errors in trigger

How to handle error in trigger.
i m inserting record in other table in after insert trigger. but if insert
statement in trigger has error, procedure quits with error and i cannot use
@.@.error to check itThats an artifical error, but that the way you could handle it:
Create Table SomeTable
(
SomeColumn INT
)
Go
ALTER TRIGGER SomeTrigger
on SomeTable
FOR INSERT
AS
BEGIN
RAISERROR('SomeError',16,1)
PRINT @.@.ERROR
IF @.@.ERROR <> 0
PRINT 'Seems that an error occured.'
END
INSERT INTO SomeTable VALUES (1)
DELETE FROM SomeTable
SELECT * FROM SomeTable
Keep in mind that some error aren=B4catchable due to the severity.
I suggest taking a look in Erland=B4s Error handling script
http://www.sommarskog.se/error-hand...triggercontext.
HTH, jens Suessmeyer.|||I don't know exactly what you're doing but there are a couple of things
you may want to check before you do an insert.
If you're using variables in your values list check them before you
insert if they aren't null in case your table has colums with not null
defined.
But in general do have a look at Erland's article.|||Ya variable is null and table does not allow null, but error should be
trapable isnt it ?
"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1137149721.665336.187300@.g44g2000cwa.googlegroups.com...
> I don't know exactly what you're doing but there are a couple of things
> you may want to check before you do an insert.
> If you're using variables in your values list check them before you
> insert if they aren't null in case your table has colums with not null
> defined.
> But in general do have a look at Erland's article.
>|||Error handling in sql server 2000 within triggers is tricky business,
have a look at Erland's article.
http://www.sommarskog.se/error-hand...triggercontext.
But since you know that the variable is null then something like this
may avoid the error:
DECLARE @.var int
SET @.var = ISNULL(@.var, 1) /* the variable will be assigned the value
of 1 if it is null
or
IF(@.var) IS NULL
BEGIN
-- write all variables to a log table
INSERT INTO ...
RETURN
END|||The AFTER trigger fires *after* the insert - hence the name. In case of a
constraint violation (such as the nullability constraint) the the AFTER
trigger will not fire at all, since the insert is aborted.
Consider using an INSTEAD OF trigger or better yet - set values properly
before inserting them or use a deafult.
ML
http://milambda.blogspot.com/|||who says the variable is coming from inserted?|||I was guessing based on OP's narrative.
ML
http://milambda.blogspot.com/|||from OP
> but if insert statement in trigger|||> but if insert statement in trigger

HANDLE ERRORS IN SP_XML_PREPAREDOCUMENT

I'm working on a procedure to import XML-data in a database. First I want to
verify that the XML is correct. My idea was to use SP_XML_PREPAREDOCUMENT an
d
handle any error messages returned.
The problem is, when the procedure reports a parse error my procedure stops.
I can't get my procedure to go on and just report the error. ...
My (simplified) code looks like this:
CREATE PROCEDURE SP_ADD_XML
@.XML_XMLSTR TEXT
AS
BEGIN
DECLARE @.FILEHANDLE INT
DECLARE @.RESULT INT
EXEC @.RESULT=SP_XML_PREPAREDOCUMENT @.FILEHANDLE output, @.XML_XMLSTR
...
END
GO
If I try to parse a bad XML code the procedure stops when executing
SP_XML_PREPAREDOCUMENT with an error:
"Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line
2
XML parsing error: End tag 'pv_best' does not match the start tag 'typ'. "
Can I prevent MY procedure from stopping when I send bad XML-data to
SP_XML_PREPAREDOCUMENT?Hi
I am guessing you are using SQL Server 2000.
You could try creating a wrapper sproc for the sp_xml_preparedocument, that
returns the handle number, calling it in your outer sproc and handling error
s
generated where your wrapper sproc fails.
The errors will still be thrown but your outer sproc will carry on. A word
of caution - make sure you clean up after it. with sp_xml_removedocument
otherwise you will have trouble with memory getting full of junk XML DOMs.
In fact this may happen anyway with bad XML since you will not get a valid
handle back from sp_xml_preparedocument if it fails and it will have grabbed
some memory. I found the only way to avoid this is to reboot your server
when you start getting memory errors.
Regards
John Harrison
"LakritZtrollet" wrote:

> I'm working on a procedure to import XML-data in a database. First I want
to
> verify that the XML is correct. My idea was to use SP_XML_PREPAREDOCUMENT
and
> handle any error messages returned.
> The problem is, when the procedure reports a parse error my procedure stop
s.
> I can't get my procedure to go on and just report the error. ...
> My (simplified) code looks like this:
> CREATE PROCEDURE SP_ADD_XML
> @.XML_XMLSTR TEXT
> AS
> BEGIN
> DECLARE @.FILEHANDLE INT
> DECLARE @.RESULT INT
> EXEC @.RESULT=SP_XML_PREPAREDOCUMENT @.FILEHANDLE output, @.XML_XMLSTR
> ...
> END
> GO
>
> If I try to parse a bad XML code the procedure stops when executing
> SP_XML_PREPAREDOCUMENT with an error:
> "Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Li
ne
> 2
> XML parsing error: End tag 'pv_best' does not match the start tag 'typ'. "
> Can I prevent MY procedure from stopping when I send bad XML-data to
> SP_XML_PREPAREDOCUMENT?

Handle Errors In Sp_xml_preparedocument

I'm working on a procedure to import XML-data in a database. First I want to
verify that the XML is correct. My idea was to use SP_XML_PREPAREDOCUMENT a
nd handle error messages returned.
The problem is, when the procedure reports a parse error the procedure stops
.
I can't get my procedure to go on and just report the error.
My (simplified) code looks like this:
CREATE PROCEDURE SP_ADD_XML
@.XML_XMLSTR TEXT
AS
BEGIN
DECLARE @.FILEHANDLE INT
DECLARE @.RESULT INT
EXEC @.RESULT=SP_XML_PREPAREDOCUMENT @.FILEHANDLE output, @.XML_XMLSTR
...
END
GO
If I try to parse a bad XML document the procedure stops when executing SP_X
ML_PREPAREDOCUMENT with an error:
Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line
2
XML parsing error: End tag 'pv_best' does not match the start tag 'typ'.
Can I prevent MY procedure from stopping when I send wrong XML-data to SP_XM
L_PREPAREDOCUMENT?Hello LakritZtrollet,
SQL 2000 or 2005?
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||SQL2000 SP3
"Kent Tegels" skrev:

> Hello LakritZtrollet,
> SQL 2000 or 2005?
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hello LakritZtrollet,
Sorry, no good suggestions here.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Handle DTS Errors

Hi,
I'm using DTS for pulling data from Oracle into SQL Server.

I retrieve the records from Oracle and insert into a staging table and from there invoke an ExecuteTask in DTS to transfer the data to the SQL Server table deciding on whether to Insert / Update / Delete appropriately based on certain parameters.

During the Insert / Update / Delete there might be constraints on the original SQL Server table which might throw errors in case of data inconsistencies. I want to track the errors arising in this scenario and take a call on whether to ignore them or not.

When I schedule the DTS package from the designer the constraint violations are picked up by the DTS and it stops the package execution. Later I coded the same on VBScript and I've handled the error using the WithEvents along with the Package2 object and tried giving a Cancel = False in the OnError Event.

But this does not seem to be working. The package is still exiting abnormally. The piece of code which I've used to Cancel the DTS Error is given below.

Private Sub goPackage_OnError(ByVal EventSource As String, _
ByVal ErrorCode As Long, _
ByVal Source As String, _
ByVal Description As String, _
ByVal HelpFile As String, _
ByVal HelpContext As Long, _
ByVal IDofInterfaceWithError As String, _
ByRef pbCancel As Boolean)

Debug.Print "DTSPackage - Error"
pbCancel = False
End Sub

Private Sub goPackage_OnQueryCancel(ByVal EventSource As String, _
ByRef pbCancel As Boolean)

Debug.Print "DTSPackage - Query cancelled"
pbCancel = False
End Sub

Setting pbCancel = False should ideally inform the DTS package to continue with execution and ignore the Errors but then the package is still terminating abnormally when an error is encountered and the rest of the records do not get processed.

Any suggestions in this regard would be greatly appreciated.A couple of thoughts:

1. Your code includes the statements Debug.print. I don't think that Debug.print is supported in VBScript and it's not supported at runtime in VB.

2. Have you looked at DTSTransformStat_SkipRow? I'm having a hard time picturing where this code is in your DTS Package. However, if you use the ActiveX script to copy data over, you can specify that on an error the row should be skipped (and you can log that row if you wanted). For example:

Function Main

DTSDestination("Foo") = DTSSource("Foo")
DTSDestination("Bar") = DTSSource("Bar")

errCount = 0

' Do your exception handling here
If DTSSource("Foo") > 10 Then
errCount = errCount + 1
End if

If DTSSource("Bar") < 8 Then
errCount = errCount + 1
End if

If errCnt > 0 Then
Main = DTSTransformStat_SkipRow
Else
Main = DTSTransformStat_OK
End If
End function

Note that this is probably a very sloppy (not to mention slow) way of doing things. Nevertheless, it is a potential solution to your question.

Hopefully, it will give you some ideas that you can pursue.

Regards,

Hugh Scott

Originally posted by Mohana Krishnan
Hi,
I'm using DTS for pulling data from Oracle into SQL Server.

I retrieve the records from Oracle and insert into a staging table and from there invoke an ExecuteTask in DTS to transfer the data to the SQL Server table deciding on whether to Insert / Update / Delete appropriately based on certain parameters.

During the Insert / Update / Delete there might be constraints on the original SQL Server table which might throw errors in case of data inconsistencies. I want to track the errors arising in this scenario and take a call on whether to ignore them or not.

When I schedule the DTS package from the designer the constraint violations are picked up by the DTS and it stops the package execution. Later I coded the same on VBScript and I've handled the error using the WithEvents along with the Package2 object and tried giving a Cancel = False in the OnError Event.

But this does not seem to be working. The package is still exiting abnormally. The piece of code which I've used to Cancel the DTS Error is given below.

Private Sub goPackage_OnError(ByVal EventSource As String, _
ByVal ErrorCode As Long, _
ByVal Source As String, _
ByVal Description As String, _
ByVal HelpFile As String, _
ByVal HelpContext As Long, _
ByVal IDofInterfaceWithError As String, _
ByRef pbCancel As Boolean)

Debug.Print "DTSPackage - Error"
pbCancel = False
End Sub

Private Sub goPackage_OnQueryCancel(ByVal EventSource As String, _
ByRef pbCancel As Boolean)

Debug.Print "DTSPackage - Query cancelled"
pbCancel = False
End Sub

Setting pbCancel = False should ideally inform the DTS package to continue with execution and ignore the Errors but then the package is still terminating abnormally when an error is encountered and the rest of the records do not get processed.

Any suggestions in this regard would be greatly appreciated.|||Thanks buddy. This did give me a idea. I split my tables into Staging tables and Temporary tables. Finally added a task in between to make sure that all the erroneous records are filtered out and moved to the respective error tables. I wanted to check for foreign key violations mostly. Later I went ahead and inserted the rest of the records in the staging tables directly into the master tables. This worked out fine.

Thanks anyway.
Regards
Mohan

Halting Execution On Error

I have a simple SSIS package split into two parts; validation and processing. I want to be able to stop execution on any package errors (such as file not found, etc) using the OnError event handler. Is this at all possible?

No, if you want to stop execution when a task fails then make sure there are no OnCompletion or OnFailure precedence constraints leading from it.

As long as you don't have any concurrent execution paths then the package will stop.

-Jamie

|||Ah. Perfect. I've been using expressions and noticed the expression and constraint option but it didn't hit me to use that until you said something.

Thanks Jamie, for helping out a greenfoot here.