Dataset error when deployed to Report Manager

Hi
I've got a custom assembly which performs validation on parameters
prior to submitting the query to the database. My dataset which
retrieves the report data is in Text format and calls an appropriate
validation method on free-text parameters, such as dates and numbers.
When validation passes, the code returns a string formatted for use in
the query, and when it fails it returns a string value that the stored
procedure recognises as a dummy value and returns without SELECTing any
data. My custom assembly sets a user-friendly error message as a
property, which is displayed on the report in place of any data.
I've designed a number of reports using this method and it works well
in the Designer environment. However, I've deployed a few of them to
Report Manager and am getting the following error when pressing "View
Report":
An error has occurred during report processing. (rsProcessingAborted)
Cannot set the command text for data set 'Report'.
(rsErrorSettingCommandText)
Error during processing of the CommandText expression of dataset
'Report'. (rsQueryCommandTextProcessingError)
An example of one of the simple Datasets is:
="EXEC spAgentSettlementsDetailedRpt " &
Code.Validator.ResetValidator &
"@.p_StartDate_IN = '" +
Code.Validator.ValidateStartDate(Parameters!p_StartDate_IN.Value) + "',
" &
"@.p_EndDate_IN= '" +
Code.Validator.ValidateEndDate(Parameters!p_StartDate_IN.Value,
Parameters!p_EndDate_IN.Value) + "', " &
"@.p_AgentID_IN = " + Parameters!p_AgentID_IN.Value.ToString() + ", "
&
"@.p_WS_Code_IN = " +
Code.Validator.ValidateWSCode(Parameters!p_WS_Code_IN.Value,
Parameters!p_AgentID_IN.Value, false)
I'm happy to provide the code within the assembly if it would be of any
help in debugging this problem.
In deploying to the server, I have copied the assembly (dll) to the
\bin directory on the server and uploaded the rdl file as per normal.
Any help would be greatly appreciated. Thank you.
AshleyI've discovered what was causing the error... it turned out to be a
security/permissions problem. The validate date methods attempted to
set the culture of the current thread to "en-AU" with the following
statement:
Thread.CurrentThread.CurrentCulture = New CultureInfo("en-AU")
This was to get around the fact that the result of the VB IsDate()
function appears to be dependent upon the culture settings of the
server. It was always returning false for dd/mm/yyyy dates such as
13/1/2005.
The above line of code was causing an exception to be raised in the
assembly and hence was returning garbage to the dataset.
Does anyone know the precise reason that setting the culture of the
thread, once the report is deployed, results in an exception?
Ashley.Manos@.gmail.com wrote:
> Hi
> I've got a custom assembly which performs validation on parameters
> prior to submitting the query to the database. My dataset which
> retrieves the report data is in Text format and calls an appropriate
> validation method on free-text parameters, such as dates and numbers.
> When validation passes, the code returns a string formatted for use in
> the query, and when it fails it returns a string value that the stored
> procedure recognises as a dummy value and returns without SELECTing any
> data. My custom assembly sets a user-friendly error message as a
> property, which is displayed on the report in place of any data.
> I've designed a number of reports using this method and it works well
> in the Designer environment. However, I've deployed a few of them to
> Report Manager and am getting the following error when pressing "View
> Report":
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot set the command text for data set 'Report'.
> (rsErrorSettingCommandText)
> Error during processing of the CommandText expression of dataset
> 'Report'. (rsQueryCommandTextProcessingError)
> An example of one of the simple Datasets is:
> ="EXEC spAgentSettlementsDetailedRpt " &
> Code.Validator.ResetValidator &
> "@.p_StartDate_IN = '" +
> Code.Validator.ValidateStartDate(Parameters!p_StartDate_IN.Value) + "',
> " &
> "@.p_EndDate_IN= '" +
> Code.Validator.ValidateEndDate(Parameters!p_StartDate_IN.Value,
> Parameters!p_EndDate_IN.Value) + "', " &
> "@.p_AgentID_IN = " + Parameters!p_AgentID_IN.Value.ToString() + ", "
> &
> "@.p_WS_Code_IN = " +
> Code.Validator.ValidateWSCode(Parameters!p_WS_Code_IN.Value,
> Parameters!p_AgentID_IN.Value, false)
> I'm happy to provide the code within the assembly if it would be of any
> help in debugging this problem.
> In deploying to the server, I have copied the assembly (dll) to the
> \bin directory on the server and uploaded the rdl file as per normal.
> Any help would be greatly appreciated. Thank you.
> Ashley|||I've discovered what was causing the error... it turned out to be a
security/permissions problem. The validate date methods attempted to
set the culture of the current thread to "en-AU" with the following
statement:
Thread.CurrentThread.CurrentCulture = New CultureInfo("en-AU")
This was to get around the fact that the result of the VB IsDate()
function appears to be dependent upon the culture settings of the
server. It was always returning false for dd/mm/yyyy dates such as
13/1/2005.
The above line of code was causing an exception to be raised in the
assembly and hence was returning garbage to the dataset.
Does anyone know the precise reason that setting the culture of the
thread, once the report is deployed, results in an exception?
> Hi
> I've got a custom assembly which performs validation on parameters
> prior to submitting the query to the database. My dataset which
> retrieves the report data is in Text format and calls an appropriate
> validation method on free-text parameters, such as dates and numbers.
> When validation passes, the code returns a string formatted for use in
> the query, and when it fails it returns a string value that the stored
> procedure recognises as a dummy value and returns without SELECTing any
> data. My custom assembly sets a user-friendly error message as a
> property, which is displayed on the report in place of any data.
> I've designed a number of reports using this method and it works well
> in the Designer environment. However, I've deployed a few of them to
> Report Manager and am getting the following error when pressing "View
> Report":
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot set the command text for data set 'Report'.
> (rsErrorSettingCommandText)
> Error during processing of the CommandText expression of dataset
> 'Report'. (rsQueryCommandTextProcessingError)
> An example of one of the simple Datasets is:
> ="EXEC spAgentSettlementsDetailedRpt " &
> Code.Validator.ResetValidator &
> "@.p_StartDate_IN = '" +
> Code.Validator.ValidateStartDate(Parameters!p_StartDate_IN.Value) + "',
> " &
> "@.p_EndDate_IN= '" +
> Code.Validator.ValidateEndDate(Parameters!p_StartDate_IN.Value,
> Parameters!p_EndDate_IN.Value) + "', " &
> "@.p_AgentID_IN = " + Parameters!p_AgentID_IN.Value.ToString() + ", "
> &
> "@.p_WS_Code_IN = " +
> Code.Validator.ValidateWSCode(Parameters!p_WS_Code_IN.Value,
> Parameters!p_AgentID_IN.Value, false)
> I'm happy to provide the code within the assembly if it would be of any
> help in debugging this problem.
> In deploying to the server, I have copied the assembly (dll) to the
> \bin directory on the server and uploaded the rdl file as per normal.
> Any help would be greatly appreciated. Thank you.
> Ashley

