Align Calculated Members and Measures

Hi

I created two calculated member:

CREATEMEMBERCURRENTCUBE.[MEASURES].[Penetracion]

ASIIf([Measures].[Surtimientos] = 0,0,[Measures].[Seguros]/[Measures].[Surtimientos]),

FORMAT_STRING = "Percent",

VISIBLE = 1;

CREATEMEMBERCURRENTCUBE.[MEASURES].[Calidad]

ASIIf([Measures].[Seguros] = 0,0,[Measures].[Prima Seguros]/[Measures].[Seguros]),

FORMAT_STRING = "$#,##0.00",

VISIBLE = 1 ;

I have two measures called [Measures].[Surtimientos] and [Measures].[Seguros] and both have its FormatString property #,#.

When I browse the cube the measure and calculated meber values ared formated but they have left alignment. If I take away the FormatString they have right alignment.

Is there a way to align the values to right when I use FormatString?

Regards

If you change FORMAT_STRING = "$#,##0.00", to FORMAT_STRING = "$# ##0.00"?

Regards

Thomas Ivarsson

Alias for a database name?

Hi there,
I'm using SQL Server 2000.
I've created a database that has the name "SworderNet.ProductAnalysis."
Various applications connect to this database using this name. Well, it
turns out that certain programs like Excel have a problem accessing SQL
databases that have a "." in the name. The simplest solution is obviously to
just rename the database but it would be a major hassle to try to track down
all of the various clients that use this database and change the
appropriate config files to reflect the new name. Is there a way to create
an "alias" name for this database (similar to a CNAME record in dns)? For
example, I'd like to create an alias called "ProductAnalysis"... The
original name would still work, but clients that have problems with "."
embedded in the database name can just use the alias name instead.
Is it possible?
Sincerely,
David SworderI'm afraid not. The closest would be to create a new database and for each
table create a view inside that database. Not a nice solution...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"David Sworder" <GilGrissom@.CSILasVegas.com> wrote in message
news:unJfw9IDEHA.1600@.tk2msftngp13.phx.gbl...
> Hi there,
> I'm using SQL Server 2000.
> I've created a database that has the name
"SworderNet.ProductAnalysis."
> Various applications connect to this database using this name. Well, it
> turns out that certain programs like Excel have a problem accessing SQL
> databases that have a "." in the name. The simplest solution is obviously
to
> just rename the database but it would be a major hassle to try to track
down
> all of the various clients that use this database and change the
> appropriate config files to reflect the new name. Is there a way to create
> an "alias" name for this database (similar to a CNAME record in dns)? For
> example, I'd like to create an alias called "ProductAnalysis"... The
> original name would still work, but clients that have problems with "."
> embedded in the database name can just use the alias name instead.
> Is it possible?
> --
> Sincerely,
> David Sworder
>|||Hi,
I have a workaround.
The overhead of this workaround might be large depending upon the database
size and the number of users accessing it.
I tried to replicate the database to the same server with a different name.
If the application, that is facing problem, accessing the database with "."
in its name, does not update the data, then the replication can be
transactional and a frequency can be set for updating the new database.
But again as I said, the overhead may be big. But this workaround would
eliminate the requirement of renaming the database and changing it in the
various clients.
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

Alerts for new login accounts

Hi all,
Looking for a method on sql server 2k of automated monitoring and
notification of creation of new login accts. When new login is created,
alert would go to one or more IT mgt people, perhaps also mgt of business
unit that use the database.
We use Blat (freeware email util) for sending emails & alerts. Situation
is we want to grant SecurityAdmin rights to one or two individuals (up to
now only sa acct/server admin accts have been used) to be able to review
sql error logs. Best I can tell, we have to grant them the Security Admin
role to do that through Enterprise Manager. However this also gives them
the right to create login accounts, so they could create their own login
acct, go in and browse around data they have no business looking at, then
delete the login account.
I have looked around, found that triggers are not supported on system
tables (idea was to create trigger on sysxlogins). Best I could find was a
method of using a scheduled sql agent job to capture data from the
sysprocesses table and record connections that are not in a "not" list to
the server event log:
http://www.databasejournal.com/feat...cle.php/2243271
thksIf you only want the users to be able to view the SQL Server
error logs, why not just grant them permissions to view the
log file at the OS level? The logs are just text files -
they can view them without using Enterprise Manager and
without having access to SQL Server.
If you did want to monitor for the addition of logins, with
SQL Server 2000 you can capture the addition of logins,
database users, adding logins/users to server/database
roles, etc using Profiler (or a server side trace). You can
find the events you can monitor in books online in the
topic: Security Audit Event Classes.
-Sue
On Tue, 1 Jun 2004 10:26:06 -0700, "GM"
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>Looking for a method on sql server 2k of automated monitoring and
>notification of creation of new login accts. When new login is created,
>alert would go to one or more IT mgt people, perhaps also mgt of business
>unit that use the database.
>We use Blat (freeware email util) for sending emails & alerts. Situation
>is we want to grant SecurityAdmin rights to one or two individuals (up to
>now only sa acct/server admin accts have been used) to be able to review
>sql error logs. Best I can tell, we have to grant them the Security Admin
>role to do that through Enterprise Manager. However this also gives them
>the right to create login accounts, so they could create their own login
>acct, go in and browse around data they have no business looking at, then
>delete the login account.
>I have looked around, found that triggers are not supported on system
>tables (idea was to create trigger on sysxlogins). Best I could find was a
>method of using a scheduled sql agent job to capture data from the
>sysprocesses table and record connections that are not in a "not" list to
>the server event log:
>http://www.databasejournal.com/feat...cle.php/2243271
>thks

Alert on deadlock

I have created an SQL Agent Alert which should notify me whenever
a deadlock is occured on sql server. The following are the details:

Type : Performance Condition Alert
Object : SQLServerLocks
Counter : Number of deadlocks/sec
Instance: Database
Alert : If counter value rises above 0

I got deadlock situation a couple of times on server but i have never received any notification. It seems this settings doenot work. Pls. advise!

Thanks

Is the SQL Agent definitely running ?

If so, "Test" that that the SQL Agent can create a mail session. Right click SQL Server Agent, choose Properties, click the test button.

|||yes. Nothing wrong with sql agent. as such i get other alerts but not this one.|||Is the occurrence count still zero ?
Under the Response tab try reducing the "delay between responses". I recall reading somewhere that if at the time SQL samples the counters there may well not be a deadlock. i.e. the delay between responses is sufficiently high for the condition to disappear before SQL samples the data.

alert in clustering

i have created sql alerts in sql server cluster

the alert fails trigger eventhough eventlog and sql server agents are working it reflects as if no events occur

Please verify that the Alerts are [enabled].|||

Arnie Rowland wrote:

Please verify that the Alerts are [enabled].

it is enabled

Alert doesn't start

Hi all,
I have the following problem. I need to start a tarce in a job step. I
created the trace and it works properly. I scripted it and ran it in Query
Analyzer. Although the status displayed by SELECT * FROM ::
fn_trace_getinfo(default) says the trace is started (status=1), nothing
happens. What can be the problem?
Thanks in advance.
Sincerely,
KolosWhat do you mean, when you say nothing happens? Are you trying to open the
output trace file when the trace is running? Then it may not work.
You have to stop the trace, to open the file to which trace is currently
logging.
You have to use sp_trace_setstatus to stop and close the trace, before
opening the output file.
You may find my following article useful:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Kolos" <Kolos@.discussions.microsoft.com> wrote in message
news:60E1CCF1-AF58-46FA-BB1F-B58C10C3115E@.microsoft.com...
Hi all,
I have the following problem. I need to start a tarce in a job step. I
created the trace and it works properly. I scripted it and ran it in Query
Analyzer. Although the status displayed by SELECT * FROM ::
fn_trace_getinfo(default) says the trace is started (status=1), nothing
happens. What can be the problem?
Thanks in advance.
Sincerely,
Kolos|||Well, thanks. The size of the file was 0 KB until the trace was stopped. I
feel a bit stupid... Thanks, anyway.
"Narayana Vyas Kondreddi" wrote:
> What do you mean, when you say nothing happens? Are you trying to open the
> output trace file when the trace is running? Then it may not work.
> You have to stop the trace, to open the file to which trace is currently
> logging.
> You have to use sp_trace_setstatus to stop and close the trace, before
> opening the output file.
> You may find my following article useful:
> http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Kolos" <Kolos@.discussions.microsoft.com> wrote in message
> news:60E1CCF1-AF58-46FA-BB1F-B58C10C3115E@.microsoft.com...
> Hi all,
> I have the following problem. I need to start a tarce in a job step. I
> created the trace and it works properly. I scripted it and ran it in Query
> Analyzer. Although the status displayed by SELECT * FROM ::
> fn_trace_getinfo(default) says the trace is started (status=1), nothing
> happens. What can be the problem?
> Thanks in advance.
> Sincerely,
> Kolos
>
>

