Previous Month To Date Calculation

I am currenty attempting to add a calculation to get previous month to date calculation. I currently have the following calculation in to calculate previous Year to Date and am having trouble adapting it. Any help is appreciated. Please note I am fairly new at this.

Previous Year To Date:

([Time Calculations].[YTD Pr Yr]=
Aggregate(
Crossjoin({[Calendar Year].[Current Period]},
PeriodsToDate(
[Time].[Calendar Year].[Year],
ParallelPeriod(
[Time].[Calendar Year].[Year],1,
[Time].[Calendar Year].CurrentMember)))
)
);

Adam

Dear Friend,

Check this example:

MTD:

Code Snippet

SUM(PeriodsToDate([DimTime].[Hierarquia].[Month],
[DimTime].[Hierarquia].CurrentMember),
[Measures].[NC_ValorCarteira])

Gets Previous Member

Code Snippet

IIF(IsEMPTY(([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))
,0,([Measures].[CM_ResFinAcum],[DimTime].[Hierarquia].PrevMember))

Helped?

Regards!

|||That is not quite what I am looking for. I can calculate current month to date just fine, but what I am attempting to calcuate is month to date for the previous year. So from Aug 1, 2006 - Aug 21 2006. Given the nature of our business, know growth from this time last year is essential. This is what I am attempting to use and it returns the entire aggregate of the previous years period. Any help would be greatly appreciated.

Aggregate
(
PeriodsToDate(
[Time].[Year - Month - Week - Day of Week].[Month],
ParallelPeriod(
[Time].[Year - Month - Week - Day of Week].[Month],12,
[Time].[Year - Month - Week - Day of Week].CurrentMember)
),
[Measures].[Orders]
)

Adam|||

Hi Adam,

I'm not sure about the structure of your [Time] dimension, so here's a sample Adventure Works query:

Code Snippet

With

Member [Measures].[MTDSales] as

Aggregate(PeriodsToDate([Date].[Calendar].[Month]),

[Measures].[Sales Amount]),

FORMAT_STRING = 'Currency'

Member [Measures].[MTDSales-PY] as

([Measures].[MTDSales],

ParallelPeriod([Date].[Calendar].[Calendar Year])),

FORMAT_STRING = 'Currency'

select

{[Measures].[Sales Amount], [Measures].[MTDSales],

[Measures].[MTDSales-PY]} on 0,

Non Empty

{[Date].[Calendar].[Month].&[2003]&[7].Children,

[Date].[Calendar].[Month].&[2004]&[7].Children} on 1

from [Adventure Works]

|||Using that structure, I am still getting all of last years numbers. The ideal goal is to compare the current months MTD with the corresponding MTD of last year to evaluate growth. It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.
|||

AdamAtAirNWater wrote:

It is misleading to compare the current MTD with the entirety of the correspond prior years MTD. What I need is someone to exlude days from the prior year MTD calculation.

But that's how I thought the sample Adventure Works query worked. For example, compare these 2 result rows:

...

July 15, 2003 $30,792.07 $3,103,364.27 $2,642,983.51

...

July 15, 2004 $1,379.50 $23,234.19 $3,103,364.27

The [MTDSales-PY] for July 15, 2004 is $3,103,364.27, which is identical to [MTDSales] for July 15, 2003 (the total for all days of July, 2003 is $3,552,319.38). To better understand your issue, could you point out specific examples in the sample query results?

|||Perhaps I made an error in the way I adapted it into my calculation. I am attempting to put this into a calculation in a cube.

How would I translate that into an expression for a cube?

Adam
|||This is what I am currently using in my cube to calculate MTD and the Prior MTD. As I said, it is returning the entire value for the previous MTD

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month]),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year])),
FORMAT_STRING = "#",
VISIBLE = 1;

|||

Well, the expressions look similar to the Adventure Works sample, so I'm wondering whether there's an issue with the [Time].[Year - Month - Dayof Month] hierarchy not being natural. In any case, it's worth trying the full forms of PeriodsToDate() and ParallelPeriod(), like:

Code Snippet

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD]
AS Aggregate(PeriodsToDate([Time].[Year - Month - Dayof Month].[Month],

[Time].[Year - Month - Dayof Month].CurrentMember),
[Measures].[Orders]),
FORMAT_STRING = "Standard",
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[MEASURES].[MTD-Prior]
AS ([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Dayof Month].[Year], 1,

[Time].[Year - Month - Dayof Month].CurrentMember)),
FORMAT_STRING = "#",
VISIBLE = 1;

|||I am not entirely sure what you mean by the hierarchy not being natural. However, I am assuming this is the issue as it is still not working as intended. I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent." I am not entirely sure what to do to fix that however.

Adam
|||

AdamAtAirNWater wrote:

I do have a yellow triangle on the hierarchy with the message that states "Attribute relationships do not exist between one or more levels in this hierarchy. The following hierarchies do not have a direct or indirect relationship defined to their parent."

This indicates that the [Year - Month - Dayof Month] hierarchy is not natural:

SQL Server 2005 Books Online

Attribute Relationships

...

Natural Hierarchy Relationships

A hierarchy is a natural hierarchy when each attribute included in the user-defined hierarchy has a one to many relationship with the attribute immediately below it.

...

Relationships representing natural hierarchies are enforced by creating an attribute relationship between the attribute for a level and the attribute for the level below it.

...

My guess is that the "Month" and/or 'DayOfMonth" attributes in the hierarchy are not unique across higher levels of the hierarchy. For example, if 'DayOfMonth" was like 1, 2, etc, the same member could appear under multiple months. So it should be qualified (could be by month and year) to make it unique. If you study how the [Date] dimension and Fiscal hierarchy in Adventure Works are designed, it will become clearer.

|||I have fixed the hierarchy issues and have even have the calculation almost exactly where I want it. If I use:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then its gets me the correct information. The only question I now have is there anyway that I can see this number at the month level of the hierarchy. When view my data in the cube, I have to drill down to the day and it give me the correct month to date on that sepecific day, but ideally I don't want to have to go that deep in order to see it. Is that possible?

Thanks again for all the help. It is greatly apprieciated and I have learned a lot.

Adam
|||But which date should be selected for browsing MTD at the month level - is it the last day of the month with data? I'm guessing that most months earlier than the current one have data for all days, so in those cases it will be the total for the month.|||I would want to show the last date with data of the current month for the previous years month.

Adam
|||

One way to do that would be to add a dedicated measure - which would work at any level. So if [MTD-PY] is defined as you indicated above:

Code Snippet

([Measures].[MTD],
ParallelPeriod([Time].[Year - Month - Date].[Simple Date], 365,
[Time].[Year - Month - Date].CurrentMember))

then [LatestMTD-PY] could be like:

Code Snippet

([Measures].[MTD-PY],

Tail(NonEmpty([Time].[Year - Month - Date].[Simple Date],

{[Measures].[Orders]})).Item(0))

previous month on first day

Newbie question. I am using a query that pulls month-to-date data that has the following where clause:

WHERE (MONTH(datefield) = MONTH(GETDATE())) AND (YEAR(datefield) = YEAR(GETDATE()))

this works just fine but what I would like for it to do is give me the previous month of data if the if it's

the first day of the month and then any other day give me month to date. Is this possible?

Thanks in advance,

Marco

Could you just use GETDATE()-1?

Code Snippet

WHERE (MONTH(datefield) = MONTH(GETDATE()-1)) AND (YEAR(datefield) = YEAR(GETDATE()-1))

HTH!

|||

I'm sorry, I should have added that I want to accomplish this without having to modify the query every month.

|||

Sorry, i don't understand. Why would you have to modify the query every month?

|||

Well, throughout the entire month I use WHERE (MONTH(datefield) = MONTH(GETDATE())) AND (YEAR(datefield) = YEAR(GETDATE())) to get the month to date data and then when the new month starts Ihave to change it to WHERE (MONTH(datefield) = MONTH(GETDATE()-1)) AND (YEAR(datefield) = YEAR(GETDATE())) to get the previous month and then change it back to continue the month-to-date data.

|||

But as you're only matching on month and year, you won't have to change it. GETDATE()-1 will take one day off the date, and so it just means that on the 1st day of the month, it will use the last day of the last month whereas any other day within that month, the month and year values are the same.

Test it out:

Code Snippet

declare @.date datetime

set @.date = '1 nov 2007'

select month(@.date-1), year(@.date-1)

declare @.date datetime

set @.date = '20 nov 2007'

select month(@.date-1), year(@.date-1)

|||

Code Snippet

SELECT DATEADD(m,-1,DATEADD(m, DATEDIFF(m, 0, '8/29/07'), 0))

Lee Everest

Sr. Consultant - Sogeti USA LLC

www.texas2oo.com/sqlblog

|||

You can combine the 2 WHERE clause like this :

select datefield,month(datefield) from dbo.datefieldtable

WHERE (

MONTH(datefield)=

case when DATEPART(day, GETDATE())=1 then MONTH(GETDATE()-1)

else MONTH(GETDATE()) end )

AND

YEAR(datefield) = YEAR(GETDATE())

--with DATEPART(day, GETDATE())=1 you can obtain the first day of month

You have to see the performance!

|||

here it is,

Code Snippet

where

datefield >= case when day(getdate()) = 1 then

cast(convert(varchar,dateadd(mm,-1,getdate()),101) as datetime)

else

