SQL tool for MSDE: February 2012

Saturday, February 25, 2012

Aggregate (SUM) a column and then use the Result?

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

What about using an Execute SQL task in the control flow to put that value into a variable; then you can use that variable in the data flow and perform the calculation using a derived column. This thread explains how to run queries against an excel file: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772416&SiteID=1

|||

Fawad wrote:

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

Seems to me you're going about this the wrong way. You need to calculate the SUM first and THEN use it in your data-flow. Hence, this is two executables (i.e. tasks) in your package. Rafael gave an example of doing this.

-Jamie

|||Hi Jamie,

I understand the flow:

Load Data
Calculate the Sum
Calculate new columns based on the sum (mostly count_value/total to get the rate)
Insert the final data into database.

What I cannot figure out is how to do the sum as an aggregate and then use it to do the computation.

Fawad
|||Hi Jamie,

I understand the flow that I require:

Import data from Excel
Sum the one column
Use this sum to calculate new column (mostly rates)
Load in the database

What I cannot figure out is how to use the result of the sum from the aggregate task.

Fawad
|||

Jamie Thomson wrote:

Fawad wrote:

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

Seems to me you're going about this the wrong way. You need to calculate the SUM first and THEN use it in your data-flow. Hence, this is two executables (i.e. tasks) in your package. Rafael gave an example of doing this.

-Jamie

Hi Jamie,

I understand the flow
Load from Excel file
Use aggregate to calculate the SUM
Use the SUM to calculate the values for other columns (mostly rates)
Insert the data into a DB

What I do not understand is how to use the result of the Sum (its one value, think of select sum(field) from table) to calculate the other values.

Fawad
|||

Fawad wrote:

Jamie Thomson wrote:

Fawad wrote:

Hi,

I am importing some data from Excel. I have to SUM one of the columns, and then use the result of the sum to calculate the percentages of each row. How can I use the Aggregate to give me a total of a column, so that i can use the total in another task and use formulas to calculate the percentages? i have tried to use multicast and join, but I get an extra row with the sum, which is not what I want; I want to use the sum for all the data.

Thanks

Seems to me you're going about this the wrong way. You need to calculate the SUM first and THEN use it in your data-flow. Hence, this is two executables (i.e. tasks) in your package. Rafael gave an example of doing this.

-Jamie

Hi Jamie,
I understand the flow
Load from Excel file
Use aggregate to calculate the SUM
Use the SUM to calculate the values for other columns (mostly rates)
Insert the data into a DB
What I do not understand is how to use the result of the Sum (its one value, think of select sum(field) from table) to calculate the other values.
Fawad

Right. So Rafael's suggestion is exactly how you should attempt to do this.

-Jamie

|||

Fawad,

My sugestion was to use an Execute SQL task (control flow) to get the SUM value into a variable; it would be a query like Select Sum(yourcolumn) from [YourExcelSheet]. this step has to be done before the dataflow. Then the dataflow would have that value avilable and it could be used in a derived column.

Aggregate - Sum with group by

Hi,

I'm trying to use the aggregate transformation to sum my orders table unit price and quantity with a grouping of state but i can't see how to add the sub grouping. My order table has the following fields of interest Unit Price (Money), Quantity (Integer) and State (Varchar)

ID Unit Price Quantity State 1 $2.19 500 AZ 2 $29.99 33 WA 3 $1000.00 1 WA 4 $1.20 7 WA

When i run the aggregate i want the output to be grouped by state

Total Price Quantity Sold State $2.19 500 AZ $1031.19 41 WA

Hope the values are correct

Martin

Add the aggregate to your data flow, and connect an input. Open the properties and check the Unit Price, Quantity, and State on the available input columns. In the grid at the bottom, make sure the State has the Operation set to "Group By", and Operation to "Sum" for the Unit Price and Quantity columns. That should do it.|||In the operation selection for state i can't select "Group By" it only has "Count". Any ideas?|||

Martin Perkins wrote:
In the operation selection for state i can't select "Group By" it only has "Count". Any ideas?

That's strange. What's the data type of the state column? Have you installed service pack 1 at least?|||

The State property is Nvarchar(max) Just installing service pack 2 for SQL Server 2005

|||

It was the length of the state column. If i convert the column from Nvarchar(max) to Nvarchar(20) i can then group by.

Thanks for help

Martin

|||

SSIS treats NVARCHAR(MAX) as BLOB type, and does not support grouping by blobs. You need to convert it to fixed-lenght type, like NVARCHAR(2).

You can do it either

1) at the source - NVARCHAR(MAX) is very inefficient method of storing 2-character data, if you can change the source, I suggest doing it

2) case to fixed-lenght type in the SQL query

3) use SSIS to create another column of fixed lenght (I'm not sure if type convertion transform does it, if not then try derived column transform)

Hope this help,

Michael.

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.

Aggragate transform with "WHERE" condition

Hello, I am new in SSIS.

I have to Load a table with aggregate data, I can do it using Aggragate transformation, but other the "GROUP BY" I need to make a filter on input records with "WHERE" condition.

In TSQL it should be:

SELECT col1, SUM(col2) FROM T

GROUP BY col1

WHERE col3 = value.

But in SSIS ?

How can I do it?

Thank

If it is in the Where clause and not Having, I would just place the Where in the Data Source that feeds into the Aggregate Transformation.|||

I need more details... I dont know if I understood...

You can add the where condition in your SQL statment in the datasource, or you can use the ConditionSplit transform to filter the data... you more other options...

give more details...

helped?

regards

|||Use a conditional split before the aggregation to filter your data.|||

csi_hugh wrote:
If it is in the Where clause and not Having, I would just place the Where in the Data Source that feeds into the Aggregate Transformation.

So would I. If you have the option to, always filter the data at source rather than use a Conditional Split.

-Jamie

Aggr

Hi,

What I want is to to get SUM of col1 and list quarter data when
applicable.

DDL:
create table #temp (col1 int, rent int, transport int, qtr smallint,
other int);

DML:
insert into #temp
values(1, 800, 300, 1, 200)