Aggregations Hits

Dear all,

I have a problem with aggregations.

I have created aggregations on my different partitions; on a particular partition the wizard is announcing a performance gain of 44%.

Despite my reports use the measure from that partition when I trace the MDX query execution using SQL Server Profiler I have no aggregations hits.

As I have modified the MDX queries many times I was thinking my MDX queries were poorly written and that would be the cause of the aggregations not being hit.

I have then created a blank report in BIDS and using the Query Builder I have created a simple query using the measure for which the aggregation designer wizard was announcing 44% of perf gain but just as in my reports the SQL Server Profiler shows that there is no aggregation hit at all.

What am I doing wrong? My cube is quite simple and very light, I have one main measure with about 1.3 million rows and the dimensions are quite small but performance is very poor.

Any help will be appreciated.

It could be a number of things. First of all, it sounds like the aggregations you've built aren't useful for the queries you're running - that's not the fault of the queries themselves, it's just you need to build the right aggregations. The term 'performance gain' is a bit misleading in BIDS because it doesn't mean that all of your queries will automatically run x% faster; only queries which can use the aggregations you've built to derive their data will run faster.

Have you read the following white papers?

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx

http://www.microsoft.com/downloads/details.aspx?FamilyId=975C5BB2-8207-4B4E-BE7C-06AC86E24C13&displaylang=en

They should give you an idea of what you need to do. It's also possible (especially given the fact that your cube is so small) that the performance problems you've got won't be solved with aggregations. Have you got any calculations defined on your cube?

Chris

|||Ensure that youre aggregations are built, you have been running ProcessIndex?|||Thanks for your answer Chris I'll check these White papers.

Aggregation Problem with Hours and Minutes in Time Dimension

For the first time, I have created a time dimension that includes hours and minutes, in addition to the Year, Quarter, Month, and Date that I am familiar with.

I want to display the average of my measures at whatever level of Time the user chooses. I thus chose "AverageOfChildren" as the aggregation method for these measures.

Unfortunately, SSAS aggregates these measures by Sum over Hours and Minutes, and then by AverageOfChildren over Date, Month, Quarter, and Year.

I am confused.

How can I force it to perform an "AverageOfChildren" at all levels ?

First, have a look at this document(http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx) and think about your design a second time(on page 34-35 in the word version)

Next. This is only a guess but how are your attribute relations between minutes, hours, date,quarter and year defined, if they are included in the same user hierarchy?

HTH

Thomas Ivarsson

aggregation Problem

I created a cube for Inventory valuation.
Every monthend a static Inventory valuation of every item will be put in the
cubes.
1. I don't want to aggregate the value across the months;
2. If the user view by year, I would like to system to put the Latest
valuation in that year. for examples 2003 = 2003/Dec, 2004 = 2004/Dec, 2005 =
2005/Jan.
How can I achieve it in the cubes design?
Hi Kam
Try looking at the ClosingPeriod MDX function in BOL
Yours sincerely
Thomas Kejser
M.Sc, MCDBA
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:7B95BD16-6351-407E-95DD-244D962F9266@.microsoft.com...
>I created a cube for Inventory valuation.
> Every monthend a static Inventory valuation of every item will be put in
> the
> cubes.
> 1. I don't want to aggregate the value across the months;
> 2. If the user view by year, I would like to system to put the Latest
> valuation in that year. for examples 2003 = 2003/Dec, 2004 = 2004/Dec,
> 2005 =
> 2005/Jan.
> How can I achieve it in the cubes design?

aggregation Problem

I created a cube for Inventory valuation.
Every monthend a static Inventory valuation of every item will be put in the
cubes.
1. I don't want to aggregate the value across the months;
2. If the user view by year, I would like to system to put the Latest
valuation in that year. for examples 2003 = 2003/Dec, 2004 = 2004/Dec, 2005
=
2005/Jan.
How can I achieve it in the cubes design?Hi Kam
Try looking at the ClosingPeriod MDX function in BOL
Yours sincerely
Thomas Kejser
M.Sc, MCDBA
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:7B95BD16-6351-407E-95DD-244D962F9266@.microsoft.com...
>I created a cube for Inventory valuation.
> Every monthend a static Inventory valuation of every item will be put in
> the
> cubes.
> 1. I don't want to aggregate the value across the months;
> 2. If the user view by year, I would like to system to put the Latest
> valuation in that year. for examples 2003 = 2003/Dec, 2004 = 2004/Dec,
> 2005 =
> 2005/Jan.
> How can I achieve it in the cubes design?

Aggravating problem with the Dataset Field Refresh