Database Tuning Advisor problems

Anyone know where I can find details on why my DTA stopped? It keeps halting
on the very first part of the process, "Submitting Configuration
Information". And the only error message it's giving me is "Tuning Process
exited unexpectedly.(DTAEngine)".
The first time around, I thought it was because of the time limit set on the
DTA, so I moved the "Limit Tuning Time". This time, though, it stopped well
before the time limit hit, and on the exact same step.
Unfortunately, I can see anything in the SQL Server or SQL Server Agent Logs
that gives me a clue on why it's choking on my tuning workload. My original
workload isn't terribly huge. It's a trace file based off the Tuning
template in Profiler and is less than 19 MB in size.
This is my first time using the DTA, so thoughts would be appreciated. I
did have to add the procs and tables to MSDB before I could get DTA to work
(for some reason, they're missing in all our instances) and I'm running 2k5
SP1 + hotfix.
Catadmin
--
MCDBA, MCSA, MCTS
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)Try focusing in on just a tiny part of the database to begin with. For
example, find a long running query that hits several tables. Turn on the
profiler, let it run. Now run the long running query about three times. Go
back to the profiler and save the trace. Open the trace in the tuning
advisor. Browse to the trace file. Select the database(s) that have the
tables you tested in the long running query. Select only those tables and
use only one of the databases. If you have more than one database you can
reuse the same trace. Let it run against those smallest of parameters.
Generally you will get results. Sometimes there isn't enough information in
the trace to kick off the process. At least that's my take on it.
--
Regards,
Jamie
"Catadmin" wrote:
> Anyone know where I can find details on why my DTA stopped? It keeps halting
> on the very first part of the process, "Submitting Configuration
> Information". And the only error message it's giving me is "Tuning Process
> exited unexpectedly.(DTAEngine)".
> The first time around, I thought it was because of the time limit set on the
> DTA, so I moved the "Limit Tuning Time". This time, though, it stopped well
> before the time limit hit, and on the exact same step.
> Unfortunately, I can see anything in the SQL Server or SQL Server Agent Logs
> that gives me a clue on why it's choking on my tuning workload. My original
> workload isn't terribly huge. It's a trace file based off the Tuning
> template in Profiler and is less than 19 MB in size.
> This is my first time using the DTA, so thoughts would be appreciated. I
> did have to add the procs and tables to MSDB before I could get DTA to work
> (for some reason, they're missing in all our instances) and I'm running 2k5
> SP1 + hotfix.
> Catadmin
> --
> MCDBA, MCSA, MCTS
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)|||Be VERY leery of using DTA. It does REALLY bad things sometimes - like
creating indexes that contain 80% of the columns in the table, multiple
indexes with nothing more than additional columns added to the set, etc. I
advise my clients to not use it.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:179FC3C7-2370-47CF-BF72-6F574B0280E9@.microsoft.com...
> Anyone know where I can find details on why my DTA stopped? It keeps
> halting
> on the very first part of the process, "Submitting Configuration
> Information". And the only error message it's giving me is "Tuning
> Process
> exited unexpectedly.(DTAEngine)".
> The first time around, I thought it was because of the time limit set on
> the
> DTA, so I moved the "Limit Tuning Time". This time, though, it stopped
> well
> before the time limit hit, and on the exact same step.
> Unfortunately, I can see anything in the SQL Server or SQL Server Agent
> Logs
> that gives me a clue on why it's choking on my tuning workload. My
> original
> workload isn't terribly huge. It's a trace file based off the Tuning
> template in Profiler and is less than 19 MB in size.
> This is my first time using the DTA, so thoughts would be appreciated. I
> did have to add the procs and tables to MSDB before I could get DTA to
> work
> (for some reason, they're missing in all our instances) and I'm running
> 2k5
> SP1 + hotfix.
> Catadmin
> --
> MCDBA, MCSA, MCTS
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the
> back?
> @.=)|||Jamie,
Hmm. Not sure why it wouldn't have enough information since the trace ran
off of Production during business hours, but I'll do as you suggest in a test
DB. See if that makes a difference.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"thejamie" wrote:
> Try focusing in on just a tiny part of the database to begin with. For
> example, find a long running query that hits several tables. Turn on the
> profiler, let it run. Now run the long running query about three times. Go
> back to the profiler and save the trace. Open the trace in the tuning
> advisor. Browse to the trace file. Select the database(s) that have the
> tables you tested in the long running query. Select only those tables and
> use only one of the databases. If you have more than one database you can
> reuse the same trace. Let it run against those smallest of parameters.
> Generally you will get results. Sometimes there isn't enough information in
> the trace to kick off the process. At least that's my take on it.
> --
> Regards,
> Jamie
>
> "Catadmin" wrote:
> > Anyone know where I can find details on why my DTA stopped? It keeps halting
> > on the very first part of the process, "Submitting Configuration
> > Information". And the only error message it's giving me is "Tuning Process
> > exited unexpectedly.(DTAEngine)".
> >
> > The first time around, I thought it was because of the time limit set on the
> > DTA, so I moved the "Limit Tuning Time". This time, though, it stopped well
> > before the time limit hit, and on the exact same step.
> >
> > Unfortunately, I can see anything in the SQL Server or SQL Server Agent Logs
> > that gives me a clue on why it's choking on my tuning workload. My original
> > workload isn't terribly huge. It's a trace file based off the Tuning
> > template in Profiler and is less than 19 MB in size.
> >
> > This is my first time using the DTA, so thoughts would be appreciated. I
> > did have to add the procs and tables to MSDB before I could get DTA to work
> > (for some reason, they're missing in all our instances) and I'm running 2k5
> > SP1 + hotfix.
> >
> > Catadmin
> > --
> > MCDBA, MCSA, MCTS
> > Random Thoughts: If a person is Microsoft Certified, does that mean that
> > Microsoft pays the bills for the funny white jackets that tie in the back?
> > @.=)|||Kevin,
I'm trying to run this offline, so I can consolidate or get rid of indexes.
Believe me, the last thing I'm going to do is run it online and allow it to
change stuff without me approving it.
Problem is, I can't even get it to run to begin with... GRRR. Oh, well.
I'll try Jamie's suggestion on a test DB and see if it runs that way.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"TheSQLGuru" wrote:
> Be VERY leery of using DTA. It does REALLY bad things sometimes - like
> creating indexes that contain 80% of the columns in the table, multiple
> indexes with nothing more than additional columns added to the set, etc. I
> advise my clients to not use it.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
> news:179FC3C7-2370-47CF-BF72-6F574B0280E9@.microsoft.com...
> > Anyone know where I can find details on why my DTA stopped? It keeps
> > halting
> > on the very first part of the process, "Submitting Configuration
> > Information". And the only error message it's giving me is "Tuning
> > Process
> > exited unexpectedly.(DTAEngine)".
> >
> > The first time around, I thought it was because of the time limit set on
> > the
> > DTA, so I moved the "Limit Tuning Time". This time, though, it stopped
> > well
> > before the time limit hit, and on the exact same step.
> >
> > Unfortunately, I can see anything in the SQL Server or SQL Server Agent
> > Logs
> > that gives me a clue on why it's choking on my tuning workload. My
> > original
> > workload isn't terribly huge. It's a trace file based off the Tuning
> > template in Profiler and is less than 19 MB in size.
> >
> > This is my first time using the DTA, so thoughts would be appreciated. I
> > did have to add the procs and tables to MSDB before I could get DTA to
> > work
> > (for some reason, they're missing in all our instances) and I'm running
> > 2k5
> > SP1 + hotfix.
> >
> > Catadmin
> > --
> > MCDBA, MCSA, MCTS
> > Random Thoughts: If a person is Microsoft Certified, does that mean that
> > Microsoft pays the bills for the funny white jackets that tie in the
> > back?
> > @.=)
>
>

