Linear equations?

Is there a way to solve a system of linear equations in Analysis Services?
Let's say I have a table with two columns for all the coefficients:
C12 - 0.30
C13 - 0.70
C21 - 0.15
C23 - 0.10
....
Is there something in Analysis Services that can do this?Can you give a bit more detail on what you are trying to achieve?|||I am trying to calculate cost allocations for budgeting, based on internal costs and a grid of distribution percentages. It comes down to solving a system of linear equations. It would have been great if I can create a report where this system is solved internally everytime the report is run.|||

There are a set of MDX functions for handling linear regression problems. It should be possible for you to use them to define one or more calculated members that take the output of one or more of the functions and calculate the cost allocations. If you look in Books Online at the MDX functions list, you'll see LinRegIntercept, LinRegPoint, LinRegSlope, LinRegVariance, and LinRegR2...

Dave Fackler

|||

And if you want to see an example of how these LinReg functions can be applied - you can check the following article: http://www.mosha.com/msolap/articles/mdxlinreg.htm

Line Chart Empty

I have a dataset that can be viewed as columns chart. But when I change to
line chart, the data is shown only partly, or not shown at all.
I noticed that if I have one set of data that covers the full lenght of
x-axis, and another set of data that covers only the right side of x-axis,
then what you will see will be the start of the line representing the first
set, then it stops completely and another line starts representing the second
set.
Anybody knows why it happends and how to fix it?
D>Are you using the RTM version of RS 2000, and SP1 or SP2 is not installed?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"uiwork" <uiwork@.discussions.microsoft.com> wrote in message
news:004E3B42-5CBE-4590-BC7D-28D15B266131@.microsoft.com...
>I have a dataset that can be viewed as columns chart. But when I change to
> line chart, the data is shown only partly, or not shown at all.
> I noticed that if I have one set of data that covers the full lenght of
> x-axis, and another set of data that covers only the right side of x-axis,
> then what you will see will be the start of the line representing the
> first
> set, then it stops completely and another line starts representing the
> second
> set.
> Anybody knows why it happends and how to fix it?
> D>|||It is RS 2000, and I have only SP1 installed. Is it a problem?
"Robert Bruckner [MSFT]" wrote:
> Are you using the RTM version of RS 2000, and SP1 or SP2 is not installed?
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "uiwork" <uiwork@.discussions.microsoft.com> wrote in message
> news:004E3B42-5CBE-4590-BC7D-28D15B266131@.microsoft.com...
> >I have a dataset that can be viewed as columns chart. But when I change to
> > line chart, the data is shown only partly, or not shown at all.
> > I noticed that if I have one set of data that covers the full lenght of
> > x-axis, and another set of data that covers only the right side of x-axis,
> > then what you will see will be the start of the line representing the
> > first
> > set, then it stops completely and another line starts representing the
> > second
> > set.
> > Anybody knows why it happends and how to fix it?
> > D>
>
>|||Yes, you are running into a known issue with line charts using multiple data
series and the datapoints having interleaved category (x-axis) values. The
fix is available in RS 2000 SP2.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"uiwork" <uiwork@.discussions.microsoft.com> wrote in message
news:991368D7-2E58-4566-AB34-68027D9FC856@.microsoft.com...
> It is RS 2000, and I have only SP1 installed. Is it a problem?
> "Robert Bruckner [MSFT]" wrote:
>> Are you using the RTM version of RS 2000, and SP1 or SP2 is not
>> installed?
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "uiwork" <uiwork@.discussions.microsoft.com> wrote in message
>> news:004E3B42-5CBE-4590-BC7D-28D15B266131@.microsoft.com...
>> >I have a dataset that can be viewed as columns chart. But when I change
>> >to
>> > line chart, the data is shown only partly, or not shown at all.
>> > I noticed that if I have one set of data that covers the full lenght of
>> > x-axis, and another set of data that covers only the right side of
>> > x-axis,
>> > then what you will see will be the start of the line representing the
>> > first
>> > set, then it stops completely and another line starts representing the
>> > second
>> > set.
>> > Anybody knows why it happends and how to fix it?
>> > D>
>>

Line between page columns?

Hi all,

My report's body section is split into 2 columns. I need to be able to add a vertical line to the dividing point of these columns so that it is visually more recognisable. How can I do this?

