Foreach Loop is killing me!!!!!!!!!!!!!!!!!!!!!!

I would like your help with the Foreach loop container. Boy, am I having issues with using it for looping through an ADO.NET dataset or what!?!? My control flow has a data flow task that is executing a Data Reader task (creating a .NET dataset for me). Now I go back to the control flow and add a Foreach loop container to loop through each record in my dataset. But which type of the Foreach loop container should I be using? I see an option to use Foreach ADO.NET schema enumerator but not sure how to configure it. Also I used the Foreach ADO option, but setting up variables to go through every column is an absolute pain in the you know what!!?!! I have about 200 columns and I want an easier way to refer to those columns in my transformation phase.

Hope you are able to help me out here.

Jamie posted an answer on another thread!!?!!?!!?!!?!!!!!!!!?!!!!!

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

Fore Color Expressions

Hi all, I can't seem to find an example anywhere on the net or BOL on this one. I assume you can set the color of a calculated members dynamically in the Fore Color Expression property but I can't find any code samples for that. Can someone provide me a simply one for changing the color to red if a condition is met?

I really appreciate it!

Brian

Hi Brian,

Here's a recent query for the AS 2000 Foodmart Sales cube - you should see some cells highlighted in red when this is run in the MDX Sample App:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/3a1eccc9d291c961

>>

With Member [Measures].[testmetric] AS
'[Measures].[Store Sales Net]-[Measures].[Store Sales]',

FORE_COLOR ='iif([Time].currentMember.Level is [Time].[Quarter] And
([Time].CurrentMember.PrevMember,[Measures].[testmetric])
- CalculationPassValue( [Measures].[testmetric], -1,Relative )

> Abs(([Time].CurrentMember.PrevMember,Measures.[testmetric])) * 0.025,

rgb(255,0,0), rgb(0,0,0))',
FORMAT_STRING = 'Currency'

SELECT
{[Measures].[Store Sales], [Measures].[Store Sales Net],
Measures.[testmetric]} ON COLUMNS ,
CrossJoin({[Product].[Product Family].[Drink],
[Product].[Product Family].[Food],
[Product].[Product Family].[Non-Consumable]},
{{[Time].[Year].[1997].CHILDREN}}) ON ROWS

FROM [Sales] CELL PROPERTIES
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]

>>

|||

Thanks, so it sounds like I'd want to do something like this at runtime in the OLAP client application and not in the SQL Server 2005 Color Expressions of a Calculated Member?

Brian

|||

You can do this using Calculations tab of Sql Server Busyness Intelligence Studio too. When you press the button on the right side of a color expression textbox (there are 2 of those – for foreground and background color) there will be a color chooser dialog box to pick the desired color. When you have selected the color its code will be inserted in the text box. You can subsequently pick another color and it will add it to the text box again. Thus the contents of the text box will have 2 color codes with comments.

Now you can modify the contents of the textbox to have an expression using the codes you obtained. Something like:

iif([measures].currentmember > 0, 4227327 /*R=255, G=128, B=64*/, 8388608 /*Navy*/)

If you like editing MDX scripts in the plain text format then you might find convenient usage of the debugger as a helper to write the script. When you are on the calculations tab you can hit F5 and the tab will load the debugger. In the text box for the script you can type your MDX statements and click toolbar buttons to select colors and fonts. The codes will be inserted at the cursor of the script text box. You can also drag the templates of MDX statements from the tree views on the left side of the debugger.

Having constructed your statement, you can execute it (F10) and immediately test with the pivot table or MDX query you like (there are 4 tabs for custom queries). Pressing F10 subsequently will step over till the end and restart from the beginning of the script. Breakpoints also help.

Once you are done you can select Debug | Stop menu command and you will be offered to keep your script or discard. Thus you can use the debugger to try something and throw away (MDX prototyping).

|||Thanks Andrew, that worked like a champ.sql

Forced Output Format - RS2000

A few details first:

The report server is remote to the development server (VS 2003).

