Continuing on error...

Is it possible to continue on with the newer replication operations even when
an error occurs ? I have set up transaction replication between two SQL
servers. When an error occurs, all the subsequent changes to the database are
not replicated, even if they might be error - free. Can this be done ?
Prakash.
Yes. See the page titled 'distribution agent utility' in SQL Server Books
Online. Distribution agent has a parameter caleld -SkipErrors, that can be
used for this purpose.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Prakash" <prakash@.msn.com> wrote in message
news:85041798-9961-4091-A593-5E58B0EA6426@.microsoft.com...
Is it possible to continue on with the newer replication operations even
when
an error occurs ? I have set up transaction replication between two SQL
servers. When an error occurs, all the subsequent changes to the database
are
not replicated, even if they might be error - free. Can this be done ?
Prakash.
|||The correct page title is "Replication Distribution Agent Utility"
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Prakash" <prakash@.msn.com> wrote in message
news:85041798-9961-4091-A593-5E58B0EA6426@.microsoft.com...
Is it possible to continue on with the newer replication operations even
when
an error occurs ? I have set up transaction replication between two SQL
servers. When an error occurs, all the subsequent changes to the database
are
not replicated, even if they might be error - free. Can this be done ?
Prakash.
|||there is also the continue on data consistency errors, which you can access
by right clicking on your distirbution agent, selecting agent profiles, and
then selecting this profile.
After changing your profile you need to stop and start your distribution
agent.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Prakash" <prakash@.msn.com> wrote in message
news:85041798-9961-4091-A593-5E58B0EA6426@.microsoft.com...
> Is it possible to continue on with the newer replication operations even
when
> an error occurs ? I have set up transaction replication between two SQL
> servers. When an error occurs, all the subsequent changes to the database
are
> not replicated, even if they might be error - free. Can this be done ?
> Prakash.

Continued issues with SQL2K5 - SP2

I continue to have issues with SP-2 for SQL 2005 suite. It has now gotten so bad, I have multiple installations, including the TOOLS ONLY on my laptop failing, that I am going to stop ALL future installations of SP-2. The COM+ failures have not been resolved that I can determine. I have tried uninstalling, I have tried SP-1 before SP-2, every combination one can find. HELP.

Time: 05/07/2007 13:24:05.631
KB Number: KB921896
Machine: GA029-MDGRAVES
OS Version: Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Package Language: 1033 (ENU)
Package Platform: x86
Package SP Level: 2
Package Version: 3042
Command-line parameters specified:
Cluster Installation: No

**********************************************************************************
Prerequisites Check & Status
SQLSupport: Passed

**********************************************************************************
Products Detected Language Level Patch Level Platform Edition
Setup Support Files ENU 9.00.1399.06 x86
SQL Server Native Client ENU 9.00.3042.00 x86
Client Components ENU RTM 9.00.1399.06 x86 STANDARD
MSXML 6.0 Parser ENU 6.00.3883.8 x86
Backward Compatibility ENU 8.05.1054 x86

**********************************************************************************
Products Disqualified & Reason
Product Reason

**********************************************************************************
Processes Locking Files
Process Name Feature Type User Name PID

**********************************************************************************
Product Installation Status
Product : Setup Support Files
Product Version (Previous): 1399
Product Version (Final) : 3042
Status : Success
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\Redist9_Hotfix_KB921896_SqlSupport.msi.log
Error Number : 0
Error Description :
-
Product : SQL Server Native Client
Product Version (Previous): 3042
Product Version (Final) :
Status : Not Selected
Log File :
Error Description :
-
Product : Client Components
Product Version (Previous): 1399
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQLTools9_Hotfix_KB921896_sqlrun_tools.msp.log
Error Number : 29549
Error Description : MSP Error: 29549 Failed to install and configure assemblies c:\Program Files\Microsoft SQL Server\90\NotificationServices\9.0.242\Bin\microsoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: -2146233087
Error message: Unknown error 0x80131501
Error description: MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)
-
Product : MSXML 6.0 Parser
Product Version (Previous): 3883
Product Version (Final) : 6.10.1129.0
Status : Success
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\Redist9_Hotfix_KB921896_msxml6.msi.log
Error Number : 0
Error Description :
-
Product : Backward Compatibility
Product Version (Previous): 1054
Product Version (Final) : 2004
Status : Success
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\Redist9_Hotfix_KB921896_SQLServer2005_BC.msi.log
Error Number : 0
Error Description :
-