Has anyone else but me had this problem before? I have created an SQL query
using the PL/SQL Developer tool while connected up to an Oracle database.
The query works fine. Then I transfer the SQL query copy/paste to the
Generic Query Designer of RS2000, set my parameters and shared data source
correctly. I then click refresh fields and get the following error.
Could not generate a list of fields for the query.
Check the query syntax or click Refresh Fields on the query toolbar.
ExecuteReader requires an open and available Connection. The connection's
current state is Closed.
At this point I double check my syntax and connection and notice that the
parameters I have previously set up have been obliterated and I reassign
them. I try again only to experience the same error.
Here is the text of my query. I also have another very similar query that
works just fine. BTW, this query was longer at one point and I shortened it,
immediately thereafter it started behaving this way. Why am I experiencing
this behavior?
SELECT
COUNT(*) RespondentCount
,SUM(CASE WHEN s.surveysetting IS NULL THEN 1
WHEN s.surveysetting NOT IN(0,1,2) THEN 1
ELSE 0 END) SurveySettingUnknown
,SUM(CASE s.surveysetting WHEN '0' THEN 1 ELSE 0 END)
SurveySettingInProcessing
,SUM(CASE s.surveysetting WHEN '1' THEN 1 ELSE 0 END) PreDeployment
,SUM(CASE s.surveysetting WHEN '2' THEN 1 ELSE 0 END) PostDeployment
,SUM(CASE WHEN s.surveysetting = 1 AND s.gender = 'M' THEN 1 ELSE 0 END)
PreDeploymentMale
,SUM(CASE WHEN s.surveysetting = 2 AND s.gender = 'M' THEN 1 ELSE 0 END)
PostDeploymentMale
,SUM(CASE WHEN s.surveysetting = 1 AND s.gender = 'F' THEN 1 ELSE 0 END)
PreDeploymentFemale
,SUM(CASE WHEN s.surveysetting = 2 AND s.gender = 'F' THEN 1 ELSE 0 END)
PostDeploymentFemale
,SUM(CASE WHEN s.surveysetting = 1 AND TO_DATE(s.dob, 'MM/DD/YYYY')
BETWEEN ADD_MONTHS(SYSDATE, -252)+1 AND SYSDATE THEN 1 ELSE 0 END)
AgePreDeployLTE20
,SUM(CASE WHEN s.surveysetting = 2 AND TO_DATE(s.dob, 'MM/DD/YYYY')
BETWEEN ADD_MONTHS(SYSDATE, -252)+1 AND SYSDATE THEN 1 ELSE 0 END)
AgePostDeployLTE20
,SUM(CASE WHEN s.surveysetting = 1 AND TO_DATE(s.dob, 'MM/DD/YYYY')
BETWEEN ADD_MONTHS(SYSDATE, -372)+1 AND ADD_MONTHS(SYSDATE, -252) THEN 1 ELSE
0 END) AgePreDeployBT21and30
,SUM(CASE WHEN s.surveysetting = 2 AND TO_DATE(s.dob, 'MM/DD/YYYY')
BETWEEN ADD_MONTHS(SYSDATE, -372)+1 AND ADD_MONTHS(SYSDATE, -252) THEN 1 ELSE
0 END) AgePostDeployBT21and30
,SUM(CASE WHEN s.surveysetting = 1 AND TO_DATE(s.dob, 'MM/DD/YYYY')
BETWEEN ADD_MONTHS(SYSDATE, -492)+1 AND ADD_MONTHS(SYSDATE, -372) THEN 1 ELSE
0 END) AgePreDeployBT31and40
,SUM(CASE WHEN s.surveysetting = 2 AND TO_DATE(s.dob, 'MM/DD/YYYY')
BETWEEN ADD_MONTHS(SYSDATE, -492)+1 AND ADD_MONTHS(SYSDATE, -372) THEN 1 ELSE
0 END) AgePostDeployBT31and40
,SUM(CASE WHEN s.surveysetting = 1 AND TO_DATE(s.dob, 'MM/DD/YYYY')
BETWEEN ADD_MONTHS(SYSDATE, -612)+1 AND ADD_MONTHS(SYSDATE, -492) THEN 1 ELSE
0 END) AgePreDeployBT41and50
,SUM(CASE WHEN s.surveysetting = 2 AND TO_DATE(s.dob, 'MM/DD/YYYY')
BETWEEN ADD_MONTHS(SYSDATE, -612)+1 AND ADD_MONTHS(SYSDATE, -492) THEN 1 ELSE
0 END) AgePostDeployBT41and50
,SUM(CASE WHEN s.surveysetting = 1 AND TO_DATE(s.dob, 'MM/DD/YYYY') >= ADD_MONTHS(SYSDATE, -612) THEN 1 ELSE 0 END) AgePreDeployGTE51
,SUM(CASE WHEN s.surveysetting = 2 AND TO_DATE(s.dob, 'MM/DD/YYYY') >= ADD_MONTHS(SYSDATE, -612) THEN 1 ELSE 0 END) AgePostDeployGTE51
,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('E-1', 'E-2', 'E-3')
THEN 1 ELSE 0 END) PayPreDeployBTE1andE3
,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('E-1', 'E-2', 'E-3')
THEN 1 ELSE 0 END) PayPostDeployBTE1andE3
,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('E-4', 'E-5', 'E-6')
THEN 1 ELSE 0 END) PayPreDeployBTE4andE6
,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('E-4', 'E-5', 'E-6')
THEN 1 ELSE 0 END) PayPostDeployBTE4andE6
,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('E-7', 'E-8', 'E-9')
THEN 1 ELSE 0 END) PayPreDeployBTE7andE9
,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('E-7', 'E-8', 'E-9')
THEN 1 ELSE 0 END) PayPostDeployBTE7andE9
,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('W-1', 'W-2', 'W-3')
THEN 1 ELSE 0 END) PayPreDeployBTW1andW3
,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('W-1', 'W-2', 'W-3')
THEN 1 ELSE 0 END) PayPostDeployBTW1andW3
,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('W-4', 'W-5') THEN 1
ELSE 0 END) PayPreDeployInW4andW5
,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('W-4', 'W-5') THEN 1
ELSE 0 END) PayPostDeployInW4andW5
,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('O-1', 'O-2', 'O-3')
THEN 1 ELSE 0 END) PayPreDeployBTO1andO3
,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('O-1', 'O-2', 'O-3')
THEN 1 ELSE 0 END) PayPostDeployBTO1andO3
,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('O-4', 'O-5', 'O-6')
THEN 1 ELSE 0 END) PayPreDeployBTO4andO6
,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('O-4', 'O-5', 'O-6')
THEN 1 ELSE 0 END) PayPostDeployBTO4andO6
,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('O-7', 'O-8', 'O-9',
'O-10') THEN 1 ELSE 0 END) PayPreDeployBTO7andO10
,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('O-7', 'O-8', 'O-9',
'O-10') THEN 1 ELSE 0 END) PayPostDeployBTO7andO10
,SUM(CASE WHEN s.last_2_yearsdeployed = 1 THEN 1 ELSE 0 END)
Last2YearsDeployed
,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoifdeployments = 1 THEN 1
ELSE 0 END) OifPreDeployments_1
,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoifdeployments = 1 THEN 1
ELSE 0 END) OifPostDeployments_1
,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoifdeployments = 2 THEN 1
ELSE 0 END) OifPreDeployments_2
,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoifdeployments = 2 THEN 1
ELSE 0 END) OifPostDeployments_2
,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoifdeployments = 3 THEN 1
ELSE 0 END) OifPreDeployments_3
,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoifdeployments = 3 THEN 1
ELSE 0 END) OifPostDeployments_3
,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoifdeployments = 4 THEN 1
ELSE 0 END) OifPreDeployments_4
,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoifdeployments = 4 THEN 1
ELSE 0 END) OifPostDeployments_4
,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoifdeployments >= 5 THEN 1
ELSE 0 END) OifPreDeployments_5Plus
,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoifdeployments >= 5 THEN 1
ELSE 0 END) OifPostDeployments_5Plus
,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoefdeployments = 1 THEN 1
ELSE 0 END) OefPreDeployments_1
,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoefdeployments = 1 THEN 1
ELSE 0 END) OefPostDeployments_1
,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoefdeployments = 2 THEN 1
ELSE 0 END) OefPreDeployments_2
,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoefdeployments = 2 THEN 1
ELSE 0 END) OefPostDeployments_2
,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoefdeployments = 3 THEN 1
ELSE 0 END) OefPreDeployments_3
,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoefdeployments = 3 THEN 1
ELSE 0 END) OefPostDeployments_3
,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoefdeployments = 4 THEN 1
ELSE 0 END) OefPreDeployments_4
,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoefdeployments = 4 THEN 1
ELSE 0 END) OefPostDeployments_4
,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoefdeployments >= 5 THEN 1
ELSE 0 END) OefPreDeployments_5Plus
,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoefdeployments >= 5 THEN 1
ELSE 0 END) OefPostDeployments_5Plus
,SUM(CASE WHEN s.surveysetting = 1 AND s.numberotherdeployments = 1 THEN 1
ELSE 0 END) OtherPreDeployments_1
,SUM(CASE WHEN s.surveysetting = 2 AND s.numberotherdeployments = 1 THEN 1
ELSE 0 END) OtherPostDeployments_1
,SUM(CASE WHEN s.surveysetting = 1 AND s.numberotherdeployments = 2 THEN 1
ELSE 0 END) OtherPreDeployments_2
,SUM(CASE WHEN s.surveysetting = 2 AND s.numberotherdeployments = 2 THEN 1
ELSE 0 END) OtherPostDeployments_2
,SUM(CASE WHEN s.surveysetting = 1 AND s.numberotherdeployments = 3 THEN 1
ELSE 0 END) OtherPreDeployments_3
,SUM(CASE WHEN s.surveysetting = 2 AND s.numberotherdeployments = 3 THEN 1
ELSE 0 END) OtherPostDeployments_3
,SUM(CASE WHEN s.surveysetting = 1 AND s.numberotherdeployments = 4 THEN 1
ELSE 0 END) OtherPreDeployments_4
,SUM(CASE WHEN s.surveysetting = 2 AND s.numberotherdeployments = 4 THEN 1
ELSE 0 END) OtherPostDeployments_4
,SUM(CASE WHEN s.surveysetting = 1 AND s.numberotherdeployments >= 5 THEN
1 ELSE 0 END) OtherPreDeployments_5Plus
,SUM(CASE WHEN s.surveysetting = 2 AND s.numberotherdeployments >= 5 THEN
1 ELSE 0 END) OtherPostDeployments_5Plus
,SUM(CASE WHEN s.surveysetting = 1 AND s.combatinjured = 1 THEN 1 ELSE 0
END) PreCombatInjured
,SUM(CASE WHEN s.surveysetting = 2 AND s.combatinjured = 1 THEN 1 ELSE 0
END) PostCombatInjured
,SUM(CASE WHEN s.surveysetting = 1 AND s.deploymenthealthconcern = 1 THEN
1 ELSE 0 END) PreDeploymentHealthConcern
,SUM(CASE WHEN s.surveysetting = 2 AND s.deploymenthealthconcern = 1 THEN
1 ELSE 0 END) PostDeploymentHealthConcern
FROM
hermes.surveyhraiiv2results s
WHERE
s.surveydate BETWEEN ? AND ?
--
Thank you,
JohnI deleted the dataset in question and then recreated it using the below query
and now it all works fine. This appears to be an obvious bug and should be
fixed.
--
Thank you,
John
"John A" wrote:
> Has anyone else but me had this problem before? I have created an SQL query
> using the PL/SQL Developer tool while connected up to an Oracle database.
> The query works fine. Then I transfer the SQL query copy/paste to the
> Generic Query Designer of RS2000, set my parameters and shared data source
> correctly. I then click refresh fields and get the following error.
> Could not generate a list of fields for the query.
> Check the query syntax or click Refresh Fields on the query toolbar.
> ExecuteReader requires an open and available Connection. The connection's
> current state is Closed.
> At this point I double check my syntax and connection and notice that the
> parameters I have previously set up have been obliterated and I reassign
> them. I try again only to experience the same error.
> Here is the text of my query. I also have another very similar query that
> works just fine. BTW, this query was longer at one point and I shortened it,
> immediately thereafter it started behaving this way. Why am I experiencing
> this behavior?
> SELECT
> COUNT(*) RespondentCount
> ,SUM(CASE WHEN s.surveysetting IS NULL THEN 1
> WHEN s.surveysetting NOT IN(0,1,2) THEN 1
> ELSE 0 END) SurveySettingUnknown
> ,SUM(CASE s.surveysetting WHEN '0' THEN 1 ELSE 0 END)
> SurveySettingInProcessing
> ,SUM(CASE s.surveysetting WHEN '1' THEN 1 ELSE 0 END) PreDeployment
> ,SUM(CASE s.surveysetting WHEN '2' THEN 1 ELSE 0 END) PostDeployment
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.gender = 'M' THEN 1 ELSE 0 END)
> PreDeploymentMale
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.gender = 'M' THEN 1 ELSE 0 END)
> PostDeploymentMale
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.gender = 'F' THEN 1 ELSE 0 END)
> PreDeploymentFemale
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.gender = 'F' THEN 1 ELSE 0 END)
> PostDeploymentFemale
> ,SUM(CASE WHEN s.surveysetting = 1 AND TO_DATE(s.dob, 'MM/DD/YYYY')
> BETWEEN ADD_MONTHS(SYSDATE, -252)+1 AND SYSDATE THEN 1 ELSE 0 END)
> AgePreDeployLTE20
> ,SUM(CASE WHEN s.surveysetting = 2 AND TO_DATE(s.dob, 'MM/DD/YYYY')
> BETWEEN ADD_MONTHS(SYSDATE, -252)+1 AND SYSDATE THEN 1 ELSE 0 END)
> AgePostDeployLTE20
> ,SUM(CASE WHEN s.surveysetting = 1 AND TO_DATE(s.dob, 'MM/DD/YYYY')
> BETWEEN ADD_MONTHS(SYSDATE, -372)+1 AND ADD_MONTHS(SYSDATE, -252) THEN 1 ELSE
> 0 END) AgePreDeployBT21and30
> ,SUM(CASE WHEN s.surveysetting = 2 AND TO_DATE(s.dob, 'MM/DD/YYYY')
> BETWEEN ADD_MONTHS(SYSDATE, -372)+1 AND ADD_MONTHS(SYSDATE, -252) THEN 1 ELSE
> 0 END) AgePostDeployBT21and30
> ,SUM(CASE WHEN s.surveysetting = 1 AND TO_DATE(s.dob, 'MM/DD/YYYY')
> BETWEEN ADD_MONTHS(SYSDATE, -492)+1 AND ADD_MONTHS(SYSDATE, -372) THEN 1 ELSE
> 0 END) AgePreDeployBT31and40
> ,SUM(CASE WHEN s.surveysetting = 2 AND TO_DATE(s.dob, 'MM/DD/YYYY')
> BETWEEN ADD_MONTHS(SYSDATE, -492)+1 AND ADD_MONTHS(SYSDATE, -372) THEN 1 ELSE
> 0 END) AgePostDeployBT31and40
> ,SUM(CASE WHEN s.surveysetting = 1 AND TO_DATE(s.dob, 'MM/DD/YYYY')
> BETWEEN ADD_MONTHS(SYSDATE, -612)+1 AND ADD_MONTHS(SYSDATE, -492) THEN 1 ELSE
> 0 END) AgePreDeployBT41and50
> ,SUM(CASE WHEN s.surveysetting = 2 AND TO_DATE(s.dob, 'MM/DD/YYYY')
> BETWEEN ADD_MONTHS(SYSDATE, -612)+1 AND ADD_MONTHS(SYSDATE, -492) THEN 1 ELSE
> 0 END) AgePostDeployBT41and50
> ,SUM(CASE WHEN s.surveysetting = 1 AND TO_DATE(s.dob, 'MM/DD/YYYY') >=> ADD_MONTHS(SYSDATE, -612) THEN 1 ELSE 0 END) AgePreDeployGTE51
> ,SUM(CASE WHEN s.surveysetting = 2 AND TO_DATE(s.dob, 'MM/DD/YYYY') >=> ADD_MONTHS(SYSDATE, -612) THEN 1 ELSE 0 END) AgePostDeployGTE51
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('E-1', 'E-2', 'E-3')
> THEN 1 ELSE 0 END) PayPreDeployBTE1andE3
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('E-1', 'E-2', 'E-3')
> THEN 1 ELSE 0 END) PayPostDeployBTE1andE3
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('E-4', 'E-5', 'E-6')
> THEN 1 ELSE 0 END) PayPreDeployBTE4andE6
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('E-4', 'E-5', 'E-6')
> THEN 1 ELSE 0 END) PayPostDeployBTE4andE6
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('E-7', 'E-8', 'E-9')
> THEN 1 ELSE 0 END) PayPreDeployBTE7andE9
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('E-7', 'E-8', 'E-9')
> THEN 1 ELSE 0 END) PayPostDeployBTE7andE9
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('W-1', 'W-2', 'W-3')
> THEN 1 ELSE 0 END) PayPreDeployBTW1andW3
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('W-1', 'W-2', 'W-3')
> THEN 1 ELSE 0 END) PayPostDeployBTW1andW3
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('W-4', 'W-5') THEN 1
> ELSE 0 END) PayPreDeployInW4andW5
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('W-4', 'W-5') THEN 1
> ELSE 0 END) PayPostDeployInW4andW5
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('O-1', 'O-2', 'O-3')
> THEN 1 ELSE 0 END) PayPreDeployBTO1andO3
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('O-1', 'O-2', 'O-3')
> THEN 1 ELSE 0 END) PayPostDeployBTO1andO3
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('O-4', 'O-5', 'O-6')
> THEN 1 ELSE 0 END) PayPreDeployBTO4andO6
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('O-4', 'O-5', 'O-6')
> THEN 1 ELSE 0 END) PayPostDeployBTO4andO6
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.paygrade IN('O-7', 'O-8', 'O-9',
> 'O-10') THEN 1 ELSE 0 END) PayPreDeployBTO7andO10
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.paygrade IN('O-7', 'O-8', 'O-9',
> 'O-10') THEN 1 ELSE 0 END) PayPostDeployBTO7andO10
> ,SUM(CASE WHEN s.last_2_yearsdeployed = 1 THEN 1 ELSE 0 END)
> Last2YearsDeployed
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoifdeployments = 1 THEN 1
> ELSE 0 END) OifPreDeployments_1
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoifdeployments = 1 THEN 1
> ELSE 0 END) OifPostDeployments_1
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoifdeployments = 2 THEN 1
> ELSE 0 END) OifPreDeployments_2
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoifdeployments = 2 THEN 1
> ELSE 0 END) OifPostDeployments_2
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoifdeployments = 3 THEN 1
> ELSE 0 END) OifPreDeployments_3
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoifdeployments = 3 THEN 1
> ELSE 0 END) OifPostDeployments_3
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoifdeployments = 4 THEN 1
> ELSE 0 END) OifPreDeployments_4
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoifdeployments = 4 THEN 1
> ELSE 0 END) OifPostDeployments_4
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoifdeployments >= 5 THEN 1
> ELSE 0 END) OifPreDeployments_5Plus
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoifdeployments >= 5 THEN 1
> ELSE 0 END) OifPostDeployments_5Plus
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoefdeployments = 1 THEN 1
> ELSE 0 END) OefPreDeployments_1
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoefdeployments = 1 THEN 1
> ELSE 0 END) OefPostDeployments_1
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoefdeployments = 2 THEN 1
> ELSE 0 END) OefPreDeployments_2
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoefdeployments = 2 THEN 1
> ELSE 0 END) OefPostDeployments_2
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoefdeployments = 3 THEN 1
> ELSE 0 END) OefPreDeployments_3
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoefdeployments = 3 THEN 1
> ELSE 0 END) OefPostDeployments_3
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoefdeployments = 4 THEN 1
> ELSE 0 END) OefPreDeployments_4
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoefdeployments = 4 THEN 1
> ELSE 0 END) OefPostDeployments_4
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.numberoefdeployments >= 5 THEN 1
> ELSE 0 END) OefPreDeployments_5Plus
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.numberoefdeployments >= 5 THEN 1
> ELSE 0 END) OefPostDeployments_5Plus
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.numberotherdeployments = 1 THEN 1
> ELSE 0 END) OtherPreDeployments_1
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.numberotherdeployments = 1 THEN 1
> ELSE 0 END) OtherPostDeployments_1
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.numberotherdeployments = 2 THEN 1
> ELSE 0 END) OtherPreDeployments_2
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.numberotherdeployments = 2 THEN 1
> ELSE 0 END) OtherPostDeployments_2
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.numberotherdeployments = 3 THEN 1
> ELSE 0 END) OtherPreDeployments_3
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.numberotherdeployments = 3 THEN 1
> ELSE 0 END) OtherPostDeployments_3
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.numberotherdeployments = 4 THEN 1
> ELSE 0 END) OtherPreDeployments_4
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.numberotherdeployments = 4 THEN 1
> ELSE 0 END) OtherPostDeployments_4
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.numberotherdeployments >= 5 THEN
> 1 ELSE 0 END) OtherPreDeployments_5Plus
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.numberotherdeployments >= 5 THEN
> 1 ELSE 0 END) OtherPostDeployments_5Plus
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.combatinjured = 1 THEN 1 ELSE 0
> END) PreCombatInjured
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.combatinjured = 1 THEN 1 ELSE 0
> END) PostCombatInjured
> ,SUM(CASE WHEN s.surveysetting = 1 AND s.deploymenthealthconcern = 1 THEN
> 1 ELSE 0 END) PreDeploymentHealthConcern
> ,SUM(CASE WHEN s.surveysetting = 2 AND s.deploymenthealthconcern = 1 THEN
> 1 ELSE 0 END) PostDeploymentHealthConcern
> FROM
> hermes.surveyhraiiv2results s
> WHERE
> s.surveydate BETWEEN ? AND ?
>
>
> --
> Thank you,
> John|||Hi John,
Thanks for suggestion. I will submit this through internal method to
development team. If there is any feedback, I will post here as a follow up.
In the meanwhile, you are also encouraged to submit this via the link below
http://lab.msdn.microsoft.com/productfeedback/default.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