The web application that will be calling it is ASP.NET 2.0 and developed in VS 2005.

I have a couple of questions:

a: Can I link to the report from the web application using a basic Hyperlink control rather than using a ReportViewer control?

b: How can I force which format it opens in? Say I want it to be rendered as a PDF in one instance but at an XSL document in another.

Many thanks and kindest regards,

TwoForTea

Yes, you can link to a report using a hyperlink such as

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render

If you want to force the format you can use this:

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=PDF

or

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=EXCEL

(Look in the books online for other format settings such as HTML, MHTML, CSV)

Forced Output Format - RS2000

A few details first:

The report server is remote to the development server (VS 2003).

The web application that will be calling it is ASP.NET 2.0 and developed in VS 2005.

I have a couple of questions:

a: Can I link to the report from the web application using a basic Hyperlink control rather than using a ReportViewer control?

b: How can I force which format it opens in? Say I want it to be rendered as a PDF in one instance but at an XSL document in another.

Many thanks and kindest regards,

TwoForTea

Yes, you can link to a report using a hyperlink such as

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render

If you want to force the format you can use this:

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=PDF

or

http://localhost/ReportServer?/AWReporter/Sales By Territory&

rs:Command=Render&rs:Format=EXCEL

(Look in the books online for other format settings such as HTML, MHTML, CSV)

Forced auto refresh to avoid page caching

Good Afternoon!
I've run into an issue whilst developing a report using VS.NET, RS and SQL
Enterprise Server 2000.
The problem I have is that I'm using RS to develop a web based app that can
be used to edit base table data. I have the app working fine, but it keeps
taking it's data from the cached reports. Obviously, this means that every
time a change is made to the base data, the user needs to refresh the page -
not an easy thing to remember when processing several hundred entries.
I have the "Do not cache temporary copies of this report" radio button
selected (by default). Also, the "Autorefresh" text box is not an option I
can take as it is unusable when there are groups set up on the table - they
keep expanding and collapsing.
I've seen ways of embedding some (rather shaky) HTML to force the refresh.
See below:
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<META HTTP-EQUIV="Pragma-directive" CONTENT="no-cache">
<META HTTP-EQUIV="cache-directive" CONTENT="no-cache">
Is anyone aware of how I can write this into the .rdl, or report? Or, is
there a way to force the page to refresh?
Thanks in advance
JonTry adding the rs:ClearSession=true to the report url.
--
Adrian M.
MCP
"Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
news:%23aqKz5aHFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Good Afternoon!
> I've run into an issue whilst developing a report using VS.NET, RS and SQL
> Enterprise Server 2000.
> The problem I have is that I'm using RS to develop a web based app that
> can be used to edit base table data. I have the app working fine, but it
> keeps taking it's data from the cached reports. Obviously, this means
> that every time a change is made to the base data, the user needs to
> refresh the page - not an easy thing to remember when processing several
> hundred entries.
> I have the "Do not cache temporary copies of this report" radio button
> selected (by default). Also, the "Autorefresh" text box is not an option
> I can take as it is unusable when there are groups set up on the table -
> they keep expanding and collapsing.
> I've seen ways of embedding some (rather shaky) HTML to force the refresh.
> See below:
> <META HTTP-EQUIV="Pragma" CONTENT="no-cache">
> <META HTTP-EQUIV="Pragma-directive" CONTENT="no-cache">
> <META HTTP-EQUIV="cache-directive" CONTENT="no-cache">
> Is anyone aware of how I can write this into the .rdl, or report? Or, is
> there a way to force the page to refresh?
> Thanks in advance
>
> Jon
>
>|||I tried it manually, and it didn't appear to change anything.
Is there a way I can embed that statement in the rdl?
Thanks
Jon
"Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
news:euodxncHFHA.4048@.TK2MSFTNGP15.phx.gbl...
> Try adding the rs:ClearSession=true to the report url.
> --
> Adrian M.
> MCP
> "Jonathan Martin" <jonathan.martin@.pcservicecall.co.uk> wrote in message
> news:%23aqKz5aHFHA.1172@.TK2MSFTNGP12.phx.gbl...
>> Good Afternoon!
>> I've run into an issue whilst developing a report using VS.NET, RS and
>> SQL Enterprise Server 2000.
>> The problem I have is that I'm using RS to develop a web based app that
>> can be used to edit base table data. I have the app working fine, but it
>> keeps taking it's data from the cached reports. Obviously, this means
>> that every time a change is made to the base data, the user needs to
>> refresh the page - not an easy thing to remember when processing several
>> hundred entries.
>> I have the "Do not cache temporary copies of this report" radio button
>> selected (by default). Also, the "Autorefresh" text box is not an option
>> I can take as it is unusable when there are groups set up on the table -
>> they keep expanding and collapsing.
>> I've seen ways of embedding some (rather shaky) HTML to force the
>> refresh. See below:
>> <META HTTP-EQUIV="Pragma" CONTENT="no-cache">
>> <META HTTP-EQUIV="Pragma-directive" CONTENT="no-cache">
>> <META HTTP-EQUIV="cache-directive" CONTENT="no-cache">
>> Is anyone aware of how I can write this into the .rdl, or report? Or, is
>> there a way to force the page to refresh?
>> Thanks in advance
>>
>> Jon
>>
>