cast(convert(varchar,dateadd(dd,1-day(getdate()),getdate()),101) as datetime)

end

and

datefield < case when day(getdate()) = 1 then

cast(convert(varchar,getdate(),101) as datetime)

else

cast(convert(varchar,getdate()+1,101) as datetime)

end

|||

Perhaps the range can be obtained by something like:

Code Snippet

declare @.testDates table (dt datetime)
insert into @.testDates
select '7/20/7' union all
select '7/31/7' union all
select '8/1/7' union all
select '8/2/7' union all
select '8/5/7' union all
select '8/31/7' union all
select '9/1/7' union all
select '9/2/7'
--select * from @.testDates

select dt,
dateadd(mm, datediff(mm, 0, dt-1), 0) as rangeLow,
dateadd(mm, datediff(mm, 0, dt-1) + 1, 0) as rangeHi,
dateadd(mm, datediff(mm, 0, dt-1) + 1, 0)-1 as monthEnd
from @.testDates

/*
dt rangeLow rangeHi monthEnd
--
2007-07-20 2007-07-01 2007-08-01 2007-07-31
2007-07-31 2007-07-01 2007-08-01 2007-07-31
2007-08-01 2007-07-01 2007-08-01 2007-07-31
2007-08-02 2007-08-01 2007-09-01 2007-08-31
2007-08-05 2007-08-01 2007-09-01 2007-08-31
2007-08-31 2007-08-01 2007-09-01 2007-08-31
2007-09-01 2007-08-01 2007-09-01 2007-08-31
2007-09-02 2007-09-01 2007-10-01 2007-09-30
*/

|||

Got it. Thank you all.

Previous Month Name in Report Header

