Error in Merging partitions in AS 2005 from DSO

I have the following code to incrementally process a cube (this is not the full code. I am omiting error handling and other less relevant parts). Basically, I clone the existing partition, process the clone, and then merge it back into the original partition:

Set dsoCube = m_dsoDatabase.MDStores.Item(cubeName)
For Each wrkDimension In dsoCube.Dimensions
m_dsoDatabase.Dimensions(wrkDimension.Name).process processDefault
Next
Set dsoPartition = dsoCube.MDStores.Item(cubeName)
Set dsoClonePartition = dsoCube.MDStores.AddNew(tmpPartition)
dsoPartition.Clone dsoClonePartition, cloneMinorChildren
dsoClonePartition.SourceTableFilter = "Tranno > GetMaxTranNo(cubeName)" ' simplification
dsoClonePartition.process processDefault
dsoPartition.Merge tmpPartition ' Error occurs in SA 2005
dsoPartition.SourceTableFilter = dsoClonePartition.SourceTableFilter
dsoPartition.Update

This works fine in AS 2000. In AS 2005, an error at the step of merging the two partitions is:
Partitions cannot be merged because the source and target partitions have a different number of aggregations.

Manually merging the temporary partition back into the main one from Management studio works without errors.

Any idea on what this might be?

Thanks,
Boris Zakharin, MCAD

Looks like a bug to me.
Please go ahead and file it using http://connect.microsoft.com/sql

I would also like to seize the opportunity and make a little plea for anyone trying to develop DSO applications against Analysis Services 2005:
Please , take a good look at your requirements and at your reasoning to why you are trying to invest in new application code that uses outdated object model. DSO running against AS2005 is there for supporting legacy applications mostly. I would highly discourage anyone from heavily investing in new development using DSO.
Take a look at .NET development using AMO. It is greatly superior to DSO.

HTH.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Done. I was just wondering, would there be a hotfix for this in the near future. If not, is there a workaround? We need to tell our customers why parts of our software are incompatible with SQL Server 2005.

As far as AMO goes, we must still support customers with SQL 2000 with the same version of our code, so AMO would seem to not be an option. Also, We have multiple utilities written in VB6 that deal with OLAP (In addition to our main ASP.NET web application), so porting to AMO would require rewriting large chunks of code in .NET including code that is unrelated to AS at all.

Thanks for your time,
Boris Zakharin, MCAD
P/A, Metavante Risk and Compliance|||

I am afraid I cannot speak of any dates or avaliablity of any particular fix.
If you have immideate need it is definitely better contact product support directly.

I completely understand the reasoning for this current DSO usage. Only... there should come a moment and you should be switching to AMO. In my humble opinion you should make all the effort and switch as soon as you can. Any application you developing applications using DSO is the the application you'll have to rebuild using AMO.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Error handling?!

Hi
I have the following problem.
When I execute a statement i get an error and my statement stops running
but there is errorhandling.
Does anyone knows why i don't get the print statement?
15 is a wrong number to generate the error.
Thanks, John
DDL:
DECLARE @.intError INTEGER
SELECT CONVERT(VARCHAR, GETDATE(), 15)
SELECT @.intError = @.@.ERROR
IF @.intError != 0
PRINT 'Error occured!'
ELSE
PRINT 'No Error...'
Result:
Server: Msg 281, Level 16, State 1, Line 3
15 is not a valid style number when converting from datetime to a
character string.
Server: Msg 8114, Level 16, State 1, Line 3
Error converting data type datetime to varchar.
========================================
======================
Johnny van Cadsand news ( ) cadsand ! demon ! nl
A computer beat me at chess once.
But, it was no match for me at kick boxing.On Sun, 29 May 2005 11:25:14 -0000, Johnny van Cadsand wrote:

>Hi
>I have the following problem.
>When I execute a statement i get an error and my statement stops running
>but there is errorhandling.
>Does anyone knows why i don't get the print statement?
(snip)
Hi Johnny,
For some errors, SQL Server will immediately terminate the batch. These
will never be catched by your error handling routine.
More information:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote :

> On Sun, 29 May 2005 11:25:14 -0000, Johnny van Cadsand wrote:
>
> (snip)
> Hi Johnny,
> For some errors, SQL Server will immediately terminate the batch.
> These will never be catched by your error handling routine.
> More information:
> http://www.sommarskog.se/error-handling-I.html
> http://www.sommarskog.se/error-handling-II.html
> Best, Hugo
Thanks...
Johnny van Cadsand news ( ) cadsand ! demon ! nl

Error handling/Error description (SQL2005)

Is there a better/simpler way than mine to get the error description in the
default_language of the current_user for a given message_id?
DECLARE @.ErrNo INT
SET @.ErrNo=21
SELECT [error description]=sys.messages.[text]
FROM sys.messages inner join sys.syslanguages ON language_id=msglangid,
sys.server_principals
WHERE sys.messages.message_id=@.ErrNo AND
sys.syslanguages.[name]=sys.server_principals.[default_language_name] AND
sys.server_principals.[name]=SYSTEM_USER
Thanks in advance.This is slightly shorter:
DECLARE @.ErrNo INT
SET @.ErrNo=21
SELECT m."text" AS "error description", *
FROM sys.messages AS m
INNER JOIN syslanguages AS l ON l.msglangid = m.language_id
WHERE m.message_id=@.ErrNo
AND l.langid = @.@.LANGID
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"LX" <tsld99@.gmail.com> wrote in message news:%23tM15l5cGHA.3900@.TK2MSFTNGP05.phx.gbl...[co
lor=darkred]
> Is there a better/simpler way than mine to get the error description in th
e
> default_language of the current_user for a given message_id?
> DECLARE @.ErrNo INT
> SET @.ErrNo=21
> SELECT [error description]=sys.messages.[text]
> FROM sys.messages inner join sys.syslanguages ON language_id=msglangid,
> sys.server_principals
> WHERE sys.messages.message_id=@.ErrNo AND
> sys.syslanguages.[name]=sys.server_principals.[default_language_name] AND
> sys.server_principals.[name]=SYSTEM_USER
> Thanks in advance.
>
>[/color]