insert into #temp
values(1, 800, 300, 2, 300)

insert into #temp
values(2, 800, 300, 2, 400)

Data retrieval DML:
select col1, sum(other) as other_Total, case when qtr = 1 then
sum(other) end qtr_total
from #temp
group by col1,qtr

Current Resultset:
col1 other_Total qtr_total
---- ---- ----
1 200 200
1 300 NULL
2 400 NULL

Desirable Resultset: (get ride of the middle row above and add up the
200 and 300), so, it would look like
col1 other_Total qtr_total
---- ---- ----
1 500 200
2 400 NULL

What am I missing here?

TIA.SELECT col1, SUM(other) AS other_total,
SUM(CASE WHEN qtr = 1 THEN other END) qtr_total
FROM #temp
GROUP BY col1

--
David Portas
SQL Server MVP
--|||Thank you, David.

Don

AggMerge_ .TMP files eat my harddisk

Hi champs;

This question has already been asked; but still no answer yet on this issue.

When I process the partition I can see that the aggregations are being created. When I look in the file system I see a bunch of .tmp files being created (e.g. AggMerge_5800_54_8s64d_28.tmp), all of them for about 1.0GB. I eventually run out of space on the drive and the processing fails.

All together these .tmp files take about 150GB. The source database is only about 200GB.

Any thoughts? Ideas? The aggregation design wizard tells me the aggregations will take up about 125kb, but the .tmp files tell me something different.

Thanks

kurlan

There are several points here.

One is. You need to take a look at the partition size. You might need to create smaller partitions.

Second. Aggregation design algorithm cannot calculate the size of your aggregations. It only makes size calculations based on the estimated counts on dimension attributes. You might want to create fewer aggregations.

Third. The reason you are seeing temporary files created is: All the aggregations you've designed cannot fit into memory and they are swapped to disc. By decreasing number of aggregations and by making sure you process fewer partitions in parallel, you should be able to achieve a point when aggregation processing does not require swapping.

Here is good source of information about best pracices for partitioning and aggregation creation: http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

Edward.
--
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.

Agg op question

hi,

i have a table 'Details' with columns [id int; level varchar(20)]. i want to get two things from one single query:
1) the total number of items with Details.id=xxx
2) all the associated [level] text

i tried a few times doing things like:
SELECT [Level], COUNT(*) FROM Details WHERE id=xxx GROUP BY id

but it either gives me error "Column [Level] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

i am using MS SQL Server as the back engine and T-SQL in Access...

Help~

thanks in advancePerhaps you can do this:

select level, (select count(*) from details where id=xxx) as cnt
from details where id=xxx;

Works in Oracle, I don't know about your DBMS.|||Beautifully done~ thanks!

Agents not appearing in Replication Monitor (SQL 2000)

During an upgrade, we had problems with PK errors on sp_MSget_repl_commands. I recreated the SP with the varbinary characteristic as we have seen in many posts and it was sucessful in starting. However, the sp now appears as a 'user' sp, not a 'system' sp, however I an not sure that is a real issue.

The problem I am seeing is that none of the agents are appearing in replication monitor under their agent folders. Replication appears to be working fine and the publications are showing on the distributor in the Replication Monitor - Publication folder under the appropriate publisher. But all of the agent specific folders (i.e. Snapshot agent, distribution agent, etc) are empty.

Another interesting point, is that if replication fails, the failure 'X' shows up on the Agents folder as well as the publisher, but again, no sign of the agent in the folder.

Thoughts?

this could be an upgrade bug where the underlying replication tables weren't updated properly, let me figure out what procs replication monitor calls to see what tables have wrong values.

Agents failed to load

Using SQL2005 EE SP1
I'm trying to set up a simple transactional replication with initial
snapshot (1st time on this server). I create the publisher and it says it's
successful, however the error log shows:
Job cannot run because LogReader subsystem failed to load. Job has been
suspended.
Job cannot run because Snapshot subsystem failed to load. Job has been
suspended.
Did I not install the proper components during the SQL2005 install?
Thanks
Ron
Is it possible that you have Express Edition on this box and this is the one
you are using? This is installed with Visual Studion and sometimes people
have got the editions confused. Pls can you run select SERVERPROPERTY (
'edition') to double check, and just in case you are connected to the EE in
SSMS, have a look at your list of services to see what services SQL server
appears under. If this is not the case then I'll see what other things we
could look for.
HTH,
Paul Ibison
|||Paul, it's definitely SQLServer2005 "Enterprise Edition" SP1.
"Paul Ibison" wrote:

> Is it possible that you have Express Edition on this box and this is the one
> you are using? This is installed with Visual Studion and sometimes people
> have got the editions confused. Pls can you run select SERVERPROPERTY (
> 'edition') to double check, and just in case you are connected to the EE in
> SSMS, have a look at your list of services to see what services SQL server
> appears under. If this is not the case then I'll see what other things we
> could look for.
> HTH,
> Paul Ibison
>
|||I'm all set now, I've got it working. Turns out there were earlier errors
from a couple of months ago that stated: Subsystem "LogReader" could not be
loaded
as well as the subsystems.
I googled that and the fix was to update the msdb.dbo.syssubsystems table to
point to the correct DLL and executable.
Thanks anyway.
"Ron" wrote:
[vbcol=seagreen]
> Paul, it's definitely SQLServer2005 "Enterprise Edition" SP1.
> "Paul Ibison" wrote:
|||Hi Ron - thanks for the update. I've found a potential cause of this issue
here: http://support.microsoft.com/kb/914171.
I'll add this to my website.
Cheers,
Paul Ibison

agents db rights for trans. repl