I'm failry new to Reporting Services and I need to put the name of the
previous month into the Report Header. For example I need it to look like
"November 2004..." How would I go about getting this into the Report Header.
Thanks in advance,
BJYOu can use: =Now().AddMonths(-1)
If you want to format it to display the way you showed, you can use:
=format(Now().AddMonths(-1), "MMMM dd, yyyy)
Charles Kangai, MCT, MCDBA
"BJ Castrellon" wrote:
> I'm failry new to Reporting Services and I need to put the name of the
> previous month into the Report Header. For example I need it to look like
> "November 2004..." How would I go about getting this into the Report Header.
> Thanks in advance,
> BJ|||You can use the expression: =Now().AddMonths(-1)
If you want to format it the way you have in your question, you can do use
the expression: =format(Now().AddMonths(-1), "MMMM yyyy")
Charles Kangai, MCT, MCDBA
"BJ Castrellon" wrote:
> I'm failry new to Reporting Services and I need to put the name of the
> previous month into the Report Header. For example I need it to look like
> "November 2004..." How would I go about getting this into the Report Header.
> Thanks in advance,
> BJ|||Is this possible with a report parameter?
I have a date being passed in and I need to display the previous month of
that date. So in a text box I put this
=datetime.parameter!startdate.addmonths(-1).tostring("MMMM")
but get only errors.
Has anyone had to do this before?
"Charles Kangai" wrote:
> You can use the expression: =Now().AddMonths(-1)
> If you want to format it the way you have in your question, you can do use
> the expression: =format(Now().AddMonths(-1), "MMMM yyyy")
> Charles Kangai, MCT, MCDBA
> "BJ Castrellon" wrote:
> > I'm failry new to Reporting Services and I need to put the name of the
> > previous month into the Report Header. For example I need it to look like
> > "November 2004..." How would I go about getting this into the Report Header.
> >
> > Thanks in advance,
> >
> > BJ|||May be this helps.
=MonthName(format(Parameters!date.Value.addmonths(-1),"MM"))
"Ben Sullins" <BenSullins@.discussions.microsoft.com> wrote in message
news:EE5DCBC0-870F-4D99-B147-D6EDA3747B8B@.microsoft.com...
> Is this possible with a report parameter?
> I have a date being passed in and I need to display the previous month of
> that date. So in a text box I put this
> =datetime.parameter!startdate.addmonths(-1).tostring("MMMM")
> but get only errors.
> Has anyone had to do this before?
>
> "Charles Kangai" wrote:
> > You can use the expression: =Now().AddMonths(-1)
> > If you want to format it the way you have in your question, you can do
use
> > the expression: =format(Now().AddMonths(-1), "MMMM yyyy")
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "BJ Castrellon" wrote:
> >
> > > I'm failry new to Reporting Services and I need to put the name of the
> > > previous month into the Report Header. For example I need it to look
like
> > > "November 2004..." How would I go about getting this into the Report
Header.
> > >
> > > Thanks in advance,
> > >
> > > BJ|||That didn't work but I was able to find this peice that did..
CDate(Parameters!startdate.Value).AddMonths(-1).ToString("MMMM")
"saglamtimur" wrote:
> May be this helps.
> =MonthName(format(Parameters!date.Value.addmonths(-1),"MM"))
>
> "Ben Sullins" <BenSullins@.discussions.microsoft.com> wrote in message
> news:EE5DCBC0-870F-4D99-B147-D6EDA3747B8B@.microsoft.com...
> > Is this possible with a report parameter?
> >
> > I have a date being passed in and I need to display the previous month of
> > that date. So in a text box I put this
> > =datetime.parameter!startdate.addmonths(-1).tostring("MMMM")
> > but get only errors.
> >
> > Has anyone had to do this before?
> >
> >
> > "Charles Kangai" wrote:
> >
> > > You can use the expression: =Now().AddMonths(-1)
> > > If you want to format it the way you have in your question, you can do
> use
> > > the expression: =format(Now().AddMonths(-1), "MMMM yyyy")
> > >
> > > Charles Kangai, MCT, MCDBA
> > >
> > > "BJ Castrellon" wrote:
> > >
> > > > I'm failry new to Reporting Services and I need to put the name of the
> > > > previous month into the Report Header. For example I need it to look
> like
> > > > "November 2004..." How would I go about getting this into the Report
> Header.
> > > >
> > > > Thanks in advance,
> > > >
> > > > BJ
>
>

Previous machine name in Profiler

I changed the machine name yesterday (from Machine1 to Server1).
I'd delete the SQL server registration in Enterprise Manager and
register again with the new server name - Server1.
Everything run correctly.
But when I open the Profiler, create a new trace, I use (local) when
"Connect to SQL Server" prompted. After I click "OK", I saw my old
machine name "Machine1" in "Trace SQL Server" field under the "General"
tab. Everytime I have to change that server name to my new machine name
in order to use the profiler.
Does anyone knows how to change that default to my new machine name?
(It didn't run when I use the old machine name)
Thanks.
Peter CCH
Check out Client/Server Network Utility the alias to be changed
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1140415112.093022.101800@.o13g2000cwo.googlegr oups.com...
>I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>
|||Hi Peter
Did you follow: http://support.microsoft.com/default...b;en-us;303774
John
"Peter CCH" wrote:

> I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>
|||I tried:
sp_dropserver 'Machine1'
go
sp_addserver 'Server1', 'local'
go
But the problem still remain.
I tried to add in alias under Client/Network Utility, but still the
same, problem remain.
Peter CCH
|||OK, got it.
Have to restart the SQL Server in order to take effect.
Thanks.
Peter CCH

Previous machine name in Profiler

I changed the machine name yesterday (from Machine1 to Server1).
I'd delete the SQL server registration in Enterprise Manager and
register again with the new server name - Server1.
Everything run correctly.
But when I open the Profiler, create a new trace, I use (local) when
"Connect to SQL Server" prompted. After I click "OK", I saw my old
machine name "Machine1" in "Trace SQL Server" field under the "General"
tab. Everytime I have to change that server name to my new machine name
in order to use the profiler.
Does anyone knows how to change that default to my new machine name?
(It didn't run when I use the old machine name)
Thanks.
Peter CCHCheck out Client/Server Network Utility the alias to be changed
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1140415112.093022.101800@.o13g2000cwo.googlegroups.com...
>I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>|||Hi Peter
Did you follow: [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;303774[/ur
l]
John
"Peter CCH" wrote:

> I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>|||I tried:
---
sp_dropserver 'Machine1'
go
sp_addserver 'Server1', 'local'
go
---
But the problem still remain.
I tried to add in alias under Client/Network Utility, but still the
same, problem remain.
Peter CCH|||OK, got it.
Have to restart the SQL Server in order to take effect.
Thanks.
Peter CCH

Previous machine name in Profiler

I changed the machine name yesterday (from Machine1 to Server1).
I'd delete the SQL server registration in Enterprise Manager and
register again with the new server name - Server1.
Everything run correctly.
But when I open the Profiler, create a new trace, I use (local) when
"Connect to SQL Server" prompted. After I click "OK", I saw my old
machine name "Machine1" in "Trace SQL Server" field under the "General"
tab. Everytime I have to change that server name to my new machine name
in order to use the profiler.
Does anyone knows how to change that default to my new machine name?
(It didn't run when I use the old machine name)
Thanks.
Peter CCHCheck out Client/Server Network Utility the alias to be changed
"Peter CCH" <petercch.wodoy@.gmail.com> wrote in message
news:1140415112.093022.101800@.o13g2000cwo.googlegroups.com...
>I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>|||Hi Peter
Did you follow: http://support.microsoft.com/default.aspx?scid=kb;en-us;303774
John
"Peter CCH" wrote:
> I changed the machine name yesterday (from Machine1 to Server1).
> I'd delete the SQL server registration in Enterprise Manager and
> register again with the new server name - Server1.
> Everything run correctly.
> But when I open the Profiler, create a new trace, I use (local) when
> "Connect to SQL Server" prompted. After I click "OK", I saw my old
> machine name "Machine1" in "Trace SQL Server" field under the "General"
> tab. Everytime I have to change that server name to my new machine name
> in order to use the profiler.
> Does anyone knows how to change that default to my new machine name?
> (It didn't run when I use the old machine name)
> Thanks.
>
> Peter CCH
>|||I tried:
---
sp_dropserver 'Machine1'
go
sp_addserver 'Server1', 'local'
go
---
But the problem still remain.
I tried to add in alias under Client/Network Utility, but still the
same, problem remain.
Peter CCH|||OK, got it.
Have to restart the SQL Server in order to take effect.
Thanks.
Peter CCH

Previous Function - Help

Newbie question. I have searched the posts and have found a few that say the
previous function works. The documentation in the online books is a little
sketchy. Does someone have a detailed example? If not, could someone point
me to more detailed documentation on this function?
Thanks,
RickThe documentation about the Previous() function is actually incorrect
because it describes multiple parameters. The Previous function only has one
argument which is an expression and you can use Previous() only in a list or
a table.
Example:
=Previous(Fields!A.Value)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:A47E8613-9078-4610-A528-FC5923197318@.microsoft.com...
> Newbie question. I have searched the posts and have found a few that say
> the
> previous function works. The documentation in the online books is a
> little
> sketchy. Does someone have a detailed example? If not, could someone
> point
> me to more detailed documentation on this function?
> Thanks,
> Rick|||Thanks Robert. Works like a champ.
"Robert Bruckner [MSFT]" wrote:
> The documentation about the Previous() function is actually incorrect
> because it describes multiple parameters. The Previous function only has one
> argument which is an expression and you can use Previous() only in a list or
> a table.
> Example:
> =Previous(Fields!A.Value)
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Rick" <Rick@.discussions.microsoft.com> wrote in message
> news:A47E8613-9078-4610-A528-FC5923197318@.microsoft.com...
> > Newbie question. I have searched the posts and have found a few that say
> > the
> > previous function works. The documentation in the online books is a
> > little
> > sketchy. Does someone have a detailed example? If not, could someone
> > point
> > me to more detailed documentation on this function?
> >
> > Thanks,
> > Rick
>
>

Previous day query

Hi There,

I am a newbie on SQL qieries and I am trying to run a query on a date/time filed to get yesterdays data.

Here is my test query ran from SQL analyzer:

select * from mvs_store_all_data_time_change where mvs_creation_date = {fn NOW() }

The error I am getting is:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

I assume it is to do with the format of this field I am trying to query and may need to be converted?

Any help will be much appreciated.

Cheers,

Riaan.Riaan,
Try
select * from mvs_store_all_data_time_change
where mvs_creation_date > (getdate()-1)|||try this one

select * from <table> where DATEPART(dd, <date_column>)=DATEPART(dd, getdate()-1)

<date_column>-where your date is saved
Explanation-let it be 03/08/2006 today,then DATEPART(dd, getdate()-1)
returns 02.and u can get the data of the previous day.
Regards,
Lipsa|||Try this one

select * from <table> where DATEPART(dd, <date_column>)=DATEPART(dd, getdate()-1)

<date_column>-your column name in database where u insert dates
Explanation-suppose its 03/08/2006 today.so ,DATEPART(dd, getdate()-1)will return 02.and u can do the comarison and get the data of previous day. :)
Regards,
Lipsa :)|||Hi There.

Ok, I have tried your suggestion, but get the following error:

"Syntax error converting datetime from character string."

Any further ideas :). Cheers, Riaan.|||Hi Phil. Ok I treid your suggestion, but get the following error "Syntax error converting datetime from character string.". Any more ideas :).

Cheers, Riaan.|||

Quote:

Originally Posted by Riaan

Hi Phil. Ok I treid your suggestion, but get the following error "Syntax error converting datetime from character string.". Any more ideas :).

Cheers, Riaan.

Ho Riaan,

try this one...

select * from mvs_store_all_data_time_change
where convert(char,mvs_creation_date,102) = convert(char,getdate()-1,102)

regards,
Pankaj Bhatnagar|||

Quote:

Originally Posted by bhatnagarp

Ho Riaan,

try this one...

select * from mvs_store_all_data_time_change
where convert(char,mvs_creation_date,102) = convert(char,getdate()-1,102)

regards,
Pankaj Bhatnagar

Hi Pankaj

Ok, I tried your suggestion and now I am not getting the error message, but no data is returned? Is this not a case of converting this field (which is a integer, to a string and then cast it as a datetime format?). If so, can you maybe help with the syntax of such a query? ALso can I do it from a view, where I am running this from, as I am also not sure where to enter it in the view?. Hope you can help.

Cheers,

Riaan.|||Hi Pankaj

Ok, I tried your suggestion and now I am not getting the error message, but no data is returned? Is this not a case of converting this field (which is a integer, to a string and then cast it as a datetime format?). If so, can you maybe help with the syntax of such a query? ALso can I do it from a view, where I am running this from, as I am also not sure where to enter it in the view?. Hope you can help.

Cheers,

Riaan.

Previous Business Day

At the moment, I have a report that's using "=Today.AddDays(-1)" to return
yesterday's date in the date range parameters, but the client has now come
back and requested that they would prefer not to use weekends...e.g.: loading
the report on Monday should show last Friday's date.
Can this be done?
Thanks in advance.
Jadranka Krapic
DBA
Stargate Technologies
(www.stargatetech.com.au)Hi,
Write simple custom code function
Function GetDate() As Date
Select Case Today.DayOfWeek
Case DayOfWeek.Monday
GetDate = Today.Date.AddDays(-3)
Case Else
GetDate = Today.Date.AddDays(-1)
End Select
End Function
And as parameter label and value put
=Code.GetDate()
Hope this help.
Best regards,
Dejan Lukovic
"Jadranka" <Jadranka@.discussions.microsoft.com> escribió en el mensaje
news:03C3F8BF-1EC8-420B-81D9-106D94D608A1@.microsoft.com...
> At the moment, I have a report that's using "=Today.AddDays(-1)" to return
> yesterday's date in the date range parameters, but the client has now come
> back and requested that they would prefer not to use weekends...e.g.:
loading
> the report on Monday should show last Friday's date.
> Can this be done?
> Thanks in advance.
> Jadranka Krapic
> DBA
> Stargate Technologies
> (www.stargatetech.com.au)

Previous and next ID

I'd like to find the previous and next record of a table based on the numeri
c
value of an identity column. For example, consider the following sample dat
a:
RecID | theValue
--
1 | first
2 | second
4 | third
6 | fourth
7 | fifth
If the value '4' is passed in to my query (via ASP.NET app), I can get the
previous/next records by running these two queries:
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
I'm trying to combine these two queries into a single query to return those
two values, but I'm banging my head against the wall. If someone could give
me insight or a completely different path to follow, I'd appreciate it.
ThanksTry:
SELECT *
FROM
(
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
) x
UNION ALL
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:157D705F-C3F6-4E7A-AE2F-AA675E4BD31B@.microsoft.com...
I'd like to find the previous and next record of a table based on the
numeric
value of an identity column. For example, consider the following sample
data:
RecID | theValue
--
1 | first
2 | second
4 | third
6 | fourth
7 | fifth
If the value '4' is passed in to my query (via ASP.NET app), I can get the
previous/next records by running these two queries:
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
I'm trying to combine these two queries into a single query to return those
two values, but I'm banging my head against the wall. If someone could give
me insight or a completely different path to follow, I'd appreciate it.
Thanks|||Thanks for the quick reply, Tom.
But it seems to be ignoring the ORDER BY clause in the second query.
The result is:
RecID
--
2
7
The 2 is correct, but the 7 is not. Any ideas?
"Tom Moreau" wrote:

> Try:
> SELECT *
> FROM
> (
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> ) x
> UNION ALL
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:157D705F-C3F6-4E7A-AE2F-AA675E4BD31B@.microsoft.com...
> I'd like to find the previous and next record of a table based on the
> numeric
> value of an identity column. For example, consider the following sample
> data:
> RecID | theValue
> --
> 1 | first
> 2 | second
> 4 | third
> 6 | fourth
> 7 | fifth
> If the value '4' is passed in to my query (via ASP.NET app), I can get the
> previous/next records by running these two queries:
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
> I'm trying to combine these two queries into a single query to return thos
e
> two values, but I'm banging my head against the wall. If someone could gi
ve
> me insight or a completely different path to follow, I'd appreciate it.
> Thanks
>
>|||select recid
(select max(recid) from thetable t2 where t2.recid<t1.recid) prevID,
(select min(recid) from thetable t2 where t2.recid>t1.recid) nextID
from theTable t1
on SQL 2K5 use row_number()|||How about :
SELECT *
FROM
(
SELECT TOP 1 RecID
FROM theTable
WHERE RecID < 4
ORDER BY RecID DESC
) x
UNION ALL
SELECT *
FROM
(
SELECT TOP 1 RecID
FROM theTable
WHERE RecID > 4
ORDER BY RecID ASC
) y
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:FB304593-6491-434F-8315-A4E69D7BDE1D@.microsoft.com...
Thanks for the quick reply, Tom.
But it seems to be ignoring the ORDER BY clause in the second query.
The result is:
RecID
--
2
7
The 2 is correct, but the 7 is not. Any ideas?
"Tom Moreau" wrote:

> Try:
> SELECT *
> FROM
> (
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> ) x
> UNION ALL
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:157D705F-C3F6-4E7A-AE2F-AA675E4BD31B@.microsoft.com...
> I'd like to find the previous and next record of a table based on the
> numeric
> value of an identity column. For example, consider the following sample
> data:
> RecID | theValue
> --
> 1 | first
> 2 | second
> 4 | third
> 6 | fourth
> 7 | fifth
> If the value '4' is passed in to my query (via ASP.NET app), I can get the
> previous/next records by running these two queries:
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID < 4
> ORDER BY RecID DESC
> SELECT TOP 1 RecID
> FROM theTable
> WHERE RecID > 4
> ORDER BY RecID ASC
> I'm trying to combine these two queries into a single query to return
> those
> two values, but I'm banging my head against the wall. If someone could
> give
> me insight or a completely different path to follow, I'd appreciate it.
> Thanks
>
>|||That did it Alexander. Thanks.
"Alexander Kuznetsov" wrote:

> select recid
> (select max(recid) from thetable t2 where t2.recid<t1.recid) prevID,
> (select min(recid) from thetable t2 where t2.recid>t1.recid) nextID
> from theTable t1
> on SQL 2K5 use row_number()
>|||Do you know the differences in rows and records? Do you now that you
are mimicking a magnetic tape file in SQL? Why did you use the
proprietary SELECT TOP syntax?
SELECT MIN(F1.foo_id) AS prev_tape_position,
MAX(F2.foo_id) AS next_tape_position
FROM FakeTape AS F1, FakeTape AS F2
WHERE F1.foo_id > @.current_tape_position
AND F2.foo_id < @.current_tape_position;
I am not usre what you want to do when the imaginary read head is on
the first or last "record".

Previous and Next functions

Hi,

I am tasked with duplicating many Oracle report in SRS. One uses a stored proc to get a list of people and sorts them by name. It then prints in red those names that are duplicates. It uses this bit of code to do it:

If {PlayerName} =
Previous ({PlayerName})
or
{PlayerName} =
Next ({PlayerName})
then Red
else Black

I don't find any quite so simple functions to do this.

I'm at the moment using VS2003 against SQL Server 2005.

Thanks,

Fairfield

We do have a Previous function http://msdn2.microsoft.com/en-us/library/ms156372.aspx

Next is not currently supported. An alternative way of doing this is to use custom code. You'd pass in all the names into the custom code and detect duplicates there (by using a hashtable for example). And then in the color style property call a custom function that looks in the hashtable and returns the desired color based on whether the specific name is a dup.

|||Thanks, I'll give it a go.

Previous & Next Row DataFlow Transform

I have issue where based up a value in a column i need to do some processing of the previous and current row. The dataflow is also already sorted. I tried creating a Script Data Flow Transformation to do this but it isn't working right and the debugging of it sucks. Would anyone know of the best way to do this? or some helpful pointers? I tried "firing" information to help debug but doesn't help when the error message i get back is a stack overflow message.

An example of what I'm trying to do is process the sorted incoming rows for each person. Each person can have multiple rows. Based upon a "status" column in each row do some different processing on the previous or current row. Some Psuedo code:

if prev.PersonID = current.PersonID if status = 1 change prev.PersonDate to today + 60 days if status = 2 change current.PersonDate to prev.PersonDate change prev.PersonDate to today + 1 day else
send rows to output

Any comments or suggestions or helpful advice/critique would be MUCH appreciated!This might help. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1139922&SiteID=1

Though, if you have a variable number of rows per PersonID, I'm not sure how well you can implement this in SSIS. Jay or some of the other script guys around here might have to chime in.|||

Yeah each person can have 1 or more rows. There isn't a defined set of rows per person. Yeah I looked at the link and that wouldn't work in my situation. Not sure if there would be a good way to do it in straight SQL either. Does anyone have suggestions on how to do it in SQL? If possible? Without cursors?

I think i'm going to play around with SQL for little bit and see if i can't come up with something, however executing a OLE DB Command on each record on 1+ million records is going to slow down the process significantly i suspect. I wonder if there is a batch update way of doing this?

|||I've done this many times. Usually I need to do some type of complex aggregation of multiple rows and only output a single row for a distinct entity. Its a great benefit that your data is already sorted.

I wrote the code below mostly from memory, so there may be some syntax problems, but hopefully you get the idea. You cache each row until you've seen the next one. So you're always writing one row behind the current one. You have to override FinishOutputs so you can write out your last row.

Code Snippet

Public Class ScriptMain
Inherits UserComponent

Private Class BufferClass
'define class members for columns
Public PersonID As Integer
Public Status As Integer
Public PersonDate As DateTime
End Class

Dim PreviousPersonID As Integer = -1

Dim Buffer As BufferClass = Nothing

Public Sub WriteBuffer()
If Not Buffer Is Nothing Then
With NewRecordsBuffer
.AddRow()
' add the persisted values from the class to the output buffer
.PersonID = Buffer.PersonID
.Status = Buffer.Status
.PersonDate = Buffer.PersonDate
End With

Buffer = Nothing

End If
End Sub

Public Overrides Sub FinishOutputs()
'write the previous row
WriteBuffer()
MyBase.FinishOutputs()
End Sub

Public Overrides Sub Input_ProcessInputRow(ByVal Row As InputBuffer)

Dim PreviousPersonDate As DateTime

If Row.PersonID = PreviousPersonID Then
'modify previous row
If Row.Status = 1 Then
Buffer.PersonDate = DateAdd("d", DateTime.Today, 60)
ElseIf Row.Status = 2 Then
PreviousPersonDate = Buffer.PersonDate
Buffer.PersonDate = DateAdd("d", DateTime.Today, 1)
End If
End If

'write previous row
WriteBuffer()

'buffer the current row
Buffer = New BufferClass
With Buffer
' fill the class with columns that need to be persisted
.PersonID = Row.PersonID
.Status = Row.Status
If Row.Status = 2 Then
.PersonDate = PreviousPersonDate
Else
.PersonDate = Row.PersonDate
End If
End With

PreviousPersonID = Row.PersonID

End Sub

End Class

|||

Thanks! I'll give this a shot. This is similar to what I was doing except I was getting a stack overflow problem and not sure why. The only difference was that I was buffering all of each persons rows (each person had 1 or more rows with the most being 14), but only changing data in the current and previous rows. When current row was a different person then it would output the all the buffered rows.

Anyways I'll give this a shot and see.

|||JayH- Does this process normally take a lot of time? It is extremely slow?|||

Hmmm I took out the FireInformation method I was using to debug (just one line) and now it is 1000 times faster.

JayH - I was wondering if it would be faster to output each row or to output a the person batch of rows?

|||It works! Thanks JayH! I implemented mine so that it handles batches than just the previous row. As far as performance between each row or a batch I'm not sure, but implementing as a batch is faster than a Merge Join transformation.|||

thames wrote:

It works! Thanks JayH! I implemented mine so that it handles batches than just the previous row. As far as performance between each row or a batch I'm not sure, but implementing as a batch is faster than a Merge Join transformation.

I'm glad you got it going. I doubt you'll find any performance difference between lagging only one row and all the rows for the PersonID, especially since you're only expecting a max of four rows per PersonID. I think the code is probably simpler to only do it for one row, but its just a matter of preference.

previewing sub report on list element? HELP PLEASE

I am not sure why i am having this problem as it has worked before. I have
placed a sub report on a list element.
When i preview the report the subreport (and table within) is pushed to the
second page while the entire list element remains on the first page.
Wat is going on here? (when viewed html it looks ok, it is only when i
'preview' or export to 'pdf' do i see the problem.
any help would be greatly appreciated.Likely due to the difference in how HTML vs. PDF renderers handle
pagination.
From designer, go to Report / Report Properties while in the Layout Tab.
Here you will be able to change the page width, page height and the margins.
The PDF export pays attention to these settings very diligently. Either
change the size of elements on your report or page size settings so report
can fit in a single page.
Alternatively, might try overriding the page size settings by passing in
parameters with the desired page height / width via URL (or via the
webservice call if you're rendering that way) when rendering to PDF.
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"dave" <dave@.discussions.microsoft.com> wrote in message
news:7DDB15B7-F78B-40E1-B984-EC27CDD75546@.microsoft.com...
>I am not sure why i am having this problem as it has worked before. I have
> placed a sub report on a list element.
> When i preview the report the subreport (and table within) is pushed to
> the
> second page while the entire list element remains on the first page.
> Wat is going on here? (when viewed html it looks ok, it is only when i
> 'preview' or export to 'pdf' do i see the problem.
> any help would be greatly appreciated.

previewing specific records in a report

Hi,

I have a form which contains records about patients. the primiary is PatientID and is based on the national ID number.

I have compiled a query to show the patient's bill (invoice). from this query I have created a report. i have placed a button on the same form mentioned above that will open the report. right now the button opens the report and all the records are showing.

i would like it that when the user goes to a particular record in the form, the user can click on the button to preview just that record's bill and no others.

i'm using access 2000.

bajanElfi should also mention i was told that i could "fix" the query in sql, but i'm not to sure how to do that.|||Originally posted by bajan_elf
i should also mention i was told that i could "fix" the query in sql, but i'm not to sure how to do that. The simplest answer is to build a criteria that restricts the query to just the data for a particular employee or bill. Without knowing a good bit more about your application, I can't really give you any specifics. The basic process isn't tough, you just add a criteria in the query form where client_number = x or where bill_id = y.

-PatP|||hmmm... no worries Pat. i got it working. it seems i needed a piece of code inthe command button the opens the report. below is what i added.

DoCmd.OpenReport stDocName, acPreview, "", "[PatientID]=[Forms]![frmAppointments]![PatientID]"

so in fact i didn't really need sql. thanks for taking the time to look at my post anyway tho, it was greatly appreciated.

bajan elf

Previewing reports - MissingMethodException

Hi, after installing RS SP1, when I want to preview a report in VS.NET 2003,
I get an error- MissingMethodException: Method not found: System.String
Microsoft.ReportingServices.Diagnostics.Globals.GetCurrentWindowsUserName().
I guess its because Microsoft.ReportingServices.Diagnostics.dll is somehow
wrongly registered, I tried to register it with regsvr32.exe (both copies,
one in Documents and Setting and one under RS home directory) but I didnt
succeed.
The report renders allright in Report Manager, but I really miss the
Preview, because it slows down the developing so much.
Any help would be appreciated.I even tried to unisnstall Reporting Services and did a clean install again,
but it didnt help. I really dont feel like reinstalling Windows now...
"Nikola Tepper" wrote:
> Hi, after installing RS SP1, when I want to preview a report in VS.NET 2003,
> I get an error- MissingMethodException: Method not found: System.String
> Microsoft.ReportingServices.Diagnostics.Globals.GetCurrentWindowsUserName().
> I guess its because Microsoft.ReportingServices.Diagnostics.dll is somehow
> wrongly registered, I tried to register it with regsvr32.exe (both copies,
> one in Documents and Setting and one under RS home directory) but I didnt
> succeed.
> The report renders allright in Report Manager, but I really miss the
> Preview, because it slows down the developing so much.
> Any help would be appreciated.|||--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nikola Tepper" <Nikola Tepper@.discussions.microsoft.com> wrote in message
news:8766C01C-9FA8-4C21-BC00-71627266FDA9@.microsoft.com...
> I even tried to unisnstall Reporting Services and did a clean install
again,
> but it didnt help. I really dont feel like reinstalling Windows now...
> "Nikola Tepper" wrote:
> > Hi, after installing RS SP1, when I want to preview a report in VS.NET
2003,
> > I get an error- MissingMethodException: Method not found: System.String
> >
Microsoft.ReportingServices.Diagnostics.Globals.GetCurrentWindowsUserName().
> > I guess its because Microsoft.ReportingServices.Diagnostics.dll is
somehow
> > wrongly registered, I tried to register it with regsvr32.exe (both
copies,
> > one in Documents and Setting and one under RS home directory) but I
didnt
> > succeed.
> > The report renders allright in Report Manager, but I really miss the
> > Preview, because it slows down the developing so much.
> > Any help would be appreciated.|||Sorry, hit "Send" too soon. Did you try reinstalling the .NET framework?
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:ODFBI7IjEHA.3632@.TK2MSFTNGP09.phx.gbl...
>
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Nikola Tepper" <Nikola Tepper@.discussions.microsoft.com> wrote in message
> news:8766C01C-9FA8-4C21-BC00-71627266FDA9@.microsoft.com...
> > I even tried to unisnstall Reporting Services and did a clean install
> again,
> > but it didnt help. I really dont feel like reinstalling Windows now...
> >
> > "Nikola Tepper" wrote:
> >
> > > Hi, after installing RS SP1, when I want to preview a report in VS.NET
> 2003,
> > > I get an error- MissingMethodException: Method not found:
System.String
> > >
>
Microsoft.ReportingServices.Diagnostics.Globals.GetCurrentWindowsUserName().
> > > I guess its because Microsoft.ReportingServices.Diagnostics.dll is
> somehow
> > > wrongly registered, I tried to register it with regsvr32.exe (both
> copies,
> > > one in Documents and Setting and one under RS home directory) but I
> didnt
> > > succeed.
> > > The report renders allright in Report Manager, but I really miss the
> > > Preview, because it slows down the developing so much.
> > > Any help would be appreciated.
>

Previewing RDL without Designer

Folks,
Is there a way to preview the "RDL" Reports programatically without
deploying to the report Server.
We needs this ability for the creation of ad-hoc reports where user designs
the contents of the report...
Any idea...? Appreciated...!
RakeshWith the new controls in local mode you can do this. The next beta for
Widbey (Visual Studio) will have both a web and winform control. They will
work both with and without Server. In local mode you can give it the rdl and
the dataset. The next beta should be available before too long. There is no
other way available until then.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:034B4A2B-6DAE-4253-8A9C-568EBC298082@.microsoft.com...
> Folks,
> Is there a way to preview the "RDL" Reports programatically without
> deploying to the report Server.
> We needs this ability for the creation of ad-hoc reports where user
designs
> the contents of the report...
> Any idea...? Appreciated...!
> Rakesh
>
>|||Hey Rakesh
I am working on something similar at the moment, and have had reasonable
results using the following.
Use the webservice interface to temporarily create a hidden folder, Create a
report from the RDL, render the report, then delete the report and folder.
All these actions are available through the SOAP interface. Its long winded
but does seem to work fine.
Regards
Quagmire|||Take a look at the RDL Project: http://www.fyireporting.com/
It has a control for viewing locally.
HTH,
Alfred Gary Myers Jr.
www.yuma.com.br
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:034B4A2B-6DAE-4253-8A9C-568EBC298082@.microsoft.com...
> Folks,
> Is there a way to preview the "RDL" Reports programatically without
> deploying to the report Server.
> We needs this ability for the creation of ad-hoc reports where user
> designs
> the contents of the report...
> Any idea...? Appreciated...!
> Rakesh
>
>

Previewing a URL image in Reporting Services

Hi all
I need to place an image from a web site onto a RS report. Is it possible to
do this in SRS 2000?
IE. http://<servername>/<directory>/<image.jpg> and render the image on the
report?
Thanks in advance
GeorgeRephrasing the question:
I've assigned the image control value to be a database field containing a
valid URL however I'm getting a red cross when trying to display the report.
Anyone got any ideas?
Thanks
George
"_george" <none@.nojne@.none> wrote in message
news:#kyvEDRmHHA.4688@.TK2MSFTNGP03.phx.gbl...
> Hi all
> I need to place an image from a web site onto a RS report. Is it possible
to
> do this in SRS 2000?
> IE. http://<servername>/<directory>/<image.jpg> and render the image on
the
> report?
> Thanks in advance
> George
>|||you need to set the image source to external and then set value =Fields!image_small.Value
Or if you drag the image control from toolbox, you will see 4 options for
image source, you need to choose Image Source = "Web" which the 4th option.
Then click next and leave the URL empty...totally tempty... then click
finish. Then you can edit the value of the image to the database field...
which is full valid url.
"Cristo" wrote:
> Rephrasing the question:
> I've assigned the image control value to be a database field containing a
> valid URL however I'm getting a red cross when trying to display the report.
> Anyone got any ideas?
> Thanks
> George
> "_george" <none@.nojne@.none> wrote in message
> news:#kyvEDRmHHA.4688@.TK2MSFTNGP03.phx.gbl...
> > Hi all
> >
> > I need to place an image from a web site onto a RS report. Is it possible
> to
> > do this in SRS 2000?
> > IE. http://<servername>/<directory>/<image.jpg> and render the image on
> the
> > report?
> >
> > Thanks in advance
> >
> > George
> >
> >
>
>|||Hi
No go.
Any other ideas'
"SL" <SL@.discussions.microsoft.com> wrote in message
news:F6A170BE-6F80-4522-92DD-1DD3969AE945@.microsoft.com...
> you need to set the image source to external and then set value => Fields!image_small.Value
> Or if you drag the image control from toolbox, you will see 4 options for
> image source, you need to choose Image Source = "Web" which the 4th
> option.
> Then click next and leave the URL empty...totally tempty... then click
> finish. Then you can edit the value of the image to the database field...
> which is full valid url.
>
> "Cristo" wrote:
>> Rephrasing the question:
>> I've assigned the image control value to be a database field containing a
>> valid URL however I'm getting a red cross when trying to display the
>> report.
>> Anyone got any ideas?
>> Thanks
>> George
>> "_george" <none@.nojne@.none> wrote in message
>> news:#kyvEDRmHHA.4688@.TK2MSFTNGP03.phx.gbl...
>> > Hi all
>> >
>> > I need to place an image from a web site onto a RS report. Is it
>> > possible
>> to
>> > do this in SRS 2000?
>> > IE. http://<servername>/<directory>/<image.jpg> and render the image on
>> the
>> > report?
>> >
>> > Thanks in advance
>> >
>> > George
>> >
>> >
>>

Previewing a report does not display the text fields at the correct position

Hi NG,
I've got a report in my report project in that the fields' position is
changed when previewing.
Have anybody recognized similar problems?On Apr 30, 6:16 am, "Martin Trabold" <m_trab...@.yahoo.de> wrote:
> Hi NG,
> I've got a report in my report project in that the fields' position is
> changed when previewing.
> Have anybody recognized similar problems?
Could you explain further. Do you mean fields changed order (i.e.,
Col1, Col2, Col4, Col3)? Or do you mean changed do to report borders
or page settings?
Enrique Martinez
Sr. Software Consultant

Preview/Print/Save reports

Hi

I have done many Reports using SQL 2005 Reporting Services and I can access them by:

http://localhost/Reports

In my ASP.NET application, I have 3 buttons:

Preview, Print and Save

If the user clicks on Preview the report is displayed

if the user clicks on Print,the report is sent automatically to the printer

if the user clicks on Save,the report is Exported in Pdf format to C:/

Can anyone give me some tutorials on how to manage Reports(.rdc) from ASP.NET

Hi,

From your description, it seems that you are wondering how to embed your report in ASP.Net application, right?

Generally, you can add a report viewer to your aspx page. You can follow the steps below to add your report viewer.

Open the Toolbox window. From the Data group, drag a ReportViewer control onto the page.

If it is not already open, open the ReportViewer Tasks smart tag panel by clicking the triangle in the upper right corner of the ReportViewer control on the Web page. In the Choose Report box, select the fully qualified file name for Report.rdlc.

When you select a report, instances of data sources used in the report are created automatically. Code is generated to instantiate each DataTable (and its DataSet container) and an ObjectDataSource control corresponding to each data source used in the report. This data source control is configured automatically.

And then, you can preview and print reports from reportviewer in the following way:
http://msdn2.microsoft.com/en-us/library/ms251693(VS.80).aspx

For more information on embed a report in your website, see:
http://msdn2.microsoft.com/en-us/library/ms252123(VS.80).aspx

Thanks.

preview tab does not display bound data

I have a table in my report that is bound to a dataset which in turn is bound
to a stored procedure. When I preview the report, the table remains blank,
despite the fact that I have bound the columns from my dataset to the table
on my report.
I get the following warning in my debug summary:
The data set â'DatasetNameâ' contains a definition for the field â'FieldName.
This field is missing from the returned result set from the data source.
However, I'm sure the result set is returning this field.
Any ideas on what could cause this?
Thanks.i figured out what the problem was. i did not properly bind the dataset.
"momo" wrote:
> I have a table in my report that is bound to a dataset which in turn is bound
> to a stored procedure. When I preview the report, the table remains blank,
> despite the fact that I have bound the columns from my dataset to the table
> on my report.
> I get the following warning in my debug summary:
> The data set â'DatasetNameâ' contains a definition for the field â'FieldName.
> This field is missing from the returned result set from the data source.
> However, I'm sure the result set is returning this field.
> Any ideas on what could cause this?
> Thanks.

preview reports?

In crystal reports you can just preview reports quickly can you do this
anyway in report services? I need to test out reports quickly as I build
them...When designing the report in Visual Studio, you just click the Preview
tab. Is that not working for you?
-Josh
Smokey Grindel wrote:
> In crystal reports you can just preview reports quickly can you do this
> anyway in report services? I need to test out reports quickly as I build
> them...|||Are you developing with the new controls in VS 2005 in local mode? If so
then the answer is no, you have to use your app to see the report.
If you are developing with Report Designer then yes you can preview.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Smokey Grindel" <nospam@.nospam.com> wrote in message
news:OswPlwEzGHA.4092@.TK2MSFTNGP04.phx.gbl...
> In crystal reports you can just preview reports quickly can you do this
> anyway in report services? I need to test out reports quickly as I build
> them...
>

Preview report drastically different than export or deployed

I have been noticing this for a while now and wanted to see if I could bring it to Microsoft's attention. On a lot of my reports, the preview looks one way, then when the report is deployed and run or exported, it looks different. And not just a few aesthetics, but lines, columns, colors, etc. I have spent numerous hours trying to fix a problem in the preview that never existed in the export or deployed version.

Does anyone have this issue? Is Microsoft aware of this issue?

What format are you trying to export to?

The "preview" in Visual Studio uses a different output format than the HTML displayed in the web control, so you may see some differences there.

Different renderers have different characteristics regarding pagination and capabilities; some differences are expected. If you supply some more details about exactly what problems your experiencing we may be able to give you some specific guidance.

Preview Problem!

I've set the pagesize to 11in, 8.5in and the body width to 10.5 to compensate
for the margins. the preview views in landscape but when i try to export to
pdf i get an "index out of range error." If I change the pagesize to 11in,
15in it works ok unless i drill down before the export, then i get the same
error again.Could you send me your RDL and preview cache file ( its name is
<reportname>.rdl.data) for investigation?
Thank you
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"VC" <VC@.discussions.microsoft.com> wrote in message
news:79011CE7-9208-4EF7-9F80-A793393F0A6D@.microsoft.com...
> I've set the pagesize to 11in, 8.5in and the body width to 10.5 to
> compensate
> for the margins. the preview views in landscape but when i try to export
> to
> pdf i get an "index out of range error." If I change the pagesize to
> 11in,
> 15in it works ok unless i drill down before the export, then i get the
> same
> error again.|||It did not happen on SP1.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"VC" <VC@.discussions.microsoft.com> wrote in message
news:10DD33E1-3EDD-46B3-97E0-B58024702F12@.microsoft.com...
> Yes, that would be great. How do i send them to you?
> "Lev Semenets [MSFT]" wrote:
>> Could you send me your RDL and preview cache file ( its name is
>> <reportname>.rdl.data) for investigation?
>> Thank you
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "VC" <VC@.discussions.microsoft.com> wrote in message
>> news:79011CE7-9208-4EF7-9F80-A793393F0A6D@.microsoft.com...
>> > I've set the pagesize to 11in, 8.5in and the body width to 10.5 to
>> > compensate
>> > for the margins. the preview views in landscape but when i try to
>> > export
>> > to
>> > pdf i get an "index out of range error." If I change the pagesize to
>> > 11in,
>> > 15in it works ok unless i drill down before the export, then i get the
>> > same
>> > error again.
>>

Preview pane not showing data

I have a problem within the Report Designer in Visual Studio .NET. I a new
report, I define a dataset that returns data via a SQL query fine. I design
the report without issues, but when I try to preview the report, no data is
displayed.
This error seems to have occured after applying reporting services SP1 to my
machine. No errors are reported, and nothing appears in the event log.
Any ideas as to how I can correct this problem out there?
Thanks in advance.
MattIs there an .rdl.data file in the same directory as your .rdl file? If so, delete it and try again.
Also, does hitting the Refresh button in the preview pane have any effect?
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt Pothier" <Matt.Pothier@.sonepar-us.com> wrote in message
news:eEtr8WvfEHA.596@.TK2MSFTNGP11.phx.gbl...
> I have a problem within the Report Designer in Visual Studio .NET. I a new
> report, I define a dataset that returns data via a SQL query fine. I design
> the report without issues, but when I try to preview the report, no data is
> displayed.
> This error seems to have occured after applying reporting services SP1 to my
> machine. No errors are reported, and nothing appears in the event log.
> Any ideas as to how I can correct this problem out there?
> Thanks in advance.
> Matt
>|||Thanks for the response. There were .rdl.data files in the same directory.
I tried deleting them, but got the same results. The refresh button did not
help either.
In a moment of frustration I uninstalled reporting services; performed a
repair install on VS .Net, reinstalled Reporting services; applied the
service pack and started over again. Everything seems to be working now.
"Donovan R. Smith [MSFT]" <donovans@.online.microsoft.com> wrote in message
news:%23iGKIuvfEHA.3556@.TK2MSFTNGP12.phx.gbl...
> Is there an .rdl.data file in the same directory as your .rdl file? If
so, delete it and try again.
> Also, does hitting the Refresh button in the preview pane have any effect?
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Matt Pothier" <Matt.Pothier@.sonepar-us.com> wrote in message
> news:eEtr8WvfEHA.596@.TK2MSFTNGP11.phx.gbl...
> > I have a problem within the Report Designer in Visual Studio .NET. I a
new
> > report, I define a dataset that returns data via a SQL query fine. I
design
> > the report without issues, but when I try to preview the report, no data
is
> > displayed.
> >
> > This error seems to have occured after applying reporting services SP1
to my
> > machine. No errors are reported, and nothing appears in the event log.
> >
> > Any ideas as to how I can correct this problem out there?
> >
> > Thanks in advance.
> >
> > Matt
> >
> >
>

Preview only returning one record of report

I'm new to reporting services so I assume this is a stupid question however here it goes.

I created a report that will produce a simple customer invoice. I use a stored procedure to return the data. When I run the dataset in the data section it returns 5 records. When I run the report in preview mode it only returns the first record of data in the report. I did notice that when I drag and drop the dataset fields into a report it appends the record with "First(Fields!..." I assume that this will only return the first record which it appears to do. When I remove the "First" it only returns the last record in the report. How do I return all 5 of the records in the preview pane. The paging section in the preview pane in all scenerios always says 1of1 with the next page arrow grayed out.

Thanks in advance..

Hi,

use tables in stead of text boxes to visualize tabular data.

Cheers,

Yani

|||

I don't want tabular data.

I want Name, Address, City State Zip of customer #1 on Page 1 Followed by detail (I use tabular data here) for that customer

Name, Address, City State Zip of customer #2 on Page 2 Followed by detail for that customer

All I get is Customet #1 (Or #5 if I remove the first stated in the original question..)

Etc..

|||

Okay,

did u try setting up all controls related to a customer into aListControl.

The contained info by a List Control is repeated for reach data row, this way you could achieve your need.

Cheers

|||

I'm totally lost at your suggestion??

I want to produce 5 invoices.

The Stored procedure returns data for those 5 invoices.( Name, address, city, state, zip etc..)

The report should return 5 pages with a customer invoice on each page so I can print them.

I'm not sure where a list control come into play here.

|||

If You don't want ur report in a tabular form then you will have to use list control.Drop textboxes into

the list control .

|||

Thank you,

I think I got it now.

One additional question I have with Formatting and Printing. Is there any type of formatting control for printing. In my research so far It appears that printing directly from the control is not an option. For now I will print to a PDF and then print. The problem I have is the PDF is creating what appears to be about a 1" margin on the page. I need the reports to print with about a .25" margin. Is there any place to control this. I will be doing checks next and the positiong will become more critical.

Thanks in advance.

|||

Go to Report Menu->Report properties -> Layout Tab.

I think this will help.

May

preview layout diferent than server layout

i am using reporting serveces :
i created a DataExtension to get a dataset's value in the report as an
xml input file (this works)
1. i am designing the report using this dataset's values and some
calculated expressions like "runningvalue" everything works fine in
the preview button but when the report is called from my c#
application the "runningvalue" is not being calculated while all the
other data is calculated.
2. the data in my report table is in groups were some groups must be
desplayed others hidden ,acccording to the xml values, as well as some
tables must be displayed otheres hidden in the preview button of the
report this works but also when the report is called from my c#
application all the tables and the groups are displaued which MUST NOT
HAPPEN
what can i do to fix this?
pls help
i need it urgently and i have been working on it for like a lot
by the way,
this is how i am calling the render: from my c# application :
proxyParameters[0] = new STB.testlab02.ParameterValue();
proxyParameters[0].Name = "DataSource";
//System.Data.DataSet dsetReport = new System.Data.DataSet();
DataSet dsReportMO = new DataSet();
dsReportMO.ReadXml("c:\\DataExt\\dsReport.xml");//dsReportMO.GetXml();
proxyParameters[0].Value = dsReportMO.GetXml();
// Build device info based on the start page
deviceInfo = String.Format(@."<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",
"emf");
//Exectute the report and get page count.
try
{
// Renders the first page of the report and returns streamIDs for
// subsequent pages
firstPage = repServ.Render(reportPath, format,
null,deviceInfo,proxyParameters,null,null,out encoding,out
mimeType,out reportHistoryParameters, out warnings, out streamIDs);
.
.
.i still cant find a solution pls help!!!!!!!
how can this work
////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////
mireille.azar@.ifsal.com (Mireille) wrote in message news:<63c1f241.0408250057.12d85680@.posting.google.com>...
> i am using reporting serveces :
> i created a DataExtension to get a dataset's value in the report as an
> xml input file (this works)
> 1. i am designing the report using this dataset's values and some
> calculated expressions like "runningvalue" everything works fine in
> the preview button but when the report is called from my c#
> application the "runningvalue" is not being calculated while all the
> other data is calculated.
> 2. the data in my report table is in groups were some groups must be
> desplayed others hidden ,acccording to the xml values, as well as some
> tables must be displayed otheres hidden in the preview button of the
> report this works but also when the report is called from my c#
> application all the tables and the groups are displaued which MUST NOT
> HAPPEN
> what can i do to fix this?
> pls help
> i need it urgently and i have been working on it for like a lot
>
> by the way,
>
> this is how i am calling the render: from my c# application :
>
> proxyParameters[0] = new STB.testlab02.ParameterValue();
> proxyParameters[0].Name = "DataSource";
> //System.Data.DataSet dsetReport = new System.Data.DataSet();
> DataSet dsReportMO = new DataSet();
> dsReportMO.ReadXml("c:\\DataExt\\dsReport.xml");//dsReportMO.GetXml();
> proxyParameters[0].Value = dsReportMO.GetXml();
> // Build device info based on the start page
> deviceInfo = String.Format(@."<DeviceInfo><OutputFormat>{0}</OutputFormat></DeviceInfo>",
> "emf");
> //Exectute the report and get page count.
> try
> {
> // Renders the first page of the report and returns streamIDs for
> // subsequent pages
> firstPage = repServ.Render(reportPath, format,
> null,deviceInfo,proxyParameters,null,null,out encoding,out
> mimeType,out reportHistoryParameters, out warnings, out streamIDs);
> .
> .
> .|||i found the solution...

Preview is better than web browser in this case

I like the functionality in the preview - that if all the parameters
have a valid value... it starts the query without pressing 'View
Report'.
Whereas in a published report in a browser, I always have to press the
'view report'.
In some cases one is better than the other - specially if the query is
small... but navigation needs to be faster.
I have repeatedly asked this question - without any answers.
But if it works in Preview... why not in the browser...'
Seems like there is setting... but no documentation...
I think we need both the options.Yes, we have struggled to answer this questions ourselves. We keep coming
back to this and will take your comments into consideration for sure. To
explain:
The problem is that on the server, there is really no way to stop the
execution of the query that was spawned by requesting a particular report.
So if you have all the defaults specified, then (for a live report) the
server will immediately kick off a report execution. This will inturn
execute the query. That query could take some time to run. Now, if the
user wants to change the values from default before the report finishes
executing, s/he might just click stop on the browser, change the paramters,
and click view report. Now due to how the browser works, the server
doesn't know the user has changed their minds. To the server it is just
another request. So now you have 2 report executions occuring for the same
user. The orignal request eventually will be killed on the report server
(once we realize the connection is closed/stale) but that still leaves the
long running query on the DB server.
So in effect by automatically running the report with the default parameters
you're increasing load significantly on the server. Now imagine how this
affects the system when you scale to 1000's of users.
In the designer, you don't have the same problem so you're more free to do
increase responsivness.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0407221045.28cb83ac@.posting.google.com...
>I like the functionality in the preview - that if all the parameters
> have a valid value... it starts the query without pressing 'View
> Report'.
> Whereas in a published report in a browser, I always have to press the
> 'view report'.
> In some cases one is better than the other - specially if the query is
> small... but navigation needs to be faster.
> I have repeatedly asked this question - without any answers.
> But if it works in Preview... why not in the browser...'
> Seems like there is setting... but no documentation...
> I think we need both the options.|||I would say - give those controls to the designer.
Let him/her decide -
1. On Default parameters - should execute immediately or not
2. On Change selection - should execute immediately or not
- parameter wise control. I.e. decide whether a particular
parameter should execute the report immediately or not
Provide with a control with each parameter.
Cascading parameters should have that option also - Specially if
selection of Parameter 1 leads to default of Parameter 2.
- In which case - first of all the "dependency" should be explicitly
defined.
- And then - whether to execute the report immediately or not - could
also be a designers choice.
Just some thoughts...

Preview has data but no columns returned

In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

I just ran a test on my side and it actually worked. I create a simple sp with a query (select * from adventureworks.production.productcategory) and the use it in the OLE DB. The preview worked fine and when connecting to a copy column transform I was able to see all the columns. However there is another thread going on now about the same issue and they say there are some work arounds:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=786591&SiteID=1

In case you need it this is the xml code of my package

<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">MARINER\rsalas</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">MARINERLAPTOP14</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">10/4/2006 8:05:36 AM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">3</DTS:Property><DTS:Property DTS:Name="VersionGUID">{28EC0411-DE09-4FBE-AA35-FA11901A0511}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>

<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">ETLRafLab</DTS:Property><DTS:Property DTS:Name="DTSID">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="Retain">0</DTS:Property><DTS:Property DTS:Name="ConnectionString">Data Source=MARINERLAPTOP14;Initial Catalog=ETLRafLabDev;Provider=SQLNCLI.1;Integrated Security=SSPI;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></DTS:ConnectionManager>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsControlFlowDiagram&gt;&lt;dwd:BoundingTop&gt;2593&lt;/dwd:BoundingTop&gt;&lt;dwd:Layout&gt;&lt;dds&gt;

&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="4" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="29951" y="19156" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;

&lt;font&gt;

&lt;ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /&gt;

&lt;/font&gt;

&lt;mouseicon&gt;

&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;

&lt;/mouseicon&gt;

&lt;/diagram&gt;

&lt;layoutmanager&gt;

&lt;ddsxmlobj /&gt;

&lt;/layoutmanager&gt;

&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Data Flow Task" left="7594" top="2593" logicalid="3" controlid="3" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}" vartype="8" /&gt;

&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;shape groupshapeid="0" groupnode="0" /&gt;

&lt;/ddscontrol&gt;

&lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;/dwd:DtsControlFlowDiagram&gt;&lt;/Package&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="DTSID">{D803BA1C-C91E-408F-9D6F-1226D9333CD0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;ConnectionManager xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DataSourceID&gt;ETLRafLab&lt;/dwd:DataSourceID&gt;&lt;/ConnectionManager&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{82682565-2AB7-4AE8-AB84-A06FBB937955}</DTS:Property><DTS:Property DTS:Name="DTSID">{CDC092B4-325E-4283-8E03-88E168C82372}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DtsDataFlowDiagram&gt;&lt;dwd:BoundingTop&gt;1667&lt;/dwd:BoundingTop&gt;&lt;dwd:Layout&gt;&lt;dds&gt;

&lt;diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="9" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="29951" y="18230" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="0" backpictureclsid="{00000000-0000-0000-0000-000000000000}"&gt;

&lt;font&gt;

&lt;ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" /&gt;

&lt;/font&gt;

&lt;mouseicon&gt;

&lt;ddsxmlobjectstreamwrapper binary="6c74000000000000" /&gt;

&lt;/mouseicon&gt;

&lt;/diagram&gt;

&lt;layoutmanager&gt;

&lt;ddsxmlobj /&gt;

&lt;/layoutmanager&gt;

&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="OLE DB Source" left="11192" top="1667" logicalid="4" controlid="4" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}/components/1" vartype="8" /&gt;

&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;shape groupshapeid="0" groupnode="0" /&gt;

&lt;/ddscontrol&gt;

&lt;ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Copies columns." left="11192" top="4233" logicalid="7" controlid="7" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" /&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}/components/83" vartype="8" /&gt;

&lt;property name="ShowConnectorSource" value="0" vartype="2" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;shape groupshapeid="0" groupnode="0" /&gt;

&lt;/ddscontrol&gt;

&lt;ddscontrol controlprogid="MSDDS.Polyline" left="12592" top="2432" logicalid="8" controlid="8" masterid="0" hint1="0" hint2="0" width="799" height="2301" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0"&gt;

&lt;control&gt;

&lt;ddsxmlobj&gt;

&lt;polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/control&gt;

&lt;layoutobject&gt;

&lt;ddsxmlobj&gt;

&lt;property name="LogicalObject" value="{B59730D8-9045-4EB3-A965-95240CC97D28}/paths/86" vartype="8" /&gt;

&lt;property name="Virtual" value="0" vartype="11" /&gt;

&lt;property name="VisibleAP" value="0" vartype="3" /&gt;

&lt;/ddsxmlobj&gt;

&lt;/layoutobject&gt;

&lt;connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout" sourceid="4" destid="7" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0"&gt;

&lt;point x="12991" y="2831" /&gt;

&lt;point x="12991" y="4233" /&gt;

&lt;/connector&gt;

&lt;/ddscontrol&gt;

&lt;/dds&gt;&lt;/dwd:Layout&gt;&lt;/dwd:DtsDataFlowDiagram&gt;&lt;dwd:DtsComponentDesignerPropertiesList&gt;&lt;dwd:DtsComponentDesignTimeProperty&gt;&lt;dwd:key xsi:type="xsd:string"&gt;1 DataSourceViewID&lt;/dwd:key&gt;&lt;/dwd:DtsComponentDesignTimeProperty&gt;&lt;/dwd:DtsComponentDesignerPropertiesList&gt;&lt;/TaskHost&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="DTSID">{A8831B5B-654F-43D4-BAA9-605512FCCDB0}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable>

<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8">&lt;PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"&gt;&lt;dwd:DestinationName&gt;Copy Column Input&lt;/dwd:DestinationName&gt;&lt;dwd:SourceName&gt;OLE DB Source Output&lt;/dwd:SourceName&gt;&lt;/PipelinePath&gt;</DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{B59730D8-9045-4EB3-A965-95240CC97D28}-86</DTS:Property><DTS:Property DTS:Name="DTSID">{2C57A2EE-F8A6-4FD6-8BAF-54C95E9E30B7}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions>

<DTS:Executable DTS:ExecutableType="DTS.Pipeline.1"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact">Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1</DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Data Flow Task</DTS:Property><DTS:Property DTS:Name="DTSID">{B59730D8-9045-4EB3-A965-95240CC97D28}</DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property><DTS:Property DTS:Name="CreationName">DTS.Pipeline.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">

<components>

<component id="1" name="OLE DB Source" componentClassID="{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}" description="OLE DB Source" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="7" pipelineVersion="0" contactInfo="OLE DB Source;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;7">

<properties>

<property id="2" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</property>

<property id="3" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>

<property id="4" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>

<property id="5" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None">Exec sp_SelectFromProductCategory</property>

<property id="6" name="SqlCommandVariable" dataType="System.String" state="default" isArray="false" description="The variable that contains the SQL command to be executed." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property>

<property id="7" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</property>

<property id="8" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</property>

<property id="9" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">2</property>

<property id="15" name="ParameterMapping" dataType="System.String" state="default" isArray="false" description="The mappings between the parameters in the SQL command and variables." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></property></properties>

<connections>

<connection id="10" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{82682565-2AB7-4AE8-AB84-A06FBB937955}"/></connections>

<outputs>

<output id="11" name="OLE DB Source Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>

<outputColumn id="32" name="ProductCategoryID" description="" lineageId="32" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="31"/>

<outputColumn id="35" name="Name" description="" lineageId="35" precision="0" scale="0" length="50" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="34"/>

<outputColumn id="38" name="rowguid" description="" lineageId="38" precision="0" scale="0" length="0" dataType="guid" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="37"/>

<outputColumn id="41" name="ModifiedDate" description="" lineageId="41" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="40"/></outputColumns><externalMetadataColumns isUsed="True">

<externalMetadataColumn id="31" name="ProductCategoryID" description="" precision="0" scale="0" length="0" dataType="i4" codePage="0"/>

<externalMetadataColumn id="34" name="Name" description="" precision="0" scale="0" length="50" dataType="wstr" codePage="0"/>

<externalMetadataColumn id="37" name="rowguid" description="" precision="0" scale="0" length="0" dataType="guid" codePage="0"/>

<externalMetadataColumn id="40" name="ModifiedDate" description="" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0"/></externalMetadataColumns></output>

<output id="12" name="OLE DB Source Error Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>

<outputColumn id="33" name="ProductCategoryID" description="" lineageId="33" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="36" name="Name" description="" lineageId="36" precision="0" scale="0" length="50" dataType="wstr" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="39" name="rowguid" description="" lineageId="39" precision="0" scale="0" length="0" dataType="guid" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="42" name="ModifiedDate" description="" lineageId="42" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="13" name="ErrorCode" description="" lineageId="13" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>

<outputColumn id="14" name="ErrorColumn" description="" lineageId="14" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></outputColumns><externalMetadataColumns isUsed="False"/></output>

</outputs>

</component>

<component id="83" name="Copy Column" componentClassID="{9A9C066E-59CB-4332-B899-8783F6049B08}" description="Copies columns." localeId="-1" usesDispositions="false" validateExternalMetadata="True" version="0" pipelineVersion="0" contactInfo="Copy Column;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;0">

<inputs>

<input id="84" name="Copy Column Input" description="" hasSideEffects="false" dangling="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></input>

</inputs>

<outputs>

<output id="85" name="Copy Column Output" description="" exclusionGroup="0" synchronousInputId="84" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><externalMetadataColumns isUsed="False"/></output>

</outputs>

</component>

</components>

<paths>

<path id="86" name="OLE DB Source Output" description="" startId="11" endId="84"/>

</paths></pipeline></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="ObjectName">OLEDB source with SP</DTS:Property><DTS:Property DTS:Name="DTSID">{5B6C56E1-90E4-4B8E-85C0-E150AF873875}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>

|||yeah I have a similar package connecting to another database (and executing a stored procedure) and that works just fine. But the package that I described in my last email just refuses to get going...btw..thanks for the other link - let me check if tweaking the procedure helps - appreciate your help!!|||

Manmeet Panigrahi wrote:

In my SSIS package, I connect to an external SQL server database. This external database supports a stored procedure that I need to execute to "retrieve data". So in my package, I set the DataAccess Mode property of my OLEDB datasource to "SQL Command" and I provide the command EXEC <proc_name> <Param>,<output_param>. (The proc has an output parameter). The preview shows all the columns and data, but somehow no columns are returned....so when I try to link this data source to a copy column task, I get an error saying the source does not have any columns...any idea why this could be happening. Thanks - Manmeet

I think the root cause of the problem here is that sprocs don't own their own metadata like tables and views do. It is theoretically possible to return completely different result sets from the same sproc depending on what parameters you pass it so there is no real metadata to bind to. Adam Machanic has a great discussion about this here:

Stored procedures are not parameterized views
(http://www.sqljunkies.com/WebLog/amachanic/archive/2006/05/29/21482.aspx)

Try the workaround that I suggested on the other thread.

-Jamie

|||

ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!

So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

|||

Manmeet Panigrahi wrote:

ok...now there is a new twist to the tale. I just found out that the select statement in the stored procedure that returns the data does a select on a temp table. And that is the reason why the columns are not being detected correctly. I also found out from another developer that they had the same issue in DTS (2000) and hence they built the DTS package using disconnected edit and it works fine!

So assuming that we are not able to change the proc, is there a way we can do the disconnected edit kind of workaround in SSIS? -Manmeet

There's similar. You could set the sql dynamically at runtime using an expression. That might be a very clever workaround actually. I'd be interested to see if it works.

-Jamie

|||Unfortunately that didnt work too...this is what I did...I now have this variable that stores the command to execute the proc that returns the data (EXEC pr_....). (This variable is populated at run time). Also this variable becomes the Data Access Mode of my OLEDB data source. Now at design time, I have this variable populated with a select statement that returns data in the correct schema format..so that I am able to setup my source destination mappings. So when the package runs, the variable gets populated (at runtime) with the EXEC statement...but unfortunately SSIS revalidates the schema (and detects that the EXEC does not return any columns) and gives me the "VS_NEEDSNEWMETADATA" errror....sigh...Unfortunately the proc is owned by a different set of ppl and it will take a lot of time for me to convince them to change it..