Error Handling/ Stored Procs

Hi,
I'm doing some fairly basic updates with stored procedures. 99% of them affect one row. I've jsut discovered that I can't get the value of @.@.rowcount and @.@.error to return as output parameters (if I check one, the other one gets reset!). My theory is then to return the rowcount and if it's not = 1, then I know I've had a problem. If I begin a transaction in vb.net and call each proc in the required order and check each step that rowcount = 1, is this a reliable method of ensuring no errors have occurred?
Thanks.As far as dealing with both @.@.ROWCOUNT and @.@.ERROR is concerned, this is what I do:

SELECT @.lError = @.@.ERROR, @.lRowCount = @.@.ROWCOUNT

I select the values into local variables in the stored procedure andthen do whatever is needed based on those values. Note that youmust SELECT both values in the same statement, and I *believe* @.@.ERRORmust appear first because it will be reset when @.@.ROWCOUNT is accessed.
In your case, @.lError should be 0 and @.lRowCount should be 1 when a record is inserted correctly.


|||Thanks, I can now get my two values back!

Error handling...

I am needing to check for an error code after a SQL statement has run, but
the error is being trapped internal to the Select statement. How do I make
this work?
I am trying to set up some code that will try the SQL statement and if it
gets an error, it will wait 30 seconds and then try again. And if it trys 3
times (or so) with out success THEN return the error message (in the error
dialog box that halts everything till you click "OK") or better yet, just
log that the error occured and allow the process to continue and take
appropriate action based on the error number.
The file pointed to by the OPENROWSET, was just copied from a local PC to
the server. The problem is that the file still has write processes occuring
in the background after the scripted copy thinks it is finished and passes
control to the next line.
I am trying to make this work with out resorting to DTS or SSIS. The
product I am using has it's own proprietary scripting set up that can call i
n
and run Transact-SQL statements.
*** SQL Code ***
Declare @.trys int
Set @.trys = 1
PRINT 'At top'
While @.trys < 3
BEGIN
Print 'Attempting Try # ' + Cast(@.Trys as char(4))
SELECT SN.CustomerID, C.CustomerID, C.FirstName, C.LastName, C.Status
FROM Customers AS C
LEFT OUTER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\StudentList.mdb';'admin';'', StudentNumbers) AS SN
ON (C.CustomerID = SN.CustomerID)
WHERE SN.CustomerID Is not null
If @.@.ERROR = 0
BREAK
Else
BEGIN
WAITFOR DELAY '000:00:30'
Set @.Trys = @.Trys + 1
CONTINUE
END
END
Print 'At bottom'
Regards,
JohnJohn Keith (JohnKeith@.discussions.microsoft.com) writes:
> I am needing to check for an error code after a SQL statement has run,
> but the error is being trapped internal to the Select statement. How do
> I make this work?
It may prove difficult if you are on SQL 2000. On SQL 2000 many errors
are not trappable in SQL, because they abort the batch. There is a trick
with setting up a linked server, but it's fairly kludgy.
On SQL 2005 you can use TRY CATCH.
For lots of details in error handlin in SQL Server, see this
article: http://www.sommarskog.se/error-handling-I.html. The linked-
server trick is described somewhere in the middle.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for that link.
Your page was very informative on the hows-and-whys. I can tell from your
sarcasm on the page that we are in the same boat on this. The remark about
"Now, taste that concept: an informational error." made me laugh.
I'm checking into SQL Server 2005 to see if my package-application (not DTS,
just an externaly written bought app) will support the newer SQL Server 2005
.
Regards,
John
"Erland Sommarskog" wrote:

> John Keith (JohnKeith@.discussions.microsoft.com) writes:
> It may prove difficult if you are on SQL 2000. On SQL 2000 many errors
> are not trappable in SQL, because they abort the batch. There is a trick
> with setting up a linked server, but it's fairly kludgy.
> On SQL 2005 you can use TRY CATCH.
> For lots of details in error handlin in SQL Server, see this
> article: http://www.sommarskog.se/error-handling-I.html. The linked-
> server trick is described somewhere in the middle.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Error Handling with SQLDataSource

Hello to all

I am having a SQLDataSource on my web page to connect with database. Now If it generates any error then where to capture that errors ?? Means at which place(which event or any other place) should i write code for handeling errors ?