Force protocol encryption

I am connection to a SQL server that has force protocol encryption checked. From my ASP.net application, specifically in my connection string, what parameter do I need to use to take advantage of this encryption?

Thanks!If using OLEDB, add this to the connection string:

Use Encryption for Data=True

Force Drop Database

Hi everybody,

I would like to know if there would be any special way to force drop a database from an ASP.NET page.

When I try to do it in the normal way, it gives me an exception like: Cannot drop the database 'xxxxxxx' because it is currently in use. I'would have to wait until there is a timeout.
In fact that database can be accessed from another pages, but I want to know if I'd be able to force drop database even when another pages are using it.

Thanks in advanceWhy must you be able to "drop" a database while it's still in use?|||Just imagine an application that creates a database for each customer, which contains personalized data for another application. Then just imagine that one customer doesn't pay his/her month fee and we wanted to delete his/her data, but he is still using it. Of course because we are the application admins, we have to be able to do that, even if database is still in use.

I hope this answer convince you and could tell me if there's a solution.

Thank you|||Personally, I wonder what happens if just after you drop the database, the user's payment arrives. To handle this scenario, I would have some other way of controlling a user's access to the system rather than deleting the database. Perhaps a flag in the database to tell you if the user should be able to access the database. I actually wonder about one database per customer, but that is an entirely different issue...

FOR XML AUTO broken in 2005

Re: http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic32700.aspx

I'm having exactly the same problem, although I'm writing queries that need to run on both SQL 2000 and 2005. I cannot believe this isn't a bug. Although I understand the logic behind the results I cannot accept the results in 2005 are correct. If each part a union produces a parent/child structure why is it considered correct that UNIONing the two produces a flat no-child relationship? It makes no sense, I don't want to see a compatible mode in a service pack for 2005 I want to see the bug fixed!

I understand your problem is that you want to write an XML publishing query with the specific UNION that works both in SQL Server 2000 and in SQL Server 2005. Please consider using FOR XML EXPLICIT - it should solve your problem.

Also note that SQL Server 2005 Service Pack 1 should contain a fix for the compatibility issue - FOR XML AUTO query with the UNION from the link you provided will work the same way between SQL Server 2000 and SQL Server 2005 SP1 for a database with 80 compatibility level, thus not breaking your application after upgrade to SQL Server 2005 SP1. FOR XML AUTO with the UNION on a database with 90 compatibility level will work the same in SQL Server 2005 RTM and SP1.

Regards,

Eugene

Technical Lead,

Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you for the reply, and yes I am using XML Explicit to work-around the problem...and it looks horrible.