**********************************************************************************
Summary
One or more products failed to install, see above for details
Exit Code Returned: 29549

Here's an external blog that discusses the workaround for the issue:

http://geekswithblogs.net/waterbaby/archive/2006/08/03/87048.aspx

Thanks,
Sam Lester (MSFT)

|||The link to XP fix was great. Thanks.

Continue table only on next page

Hello!
I have 3 elements in a report, a header, a body containing a table and a
footer. The header and footer does not need to be a "header" or a "footer".
I want the footer to be placed at the bottom of the page below the allocated
space for the body (regardless how many rows there are in the list) and I
want the list to continue on the next page if it exceeds the allocated space
for the body and finally I want the footer only to be on the first page. The
footer cannot be a table footer.
How can this be done?
For the footer I have tried:
1. If I use a footer to the bank giro my only choice is
"printonlastpage=false" but then, if there are 3 or more pages it would be
printed on page 1, page 2 etc but not on the last page.
2. If I use a textbox below the table containing the invoice items I cannot
find a method to only put the textbox only on the first page, it will only
be on the last page, after the invoiceitems table on the last page.
Thanks,
MariusMarius,
I posted a sample RDL for the problem you posted on June 22, 2004 - Making
an invoice. Please let me know if that proposed solution meets your
requirements. If I am not mistaken this new request deals with the same
issue.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marius Trælnes" <marius.traelnesnospam@.nospamc2i.net> wrote in message
news:%23bKmMsdWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have 3 elements in a report, a header, a body containing a table and a
> footer. The header and footer does not need to be a "header" or a
"footer".
> I want the footer to be placed at the bottom of the page below the
allocated
> space for the body (regardless how many rows there are in the list) and I
> want the list to continue on the next page if it exceeds the allocated
space
> for the body and finally I want the footer only to be on the first page.
The
> footer cannot be a table footer.
> How can this be done?
> For the footer I have tried:
> 1. If I use a footer to the bank giro my only choice is
> "printonlastpage=false" but then, if there are 3 or more pages it would be
> printed on page 1, page 2 etc but not on the last page.
> 2. If I use a textbox below the table containing the invoice items I
cannot
> find a method to only put the textbox only on the first page, it will only
> be on the last page, after the invoiceitems table on the last page.
> Thanks,
> Marius
>
>|||Hello!
When In Outlook Express I push "Get next 300 heaers" your response came...
:-o
Thank you! I will have a look.
Marius
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:%23pQHOqgWEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Marius,
> I posted a sample RDL for the problem you posted on June 22, 2004 - Making
> an invoice. Please let me know if that proposed solution meets your
> requirements. If I am not mistaken this new request deals with the same
> issue.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Marius Trælnes" <marius.traelnesnospam@.nospamc2i.net> wrote in message
> news:%23bKmMsdWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> > Hello!
> >
> > I have 3 elements in a report, a header, a body containing a table and a
> > footer. The header and footer does not need to be a "header" or a
> "footer".
> >
> > I want the footer to be placed at the bottom of the page below the
> allocated
> > space for the body (regardless how many rows there are in the list) and
I
> > want the list to continue on the next page if it exceeds the allocated
> space
> > for the body and finally I want the footer only to be on the first page.
> The
> > footer cannot be a table footer.
> >
> > How can this be done?
> >
> > For the footer I have tried:
> > 1. If I use a footer to the bank giro my only choice is
> > "printonlastpage=false" but then, if there are 3 or more pages it would
be
> > printed on page 1, page 2 etc but not on the last page.
> > 2. If I use a textbox below the table containing the invoice items I
> cannot
> > find a method to only put the textbox only on the first page, it will
only
> > be on the last page, after the invoiceitems table on the last page.
> >
> > Thanks,
> > Marius
> >
> >
> >
> >
>