Which programming language (e.g. C#) do you use for your web page? In C#, you can use a 'try...catch' block to catch the exception when connect to SQLDataSource; you can perform the connection action in a 'button click' event or in page load event as you like.|||

Yes I know in C# I can use Try--catch. But on which event Do I use Try and Catct ?

If I use this on "selecting " it does not have any effect...

|||Yeah im also wondering how to do this.

The sql data source is loaded when the page loads and runs the select command, so it is not run when a button is pressed.|||You can handle the error on the Inserted, Deleted and Updated events. (They all have Exception properties, and if you handle it, you can set ExceptionHandled to true.)|||Hi,

I have a similar question : How do I handleconnection exceptions ?

mpswaim:

You can handle the error on the Inserted,Deleted and Updated events. (They all have Exception properties, and ifyou handle it, you can set ExceptionHandled to true.)

When the server is unavailable, the exception property of the selected event is null.

Thanks,

Loïc

Error handling with regards to a caculation

I have a report that calculates a students GPA based on two fields in a dataset:

TrmGpaPts/CreditAwd = GPA SQL below

My issue is that the students with 0 (zero) or NULL credits awarded results in a "divide by zero error". How can I place a 0 (zero) in the GPA column when the student has 0 or NULL credits?

Thanks

select
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as Ident,
stugrp_active.graden as Grade,
trnscrpt.graden as Grd_Lvl,
sum(trnscrpt.gradcrawd) as CreditAwd,
round(sum(case when Trnscrpt.GpaCrAtt is null then 0 else Trnscrpt.GpaCrAtt end * gpamarks.gpavallvl0),3) AS TrmGpaPts

from
dbo.trnscrpt
inner join dbo.stugrp_active on (trnscrpt.suniq = stugrp_active.suniq) INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark

where
school.schname = @.school and
stugrp_active.graden =@.graden and
trnscrpt.graden = @.trnscrptgraden and
trnscrpt.termc = @.termc

group by
school.schname,
trnscrpt.termc,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname),
trnscrpt.suniq,
stugrp_active.graden,
trnscrpt.graden

order by
School,
Student

When CreditAwd is zero then you don't want to do the division (TrmGpaPts/CreditAward)

You should put something like this in the expression for your report to get the GPA.

=IIf(Fields!CreditAward.Value = 0, "N/A", (Fields!TrmGpaPts.Value) / IIf(Fields!CreditAward.Value = 0, 1, Fields!CreditAward.Value))

|||

Thanks! I see the general direction I need to take.

|||Glad I could help.

|||

Using GregSQL's expression from above, I'm getting the error below.

What does this mean to specify the data set scope and where would I do that?

The Value expression for the textbox ‘GPA’ refers to the field ‘CreditAward’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Build complete

|||It's saying that field is not in your dataset. Open your dataset in business intelligence studio (View -> datasets). Right click Report Datasets and click refresh. See if CreditAward is then added to your dataset in that window. If not, then you are not selecting it properly in your SQL query.

|||

Thanks. Makes sense once I spotted the typing error.

Error handling with regards to a caculation

I have a report that calculates a students GPA based on two fields in a dataset:

TrmGpaPts/CreditAwd = GPA SQL below

My issue is that the students with 0 (zero) or NULL credits awarded results in a "divide by zero error". How can I place a 0 (zero) in the GPA column when the student has 0 or NULL credits?

Thanks

select
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as Ident,
stugrp_active.graden as Grade,
trnscrpt.graden as Grd_Lvl,
sum(trnscrpt.gradcrawd) as CreditAwd,
round(sum(case when Trnscrpt.GpaCrAtt is null then 0 else Trnscrpt.GpaCrAtt end * gpamarks.gpavallvl0),3) AS TrmGpaPts

from
dbo.trnscrpt
inner join dbo.stugrp_active on (trnscrpt.suniq = stugrp_active.suniq) INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark

where
school.schname = @.school and
stugrp_active.graden =@.graden and
trnscrpt.graden = @.trnscrptgraden and
trnscrpt.termc = @.termc

group by
school.schname,
trnscrpt.termc,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname),
trnscrpt.suniq,
stugrp_active.graden,
trnscrpt.graden

order by
School,
Student

When CreditAwd is zero then you don't want to do the division (TrmGpaPts/CreditAward)

You should put something like this in the expression for your report to get the GPA.

=IIf(Fields!CreditAward.Value = 0, "N/A", (Fields!TrmGpaPts.Value) / IIf(Fields!CreditAward.Value = 0, 1, Fields!CreditAward.Value))

|||

Thanks! I see the general direction I need to take.

|||Glad I could help.

|||

Using GregSQL's expression from above, I'm getting the error below.

What does this mean to specify the data set scope and where would I do that?

The Value expression for the textbox ‘GPA’ refers to the field ‘CreditAward’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Build complete

|||It's saying that field is not in your dataset. Open your dataset in business intelligence studio (View -> datasets). Right click Report Datasets and click refresh. See if CreditAward is then added to your dataset in that window. If not, then you are not selecting it properly in your SQL query.

|||

Thanks. Makes sense once I spotted the typing error.

Error Handling with openrowset

I am using openrowset to interact with some fox pro tables.
I want to be able do error handling if the openrowset quiery fails.
When the quiery gets and error it terminates and does not seem to return and
error status.
Here is a code sample.
Update openrowset('MSDASQL',
'Driver={Microsoft Visual FoxPro
Driver};UID=;PWD=;SourceDB=\\dataserver\prod\apps\ tele\;SourceType=DBF;Exclusive=No;Background
Fetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes',
'select PROMO_CODE from prospect_conf
where area_code+home_phone in (select parea_code+phome_phon from
prospext_conf
where ctod(misc5) = date())')
set promo_code = LEft(promo_code,3)+'0'
Thanks in advance
Regarding Trapping the error from an OpenRowset failure.
I have had similar problems using OpenRowset with FoxPro tables, and found the workaround to be as follows.
1. Build the Openrowset query inside a stored procedure.
2. Call that procedure from inside a parameterised DTS package (Exec usp_Procname ?,?,?)
3. Call the DTS package from another stored procedure.
The DTS package will return a trapable error, so if you are trying to do some type of batch processing your process can continue on.
Admittedly it's a bit convoluted, but it works.