Database Tuning Advisor problems

Anyone know where I can find details on why my DTA stopped? It keeps halting
on the very first part of the process, "Submitting Configuration
Information". And the only error message it's giving me is "Tuning Process
exited unexpectedly.(DTAEngine)".
The first time around, I thought it was because of the time limit set on the
DTA, so I moved the "Limit Tuning Time". This time, though, it stopped well
before the time limit hit, and on the exact same step.
Unfortunately, I can see anything in the SQL Server or SQL Server Agent Logs
that gives me a clue on why it's choking on my tuning workload. My original
workload isn't terribly huge. It's a trace file based off the Tuning
template in Profiler and is less than 19 MB in size.
This is my first time using the DTA, so thoughts would be appreciated. I
did have to add the procs and tables to MSDB before I could get DTA to work
(for some reason, they're missing in all our instances) and I'm running 2k5
SP1 + hotfix.
Catadmin
MCDBA, MCSA, MCTS
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
Try focusing in on just a tiny part of the database to begin with. For
example, find a long running query that hits several tables. Turn on the
profiler, let it run. Now run the long running query about three times. Go
back to the profiler and save the trace. Open the trace in the tuning
advisor. Browse to the trace file. Select the database(s) that have the
tables you tested in the long running query. Select only those tables and
use only one of the databases. If you have more than one database you can
reuse the same trace. Let it run against those smallest of parameters.
Generally you will get results. Sometimes there isn't enough information in
the trace to kick off the process. At least that's my take on it.
Regards,
Jamie
"Catadmin" wrote:

> Anyone know where I can find details on why my DTA stopped? It keeps halting
> on the very first part of the process, "Submitting Configuration
> Information". And the only error message it's giving me is "Tuning Process
> exited unexpectedly.(DTAEngine)".
> The first time around, I thought it was because of the time limit set on the
> DTA, so I moved the "Limit Tuning Time". This time, though, it stopped well
> before the time limit hit, and on the exact same step.
> Unfortunately, I can see anything in the SQL Server or SQL Server Agent Logs
> that gives me a clue on why it's choking on my tuning workload. My original
> workload isn't terribly huge. It's a trace file based off the Tuning
> template in Profiler and is less than 19 MB in size.
> This is my first time using the DTA, so thoughts would be appreciated. I
> did have to add the procs and tables to MSDB before I could get DTA to work
> (for some reason, they're missing in all our instances) and I'm running 2k5
> SP1 + hotfix.
> Catadmin
> --
> MCDBA, MCSA, MCTS
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
> @.=)
|||Be VERY leery of using DTA. It does REALLY bad things sometimes - like
creating indexes that contain 80% of the columns in the table, multiple
indexes with nothing more than additional columns added to the set, etc. I
advise my clients to not use it.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:179FC3C7-2370-47CF-BF72-6F574B0280E9@.microsoft.com...
> Anyone know where I can find details on why my DTA stopped? It keeps
> halting
> on the very first part of the process, "Submitting Configuration
> Information". And the only error message it's giving me is "Tuning
> Process
> exited unexpectedly.(DTAEngine)".
> The first time around, I thought it was because of the time limit set on
> the
> DTA, so I moved the "Limit Tuning Time". This time, though, it stopped
> well
> before the time limit hit, and on the exact same step.
> Unfortunately, I can see anything in the SQL Server or SQL Server Agent
> Logs
> that gives me a clue on why it's choking on my tuning workload. My
> original
> workload isn't terribly huge. It's a trace file based off the Tuning
> template in Profiler and is less than 19 MB in size.
> This is my first time using the DTA, so thoughts would be appreciated. I
> did have to add the procs and tables to MSDB before I could get DTA to
> work
> (for some reason, they're missing in all our instances) and I'm running
> 2k5
> SP1 + hotfix.
> Catadmin
> --
> MCDBA, MCSA, MCTS
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the
> back?
> @.=)
|||Jamie,
Hmm. Not sure why it wouldn't have enough information since the trace ran
off of Production during business hours, but I'll do as you suggest in a test
DB. See if that makes a difference.
Catadmin
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"thejamie" wrote:
[vbcol=seagreen]
> Try focusing in on just a tiny part of the database to begin with. For
> example, find a long running query that hits several tables. Turn on the
> profiler, let it run. Now run the long running query about three times. Go
> back to the profiler and save the trace. Open the trace in the tuning
> advisor. Browse to the trace file. Select the database(s) that have the
> tables you tested in the long running query. Select only those tables and
> use only one of the databases. If you have more than one database you can
> reuse the same trace. Let it run against those smallest of parameters.
> Generally you will get results. Sometimes there isn't enough information in
> the trace to kick off the process. At least that's my take on it.
> --
> Regards,
> Jamie
>
> "Catadmin" wrote:
|||Kevin,
I'm trying to run this offline, so I can consolidate or get rid of indexes.
Believe me, the last thing I'm going to do is run it online and allow it to
change stuff without me approving it.
Problem is, I can't even get it to run to begin with... GRRR. Oh, well.
I'll try Jamie's suggestion on a test DB and see if it runs that way.
Catadmin
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"TheSQLGuru" wrote:

> Be VERY leery of using DTA. It does REALLY bad things sometimes - like
> creating indexes that contain 80% of the columns in the table, multiple
> indexes with nothing more than additional columns added to the set, etc. I
> advise my clients to not use it.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
> news:179FC3C7-2370-47CF-BF72-6F574B0280E9@.microsoft.com...
>
>

Database Tuning Advisor problems

Anyone know where I can find details on why my DTA stopped? It keeps haltin
g
on the very first part of the process, "Submitting Configuration
Information". And the only error message it's giving me is "Tuning Process
exited unexpectedly.(DTAEngine)".
The first time around, I thought it was because of the time limit set on the
DTA, so I moved the "Limit Tuning Time". This time, though, it stopped well
before the time limit hit, and on the exact same step.
Unfortunately, I can see anything in the SQL Server or SQL Server Agent Logs
that gives me a clue on why it's choking on my tuning workload. My original
workload isn't terribly huge. It's a trace file based off the Tuning
template in Profiler and is less than 19 MB in size.
This is my first time using the DTA, so thoughts would be appreciated. I
did have to add the procs and tables to MSDB before I could get DTA to work
(for some reason, they're missing in all our instances) and I'm running 2k5
SP1 + hotfix.
Catadmin
--
MCDBA, MCSA, MCTS
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)Try focusing in on just a tiny part of the database to begin with. For
example, find a long running query that hits several tables. Turn on the
profiler, let it run. Now run the long running query about three times. Go
back to the profiler and save the trace. Open the trace in the tuning
advisor. Browse to the trace file. Select the database(s) that have the
tables you tested in the long running query. Select only those tables and
use only one of the databases. If you have more than one database you can
reuse the same trace. Let it run against those smallest of parameters.
Generally you will get results. Sometimes there isn't enough information in
the trace to kick off the process. At least that's my take on it.
--
Regards,
Jamie
"Catadmin" wrote:

> Anyone know where I can find details on why my DTA stopped? It keeps halt
ing
> on the very first part of the process, "Submitting Configuration
> Information". And the only error message it's giving me is "Tuning Proces
s
> exited unexpectedly.(DTAEngine)".
> The first time around, I thought it was because of the time limit set on t
he
> DTA, so I moved the "Limit Tuning Time". This time, though, it stopped we
ll
> before the time limit hit, and on the exact same step.
> Unfortunately, I can see anything in the SQL Server or SQL Server Agent Lo
gs
> that gives me a clue on why it's choking on my tuning workload. My origin
al
> workload isn't terribly huge. It's a trace file based off the Tuning
> template in Profiler and is less than 19 MB in size.
> This is my first time using the DTA, so thoughts would be appreciated. I
> did have to add the procs and tables to MSDB before I could get DTA to wor
k
> (for some reason, they're missing in all our instances) and I'm running 2k
5
> SP1 + hotfix.
> Catadmin
> --
> MCDBA, MCSA, MCTS
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the back?
'
> @.=)|||Be VERY leery of using DTA. It does REALLY bad things sometimes - like
creating indexes that contain 80% of the columns in the table, multiple
indexes with nothing more than additional columns added to the set, etc. I
advise my clients to not use it.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:179FC3C7-2370-47CF-BF72-6F574B0280E9@.microsoft.com...
> Anyone know where I can find details on why my DTA stopped? It keeps
> halting
> on the very first part of the process, "Submitting Configuration
> Information". And the only error message it's giving me is "Tuning
> Process
> exited unexpectedly.(DTAEngine)".
> The first time around, I thought it was because of the time limit set on
> the
> DTA, so I moved the "Limit Tuning Time". This time, though, it stopped
> well
> before the time limit hit, and on the exact same step.
> Unfortunately, I can see anything in the SQL Server or SQL Server Agent
> Logs
> that gives me a clue on why it's choking on my tuning workload. My
> original
> workload isn't terribly huge. It's a trace file based off the Tuning
> template in Profiler and is less than 19 MB in size.
> This is my first time using the DTA, so thoughts would be appreciated. I
> did have to add the procs and tables to MSDB before I could get DTA to
> work
> (for some reason, they're missing in all our instances) and I'm running
> 2k5
> SP1 + hotfix.
> Catadmin
> --
> MCDBA, MCSA, MCTS
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the
> back?
> @.=)|||Jamie,
Hmm. Not sure why it wouldn't have enough information since the trace ran
off of Production during business hours, but I'll do as you suggest in a tes
t
DB. See if that makes a difference.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"thejamie" wrote:
[vbcol=seagreen]
> Try focusing in on just a tiny part of the database to begin with. For
> example, find a long running query that hits several tables. Turn on the
> profiler, let it run. Now run the long running query about three times.
Go
> back to the profiler and save the trace. Open the trace in the tuning
> advisor. Browse to the trace file. Select the database(s) that have the
> tables you tested in the long running query. Select only those tables and
> use only one of the databases. If you have more than one database you ca
n
> reuse the same trace. Let it run against those smallest of parameters.
> Generally you will get results. Sometimes there isn't enough information
in
> the trace to kick off the process. At least that's my take on it.
> --
> Regards,
> Jamie
>
> "Catadmin" wrote:
>|||Kevin,
I'm trying to run this offline, so I can consolidate or get rid of indexes.
Believe me, the last thing I'm going to do is run it online and allow it to
change stuff without me approving it.
Problem is, I can't even get it to run to begin with... GRRR. Oh, well.
I'll try Jamie's suggestion on a test DB and see if it runs that way.
Catadmin
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"TheSQLGuru" wrote:

> Be VERY leery of using DTA. It does REALLY bad things sometimes - like
> creating indexes that contain 80% of the columns in the table, multiple
> indexes with nothing more than additional columns added to the set, etc.
I
> advise my clients to not use it.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
> news:179FC3C7-2370-47CF-BF72-6F574B0280E9@.microsoft.com...
>
>