We are about to deploy transactional replication with queue updating
subscribers. Both the publisher and distributor are on server A and the
publisher is on server B. We have one publication on the publisher which
will PUSH a small database (around 51 articles) to the subscriber. The
MSSQLSERVER service and the SQLSERVERAGENT services run in the context of
a windows domain account. These services are configured with different
domain accounts on the 2 servers.
- Do the domain accounts require specific rights/permissions on both the SQL
servers and the databases?
- In the properties page for the snapshot agent, log reader agent, queue
reader and distribution agent there is an OWNER setting. Does this has to be
set to the domain accounts for the MSSQLSERVER and SQLSERVERAGENT services
so replication across servers can occur?
Sysadmin on the publisher, dbo on the subscriber for push, for pull sysadmin
on the subscriber and in the pal on the publisher.
I make the job owner sa. It works better for me, when I do this as opposed
to using another account.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"newsgroupuser" <abraxas1969@.hotmail.com> wrote in message
news:OAQA%23EPRFHA.1564@.TK2MSFTNGP09.phx.gbl...
> We are about to deploy transactional replication with queue updating
> subscribers. Both the publisher and distributor are on server A and the
> publisher is on server B. We have one publication on the publisher which
> will PUSH a small database (around 51 articles) to the subscriber. The
> MSSQLSERVER service and the SQLSERVERAGENT services run in the context
> of
> a windows domain account. These services are configured with different
> domain accounts on the 2 servers.
>
> - Do the domain accounts require specific rights/permissions on both the
> SQL
> servers and the databases?
>
> - In the properties page for the snapshot agent, log reader agent, queue
> reader and distribution agent there is an OWNER setting. Does this has to
> be
> set to the domain accounts for the MSSQLSERVER and SQLSERVERAGENT services
> so replication across servers can occur?
>
>

agents blocking each other

Contantly there seems to be a blocking issue between the distribution agent,
the log agent, and the distribution clean up agent.
This will eventually cause one of the agents to shut down (the log agent).
What can I do to elevate this situation and why is it happening?
--Kristy
Avoid using EM.
Through Query Analyzer run the distribution clean up agent at a time when
there are few users on the system.
Consider moving to pull subscriptions and possibly a remote distributor.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasereplyby@.posting.com> wrote in message
news:%235Ev4idHFHA.3628@.TK2MSFTNGP15.phx.gbl...
> Contantly there seems to be a blocking issue between the distribution
agent,
> the log agent, and the distribution clean up agent.
> This will eventually cause one of the agents to shut down (the log agent).
> What can I do to elevate this situation and why is it happening?
> --Kristy
>
|||Thanks.
Why is it happening and what do the suggestions you provide buy me? Also,
would it be okay to schedule a job to run it? That way the off peak hours
could be utilized best. (I do have to sleep at some point ;-) )
Thanks again,
(and still anxiously awaiting my order to arrive)
Kristy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u0iLc2gHFHA.2132@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Avoid using EM.
> Through Query Analyzer run the distribution clean up agent at a time when
> there are few users on the system.
> Consider moving to pull subscriptions and possibly a remote distributor.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Kristy" <pleasereplyby@.posting.com> wrote in message
> news:%235Ev4idHFHA.3628@.TK2MSFTNGP15.phx.gbl...
> agent,
agent).
>
|||In addition Hilary, what does pull subscriptions offer over push ?
"Kristy" <pleasepostreply@.here.com> wrote in message
news:enALdThHFHA.1176@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Thanks.
> Why is it happening and what do the suggestions you provide buy me? Also,
> would it be okay to schedule a job to run it? That way the off peak hours
> could be utilized best. (I do have to sleep at some point ;-) )
> Thanks again,
> (and still anxiously awaiting my order to arrive)
> Kristy
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u0iLc2gHFHA.2132@.TK2MSFTNGP14.phx.gbl...
when
> agent).
>
|||EM, does lots of enumeration and runs far more commands than a simple
command (lets say sp_addsubscription) does. Run profiler to see this.
So if you run a command directly in isqlw you get far less locking.
You can schedule a job to run it, but then you have to log error messages
and make sure that everything runs fine. I would advise you to set your
alarm for 2:00 AM and then log in and do it at that time.
Another thing you can do is stop SQL Server Agent, make your change, and
then restart it.
Pull subscribers run the distribution agent on the subscriber, it connects
with the distribution database on the publisher and results in better
performance on the publisher and less locking.
You might also want to stagger when you agents run - for instance stagger
them to run at a prime number - like every 7 minutes, and don't run them
continuously.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasepostreply@.here.com> wrote in message
news:enALdThHFHA.1176@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Thanks.
> Why is it happening and what do the suggestions you provide buy me? Also,
> would it be okay to schedule a job to run it? That way the off peak hours
> could be utilized best. (I do have to sleep at some point ;-) )
> Thanks again,
> (and still anxiously awaiting my order to arrive)
> Kristy
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u0iLc2gHFHA.2132@.TK2MSFTNGP14.phx.gbl...
when
> agent).
>
|||btw - with pull subscriptions you have no central point of administration.
For instance, with push you connect to your publisher in EM and manage your
replication solution. With pull you have to connect to each subscriber and
expand replication monitor to see errors. Make sure you use Replication
Monitor Group to show all your subscribers (use Tools, Replication, Show
Replication Monitor Group and go to the bottom node and right click on
Replication Monitor Group and select add distributor to monitor).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kristy" <pleasepostreply@.here.com> wrote in message
news:enALdThHFHA.1176@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Thanks.
> Why is it happening and what do the suggestions you provide buy me? Also,
> would it be okay to schedule a job to run it? That way the off peak hours
> could be utilized best. (I do have to sleep at some point ;-) )
> Thanks again,
> (and still anxiously awaiting my order to arrive)
> Kristy
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u0iLc2gHFHA.2132@.TK2MSFTNGP14.phx.gbl...
when
> agent).
>
|||answered above.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eQ1oolhHFHA.2420@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> In addition Hilary, what does pull subscriptions offer over push ?
> "Kristy" <pleasepostreply@.here.com> wrote in message
> news:enALdThHFHA.1176@.TK2MSFTNGP12.phx.gbl...
Also,[vbcol=seagreen]
hours[vbcol=seagreen]
> when
distributor.[vbcol=seagreen]
distribution
>

Agents and thread limits from SQL 2005 Distributor machine