Error handling with MDX and Reporting Services

I have having trouble with an MDX Statement. There are some instances where the MDX statement I am passing in isn't correct or invalid. The report takes in serveral default parameters and creates an MDX statement from it. When this error happens I get an error similiar to

Formula error - cannot find dimension member ("[Time].[2005]") - in a name-binding function

on the report page. I would like to be able to handle this error in reporting services so how. Essientially, I would want to be able to just say if this error happens just take me the plain page and do not load up any defaults.You might consider this a hack, but this can be solved from outside Reporting Services with the new missing member functionality in 2005 (ie invalid member lookups wont generate errors),|||I am using RS 2005 and AS 2005 RTM.

I am having the report to accept a parameter UserUserID that accepts values such as [User].[User ID].&[1].

This is working fine when the front-end calls it with a correct/valid UserID.

When it is the calling the report with a valid UserID, two things that I observe happening:
a) when the Parameter is set to Hidden (on the Report Parameters properties), this error will be displayed "The 'UserUserID' parameter is missing a value"
b) when the Parameter is not set to Hidden, a blank page is shown with the parameter prompt <Select a Value>.

Is there any way to better handle situation a)? Since I need to hide the parameters from the end-users....

Thanks.

Error handling with MDX and Reporting Services

I have having trouble with an MDX Statement. There are some instances where the MDX statement I am passing in isn't correct or invalid. The report takes in serveral default parameters and creates an MDX statement from it. When this error happens I get an error similiar to

Formula error - cannot find dimension member ("[Time].[2005]") - in a name-binding function

on the report page. I would like to be able to handle this error in reporting services so how. Essientially, I would want to be able to just say if this error happens just take me the plain page and do not load up any defaults.

You might consider this a hack, but this can be solved from outside Reporting Services with the new missing member functionality in 2005 (ie invalid member lookups wont generate errors),|||I am using RS 2005 and AS 2005 RTM.

I am having the report to accept a parameter UserUserID that accepts values such as [User].[User ID].&[1].

This is working fine when the front-end calls it with a correct/valid UserID.

When it is the calling the report with a valid UserID, two things that I observe happening:
a) when the Parameter is set to Hidden (on the Report Parameters properties), this error will be displayed "The 'UserUserID' parameter is missing a value"
b) when the Parameter is not set to Hidden, a blank page is shown with the parameter prompt <Select a Value>.

Is there any way to better handle situation a)? Since I need to hide the parameters from the end-users....

Thanks.

error handling with linked servers

Hi,
Could someone please tell me how to correctly manage errors with
linked servers?
I make an insert in a linked server. When a foreign key is violated
the whole batch is
aborted so I could not manage the error as I do on a standard server.
I mean:
locally this works fine and I always see the print:
insert into MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
The statement has been terminated.
an error happened!!!
on the contrary with a linked server the following code doesn't show
any print:
insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
The statement has been terminated.
Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
Thanks in advance for any kind suggestion,
Stefano
The batch is aborted with the linked server error due to the SET XACT_ABORT
ON setting, which is required here. If you are using SQL 2005, you can use
a TRY...CATCH block to trap the error in T-SQL. I believe the error must be
handled on the client side in SQL 2000.
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:36b3d389-0d49-428a-91fb-16814832b35d@.q39g2000hsf.googlegroups.com...
> Hi,
> Could someone please tell me how to correctly manage errors with
> linked servers?
> I make an insert in a linked server. When a foreign key is violated
> the whole batch is
> aborted so I could not manage the error as I do on a standard server.
> I mean:
>
> locally this works fine and I always see the print:
> insert into MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
> Msg 547, Level 16, State 0, Line 2
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
> The statement has been terminated.
> an error happened!!!
>
> on the contrary with a linked server the following code doesn't show
> any print:
> insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
>
> The statement has been terminated.
> Msg 547, Level 16, State 1, Line 1
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
>
> Thanks in advance for any kind suggestion,
> Stefano
|||Hi Dan,
thank you for your replay.
Now I understand: this is the correct behaviour with SET XACT_ABORT ON
which is implicitly
used by most OLD DB Providers.
Locally it works because SET XACT_ABORT is OFF by default.
Unfortunately I have to deal with SQL 2000 not 2005.
I could not (I think) handle this easly on the application because
this happens inside stored
procedures and I do not want to change a lot of code.
Since I have some possibility on the linked side I will create some
procedures on the target
database and call the remotely. These procedure on the remote side
will be able to handle
the violation.
I this this could be ok... do you agre?
Thanks again,
Stefano
|||> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
I have not done this myself but I think this approach might work. You'll
need to enable linked server rpc calls.
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:dfedf9ed-3d7d-41c4-a2b5-334cbb52cb47@.f47g2000hsd.googlegroups.com...
> Hi Dan,
> thank you for your replay.
> Now I understand: this is the correct behaviour with SET XACT_ABORT ON
> which is implicitly
> used by most OLD DB Providers.
> Locally it works because SET XACT_ABORT is OFF by default.
> Unfortunately I have to deal with SQL 2000 not 2005.
> I could not (I think) handle this easly on the application because
> this happens inside stored
> procedures and I do not want to change a lot of code.
> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
> Thanks again,
> Stefano
|||ok, thanks!

error handling with linked servers