I also understand that the service pack will have this special compatible mode, my point is that it shouldn't have. I don't understand the justification of letting 2005 produce results the way it does. I'm stating that this is a bug and should be fixed. I'd be very interested in learning why Microsoft feel this is a compatibility issue and not a bug. Given that if Select X...For XML AUTO produces a parent/child result surely unioning two lots of Select X would still produce a parent/child result?

|||

In addition to my explanations in http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic32700.aspx I'd say that while column naming derived from the first leg of UNION [ALL] is documented in BOL column-to-table association (which FOR XML AUTO uses) on top of UNION [ALL] was never documented; SQL Server 2000 behavior there is incorrect.

I generally discourage you from using AUTO mode of FOR XML on top of set operations (like UNION [ALL]/EXCEPT/INTERSECT) since it will prevent from using some performance optimizations we can do in AUTO mode. This is in SQL Server 2005. In SQL Server 2000 we would apply the optimizations but because of the buggy column-to-table associations we can get wrong results. I provided a repro for the wrong results below.

If you need UNION ALL and not UNION you may consider supplying two separate FOR XML AUTO and concatenating the results on the client side. This is given that you need to use a syntax that works on both SQL Server 200 and 2005. In SQL Server 2005 this can be achieved in a more explicit and cleaner way.

Here’s the SQL Server 2000 repro that produces wrong results. Notice different PK constraints on different tables and duplicate col1 values for t2 and t4.

create table t1(col1 int not null primary key, col2 varchar(256) not null)

insert t1 select 1,'t1col2row1'

insert t1 select 2,'t1col2row2'

go

create table t3(col1 int not null primary key, col2 varchar(256) not null)

insert t3 select 1,'t3col2row1'

insert t3 select 2,'t3col2row2'

go

create table t2(col1 int not null, col2 varchar(256) not null primary key)

insert t2 select 1,'t2col2row1'

insert t2 select 1,'t2col2row2'

go

create table t4(col1 int not null, col2 varchar(256) not null primary key)

insert t4 select 1,'t4col2row1'

insert t4 select 1,'t4col2row2'

go

select t1.col1,t1.col2 col12,t3.col2 from t3

inner join t1 on t3.col1 = t1.col1

union

select t2.col1,t2.col2 col12,t4.col2 from t4

inner join t2 on t4.col1 = t2.col1

for xml auto

go

Regards,

Eugene

Technical Lead,

Microsoft SQL Server

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Again, thank you for the reply, interesting to hear that it doesn't perform very well. As I've mentioned I do understand the algorithm that is producing the results, what I'm saying is the algorithm is fundamentally flawed when used with UNIONs. To the user, SQL 2000 produces logical results whereas 2005 does not, for me that tells me that this is a bug. If you want to tell me that UNIONs and FOR XML AUTO are not longer supported but we'll provide a compat' mode, then I can swallow that. What I don't understand is the view that it's working fine in 2005 when clearly it doesn't.

As for the workarounds (and doesn't this also imply a bug) those are ok (not a great fan of using the client to do that) and I'm using the EXPLICIT XML alternative.

I don't want to appear argumentative but I would just like it to be recognised as a failing in 2005 and it should be documented as a breaking change rather than have a test fail or ,worse, have a customer report that upgrading to 2005 has broken their crucial application and have had to downgrade back to 2000! All of which could be avoided by clearly stating that there is a breaking change.

For Each Loop Container in SSIS

Is anyone aware of any good links on how to use the
For Each Loop Container for ADO.NET dataset?
Thanks,
SHivayou can check out http://sqlis.com. Alan and Darren have some good info
there.
-oj
"Shiva" <arbitsquare@.hotmail.com> wrote in message
news:ef8P%23P3fGHA.3364@.TK2MSFTNGP05.phx.gbl...
> Is anyone aware of any good links on how to use the
> For Each Loop Container for ADO.NET dataset?
> Thanks,
> SHiva
>
>