aggegrate component

Hi,

i have 2.5million records which i have to aggregate on a couple of columns, then add those to another table.

i have created ole-db-source -> aggegrate

is this wise?

or should i use ole-db-source with a sql-query that aggregates for me...

will this increase my performance?

Try to use the power of the SQL engine to do this if you can. It has the benefit of indexes and statistics, which are not available to the Aggregate Tx, so it should be faster. However if the source is a high volume transactional system it may be unacceptable to have this type of query running, in which case the Agg Tx is probably a better way for you to go.

As to which is faster, I can guess, but just test it and find out the real answer.

Agent Proxy accounts not listed

Hi,

I just created a new proxy account. Creating credentials wasn't any problem (my NT user), creating the proxy (for all subsystems) also. But when I look at the subsystems in the SQL Management Studio I don't see the new proxy! I refreshed it, restarted it, no chance. I also tried to recreate the proxy but I can't since it tells me that it's already present. So where is it?

I need a proxy because my SQL Agent is running as local system but I need some credentials from a user to run a SSIS package. When I create a SSIS job step I only see "SQL Server Agent account" under "run as". I expected to see the proxy account there, too!

What can I do?

Thanks,

I tried with the same scenario you mentioned. Running SQLAgent account under LocalSystem and creating proxies and i could view then under Proxies node and could successfully see in Job step "Run As" combo box