Hi,
Could someone please tell me how to correctly manage errors with
linked servers?
I make an insert in a linked server. When a foreign key is violated
the whole batch is
aborted so I could not manage the error as I do on a standard server.
I mean:
locally this works fine and I always see the print:
insert into MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
The statement has been terminated.
an error happened!!!
on the contrary with a linked server the following code doesn't show
any print:
insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
if @.@.error <> 0
print 'an error happened!!!'
The statement has been terminated.
Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_XXX". The conflict occurred in database "MYDB", table
"dbo.MYTABLE", column '_Id'.
Thanks in advance for any kind suggestion,
StefanoThe batch is aborted with the linked server error due to the SET XACT_ABORT
ON setting, which is required here. If you are using SQL 2005, you can use
a TRY...CATCH block to trap the error in T-SQL. I believe the error must be
handled on the client side in SQL 2000.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:36b3d389-0d49-428a-91fb-16814832b35d@.q39g2000hsf.googlegroups.com...
> Hi,
> Could someone please tell me how to correctly manage errors with
> linked servers?
> I make an insert in a linked server. When a foreign key is violated
> the whole batch is
> aborted so I could not manage the error as I do on a standard server.
> I mean:
>
> locally this works fine and I always see the print:
> insert into MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
> Msg 547, Level 16, State 0, Line 2
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
> The statement has been terminated.
> an error happened!!!
>
> on the contrary with a linked server the following code doesn't show
> any print:
> insert into LINKEDSRV.MYDB.dbo.MYTABLE values (100,99)
> if @.@.error <> 0
> print 'an error happened!!!'
>
> The statement has been terminated.
> Msg 547, Level 16, State 1, Line 1
> The INSERT statement conflicted with the FOREIGN KEY constraint
> "FK_XXX". The conflict occurred in database "MYDB", table
> "dbo.MYTABLE", column '_Id'.
>
> Thanks in advance for any kind suggestion,
> Stefano|||Hi Dan,
thank you for your replay.
Now I understand: this is the correct behaviour with SET XACT_ABORT ON
which is implicitly
used by most OLD DB Providers.
Locally it works because SET XACT_ABORT is OFF by default.
Unfortunately I have to deal with SQL 2000 not 2005.
I could not (I think) handle this easly on the application because
this happens inside stored
procedures and I do not want to change a lot of code.
Since I have some possibility on the linked side I will create some
procedures on the target
database and call the remotely. These procedure on the remote side
will be able to handle
the violation.
I this this could be ok... do you agre?
Thanks again,
Stefano|||> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
I have not done this myself but I think this approach might work. You'll
need to enable linked server rpc calls.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<stefano.figurelli@.gmail.com> wrote in message
news:dfedf9ed-3d7d-41c4-a2b5-334cbb52cb47@.f47g2000hsd.googlegroups.com...
> Hi Dan,
> thank you for your replay.
> Now I understand: this is the correct behaviour with SET XACT_ABORT ON
> which is implicitly
> used by most OLD DB Providers.
> Locally it works because SET XACT_ABORT is OFF by default.
> Unfortunately I have to deal with SQL 2000 not 2005.
> I could not (I think) handle this easly on the application because
> this happens inside stored
> procedures and I do not want to change a lot of code.
> Since I have some possibility on the linked side I will create some
> procedures on the target
> database and call the remotely. These procedure on the remote side
> will be able to handle
> the violation.
> I this this could be ok... do you agre?
> Thanks again,
> Stefano|||ok, thanks!

Error Handling with Execute Package Task

I have a main package that calls several other packages using Execute Package Tasks. I also have OnPreExecute, OnPostExecute, and OnError event handlers at the package level to audit the beginning and completion of each package. I want to prevent each task from bubbling up to the package event handlers as well as prevent each task from bubbling up to the main package event handlers. I've tried setting the Propagate variable for each of the event handlers to False and setting the DisableEventHandlers property of each task to True but neither solution seems to work. Is there a way to do this that I'm missing?

You have to set [System::Propogate]=false in the eenthandler of the task for which you don't want events to propogate.

Your post doesn't specify on which eventhandlers you are setting this.

-Jamie

|||

I found another thread that describes the problem I'm seeing. Here is the previous thread and, in following the thread, I'm not sure if the conclusion was that this behavior was as designed or that this is really a bug. Here is a link to the previous discussion:

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

The problem in a nutshell is the following: There are two packages, a parent package which calls a child package using the Execute Package Task. Both packages have OnPostExecute event handlers defined at the package level to log progress with different ExecutionInstanceGUID. Even when the System::Propagate property is set to False for the child package OnPropagate event handler, the parent OnPostExecute event handler is still raised.

I guess the work around is to create an empty event handler for the Execute Package Task in the parent package and set the System::Propagate property for that empty event handler to False to prevent the main package OnPostExecute event from being raised. Since I have several Execute Package Tasks in the real solution, a better approach would be to wrap all the Execute Package Tasks in a Sequence container and create the do nothing event handler with the Propagate property set to false at the Sequence container rather than at each of the Execute Package tasks event handlers.

Does this make sense? Or is there possibly a better solution that I'm missing? Have to confess, I'm getting a bit overwhelmed by the number of possible appoaches one can use to log and audit package execution.

|||

Martin,

I cannot replicate your problem. If I set System::Propogate=FALSE on an eventhandler scoped to the child package then any events raised by the package container of the child package do not propogate to the parent package.

Note that this does NOT stop events raised by containers in the child package from propogating to the parent package. If you want to stop this from happening then set System::Propogate=FALSE on all of those containers as well.

I've also re-read your first post where you say "I want to prevent each task from bubbling up to the package event handlers ". If you only want to capture events fired by the package then you can compare System::PackageID to System::SourceID. If they are different then the event has not been raised by the package.

-Jamie

|||

Jamie,

I guess I was looking at the architecture wrong. I thought the child package was the container for all other containers in the child package and therefore, by setting Propagate to false for all child package event handlers, no events would be raised by any containers in the child package to the parent package. That doesn't seem to be the case. By creating empty event handlers for the Execute Package Task in the parent package and setting Propagate to false for the empty event handlers, events raised by child package containers are not propagated to the parent package event handlers.

We are logging to two tables. Package events in one table and tasks in another table and have created a package template to handle the plumbing. This is great stuff.

|||

Martin Mason wrote:

Jamie,

I guess I was looking at the architecture wrong. I thought the child package was the container for all other containers in the child package and therefore, by setting Propagate to false for all child package event handlers, no events would be raised by any containers in the child package to the parent package. That doesn't seem to be the case.

Yeah, System::Propogate can often be misunderstood - mainly due to the complexity of the container hierarchy. System::Propogate only stops events fired by the container that it is scoped to from propogating. It does not stop events fired by descendant containers from propogating.

Martin Mason wrote:

By creating empty event handlers for the Execute Package Task in the parent package and setting Propagate to false for the empty event handlers, events raised by child package containers are not propagated to the parent package event handlers.

I'm surprised that that is the case though. The package container and the TaskHost container on the Execute Package Task are no different from any other container and should behave the same way.

Martin Mason wrote:

We are logging to two tables. Package events in one table and tasks in another table and have created a package template to handle the plumbing. This is great stuff.

I agree. I'm a big fan of using eventhandlers for custom logging and have advocated it strongly here: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

-Jamie

|||

Is there an easy way to identify which "System::Propogate" variable goes with what container object? The variable names and scope are all the same? How can you tell which variable goes with which container event. When I define an OnPreExecute event for multiple containers/tasks I can't tell which "System::Propogate" variable goes with what object?

Maybe I am missing something obvious but I do not see the connection?

Thanks!

|||

Each eventhandler has a [System::Propogate] variable scoped to it. Variables called [System::Propogate] are ONLY scoped to eventhandlers, nothing else. Therefore, when you use one there can be no ambiguity about which one you are using, it is the one scoped to the eventhandler you are currently editing.

And remember that an eventhandler is, in turn, scoped to a container in the control-flow.

Does that answer the question?

-Jamie

|||

Um Kind of.

I guess I should have noted that I was referring to the variables list, located under SSIS on the toolbar? Not just when I am editing the event. I understand that when I am editing the event ssis will only show me variables that are in scope to the event/container/task but I was referring to the "ALL" Variable list, where you can view all System and user variables and set their values etc. or add new variables or to view there properties in the properties pane?

Do I make sense?

Thanks!

Error Handling with Execute Package Task

I have a main package that calls several other packages using Execute Package Tasks. I also have OnPreExecute, OnPostExecute, and OnError event handlers at the package level to audit the beginning and completion of each package. I want to prevent each task from bubbling up to the package event handlers as well as prevent each task from bubbling up to the main package event handlers. I've tried setting the Propagate variable for each of the event handlers to False and setting the DisableEventHandlers property of each task to True but neither solution seems to work. Is there a way to do this that I'm missing?

You have to set [System::Propogate]=false in the eenthandler of the task for which you don't want events to propogate.

Your post doesn't specify on which eventhandlers you are setting this.

-Jamie

|||

I found another thread that describes the problem I'm seeing. Here is the previous thread and, in following the thread, I'm not sure if the conclusion was that this behavior was as designed or that this is really a bug. Here is a link to the previous discussion:

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

The problem in a nutshell is the following: There are two packages, a parent package which calls a child package using the Execute Package Task. Both packages have OnPostExecute event handlers defined at the package level to log progress with different ExecutionInstanceGUID. Even when the System::Propagate property is set to False for the child package OnPropagate event handler, the parent OnPostExecute event handler is still raised.

I guess the work around is to create an empty event handler for the Execute Package Task in the parent package and set the System::Propagate property for that empty event handler to False to prevent the main package OnPostExecute event from being raised. Since I have several Execute Package Tasks in the real solution, a better approach would be to wrap all the Execute Package Tasks in a Sequence container and create the do nothing event handler with the Propagate property set to false at the Sequence container rather than at each of the Execute Package tasks event handlers.

Does this make sense? Or is there possibly a better solution that I'm missing? Have to confess, I'm getting a bit overwhelmed by the number of possible appoaches one can use to log and audit package execution.

|||

Martin,

I cannot replicate your problem. If I set System::Propogate=FALSE on an eventhandler scoped to the child package then any events raised by the package container of the child package do not propogate to the parent package.

Note that this does NOT stop events raised by containers in the child package from propogating to the parent package. If you want to stop this from happening then set System::Propogate=FALSE on all of those containers as well.

I've also re-read your first post where you say "I want to prevent each task from bubbling up to the package event handlers ". If you only want to capture events fired by the package then you can compare System::PackageID to System::SourceID. If they are different then the event has not been raised by the package.

-Jamie

|||

Jamie,

I guess I was looking at the architecture wrong. I thought the child package was the container for all other containers in the child package and therefore, by setting Propagate to false for all child package event handlers, no events would be raised by any containers in the child package to the parent package. That doesn't seem to be the case. By creating empty event handlers for the Execute Package Task in the parent package and setting Propagate to false for the empty event handlers, events raised by child package containers are not propagated to the parent package event handlers.

We are logging to two tables. Package events in one table and tasks in another table and have created a package template to handle the plumbing. This is great stuff.

|||

Martin Mason wrote:

Jamie,

I guess I was looking at the architecture wrong. I thought the child package was the container for all other containers in the child package and therefore, by setting Propagate to false for all child package event handlers, no events would be raised by any containers in the child package to the parent package. That doesn't seem to be the case.

Yeah, System::Propogate can often be misunderstood - mainly due to the complexity of the container hierarchy. System::Propogate only stops events fired by the container that it is scoped to from propogating. It does not stop events fired by descendant containers from propogating.

Martin Mason wrote:

By creating empty event handlers for the Execute Package Task in the parent package and setting Propagate to false for the empty event handlers, events raised by child package containers are not propagated to the parent package event handlers.

I'm surprised that that is the case though. The package container and the TaskHost container on the Execute Package Task are no different from any other container and should behave the same way.

Martin Mason wrote:

We are logging to two tables. Package events in one table and tasks in another table and have created a package template to handle the plumbing. This is great stuff.

I agree. I'm a big fan of using eventhandlers for custom logging and have advocated it strongly here: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

-Jamie

|||

Is there an easy way to identify which "System::Propogate" variable goes with what container object? The variable names and scope are all the same? How can you tell which variable goes with which container event. When I define an OnPreExecute event for multiple containers/tasks I can't tell which "System::Propogate" variable goes with what object?

Maybe I am missing something obvious but I do not see the connection?

Thanks!

|||

Each eventhandler has a [System::Propogate] variable scoped to it. Variables called [System::Propogate] are ONLY scoped to eventhandlers, nothing else. Therefore, when you use one there can be no ambiguity about which one you are using, it is the one scoped to the eventhandler you are currently editing.

And remember that an eventhandler is, in turn, scoped to a container in the control-flow.

Does that answer the question?

-Jamie

|||

Um Kind of.

I guess I should have noted that I was referring to the variables list, located under SSIS on the toolbar? Not just when I am editing the event. I understand that when I am editing the event ssis will only show me variables that are in scope to the event/container/task but I was referring to the "ALL" Variable list, where you can view all System and user variables and set their values etc. or add new variables or to view there properties in the properties pane?

Do I make sense?

Thanks!

Error handling with CREATE INDEX

I'm receiving a "severe" error that I can't seem to trap and evaluate in SQL Server 2000 (and no, I can't switch to 2005, this is on our customers' machines). And I've not been able to find info on this specific problem.

This is essentially the statement I'd like to catch and gracefully quit if it occurs:

CREATE UNIQUE NONCLUSTERED INDEX UQ_First_Key_SecondField_ThirdField
ON [dbo].[DetailTable] ( Prime_Key, SecondField, ThirdField ) ON [PRIMARY]
SET @.ErrorNumber = @.@.ERROR

Execution never gets to the "SET @.ErrorNumber = @.@.ERROR" statement so I can't act on it. Instead it bombs right away and gives me this error message:

Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 5. Most significant primary key is '706'.
The statement has been terminated.

When that CREATE statement is executed I'd like to gracefully exit the stored procedure (sproc) and report the error to the operator. It's not that I don't understand the error - I fully expect it with SOME of our customers - the problem is that I want to report the REASON for the error to our customers of various expertise.

I created a series of sprocs to re-create indexes in our customers' databases when we define them. Indexes were not defined before, or were defined with random names, so now I'm trying to manage index names and designs explicitly with a series of sprocs I create in SQL Server 2000 to search for, and/or delete, and/or recreate if necessary after verifying the suitability of the index names and syntax as defined by our developers.

Note that specically I'm using either EXEC( ) or EXEC @.ErrorNumber = sp_executesql @.SQLString and I get the same results as if I just use the CREATE statement described above - it bombs out on me and I can't handle the error gracefully.So should I take the lack of response to mean that there is no way to trap this kind of error?|||You will not be able to trap "fatal errors" in SQL, you should build in error handling at the application level to take care of such situations.|||

Quote:

Originally Posted by Motoma

You will not be able to trap "fatal errors" in SQL, you should build in error handling at the application level to take care of such situations.

Thanks, Motoma, I was afraid that might be the case. I was hoping to avoid having to build a new tool to take care of this "simple" task outside the normal procedures we use to upgrade our customers...

error handling while processing a cube

hi

in analysis services when i process a cube i got an error that
data from the fact table isnt exists in the dimenstion table
for example : fact table sales has column suplier that one of its row
has data(suplier id = 170) that doesnt match with the dimension table ( in the dimension table there isnt such supier id)
how can i handle this error ?
can i insert instead someyhing else so that the proccess can continue and not failed ?

Thanks

Eyal

Ideally, you've addressed this problem in your data warehouse/data mart. I recommend to my clients they never allow NULLs in their foreign keys. This requires you to place a NULL member in each dimension -- we usually assign that member the surrogate key value of -1 -- and eliminates this issue.

It sounds like in your example, you have a valid key in your partition, so I would suggest you make sure you dimension is processed before you process your cube/partitions.

If that is not an option, you use use the UnknownMember and ErrorConfiguration properties in SSAS to handle this issue. In the dimension that is causing problems (the supplier dimension in your example?) set the UnknownMember property to either visible or hidden. It defaults to None which makes the UnknownMember inaccessible.

Then, on the measure group that points to this dimension table, change the error configuration to replace missing key values with unknown (using the KeyErrorAction and KeyNotFound properties). Be certain to also set the KeyErrorLimitAction property to Stop Logging when the max error is hit.