Thank you,

Stephen.

Any ideas? I am having real trouble with this.

Stephen.

Limiting Matrix Columns

I am dealing with a report which must display records in columns. The number of columns is dynamic as is the number of fields (rows) which will be displayed.
For example, my data is structured as follows:
ID Name Price State Category etc...
1 Widget $100 AL Tools
2 Gadget $45 NY Accessories
3 Thingy $12.50 TX Tools
etc.
.
.
In order to convert this to a matrix, I have queried the data to return in the following format:
ID Attribute Value
1 Name Widget
1 Price $100
1 State AL
1 Category Tools
2 Name Gadget
2 Price $45
2 State NY
2 Category Accessories
3 Name Thingy
3 Price $12.50
3 State TX
3 Category Tools
etc...
So, I create a matrix in my report with 1 column and 1 row. The column is grouped on ID. The row is grouped on Attribute. The detail displays the Value. Giving me:
1 2 3 etc...
Name Widget Gadget Thingy
Price $100 $45 $12.50
State AL NY TX
Category Tools Accessories Tools
etc...
This all works great and is very simple. The problem stems from the fact that I only want to display a limited number of columns on each page. Once 8 columns are displayed, I would like the matrix to skip to a new page, which redisplays my header. Unfortunately, the matrix just keeps expanding to the right and never wraps. Is there any way to work around this? Has anyone found a way to force a matrix to wrap and repeat the headers?
Thanks

Hello:

I have just sstayed away from Matrix'es in that I have no control. I have not found a way around your above example - maybe someone else knows but I sure don't - so I have just stayed with simple lists and have manipulated the data via a stored procedure with a "crosstab" or "pivot table" to generate the data (with respective columns) that I want and then within RS just layout the report visibility similar to a matrix.

A different subject for a different day but the same occurs within Analysis Services in that the horizontal presentation just extrapolates itself forever based upon the dimensions and columns - maybe I did not say that corectly but the same thing occurs.
So here is a nice little "generic" SQL that will allow you to present your row data as column data. A side Note - that is the ONLY issue I have with Reporting Services in that all data is always represented as a ROW - so this Stored Procedure will definitely help you control what you want to present.
There are some initial definitions you should deploy such as storing these parameters for each report within a table in SQL and if you want I can help you - but nevertheless here is the code and I rename this Stored Procedure for the name of my Reporting Services Report.
CREATE procedure Ex_CrossTab or whatever name you want (@.SQL varchar(2500),

@.PivotCol varchar(100),

@.Summaries varchar(100),

@.GroupBy varchar(100),

@.OtherFields varchar(150) = Null,

@.Debug bit = 0)

AS

set nocount on

set ansi_warnings off

declare @.Vals varchar(8000);

declare @.Vals2 varchar(8000);

declare @.Vals3 varchar(8000);

declare @.tmp varchar(1000);

declare @.TotalLen int;

set @.Vals = '';

set @.Vals2 = '';

set @.Vals3 = '';

set @.TotalLen = len(@.SQL) + len(@.GroupBy) + Len(ISNULL(@.OtherFields,''))

set @.OtherFields = isNull(', ' + @.OtherFields ,'')

create table #temp (Pivot varchar(100))

insert into #temp

exec ('select distinct convert(varchar(100),' + @.PivotCol + ') as Pivot FROM (' + @.SQL + ') A')

select @.tmp =