Continue SP after Database Access Failure

Over night we take a copy of various live SQL databases onto another SQL
server for reporting purposes.
I have a stored procedure that compares the latest live data against the 1
day old copies to ensure that they are up to date.
I connect to the live databases using linked servers.
Here's where the problem is - when one of the external links is down or one
of the live databases is offline the stored procedure has an error and stops
.
How can I test within the stored procedure that the database on the linked
server is available? Then, based on the result, carry out an action?
Even a simple select statement against an unavailable database halts the
whole SP even though I've tried breaking the code down into seperate
transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
fails with "SQL Server does not exist or access denied."
Any advice greatly appreciated.Hi Paula,
Error handling in SQL Server 2000 is "somewhat" problematic as you have
seen.
For these cases, I use the following trick of nesting the execution scopes:
USE tempdb
select * from nonexist
select 'passed after error', @.@.error
go
-- batch was terminated without returning the message
exec ('select * from nonexist')
select 'passed after error', @.@.error
go
-- inner scope was aborted, outer scope continued
create proc p3 as
select * from nonexist
select 'passed after error', @.@.error
go
exec p1
-- batch was terminated without returning the message
create proc p2 as
select * from nonexist
go
create proc p3 as
exec p2
select 'passed after error', @.@.error
go
exec p3
-- inner procedure was aborted, outer procedure continued
This should work for most cases although some errors will stop and rollback
the whole batch including outer scopes.
I have tested it with inaccessible linked servers and it worked fine for me.
See the following thread for more details:
http://groups-beta.google.com/group...f3390d2b34758e2
HTH
Ami
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Perhaps the object_id(<object> ) function can help. For example, if
object_id('mydb..mytable') will return an object id if the database and
table exists, otherwise it will return NULL.
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> Over night we take a copy of various live SQL databases onto another SQL
> server for reporting purposes.
> I have a stored procedure that compares the latest live data against the 1
> day old copies to ensure that they are up to date.
> I connect to the live databases using linked servers.
> Here's where the problem is - when one of the external links is down or
one
> of the live databases is offline the stored procedure has an error and
stops.
> How can I test within the stored procedure that the database on the linked
> server is available? Then, based on the result, carry out an action?
> Even a simple select statement against an unavailable database halts the
> whole SP even though I've tried breaking the code down into seperate
> transactions, checking for @.@.ERROR > 0, SET XACT_ABORT OFF, the code still
> fails with "SQL Server does not exist or access denied."
> Any advice greatly appreciated.
>|||Works for tables on the local SQL server, but not on Linked Servers, which i
s
where I'm having the problem.
Thanks for the tip anyway.
Paula
"JohnnyAppleseed" wrote:

> Perhaps the object_id(<object> ) function can help. For example, if
> object_id('mydb..mytable') will return an object id if the database and
> table exists, otherwise it will return NULL.
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:752B8EAA-BC40-4B0D-B413-EFC8F94189A7@.microsoft.com...
> one
> stops.
>
>|||Paula
You can check PING Server to make sure that remote server is UP or DOWN
set nocount on
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '00.00.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
SELECT * FROM #t_ip
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
'Do something'
END
DROP TABLE #t_ip
"PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> Works for tables on the local SQL server, but not on Linked Servers, which
is
> where I'm having the problem.
> Thanks for the tip anyway.
> Paula
> "JohnnyAppleseed" wrote:
>
SQL
the 1
or
linked
the
still|||Abolutely great! I was over complicating things for my self instead of
breaking the problem down. I will now be pinging the server using your
helpful code, then testing for the database using another great persons
suggestions from this wonderful resource!
Thanks again
Paula
"Uri Dimant" wrote:

> Paula
> You can check PING Server to make sure that remote server is UP or DOWN
> set nocount on
> CREATE TABLE #t_ip (ip varchar(255))
> DECLARE @.PingSql varchar(1000)
> SELECT @.PingSql = 'ping ' + '00.00.0.0'
> INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
> SELECT * FROM #t_ip
> IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
> BEGIN
> 'Do something'
> END
> DROP TABLE #t_ip
>
> "PaulaPompey" <PaulaPompey@.discussions.microsoft.com> wrote in message
> news:2D613817-450D-45B4-8EE2-D0B0B849D4E5@.microsoft.com...
> is
> SQL
> the 1
> or
> linked
> the
> still
>
>

Continue package execution on task failure

Hi,

I am developing an SSIS package and need the execution of the package to continue even if one of the tasks within the package fails. I have an OnError event handler for this task which fires when it fails but want the rest of the package to continue.

Any suggestions greatly appreciated.

Thanks

Hi PK2000

Can you tell me on what kind of package it fails on and what the next package is that you want to execute?

Kind Regards,

Joos Nieuwoudt

|||

Hi Joos,

I probably didn't explain the problem clearly......

I have one SSIS package with multiple tasks. I want the the package to continue even if the first task within the package fails.

Thanks

|||Hi

by default the arrows between your tasks on the control flow (precedence constraints) are green. It means that you require the task to be successful in order for the next task to be launched.

You can change this behaviour by double-clicking on the green arrow to change the constraint value from 'Success' (green) to 'Completion' (blue) - the next task will then be executed on both failure or success.

cheers

Thibaut|||

Hi PK2000,

Sorry, I understood your question wrong but Thibaut's answer is correct. Try it.

Kind Regards,

Joos Nieuwoudt.

continue option in for loop

I have a for loop I have several tasks inside. I would like to "continue" with a new iteration if any of the tasks in the loop error or fail. Is this possible and if so -- how?

How about this:

Odd... the image shows up in the editor, but not in the message itself. Here is the URL: http://manowar.kicks-ass.org/images/matthew/blog/forloop-continue.jpg

This is basically a variation on the "script task as control flow placeholder" theme discussed here: http://bi-polar23.blogspot.com/2007/05/conditional-task-execution.html

Disclaimer: I have not fully tested this, but it should work. If not, it should give you a good starting point.

|||Thank you. I'll give this a try and send you a reply|||In doing testing, it appears that if a task within a for loop task fails, it immediately attempts to jump to the for loop container as its parent thereby acting as a 'continue' behavior. One item to note is that if continued looping is necessary in the event of a task failure, the MaxErrorCount value needs to be set to a value greater than 1. Otherwise the loop will fail altogether on the first child task failure.

Continue on INSERT error.

Hi!

Imagine this SQL statement:

Code Snippet

INSERT INTO B SELECT * FROM A

If one of the insert fails ... don't continue, the statement fail. For example if any field in A violate a constraint in B, the statement fails.

I want that the statement continue if errors occurs, if i lost a number of rows don't matter ... but if i can save or log this row will be great too !!

Is posible? Any way to do it?

Regards.

Make two statements, by adding a WHERE clause, you can verify the CONSTRAINT and add rows ONLY if the CONSTRAINT passes. Then in the second statement, in the WHERE clause, get the rows that do not pass.

FOR illustration:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
Name varchar(20) PRIMARY KEY
)

INSERT INTO @.MyTable VALUES ( 'Bill' )

DECLARE @.MyOtherTable table
( RowID int IDENTITY,
Name varchar(20)
)

DECLARE @.Failures table
( RowID int,
Name varchar(20)
)

INSERT INTO @.MyOtherTable VALUES ( 'Bill' )
INSERT INTO @.MyOtherTable VALUES ( 'Mary' )
INSERT INTO @.MyOtherTable VALUES ( 'Omar' )

-- First, isolate the CONSTRAINT Failures
INSERT INTO @.Failures
SELECT
t.RowID,
t.Name
FROM @.MyOtherTable t
JOIN @.MyTable m
ON m.Name = t.Name

-- Insert the rows that pass the CONSTRAINT test
INSERT INTO @.MyTable ( Name )
SELECT t.Name
FROM @.MyOtherTable t
JOIN @.MyTable m
ON m.Name <> t.Name

SELECT *
FROM @.MyTable

RowID Name
-- --
1 Bill
2 Mary
3 Omar

SELECT *
FROM @.Failures

RowID Name
-- --
1 Bill

|||

Thanks for your reply.

I will write my question in another way. What I want is if I can change the SQL/Server constraint behaviour when a error is thrown. I know that I can do the insert with a "WHERE" clause. But it some cases is useful to perform your own behaviour when the table has a lot of fields and a lot of rows and you are using a INSERT ... SELECT ... clause. There is some utility (NOTIFICATION, TRIGGERS) that help to do this in a speedy way?

Regards.

|||

A CONSTRAINT failure occurs BEFORE the data is inserted into the table -so a AFTER INSERT TRIGGER would not work.

You could create a BEFORE INSERT TRIGGER, but then you would STILL have to use the two step process I demonstrated in my earlier post. And there may be increased locking and blocking behavior as a result of using a TRIGGER.

Bottom line is that the CONSTRAINT prevents the data from getting into the table. Without the data getting to the table, there is little to offer in the form of Notifications, etc., and you are also, pardon the ironic pun, constrained in the ability to use a TRIGGER.

|||

OK! Thanks.

Regards.

Continue INSERT after key violation?

Hi there.
I have a simple table with a UNIQUE constraint on one field. I wish to
populate this table using a stored procedure that returns the equivalent
results set like so:
INSERT INTO MyTable EXECUTE p_GetMyResultsSet
My stored proc returns unique, distinct results each time it's called
(unique within each call!). The problem I'm having though is that, if the
stored proc returns any record that already exists in the table, the whole
statement quits with a 'constraint violation'.
I would like the statement to continue inserting the results - only leaving
out records which fail the criteria - is this possible?
e.g: 1. p_GetMyResultsSet returns one row with a key value of '1' - record
is inserted into the table OK.
2. p_GetMyResultsSet returns 4 rows with key values of 1, 2, 3, 4.
What happens now: none of the records are inserted because '1' already exist
s.
What I'd like to hapen: records 2, 3, 4 to be inserted.Can't you change the proc so that it only returns the rows that don't
exist? For example:
SELECT x, ...
FROM foo
WHERE NOT EXISTS
(SELECT *
FROM MyTable
WHERE x = foo.x) ;
If not, you could insert the results of the proc to a temp table and
then to MyTable using the same WHERE NOT EXISTS logic.
David Portas
SQL Server MVP
--|||Yes - I think I'll have to use the temp table approach. I'm restricted from
using the 'not exists' because the stored proc needs to be available for use
by other apps and processes- the "MyTable" population is just one process o
f
many using the proc to generate data...
Thanks for the help!
"David Portas" wrote:

> Can't you change the proc so that it only returns the rows that don't
> exist? For example:
> SELECT x, ...
> FROM foo
> WHERE NOT EXISTS
> (SELECT *
> FROM MyTable
> WHERE x = foo.x) ;
> If not, you could insert the results of the proc to a temp table and
> then to MyTable using the same WHERE NOT EXISTS logic.
> --
> David Portas
> SQL Server MVP
> --
>

Continue after error

I have an SSIS package that uses a for each loop to send an order confirmation e-mail. If it does not find an email I need the package to continue after the failure path. The package stops after the failure, but I need it to continue with the next iteration of the for each loop. How can I do this?

Suppress execution error propagation up the container hierarchy, so that the foreach loop container is unaware of errors in its own executables.

Specifically, set the Propagate system variable to false on an event handler scoped below the foreach loop container. Propagate is a system variable which controls upward execution error propagation.

See the following post for more details on how to set the value of this system variable.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1561849&SiteID=1

Now, for validation errors, which will always bubble up at least one level up the container hierarchy, you'll have to set do something like setting the MaximumErrorCount to a large number on the foreach loop container.

|||

That works for me. Thanks.