One important gotcha on this. If your dimension member lands in your dimensions, the facts assigned to the unknown member do not revert to your new dimension value. To line your facts up correctly with the values in your dimension, you will need to do a full reprocess of the partition.

B.

Error handling when one field value is bad

Here's an example of what I'm playing with:

update TestTable

set IntValue = Cast(StringValue, as int)

flag =

BEGIN TRY

0

END TRY

BEGIN CATCH

1

END CATCH

I'm trying to take a string value holding an integer and populate an integer field with that value. But, every so often, the string value does not contain an integer (e.g. '13.9" or "CA"). That error would cause the entire column UPDATE to be rolled back. i'm trying to get around it with some kind of "TRY CATCH" construct but I don't believe it can work.

What would you suggest?

Barkingdog

No, you are right this won′t work. For me, the best Exception handling is to prevent exceptions, in your case check the value for being numeric, there is a function ISNUMERIC which can do that for you. But be careful with that function, because it checks for currency numeric only, so the value "$" which is part of a currency expression will also evaluate to true.

See more details on the site: http://www.aspfaq.com/show.asp?id=2390

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Error handling when calling external sql-Files

I am using SQL Server 2000. I have some files with SQL-Statements.

The SQL-Serveragent runs jobs which execute the SQL-Files:
(e.g. osql /E /n /i \\server\d$\lager_pool.sql)

How can I implement an error handling.

If an error occurs, the script stops, and I can't read the variable
@.error

My script - table xy doesnt exist

SELECT * FROM XY
SELECT @.@.error
SELECT 33

The execution stops with an error after the first line

Thanks for your help.
aaapaulHi

Look at using the -b flag when you call osql, there is more information in
Books Online.

John

<lvpaul@.gmx.net> wrote in message
news:1128067412.114048.12840@.g44g2000cwa.googlegro ups.com...
I am using SQL Server 2000. I have some files with SQL-Statements.

The SQL-Serveragent runs jobs which execute the SQL-Files:
(e.g. osql /E /n /i \\server\d$\lager_pool.sql)

How can I implement an error handling.

If an error occurs, the script stops, and I can't read the variable
@.error

My script - table xy doesnt exist

SELECT * FROM XY
SELECT @.@.error
SELECT 33

The execution stops with an error after the first line

Thanks for your help.
aaapaul|||Thanks, but it doesnt work

This is my script - its a file c:\test.sql

declare @.errorcode int
select * from abc
SET @.errorcode = @.@.error
--writing log
insert adaten.dbo.loghist(logdatum,loginfo,logfehler,saet ze)
values (getdate(),'test',@.errorcode,25)

The table "abc" dont exist.

If I call the script

osql /E /n /ic:\test.sql (with or without /b) the insert anweisung
instruction wont be executed.

I want something like ON ERROR RESUME NEXT. Is this possible ?

Thanks
aaapaul|||Hi

You may want to try using EXEC for your select statement

declare @.errorcode int
EXEC ('select * from abc')
SET @.errorcode = @.@.error
--writing log
insert adaten.dbo.loghist(logdatum,loginfo,logfehler,saet ze)
values (getdate(),'test',@.errorcode,25)

John

<lvpaul@.gmx.net> wrote in message
news:1128491173.083475.231000@.o13g2000cwo.googlegr oups.com...
Thanks, but it doesnt work

This is my script - its a file c:\test.sql

declare @.errorcode int
select * from abc
SET @.errorcode = @.@.error
--writing log
insert adaten.dbo.loghist(logdatum,loginfo,logfehler,saet ze)
values (getdate(),'test',@.errorcode,25)

The table "abc" dont exist.

If I call the script

osql /E /n /ic:\test.sql (with or without /b) the insert anweisung
instruction wont be executed.

I want something like ON ERROR RESUME NEXT. Is this possible ?

Thanks
aaapaul

error handling vbscript via sqlserver

Hi,

I wrote a small vbscript that sends information from a local computer to SQL server.
The method I use for connecting to the SQL server is via ODBC DSN.
This scripts runs on a DMZ located machine, while the SQL server is inside our LAN.

All works fine, but when the network connection between DMZ machine and SQL server lost
for a min. I receive an error.

I monitored the error and tried to error handle it.

after network is back the script is still on, but the error still there.

this is the part where I get the error:

-----------------------
Public function reso(byval res)

'error 3705 occurs here

objConn.Open strConn

Set objrs = objconn.Execute("exec cmp_creation "& Computer &"," & res)


objConn.Close

end function
-----------------------

Help would be much appreciatedWhy not use a DSN-less connection?

Also, if the server is in your DMZ and the SQL Server is behind a firewall (assumed), I would be sure to use the IP address instead of the network name.

Regards,

hmscott|||I'm not sure about the DSN-Less connection.... what would it change regarding my problem? also, what port do I have to open for a DSN-Less connection?|||I don't think that there's anything really wrong with a DSN connection and I'm not positive it would rectify your particular issue. However, DSN connections leave "footprints" on the server in your DMZ (in the registry) which are potentially vulnerable.

DSN and DSN-less connections use the same ports to connect to their respective data sources. Thus, a DSN connection to SQL Server would use port 1433 as would a DSN-less connection.

In general (from what I understand), DSN-less connections are "lighter" on the server, incurring less memory overhead. They are not, however, necessarily faster.

In your script, do you check for the state of the connection? Is it just opened once and left open? Or is it opened and closed as necessary?

Regards,

hmscott

Originally posted by eransp
I'm not sure about the DSN-Less connection.... what would it change regarding my problem? also, what port do I have to open for a DSN-Less connection?