replace(replace(@.Summaries,'(','(CASE WHEN ' + @.PivotCol + '=''' + replace(Pivot,'''',''') +

''' THEN '),')[', ' END) as [' + Pivot ),

@.TotalLen = @.TotalLen + Len(@.tmp),

@.Vals = case when @.TotalLen < 7800 then @.Vals + ', ' + @.tmp else @.Vals end,

@.Vals2 = case when @.TotalLen between 7800 and 15799 then @.Vals2 + ', ' + @.tmp else @.Vals2 end,

@.Vals3 = case when @.TotalLen between 15800 and 23799 then @.Vals3 + ', ' + @.tmp else @.Vals3 end

from
#Temp
order by
Pivot

drop table #Temp

if (@.Debug=0)

exec ( 'select ' + @.GroupBy + @.OtherFields + @.Vals + @.Vals2 +

@.Vals3 + ' from (' + @.SQL + ') A GROUP BY ' + @.GroupBy)

else

begin

create table #Temp2 (SQLText Text);

insert into #Temp2 (SQLText)

values ('select ' + @.GroupBy + @.OtherFields + @.Vals + @.Vals2 +

@.Vals3 + ' from (' + @.SQL + ') A GROUP BY ' + @.GroupBy);

select * from #Temp2

end

set nocount off
set ansi_warnings on
GO

The above code executes your SQL statement as Dynamic SQL so make sure that you get rid of all blanks within your SQL because it is passed as a parameter to the above stored procedure. So use Query Analyzer and once again eliminate all "white" space and then pass your SQL statement as a parameter to the above stored procedure. Based upon your SQL Select you can eliminate the data that is returned thus "eliminating" your columns from extroploating within Reporting Services.
Definitions on how to use the above stored procedure are below:


Select - This is the SQL statement or table you are querying. It can be any valid SELECT statement, with JOINS, WHERE clauses, TOP, etc -- anything that you can cut and paste into query analyzer and execute. (And, in fact, you should always do this when testing things out).

Limiting job history in SQL2005

Hi,
Under SQL Agent properties/History there are 2 limiting columns for Limit
size of job history log.
We have Maximum job history set to 12000 and maximum job history rows per
job at 150 but we only seem to be able to keep 2 or 3 days worth of
jobhistory even though looking in sysjobhistory I can only return 7300 rows.
I thought that it would keep a maximum of 150 per job to a total of 12000.
Is something else limiting the data in sysjobhistory?
Thanks
ChrisHi,
We found the problem. If you run a maintenance plan that has a History
Cleanup Task and its run by an admin level account then the
sp_purge_jobhistory does not include a @.job_name parameter. This combination
means that all job history gets cleaned up rather than just the job that has
the History Cleanup Task problem raised on Connect as bug 322320.
Chris
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Under SQL Agent properties/History there are 2 limiting columns for Limit
> size of job history log.
> We have Maximum job history set to 12000 and maximum job history rows per
> job at 150 but we only seem to be able to keep 2 or 3 days worth of
> jobhistory even though looking in sysjobhistory I can only return 7300
> rows.
> I thought that it would keep a maximum of 150 per job to a total of 12000.
> Is something else limiting the data in sysjobhistory?
> Thanks
> Chris
>|||Very interesting Chris. Thanks for the update.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We found the problem. If you run a maintenance plan that has a History
> Cleanup Task and its run by an admin level account then the
> sp_purge_jobhistory does not include a @.job_name parameter. This
> combination means that all job history gets cleaned up rather than just
> the job that has the History Cleanup Task problem raised on Connect as bug
> 322320.
> Chris
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for Limit
>> size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows per
>> job at 150 but we only seem to be able to keep 2 or 3 days worth of
>> jobhistory even though looking in sysjobhistory I can only return 7300
>> rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>|||Unfortunately Andrew I have not seen any reposnse to my Connect post.
Thanks
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
> Very interesting Chris. Thanks for the update.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than just
>> the job that has the History Cleanup Task problem raised on Connect as
>> bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth of
>> jobhistory even though looking in sysjobhistory I can only return 7300
>> rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>|||Actually I just had a look at the sp_purge_jobhistory sp and the bottom line
is that if you don't specify a job ID or name it will delete all history
beyond the date specified. It makes no difference how many rows you have set
in SQL Agent as it does not even check for that. So if you want to keep x
many days worth you need to ensure that the date specified in the Maint Plan
for the history cleanup is far enough out so the history is maintained.
There is no link between the two. The Agent behavior was that way long
before the maint plan had the hist cleanup task and they simply didn't
factor in agents settings when they wrote the task for the maint plans. They
should have have had you specify a minimum # of rows deform they deleted
them by date only.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
> Unfortunately Andrew I have not seen any reposnse to my Connect post.
> Thanks
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than just
>> the job that has the History Cleanup Task problem raised on Connect as
>> bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth of
>> jobhistory even though looking in sysjobhistory I can only return 7300
>> rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>>
>|||Andrew,
That's part of the problem. BOL states that if you are an admin then a
straight sp_purge_jobhistory, without the @.job_name will purge ALL
jobhistory. This is my problem. The maintenance plan does not include the
@.job_name parameter otherwise this might have worked fine. We had to look at
all our jobs that do a cleanup and are run by an admin account to make sure
that we have made the cleanup period long enough.
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
> line is that if you don't specify a job ID or name it will delete all
> history beyond the date specified. It makes no difference how many rows
> you have set in SQL Agent as it does not even check for that. So if you
> want to keep x many days worth you need to ensure that the date specified
> in the Maint Plan for the history cleanup is far enough out so the history
> is maintained. There is no link between the two. The Agent behavior was
> that way long before the maint plan had the hist cleanup task and they
> simply didn't factor in agents settings when they wrote the task for the
> maint plans. They should have have had you specify a minimum # of rows
> deform they deleted them by date only.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than just
>> the job that has the History Cleanup Task problem raised on Connect as
>> bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth
>> of jobhistory even though looking in sysjobhistory I can only return
>> 7300 rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>>
>>
>|||Chris,
That only means that you need it to be in the sa or SQLAgentOperatorRole in
order to modify any jobs that are not you own. Since you don't specify a job
ID it has to assume you need to be in one of those two roles. If not it
throws an exception and does nothing. So if you want to keep a certain
amount of history for a particular job I suggest you create your won
scheduled job that calls sp_purge_jobhistory with the proper Job names and
dates for each. You may need to set up a table with any jobs that need a
date other than the default you pass. It's basically do it for every job by
date or one job at a time by date and job id or name. Unfortunately there is
no in between.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
> Andrew,
> That's part of the problem. BOL states that if you are an admin then a
> straight sp_purge_jobhistory, without the @.job_name will purge ALL
> jobhistory. This is my problem. The maintenance plan does not include the
> @.job_name parameter otherwise this might have worked fine. We had to look
> at all our jobs that do a cleanup and are run by an admin account to make
> sure that we have made the cleanup period long enough.
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
>> line is that if you don't specify a job ID or name it will delete all
>> history beyond the date specified. It makes no difference how many rows
>> you have set in SQL Agent as it does not even check for that. So if you
>> want to keep x many days worth you need to ensure that the date specified
>> in the Maint Plan for the history cleanup is far enough out so the
>> history is maintained. There is no link between the two. The Agent
>> behavior was that way long before the maint plan had the hist cleanup
>> task and they simply didn't factor in agents settings when they wrote the
>> task for the maint plans. They should have have had you specify a minimum
>> # of rows deform they deleted them by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a History
>> Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than
>> just the job that has the History Cleanup Task problem raised on
>> Connect as bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> Under SQL Agent properties/History there are 2 limiting columns for
>> Limit size of job history log.
>> We have Maximum job history set to 12000 and maximum job history rows
>> per job at 150 but we only seem to be able to keep 2 or 3 days worth
>> of jobhistory even though looking in sysjobhistory I can only return
>> 7300 rows.
>> I thought that it would keep a maximum of 150 per job to a total of
>> 12000.
>> Is something else limiting the data in sysjobhistory?
>> Thanks
>> Chris
>>
>>
>>
>|||Thanks again Andrew. MS could always fix the bug and include the @.job_name
in the script they produce.
I don't see us changing our backups as you have to be an admin to run the
xp_delete_file that comes from the Maintenance Cleanup Task (See bug 247463
in Connect).
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eJzpwveYIHA.4272@.TK2MSFTNGP05.phx.gbl...
> Chris,
> That only means that you need it to be in the sa or SQLAgentOperatorRole
> in order to modify any jobs that are not you own. Since you don't specify
> a job ID it has to assume you need to be in one of those two roles. If not
> it throws an exception and does nothing. So if you want to keep a certain
> amount of history for a particular job I suggest you create your won
> scheduled job that calls sp_purge_jobhistory with the proper Job names and
> dates for each. You may need to set up a table with any jobs that need a
> date other than the default you pass. It's basically do it for every job
> by date or one job at a time by date and job id or name. Unfortunately
> there is no in between.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Andrew,
>> That's part of the problem. BOL states that if you are an admin then a
>> straight sp_purge_jobhistory, without the @.job_name will purge ALL
>> jobhistory. This is my problem. The maintenance plan does not include the
>> @.job_name parameter otherwise this might have worked fine. We had to look
>> at all our jobs that do a cleanup and are run by an admin account to make
>> sure that we have made the cleanup period long enough.
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
>> line is that if you don't specify a job ID or name it will delete all
>> history beyond the date specified. It makes no difference how many rows
>> you have set in SQL Agent as it does not even check for that. So if you
>> want to keep x many days worth you need to ensure that the date
>> specified in the Maint Plan for the history cleanup is far enough out so
>> the history is maintained. There is no link between the two. The Agent
>> behavior was that way long before the maint plan had the hist cleanup
>> task and they simply didn't factor in agents settings when they wrote
>> the task for the maint plans. They should have have had you specify a
>> minimum # of rows deform they deleted them by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> We found the problem. If you run a maintenance plan that has a
>> History Cleanup Task and its run by an admin level account then the
>> sp_purge_jobhistory does not include a @.job_name parameter. This
>> combination means that all job history gets cleaned up rather than
>> just the job that has the History Cleanup Task problem raised on
>> Connect as bug 322320.
>> Chris
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Under SQL Agent properties/History there are 2 limiting columns for
>>> Limit size of job history log.
>>> We have Maximum job history set to 12000 and maximum job history
>>> rows per job at 150 but we only seem to be able to keep 2 or 3 days
>>> worth of jobhistory even though looking in sysjobhistory I can only
>>> return 7300 rows.
>>>
>>> I thought that it would keep a maximum of 150 per job to a total of
>>> 12000.
>>>
>>> Is something else limiting the data in sysjobhistory?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>
>>
>>
>>
>|||> Thanks again Andrew. MS could always fix the bug and include the @.job_name
> in the script they produce.
I think you are missing my point. The task in the Maint Plan does not have a
way to specify the job only a date. This isn't a bug it is simply how they
created the task. How do you expect them to specify the job name? Which job
name are they supposed to use? Like I said the task is not in any way shape
or form tied to SQL Agent. It simply deletes rows from MSDB that happen to
be the Agent history. But it knows nothing of the jobs themselves. If it
looped thru each job one by one and specified the name it would still delete
all the rows for each job that were after the date you specified. You end up
with the same result. So this isn't a bug and I am sure they will not fix it
as such. If you want anything other than that behavior you must create it
your self. The Maint Plans are extremely limiting in what they can do and
how they do it. And it is not always clear on what it actually does unless
you trace the plans actions. One of the main reasons why I almost never
recommend using Maint Plans in the first place.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:um0fh8eYIHA.5132@.TK2MSFTNGP02.phx.gbl...
> Thanks again Andrew. MS could always fix the bug and include the @.job_name
> in the script they produce.
> I don't see us changing our backups as you have to be an admin to run the
> xp_delete_file that comes from the Maintenance Cleanup Task (See bug
> 247463 in Connect).
> Chris
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eJzpwveYIHA.4272@.TK2MSFTNGP05.phx.gbl...
>> Chris,
>> That only means that you need it to be in the sa or SQLAgentOperatorRole
>> in order to modify any jobs that are not you own. Since you don't specify
>> a job ID it has to assume you need to be in one of those two roles. If
>> not it throws an exception and does nothing. So if you want to keep a
>> certain amount of history for a particular job I suggest you create your
>> won scheduled job that calls sp_purge_jobhistory with the proper Job
>> names and dates for each. You may need to set up a table with any jobs
>> that need a date other than the default you pass. It's basically do it
>> for every job by date or one job at a time by date and job id or name.
>> Unfortunately there is no in between.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Andrew,
>> That's part of the problem. BOL states that if you are an admin then a
>> straight sp_purge_jobhistory, without the @.job_name will purge ALL
>> jobhistory. This is my problem. The maintenance plan does not include
>> the @.job_name parameter otherwise this might have worked fine. We had to
>> look at all our jobs that do a cleanup and are run by an admin account
>> to make sure that we have made the cleanup period long enough.
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the bottom
>> line is that if you don't specify a job ID or name it will delete all
>> history beyond the date specified. It makes no difference how many rows
>> you have set in SQL Agent as it does not even check for that. So if
>> you want to keep x many days worth you need to ensure that the date
>> specified in the Maint Plan for the history cleanup is far enough out
>> so the history is maintained. There is no link between the two. The
>> Agent behavior was that way long before the maint plan had the hist
>> cleanup task and they simply didn't factor in agents settings when they
>> wrote the task for the maint plans. They should have have had you
>> specify a minimum # of rows deform they deleted them by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>> Very interesting Chris. Thanks for the update.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>>> Hi,
>>>
>>> We found the problem. If you run a maintenance plan that has a
>>> History Cleanup Task and its run by an admin level account then the
>>> sp_purge_jobhistory does not include a @.job_name parameter. This
>>> combination means that all job history gets cleaned up rather than
>>> just the job that has the History Cleanup Task problem raised on
>>> Connect as bug 322320.
>>>
>>> Chris
>>>
>>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Under SQL Agent properties/History there are 2 limiting columns for
>>> Limit size of job history log.
>>> We have Maximum job history set to 12000 and maximum job history
>>> rows per job at 150 but we only seem to be able to keep 2 or 3 days
>>> worth of jobhistory even though looking in sysjobhistory I can only
>>> return 7300 rows.
>>>
>>> I thought that it would keep a maximum of 150 per job to a total of
>>> 12000.
>>>
>>> Is something else limiting the data in sysjobhistory?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>>
>>>
>>
>>
>>
>|||I see your point Andrew. I hadn't thought about how the Maint Plan would use
the Maintenance Cleanup Task. I am not a Visual Studio user so I don't
always see how the toolset gets used in Management Studio.
It definitely sounds as though taking this task out of the maintenance plans
and creating a nightly job could be the best option.
Chris
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23I2X1bfYIHA.4172@.TK2MSFTNGP02.phx.gbl...
>> Thanks again Andrew. MS could always fix the bug and include the
>> @.job_name in the script they produce.
> I think you are missing my point. The task in the Maint Plan does not have
> a way to specify the job only a date. This isn't a bug it is simply how
> they created the task. How do you expect them to specify the job name?
> Which job name are they supposed to use? Like I said the task is not in
> any way shape or form tied to SQL Agent. It simply deletes rows from MSDB
> that happen to be the Agent history. But it knows nothing of the jobs
> themselves. If it looped thru each job one by one and specified the name
> it would still delete all the rows for each job that were after the date
> you specified. You end up with the same result. So this isn't a bug and I
> am sure they will not fix it as such. If you want anything other than that
> behavior you must create it your self. The Maint Plans are extremely
> limiting in what they can do and how they do it. And it is not always
> clear on what it actually does unless you trace the plans actions. One of
> the main reasons why I almost never recommend using Maint Plans in the
> first place.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:um0fh8eYIHA.5132@.TK2MSFTNGP02.phx.gbl...
>> Thanks again Andrew. MS could always fix the bug and include the
>> @.job_name in the script they produce.
>> I don't see us changing our backups as you have to be an admin to run the
>> xp_delete_file that comes from the Maintenance Cleanup Task (See bug
>> 247463 in Connect).
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eJzpwveYIHA.4272@.TK2MSFTNGP05.phx.gbl...
>> Chris,
>> That only means that you need it to be in the sa or SQLAgentOperatorRole
>> in order to modify any jobs that are not you own. Since you don't
>> specify a job ID it has to assume you need to be in one of those two
>> roles. If not it throws an exception and does nothing. So if you want to
>> keep a certain amount of history for a particular job I suggest you
>> create your won scheduled job that calls sp_purge_jobhistory with the
>> proper Job names and dates for each. You may need to set up a table with
>> any jobs that need a date other than the default you pass. It's
>> basically do it for every job by date or one job at a time by date and
>> job id or name. Unfortunately there is no in between.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:%232tTjgeYIHA.3652@.TK2MSFTNGP02.phx.gbl...
>> Andrew,
>> That's part of the problem. BOL states that if you are an admin then a
>> straight sp_purge_jobhistory, without the @.job_name will purge ALL
>> jobhistory. This is my problem. The maintenance plan does not include
>> the @.job_name parameter otherwise this might have worked fine. We had
>> to look at all our jobs that do a cleanup and are run by an admin
>> account to make sure that we have made the cleanup period long enough.
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uW1Q5tdYIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Actually I just had a look at the sp_purge_jobhistory sp and the
>> bottom line is that if you don't specify a job ID or name it will
>> delete all history beyond the date specified. It makes no difference
>> how many rows you have set in SQL Agent as it does not even check for
>> that. So if you want to keep x many days worth you need to ensure
>> that the date specified in the Maint Plan for the history cleanup is
>> far enough out so the history is maintained. There is no link between
>> the two. The Agent behavior was that way long before the maint plan
>> had the hist cleanup task and they simply didn't factor in agents
>> settings when they wrote the task for the maint plans. They should
>> have have had you specify a minimum # of rows deform they deleted them
>> by date only.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:OoK7jCdYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Unfortunately Andrew I have not seen any reposnse to my Connect post.
>> Thanks
>> Chris
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:Or3yu6cYIHA.4808@.TK2MSFTNGP05.phx.gbl...
>>> Very interesting Chris. Thanks for the update.
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>>
>>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>>> news:eGw2yxcYIHA.4208@.TK2MSFTNGP04.phx.gbl...
>>> Hi,
>>>
>>> We found the problem. If you run a maintenance plan that has a
>>> History Cleanup Task and its run by an admin level account then the
>>> sp_purge_jobhistory does not include a @.job_name parameter. This
>>> combination means that all job history gets cleaned up rather than
>>> just the job that has the History Cleanup Task problem raised on
>>> Connect as bug 322320.
>>>
>>> Chris
>>>
>>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>>> news:O9nm5EQPIHA.4272@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> Under SQL Agent properties/History there are 2 limiting columns
>>> for Limit size of job history log.
>>> We have Maximum job history set to 12000 and maximum job history
>>> rows per job at 150 but we only seem to be able to keep 2 or 3
>>> days worth of jobhistory even though looking in sysjobhistory I
>>> can only return 7300 rows.
>>>
>>> I thought that it would keep a maximum of 150 per job to a total
>>> of 12000.
>>>
>>> Is something else limiting the data in sysjobhistory?
>>>
>>> Thanks
>>>
>>> Chris
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>

limited permissions on test server for developers

I want to give developers limited permissions on a test sql server 2005
database. Basically they should be able to add columns to a table or
modify a columns datatype.
Create table and view definition have been granted to the developer
accounts (along with insert/update/delete), but they receive the
following error when trying to modify a table schema:
table dbo.ppir is set to read only, user doesn't have enough rights on
this table.
I am fairly new to sql server, but I did do some searching to find an
answer. I would prefer not to give them db owner permissions if
possible.
Thanks,
Andyandy wrote:
> I want to give developers limited permissions on a test sql server 2005
> database. Basically they should be able to add columns to a table or
> modify a columns datatype.
>
You could add the accounts to the "db_ddladmin" role on the database.
Regards,
lucm|||Hi Andy
They will need ALTER TABLE permission on the given table. See ALTER TABLE in
books online for more.
John
"andy" wrote:
> I want to give developers limited permissions on a test sql server 2005
> database. Basically they should be able to add columns to a table or
> modify a columns datatype.
> Create table and view definition have been granted to the developer
> accounts (along with insert/update/delete), but they receive the
> following error when trying to modify a table schema:
> table dbo.ppir is set to read only, user doesn't have enough rights on
> this table.
> I am fairly new to sql server, but I did do some searching to find an
> answer. I would prefer not to give them db owner permissions if
> possible.
> Thanks,
> Andy
>|||This worked. Thanks for the help
Andy
On Nov 16, 12:45 pm, "lucm" <l...@.iqato.com> wrote:
> andy wrote:
> > I want to give developers limited permissions on a test sql server 2005
> > database. Basically they should be able to add columns to a table or
> > modify a columns datatype.You could add the accounts to the "db_ddladmin" role on the database.
> Regards,
> lucm

limited permissions on test server for developers

I want to give developers limited permissions on a test sql server 2005
database. Basically they should be able to add columns to a table or
modify a columns datatype.
Create table and view definition have been granted to the developer
accounts (along with insert/update/delete), but they receive the
following error when trying to modify a table schema:
table dbo.ppir is set to read only, user doesn't have enough rights on
this table.
I am fairly new to sql server, but I did do some searching to find an
answer. I would prefer not to give them db owner permissions if
possible.
Thanks,
Andy
andy wrote:
> I want to give developers limited permissions on a test sql server 2005
> database. Basically they should be able to add columns to a table or
> modify a columns datatype.
>
You could add the accounts to the "db_ddladmin" role on the database.
Regards,
lucm
|||Hi Andy
They will need ALTER TABLE permission on the given table. See ALTER TABLE in
books online for more.
John
"andy" wrote:

> I want to give developers limited permissions on a test sql server 2005
> database. Basically they should be able to add columns to a table or
> modify a columns datatype.
> Create table and view definition have been granted to the developer
> accounts (along with insert/update/delete), but they receive the
> following error when trying to modify a table schema:
> table dbo.ppir is set to read only, user doesn't have enough rights on
> this table.
> I am fairly new to sql server, but I did do some searching to find an
> answer. I would prefer not to give them db owner permissions if
> possible.
> Thanks,
> Andy
>
|||This worked. Thanks for the help
Andy
On Nov 16, 12:45 pm, "lucm" <l...@.iqato.com> wrote:
> andy wrote:
> Regards,
> lucm

limited permissions on test server for developers

I want to give developers limited permissions on a test sql server 2005
database. Basically they should be able to add columns to a table or
modify a columns datatype.
Create table and view definition have been granted to the developer
accounts (along with insert/update/delete), but they receive the
following error when trying to modify a table schema:
table dbo.ppir is set to read only, user doesn't have enough rights on
this table.
I am fairly new to sql server, but I did do some searching to find an
answer. I would prefer not to give them db owner permissions if
possible.
Thanks,
Andyandy wrote:
> I want to give developers limited permissions on a test sql server 2005
> database. Basically they should be able to add columns to a table or
> modify a columns datatype.
>
You could add the accounts to the "db_ddladmin" role on the database.
Regards,
lucm|||Hi Andy
They will need ALTER TABLE permission on the given table. See ALTER TABLE in
books online for more.
John
"andy" wrote:

> I want to give developers limited permissions on a test sql server 2005
> database. Basically they should be able to add columns to a table or
> modify a columns datatype.
> Create table and view definition have been granted to the developer
> accounts (along with insert/update/delete), but they receive the
> following error when trying to modify a table schema:
> table dbo.ppir is set to read only, user doesn't have enough rights on
> this table.
> I am fairly new to sql server, but I did do some searching to find an
> answer. I would prefer not to give them db owner permissions if
> possible.
> Thanks,
> Andy
>|||This worked. Thanks for the help
Andy
On Nov 16, 12:45 pm, "lucm" <l...@.iqato.com> wrote:
> andy wrote:
> Regards,
> lucm

Limitations in term of number of tasks and number of columns

Hi,

I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.

To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.
Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.

Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?

If not, then do you have any idea why it's so slow ?

Thanks in advance for any answer.
Two things. One, the XML on your package has to be extremely large and cumbersome for the engine to work with. I would imagine this is one source of your slowness. If you can break your package up into smaller packages, that would be much better and likely easier to support.

Two, you might have some success in working offline via the "Work Offline" switch under the SSIS menu in BIDS. That is, perhaps some of the slowness is in validating your data flows against the connections.

Limit table rows

How do I limit table rows in a report, right now it shows all rows and that
makes it difficult to see what the columns are.
SQL 2000
----
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 40320 spam-mails
Betalende brugere får ikke denne besked i deres e-mails.
Hent en gratis SPAMfighter her.On Feb 19, 4:24 pm, "CRM Master" <no_s...@.spam.com> wrote:
> How do I limit table rows in a report, right now it shows all rows and th=at
> makes it difficult to see what the columns are.
> SQL 2000
> ----=--
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at f=E5 40320 spam-mails
> Betalende brugere f=E5r ikke denne besked i deres e-mails.
> Hent en gratis SPAMfighter her.
You should adjust your query by using 'Top(number of rows).'
If you are referring to report columns, you can limit the number of
records per column by selecting the 'Edit details group' (if you're
using a label), below 'Group on:' and 'Expression' enter
=3DCeiling(RowNumber(Nothing)/18) where 18 can be changed to number of
records per column. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer