Fail part of a Stored Procedures but continue processing

Here is a fun one, and I am not sure its possible.
I have 2 SQL Stored Procedures.
The first one calls the second one. All the first one does is grab the
next database name and pass that variable to the second stored
procedures. Once the second one finishes it goes back to the first one
goes to the next database and repeats the process until all the
databases are finished.
The problem is if the second Stored Procedures fails, it fails the
first.
My Question; is there a way that if the second stored procedure fails,
that the first stored procedure will still continue onto the next
database? Just because one database locks up for some reason does not
me they all will.
USE [DBAdmin]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[UD_Rebuild_All_Database_Indexes]
@.maxFrag DECIMAL = 30.0, -- The maximum fragmentation to allow
@.indexSizeForRebuild INT = 100 -- Maximum index size to do a
rebuild instead of defrag
AS
DECLARE @.ExecSQLcmd varchar(1024)
DECLARE @.DBNum_to_Name int
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.DBNum_to_Name is not null
BEGIN
SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes ' +
CONVERT(VARCHAR,@.maxFrag) + ', ' +
CONVERT(VARCHAR,@.indexSizeForRebuild)
FROM master.dbo.sysdatabases
WHERE dbid =@.DBNum_to_Name
-- Print (@.ExecSQLcmd) -- For Debugging
EXEC (@.ExecSQLcmd)
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.DBNum_to_Name AND DATABASEPROPERTY(name, 'IsReadOnly') =
0
ENDHow are you running this procedure? Have you tried it in QA? Without
testing it, I believe it should continue on, as long as the caller lets it.
It does depend on the error you are getting (an error can stop the batch.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Matthew" <MKruer@.gmail.com> wrote in message
news:1138314691.791878.263020@.o13g2000cwo.googlegroups.com...
> Here is a fun one, and I am not sure its possible.
> I have 2 SQL Stored Procedures.
> The first one calls the second one. All the first one does is grab the
> next database name and pass that variable to the second stored
> procedures. Once the second one finishes it goes back to the first one
> goes to the next database and repeats the process until all the
> databases are finished.
> The problem is if the second Stored Procedures fails, it fails the
> first.
> My Question; is there a way that if the second stored procedure fails,
> that the first stored procedure will still continue onto the next
> database? Just because one database locks up for some reason does not
> me they all will.
> USE [DBAdmin]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE proc [dbo].[UD_Rebuild_All_Database_Indexes]
> @.maxFrag DECIMAL = 30.0, -- The maximum fragmentation to allow
> @.indexSizeForRebuild INT = 100 -- Maximum index size to do a
> rebuild instead of defrag
> AS
> DECLARE @.ExecSQLcmd varchar(1024)
> DECLARE @.DBNum_to_Name int
> SELECT @.DBNum_to_Name =min(dbid)
> FROM master.dbo.sysdatabases
> WHERE dbid > 4
> WHILE @.DBNum_to_Name is not null
> BEGIN
> SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes ' +
> CONVERT(VARCHAR,@.maxFrag) + ', ' +
> CONVERT(VARCHAR,@.indexSizeForRebuild)
> FROM master.dbo.sysdatabases
> WHERE dbid =@.DBNum_to_Name
> -- Print (@.ExecSQLcmd) -- For Debugging
> EXEC (@.ExecSQLcmd)
> SELECT @.DBNum_to_Name =min(dbid)
> FROM master.dbo.sysdatabases
> WHERE dbid > @.DBNum_to_Name AND DATABASEPROPERTY(name, 'IsReadOnly') =
> 0
> END
>|||Matthew (MKruer@.gmail.com) writes:
> The problem is if the second Stored Procedures fails, it fails the
> first.
> My Question; is there a way that if the second stored procedure fails,
> that the first stored procedure will still continue onto the next
> database? Just because one database locks up for some reason does not
> me they all will.
Error handling in SQL Server is a messy topic. Some errors cancels the
entire batch, and in SQL 2000 there is no way you can catch that from
SQL.
If you are on SQL 2005, you can use the new TRY-CATCH construct.
If you are on SQL 2000, there is actually a workaround. It's not really
useful in the general case, but for a symin script like yours, it
would be OK. Since I have this in an article on my web site, I just post
the link: http://www.sommarskog.se/error-hand...#linked-servers
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

Extremely long processing time

I have a historical table that is over 100 million records in size.
Processing this table in AS takes an extremly long time (about 2 hours). Is
there any way to speed this up in AS?
is it 2000 or 2005?
As for the processing, are your cubes optimized (keys from dimensions read
from fact?). In another words, if you look at the select query does it have
any join statements?
MC
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>
|||do you have a distinct count measure in the cube?
(this cause the SQL statement to be sorted by this column)
have you created partitions?
have you optimized the schema in the cube (AS2000)? (the select statement
used use less inner join commands between your fact table and your
dimensions)
does your SQL Server is on another server or the same server?
how many aggregation have you in your cube?
what is your server? (CPU, Memory...)
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>
|||The general rule of thumb is that a common server-class machine with a
reasonable I/O subsystem will do about 1 million rows per minute. That
varies based on many issues, such as the number of aggregates, storage type.
Your experience is a bit low, but not unreasonable.
For hints on how to improve, look at the AS Performance Guide at:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
Hope that helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>

Extremely long processing time

I have a historical table that is over 100 million records in size.
Processing this table in AS takes an extremly long time (about 2 hours). Is
there any way to speed this up in AS?is it 2000 or 2005?
As for the processing, are your cubes optimized (keys from dimensions read
from fact?). In another words, if you look at the select query does it have
any join statements?
MC
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>|||do you have a distinct count measure in the cube?
(this cause the SQL statement to be sorted by this column)
have you created partitions?
have you optimized the schema in the cube (AS2000)? (the select statement
used use less inner join commands between your fact table and your
dimensions)
does your SQL Server is on another server or the same server?
how many aggregation have you in your cube?
what is your server? (CPU, Memory...)
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>|||The general rule of thumb is that a common server-class machine with a
reasonable I/O subsystem will do about 1 million rows per minute. That
varies based on many issues, such as the number of aggregates, storage type.
Your experience is a bit low, but not unreasonable.
For hints on how to improve, look at the AS Performance Guide at:
http://www.microsoft.com/technet/pr...n/ansvcspg.mspx
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nestor" <n3570r@.yahoo.com> wrote in message
news:u4GDtMV9FHA.2040@.TK2MSFTNGP14.phx.gbl...
>I have a historical table that is over 100 million records in size.
>Processing this table in AS takes an extremly long time (about 2 hours). Is
>there any way to speed this up in AS?
>

Extracting Parameter values from a Custom Data Processing Extension

I've created a custom Data Processing Extension and I've implemented the IDBCommandAnalysis interface so that my reports can enter parameters and pass them to my Data Processing Extension.

My question is, how do I extract the value from the Parameters coming from the report? Where do the parameters get passed off from the report? I can query the Parameters collection and my report gets prompted in Preview mode to enter something for the parameter but I can't find the spot where it gets passed for processing.

The IDBCommand interface contains a method CreateParameter(...).

For every query parameter, RS will call CreateParameter to add the parameter to the Parameters collection.

See also: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_clr_dataproc_2a4a.asp

-- Robert

|||Where is the proper place to initialize the Parameter collection? I keep getting a "Collection was modified" when I try to tell my Command object was parameters it has.|||

Robert Bruckner MSFT wrote:

For every query parameter, RS will call CreateParameter to add the parameter to the Parameters collection.

Sorry for pullig out this old thread, but it's exactly the problem I have right now.
At the moment my CreateParameter()-Function looks like this (like in the FSI example)

Code Snippet

Return CType(New DataParameter, IDataParameter)

nothing more. As an extra, I am searching for parameters everytime the command-text is set. I am pullig out the parameters from the text with a regExp and add them to the collection.
Is this the right way?

In technet it says: "The CreateParameter method is called by the report server to set the name and value for each query parameter in the command text. The parameter is then added to the Parameters collection prior to the ExecuteReader method being called."

Right now, I am doing all of that in my cde manually:
set command-text? then get parameters from string -> add all of them to the collection.

without this extra-step after setting the command-text, the collection gets called later and would be empty. There is no call from report-server to CreateParameter. (I put traces in every function/sub)|||Ok, I worked it out:

"Public ReadOnly Property Parameters() As IDataParameterCollection Implements IDbCommand.Parameters"
This one is used by the server to access and build up the parameterCollection.

"Public Function GetParameters() As IDataParameterCollection Implements IDbCommandAnalysis.GetParameters"
This one has to parse out all parameters from the query, so the dataset preview can list them.
http://technet.microsoft.com/en-us/library/aa256673(SQL.80).aspx

Extracting Parameter values from a Custom Data Processing Extension

I've created a custom Data Processing Extension and I've implemented the IDBCommandAnalysis interface so that my reports can enter parameters and pass them to my Data Processing Extension.

My question is, how do I extract the value from the Parameters coming from the report? Where do the parameters get passed off from the report? I can query the Parameters collection and my report gets prompted in Preview mode to enter something for the parameter but I can't find the spot where it gets passed for processing.

The IDBCommand interface contains a method CreateParameter(...).

For every query parameter, RS will call CreateParameter to add the parameter to the Parameters collection.

See also: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_clr_dataproc_2a4a.asp

-- Robert

|||Where is the proper place to initialize the Parameter collection? I keep getting a "Collection was modified" when I try to tell my Command object was parameters it has.
|||

Robert Bruckner MSFT wrote:

For every query parameter, RS will call CreateParameter to add the parameter to the Parameters collection.

Sorry for pullig out this old thread, but it's exactly the problem I have right now.
At the moment my CreateParameter()-Function looks like this (like in the FSI example)

Code Snippet

Return CType(New DataParameter, IDataParameter)

nothing more. As an extra, I am searching for parameters everytime the command-text is set. I am pullig out the parameters from the text with a regExp and add them to the collection.
Is this the right way?

In technet it says: "The CreateParameter method is called by the report server to set the name and value for each query parameter in the command text. The parameter is then added to the Parameters collection prior to the ExecuteReader method being called."

Right now, I am doing all of that in my cde manually:
set command-text? then get parameters from string -> add all of them to the collection.

without this extra-step after setting the command-text, the collection gets called later and would be empty. There is no call from report-server to CreateParameter. (I put traces in every function/sub)
|||Ok, I worked it out:

"Public ReadOnly Property Parameters() As IDataParameterCollection Implements IDbCommand.Parameters"
This one is used by the server to access and build up the parameterCollection.

"Public Function GetParameters() As IDataParameterCollection Implements IDbCommandAnalysis.GetParameters"
This one has to parse out all parameters from the query, so the dataset preview can list them.
http://technet.microsoft.com/en-us/library/aa256673(SQL.80).aspx