Hi DBA's
We have one machime with 4 core processors and 2 GB of RAM. This machine have
3 sql 2005 instances and It's responsable form many replications. More of
them, have pull distribution agent, so we can controll these number over
project growing, but one instance don't.
All distribution agents were configured like "PUSH" and I don't intend to
use "PULL" with these because probably we'll affect subscribers with many
jobs (and thread distribution agents), that the same way, more difficult to
administrate.
How do we see these limits? If so, we can scale to another instance and
controll them!
Plese, if anyone has any article or "better pratical", we appreciate!
Numbers is the focus. We need numbers resgiters like x distrib.exe y logread.
exe and thus for ahead.
Thanks!
Krisnamourt
Message posted via http://www.droptable.com
select logreader=sum(case when program_name like '%logreader%'then 1 else 0
end),distrib=sum(case when program_name like '%'+rtrim(@.@.servername)+'%-%'
then 1 else 0 end) from sys.sysprocesses
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7bfea23d5f150@.uwe...
> Hi DBA's
> We have one machime with 4 core processors and 2 GB of RAM. This machine
> have
> 3 sql 2005 instances and It's responsable form many replications. More of
> them, have pull distribution agent, so we can controll these number over
> project growing, but one instance don't.
> All distribution agents were configured like "PUSH" and I don't intend to
> use "PULL" with these because probably we'll affect subscribers with many
> jobs (and thread distribution agents), that the same way, more difficult
> to
> administrate.
> How do we see these limits? If so, we can scale to another instance and
> controll them!
> Plese, if anyone has any article or "better pratical", we appreciate!
> Numbers is the focus. We need numbers resgiters like x distrib.exe y
> logread.
> exe and thus for ahead.
> Thanks!
> Krisnamourt
> --
> Message posted via http://www.droptable.com
>
|||Thanks Hilary,
This article below show us some limitation from sql 2000 about log reader
threads. I did some research and didn't find any limitation about sql 2005.
So, is it true 100 log readers or what is the limitation of this agent?
http://support.microsoft.com/kb/306457/en-us
Hilary Cotter wrote:[vbcol=seagreen]
>select logreader=sum(case when program_name like '%logreader%'then 1 else 0
>end),distrib=sum(case when program_name like '%'+rtrim(@.@.servername)+'%-%'
>then 1 else 0 end) from sys.sysprocesses
>[quoted text clipped - 22 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200712/1
|||I am unaware of any such limitation for Standard and Enterprise Editions.
Note that in the SQL 2000 article it refers to the number of simultaneous
agents which could be simultaneously running, its not necessarily a hard
limit as to the number of log reader agents/distribution agents that can be
on your system.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7c2e263b3461b@.uwe...
> Thanks Hilary,
> This article below show us some limitation from sql 2000 about log reader
> threads. I did some research and didn't find any limitation about sql
> 2005.
> So, is it true 100 log readers or what is the limitation of this agent?
> http://support.microsoft.com/kb/306457/en-us
> Hilary Cotter wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200712/1
>

AgentMail Problem (SQL2K)

Hi,
I use Microsoft outlook as MAPI client. On my own computer, when the outlook
is open, both SQL Mail and Agent Mail can use the file of mail profile to
send email. But on one of client's computer, when outlook is open, it locks
the file of profile and both SQL Mail and Agent Mail fail when try to
connect to profile. When I close outlook, they can access profile. This is
strange behavior that I had never seen elsewhere.
Any help would be greatly appreciated.
LeilaI believe this behavior can depend on your version of Outlook (I prefer Outlook 2000, I've seen the
strange behavior with later versions). It might also be related to whether all users use the same
Windows account.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use Microsoft outlook as MAPI client. On my own computer, when the outlook is open, both SQL
> Mail and Agent Mail can use the file of mail profile to send email. But on one of client's
> computer, when outlook is open, it locks the file of profile and both SQL Mail and Agent Mail fail
> when try to connect to profile. When I close outlook, they can access profile. This is strange
> behavior that I had never seen elsewhere.
> Any help would be greatly appreciated.
> Leila
>|||> It might also be related to whether all users use the same Windows
> account.
If this is the case, then closing the outlook must not solve the problem I
think
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234lZSjNjHHA.680@.TK2MSFTNGP06.phx.gbl...
>I believe this behavior can depend on your version of Outlook (I prefer
>Outlook 2000, I've seen the strange behavior with later versions). It might
>also be related to whether all users use the same Windows account.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I use Microsoft outlook as MAPI client. On my own computer, when the
>> outlook is open, both SQL Mail and Agent Mail can use the file of mail
>> profile to send email. But on one of client's computer, when outlook is
>> open, it locks the file of profile and both SQL Mail and Agent Mail fail
>> when try to connect to profile. When I close outlook, they can access
>> profile. This is strange behavior that I had never seen elsewhere.
>> Any help would be greatly appreciated.
>> Leila
>|||I think that some versions of MAPI (Outlook) doesn't like several Windows accounts using mail at the
same time. Say that Agent and SQL Server uses the same service account. They might not conflict, but
if you are logged in interactively using some other account, you might be in for some problems.
Anyhow, I recommend Outlook 2000. Or actually, I don't recommend SQL Mail at all
(http://www.karaszi.com/SQLServer/info_no_mapi.asp), since it causes so many of these problems...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Leila" <Leilas@.hotpop.com> wrote in message news:OLQuVxOjHHA.2552@.TK2MSFTNGP06.phx.gbl...
>> It might also be related to whether all users use the same Windows account.
> If this is the case, then closing the outlook must not solve the problem I think
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%234lZSjNjHHA.680@.TK2MSFTNGP06.phx.gbl...
>>I believe this behavior can depend on your version of Outlook (I prefer Outlook 2000, I've seen
>>the strange behavior with later versions). It might also be related to whether all users use the
>>same Windows account.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Leila" <Leilas@.hotpop.com> wrote in message news:uMzUEcNjHHA.4872@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I use Microsoft outlook as MAPI client. On my own computer, when the outlook is open, both SQL
>> Mail and Agent Mail can use the file of mail profile to send email. But on one of client's
>> computer, when outlook is open, it locks the file of profile and both SQL Mail and Agent Mail
>> fail when try to connect to profile. When I close outlook, they can access profile. This is
>> strange behavior that I had never seen elsewhere.
>> Any help would be greatly appreciated.
>> Leila
>>
>

Agent will not start

We are moving our SQL Server 2000 databse to a new server. After I
installed it and copied the data over, I found that SQL Server Agent will
not start. I get an Access is denied... message.
The only difference that I know of is that the new server is running Windows
2003 Advanced server and the old serve is not.
Any suggestions?
TIA
MikeIf you are using a system account to log in the service,
change it to a local or domain account that you know the
password for. If you are using a local or domain account,
set the service to system account (stop and restart the
service) and then re-enter your domain/local account with
the correct password. >--Original Message--
>We are moving our SQL Server 2000 databse to a new
server. After I
>installed it and copied the data over, I found that SQL
Server Agent will
>not start. I get an Access is denied... message.
>The only difference that I know of is that the new server
is running Windows
>2003 Advanced server and the old serve is not.
>Any suggestions?
>TIA
>Mike
>
>.
>|||Was this a brand new install? Pls check your sqlagent.out located in the
LOG folder of your SQL Server installation directory for more information
on the error mesg? the sQL Error logs will help as well.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Agent will not start

Agent stops and won't restart

Various sites/servers this is happening.
Start Server AGent
Schedule a DTS to run nightly.
Come back a few days later
SQL SA is stopped
the job never ran
When this first happened I checked the props of SQLSA
and found the "Auto - Restart SQL Server on failure" and "A-R SQL SA..." check boxes.
Checked them.
Come back a few days later. Same results
When I view the jobs in SQL SA- Jobs area those jobs failed for the inimitable
sqlstate 42000 error 8198 "... if the owner
(server\administrator) of job Nightly Update has server access (reason: Could
not obtain information about ... 'server\administrator'.
Yes, the adminitrator. Now I've changed the owner of the jobs to
sa. I'm afraid to presume that sa is powerful enough to stand up to SQL authentication if the admin can't (smirk)
Does anyone have any insights? would the 8198 cause SA to stop? Why would a subordinate job stop SA?
if the sub-job can stop it why doesn't it restart as told to in the properties?
thanks, Bryan
Hi Bryan,
An 8198 error is normally raised when process executed within SQL Server or
from the SQL Server Agent, such as the "xp_logininfo" stored procedure, a
scheduled job, or a replication agent, needs to verify the credentials of a
Windows-authenticated login. The attempt to retrieve those credentials on
the domain failed for an unspecified reason
Please take a look at article
http://support.microsoft.com/default...b;en-us;883551 that talks
about some of the reasons for the 8198 error that you are getting.
Hope that helps !!!
Fargham

Agent started but not started?

I have a SQL 2005 server running on a win2k3 box. For some reason, no
matter what account the SQL Server Agent runs under, error messages
are logged in the security logs. Even if the login account is set to
"local service". BUT if I look at the service under "services", it
says started. It also says started in the SQL 2005 configuration
manager.
Has anyone seen this before?
Thanks
Hello,
Can you create one test job and try executing. If the job runs then SQL
Agent is working fine...also see the SQLAgent.out file in Log folder of SQL
Server
installation folder for any errors
Thanks
Hari
<costello_joe@.hotmail.com> wrote in message
news:1180399298.618353.292290@.q19g2000prn.googlegr oups.com...
> I have a SQL 2005 server running on a win2k3 box. For some reason, no
> matter what account the SQL Server Agent runs under, error messages
> are logged in the security logs. Even if the login account is set to
> "local service". BUT if I look at the service under "services", it
> says started. It also says started in the SQL 2005 configuration
> manager.
> Has anyone seen this before?
> Thanks
>

Agent started but not started?

I have a SQL 2005 server running on a win2k3 box. For some reason, no
matter what account the SQL Server Agent runs under, error messages
are logged in the security logs. Even if the login account is set to
"local service". BUT if I look at the service under "services", it
says started. It also says started in the SQL 2005 configuration
manager.
Has anyone seen this before?
ThanksHello,
Can you create one test job and try executing. If the job runs then SQL
Agent is working fine...also see the SQLAgent.out file in Log folder of SQL
Server
installation folder for any errors
Thanks
Hari
<costello_joe@.hotmail.com> wrote in message
news:1180399298.618353.292290@.q19g2000prn.googlegroups.com...
> I have a SQL 2005 server running on a win2k3 box. For some reason, no
> matter what account the SQL Server Agent runs under, error messages
> are logged in the security logs. Even if the login account is set to
> "local service". BUT if I look at the service under "services", it
> says started. It also says started in the SQL 2005 configuration
> manager.
> Has anyone seen this before?
> Thanks
>

Agent started but not started?

I have a SQL 2005 server running on a win2k3 box. For some reason, no
matter what account the SQL Server Agent runs under, error messages
are logged in the security logs. Even if the login account is set to
"local service". BUT if I look at the service under "services", it
says started. It also says started in the SQL 2005 configuration
manager.
Has anyone seen this before?
ThanksHello,
Can you create one test job and try executing. If the job runs then SQL
Agent is working fine...also see the SQLAgent.out file in Log folder of SQL
Server
installation folder for any errors
Thanks
Hari
<costello_joe@.hotmail.com> wrote in message
news:1180399298.618353.292290@.q19g2000prn.googlegroups.com...
> I have a SQL 2005 server running on a win2k3 box. For some reason, no
> matter what account the SQL Server Agent runs under, error messages
> are logged in the security logs. Even if the login account is set to
> "local service". BUT if I look at the service under "services", it
> says started. It also says started in the SQL 2005 configuration
> manager.
> Has anyone seen this before?
> Thanks
>

Agent services

There is no SQL Server Agent with SQL Server Express. Not
sure what you were looking to do but you can often use the
Windows Scheduler and sqlcmd scripts to accomplish a lot of
things you would have done in Agent.
-Sue
On Wed, 21 Mar 2007 20:24:42 +0100, "Ana" <nospam@.yahoo.com>
wrote:

>Hi,
>How to activate the Agent on SQLExpress05?
>TIA
>Ana
>
>----
>Estoy utilizando la versin gratuita de SPAMfighter para usuarios privados.
>Ha eliminado 3003 correos spam hasta la fecha.
>Los abonados no tienen este mensaje en sus correos.
>Pruebe SPAMfighter gratis ya!
>
To add on to Sue, take a look into below links to backup the SQL Express
databases:-
http://www.sqldbatips.com/showarticle.asp?ID=27
http://www.sqldbatips.com/showarticle.asp?ID=29
Thanks
Hari
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:dmj303ls7b691rdqqhm205glau580q6ego@.4ax.com...
> There is no SQL Server Agent with SQL Server Express. Not
> sure what you were looking to do but you can often use the
> Windows Scheduler and sqlcmd scripts to accomplish a lot of
> things you would have done in Agent.
> -Sue
> On Wed, 21 Mar 2007 20:24:42 +0100, "Ana" <nospam@.yahoo.com>
> wrote:
>

Agent Service Fails to Start

For some time now, I've been unable to start SQL Server
Agent service. I can run agent in cmd window via:
sqlagent.exe -c -v. The error on failed startup is
something like, sqlagent failed to start in a timely
manner.Have you tried starting the agent from Service Manager? If so, what error
message do you get? What user account does the Agent run with? Does the
account belong to a Win2K domain or the local machine?
The answers to these questions will help to diagnose the problem.
"Clark" <anonymous@.discussions.microsoft.com> wrote in message
news:4a2e01c3ab7d$eebde570$a601280a@.phx.gbl...
> For some time now, I've been unable to start SQL Server
> Agent service. I can run agent in cmd window via:
> sqlagent.exe -c -v. The error on failed startup is
> something like, sqlagent failed to start in a timely
> manner.|||Oh yes, I've tried that.
1. With Service Manager, it fails to start with something
like, sqlagent failed to start in a timely manner.
2. Agent uses the sama account as SQL Server service.
I plan to dink with this on Monday morning.
Question: Can the time to load a service be extended,
for example, to load a service on a very busy server?
>--Original Message--
>Have you tried starting the agent from Service Manager?
If so, what error
>message do you get? What user account does the Agent run
with? Does the
>account belong to a Win2K domain or the local machine?
>The answers to these questions will help to diagnose the
problem.
>
>"Clark" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4a2e01c3ab7d$eebde570$a601280a@.phx.gbl...
>> For some time now, I've been unable to start SQL Server
>> Agent service. I can run agent in cmd window via:
>> sqlagent.exe -c -v. The error on failed startup is
>> something like, sqlagent failed to start in a timely
>> manner.
>
>.
>|||I am having a similar problem too. I am trying to start the service from
Enterprise Manager and get the following error:
A error 5 - (Access is denied) occured while performing the service opeation
on the SQL ServerAgent service
I tried changeing the account info to the SA login and password but to no
avail. Is it possibel that the service is needs the machine domain Admin
account info?
John.
"Clark" <anonymous@.discussions.microsoft.com> wrote in message
news:505301c3ac4e$be0c0070$a601280a@.phx.gbl...
> Oh yes, I've tried that.
> 1. With Service Manager, it fails to start with something
> like, sqlagent failed to start in a timely manner.
> 2. Agent uses the sama account as SQL Server service.
> I plan to dink with this on Monday morning.
> Question: Can the time to load a service be extended,
> for example, to load a service on a very busy server?
>
> >--Original Message--
> >Have you tried starting the agent from Service Manager?
> If so, what error
> >message do you get? What user account does the Agent run
> with? Does the
> >account belong to a Win2K domain or the local machine?
> >
> >The answers to these questions will help to diagnose the
> problem.
> >
> >
> >"Clark" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:4a2e01c3ab7d$eebde570$a601280a@.phx.gbl...
> >> For some time now, I've been unable to start SQL Server
> >> Agent service. I can run agent in cmd window via:
> >> sqlagent.exe -c -v. The error on failed startup is
> >> something like, sqlagent failed to start in a timely
> >> manner.
> >
> >
> >.
> >|||Hi John,
Sa login is for SQL server authentication, which is different from the account used for SQL server agent service. To run sql server agent, one must typically have a domain user account (as it is used for sql mail, replication, backup etc). Also this domain name should have administrative privs on the machine where sql server instance is running.
Sometimes I guess rebuilding registry can solve the problem. Use "regrebld" in the cmd line prompt. I haven't used this utility myself, so inquire with others before using it
Thank
GYK|||Thanks for you help.
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:CFD08193-68CB-48CA-A931-04BBA0EC21A2@.microsoft.com...
> Hi John,
> Sa login is for SQL server authentication, which is different from the
account used for SQL server agent service. To run sql server agent, one must
typically have a domain user account (as it is used for sql mail,
replication, backup etc). Also this domain name should have administrative
privs on the machine where sql server instance is running.
> Sometimes I guess rebuilding registry can solve the problem. Use
"regrebld" in the cmd line prompt. I haven't used this utility myself, so
inquire with others before using it.
> Thanks
> GYK

Agent Scheduling Confusion....

I'm a little confused.
I've been working on setting up one snapshot publication for one DB, and one
"one-way" transactional publication for another DB (both on the same
server).
My three "client" subscribers are running MSDE 2000 SP3, and they are
subscribing to these to Publishers.
As I have been in the process of setting these up, I'm having difficulty
understanding how all the "scheduling" works.
I mean there's a schedule for the "snapshot" agent. a schedule for the
"transactional" agent, a schedule for the "distribution" agent, a schedule
for the "subscriber" agent, ... and so on.
For my scenario, I need to know how to configure all the schedulers
(publisher and subscribers) so that when the "client" subscriber is
connected to the network, it automatically checks to see if there is an
updated snapshot, and see if there are any "transactional" transactions
published at the publisher. I want the publisher to publish the "snapshot"
every day at 3:00 AM, and I want the "transactional" publication to always
be "up-to-date".
Any ideas on where to get the most useful information about all the
"scheduling", or should I buy a book on replication

I've become pretty much adept at the actual setting up of all the security,
folders, etc. I'm just real unsure about the scheduling of the different
agents...
Thanks
-- Will G.
When you want the subscriber to determine when to connect to the publisher
and download updates you should be using a pull subscription.
Either that or use a push subscription and schedule your distribution agent
to run every 1 minute and have it fail continually until the subscriber
connects. The first option is the better way to go.
You can schedule your pull agent to run continuously. You might want to
configure your subscription to be managed by Windows Synchronization Manager
for a greater range of options on when to pull your subscription, for
instance it can pull your subscription on logon, when the network connection
is live, etc.
If you schedule your snapshot to run every morning at 3:00 it will only be
generated the first time, and subsequent times when a subscription needs
reinitialization, ie you made a schema change or the subscription expired.
You are still probably confused. Post back with any remaining or new
questions you might have, and someone should pick them up and answer them.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Will Gillen" <gille001@.nsuok.edu> wrote in message
news:ODw6zwevEHA.3080@.TK2MSFTNGP12.phx.gbl...
> I'm a little confused.
> I've been working on setting up one snapshot publication for one DB, and
one
> "one-way" transactional publication for another DB (both on the same
> server).
> My three "client" subscribers are running MSDE 2000 SP3, and they are
> subscribing to these to Publishers.
> As I have been in the process of setting these up, I'm having difficulty
> understanding how all the "scheduling" works.
> I mean there's a schedule for the "snapshot" agent. a schedule for the
> "transactional" agent, a schedule for the "distribution" agent, a schedule
> for the "subscriber" agent, ... and so on.
> For my scenario, I need to know how to configure all the schedulers
> (publisher and subscribers) so that when the "client" subscriber is
> connected to the network, it automatically checks to see if there is an
> updated snapshot, and see if there are any "transactional" transactions
> published at the publisher. I want the publisher to publish the
"snapshot"
> every day at 3:00 AM, and I want the "transactional" publication to always
> be "up-to-date".
> Any ideas on where to get the most useful information about all the
> "scheduling", or should I buy a book on replication

> I've become pretty much adept at the actual setting up of all the
security,
> folders, etc. I'm just real unsure about the scheduling of the different
> agents...
> Thanks
> -- Will G.
>
>

Agent Query

Is there any way to query whether the SQL Agent is running i.e. from within a
scripted query. I support about 30 msde replicated databases running on
different servers across a wan and often the damn sql agent does not start up
after a reboot. I want to write a sql script that will query whether the
Agent is running, without having to use the Enterprise Manager interface.
hi,
"Surfgate" <Surfgate@.discussions.microsoft.com> ha scritto nel messaggio
news:D3799708-5CFD-4E9D-8D3E-049160E65AD8@.microsoft.com...
> Is there any way to query whether the SQL Agent is running i.e. from
within a
> scripted query. I support about 30 msde replicated databases running on
> different servers across a wan and often the damn sql agent does not start
up
> after a reboot. I want to write a sql script that will query whether the
> Agent is running, without having to use the Enterprise Manager interface.
actually it's not an activity I know how to do with Transact-SQL... but you
can perhaps use SQL-DMO in a vbs file querying the
SQLServerObject.JobServer.Status for a 1 value (SQLDMOSvc_Running), but this
requires an extra SQL-DMO connection to all servers...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

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 not running?

Hi I am trying to run na backup and when I run the job manually I get
an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently
not running and cannot be notified. In the event log I get these two,
but the agent is definitely running. Any ideas?
Thanks
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
You do not have sufficient operating system permission to open the
SQLServerAgent status event.
Event Type: Error
Event Source: SQLSERVERAGENT
Event Category: Alert Engine
The data portion of event 17052 from MSSQLSERVER is invalid.Starting the Agent service?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1162561943.730620.280980@.h48g2000cwc.googlegroups.com...
> Hi I am trying to run na backup and when I run the job manually I get
> an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently
> not running and cannot be notified. In the event log I get these two,
> but the agent is definitely running. Any ideas?
> Thanks
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> You do not have sufficient operating system permission to open the
> SQLServerAgent status event.
>
> Event Type: Error
> Event Source: SQLSERVERAGENT
> Event Category: Alert Engine
> The data portion of event 17052 from MSSQLSERVER is invalid.
>|||Well the agent has the Green arrow on the icon in EM and when I right
click it, Start is greyed out and Stop is not. In services
SQLSERVERAGENT has the status of started, so Im confused as to why Im
getting this error.
Tibor Karaszi wrote:
> Starting the Agent service?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> news:1162561943.730620.280980@.h48g2000cwc.googlegroups.com...
> > Hi I am trying to run na backup and when I run the job manually I get
> > an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently
> > not running and cannot be notified. In the event log I get these two,
> > but the agent is definitely running. Any ideas?
> >
> > Thanks
> >
> > Event Type: Error
> > Event Source: MSSQLSERVER
> > Event Category: (2)
> > Event ID: 17052
> > You do not have sufficient operating system permission to open the
> > SQLServerAgent status event.
> >
> >
> > Event Type: Error
> > Event Source: SQLSERVERAGENT
> > Event Category: Alert Engine
> > The data portion of event 17052 from MSSQLSERVER is invalid.
> >|||Did you check Agent for the correct instance? Perhaps you have several instances of SQL Server
installed?
Assuming you did, I'd check SQL Server agent's error log for errors as the next step.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1162833283.408183.293470@.h54g2000cwb.googlegroups.com...
> Well the agent has the Green arrow on the icon in EM and when I right
> click it, Start is greyed out and Stop is not. In services
> SQLSERVERAGENT has the status of started, so Im confused as to why Im
> getting this error.
>
> Tibor Karaszi wrote:
>> Starting the Agent service?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
>> news:1162561943.730620.280980@.h48g2000cwc.googlegroups.com...
>> > Hi I am trying to run na backup and when I run the job manually I get
>> > an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently
>> > not running and cannot be notified. In the event log I get these two,
>> > but the agent is definitely running. Any ideas?
>> >
>> > Thanks
>> >
>> > Event Type: Error
>> > Event Source: MSSQLSERVER
>> > Event Category: (2)
>> > Event ID: 17052
>> > You do not have sufficient operating system permission to open the
>> > SQLServerAgent status event.
>> >
>> >
>> > Event Type: Error
>> > Event Source: SQLSERVERAGENT
>> > Event Category: Alert Engine
>> > The data portion of event 17052 from MSSQLSERVER is invalid.
>> >
>|||Only 1 instance running. It does work on the schedule when not looged
in though, its only when I manually run the job from EM it fails.Will
check the agent error log
Tibor Karaszi wrote:
> Did you check Agent for the correct instance? Perhaps you have several instances of SQL Server
> installed?
> Assuming you did, I'd check SQL Server agent's error log for errors as the next step.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> news:1162833283.408183.293470@.h54g2000cwb.googlegroups.com...
> > Well the agent has the Green arrow on the icon in EM and when I right
> > click it, Start is greyed out and Stop is not. In services
> > SQLSERVERAGENT has the status of started, so Im confused as to why Im
> > getting this error.
> >
> >
> >
> > Tibor Karaszi wrote:
> >> Starting the Agent service?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> >> news:1162561943.730620.280980@.h48g2000cwc.googlegroups.com...
> >> > Hi I am trying to run na backup and when I run the job manually I get
> >> > an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently
> >> > not running and cannot be notified. In the event log I get these two,
> >> > but the agent is definitely running. Any ideas?
> >> >
> >> > Thanks
> >> >
> >> > Event Type: Error
> >> > Event Source: MSSQLSERVER
> >> > Event Category: (2)
> >> > Event ID: 17052
> >> > You do not have sufficient operating system permission to open the
> >> > SQLServerAgent status event.
> >> >
> >> >
> >> > Event Type: Error
> >> > Event Source: SQLSERVERAGENT
> >> > Event Category: Alert Engine
> >> > The data portion of event 17052 from MSSQLSERVER is invalid.
> >> >
> >|||Hi Can anyone suggest what might be goig wrong here?
I have 4 backup jobs, 1 old one and 3 new-ish. All the settings are the
same on the 3 new jobs, the only difference is the time they run. All
fail when manually run with the errors below, the old one still
succeeds on the schedule when no one is logged in.
The strange thing is that the one that succeeds on the schedule is
actually now running at a different time than is defined, yesterday I
changed it from 00:00 to 03:00 and changed the schedule name from
"Schedule 1" to "3AM" yet it still runs at 00:00 and reports "Schedule
1" I also changed one of the step names yet it still reports the old
step name.
It seems the changes being made in EM are not taking effect, and that
SQL Server cannot see that the agent is running.
I have not had this problem before on other SQL 2000 installs, the only
diference is that I installed this folling best practices and set up
sqlservice and sqlagent on diferent windows accounts. ALl jobs login as
Administrator , I have tried changing thois the SQLAgent & SQLService
but it makes no difference.
The only thing thatr has changed on the server is windows updates.
The version is 8.00.2039 Standard Edition on NT5.2 SP1
Thanks
hals_left wrote:
> Only 1 instance running. It does work on the schedule when not looged
> in though, its only when I manually run the job from EM it fails.Will
> check the agent error log
>
> Tibor Karaszi wrote:
> > Did you check Agent for the correct instance? Perhaps you have several instances of SQL Server
> > installed?
> >
> > Assuming you did, I'd check SQL Server agent's error log for errors as the next step.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> > news:1162833283.408183.293470@.h54g2000cwb.googlegroups.com...
> > > Well the agent has the Green arrow on the icon in EM and when I right
> > > click it, Start is greyed out and Stop is not. In services
> > > SQLSERVERAGENT has the status of started, so Im confused as to why Im
> > > getting this error.
> > >
> > >
> > >
> > > Tibor Karaszi wrote:
> > >> Starting the Agent service?
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >>
> > >>
> > >> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> > >> news:1162561943.730620.280980@.h48g2000cwc.googlegroups.com...
> > >> > Hi I am trying to run na backup and when I run the job manually I get
> > >> > an error SQL-DMO State 42000, Error 200225 SQLServerAgent is currently
> > >> > not running and cannot be notified. In the event log I get these two,
> > >> > but the agent is definitely running. Any ideas?
> > >> >
> > >> > Thanks
> > >> >
> > >> > Event Type: Error
> > >> > Event Source: MSSQLSERVER
> > >> > Event Category: (2)
> > >> > Event ID: 17052
> > >> > You do not have sufficient operating system permission to open the
> > >> > SQLServerAgent status event.
> > >> >
> > >> >
> > >> > Event Type: Error
> > >> > Event Source: SQLSERVERAGENT
> > >> > Event Category: Alert Engine
> > >> > The data portion of event 17052 from MSSQLSERVER is invalid.
> > >> >
> > >

Saturday, February 25, 2012

Aggregate (SUM) a column and then use the Result?

Aggregate - Sum with group by

Aggravating problem with the Dataset Field Refresh

Aggragate transform with "WHERE" condition

Aggr

AggMerge_ .TMP files eat my harddisk

aggegrate component

Agg op question

Agents not appearing in Replication Monitor (SQL 2000)

Agents failed to load

agents db rights for trans. repl

agents blocking each other

Agents and thread limits from SQL 2005 Distributor machine

AgentMail Problem (SQL2K)

AgentMail Problem (SQL2K)

AgentMail Problem (SQL2K)

Agent will not start

Agent will not start

Agent will not start

Agent stops and won't restart

Agent started but not started?

Agent started but not started?

Agent started but not started?

Agent services

Agent Service Fails to Start

Agent Scheduling Confusion....

Agent Query

Agent Proxy accounts not listed

Agent not running?

Agent not running?

Agent not running?

SQL tool for MSDE

Blog Archive

About Me