I am assuming that grant the proxy to the subsystems was missed . Please check under should under unassigned proxies. If not granted to the subsystems Job step "Run As" combo box will list SQLAgent service account.

If you see proxy under unassigned Proxies node, open it and check the subsystems you want to use. Now things should work fine in job step dialog

Thanks,

Gops Dwarak

|||

Gops,

thanks for the repy... I see funny things going on... I can create the proxy, click on the subsystems I want to apply, it's created without any error. When I look into the subsystems I don't see the proxy. I see it under "unassigned". When I select the subsystems there and try to apply them, I get an error that this proxy is already assigned and I should check my permissions. I can delete it and try again, but that doesn't help...

Any idea?

Agent job fails, why?

SQL Server 2005 RTM:
I have created a job to copy a database from another server, but the job
fails when I try to run it.
This is the only information I can see in the Job History:
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
Emailed,Operator Net sent,Operator Paged,Retries Attempted
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DE V01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
last step to run was step 1
(CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0, 0,,,,0
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DE V01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSER VER2005_DEV01_0_Step,,Executed
as user: SAFARI\Administrator. The package execution failed. The step
failed.,00:00:31,0,0,,,,0
This doesn't help me to understand why the job fails.
How can I find the "Real" error log and get a decent clue on why the job
failed?
Olav
Specify an output file for the job step and see if you get any root error messages there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Olav Tollefsen" <x@.y.com> wrote in message news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
> SQL Server 2005 RTM:
> I have created a job to copy a database from another server, but the job fails when I try to run
> it.
> This is the only information I can see in the Job History:
> Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql
> Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DE V01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The last step to run was
> step 1 (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0, 0,,,,0
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DE V01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSER VER2005_DEV01_0_Step,,Executed
> as user: SAFARI\Administrator. The package execution failed. The step failed.,00:00:31,0,0,,,,0
> This doesn't help me to understand why the job fails.
> How can I find the "Real" error log and get a decent clue on why the job failed?
> Olav
>
|||I have created a new SSIS package using the Import / Export Wizard and
theese are the errors I get when I run it:
...
Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "Ad hoc updates to system catalogs are not
allowed.".
Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The "input "OLE DB Destination Input" (29)" failed because
error code 0xC020907B occurred, and the error row disposition on "input "OLE
DB Destination Input" (29)" specifies failure on error. An error occurred on
the specified object of the specified component.
Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline:
The ProcessInput method on component "OLE DB Destination" (2) failed with
error code 0xC0209029. The identified component returned an error from the
ProcessInput method. The error is specific to the component, but the error
is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline:
Thread "WorkThread0" has exited with error code 0xC0209029.
Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The final commit for the data insertion has started.
Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The final commit for the data insertion has ended.
Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: "component "OLE DB Destination" (2)" wrote 0 rows.
Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
Task failed: Execute Inner Package
Task failed: CompensatingSql
SSIS package "Package1.dtsx" finished: Failure.
...
Seems like the root error message is "Ad hoc updates to system catalogs are
not allowed.".
This doesn't make me very much wiser!
Does anyone have a clueon what this means?
Olav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
> Specify an output file for the job step and see if you get any root error
> messages there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Olav Tollefsen" <x@.y.com> wrote in message
> news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
>
|||I'm no DTS person, but it looks line you try to execute some code that tries to modify the system
tables on 2005 (which isn't possible). If this isn't the case, I suggest you post to the DTS group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Olav Tollefsen" <x@.y.com> wrote in message news:uaSLdqw4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I have created a new SSIS package using the Import / Export Wizard and theese are the errors I get
>when I run it:
> ...
> Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Prepare for
> Execute phase is beginning.
> Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Pre-Execute phase
> is beginning.
> Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Execute phase is
> beginning.
> Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: An OLE DB
> error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005
> Description: "Ad hoc updates to system catalogs are not allowed.".
> Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The "input
> "OLE DB Destination Input" (29)" failed because error code 0xC020907B occurred, and the error row
> disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error
> occurred on the specified object of the specified component.
> Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: The ProcessInput method
> on component "OLE DB Destination" (2) failed with error code 0xC0209029. The identified component
> returned an error from the ProcessInput method. The error is specific to the component, but the
> error is fatal and will cause the Data Flow task to stop running.
> Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Thread "WorkThread0"
> has exited with error code 0xC0209029.
> Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Post Execute
> phase is beginning.
> Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The
> final commit for the data insertion has started.
> Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The
> final commit for the data insertion has ended.
> Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Cleanup phase is
> beginning.
> Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: "component "OLE
> DB Destination" (2)" wrote 0 rows.
> Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
> Task failed: Execute Inner Package
> Task failed: CompensatingSql
> SSIS package "Package1.dtsx" finished: Failure.
> ...
> Seems like the root error message is "Ad hoc updates to system catalogs are not allowed.".
> This doesn't make me very much wiser!
> Does anyone have a clueon what this means?
> Olav
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
>

Agent job fails, why?

SQL Server 2005 RTM:
I have created a job to copy a database from another server, but the job
fails when I try to run it.
This is the only information I can see in the Job History:
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
Emailed,Operator Net sent,Operator Paged,Retries Attempted
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,
Error,0,DEV01,CDW_LION_SQLSERVER2005
_DEV01_0,(Job
outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
last step to run was step 1
(CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,
Error,1,DEV01,CDW_LION_SQLSERVER2005
_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_
Step,,Executed
as user: SAFARI\Administrator. The package execution failed. The step
failed.,00:00:31,0,0,,,,0
This doesn't help me to understand why the job fails.
How can I find the "Real" error log and get a decent clue on why the job
failed?
OlavSpecify an output file for the job step and see if you get any root error me
ssages there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Olav Tollefsen" <x@.y.com> wrote in message news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...[vb
col=seagreen]
> SQL Server 2005 RTM:
> I have created a job to copy a database from another server, but the job f
ails when I try to run
> it.
> This is the only information I can see in the Job History:
> Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Messa
ge,Duration,Sql
> Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,
Retries Attempted
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,
Error,0,DEV01,CDW_LION_SQLSERVER20
05_DEV01_0,(Job
> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
last step to run was
> step 1 (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,
Error,1,DEV01,CDW_LION_SQLSERVER20
05_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_
0_Step,,Executed
> as user: SAFARI\Administrator. The package execution failed. The step fai
led.,00:00:31,0,0,,,,0
> This doesn't help me to understand why the job fails.
> How can I find the "Real" error log and get a decent clue on why the job f
ailed?
> Olav
>[/vbcol]|||I have created a new SSIS package using the Import / Export Wizard and
theese are the errors I get when I run it:
...
Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "Ad hoc updates to system catalogs are not
allowed.".
Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The "input "OLE DB Destination Input" (29)" failed beca
use
error code 0xC020907B occurred, and the error row disposition on "input "OLE
DB Destination Input" (29)" specifies failure on error. An error occurred on
the specified object of the specified component.
Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeli
ne:
The ProcessInput method on component "OLE DB Destination" (2) failed with
error code 0xC0209029. The identified component returned an error from the
ProcessInput method. The error is specific to the component, but the error
is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeli
ne:
Thread "WorkThread0" has exited with error code 0xC0209029.
Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE
DB
Destination [2]: The final commit for the data insertion has started.
Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE
DB
Destination [2]: The final commit for the data insertion has ended.
Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: "component "OLE DB Destination" (2)" wrote 0 rows.
Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
Task failed: Execute Inner Package
Task failed: CompensatingSql
SSIS package "Package1.dtsx" finished: Failure.
...
Seems like the root error message is "Ad hoc updates to system catalogs are
not allowed.".
This doesn't make me very much wiser!
Does anyone have a clueon what this means?
Olav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
> Specify an output file for the job step and see if you get any root error
> messages there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Olav Tollefsen" <x@.y.com> wrote in message
> news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
>|||I'm no DTS person, but it looks line you try to execute some code that tries
to modify the system
tables on 2005 (which isn't possible). If this isn't the case, I suggest you
post to the DTS group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Olav Tollefsen" <x@.y.com> wrote in message news:uaSLdqw4FHA.696@.TK2MSFTNGP09.phx.gbl...[vbc
ol=seagreen]
>I have created a new SSIS package using the Import / Export Wizard and thee
se are the errors I get
>when I run it:
> ...
> Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Prepare for
> Execute phase is beginning.
> Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Pre-Execute phase
> is beginning.
> Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Execute phase is
> beginning.
> Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB D
estination [2]: An OLE DB
> error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hres
ult: 0x80004005
> Description: "Ad hoc updates to system catalogs are not allowed.".
> Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB D
estination [2]: The "input
> "OLE DB Destination Input" (29)" failed because error code 0xC020907B occu
rred, and the error row
> disposition on "input "OLE DB Destination Input" (29)" specifies failure o
n error. An error
> occurred on the specified object of the specified component.
> Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipe
line: The ProcessInput method
> on component "OLE DB Destination" (2) failed with error code 0xC0209029. T
he identified component
> returned an error from the ProcessInput method. The error is specific to t
he component, but the
> error is fatal and will cause the Data Flow task to stop running.
> Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipe
line: Thread "WorkThread0"
> has exited with error code 0xC0209029.
> Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Post Execute
> phase is beginning.
> Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OL
E DB Destination [2]: The
> final commit for the data insertion has started.
> Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OL
E DB Destination [2]: The
> final commit for the data insertion has ended.
> Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: Cleanup phase is
> beginning.
> Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B}, DT
S.Pipeline: "component "OLE
> DB Destination" (2)" wrote 0 rows.
> Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
> Task failed: Execute Inner Package
> Task failed: CompensatingSql
> SSIS package "Package1.dtsx" finished: Failure.
> ...
> Seems like the root error message is "Ad hoc updates to system catalogs ar
e not allowed.".
> This doesn't make me very much wiser!
> Does anyone have a clueon what this means?
> Olav
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
>[/vbcol]

Agent job fails, why?

SQL Server 2005 RTM:
I have created a job to copy a database from another server, but the job
fails when I try to run it.
This is the only information I can see in the Job History:
Date,Source,Severity,Step ID,Server,Job Name,Step
Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
Emailed,Operator Net sent,Operator Paged,Retries Attempted
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
last step to run was step 1
(CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
11/02/2005
13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
as user: SAFARI\Administrator. The package execution failed. The step
failed.,00:00:31,0,0,,,,0
This doesn't help me to understand why the job fails.
How can I find the "Real" error log and get a decent clue on why the job
failed?
OlavSpecify an output file for the job step and see if you get any root error messages there.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Olav Tollefsen" <x@.y.com> wrote in message news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
> SQL Server 2005 RTM:
> I have created a job to copy a database from another server, but the job fails when I try to run
> it.
> This is the only information I can see in the Job History:
> Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql
> Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The last step to run was
> step 1 (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
> 11/02/2005
> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
> as user: SAFARI\Administrator. The package execution failed. The step failed.,00:00:31,0,0,,,,0
> This doesn't help me to understand why the job fails.
> How can I find the "Real" error log and get a decent clue on why the job failed?
> Olav
>|||I have created a new SSIS package using the Import / Export Wizard and
theese are the errors I get when I run it:
...
Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client"
Hresult: 0x80004005 Description: "Ad hoc updates to system catalogs are not
allowed.".
Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The "input "OLE DB Destination Input" (29)" failed because
error code 0xC020907B occurred, and the error row disposition on "input "OLE
DB Destination Input" (29)" specifies failure on error. An error occurred on
the specified object of the specified component.
Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline:
The ProcessInput method on component "OLE DB Destination" (2) failed with
error code 0xC0209029. The identified component returned an error from the
ProcessInput method. The error is specific to the component, but the error
is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline:
Thread "WorkThread0" has exited with error code 0xC0209029.
Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The final commit for the data insertion has started.
Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB
Destination [2]: The final commit for the data insertion has ended.
Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B},
DTS.Pipeline: "component "OLE DB Destination" (2)" wrote 0 rows.
Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
Task failed: Execute Inner Package
Task failed: CompensatingSql
SSIS package "Package1.dtsx" finished: Failure.
...
Seems like the root error message is "Ad hoc updates to system catalogs are
not allowed.".
This doesn't make me very much wiser!
Does anyone have a clueon what this means?
Olav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
> Specify an output file for the job step and see if you get any root error
> messages there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Olav Tollefsen" <x@.y.com> wrote in message
> news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
>> SQL Server 2005 RTM:
>> I have created a job to copy a database from another server, but the job
>> fails when I try to run it.
>> This is the only information I can see in the Job History:
>> Date,Source,Severity,Step ID,Server,Job Name,Step
>> Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator
>> Emailed,Operator Net sent,Operator Paged,Retries Attempted
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
>> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The
>> last step to run was step 1
>> (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
>> as user: SAFARI\Administrator. The package execution failed. The step
>> failed.,00:00:31,0,0,,,,0
>> This doesn't help me to understand why the job fails.
>> How can I find the "Real" error log and get a decent clue on why the job
>> failed?
>> Olav
>|||I'm no DTS person, but it looks line you try to execute some code that tries to modify the system
tables on 2005 (which isn't possible). If this isn't the case, I suggest you post to the DTS group.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Olav Tollefsen" <x@.y.com> wrote in message news:uaSLdqw4FHA.696@.TK2MSFTNGP09.phx.gbl...
>I have created a new SSIS package using the Import / Export Wizard and theese are the errors I get
>when I run it:
> ...
> Information: 0x40043006 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Prepare for
> Execute phase is beginning.
> Information: 0x40043007 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Pre-Execute phase
> is beginning.
> Information: 0x4004300C at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Execute phase is
> beginning.
> Error: 0xC0202009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: An OLE DB
> error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005
> Description: "Ad hoc updates to system catalogs are not allowed.".
> Error: 0xC0209029 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The "input
> "OLE DB Destination Input" (29)" failed because error code 0xC020907B occurred, and the error row
> disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error
> occurred on the specified object of the specified component.
> Error: 0xC0047022 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: The ProcessInput method
> on component "OLE DB Destination" (2) failed with error code 0xC0209029. The identified component
> returned an error from the ProcessInput method. The error is specific to the component, but the
> error is fatal and will cause the Data Flow task to stop running.
> Error: 0xC0047021 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Thread "WorkThread0"
> has exited with error code 0xC0209029.
> Information: 0x40043008 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Post Execute
> phase is beginning.
> Information: 0x402090DF at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The
> final commit for the data insertion has started.
> Information: 0x402090E0 at {6C86B366-5C26-40A1-B214-86D284BE277B}, OLE DB Destination [2]: The
> final commit for the data insertion has ended.
> Information: 0x40043009 at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: Cleanup phase is
> beginning.
> Information: 0x4004300B at {6C86B366-5C26-40A1-B214-86D284BE277B}, DTS.Pipeline: "component "OLE
> DB Destination" (2)" wrote 0 rows.
> Task failed: {6C86B366-5C26-40A1-B214-86D284BE277B}
> Task failed: Execute Inner Package
> Task failed: CompensatingSql
> SSIS package "Package1.dtsx" finished: Failure.
> ...
> Seems like the root error message is "Ad hoc updates to system catalogs are not allowed.".
> This doesn't make me very much wiser!
> Does anyone have a clueon what this means?
> Olav
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OiKc3CR4FHA.744@.TK2MSFTNGP10.phx.gbl...
>> Specify an output file for the job step and see if you get any root error messages there.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Olav Tollefsen" <x@.y.com> wrote in message news:OAZp3hM4FHA.3636@.TK2MSFTNGP09.phx.gbl...
>> SQL Server 2005 RTM:
>> I have created a job to copy a database from another server, but the job fails when I try to run
>> it.
>> This is the only information I can see in the Job History:
>> Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql
>> Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,0,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,(Job
>> outcome),,The job failed. The Job was invoked by User SAFARI\olavt. The last step to run was
>> step 1 (CDW_LION_SQLSERVER2005_DEV01_0_Step).,00:00:31,0,0,,,,0
>> 11/02/2005
>> 13:35:51,CDW_LION_SQLSERVER2005_DEV01_0,Error,1,DEV01,CDW_LION_SQLSERVER2005_DEV01_0,CDW_LION_SQLSERVER2005_DEV01_0_Step,,Executed
>> as user: SAFARI\Administrator. The package execution failed. The step failed.,00:00:31,0,0,,,,0
>> This doesn't help me to understand why the job fails.
>> How can I find the "Real" error log and get a decent clue on why the job failed?
>> Olav
>>
>

Agent Job Error

Hi all,

I have created alert to give me information for a backup that was done and since I don't have SQL Mail set up on my servers I use sp_SQLSMTPMAIL using a smtp server. The Alert is kicking off a job called EMAIL DBA which have the code below:

exec master.dbo.sp_sqlsmtpmail @.vcTo = 'lwilliams@.uhc.com', @.vcBody = '

Error: [A-ERR]
Severity: [A-SEV]
Date: [STRTDT]
Time: [STRTTM]
Database: [A-DBN]
Message: [A-MSG]

Check the [SRVR] SQL Server ErrorLog and the Application event log on the server for additional details'

This uses the Agent Token which is very powerful.

The error I get on this job is:

Executed as user: NT AUTHORITY\SYSTEM. Line 8: Incorrect syntax near '\'. [SQLSTATE 42000] (Error 170). The step failed.

If you look at the code there is no '\', so am I missing something or could this error come from the sp_SQLSMTPMAIL.

Please help thanks

LystraCan anyone HELP out there?

Thanks

after taking backup , not able to see Databasename.Bak

Hi..

I am triying to take full ,differential ,transactionlog backups...

For that i have created jobs...

I am able to create a job, run a job, it says success...

But not able to see the database.bak at the specified location...

I have admin rights on my local computer .. also admin rights on sql server2005...

can you tell me , where i am going wrong...

And if i take a back up by right clicking the database , i am abe to get the database.bak

Since this is apparently a question related strictly to SQL, I've moved it from the Team Foundation Server forum to one of the SQL forums.|||If you created a database maintenance plan, the filenames should be in this format: dbname_db_yyyymmddhhmm.bak for full database backup; for transaction log backups, they're in his format: dbname_tlog_yyyymmddhhmm.trn. Look for these files|||

If you are doing a backup you are always doing a backup on the server not on the client you are connected with.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

After Restore of a DB to another, it runs slower?

Hi,
I've created a DB,and then I restored it from the develop DB, so I can get
all the data into it, but all the qry's run slower on the production DB as
compared to the develop DB.
Any suggestions?
Thanks alot!
DekeThere can be lots of reasons but have you updated the stats after the
restore? If not run sp_updatestats.
--
Andrew J. Kelly SQL MVP
"Deke" <fakeemail@.nospam.net> wrote in message
news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I've created a DB,and then I restored it from the develop DB, so I can
get
> all the data into it, but all the qry's run slower on the production DB as
> compared to the develop DB.
> Any suggestions?
> Thanks alot!
> Deke
>|||Hi Andrew,
Well I ran the sp_udatestats from the master db in query analyzer and it
showed a bunch of stuff being updated, but when I run the query it's still
slow (10 sec) where in the other develop db when I run the same query it's
instant.
Any other suggestions?
Thanks
Deke
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> There can be lots of reasons but have you updated the stats after the
> restore? If not run sp_updatestats.
> --
> Andrew J. Kelly SQL MVP
>
> "Deke" <fakeemail@.nospam.net> wrote in message
> news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I've created a DB,and then I restored it from the develop DB, so I can
> get
> > all the data into it, but all the qry's run slower on the production DB
as
> > compared to the develop DB.
> >
> > Any suggestions?
> > Thanks alot!
> > Deke
> >
> >
>|||Are the databases on the same server? If not could one server be better than
the other? Could network issues come into play due to the location of each
serer? Are you seeing the same results when you run SP from query analyzer?
"Deke" <fakeemail@.nospam.net> wrote in message
news:#WvNjO#eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew,
> Well I ran the sp_udatestats from the master db in query analyzer and it
> showed a bunch of stuff being updated, but when I run the query it's still
> slow (10 sec) where in the other develop db when I run the same query it's
> instant.
> Any other suggestions?
> Thanks
> Deke
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> > There can be lots of reasons but have you updated the stats after the
> > restore? If not run sp_updatestats.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Deke" <fakeemail@.nospam.net> wrote in message
> > news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > I've created a DB,and then I restored it from the develop DB, so I
can
> > get
> > > all the data into it, but all the qry's run slower on the production
DB
> as
> > > compared to the develop DB.
> > >
> > > Any suggestions?
> > > Thanks alot!
> > > Deke
> > >
> > >
> >
> >
>|||Well running it in Master does nothing for the stats in the user db. Try:
USE YourDB
GO
sp_updatestats
It also takes a little while for the data to get in cache and the sp's etc
to all compile before it will be as fast as possible. Is the hardware
identical?
Andrew J. Kelly SQL MVP
"Deke" <fakeemail@.nospam.net> wrote in message
news:%23WvNjO%23eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew,
> Well I ran the sp_udatestats from the master db in query analyzer and it
> showed a bunch of stuff being updated, but when I run the query it's still
> slow (10 sec) where in the other develop db when I run the same query it's
> instant.
> Any other suggestions?
> Thanks
> Deke
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> > There can be lots of reasons but have you updated the stats after the
> > restore? If not run sp_updatestats.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Deke" <fakeemail@.nospam.net> wrote in message
> > news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > I've created a DB,and then I restored it from the develop DB, so I
can
> > get
> > > all the data into it, but all the qry's run slower on the production
DB
> as
> > > compared to the develop DB.
> > >
> > > Any suggestions?
> > > Thanks alot!
> > > Deke
> > >
> > >
> >
> >
>|||Thanks Andrew,
The DB is on the same server, so it's not a hardware issue. Maybe during
the developemnt of the original query it fine tuned itself for the fastest
way, and then when copied it lost that info(')
But doing what you posted in your last post did the job, thank you very
much!
It's fun learning something new everyday. :)
Deke
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:etEEx2%23eEHA.3476@.tk2msftngp13.phx.gbl...
> Well running it in Master does nothing for the stats in the user db. Try:
> USE YourDB
> GO
> sp_updatestats
> It also takes a little while for the data to get in cache and the sp's etc
> to all compile before it will be as fast as possible. Is the hardware
> identical?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Deke" <fakeemail@.nospam.net> wrote in message
> news:%23WvNjO%23eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> > Hi Andrew,
> >
> > Well I ran the sp_udatestats from the master db in query analyzer and
it
> > showed a bunch of stuff being updated, but when I run the query it's
still
> > slow (10 sec) where in the other develop db when I run the same query
it's
> > instant.
> >
> > Any other suggestions?
> > Thanks
> > Deke
> >
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> > > There can be lots of reasons but have you updated the stats after the
> > > restore? If not run sp_updatestats.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Deke" <fakeemail@.nospam.net> wrote in message
> > > news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > >
> > > > I've created a DB,and then I restored it from the develop DB, so I
> can
> > > get
> > > > all the data into it, but all the qry's run slower on the production
> DB
> > as
> > > > compared to the develop DB.
> > > >
> > > > Any suggestions?
> > > > Thanks alot!
> > > > Deke
> > > >
> > > >
> > >
> > >
> >
> >
>|||Well each time a stored proc (or any query for that matter) is run (in each
db) it has to be compiled and a query plan is chosen. This compiled plan is
then stored in cache (memory only) so that when you run it again there is no
need to recompile again. The query plan is based on several things one of
which are the statistics for the table(s) involved. When you restore a DB
you should always update the stats to ensure they are current and useable.
When you copy a db and run a sp in the new db it has no knowledge of the old
one so it gets a new plan and if the stats are wrong it can choose a wrong
plan. When you update the stats it forces the plans to be recompiled again
and then they will use the new stats and hopefully choose the proper plan.
--
Andrew J. Kelly SQL MVP
"Deke" <fakeemail@.nospam.net> wrote in message
news:Ol8R49%23eEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Thanks Andrew,
> The DB is on the same server, so it's not a hardware issue. Maybe
during
> the developemnt of the original query it fine tuned itself for the fastest
> way, and then when copied it lost that info(')
> But doing what you posted in your last post did the job, thank you very
> much!
> It's fun learning something new everyday. :)
> Deke
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:etEEx2%23eEHA.3476@.tk2msftngp13.phx.gbl...
> > Well running it in Master does nothing for the stats in the user db.
Try:
> >
> > USE YourDB
> > GO
> > sp_updatestats
> >
> > It also takes a little while for the data to get in cache and the sp's
etc
> > to all compile before it will be as fast as possible. Is the hardware
> > identical?
> >
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Deke" <fakeemail@.nospam.net> wrote in message
> > news:%23WvNjO%23eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> > > Hi Andrew,
> > >
> > > Well I ran the sp_udatestats from the master db in query analyzer
and
> it
> > > showed a bunch of stuff being updated, but when I run the query it's
> still
> > > slow (10 sec) where in the other develop db when I run the same query
> it's
> > > instant.
> > >
> > > Any other suggestions?
> > > Thanks
> > > Deke
> > >
> > >
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> > > > There can be lots of reasons but have you updated the stats after
the
> > > > restore? If not run sp_updatestats.
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "Deke" <fakeemail@.nospam.net> wrote in message
> > > > news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> > > > > Hi,
> > > > >
> > > > > I've created a DB,and then I restored it from the develop DB, so
I
> > can
> > > > get
> > > > > all the data into it, but all the qry's run slower on the
production
> > DB
> > > as
> > > > > compared to the develop DB.
> > > > >
> > > > > Any suggestions?
> > > > > Thanks alot!
> > > > > Deke
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

After Restore of a DB to another, it runs slower?

Hi,
I've created a DB,and then I restored it from the develop DB, so I can get
all the data into it, but all the qry's run slower on the production DB as
compared to the develop DB.
Any suggestions?
Thanks alot!
DekeThere can be lots of reasons but have you updated the stats after the
restore? If not run sp_updatestats.
Andrew J. Kelly SQL MVP
"Deke" <fakeemail@.nospam.net> wrote in message
news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I've created a DB,and then I restored it from the develop DB, so I can
get
> all the data into it, but all the qry's run slower on the production DB as
> compared to the develop DB.
> Any suggestions?
> Thanks alot!
> Deke
>|||Hi Andrew,
Well I ran the sp_udatestats from the master db in query analyzer and it
showed a bunch of stuff being updated, but when I run the query it's still
slow (10 sec) where in the other develop db when I run the same query it's
instant.
Any other suggestions?
Thanks
Deke
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
> There can be lots of reasons but have you updated the stats after the
> restore? If not run sp_updatestats.
> --
> Andrew J. Kelly SQL MVP
>
> "Deke" <fakeemail@.nospam.net> wrote in message
> news:enigPp8eEHA.4068@.TK2MSFTNGP11.phx.gbl...
> get
as[vbcol=seagreen]
>|||Are the databases on the same server? If not could one server be better than
the other? Could network issues come into play due to the location of each
serer? Are you seeing the same results when you run SP from query analyzer?
"Deke" <fakeemail@.nospam.net> wrote in message
news:#WvNjO#eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew,
> Well I ran the sp_udatestats from the master db in query analyzer and it
> showed a bunch of stuff being updated, but when I run the query it's still
> slow (10 sec) where in the other develop db when I run the same query it's
> instant.
> Any other suggestions?
> Thanks
> Deke
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
can[vbcol=seagreen]
DB[vbcol=seagreen]
> as
>|||Well running it in Master does nothing for the stats in the user db. Try:
USE YourDB
GO
sp_updatestats
It also takes a little while for the data to get in cache and the sp's etc
to all compile before it will be as fast as possible. Is the hardware
identical?
Andrew J. Kelly SQL MVP
"Deke" <fakeemail@.nospam.net> wrote in message
news:%23WvNjO%23eEHA.1724@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew,
> Well I ran the sp_udatestats from the master db in query analyzer and it
> showed a bunch of stuff being updated, but when I run the query it's still
> slow (10 sec) where in the other develop db when I run the same query it's
> instant.
> Any other suggestions?
> Thanks
> Deke
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e6jrk79eEHA.396@.TK2MSFTNGP12.phx.gbl...
can[vbcol=seagreen]
DB[vbcol=seagreen]
> as
>|||Thanks Andrew,
The DB is on the same server, so it's not a hardware issue. Maybe during
the developemnt of the original query it fine tuned itself for the fastest
way, and then when copied it lost that info(')
But doing what you posted in your last post did the job, thank you very
much!
It's fun learning something new everyday.
Deke
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:etEEx2%23eEHA.3476@.tk2msftngp13.phx.gbl...
> Well running it in Master does nothing for the stats in the user db. Try:
> USE YourDB
> GO
> sp_updatestats
> It also takes a little while for the data to get in cache and the sp's etc
> to all compile before it will be as fast as possible. Is the hardware
> identical?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Deke" <fakeemail@.nospam.net> wrote in message
> news:%23WvNjO%23eEHA.1724@.TK2MSFTNGP10.phx.gbl...
it[vbcol=seagreen]
still[vbcol=seagreen]
it's[vbcol=seagreen]
> can
> DB
>|||Well each time a stored proc (or any query for that matter) is run (in each
db) it has to be compiled and a query plan is chosen. This compiled plan is
then stored in cache (memory only) so that when you run it again there is no
need to recompile again. The query plan is based on several things one of
which are the statistics for the table(s) involved. When you restore a DB
you should always update the stats to ensure they are current and useable.
When you copy a db and run a sp in the new db it has no knowledge of the old
one so it gets a new plan and if the stats are wrong it can choose a wrong
plan. When you update the stats it forces the plans to be recompiled again
and then they will use the new stats and hopefully choose the proper plan.
Andrew J. Kelly SQL MVP
"Deke" <fakeemail@.nospam.net> wrote in message
news:Ol8R49%23eEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Thanks Andrew,
> The DB is on the same server, so it's not a hardware issue. Maybe
during
> the developemnt of the original query it fine tuned itself for the fastest
> way, and then when copied it lost that info(')
> But doing what you posted in your last post did the job, thank you very
> much!
> It's fun learning something new everyday.
> Deke
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:etEEx2%23eEHA.3476@.tk2msftngp13.phx.gbl...
Try:[vbcol=seagreen]
etc[vbcol=seagreen]
and[vbcol=seagreen]
> it
> still
> it's
the[vbcol=seagreen]
I[vbcol=seagreen]
production[vbcol=seagreen]
>